MySQL运维实战(5.2) MySQL charset基本概念

俊达1年前 (2023-04-06)技术文章300

mysql多字符集

mysql支持多字符集。一个数据库中可以存储不同字符集的数据，一个表的不同字段可以使用不同的字符集。

mysql> show character set like '%g%';
+---------+---------------------------------+--------------------+--------+
| Charset | Description                     | Default collation  | Maxlen |
+---------+---------------------------------+--------------------+--------+
| big5    | Big5 Traditional Chinese        | big5_chinese_ci    |      2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci |      4 |
| gb2312  | GB2312 Simplified Chinese       | gb2312_chinese_ci  |      2 |
| gbk     | GBK Simplified Chinese          | gbk_chinese_ci     |      2 |
| geostd8 | GEOSTD8 Georgian                | geostd8_general_ci |      1 |
| greek   | ISO 8859-7 Greek                | greek_general_ci   |      1 |
+---------+---------------------------------+--------------------+--------+
6 rows in set (0.00 sec)

MySQL中，有字符的地方，就有字符集

MySQL字符类型的字段（varchar,char,text)，都有对应的字符集。
MySQL元数据(如表名、字段名、表和字段的注释、存储过程代码）

字符集参数

MySQL中有一系列字符集相关的参数，可以通过命令查看：

mysql> show variables like '%char%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb3                                              |
| character_set_connection | utf8mb3                                              |
| character_set_database   | big5                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb3                                              |
| character_set_server     | utf8mb4                                              |
| character_set_system     | utf8mb3

参数	含义
character_set_system	系统字符集，都是utf8，用来存储元数据（字段名、表名、comment、存储过程代码等）
character_set_server	mysql服务器字符集
character_set_client	客户端数据编码
character_set_connection
character_set_results	服务端返回给客户端的数据（如字段名、数据、错误信息）的编码
character_set_filesystem	文件名编码。使用load data, select into outfile时文件名的编码
character_set_database	数据库默认编码。

建表时不指定字符集

如果建表时不指定表或字段的字符集，mysql如何确定他们的字符集？

1、建库时，如果不指定库的默认字符集，则库的默认字符集设置为character_set_server。

mysql> show variables like '%character_set_server%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> create database db_latin;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db_latin;
+----------+--------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                        |
+----------+--------------------------------------------------------------------------------------------------------+
| db_latin | CREATE DATABASE `db_latin` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------+

2、建表时，如果不指定表的字符集，则表的字符集设置为所在数据库的默认字符集。

mysql> create database db_utf8 default character set utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> create database db_gbk default character set gbk;
Query OK, 1 row affected (0.00 sec)


mysql> create table db_utf8.t(a varchar(100));
Query OK, 0 rows affected (0.00 sec)

mysql> create table db_gbk.t(a varchar(100));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table db_utf8.t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)

mysql> show create table db_gbk.t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

3、建表时，如果不指定字段的字符集，则字段的字符集设置为表的默认字符集。

4、如果显式指定字段的字符集，则以指定的字符集为准

mysql> alter table db_gbk.t add c1 varchar(10), add  c2 varchar(10) character set utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table db_gbk.t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` varchar(100) DEFAULT NULL,
  `c1` varchar(10) DEFAULT NULL,
  `c2` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

一般情况下，我们建议：

1、建表时显式指定表的字符集。

2、同一个表的多个字段，使用同样的字符集。不混合使用多个字符集。

3、一个库的多个表，使用同样的字符集。

混合使用不同的字符集可能会导致一些问题：

1、容易产生乱码。

2、表关联时发生隐式类型转换，影响查询性能。