字符集级别
MySQL中默认字符集的设置有四级:
- 服务器级
- 数据库级
- 表级
- 字段级
前三种均为默认设置，并不代表字段最终会使用这个字符集设置。
查询当前字符集
- 查看数据库编码：
SHOW CREATE DATABASE db_name;
- 查看表编码：
SHOW CREATE TABLE table_name;
- 查看字段编码：
SHOW FULL COLUMNS FROM table_name;
-- 或
SHOW FULL FIELDS FROM table_name;
修改字符集
- 修改数据库字符集：
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
- 修改表默认的字符集和所有字符列的字符集（CHAR,VARCHAR,TEXT）
ALTER TABLE table_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
-- 示例：
ALTER TABLE cmpt_test CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
- 只是修改表的默认字符集：
ALTER TABLE table_name DEFAULT CHARACTER SET character_name [COLLATE...];
-- 示例：
ALTER TABLE cmpt_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- 修改字段的字符集：
ALTER TABLE table_name CHANGE f_name f_name CHARACTER SET character_name [COLLATE ...];
-- 示例：
ALTER TABLE cmpt_test CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
组装SQL
- 根据字段拼接修改字符集语句
SELECT DISTINCT
CONCAT( "ALTER TABLE ", table_schema, ".", table_name, " CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" ) AS updateSql
FROM
information_schema.COLUMNS
WHERE
table_schema = 'db_name'
AND COLLATION_NAME <> 'utf8mb4_unicode_ci';
- 根据表拼接修改字符集语句
SELECT
CONCAT( "ALTER TABLE ", table_schema, ".", table_name, " CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" ) AS updateSql
FROM
information_schema.TABLES
WHERE
table_schema = 'db_name'
AND TABLE_COLLATION <> 'utf8mb4_unicode_ci';
