一、改字段
SELECT
CONCAT(
'ALTER TABLE `',
TABLE_NAME,
'` MODIFY `',
COLUMN_NAME,
'` ',
DATA_TYPE,
'(',
CHARACTER_MAXIMUM_LENGTH,
') CHARACTER SET utf8mb4',
(
CASE
WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL'
ELSE
''
END
),
';'
)
FROM
information_schema. COLUMNS
WHERE
TABLE_SCHEMA = 'dataBaseName'
AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'char')
二、改表
SELECT
CONCAT(
'ALTER TABLE ',
TABLE_NAME,
' CONVERT TO CHARACTER SET utf8mb4;'
)
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA = 'dataBaseName';
将红色部分替换为自己数据名名和需要改成的字符集,以上两步分别执行,将执行得到的结果复制,再执行一遍。