MySQL批量修改表名(字段名)为小写

批量改表名为小写

SELECT
    concat( 'alter table ', TABLE_NAME, ' rename to ', LOWER( TABLE_NAME ), ';' ) AS '修改脚本'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = 'canary';

批量改表字段为小写

SELECT
    concat( 'alter table ', TABLE_NAME, ' change column ', COLUMN_NAME, ' ', LOWER( COLUMN_NAME ),
        ' ', COLUMN_TYPE, ' comment ''', COLUMN_COMMENT, ''';' ) AS '修改脚本'
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = 'canary';

批量修改表字符集

SELECT
	CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' )
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'canary';

批量修改表字段字符集

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集', COLLATION_NAME '原排序规则',
       CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'canary';