MySQL不同字符集之间的区别和选择

MySQL支持多种字符集，每种字符集有其特定的用途和适应场景。主要的字符集包括：

常见字符集

1. UTF-8

• 用途： 支持全球大多数语言的字符，是最通用的字符集之一。
• 特点： 每个字符占用1到4个字节，可存储各种语言的文本。

2. UTF-16

• 用途： 用于存储Unicode字符，每个字符占用2或4个字节。
• 特点： 在存储大量亚洲语言字符时比UTF-8更有效率。

3. GBK（GB2312的扩展）

• 用途： 主要用于简体中文，每个字符占用2个字节。
• 特点： 不支持其他语言，不适合多语言应用。

4. Latin1

• 用途： 适用于西欧语言，每个字符占用1个字节。
• 特点： 不支持亚洲语言。

选择字符集的考虑因素

在选择字符集时，应考虑以下因素：

• 语言支持： 选择支持你应用中所用语言的字符集。
• 存储空间： 不同字符集的存储空间占用不同，UTF-8相对较节省空间。
• 性能： 一些字符集在特定应用场景下可能更高效。
• 排序和比较： 不同字符集可能导致不同的排序和比较结果。

排序规则（Collation）

什么是排序规则

排序规则定义了字符集中的字符如何进行比较和排序。不同的排序规则会影响数据的排序和查询结果。选择合适的排序规则非常重要，因为它直接影响数据库的性能和用户体验。

常见排序规则

• utf8_general_ci：

  • 特点： 大小写不敏感的通用排序规则，适用于大多数场景。
  • 优点： 性能较好，适合不需要严格区分大小写的应用。
  • 缺点： 在某些语言中，排序结果可能不符合预期。

• utf8mb4_unicode_ci：

  • 特点： 基于Unicode标准的排序规则，支持更多特殊字符。
  • 优点： 提供更准确的排序和比较结果，适用于多语言应用。
  • 缺点： 性能略低于utf8_general_ci。

• utf8mb4_bin：

  • 特点： 二进制排序规则，严格区分大小写和重音符号。
  • 优点： 提供最精确的比较结果。
  • 缺点： 性能较低，适用于对大小写和特殊字符敏感的应用。

示例

在创建数据库或表时，你可以指定字符集和排序规则。例如：

查看SQL示例

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这会创建一个使用UTF-8字符集和utf8mb4_unicode_ci排序规则的数据库。确保选择适合你应用需求的字符集和排序规则。

检查和修改字符集与排序规则

查看当前MySQL实例中不符合字符集和排序规则规范的库名

查看SQL示例

SELECT
SCHEMA_NAME '数据库',
DEFAULT_CHARACTER_SET_NAME '库字符集',
DEFAULT_COLLATION_NAME '库排序规则'
FROM
information_schema.SCHEMATA
WHERE
(DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
AND SCHEMA_NAME NOT IN ( 'sys', 'mysql', 'performance_schema', 'information_schema' );

查看当前MySQL实例中不符合排序规范的表

查看SQL示例

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '表排序规则',TABLE_ROWS '表行数' 
FROM information_schema.TABLES 
WHERE TABLE_COLLATION != 'utf8mb4_unicode_ci' 
AND TABLE_SCHEMA NOT IN ('sys','mysql','performance_schema','information_schema');

修改数据库的字符集和排序规则

在修改表及表字段的字符集和排序规则之前，需要先修改库的字符集和排序规则：

查看SQL示例

SELECT
SCHEMA_NAME '数据库',
DEFAULT_CHARACTER_SET_NAME '库字符集',
DEFAULT_COLLATION_NAME '库排序规则',
CONCAT( 'ALTER DATABASE ', SCHEMA_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) '修正库规则SQL'
FROM
information_schema.SCHEMATA
WHERE
(DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
AND SCHEMA_NAME NOT IN ( 'sys', 'mysql', 'performance_schema', 'information_schema' );

修改表及表字段的字符集和排序规则

查看SQL示例

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '排序规则',
CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') '修正表规则SQL' 
FROM information_schema.TABLES 
WHERE TABLE_COLLATION != 'utf8mb4_unicode_ci' 
AND TABLE_SCHEMA NOT IN ('sys','mysql','performance_schema','information_schema');

通过使用上述SQL语句，可以有效地检查并修正数据库和表的字符集和排序规则，确保数据的一致性和正确性。

---