mysql字符排序规则utf8mb4_0900_ai_ci修改为utf8_general_ci

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' #要修改的数据库名称