mysql字符排序规则utf8mb4_0900_ai_ci修改为utf8_general_ci_表统一成数据库的排序编码 utf8mb4_general_ci

0、【一定注意】

表不能有外键 ，可以有索引

1、 查看排序规则

show variables where Variable_name like '%collation%';
1

2、 数据库修改

alter database  库名 CHARACTER set = utf8mb4 COLLATE = utf8mb4_general_ci;
1

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';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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' #要修改的数据库名称
1
2
3
4
5
6
7
8