7

MySQL - 字符集简介

 9 months ago
source link: https://chanjarster.github.io/post/mysql/charset-intro/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
2023-11-29 3 分钟阅读 mysql

数据库字符集，是一个经常被人忽略的东西，但配置不正确又容易发生意想不到的bug。

事情的起因是有一个字符串字段上加了 Unique 索引，在插入 foo 和 Foo 两条记录的时候被报告违反唯一索引。

简介字符集和排序规则

在 MySQL 中，字符集（Charset）不是孤立使用的，它常常搭配字符集排序规则（Collation）一起存在。

字符集大家都知道，而排序规则则比较陌生，实际上排序规则定义了 MySQL 在比较字符串时的行为，比大小、判断是否相等都会影响到。

你可以通过下列命令查看支持的字符集，可以看到每个字符集有默认的排序规则：

-- 查看 MySQL 支持的字符集
SHOW CHARACTER SET LIKE 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
| utf8mb3 | UTF-8 Unicode    | utf8mb3_general_ci |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_0900_ai_ci |      4 |
+---------+------------------+--------------------+--------+

以及支持的排序规则：

-- 查看 MySQL 支持的字符集排序规则
SHOW COLLATION WHERE Charset = 'utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
...
| utf8mb4_general_ci         | utf8mb4 |  45 |         | Yes      |       1 | PAD SPACE     |
...

在使用的时候也是配合使用的，比如定义表的字符集和排序规则：

CREATE TABLE FOO
(
    NAME VARCHAR(512)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

也可以不指定排序规则，那么就用字符集默认的排序规则； 也可以字符集都不指定，使用 Databse 默认的字符集和排序规则。

支持的字符集和排序规则

MySQL 支持的字符集和排序规则有这么几大类：

排序规则的解读

排序规则有三个部分组成：<charset>_<lang>_<suffix>，下面以 utf8mb4_general_ci 为例说明：

  • utf8mb4 代表字符集
  • general 代表语言，可以是 locale 代码或者语言名称，general 代表通用
  • _ci 后缀代表大小写不敏感（case-insensitive），后缀可以有多个，比如 _ai_ci

后缀详细表格在这里：

Suffix 含义
_ai Accent-insensitive
_as Accent-sensitive
_ci Case-insensitive
_cs Case-sensitive
_ks Kana-sensitive，专供日语
_bin Binary，字符的二进制码排序

注意：如果没有 _ai 和 _as，那么：

  • _ci 暗含 _ai
  • _cs 暗含 _as

文章的开头提到 foo 和 Foo 两个字符串值触发了唯一索引约束，是因为表使用了 utf8mb4 字符集和 utf8mb4_general_ci 排序规则。 _ci 后缀代表大小写不敏感，所以触发了唯一约束。 其实字符串匹配条件也是大小写不敏感的，WHERE col='Foo' 和 WHERE col='foo' 都能查询出数据。

关于 _bin 二进制排序规则有另外要注意的地方，见文档。

更多内容参阅 文档。

指定字符集和排序规则

指定服务器 参考：

mysqld
mysqld --character-set-server=utf8mb4
mysqld --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_0900_ai_ci

指定 Database 参考：

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

指定表格 参考：

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

指定字段 参考：

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

指定 SELECT 'string' 字符串，不常用，见 文档。

还有其他用法，自行参阅文档。

字符集变换

修改一个字段的字符集也有一些坑，见文档。

指定连接的字符集和排序规则

见文档。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK