先用下面的语句生成脚本,再运行脚本
1. 某个schema中所有表字段为varchar类型的字符集使用UTF8,排序规则使用utf8_general_ci
SELECT
CONCAT(
'ALTER TABLE `',
TABLE_NAME,
'` MODIFY `',
COLUMN_NAME,
'` ',
DATA_TYPE,
'(',
CHARACTER_MAXIMUM_LENGTH,
') CHARACTER SET UTF8 COLLATE utf8_general_ci',
(
CASE
WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL'
ELSE
''
END
),
';'
)
FROM
information_schema. COLUMNS
WHERE
TABLE_SCHEMA = 'table_name'
AND DATA_TYPE = 'varchar'
AND (
CHARACTER_SET_NAME != 'utf8'
OR COLLATION_NAME != 'utf8_general_ci'
);
2. 某个schema中所有表的字符集使用UTF8,排序规则使用utf8_general_ci
SELECT
CONCAT(
'ALTER TABLE ',
TABLE_NAME,
' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
)
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA = 'schema_name';
转载于:https://blog.51cto.com/lpsqf/1768903