mysql字符集与校对规则

末蓝、 2024-04-17 14:16 76阅读 0赞

**mysql字符集与校对规则**

mysql的字符集和校对规则有四个级别的默认设置：服务器级，数据库级，表级和字段级。他们分别在不同的地方设置，作用也不同。  
查看字符集级对应的校对规则。  
Charset：字符集，Default collation：默认使用的校对规则。Maxlen：字符集的最大长度

可通过命令查看字符集对应的可用的校对规则。

mysql> show collation like 'gbk%';
    +----------------+---------+----+---------+----------+---------+---------------+
    | Collation      | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
    +----------------+---------+----+---------+----------+---------+---------------+
    | gbk_bin        | gbk     | 87 |         | Yes      |       1 | PAD SPACE     |
    | gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 | PAD SPACE     |
    +----------------+---------+----+---------+----------+---------+---------------+
    2 rows in set (0.00 sec)

校对规则命名约定：相关的字符集名\_语言名\_ci(大小写不敏感)或\_cs(大小写敏感)，或字符集名\_bin(二元，即比较是基于字符编码的值，所以和语言无关，命名中无语言标识)。  
如一下utf8对应的校对规则

mysql> show collation like 'utf8\_%';
    +--------------------------+---------+-----+---------+----------+---------+---------------+
    | Collation                | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
    +--------------------------+---------+-----+---------+----------+---------+---------------+
    | utf8_bin                 | utf8    |  83 |         | Yes      |       1 | PAD SPACE     |
    | utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 | PAD SPACE     |
    | utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 | PAD SPACE     |
    | utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 | PAD SPACE     |
    | utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 | PAD SPACE     |
    | utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 | PAD SPACE     |
    | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 | PAD SPACE     |
    | utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 | PAD SPACE     |
    | utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 | PAD SPACE     |
    | utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 | PAD SPACE     |
    | utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 | PAD SPACE     |
    | utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 | PAD SPACE     |
    | utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 | PAD SPACE     |
    | utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 | PAD SPACE     |
    | utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 | PAD SPACE     |
    | utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 | PAD SPACE     |
    | utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 | PAD SPACE     |
    | utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 | PAD SPACE     |
    | utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 | PAD SPACE     |
    | utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 | PAD SPACE     |
    | utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 | PAD SPACE     |
    | utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 | PAD SPACE     |
    | utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 | PAD SPACE     |
    | utf8_tolower_ci          | utf8    |  76 |         | Yes      |       1 | PAD SPACE     |
    | utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 | PAD SPACE     |
    | utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 | PAD SPACE     |
    | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 | PAD SPACE     |
    | utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 | PAD SPACE     |
    +--------------------------+---------+-----+---------+----------+---------+---------------+
    28 rows in set (0.00 sec)

一，服务器级字符集和校对规则  
在mysql服务启动时确定，默认使用latin1作为服务器级字符集。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzg5NzY0_size_16_color_FFFFFF_t_70]  
可使用如下命令查看当前server的字符集和校对规则。

二，数据库级字符集和校对规则  
mysql数据库，默认的字符集为：armscii8，默认校对规则为：armscii8\_general\_ci。

mysql> show variables like
        -> 'character_set_database';
    +------------------------+----------+
    | Variable_name          | Value    |
    +------------------------+----------+
    | character_set_database | armscii8 |
    +------------------------+----------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> show variables like 'collation_database';
    +--------------------+---------------------+
    | Variable_name      | Value               |
    +--------------------+---------------------+
    | collation_database | armscii8_general_ci |
    +--------------------+---------------------+
    1 row in set, 1 warning (0.00 sec)

三，表级字符集和校对规则  
可用如下命令查看表级字符集和校对规则。表级字符集和校对规则可在建表时显式指定。

mysql> show create table tcc \G;
    *************************** 1. row ***************************
           Table: tcc
    Create Table: CREATE TABLE `tcc` (
      `charset_desc` char(8) COLLATE utf8_bin DEFAULT NULL,
      `collate_desc` char(8) COLLATE utf8_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    1 row in set (0.00 sec)

四，列字符集和校对规则  
使用频率较少。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzg5NzY0_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/85288daf0d1141d1a78b0572a54b30fc.png