TiDB字符集和时区

TiDB 字符集和时区

mysql> select @@version;

+--------------------------------------+

| @@version |

+--------------------------------------+

| 5.7.10-TiDB-v2.1.0-beta-179-g5a0fd2d |

+--------------------------------------+

1 row in set (0.00 sec)

mysql> show variables like 'coll%';

+----------------------+-------------------+

| Variable_name | Value |

+----------------------+-------------------+

| collation_connection | utf8_general_ci |

| collation_server | latin1_swedish_ci |

| collation_database | latin1_swedish_ci |

+----------------------+-------------------+

3 rows in set (0.01 sec)

mysql> show variables like 'char%';

+--------------------------+--------------------------------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------------------------------+

| character_sets_dir | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |

| character_set_connection | utf8 |

| character_set_client | utf8 |

| character_set_system | utf8 |

| character_set_results | utf8 |

| character_set_filesystem | binary |

| character_set_server | latin1 |

| character_set_database | latin1 |

+--------------------------+--------------------------------------------------------+

8 rows in set (0.01 sec)

登录进入默认的为utf8.

创建使用默认的字符集和排序规则的表：

mysql> create database wuhan;

Query OK, 0 rows affected (0.12 sec)

mysql> create table wuhan.city(id int not null auto_increment primary key,cityname varchar(20));

Query OK, 0 rows affected (0.14 sec)

mysql> show create table wuhan.city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cityname` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1 row in set (0.00 sec)

可以看到使用的默认字符集为utf8，排序规则为utf8_bin。

创建指定字符集和排序规则的表：

mysql> show create table wuhan.city2\G

*************************** 1. row ***************************

Table: city2

Create Table: CREATE TABLE `city2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cityname` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

1 row in set (0.00 sec)

使用latin1字符集和排序规则的表。

mysql> create table wuhan.city3(id int not null auto_increment primary key,cityname varchar(20)) character set latin1 COLLATE latin1_general_ci;

Query OK, 0 rows affected (0.14 sec)

mysql> show create table wuhan.city3\G

*************************** 1. row ***************************

Table: city3

Create Table: CREATE TABLE `city3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cityname` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

1 row in set (0.00 sec)

---查询当前数据库的字符集和排序规则：

mysql> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

| latin1 | latin1_swedish_ci |

+--------------------------+----------------------+

1 row in set (0.01 sec)

mysql> use wuhan

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

| utf8 | utf8_bin |

+--------------------------+----------------------+

1 row in set (0.00 sec)

--查询表的排序规则：

mysql> SELECT t.TABLE_SCHEMA,t.TABLE_NAME,t.TABLE_COLLATION

-> FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA='wuhan';

+--------------+------------+-------------------+

| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |

+--------------+------------+-------------------+

| wuhan | city | utf8_bin |

| wuhan | city2 | utf8_general_ci |

| wuhan | city3 | latin1_general_ci |

+--------------+------------+-------------------+

第二部分 设置字符集和排序规则：

设置为utf8，排序规则则自动设置为utf8_bin.

set names 'utf8';

等同于：

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

需要另外设置：

set global collation_server=utf8_bin;

SET CHARACTER SET 'charset_name'

等同于：

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET collation_connection = @@collation_database;

mysql> show variables like 'char%';

+--------------------------+--------------------------------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------------------------------+

| character_set_connection | utf8 |

| character_set_client | utf8 |

| character_set_system | utf8 |

| character_set_database | utf8 |

| character_set_results | utf8 |

| character_sets_dir | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |

| character_set_filesystem | binary |

| character_set_server | utf8 |

+--------------------------+--------------------------------------------------------+

8 rows in set (0.01 sec)

mysql> show variables like 'coll%';

+----------------------+----------+

| Variable_name | Value |

+----------------------+----------+

| collation_connection | utf8_bin |

| collation_database | utf8_bin |

| collation_server | utf8_bin |

+----------------------+----------+

3 rows in set (0.01 sec)

结论：

经过验证默认的字符集就是utf8，排序规则是utf8_bin.

在 TiDB 中实际上 utf8 被当做成了 utf8mb4 来处理。

当前tiBD支持的字符集和校验规则可以分别通过如下方式查询：

mysql> SHOW CHARACTER SET;

+---------+---------------+-------------------+--------+

| Charset | Description | Default collation | Maxlen |

+---------+---------------+-------------------+--------+

| utf8 | UTF-8 Unicode | utf8_bin | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_bin | 4 |

| ascii | US ASCII | ascii_bin | 1 |

| latin1 | Latin1 | latin1_bin | 1 |

| binary | binary | binary | 1 |

+---------+---------------+-------------------+--------+

5 rows in set (0.00 sec)

mysql> SHOW COLLATION;

目前支持排序规则219个。

但是上面的设置在tiDB重启后就使用了默认的字符集和和排序规则。

默认的字符集为utf8.TiDB目前不支持将字符集合排序规则写入到配置文件中。

时区支持：

TiDB 使用的时区由 time_zone 全局变量和 session 变量决定。 time_zone 的初始值是机器当前的系统时区为SYSTEM。

--查询时区：

mysql> SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| SYSTEM | SYSTEM |

+--------------------+---------------------+

1 row in set (0.00 sec)

对于中国用户，按照默认使用即可。