【MySQL】全库调整表大小写语句

统一修改字段成小写+下划线的命名规则：

 

V1上线后，重新看SQL调整的较可行的写法：

# = = = = = = = = = = = = = = = 统一更改全库所有字段大小写脚本SQL(会删除字段原来的字符集和排序规则) = = = = = = = = = = = = = = = = = = = = 
SELECT
    CONCAT(
        'ALTER TABLE `', `TABLE_SCHEMA`, '`.`', `TABLE_NAME`, '` CHANGE COLUMN `', 
        `COLUMN_NAME`, '` `', LOWER(`COLUMN_NAME`) , '` ',   -- 调整小写或者大写 UPPER(`COLUMN_NAME`)
        `COLUMN_TYPE`, 
        IF(`IS_NULLABLE` = 'NO', ' NOT NULL', ' NULL'),  -- 保持原字段 是否非空设定
        IF(`EXTRA` IS NOT NULL, ' AUTO_INCREMENT', ''),  -- 保持主键自带自增属性
        IF(`COLUMN_DEFAULT` IS NULL, '',  CONCAT('DEFAULT \'', `COLUMN_DEFAULT`, '\'')),  -- 保持字段默认值属性
        ' COMMENT \'',  `COLUMN_COMMENT`, '\';' -- 保持字段注释信息，如果无则是空字符串
    ) AS '统一更改字段大小写脚本SQL'
FROM 
    `information_schema`.`COLUMNS`
WHERE 
    `TABLE_SCHEMA` NOT IN('mysql', 'information_schema', 'performance_schema', 'sys') -- 非MySQL库

如果要对字段统一设置字符集&排序规则：

# = = = = = = = = = = = = = = = 统一更改字段字符集脚本SQL(utf8mb4_general_ci) = = = = = = = = = = = = = = = = = = = = 
SELECT
    CONCAT(
        'ALTER TABLE `', `TABLE_SCHEMA`, '`.`', `TABLE_NAME`, 
        '` MODIFY COLUMN `', `COLUMN_NAME`, '` ', `COLUMN_TYPE`, 
        ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',  -- 设定字符集
        IF(`IS_NULLABLE` = 'NO', ' NOT NULL', ' NULL'), 
        IF(`COLUMN_DEFAULT` IS NULL, '', CONCAT(' DEFAULT \'', `COLUMN_DEFAULT`, '\'')), 
        ' COMMENT \'', `COLUMN_COMMENT`,'\';' -- 保持注释
    ) AS '统一更改字段字符集脚本SQL'
FROM 
    `information_schema`.`COLUMNS`
WHERE 
    `TABLE_SCHEMA` NOT IN('mysql', 'information_schema', 'performance_schema', 'sys') -- 非MySQL库
    AND `DATA_TYPE` IN ('varchar', 'char', 'tinytext', 'text', 'mediumtext', 'longtext') -- 指定文本类型才具有字符集和排序规则属性设定
    AND `COLUMN_KEY` != 'PRI'; -- 不干涉主键