Mysql修改数据库字符集

1.修改mysql 配置文件， 在my.cnf文件中增加character-set-server=utf8mb4 collation-server=utf8mb4_general_ci，然后重启mysql

2. 修改数据库、表、字段的字符集和collation

-- 首先，查询当前数据库中所有表的名称和字符集 
SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name'; 
-- 然后，使用ALTER TABLE语句来修改每个表的字符集和字符串字符集 
-- 例如，将名为your_table_name的表的字符集和字符串字符集修改为utf8mb4 
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS=0;

//To Change database collation
ALTER DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


//To change table collation:
SELECT CONCAT('ALTER TABLE `',  table_name, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'jiradb'
AND
(
    C.CHARACTER_SET_NAME != 'utf8mb4'
    OR
    C.COLLATION_NAME != 'utf8mb4_general_ci'
);
//上面语句生成的是修改表编码的SQL语句， 批量拷贝这些语句再执行


//To change column collation for varchar columns:
SELECT CONCAT('ALTER TABLE `', 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), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'jiradb'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_general_ci'
);
//上面语句生成的是修改表中数据编码的SQL语句， 批量拷贝这些语句再执行



//To change column collation for non-varchar columns:
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'jiradb'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_general_ci'
);


SET FOREIGN_KEY_CHECKS=1;