Comprendre les paramètres du jeu de caractères MySQL en 5 minutes-tutoriel mysql-php.cn

Cet article présente principalement les concepts et les connexions du jeu de caractères MySQL et de l'ordre des caractères, et partage avec vous différentes façons d'afficher les jeux de caractères pris en charge par MYSQL. Veuillez vous référer à cet article pour plus de détails, j'espère qu'il pourra vous aider.

1. Aperçu du contenu

Dans le processus d'utilisation de MySQL, il est très important de comprendre les concepts de jeu de caractères et d'ordre des caractères, ainsi que l'impact de différents paramètres sur le stockage et la comparaison des données. Le problème « tronqué » que de nombreux étudiants rencontrent dans leur travail quotidien est très probablement dû à une compréhension inadéquate des jeux de caractères et des séquences de caractères, ou à des paramètres incorrects.

Cet article présente les contenus suivants, du moins profond au plus profond :

Concepts de base et connexions des jeux de caractères et des séquences de caractères
Niveaux de configuration des jeux de caractères et de l'ordre des caractères pris en charge par MySQL, et relation entre chaque niveau de configuration
Serveur, base de données, table, jeu de caractères au niveau des colonnes et ordre des caractères Afficher et définir
Quand devez-vous définir le jeu de caractères et la séquence de caractères

2. Jeu de caractères ? et séquence de caractères Concepts et connexions

En termes de stockage de données, MySQL fournit une prise en charge de différents jeux de caractères. Pour les opérations de comparaison de données, une prise en charge de différents ordres de caractères est fournie.

MySQL fournit différents niveaux de paramètres, notamment le niveau du serveur, le niveau de la base de données, le niveau de la table et le niveau des colonnes, qui peuvent fournir des paramètres très précis.

Qu'est-ce qu'un jeu de caractères et un ordre des caractères ? Pour faire simple :

Jeu de caractères (character set) : définit les caractères et l'encodage des caractères.
Ordre des caractères (collation) : définit les règles de comparaison des caractères.

Par exemple :

a quatre caractères : A, B, a, b Les codes de ces quatre caractères sont respectivement A = 0, B = 1. une = 2, b = 3. Les caractères + encodage constituent ici le jeu de caractères.

Et si on veut comparer la taille de deux caractères ? Par exemple, A, B ou a, b, la manière la plus intuitive de comparer est d'utiliser leur codage, par exemple, car 0 <

De plus, pour A et a, bien qu'ils aient des encodages différents, nous pensons que les caractères majuscules et minuscules doivent être égaux, c'est-à-dire A == a.

Deux règles de comparaison sont définies ci-dessus, et l'ensemble de ces règles de comparaison est le classement.

S'ils sont à la fois des caractères majuscules et minuscules, comparez leurs tailles d'encodage
Si les deux caractères ont une relation de casse, alors ils sont égaux ; .

3. Jeux de caractères et séquences de caractères pris en charge par MySQL

MySQL prend en charge plusieurs jeux de caractères et séquences de caractères.

Un jeu de caractères correspond à au moins une séquence de caractères (généralement 1 à plusieurs).
Deux jeux de caractères différents ne peuvent pas avoir la même séquence de caractères.
Chaque jeu de caractères a un ordre de caractères par défaut.

Ce qui précède est relativement abstrait. Regardons les sections suivantes pour comprendre ce qui se passe.

1. Vérifiez les jeux de caractères pris en charge

Vous pouvez vérifier les jeux de caractères pris en charge par MYSQL via les méthodes suivantes.

Méthode 1 :

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European  | dec8_swedish_ci | 1 |
...省略</p>
<p>Méthode 2 : </p>
<pre class="brush:php;toolbar:false">mysql> use information_schema;
mysql> select * from CHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5  | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8  | dec8_swedish_ci | DEC West European  | 1 |
...省略

Copier après la connexion

Lors de la visualisation à l'aide de SHOW CHARACTER SET, vous pouvez également ajouter des conditions de qualification WHERE ou LIKE.

Exemple 1 : Utilisation des conditions de qualification WHERE.

mysql> SHOW CHARACTER SET WHERE Charset="utf8";
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)

Copier après la connexion

Exemple 2 : Utiliser la qualification LIKE.

mysql> SHOW CHARACTER SET LIKE "utf8%";
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)

Copier après la connexion

2. Vérifiez l'ordre des caractères pris en charge

De même, vous pouvez vérifier l'ordre des caractères pris en charge par MYSQL de la manière suivante.

Méthode 1 : Vérifiez via SHOW COLLATION.

Comme vous pouvez le constater, le jeu de caractères utf8 comporte plus de 10 séquences de caractères. Déterminez si la séquence de caractères est la séquence par défaut selon que la valeur par défaut est Oui.

mysql> SHOW COLLATION WHERE Charset = 'utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation  | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci  | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin   | utf8 | 83 |  | Yes | 1 |
...略

Copier après la connexion

Méthode 2 : requête information_schema.COLLATIONS.

mysql> USE information_schema;
mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci  | utf8  | 33 | Yes | Yes  | 1 |
| utf8_bin   | utf8  | 83 |  | Yes  | 1 |
| utf8_unicode_ci  | utf8  | 192 |  | Yes  | 8 |

Copier après la connexion

3. Spécification du nom de la séquence de caractères

Le nom de la séquence de caractères est préfixé par son jeu de caractères correspondant, comme indiqué ci-dessous. Par exemple, la séquence de caractères utf8_general_ci indique qu'il s'agit de la séquence de caractères du jeu de caractères utf8.

Pour plus de règles, veuillez vous référer à la documentation officielle.

MariaDB [information_schema]> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8" limit 2; 
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+-----------------+
| utf8  | utf8_general_ci |
| utf8  | utf8_bin |
+--------------------+-----------------+
2 rows in set (0.00 sec)

Copier après la connexion

4. Jeu de caractères et séquence de caractères du serveur

Objectif : Lorsque vous créez une base de données et ne spécifiez pas le jeu de caractères et la séquence de caractères, le jeu de caractères du serveur et le serveur caractères L'ordre sera utilisé comme jeu de caractères et classement par défaut de la base de données.

Comment spécifier : lorsque le service MySQL est démarré, il peut être spécifié via les paramètres de ligne de commande. Il peut également être spécifié via des variables dans le fichier de configuration.

Jeu de caractères et séquence de caractères par défaut du serveur : spécifiés via les paramètres de compilation lors de la compilation de MySQL.

character_set_server et collation_server correspondent respectivement au jeu de caractères du serveur et à la séquence de caractères du serveur.

1. Vérifiez le jeu de caractères du serveur et la séquence de caractères

correspondant respectivement aux deux variables système Character_set_server et collation_server.

mysql> SHOW VARIABLES LIKE "character_set_server";
mysql> SHOW VARIABLES LIKE "collation_server";

Copier après la connexion

2. Spécifiez

lors du démarrage du service Vous pouvez spécifier le jeu de caractères du serveur et la séquence de caractères lors du démarrage du service MySQL. Si elles ne sont pas spécifiées, les séquences de caractères par défaut sont latin1, latin1_swedish_ci

mysqld --character-set-server=latin1 \
 --collation-server=latin1_swedish_ci

Copier après la connexion

Spécifiez le jeu de caractères du serveur séparément. À l'heure actuelle, la séquence de caractères du serveur est la séquence de caractères par défaut de latin1, latin1_swedish_ci.

mysqld --character-set-server=latin1

Copier après la connexion

3. Spécification du fichier de configuration

En plus de spécifier les paramètres de ligne de commande, il peut également être spécifié dans le fichier de configuration, comme indiqué ci-dessous.

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Copier après la connexion

4. Modification du runtime

Exemple : modification du runtime (elle deviendra invalide après le redémarrage. Si vous souhaitez qu'elle reste inchangée après le redémarrage, vous devez l'écrire dans le fichier de configuration )

mysql> SET character_set_server = utf8 ;

Copier après la connexion

5、编译时指定默认字符集、字符序

character_set_server、collation_server的默认值，可以在MySQL编译时，通过编译选项指定：

cmake . -DDEFAULT_CHARSET=latin1 \
  -DDEFAULT_COLLATION=latin1_german1_ci

Copier après la connexion

五、database的字符集、字符序

用途：指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库，可以分别指定不同的字符集/字符序。

1、设置数据的字符集/字符序

可以在创建、修改数据库的时候，通过CHARACTER SET、COLLATE指定数据库的字符集、排序规则。

创建数据库：

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

Copier après la connexion

修改数据库：

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

Copier après la connexion

例子：创建数据库test_schema，字符集设置为utf8，此时默认的排序规则为utf8_general_ci。

CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;

Copier après la connexion

2、查看数据库的字符集/字符序

有3种方式可以查看数据库的字符集/字符序。

例子一：查看test_schema的字符集、排序规则。（需要切换默认数据库）

mysql> use test_schema;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8   | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)

Copier après la connexion

例子二：也可以通过下面命令查看test_schema的字符集、数据库（不需要切换默认数据库）

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name="test_schema";
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| test_schema | utf8   | utf8_general_ci |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

Copier après la connexion

例子三：也可以通过查看创建数据库的语句，来查看字符集。

mysql> SHOW CREATE DATABASE test_schema;
+-------------+----------------------------------------------------------------------+
| Database | Create Database       |
+-------------+----------------------------------------------------------------------+
| test_schema | CREATE DATABASE `test_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Copier après la connexion

3、database字符集、字符序是怎么确定的

创建数据库时，指定了CHARACTER SET或COLLATE，则以对应的字符集、排序规则为准。
创建数据库时，如果没有指定字符集、排序规则，则以character_set_server、collation_server为准。

六、table的字符集、字符序

创建表、修改表的语法如下，可通过CHARACTER SET、COLLATE设置字符集、字符序。

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]

Copier après la connexion

1、创建table并指定字符集/字符序

例子如下，指定字符集为utf8，字符序则采用默认的。

CREATE TABLE `test_schema`.`test_table` (
 `id` INT NOT NULL COMMENT '',
 PRIMARY KEY (`id`) COMMENT '')
DEFAULT CHARACTER SET = utf8;

Copier après la connexion

2、查看table的字符集/字符序

同样，有3种方式可以查看table的字符集/字符序。

方式一：通过SHOW TABLE STATUS查看table状态，注意Collation为utf8_general_ci，对应的字符集为utf8。

MariaDB [blog]> SHOW TABLE STATUS FROM test_schema \G;
*************************** 1. row ***************************
  Name: test_table
  Engine: InnoDB
 Version: 10
 Row_format: Compact
  Rows: 0
 Avg_row_length: 0
 Data_length: 16384
Max_data_length: 0
 Index_length: 0
 Data_free: 11534336
 Auto_increment: NULL
 Create_time: 2018-01-09 16:10:42
 Update_time: NULL
 Check_time: NULL
 Collation: utf8_general_ci
 Checksum: NULL
 Create_options: 
 Comment: 
1 row in set (0.00 sec)

Copier après la connexion

方式二：查看information_schema.TABLES的信息。

mysql> USE test_schema;
mysql> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";
+-----------------+
| TABLE_COLLATION |
+-----------------+
| utf8_general_ci |
+-----------------+

Copier après la connexion

方式三：通过SHOW CREATE TABLE确认。

mysql> SHOW CREATE TABLE test_table;
+------------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table             |
+------------+----------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
 `id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Copier après la connexion

3、table字符集、字符序如何确定

假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name。如果创建table时：

明确了charset_name、collation_name，则采用charset_name、collation_name。
只明确了charset_name，但collation_name未明确，则字符集采用charset_name，字符序采用charset_name对应的默认字符序。

只明确了collation_name，但charset_name未明确，则字符序采用collation_name，字符集采用collation_name关联的字符集。

charset_name、collation_name均未明确，则采用数据库的字符集、字符序设置。

七、column的字符集、排序

类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，语法如下：

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

Copier après la connexion

1、新增column并指定字符集/排序规则

例子如下：（创建table类似）

mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;

Copier après la connexion

2、查看column的字符集/字符序

例子如下：

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+-----------------+
| utf8  | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

Copier après la connexion

3、column字符集/排序规则确定

假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name：

如果charset_name、collation_name均明确，则字符集、字符序以charset_name、collation_name为准。

只明确了charset_name，collation_name未明确，则字符集为charset_name，字符序为charset_name的默认字符序。

只明确了collation_name，charset_name未明确，则字符序为collation_name，字符集为collation_name关联的字符集。

charset_name、collation_name均未明确，则以table的字符集、字符序为准。

八、选择：何时设置字符集、字符序

一般来说，可以在三个地方进行配置：

创建数据库的时候进行配置。

mysql server启动的时候进行配置。

从源码编译mysql的时候，通过编译参数进行配置

1、方式一：创建数据库的时候进行配置

这种方式比较灵活，也比较保险，它不依赖于默认的字符集/字符序。当你创建数据库的时候指定字符集/字符序，后续创建table、column的时候，如果不特殊指定，会继承对应数据库的字符集/字符序。

CREATE DATABASE mydb
 DEFAULT CHARACTER SET utf8
 DEFAULT COLLATE utf8_general_ci;

Copier après la connexion

2、方式二：mysql server启动的时候进行配置

可以添加以下配置，这样mysql server启动的时候，会对character-set-server、collation-server进行配置。

当你通过mysql client创建database/table/column，且没有显示声明字符集/字符序，那么就会用character-set-server/collation-server作为默认的字符集/字符序。

另外，client、server连接时的字符集/字符序，还是需要通过SET NAMES进行设置。

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Copier après la connexion

3、方式三：从源码编译mysql的时候，通过编译参数进行设置

编译的时候如果指定了-DDEFAULT_CHARSET和-DDEFAULT_COLLATION，那么：

创建database、table时，会将其作为默认的字符集/字符序。

client连接server时，会将其作为默认的字符集/字符序。（不用单独SET NAMES）

shell> cmake . -DDEFAULT_CHARSET=utf8 \
  -DDEFAULT_COLLATION=utf8_general_ci

Copier après la connexion

九、写在后面

本文较为详细地介绍了MySQL中字符集、字符序相关的内容，这部分内容主要针对的是数据的存储与比较。其实还有很重要的一部分内容还没涉及：针对连接的字符集、字符序设置。

由于连接的字符集、字符序设置不当导致的乱码问题也非常多，这部分内容展开来讲内容也不少，放在下一篇文章进行讲解。