MySQL的数据类型介绍

MySQL的数据类型介绍

一、整型

类型	存储空(单位:字节)	取值区间(有符号)	取值区间(无符号)
tinyint	1	[ -128,127 ]	[0,255]
smallint	2	[-32768 , 32767]	[0,65535]
mediumint	3	[-8388608 , 8388607]	[0 , 16777215]
int	4	[-2147483648 , 2147483647]	[0 , 4294967295]
bigint	8	[ -9223372036854775808 , 9223372036854775807]	[0 , 18446744073709551615]

1.INT类型使用建议

推荐不使用unsigned
自增int类型主键建议使用bigint

2.unsigned的注意事项

mysql> create table test_unsigned(a int unsigned, b int unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test_unsigned values(1, 2);
Query OK, 1 row affected (0.01 sec)

//注意这个地方就报错了！
mysql> select a - b from test_unsigned;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test_unsigned`.`a` - `test`.`test_unsigned`.`b`)'
mysql> select b-a from test_unsigned;
+------+
| b-a  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

// 这样可以得到负数
mysql> set sql_mode = 'no_unsigned_subtraction';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select a - b from test_unsigned;
+-------+
| a - b |
+-------+
|    -1 |
+-------+
1 row in set (0.00 sec)

3.zerofill的注意事项

对于INT(N)来说，N无论写成多大，都是占4个字节的空间，而非写成几就能存几位 N不是带代表能存多少位，只是显示宽度。例如 INT(4) 和 INT(10)其实存的东西是一样的长度。 zerofill 表示当存储的数字长度 < N 时，用数字0 填充左边，直至补满长度N 当存储数字的长度超过N时，按照实际存储的数字显示 int(N)中的N 和zerofill 配合才有意义，且仅仅是显示的时候才有意义，和实际存储没有关系，不会去截取数字的长度。

mysql> create table test_int_n(a int(3) zerofill);
Query OK, 0 rows affected (0.03 sec)

//不满 N=3时，左边用0填充
mysql> insert into test_int_n values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_int_n;
+------+
| a    |
+------+
|  001 |
+------+
1 row in set (0.00 sec)

//超过N=3的长度时，是什么数字，就显示什么数字
mysql> insert into test_int_n values(1111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_int_n;
+------+
| a    |
+------+
|  001 |
| 1111 |
+------+
2 rows in set (0.00 sec)

//
mysql> select a, HEX(a) from test_int_n;
+------+--------+
| a    | HEX(a) |
+------+--------+
|  001 | 1      |   -- 实际存储的还是1
| 1111 | 457    |   -- 1111对于的16进制就是457
+------+--------+
2 rows in set (0.00 sec)

4.auto_increment

自增，每张表最多一个，必须是索引的一部分

//没有指定key，创建的时候就会报错
mysql> create table test_auto_increment(a int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

//指定了key，则创建成功
mysql> create table test_auto_increment(a int auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)

//插入null，意思就是自增开始，从1开始
mysql> insert into test_auto_increment values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_auto_increment;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

//插入0，自增为2
mysql> insert into test_auto_increment values(0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_auto_increment;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

//插入-1，则为-1
mysql> insert into test_auto_increment values(-1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

//插入null，继续从目前最大的值开始自增，从2开始
mysql> insert into test_auto_increment values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
+----+
4 rows in set (0.00 sec)

//再次插入-1 ，报错，说明-1是直接插入值，而非使用自增
mysql> insert into test_auto_increment values(-1);
ERROR 1062 (23000): Duplicate entry '-1' for key 'PRIMARY'
mysql> insert into test_auto_increment values('0');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
|  4 |
+----+
5 rows in set (0.00 sec)

//这个是一个将自增从0开始的办法，先插入-1，再把 -1 update为 0
mysql> update test_auto_increment set a = 0 where a = -1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_auto_increment;
+---+
| a |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+---+
5 rows in set (0.00 sec)

//插入3个值，第一个是从4开始自增所以为5，第二个是直接插入100，第三个是从100开始自增所以为101
mysql> insert into test_auto_increment values(NULL), (100), (NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_auto_increment;
+-----+
| a   |
+-----+
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
| 100 |
| 101 |
+-----+
8 rows in set (0.00 sec)

//插入99，因为没有99这个值，所以直接插入进去
mysql> insert into test_auto_increment values(99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_auto_increment;
+-----+
| a   |
+-----+
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|  99 |
| 100 |
| 101 |
+-----+
9 rows in set (0.00 sec)

注意：

AUTO_INCREMENT 是实例启动时，取当前表的最大值，然后 +1 即为下次自增的值。（MAX + 1)

数字 0 这个值比较特殊，插入0和插入NULL的效果是一样的，都是代表自增

insert into tablename select NULL; 等价于insert into tablename values (NULL);

二、浮点型

类型占用空间精度精确性

float 4 单精度低

double 8 双精度低

decimal 变长高精度非常高

类型	占用空间	精度	精确性
float	4	单精度	低
double	8	双精度	低
decimal	变长	高精度	非常高

M*G/G 不一定=M
float(M,D) double(M,D) decimal(M,D) 表示显示M位整数，其中D位位于小数点后面

避免使用浮点类型，因为它并不属于精确的类型。在生产环境中，不建议使用float和double，对于财务系统或者说和钱有关的，必须使用decimal。即使使用了decimal，但是在数值运算中还是会转成浮点来运算，而且在计算过程中会出现四舍五入的情况。

//创建一个含有decimal类型的表
mysql> show create table test_decimal \G
*************************** 1. row ***************************
       Table: test_decimal
Create Table: CREATE TABLE `test_decimal` (
  `id` int(10) NOT NULL,
  `salary` decimal(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

//插入一个超过范围的6位的数，在表的定义写的是decimal(6,2)，代表一共6位数，其中有2位为小数
//在5.7版本可用插入，但会有waring提示；在5.6版本直接报错
//查看插入的结果，就是最接近123456的一个数
mysql> insert into test_decimal(id,salary) values(1,123456);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'salary' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_decimal;
+----+---------+
| id | salary  |
+----+---------+
|  1 | 9999.99 |
+----+---------+
1 row in set (0.00 sec)

//插入一个不超过范围的数，则正常显示
mysql> insert into test_decimal(id,salary) values(1,1234);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_decimal;
+----+---------+
| id | salary  |
+----+---------+
|  1 | 9999.99 |
|  1 | 1234.00 |
+----+---------+
2 rows in set (0.00 sec)

//插入一个小数超范围的数，则会四舍五入
mysql> insert into test_decimal(id,salary) values(1,1234,5678);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into test_decimal(id,salary) values(1,1234.5678);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test_decimal;
+----+---------+
| id | salary  |
+----+---------+
|  1 | 9999.99 |
|  1 | 1234.00 |
|  1 | 1234.57 |
+----+---------+
3 rows in set (0.00 sec)

三、字符串类型

	说明	N的含义	是否有字符集	最大长度
CHAR(N)	定长字符	字符	是	255
VARCHAR(N)	变长字符	字符	是	16384
BINARY(N)	定长二进制字节	字节	否	255
VARBINARY(N)	变长二进制字节	字节	否	16384
TINYBLOB	二进制大对象	字节	否	256
BLOB	二进制大对象	字节	否	16K
MEDIUMBLOB	二进制大对象	字节	否	16M
LONGBLOB	二进制大对象	字节	否	4G
TINYTEXT	大对象	字节	是	256
TEXT	大对象	字节	是	16K
MEDIUMTEXT	大对象	字节	是	16M
LONGTEXT	大对象	字节	是	4G

BLOB=>VARBINARY TEXT=>VARCHAR

注意事项 1.在BLOB和TEXT列上创建索引的时候，必须指定索引前缀的长度 2.varchar和varbinary前缀长度是可选的 3.blob和te列不能有默认值 4.blob和text列排序时只使用该列的前 max_sort_length个字节

1.char(N)说明

假设当前table的字符集的最大长度为W , 则char(N) 的最大存储空间为(N * W)Byte 假设使用UTF-8 ，则char(10)可以最小存储10个字节的字符，最大存储30个字节的字符，其实是另一种意义上的varchar 当存储的字符数小于N 时，尾部使用空格填充，并且填充最小字节的空格

mysql> create table test_char(a char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test_char\G
*************************** 1. row ***************************
       Table: test_char
Create Table: CREATE TABLE `test_char` (
  `a` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> insert into test_char values('abc');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_char values('启明星');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_char values('启明星hh');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_char values('启明hh星');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_char values('启明hh星啊');
Query OK, 1 row affected (0.01 sec)

mysql> select a, length(a) from test_char;
+----------------+-----------+
| a              | length(a) |
+----------------+-----------+
| abc            |         3 |
| 启明星         |         9 |
| 启明星hh       |        11 |
| 启明hh星       |        11 |
| 启明hh星啊     |        14 |
+----------------+-----------+
5 rows in set (0.01 sec)

mysql> select a, hex(a) from test_char;
+----------------+------------------------------+
| a              | hex(a)                       |
+----------------+------------------------------+
| abc            | 616263                       |
| 启明星         | E590AFE6988EE6989F           |
| 启明星hh       | E590AFE6988EE6989F6868       |
| 启明hh星       | E590AFE6988E6868E6989F       |
| 启明hh星啊     | E590AFE6988E6868E6989FE5958A |
+----------------+------------------------------+
5 rows in set (0.00 sec)

mysql> select hex(' ');
+----------+
| hex(' ') |
+----------+
| 20       |
+----------+
1 row in set (0.00 sec)

用hexdump来比较一下：

-- 1:abc
-- 2:启明星
-- 3:启明星hh
-- 4:启明hh星
-- 5:启明ab星啊

[root@nazeebo test]# hexdump -C test_char.ibd
...
...
00006070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 24 00  |supremum......$.|
00006080  00 00 00 02 07 00 00 00  00 0a b5 fe 00 00 00 48  |...............H|
00006090  01 10 61 62 63 20 20 20  20 20 20 20 0a 00 00 00  |..abc       ....|
000060a0  18 00 24 00 00 00 00 02  08 00 00 00 00 0a b6 ff  |..$.............|
000060b0  00 00 00 4c 01 10 e5 90  af e6 98 8e e6 98 9f 20  |...L........... |
000060c0  0b 00 00 00 20 00 25 00  00 00 00 02 09 00 00 00  |.... .%.........|
000060d0  00 0a bb a2 00 00 00 4d  01 10 e5 90 af e6 98 8e  |.......M........|
000060e0  e6 98 9f 68 68 0b 00 00  00 28 00 25 00 00 00 00  |...hh....(.%....|
000060f0  02 0a 00 00 00 00 0a bc  a3 00 00 00 49 01 10 e5  |............I...|
00006100  90 af e6 98 8e 68 68 e6  98 9f 0e 00 00 00 30 ff  |.....hh.......0.|
00006110  5f 00 00 00 00 02 0b 00  00 00 00 0a c1 a6 00 00  |_...............|
00006120  00 4a 01 10 e5 90 af e6  98 8e 68 68 e6 98 9f e5  |.J........hh....|
00006130  95 8a 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00006140  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
...
省略一部分输出
...
[root@nazeebo test]#

2.varchar(N)说明

mysql> create table test_varchar(a varchar(10));
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into test_varchar values('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_varchar values('启明星');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_varchar values('启明星hh');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_varchar values('启明hh星');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_varchar values('启明hh星啊');
Query OK, 1 row affected (0.01 sec)

mysql> select a, hex(a) from test_varchar;
+----------------+------------------------------+
| a              | hex(a)                       |
+----------------+------------------------------+
| abc            | 616263                       |
| 启明星         | E590AFE6988EE6989F           |
| 启明星hh       | E590AFE6988EE6989F6868       |
| 启明hh星       | E590AFE6988E6868E6989F       |
| 启明hh星啊     | E590AFE6988E6868E6989FE5958A |
+----------------+------------------------------+
5 rows in set (0.01 sec)

mysql> select a, length(a) from test_varchar;
+----------------+-----------+
| a              | length(a) |
+----------------+-----------+
| abc            |         3 |
| 启明星         |         9 |
| 启明星hh       |        11 |
| 启明hh星       |        11 |
| 启明hh星啊     |        14 |
+----------------+-----------+
5 rows in set (0.00 sec)

[root@nazeebo test]# hexdump -C test_varchar.ibd
...
...
00006070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|
00006080  00 00 00 02 0c 00 00 00  00 0a c6 ab 00 00 00 51  |...............Q|
00006090  01 10 61 62 63 09 00 00  00 18 00 23 00 00 00 00  |..abc......#....|
000060a0  02 0d 00 00 00 00 0a c7  ac 00 00 00 4c 01 10 e5  |............L...|
000060b0  90 af e6 98 8e e6 98 9f  0b 00 00 00 20 00 25 00  |............ .%.|
000060c0  00 00 00 02 0e 00 00 00  00 0a cc af 00 00 00 4d  |...............M|
000060d0  01 10 e5 90 af e6 98 8e  e6 98 9f 68 68 0b 00 00  |...........hh...|
000060e0  00 28 00 25 00 00 00 00  02 0f 00 00 00 00 0a cd  |.(.%............|
000060f0  b0 00 00 00 4b 01 10 e5  90 af e6 98 8e 68 68 e6  |....K........hh.|
00006100  98 9f 0e 00 00 00 30 ff  67 00 00 00 00 02 10 00  |......0.g.......|
00006110  00 00 00 0a d2 b3 00 00  00 4c 01 10 e5 90 af e6  |.........L......|
00006120  98 8e 68 68 e6 98 9f e5  95 8a 00 00 00 00 00 00  |..hh............|
00006130  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
...
...

3.char和varchar的区别

char用于定长，范围0-255，如果字符数没有到达定义的长度，会在后面用空格补全存入数据库；如果超过指定长度大小，会被截断。 varchar用于变长，范围0-65535，存储时按大小存取不会补空格，超过长度会截取。使用varchar类型时，和字符集有关，会多用1~2个字节来记录字节长度，当数据位战友的字节数小于255时，用1个字节来记录长度，当数据位战友的字节数大于255时，用2个字节来记录长度，另外一位用来记录是否为null值

MySQL每一行的最大字节数为65535 使用UTF8字符集的时候，每个字符最多占3个字节，最大长度不超过(65535-2-1)/3=21844 使用GBK字符集的时候，每个字符最多占用2个字节，最大长度不超（65535-2-1）/ 2 = 36766

4.BLOB和TEXT

在BLOB和TEXT上创建索引时，必须指定索引前缀的长度

mysql> create table test_text(a int primary key, b text, key(b));
ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length
mysql> create table test_text(a int primary key, b text, key(b(64)));
Query OK, 0 rows affected (0.02 sec)

mysql> select @@max_sort_length;
+-------------------+
| @@max_sort_length |
+-------------------+
|              1024 |
+-------------------+
1 row in set (0.00 sec)

mysql>

BLOB和TEXT列不能有默认值 BLOB和TEXT列排序时只使用该列的前max_sort_length个字节

四、集合类型enum 和 set

enum允许最多65536个值 set允许最多64个值通过sql_mode进行约束

1.集合的插入

//创建一个表，含有enum类型，插入几条数据
//虽然enum写的是字符串，单其实存储的整型，效率还行
mysql> create table test_col(
    -> user varchar(10),
    -> sex enum('male','female')
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_col values ("tom", "male");
Query OK, 1 row affected (0.00 sec)

//插入错误的enum，会有warings，并且相应的值被截断直接为空
mysql> insert into test_col values ("tom", "xmale");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'sex' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_col;
+------+------+
| user | sex  |
+------+------+
| tom  | male |
| tom  |      |
+------+------+
2 rows in set (0.00 sec)

//设置sql_mode，发现错误的enum类型是插入不进去的
mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into test_col values ("bob", "xmale");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> select * from test_col;
+------+------+
| user | sex  |
+------+------+
| tom  | male |
| tom  |      |
+------+------+
2 rows in set (0.00 sec)

建议业务上都设置成严格模式另外，虽然enum写的是字符串，单其实存储的是int类型数组(相当于male=0，female=1)，性能还是可以的

2.集合的排序

mysql> show create table test_col_sort\G
*************************** 1. row ***************************
       Table: test_col_sort
Create Table: CREATE TABLE `test_col_sort` (
  `user` char(10) DEFAULT NULL,
  `type` enum('aaa','zzz','bbb','yyy','fff') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

//插入数据
mysql> insert into test_col_sort(user,type) values('user1','aaa'),('user2','bbb'),('user3','yyy'),('user4','zzz');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

//order by type 就等于按照enum里面的顺序，[0,1,2,3]
mysql> select * from test_col_sort order by type asc;
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |
| user4 | zzz  |
| user2 | bbb  |
| user3 | yyy  |
+-------+------+
4 rows in set (0.00 sec)

//order by  cast(type as char) ，意思是将type转换为char再进行排序
mysql> select * from test_col_sort order by cast(type as char) asc;
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |
| user2 | bbb  |
| user3 | yyy  |
| user4 | zzz  |
+-------+------+
4 rows in set (0.00 sec)

//order by concat
mysql> select * from test_col_sort order by concat(type) asc;
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |
| user2 | bbb  |
| user3 | yyy  |
| user4 | zzz  |
+-------+------+
4 rows in set (0.00 sec)

五、日期类型

日期类型	占用字节	表示范围
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	1000-01-01 ~ 9999-12-31
TIMESTAMP	4	1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR	1	YEAR(2):1970-2070, YEAR(4):1901-2155
TIME	3	-838:59:59 ~ 838:59:59

1.TIMESTAMP 和 DATETIME

datetime与timestamp最大的区别在于时区

mysql> create table test_time(a timestamp, b datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_time values (now(), now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2018-06-29 00:28:40 | 2018-06-29 00:28:40 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
1 row in set (0.00 sec)

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +00:00      |
+-------------+
1 row in set (0.00 sec)

//设置了时区后，两者有了差别
mysql> select * from test_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2018-06-28 16:28:40 | 2018-06-29 00:28:40 |
+---------------------+---------------------+
1 row in set (0.00 sec)

2.微秒解读

MySQL5.6.4版本开始支持微秒支持类型包括：time 、datetime与、timestamp type_name(fsp) fsp<=6

//用法 type_name(fsp)  fsp<=6
mysql> select now(5);
+---------------------------+
| now(5)                    |
+---------------------------+
| 2018-06-28 16:34:10.64251 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2018-06-28 16:34:36.658036 |
+----------------------------+
1 row in set (0.00 sec)

// 最多支持6位精度
mysql> select now(7);
ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.
mysql>
mysql> create table test_time_fac (t datetime(6));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_time_fac values(now(6));
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_time_fac;
+----------------------------+
| t                          |
+----------------------------+
| 2018-06-28 16:35:04.557430 |
+----------------------------+
1 row in set (0.00 sec)

3.日期函数

函数	说明	备注
NOW	返回sql执行时的时间	如果不考虑其他因素，可以理解为写完SQL，敲下回车瞬间的时间
current_timestamp	与now一样
sysdate	返回执行函数时的时间	MySQL处理你的函数时的时间，统一SQL语句中，大于NOW
date_add(date,interval expr unit)	增加时间	可用ADD，然后unit给负数
date_sub(date,interval expr unit)	减少时间
date_format	格式化时间显示

3.1 NOW 和 SYSDATE的区别

mysql> select now(6),sysdate(6),sleep(5),now(6),sysdate(6);
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 | sleep(5) | now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
| 2018-06-29 00:38:59.002591 | 2018-06-29 00:38:59.002708 |        0 | 2018-06-29 00:38:59.002591 | 2018-06-29 00:39:04.002832 |
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
1 row in set (5.00 sec)

两个now(6)都相等，因为是SQL执行时的时间(可以简单理解为按回车的时间) 两个sysdate(6)差了5秒，差不多刚好是sleep(5)的时间

3.2 date_add的使用方法

//加5天
mysql> select now(),date_add(now(), interval 5 day);
+---------------------+---------------------------------+
| now()               | date_add(now(), interval 5 day) |
+---------------------+---------------------------------+
| 2018-06-29 00:41:03 | 2018-07-04 00:41:03             |
+---------------------+---------------------------------+
1 row in set (0.00 sec)

//加负的5个月=倒退5个月
mysql> select now(),date_add(now(), interval -5 month);
+---------------------+------------------------------------+
| now()               | date_add(now(), interval -5 month) |
+---------------------+------------------------------------+
| 2018-06-29 00:41:28 | 2018-01-29 00:41:28                |
+---------------------+------------------------------------+
1 row in set (0.00 sec)

//减5个月
mysql> select now(),date_sub(now(), interval 5 month);
+---------------------+-----------------------------------+
| now()               | date_sub(now(), interval 5 month) |
+---------------------+-----------------------------------+
| 2018-06-29 00:41:43 | 2018-01-29 00:41:43               |
+---------------------+-----------------------------------+
1 row in set (0.00 sec)

3.3 date_format的用法

mysql> SELECT now(),DATE_FORMAT((select now(6)), '%H:%i:%s');
+---------------------+------------------------------------------+
| now()               | DATE_FORMAT((select now(6)), '%H:%i:%s') |
+---------------------+------------------------------------------+
| 2018-06-29 00:44:46 | 00:44:46                                 |
+---------------------+------------------------------------------+
1 row in set (0.00 sec)

3.4 字段更新时间

mysql> create table test_field_update(
    -> a int(10),
    -> b timestamp not null default current_timestamp on update current_timestamp
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_field_update values(1, now(6));
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_field_update;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2018-06-29 00:45:46 |
+------+---------------------+
1 row in set (0.00 sec)

//更新数据，发现相应的字段的数据也更新了，这个可以拿来用在记录记录的表更上
//当然，如果想更精确，可以使用timestamp，now(6)
mysql> update test_field_update set a=100 where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_field_update;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|  100 | 2018-06-29 00:46:01 |
+------+---------------------+
1 row in set (0.00 sec)

六、JSON类型

5.7版本开始支持
原生json类型用来替代BLOB
json数据有效性检查：blob类型无法在数据库层做这样的约束性检查，而json类型可以
查询性能的提升：查询不需要遍历所有的字符串才能找到数据
支持部分属性索引：通过虚拟列的功能可以对json中的部分数据进行索引

1.JSON格式示例(wiki百科)

{
    "firstName": "John",  //Key: Value 格式
    "lastName": "Smith",
    "sex": "male",
    "age": 25,
    "address": //Key: Value;其中 Value 也是一个 Key - Value 的结构
    {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [{
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}

2. JSON 和 BLOB 的对比

JSON：
- JSON数据可以做有效性检查;
- JSON使得查询性能提升;
- JSON支持部分属性索引，通过虚拟列的功能可以对JSON中的部分数据进行索引;
BLOB：
- BLOB类型无法在数据库层做约束性检查;
- BLOB进行查询，需要遍历所有字符串;
- BLOB做只能做指定长度的索引;
5.7之前，只能把JSON当作BLOB进行存储。数据库层面无法对JSON数据做一些操作，只能由应用程序处理。

3. 结构化和非结构化

结构化
- 二维表结构（行和列）
- 使用SQL语句进行操作
非结构化
- 使用Key-Value格式定义数据，无结构定义
- Value可以嵌套Key-Value格式的数据
- 使用JSON进行实现

4. json示例

mysql> create table test_json (
    -> uid int auto_increment,
    -> data json,
    -> primary key(uid)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into test_json values (
    -> null, -- 自增长数据，可以插入null
    -> '{
    '> "name":"nazeebo",
    '> "age":38,
    '> "address":"CD"
    '> }'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_json values (
    -> null,
    -> '{
    '> "name":"lowa",
    '> "age":37,
    '> "mail":"lowa@bxbb.com"
    '> }'
    -> );
Query OK, 1 row affected (0.00 sec)

// 无法插入，因为是JSON类型
mysql> insert into test_json values ( null, "今晚打老虎");
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test_json.data'.
mysql>
mysql> select * from test_json;
+-----+------------------------------------------------------+
| uid | data                                                 |
+-----+------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": "CD"}      | //这个json中有address字段
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"} | //这个json中有mail字段
+-----+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

5. json的相关函数

5.1 使用json_extract提取数据

mysql> select json_extract('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |  // 从list中抽取 下标 为1的元素（下标从0开始）
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select
    -> json_extract(data, '$.name'), -- 提起name字段的数据
    -> json_extract(data, '$.address') -- 提取address字段的数据
    -> from test_json;
+------------------------------+---------------------------------+
| json_extract(data, '$.name') | json_extract(data, '$.address') |
+------------------------------+---------------------------------+
| "nazeebo"                    | "CD"                            |
| "lowa"                       | NULL                            |  // lowa 没有address字段，填充了NULL
+------------------------------+---------------------------------+
2 rows in set (0.00 sec)

5.2 使用json_object 将list(K-V对)封装成json格式

mysql> select json_object("name", "lm", "email", "limin@bxbb.com", "age",99);
+----------------------------------------------------------------+
| json_object("name", "lm", "email", "limin@bxbb.com", "age",99) |
+----------------------------------------------------------------+
| {"age": 99, "name": "lm", "email": "limin@bxbb.com"}           |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test_json values (
    -> null,
    -> json_object("name", "lm", "email", "limin@bxbb.com", "age",99) -- 进行封装
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_json;
+-----+------------------------------------------------------+
| uid | data                                                 |
+-----+------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": "CD"}      |
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"} |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"} |
+-----+------------------------------------------------------+
3 rows in set (0.00 sec)

5.3 使用json_insert 插入数据

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        | //存在的不受影响，例如a还是=1
+----------------------------------------------------+ //c之前不存在，则插入
1 row in set (0.00 sec)

mysql>
mysql> select * from test_json;
+-----+------------------------------------------------------+
| uid | data                                                 |
+-----+------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": "CD"}      |
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"} |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"} |
+-----+------------------------------------------------------+
3 rows in set (0.01 sec)

//  插入 addres_2
mysql> update test_json set data = json_insert(data, "$.address_2", "BJ") where uid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_json;
+-----+--------------------------------------------------------------------+
| uid | data                                                               |
+-----+--------------------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": "CD", "address_2": "BJ"} |  //对比发现插入了 addres_2
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"}               |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"}               |
+-----+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

5.4 使用json_merge 合并数据并返回

注意：原数据不受影响

mysql> select json_merge('{"name": "zxx"}', '{"id": 9527}');
+-----------------------------------------------+
| json_merge('{"name": "zxx"}', '{"id": 9527}') |
+-----------------------------------------------+
| {"id": 9527, "name": "zxx"}                   |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select
    -> json_merge(
    -> json_extract(data, '$.address'), -- json 1
    -> json_extract(data, '$.address_2')) -- jons 2
    -> from test_json where uid = 1;
+----------------------------------------------------------------------------------+
| json_merge(
json_extract(data, '$.address'),
json_extract(data, '$.address_2')) |
+----------------------------------------------------------------------------------+
| ["CD", "BJ"]                                                                     |
+----------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select
    -> JSON_MERGE_PRESERVE(
    -> json_extract(data, '$.address'), -- json 1
    -> json_extract(data, '$.address_2')) -- jons 2
    -> from test_json where uid = 1;
+-------------------------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE(
json_extract(data, '$.address'),
json_extract(data, '$.address_2')) |
+-------------------------------------------------------------------------------------------+
| ["CD", "BJ"]                                                                              |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

5.5 使用json_array_append 追加数据

mysql>  set @j = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_array_append(@j, '$[1]', 1);
+----------------------------------+
| json_array_append(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]        |  //下标为1的值，增加“1”
+----------------------------------+
1 row in set (0.00 sec)

mysql> update test_json set data = json_array_append(
    -> data,
    -> '$.address',
    -> json_extract(data, '$.address_2'))
    -> where uid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_json;
+-----+----------------------------------------------------------------------------+
| uid | data                                                                       |
+-----+----------------------------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": ["CD", "BJ"], "address_2": "BJ"} |  //将address2的值添加到了address中去
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"}                       |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"}                       |
+-----+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

5.6 使用json_remove 从json记录中删除数据

mysql> set @j = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected (0.00 sec)

mysql>  select json_remove(@j, '$[1]');
+-------------------------+
| json_remove(@j, '$[1]') |
+-------------------------+
| ["a", "d"]              |  //删除下标为1的key和value
+-------------------------+
1 row in set (0.00 sec)

mysql> update test_json set data = json_remove(data, "$.address_2") where uid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_json;
+-----+---------------------------------------------------------+
| uid | data                                                    |
+-----+---------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": ["CD", "BJ"]} |  // address_2 的字段删除了
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"}    |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"}    |
+-----+---------------------------------------------------------+
3 rows in set (0.00 sec)

6. json 字段创建索引

JSON 类型数据本身 无法直接 创建索引，需要将需要索引的 JSON数据 重新生成虚拟列(Virtual Columns) 之后，对该列进行索引

6.1 新建表的时候创建json索引

1.创建表，建表的语法中抽取data中的name， 生成新的一列，名字为gen_col
mysql> create table test_json_index(
    ->  data json,
    ->  gen_col varchar(10) generated always as (json_extract(data, '$.name')),
    ->  index idx (gen_col) -- 将gen_col 作为索引
    ->  );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test_json_index\G
*************************** 1. row ***************************
       Table: test_json_index
Create Table: CREATE TABLE `test_json_index` (
  `data` json DEFAULT NULL,
  `gen_col` varchar(10) GENERATED ALWAYS AS (json_extract(`data`,'$.name')) VIRTUAL,
  KEY `idx` (`gen_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

2.插入两条数据
mysql> insert into test_json_index(data) values ('{"name":"lowa", "age":38, "address":"CD"}');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into test_json_index(data) values ('{"name":"nazeebo", "age":66, "address":"SZ"}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_json_index;
+-------------------------------------------------+-----------+
| data                                            | gen_col   |
+-------------------------------------------------+-----------+
| {"age": 38, "name": "lowa", "address": "CD"}    | "lowa"    |
| {"age": 66, "name": "nazeebo", "address": "SZ"} | "nazeebo" |
+-------------------------------------------------+-----------+
2 rows in set (0.00 sec)

3.查询的时候发现找不到记录
mysql> select json_extract(data,"$.name") as username from test_json_index where gen_col="lowa";
Empty set (0.00 sec)

4.原因是因为存储的时候连“”也存储了的
mysql>  select hex('"');
+----------+
| hex('"') |
+----------+
| 22       |
+----------+
1 row in set (0.00 sec)

mysql> select hex(gen_col) from test_json_index;
+--------------------+
| hex(gen_col)       |
+--------------------+
| 226C6F776122       |
| 226E617A6565626F22 |
+--------------------+
2 rows in set (0.00 sec)

5.所以带上双引号再去查询就可以得到正确的结果集
mysql> select json_extract(data,"$.name") as username from test_json_index where gen_col='"lowa"';
+----------+
| username |
+----------+
| "lowa"   |
+----------+
1 row in set (0.00 sec)

6.查看执行计划，使用了index的
mysql> explain select json_extract(data,"$.name") as username from test_json_index where gen_col='"lowa"' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_json_index
   partitions: NULL
         type: ref
possible_keys: idx
          key: idx
      key_len: 43
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

7.为了避免出现连双引号一起存储的情况，采用json_unquote函数进行去掉双引号创建新的表test_json_index_2
mysql> create table test_json_index_2 (
    -> data json,
    -> gen_col varchar(10) generated always as (
    -> json_unquote( -- 使用json_unquote函数进行去掉双引号
    -> json_extract(data, "$.name")
    -> )),
    -> key idx(gen_col)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test_json_index_2\G
*************************** 1. row ***************************
       Table: test_json_index_2
Create Table: CREATE TABLE `test_json_index_2` (
  `data` json DEFAULT NULL,
  `gen_col` varchar(10) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,'$.name'))) VIRTUAL,
  KEY `idx` (`gen_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

8.再次插入两条记录
mysql> insert into test_json_index_2(data) values ('{"name":"lowa", "age":38, "address":"CD"}');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_json_index_2(data) values ('{"name":"nazeebo", "age":66, "address":"SZ"}');
Query OK, 1 row affected (0.00 sec)

9.再次查询，发现能正确的获取到结果集
mysql> select json_extract(data,"$.name") as username from test_json_index_2 where gen_col="lowa";
+----------+
| username |
+----------+
| "lowa"   |
+----------+
1 row in set (0.00 sec)

10.查询执行计划，发现也使用了index
mysql> explain select json_extract(data,"$.name") as username from test_json_index_2 where gen_col="lowa"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_json_index_2
   partitions: NULL
         type: ref
possible_keys: idx
          key: idx
      key_len: 43
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

6.2 修改已存在的表创建JSON索引

1.拿之前创建好的表test_json来做实验
mysql> show create table test_json\G
*************************** 1. row ***************************
       Table: test_json
Create Table: CREATE TABLE `test_json` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from test_json;
+-----+---------------------------------------------------------+
| uid | data                                                    |
+-----+---------------------------------------------------------+
|   1 | {"age": 38, "name": "nazeebo", "address": ["CD", "BJ"]} |
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"}    |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"}    |
+-----+---------------------------------------------------------+
3 rows in set (0.00 sec)

2.创建虚拟列
**注意：virtual 关键字是不将该列的字段值存储，对应的是stored**
mysql> alter table test_json
    -> add user_name varchar(32)
    -> generated always as (json_extract(data,"$.name")) virtual;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_json;
+-----+---------------------------------------------------------+-----------+
| uid | data                                                    | user_name |
+-----+---------------------------------------------------------+-----------+
|   1 | {"age": 38, "name": "nazeebo", "address": ["CD", "BJ"]} | "nazeebo" |
|   2 | {"age": 37, "mail": "lowa@bxbb.com", "name": "lowa"}    | "lowa"    |
|   3 | {"age": 99, "name": "lm", "email": "limin@bxbb.com"}    | "lm"      |
+-----+---------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

3.添加索引
mysql> alter table test_json add index idx(user_name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.查看执行计划
mysql> explain select * from test_json where user_name='"lowa"'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_json
   partitions: NULL
         type: ref
possible_keys: idx
          key: idx
      key_len: 131
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> show create table test_json\G
*************************** 1. row ***************************
       Table: test_json
Create Table: CREATE TABLE `test_json` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  `user_name` varchar(32) GENERATED ALWAYS AS (json_extract(`data`,'$.name')) VIRTUAL,
  PRIMARY KEY (`uid`),
  KEY `idx` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

七、字符集相关

1. MySQL字符集包括：

字符集(character) --用于定义字符串的存储方式校对规则(collation) --用于定义比较字符串的方式常用的字符集：

utf8：最常用的字符集，占3个字节
utf8mb4：utf8的超级，占4个字节
gbk ：占2个字节，通用性没有utf8好
gb18030：是最新的汉字编码字符集国家标准, 向下兼容 GBK 和 GB2312 标准，编码是一二四字节变长编码

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 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

2. collation（排序规则）

collation的含义是指排序规则， ci（case insensitive）结尾的排序集是不区分大小写的

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> create table test_ci (a varchar(10), key(a));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_ci values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_ci values('A');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_ci where a = 'a';
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

是不是感觉很神奇！！！a=A 居然是TRUE！解释一下为什么会出现以上的情况。首先，查下默认的排序规则

mysql> show variables like '%coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

ci（case insensitive）结尾的排序集是不区分大小写的，所以才有了以上的结果

 // 修改当前session的collate，a终于不等于A 了，但是表里面的数据还是 a=A
 mysql> set names utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select * from test_ci where a = 'a';
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

//修改了session的collate对表数据不起作用，因为表自身也有collate。
//尝试给表修改collate
mysql> alter table test_ci collate utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

//发现还是a=A，这是因为只修改collate，对已经存在的数据不起作用
mysql> select * from test_ci where a = 'a';
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

// 要使得已经存在的数据也修改新的collate，需要用convert命令进行转换
mysql> alter table test_ci convert to character set utf8mb4 collate utf8mb4_bin;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_ci where a = 'a';
+------+
| a    |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql>

//另外，如果没有数据，可以直接用下面的sql进行修改
mysql> alter table test_ci character set utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 乱码问题

字符集涉及的范围很广，像程序链接、数据库、服务器、表以及表中的字段、结果集，都会涉及到字符集。字符集最容易遇到的问题就是乱码。

 mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8                                                           |
| character_set_connection | utf8                                                           |
| character_set_database   | utf8mb4                                                        |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8                                                           |
| character_set_server     | utf8mb4                                                        |
| character_set_system     | utf8                                                           |
| character_sets_dir       | /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)

如何解决乱码呢？答案是：保证三者一致。三者包括：连接终端的字符集、操作系统的字符集、MySQL数据库的字符集。如果想要临时的修改数据库字符集，可以在数据库命令行执行set names xxx来实现。三者统一都是UTF8，就不会出现中文乱码的情况了。