TiDB 字符集和時區(qū)
mysql> select @@version;
+--------------------------------------+
| @@version |
+--------------------------------------+
| 5.7.10-TiDB-v2.1.0-beta-179-g5a0fd2d |
+--------------------------------------+
1 row in set (0.00 sec)文章來源：http://www.zghlxwxcb.cn/news/detail-567406.html
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)
登錄進(jìn)入默認(rèn)的為utf8.
創(chuàng)建使用默認(rèn)的字符集和排序規(guī)則的表：
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)
可以看到使用的默認(rèn)字符集為utf8，排序規(guī)則為utf8_bin。
創(chuàng)建指定字符集和排序規(guī)則的表：
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字符集和排序規(guī)則的表。
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)
---查詢當(dāng)前數(shù)據(jù)庫的字符集和排序規(guī)則：
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)
--查詢表的排序規(guī)則：
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 |
+--------------+------------+-------------------+
第二部分 設(shè)置字符集和排序規(guī)則：
設(shè)置為utf8，排序規(guī)則則自動設(shè)置為utf8_bin.
set names 'utf8';
等同于：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
需要另外設(shè)置：
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)
結(jié)論：
經(jīng)過驗(yàn)證默認(rèn)的字符集就是utf8，排序規(guī)則是utf8_bin.
在 TiDB 中實(shí)際上 utf8 被當(dāng)做成了 utf8mb4 來處理。
當(dāng)前tiBD支持的字符集和校驗(yàn)規(guī)則可以分別通過如下方式查詢：
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;
目前支持排序規(guī)則219個。
但是上面的設(shè)置在tiDB重啟后就使用了默認(rèn)的字符集和和排序規(guī)則。
默認(rèn)的字符集為utf8.TiDB目前不支持將字符集合排序規(guī)則寫入到配置文件中。
時區(qū)支持：
TiDB 使用的時區(qū)由 time_zone 全局變量和 session 變量決定。 time_zone 的初始值是機(jī)器當(dāng)前的系統(tǒng)時區(qū)為SYSTEM。
--查詢時區(qū)：
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
對于中國用戶，按照默認(rèn)使用即可。文章來源地址http://www.zghlxwxcb.cn/news/detail-567406.html
到了這里，關(guān)于TiDB字符集和時區(qū)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
