0、【一定注意】
表不能有外键 ,可以有索引
1、 查看排序规则
show variables where Variable_name like '%collation%';
2、 数据库修改
alter database 库名 CHARACTER set = utf8mb4 COLLATE = utf8mb4_general_ci;
3、 字段修改
SELECT
TABLE_SCHEMA '数据库',
TABLE_NAME '表',
COLUMN_NAME '字段',
CHARACTER_SET_NAME '原字符集',
COLLATION_NAME '原排序规则',
CONCAT(
'ALTER TABLE ',
TABLE_SCHEMA,
'.',
'`',
table_name,
'` MODIFY `',
column_name,
'` ',
DATA_TYPE,
'(',
CHARACTER_MAXIMUM_LENGTH,
') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
( CASE WHEN IFNULL( column_comment, '' )= '' THEN '' ELSE concat( ' COMMENT \'', column_comment, '\'' ) END ),
';'
) AS `修正sql`
FROM
information_schema.COLUMNS
WHERE
1 = 1
AND TABLE_SCHEMA = 'studay' #要修改的数据库名称
AND DATA_TYPE = 'varchar' #只修改字符数据
AND COLLATION_NAME RLIKE 'utf8'; #原排序规则or COLLATION_NAME = 'utf8mb4_0900_ai_ci';
4、 表修改
SELECT
TABLE_SCHEMA '数据库',
TABLE_NAME '表',
CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) AS `修正sql`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'studay' #要修改的数据库名称