sql常用特殊操作示例

批量修改

-- 批量修改
update user e,
    (
        -- 拼接需要的字符串
        select c.id, c.new_name
        from (select id,
                     substring_index(name, '_', 1) new_name
              from user
             ) c
    ) a
set name = a.new_name
where e.id = a.id
  -- 判断符合条件的字符串
  and LENGTH(SUBSTRING_INDEX(substring_index(name, '_', 2), '_', - 1)) = 3;

参考：https://dev.mysql.com/doc/refman/5.7/en/update.html

sql添加序号示例

1
2
3

select @orderNo := @orderNo + 1 AS orderNo, u.*
from user u,
     (SELECT @orderNo := 0) n;

添加字段时，对字段是否存在进行校验，即字段存在则不添加

DROP PROCEDURE IF EXISTS pro_AddColumn;
DELIMITER $$
CREATE PROCEDURE pro_AddColumn(
    IN tablename VARCHAR(50),
    IN colname VARCHAR(50),
    IN coltype VARCHAR(20),
    IN colnull VARCHAR(50),
    IN coldesc VARCHAR(50)
)
BEGIN
    -- 此处可针对具体业务字段存在判断进行修改
    if not exists(select 1 from information_schema.`COLUMNS` where table_name=tablename and column_name=colname) then
        set @sqlStr = CONCAT('alter table ', tablename, ' add ', colname, ' ',coltype,' ', colnull, ' COMMENT ',  coldesc);
        prepare sqlStr from @sqlStr;
        execute sqlStr;
    end if;
END $$

DELIMITER
-- 表名, 字段名, 字段类型,是否为null, 注释
CALL pro_AddColumn( 'oh_test_user', 'damage ', 'int(1)', 'null','"当前人员接害：1 是，2 否"');
CALL pro_AddColumn( 'oh_test_user', 'actual_date ', 'datetime', 'null','"实际体检日期"');
CALL pro_AddColumn( 'oh_test_user', 'opinions ', 'varchar(100)', 'null','"体检结论与处理意见"');
CALL pro_AddColumn( 'oh_test_user', 'implementation ', 'varchar(100)', 'null','"落实情况"');
DROP PROCEDURE pro_AddColumn;

删除索引时，对索引是否存在进行校验，即索引存在则不删除，防止保存；也可以在添加索引时，删除存在的该索引

DROP PROCEDURE IF EXISTS del_idx;
DELIMITER $
CREATE PROCEDURE del_idx(IN p_db_name VARCHAR(100),
                         IN p_tablename VARCHAR(100),
                         IN p_idxname VARCHAR(100))
BEGIN
    SET @str = CONCAT(' drop index ', p_idxname, ' on ', p_tablename);
    SELECT COUNT(*)
    INTO @cnt
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = p_db_name
      and table_name = p_tablename
      AND index_name = p_idxname;
    IF @cnt > 0 THEN
        PREPARE stmt FROM @str;
        EXECUTE stmt ;
    end if;
END $
DELIMITER
-- 数据库名称，表名，索引名称；
CALL del_idx('test_mwk','name', 'name_index');
DROP PROCEDURE del_idx;

清除表数据重新插入构建的数据，包含随机生成10位字符的id

-- 清空表数据
truncate table ira_equipment_notify_user_rel;
-- 插入数据
INSERT INTO ira_equipment_notify_user_rel
select (
           -- 构建随机的10位字符串
           SELECT concat(
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97),
                          char(round(rand() * 25) + 97)
                      )) id,
       now()             create_date,
       u.user_id,
       e.id              equipment_id
from ira_equipment e
         left join (
    -- 构建临时默认数据表
    select 'g1pcniqy5a' park_id,
           '0b7e4a40-c' user_id
    union all
    select 'g1pcniqy5a' park_id,
           'g01ah4it7n' user_id
    union all
    select 'g1pcniqy5a' park_id,
           'f9ce6a36-c' user_id
    union all
    select 'g1pcniqy5a' park_id,
           'f92fd3ac-c' user_id
    union all
    select 'g1pd1fwm5n' park_id,
           '0b6bdf89-c' user_id
    union all
    select 'g1pd1fwm5n' park_id,
           'fb0e91e6-c' user_id
    union all
    select 'g1pd1fwm5n' park_id,
           'f92fd3ac-c' user_id
    union all
    select 'g12o5k1osi' park_id,
           '101dfcf0-c' user_id
    union all
    select 'g12o5k1osi' park_id,
           '0dc47e9a-c' user_id
    union all
    select 'g12o5k1osi' park_id,
           'f92fd3ac-c' user_id
) u on u.park_id = e.park_id;

级联关系表，如省市表里有id、name、parent_id，查询出所有叶子节点

SELECT u2.id, u2.name
FROM (SELECT @ids                                 AS p_ids,
             (SELECT @ids := GROUP_CONCAT(id)
              FROM province_city
              WHERE FIND_IN_SET(parent_id, @ids)) AS c_ids,
             @l := @l + 1                         AS LEVEL
      FROM province_city,
           (SELECT @ids := 'g3cbrymald', @l := 0) b #此处为需要传递的父类id.
      WHERE @ids IS NOT NULL) u1
         JOIN province_city u2 ON FIND_IN_SET(u2.id, u1.p_ids)
         AND u2.id != 'g3cbrymald'  #需要包含自己, 则删掉 !=

级联关系表，如省市表里有id、name、parent_id，查询出所有父节点

SELECT u2.id, u2.name
FROM (
         SELECT @id                                                                                               c_ids,
                (SELECT @id := GROUP_CONCAT(parent_id) FROM province_city WHERE FIND_IN_SET(id, @id)) p_ids,
                @l := @l + 1 AS                                                                                   LEVEL
         FROM province_city,
              (SELECT @id := 'g3cbrymald', @l := 0) b
         WHERE @id IS NOT NULL
     ) u1
         JOIN province_city u2 ON u1.c_ids = u2.id

参考：https://blog.csdn.net/qq_42986107/article/details/101113098

批量删除字段，当字段存在时则删除，不存在则不删除；避免删除字段时报错

DROP PROCEDURE IF EXISTS pro_DropColumn;
DELIMITER $$
CREATE PROCEDURE pro_DropColumn(
    IN tablename VARCHAR(50),
    IN colname VARCHAR(50)
)
BEGIN
    -- 此处可针对具体业务字段存在判断进行修改                     指定当前运行的数据库
    if exists(select 1 from information_schema.`COLUMNS` where table_schema = DATABASE() and table_name=tablename and column_name=colname) then
        set @sqlStr = CONCAT('alter table ', tablename, ' drop column ', colname, ';');
        prepare sqlStr from @sqlStr;
        execute sqlStr;
    end if;
END $$

DELIMITER
-- 表名, 字段名
CALL pro_DropColumn( 'ist_header_category', 'int_value1');
CALL pro_DropColumn( 'ist_header_category', 'int_value2');
CALL pro_DropColumn( 'ist_header_category', 'date_value1');
CALL pro_DropColumn( 'ist_header_category', 'date_value2');
CALL pro_DropColumn( 'ist_header_category', 'double_value1');
CALL pro_DropColumn( 'ist_header_category', 'double_value2');
DROP PROCEDURE pro_DropColumn;