MySQL修改字符集

字符集级别

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