修改数据库实例、修改数据库、修改数据表、修改数据，编码、排序规则
查实例字符集
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';