1.批量修改表字符集
SELECT
CONCAT( 'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' )
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'test'
AND TABLE_TYPE = 'BASE TABLE';
会生成一串sql 语句
全选复制到sql 窗口执行
2 修改表字段字符集
SELECT
CONCAT('ALTER TABLE `',TABLE_NAME,'` CHANGE `',COLUMN_NAME,'` `',COLUMN_NAME,'` VARCHAR(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
CASE WHEN COLUMN_COMMENT IS NULL OR COLUMN_COMMENT='' THEN ' ' ELSE CONCAT(' COMMENT ',"'",COLUMN_COMMENT,"'") END,';')
FROM
INFORMATION_SCHEMA.COLUMNS t
WHERE
1 = 1
AND table_schema = 'test'
AND data_type = 'varchar' ;
同样会出现一堆sql,执行sql 再看看数据表字符集吧
一、查看MySQL数据库服务器和数据库MySQL字符集。
show variables like '%char%';
二、查看MySQL数据表(table)的MySQL字符集。
show table status from test ;
三、查看MySQL数据列(column)的MySQL字符集。
show full columns from sys.sys_config;