Go after
0%

sql脚本

发表于 更新于 分类于 阅读次数：
本文字数： 8.7k 阅读时长 ≈ 8 分钟

sql脚本

sql使用-高级

comment

information_schema库的COLUMNS表，里面记录了mysql所有库中所有表的字段信息

字段信息解释：https://blog.csdn.net/lkforce/article/details/79557482

查看某个库所有表的注释

1
2
3
4
5
6
7
8
9
SELECT
	table_name 表名,
	table_comment 表说明 
FROM
	information_schema.TABLES 
WHERE
	table_schema = '要查询的库' 
ORDER BY
	table_name;

rank

某个库所有表添加共同列

1
2
3
4
5
6
7
8
SELECT
	concat( 'ALTER TABLE ', table_schema, '.', table_name, ' ADD COLUMN te_id varchar(64) COMMENT "租户id", 
	ADD COLUMN bran_id varchar(64) COMMENT "品牌id";
	' ) 
FROM
	information_schema.TABLES t
WHERE
	table_schema = 'table_name';

某个库的所有表共同列初始化值

1
2
3
4
5
6
SELECT
	concat( 'update ', table_schema, '.', table_name, ' set te_id = "C001",bran_id = 100;' ) s
FROM
	information_schema.TABLES t 
WHERE
	table_schema = 'table_name';

select

根据值(条件）查不同字段（列）

1
2
3
4
5
6
7
8
9
10
SELECT   
	sbi.goods_code,  
	sbi.goods_name,  
	case sbi.is_review_stock when 1 then sbi.review_theoretical_count when 0 then sbi.theoretical_count end theoretical_count
FROM  
	sexytea_pss.t_item sbi   
WHERE  
	AND sb.`status` = "FINISHED"   
	AND sb.commit_time > "2022-10-31 12:00:00" 

对比两列值差集（A-B）

1
2
3
4
5
6
7
SELECT
  a.code
FROM
  a
  LEFT JOIN b ON a.code = b.code 
WHERE
  b.code IS NULL；

查询所有非系统库的表的注释

1
2
3
4
5
6
7
8
9
10
SELECT
	table_schema 库名,
	table_name 表名,
	table_comment 表说明 
FROM
	information_schema.TABLES 
WHERE
	table_schema not in ('mysql','information_schema','performance_schema','sys')
ORDER BY
	table_name;

查询某个库所有表及字段的注释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	a.table_name 表名,
	a.table_comment 表说明,
	b.COLUMN_NAME 字段名,
	b.column_comment 字段说明,
	b.column_type 字段类型,
	b.column_key 约束 
FROM
	information_schema.TABLES a
	LEFT JOIN information_schema.COLUMNS b ON a.table_name = b.TABLE_NAME 
WHERE
	a.table_schema = '要查询的库'
ORDER BY
	a.table_name

查询某表的所有字段的注释，按照字段顺序排列

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	COLUMN_NAME 列名,
	COLUMN_COMMENT 描述,
	COLUMN_TYPE 数据类型,
	if(IS_NULLABLE = 'NO', 'NO','') AS '是否为空',
	COLUMN_KEY AS '约束条件'
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = '要查询的库'
	AND table_name = '要查询的表'
ORDER BY ORDINAL_POSITION;

insert

插入另一张的内容

1
2
3
4
5
6
DELETE FROM	mdm_post_post_type;
# 插入另一张表
INSERT INTO mdm_post_post_type ( post_post_type_id, post_code, type_id, is_del )
 SELECT post_id, p.post_code, p.type_id, 0 FROM mdm_post p WHERE p.type_id IS NOT NULL ;
# 修改雪花id前缀
update mdm_post_post_type ppt1, mdm_post_post_type ppt2 set ppt1.post_post_type_id = (concat('PPTY',substring(ppt1.post_post_type_id, 4) ) ) where ppt1.post_post_type_id = ppt2.post_post_type_id;

update

修改表字段 名称和类型

1
ALTER TABLE 表名 CHANGE 旧字段名 新字段名  VARCHAR ( 64 ) comment '主键';

修改数据表字符集和排序规则

– 使用方法：先执行如下脚本生成修改数据表和表字段的脚本，然后再执行这些生成的脚本。
– 修改指定数据库中所有数据表的字符集为 utf8mb4，并将排序规则修改为 utf8mb4_unicode_ci

1
2
3
4
5
6
7
8
9
10
SELECT
	CONCAT( 'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = '库名' 
	AND table_name IN (
		'hr_test',
	'hr_test2' 
	)

修改字段的字符集和排序规则

– 使用方法：先执行如下脚本生成修改数据表和表字段的脚本，然后再执行这些生成的脚本。

– 修改指定数据库中所有varchar类型的表字段的字符集为 utf8mb4，并将排序规则修改为utf8mb4_unicode_ci

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
	CONCAT(
		'ALTER TABLE `',
		table_name,
		'` MODIFY `',
		column_name,
		'` ',
		DATA_TYPE,
		'(',
		CHARACTER_MAXIMUM_LENGTH,
		') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
		( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
		' comment "', column_comment,'";' 
) 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = '库名' 
	AND DATA_TYPE = 'varchar' 
	AND ( CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_unicode_ci' ) 
	AND table_name in( 'test' );

修改某个字段中的某些内容

1
2
# 将字段body_content_name中的,替换为|
update bpm_matrix_body set body_content_name = replace(body_content_name,',','|');

用一张表的字段更新另一张表的字段

1
2
3
4
5
UPDATE mdm_uat.mdm_dept d1,
test.mdm_dept d2 
SET d1.wx_dept_id = d2.wx_dept_id 
WHERE
	d1.dept_code = d2.dept_code

用一张表的数据修改另一张表的数据逐条生成sql语句

1
2
3
4
5
6
7
8
9
10
11
12
# 角色id生成关闭用户sql
SELECT
	concat( 'update mds.user user u set u.status = 3 where u.id = "', temp.u_id, '";' ) 
FROM
	(
	SELECT
		r.u_id 
	FROM
		role r
	WHERE
		r.id IN ( "12" ) 
	) temp;

batch update

update set when then insert into values on duplicate key update replace into values 临时表 or 分区表 for 循环
优点 灵活性高，不会重置自增ID SQL简单
缺点 sql复杂，速度比insert into 慢 速度快 1.唯一索引相同时，先删除再插入，重置自增ID 2.速度比insert略慢 可能会增加查询复杂度 速度慢
场景 更新字段少 大型表或需要高效批量更新 ？

  1. update set when then, set when then where

    1
    2
    3
    4
    5
    6
    7
    UPDATE users
    SET last_name = CASE
      WHEN user_id = 1 THEN 'Doe'
      WHEN user_id = 2 THEN 'Smith'
      WHEN user_id = 3 THEN 'Johnson'
    END
    WHERE user_id IN (1, 2, 3);
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    update t_item
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="actual_receive = case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.actualReceive != null">
                    when sku_code = #{item.skuCode} then #{item.actualReceive}
                </if>
            </foreach>
        </trim>
    </trim>
    where
    <foreach collection="list" index="index" item="item" separator="or">
        order_id = #{item.orderId}
    </foreach>

  2. insert into values … on duplicate key update

    1
    2
    3
    4
    5
    6
    7
    8
    <insert id="batchUpdateUsers" parameterType="java.util.List">
        INSERT INTO user (id, name, age) VALUES 
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id}, #{item.name}, #{item.age})
        </foreach>
        ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age)
    </insert>

  3. REPLACE INTO … VALUES

    是一个删除和插入的组合操作，当新记录与现有记录具有相同的唯一键值时，它会删除现有记录并插入新记录。

    在插入新记录时重置自增ID的值

    1
    2
    3
    4
    5
    6
    <insert id="batchReplaceUsers" parameterType="java.util.List">
        REPLACE INTO user (id, name, age) VALUES 
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id}, #{item.name}, #{item.age})
        </foreach>
    </insert>

  4. 临时表

    减少原始表上的连接和过滤操作，从而提高效率。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TEMPORARY TABLE temp_users
    SELECT *
    FROM users;

    UPDATE users
    SET last_name = 'Doe'
    WHERE user_id IN (SELECT user_id FROM temp_users);

    DROP TEMPORARY TABLE temp_users;

  5. for循环

    1
    2
    3
    4
    5
    <update id="updateUserAge" parameterType="java.util.List">
        <foreach collection="list" item="user" separator=";">
            UPDATE user SET age = #{user.age} WHERE id = #{user.id}
        </foreach>
    </update>

递归

WITH RECURSIVE递归

通过 dept_id 获取 dept_id下的所有dept_id

表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `dept_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门唯一标识',
  `dept_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '部门名称',
  `parent_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '父部门id',
  `is_del` tinyint(1) NULL DEFAULT NULL COMMENT '0未删除；1：已删除',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '部门' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '总部', '-1', 0);
INSERT INTO `dept` VALUES ('2', '北京', '1', 0);
INSERT INTO `dept` VALUES ('3', '天津', '1', 0);
INSERT INTO `dept` VALUES ('4', '昌平区', '2', 0);

SET FOREIGN_KEY_CHECKS = 1;

查询语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
WITH RECURSIVE sys_dept_sub AS (
	SELECT
		dept_id AS rootId 
	FROM
		dept 
	WHERE
		dept_id = #{deptId} UNION ALL
	SELECT
		dept_id 
	FROM
		dept r,
		sys_dept_sub d 
	WHERE
		r.parent_id = d.rootId 
		AND is_del = 0 
	) SELECT
	dept_id 
FROM
	dept 
WHERE
	EXISTS (
	SELECT
		rootId 
	FROM
		sys_dept_sub t 
	WHERE
	rootId = dept_id 
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH RECURSIVE temp AS (
        SELECT
        *
        FROM
        mdm_post
        WHERE
        post_code = #{postCode}
        AND is_del = 0 UNION
        DISTINCT SELECT
        d.*
        FROM
        mdm_post d
        INNER JOIN temp t ON d.parent_code = t.post_code
        AND d.is_del = 0
        ) SELECT DISTINCT
        post_code
        FROM
        temp

local实现递归

  • local函数介绍

    1
    2
    // 如果字符串 string 包含 subStr
    locate(subStr,string) > 0

  • 递归地实现

    1
    表中定义部门路径字段。dept_path

    数据

    dept_path
    集团总部/人力资源
    集团总部/人力资源/财务

    查询

    1
    2
    查询部门a和部门a下的所有部门。
    locate(a.name,dept_path)

某张表卡死

  • 原因：因事务未提交锁表。例如：alter 表字段

  • 解决：

    1. 直接 -杀死未提交事务

      1
      2
      3
      4
      5
      root权限下：
      # 查看未提交事务
      select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;
      # 杀死未提交事务
      kill 989465;

    2. 根本 - 调整锁超时阈值

      1
      2
      3
      # lock_wait_timeout 表示获取metadata lock的超时（单位为秒），允许的值范围为1到31536000（1年）。 默认值为31536000。
      set session lock_wait_timeout = 1800;
      set global lock_wait_timeout = 1800;

逆向工程生成的实体类与表不一致

  • 原因

    1. 逆向工程使用information_schema.COLUMNS，此表在某些情况下会有实际的表不一致。
    2. 不同的库下有相同的表，另一个表中与本表不一致

  • 解决

    1
    2
    3
    4
    # generatorConfig.xml 中配置忽视某列
    <table tableName="bpm_matrix_body">
                <ignoreColumn column="header_code"></ignoreColumn>
    </table>