查实例字符集

show variables like '%character%';

查实例排序规则

show variables like '%collation%';

查库语句

show create database test;

查表排序规则

show table status from test like 'test_saas_single';

查字段排序规则

show full columns from test_saas_single;

修改数据表

SELECT
TABLE_SCHEMA '数据库',
TABLE_NAME '表',
TABLE_COLLATION '原排序规则',
CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' COLLATE=utf8mb4_bin;' ) '修正SQL'
FROM
information_schema.TABLES
WHERE
TABLE_COLLATION RLIKE 'utf8' AND TABLE_SCHEMA = 'test' AND TABLE_COLLATION != 'utf8mb4_general_ci';

show create database test;
show table status from test like 'zibiaob';

修改数据库

SELECT
SCHEMA_NAME '数据库',
DEFAULT_CHARACTER_SET_NAME '原字符集',
DEFAULT_COLLATION_NAME '原排序规则',
CONCAT( 'ALTER DATABASE ', SCHEMA_NAME, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.SCHEMATA
WHERE
DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8' AND SCHEMA_NAME = 'test';

show variables like '%collation%';

ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改数据字段

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, ' COMMENT ', ''', COLUMN_COMMENT, '',', ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.COLUMNS
WHERE
COLLATION_NAME RLIKE 'utf8' AND TABLE_SCHEMA = 'test' AND COLLATION_NAME != 'utf8mb4_general_ci' AND TABLE_NAME = 'test_aaa';