#字段修改
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, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.`COLUMNS`
WHERE
COLLATION_NAME LIKE '%_ai_ci'
AND TABLE_SCHEMA like '%xg_chengan%';
#表修改
SELECT
TABLE_SCHEMA '数据库',
TABLE_NAME '表',
TABLE_COLLATION '原排序规则',
CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.`TABLES`
WHERE
TABLE_COLLATION RLIKE 'utf8';
#修改数据库
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';
mysql 数据库 修改编码的脚本生成sql
最新推荐文章于 2023-09-16 16:13:24 发布