第6章：MySQL之数据类型

修订日期：2020-10-23

一. INT类型

1. INT类型的分类

TINYINT
- 存储空间： 1 字节
- 取值范围
  - 有符号(signed) ： [-128, 127]
  - 无符号(unsigned) ：[0, 255]
SMALLINT
- 存储空间： 2 字节
- 取值范围
  - 有符号(signed) ： [-32768, 32767]
  - 无符号(unsigned) ：[0, 65535]
MEDIUMINT
- 存储空间： 3 字节
- 取值范围
  - 有符号(signed) ： [-8388608, 8388607]
  - 无符号(unsigned) ：[0, 16777215]
INT
- 存储空间： 4 字节
- 取值范围
  - 有符号(signed) ： [-2147483648, 2147483647]
  - 无符号(unsigned) ：[0, 4294967295]
BIGINT
- 存储空间： 8 字节
- 取值范围
  - 有符号(signed) ： [-9223372036854775808, 9223372036854775807]
  - 无符号(unsigned) ：[0, 18446744073709551615]

2. INT类型的使用

自增长ID
- 推荐使用BIGINT，而不是INT；
unsigned or signed
- 根据实际情况使用，一般情况下推荐默认的sigend
- unsigned 的注意事项

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

mysql> use dbtype
Database changed

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

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

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

mysql> select a - b  from t_unsigned;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`dbtype`.`t_unsigned`.`a` - `dbtype`.`t_unsigned`.`b`)'   --表示超出数据范围

mysql> set sql_mode = 'no_unsigned_subtraction';  --设置sql_mode模式
Query OK, 0 rows affected (0.00 sec)

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

mysql>

一般情况下使用int时，推荐有符号数(signed)，使用无符号数只是比原来多一倍的取值，数量级上没有改变。
如果需要取值超过10位以上，直接选择用BIGINT类型

3. INT(N)

mysql> show create table t_unsigned \G;
*************************** 1. row ***************************
       Table: t_unsigned
Create Table: CREATE TABLE `t_unsigned` (
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

int(N) 和 zerofill
- int(N)中的N是显示宽度，不表示存储的数字的长度的上限。
- zerofill表示当存储的数字长度 < N时，用数字0填充左边，直至补满长度N
- 当存储数字的长度超过N时，按照实际存储的数字显示


mysql> create table t_int_num(a int(3) zerofill); -- 显示宽度N=3
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t_int_num values(10);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t_int_num;
+------+
| a    |
+------+
|  010 |     -- 不满 N=3时，左边用0填充
|  002 |
+------+
2 rows in set (0.00 sec)

mysql> insert into t_int_num values(444444);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_int_num;
+--------+
| a      |
+--------+
|    010 |
|    002 |
| 444444 |      -- 超过N=3的长度时，是什么数字，显示什么数字
+--------+
3 rows in set (0.00 sec)


mysql> select a, HEX(a) from t_int_num\G
*************************** 1. row ***************************
     a: 010
HEX(a): A      -- 实际存储的还是十六进制A
*************************** 2. row ***************************
     a: 002
HEX(a): 2
*************************** 3. row ***************************
     a: 444444
HEX(a): 6C81C    -- 实际存储的还是十六进制444444
3 rows in set (0.00 sec)

int(N)中的N和zerofill配合才有意义，且仅仅是显示的时候才有意义，和实际存储没有关系，不会去截取数字的长度。

4. AUTO_INCREMENT

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

mysql> create table t_auto_inc(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     --自增类必须为主键

mysql> create table t_auto_inc(a int auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_auto_inc values(null);  --插入null值
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+---+
| a |
+---+
| 1 |    -- 插入NULL值，便可以让其自增，且默认从1开始
+---+
1 row in set (0.00 sec)

mysql> insert into t_auto_inc values(1);     
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  --自增配置主键必须为唯一值

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

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

mysql> insert into t_auto_inc values(22);  --插入数值，如果值大于原来自增值就会用新值+1来表示
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  1 |
|  2 |
| 22 |    --变成自增值
+----+
3 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(3);  --插入值小于自增值22所以还是没变
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |   
| 22 |        --自增值不变
+----+
4 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(null);    --当插如null值
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_auto_inc values(null);   --再次插入null值
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;    
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
| 22 |
| 23 |      --自增值+1
| 24 |      --自增值再+1
+----+
6 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(20);     --插入20值小于自增值24
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
| 20 |       --插入到自增值前面
| 22 |
| 23 |
| 24 |
+----+
7 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(-1);  --插入-1
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
+----+
8 rows in set (0.00 sec)


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

mysql> update t_auto_inc set a =0 where a =-1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
+----+
8 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(0);  --数字 0 这个值比较特殊， 插入0和插入NULL的效果是一样的，都是代表自增
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
| 26 |
+----+
9 rows in set (0.00 sec)

AUTO_INCREMENT是实例启动时，取当前表的最大值，然后 +1 即为下次自增的值。（MAX + 1)
注意: insert into tablename select NULL; 等价与 insert into tablename values (NULL);

二. 数字类型

1. 数字类型的分类

单精度类型：FLOAT
- 存储空间：4 字节
- 精确性：低
双精度类型：DOUBLE
- 占用空间：8 字节
- 精确性：低，比FLOAT高
高精度类型：DECIMAL
- 占用空间：变长
- 精确性：非常高

注意：财务系统必须使用DECIMAL

三. 字符串类型

1. 字符串类型介绍

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

2. N和字符集

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

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

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

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

mysql> insert into t_char values('你好哦');
Query OK, 1 row affected (0.00 sec)

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

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

mysql> insert into t_char values('很不好abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_char values('很不abc好');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_char values('非常不好abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_char values('非常不好abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_char values('非常不好abc');
Query OK, 1 row affected (0.00 sec)

mysql>  select a, length(a) from t_char;
+-----------------+-----------+
| a               | length(a) |
+-----------------+-----------+
| abc             |         3 |
| 你好哦          |         9 |
| 你好abc         |         9 |
| abc你好         |         9 |
| 很不好abc       |        12 |
| 很不abc好       |        12 |
| 非常不好abc     |        15 |
+-----------------+-----------+
7 rows in set (0.00 sec)

mysql> select a,hex(a) from t_char;
+-----------------+--------------------------------+
| a               | hex(a)                         |
+-----------------+--------------------------------+
| abc             | 616263                         |    -- 注意这里，以及下面的16进制值，一会可以对比
| 你好哦          | E4BDA0E5A5BDE593A6             |
| 你好abc         | E4BDA0E5A5BD616263             |
| abc你好         | 616263E4BDA0E5A5BD             |
| 很不好abc       | E5BE88E4B88DE5A5BD616263       |
| 很不abc好       | E5BE88E4B88D616263E5A5BD       |
| 非常不好abc     | E99D9EE5B8B8E4B88DE5A5BD616263 |
+-----------------+--------------------------------+
7 rows in set (0.00 sec)

mysql> select hex(' ');
+----------+
| hex(' ') |
+----------+
| 20       |   -- 注意`空格`,空格对应的16进制数字是 `20`
+----------+
1 row in set (0.00 sec)

t_char表实际二进制存储文件

 
-- shell> hexdump -C t_char.idb

0000c070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 24 00  |supremum......$.|
0000c080  00 00 00 12 04 00 00 40  18 07 2c b8 00 01 df 9c  |.......@..,.....|
0000c090  01 10 61 62 63 20 20 20  20 20 20 20 0a 00 00 00  |..abc       ....| --abc为61 62 63 空格为20 后面补了7个空格也就是 '61 62 63 20 20 20  20 20 20 20'
0000c0a0  18 00 24 00 00 00 00 12  05 00 00 40 18 07 2d b9  |..$........@..-.|
0000c0b0  00 01 df 9d 01 10 e4 bd  a0 e5 a5 bd e5 93 a6 20  |............... | --你好哦表示  e4 bd a0 e5 a5 bd e5 93 a6 20 后面补了空格20
0000c0c0  0a 00 00 00 20 00 24 00  00 00 00 12 06 00 00 40  |.... .$........@|
0000c0d0  18 07 32 bc 00 01 df b0  01 10 e4 bd a0 e5 a5 bd  |..2.............| --你好abc表示 e4 bd a0 e5 a5 bd 61 62 63 20 后面补了空格20
0000c0e0  61 62 63 20 0a 00 00 00  28 00 24 00 00 00 00 12  |abc ....(.$.....|
0000c0f0  07 00 00 40 18 07 33 bd  00 01 df a1 01 10 61 62  |...@..3.......ab| --abc你好表示 61 62 63 e4 bd a0 e5 a5 bd 20 后面补了空格20
0000c100  63 e4 bd a0 e5 a5 bd 20  0c 00 00 00 30 00 26 00  |c...... ....0.&.|
0000c110  00 00 00 12 08 00 00 40  18 07 3f a7 00 01 df 97  |.......@..?.....| 
0000c120  01 10 e5 be 88 e4 b8 8d  e5 a5 bd 61 62 63 0c 00  |...........abc..| --很不好abc表示 e5 be 88 e4 b8 8d e5 a5 bd 61 62 63 没有空格补
0000c130  00 00 38 00 26 00 00 00  00 12 09 00 00 40 18 07  |..8.&........@..|
0000c140  40 a8 00 01 df 8d 01 10  e5 be 88 e4 b8 8d 61 62  |@.............ab| --很不abc好表示 e5 be 88 e4 b8 8d 61 62 63 e5 a5 bd 没有空格补
0000c150  63 e5 a5 bd 0f 00 00 00  40 ff 15 00 00 00 00 12  |c.......@.......|
0000c160  0a 00 00 40 18 07 4c b2  00 01 df 81 01 10 e9 9d  |...@..L.........|
0000c170  9e e5 b8 b8 e4 b8 8d e5  a5 bd 61 62 63 00 00 00  |..........abc...|
0000c180  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

varchar(N)

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

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

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

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

mysql> insert into t_varchar values('很不好abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_varchar values('非常不好abc');
Query OK, 1 row affected (0.00 sec)

mysql>  select a, length(a) from t_varchar;
+-----------------+-----------+
| a               | length(a) |
+-----------------+-----------+
| abc             |         3 |
| abc你好         |         9 |
| 很不好abc       |        12 |
| 非常不好abc     |        15 |
+-----------------+-----------+
4 rows in set (0.00 sec)

mysql> select a,hex(a) from t_varchar;
+-----------------+--------------------------------+
| a               | hex(a)                         |
+-----------------+--------------------------------+
| abc             | 616263                         |
| abc你好         | 616263E4BDA0E5A5BD             |
| 很不好abc       | E5BE88E4B88DE5A5BD616263       |
| 非常不好abc     | E99D9EE5B8B8E4B88DE5A5BD616263 |
+-----------------+--------------------------------+
4 rows in set (0.00 sec)

t_varchar表实际二进制存储文件


-- shell> hexdump -C t_char.idb
-- 和char一样观察，都没有进行空格的填充

0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|
0000c080  00 00 00 12 0f 00 00 40  18 08 17 fb 00 01 df 8a  |.......@........|
0000c090  01 10 61 62 63 09 00 00  00 18 00 23 00 00 00 00  |..abc......#....|
0000c0a0  12 10 00 00 40 18 08 18  fc 00 01 df b5 01 10 61  |....@..........a|
0000c0b0  62 63 e4 bd a0 e5 a5 bd  0c 00 00 00 20 00 26 00  |bc.......... .&.|
0000c0c0  00 00 00 12 11 00 00 40  18 08 1d ff 00 01 df 8b  |.......@........|
0000c0d0  01 10 e5 be 88 e4 b8 8d  e5 a5 bd 61 62 63 0f 00  |...........abc..|
0000c0e0  00 00 28 ff 8b 00 00 00  00 12 12 00 00 40 18 08  |..(..........@..|
0000c0f0  1e 80 00 01 df b6 01 10  e9 9d 9e e5 b8 b8 e4 b8  |................|
0000c100  8d e5 a5 bd 61 62 63 00  00 00 00 00 00 00 00 00  |....abc.........|
0000c110  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*

插入数据尾部带空格

mysql> insert into test_char values('好好好   ');  -- 后面有3个空格
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_varchar values('好好好   '); -- 后面有3个空格
Query OK, 1 row affected (0.02 sec)

-- 
-- t_char 表
--

mysql>  select a, length(a) from t_char;
+-----------------+-----------+
| a               | length(a) |
+-----------------+-----------+
| abc             |         3 |
| 你好哦          |         9 |
| 你好abc         |         9 |
| abc你好         |         9 |
| 很不好abc       |        12 |
| 很不abc好       |        12 |
| 非常不好abc     |        15 |
| 开黑中          |         9 |   --9个字节
| 你好啊          |         9 |
+-----------------+-----------+
9 rows in set (0.00 sec)

mysql> select a,hex(a) from t_char;
+-----------------+--------------------------------+
| a               | hex(a)                         |
+-----------------+--------------------------------+
| abc             | 616263                         |
| 你好哦          | E4BDA0E5A5BDE593A6             |
| 你好abc         | E4BDA0E5A5BD616263             |
| abc你好         | 616263E4BDA0E5A5BD             |
| 很不好abc       | E5BE88E4B88DE5A5BD616263       |
| 很不abc好       | E5BE88E4B88D616263E5A5BD       |
| 非常不好abc     | E99D9EE5B8B8E4B88DE5A5BD616263 |
| 开黑中          | E5BC80E9BB91E4B8AD             | 
| 你好啊          | E4BDA0E5A5BDE5958A             |   --无填充
+-----------------+--------------------------------+
9 rows in set (0.00 sec)

--
-- t_varchar表
--
mysql> insert into t_varchar values('你好啊   ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_varchar values('开黑中   ');
Query OK, 1 row affected (0.00 sec)

mysql>  select a, length(a) from t_varchar;
+-----------------+-----------+
| a               | length(a) |
+-----------------+-----------+
| abc             |         3 |
| abc你好         |         9 |
| 很不好abc       |        12 |
| 非常不好abc     |        15 |
| 你好啊          |        12 |  -- (你好啊)9个字节 +  3个字节的空格
| 开黑中          |        12 | 
+-----------------+-----------+
6 rows in set (0.00 sec)

mysql> select a,hex(a) from t_varchar;
+-----------------+--------------------------------+
| a               | hex(a)                         |
+-----------------+--------------------------------+
| abc             | 616263                         |
| abc你好         | 616263E4BDA0E5A5BD             |
| 很不好abc       | E5BE88E4B88DE5A5BD616263       |
| 非常不好abc     | E99D9EE5B8B8E4B88DE5A5BD616263 |
| 你好啊          | E4BDA0E5A5BDE5958A202020       |     -- 后面有20 20 20 ，表示3个自己的空格
| 开黑中          | E5BC80E9BB91E4B8AD202020       |   
+-----------------+--------------------------------+
6 rows in set (0.00 sec)

上面的现象无法用统一的规则进行表述，但是官方文档给出的解释是，这样的安排是为了避免索引页的碎片

3.BLOB和TEXT

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

mysql>  create table t_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 t_text(a int primary key, b text, key(b(64)));
Query OK, 0 rows affected (0.00 sec)

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

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

不建议在MySQL中存储大型的二进制数据，比如歌曲，视频

四. 字符集

1. 常见的字符集

utf8
utf8mb4
gbk
gb18030

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 | -- gbk，表示的字符有限
| 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 | -- utf8，最长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 | -- utf8 + mobile端字符
| 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 | -- gb18030,最长4个字节
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

2. collation

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

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |  -- 因为大小写无关，所以返回1
+-----------+
1 row in set (0.00 sec)

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

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

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

mysql> select * from t_ci where a = 'a';
+------+
| a    |
+------+
| a    |  
| A    |  -- A也被我们查到了
+------+
2 rows in set (0.00 sec)
mysql> select * from t_ci where a ='A';
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

mysql> select 'a' ='a   ';     --插入5个空格也会被查询到，这是个坑
+-------------+
| 'a' ='a   ' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

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

上面的情况如果从业务的角度上看，可以很好理解，比如创建一个用户叫做GAVIN，你是不希望再创建一个叫做gavin的用户

修改默认的collation

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)

字符集的指定，可以在创建数据库的时候指定，也可以在创建表的时候单独指定，也可以创建列的时候进行指定

五. 集合类型

集合类型ENUM 和 SET
ENUM类型最多允许65536个值
SET类型最多允许64个值
通过sql_mode参数可以用户约束检查

1. 集合类型的排序

mysql> create table t_col (user varchar(10),sex enum('male', 'female'));   -- 虽然写的是字符串，单其实存储的整型，效率还是可以的
    
mysql> insert into t_col values("fanghao","male");
Query OK, 1 row affected (0.02 sec)

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

mysql> insert into t_col values("yanglaoshi","female");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col values("banlaoshi","male");   --正常插入
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_col;
+------------+--------+
| user       | sex    |
+------------+--------+
| fanghao    | male   |
| xuliuyan   | male   |
| yanglaoshi | female |   
| banlaoshi  | male   |
+------------+--------+     
4 rows in set (0.00 sec)


mysql>  set sql_mode='';    -- 设置为sql_mode 空模式
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_col values("haozong","xmale");  --可以插入非法sql
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql>  set sql_mode='strict_trans_tables';   -- 设置为严格模式
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> insert into t_col values("xiaoxiaohao","xmale");  --插入报错
ERROR 1406 (22001): Data too long for column 'user' at row 1

强烈建议新业务上都设置成严格模式

2. 集合类型的排序

mysql> create table t_col_sort(user char(10),type enum('aaa','zzz','bbb','yyy','fff'));  -- aaa=0, zzz=1, bbb=2, yyy=3, fff=4
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t_col_sort values("user1","aaa");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col_sort values("user2","bbb");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col_sort values("user3","yyy");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col_sort values("user4","zzz");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_col_sort order by type asc; -- 以type作为key，进行升序排序
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |  -- 0
| user4 | zzz  |  -- 1
| user2 | bbb  |  -- 2
| user3 | yyy  |  -- 3
+-------+------+  
4 rows in set (0.00 sec)

-- 枚举类型实际是整型数据，按照插入顺序进行排列

mysql>  select * from t_col_sort;
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |
| user2 | bbb  |
| user3 | yyy  |
| user4 | zzz  |
+-------+------+
4 rows in set (0.00 sec)

--
-- 使用ascii排序
--
mysql> select * from t_col_sort order by cast(type as char) asc;  -- 使用cast()函数转换成某种型
+-------+------+                                                     -- 这里我们转成char型
| user  | type |                                                     -- 然后进行排序(ascii)
+-------+------+
| user1 | aaa  | -- 0
| user2 | bbb  | -- 2
| user3 | yyy  | -- 3
| user4 | zzz  | -- 1
+-------+------+
4 rows in set (0.00 sec)

-- 或者使用concat

mysql> select * from t_col_sort order by concat(type) asc;   -- concat()是连接字符串函数            
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |  -- 0
| user2 | bbb  |  -- 2
| user3 | yyy  |  -- 3
| user4 | zzz  |  -- 1
+-------+------+
4 rows in set (0.00 sec)

mysql> select concat("abc", "大家好");
+----------------------------+
| concat("abc", "大家好")    |
+----------------------------+
| abc大家好                  |
+----------------------------+
1 row in set (0.00 sec)

六. 日期类型

日期类型	占用空间(byte)（<5.6）	占用空间(byte)(>=5.6)	表示范围
`DATETIME`	8	5 + 微秒存储空间	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	3	1000-01-01 ~ 9999-12-31
`TIMESTAMP`	4	4 + 微秒存储空间	1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR	1	1	YEAR(2):1970-2070, YEAR(4):1901-2155
TIME	3	3 + 微秒存储空间	-838:59:59 ~ 838:59:59

微秒位数	所需存储空间
0	0
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

TIMESTAMP 带时区功能

1. TIMESTAMP和DATETIME

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

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

mysql> select * from t_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2017-11-29 10:48:04 | 2017-11-29 10:48:04 |
+---------------------+---------------------+
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 * from t_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2017-11-29 02:48:04 | 2017-11-29 10:48:04 |  -- 相差8个小时，时区的差别体现出来了
+---------------------+---------------------+
1 row in set (0.00 sec)

2. 微秒

从MySQL5.6.X开始，支持微秒，最大显示6位


mysql>  select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2017-11-29 02:53:29.019376 |  -- （019376） 6位微秒显示
+----------------------------+
1 row in set (0.00 sec)

mysql> select now(7);
ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.  -- 不支持，最大到6位

mysql> create table t_time_fac (t datetime(6));
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from t_time_fac;
+----------------------------+
| t                          |
+----------------------------+
| 2017-11-29 02:59:03.945431 |  -- 由于是用了6位微秒位数，根据表格显示，实际存储的空间是 5 + 3 = 8 byte
+----------------------------+
1 row in set (0.00 sec)

3. 时间函数

常用函数

函数名	函数说明	备注
NOW	返回`SQL执行时`的时间	如果不考虑其他因素，可以理解为写完SQL，敲下回车瞬间的时间
CURRENT_TIMESTAMP	与NOW()函数同义
SYSDATE	返回`函数执行时`的时间	MySQL处理你的函数时的时间，统一SQL语句中，大于NOW
DATA_ADD(date, interval expr uint)	增加时间
DATA_SUB(date, interval expr uint)	减少时间	可用ADD，然后unit给负数
DATE FORMAT	格式化时间

所有时间函数--官方文档


--
-- NOW和SYSDATE的区别
--
mysql> select now(),sysdate();
+---------------------+---------------------+
| now()               | sysdate()           |
+---------------------+---------------------+
| 2017-11-29 11:04:58 | 2017-11-29 11:04:58 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now(),sysdate(),sleep(2),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now()               | sysdate()           | sleep(2) | now()               | sysdate()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2017-11-29 11:05:49 | 2017-11-29 11:05:49 |        0 | 2017-11-29 11:05:49 | 2017-11-29 11:05:51 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (2.00 sec)

mysql> select  now(6),sysdate(6);
+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+
| 2017-11-29 11:07:44.038222 | 2017-11-29 11:07:44.038360 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)


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

-----

--
-- date_add
--

mysql> select date_add(now(),interval 7 day);    -- 增加7天
+--------------------------------+
| date_add(now(),interval 7 day) |
+--------------------------------+
| 2017-12-06 11:09:28            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 month);  --增量1个月
+----------------------------------+
| date_add(now(),interval 1 month) |
+----------------------------------+
| 2017-12-29 11:10:06              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval -2 month);  --减少2个月
+-----------------------------------+
| date_add(now(),interval -2 month) |
+-----------------------------------+
| 2017-09-29 11:10:18               |
+-----------------------------------+
1 row in set (0.00 sec)


--
-- date_format
--
mysql> SELECT DATE_FORMAT((select now(6)), '%H:%i:%s');
+------------------------------------------+
| DATE_FORMAT((select now(6)), '%H:%i:%s') |
+------------------------------------------+
| 11:11:20                               |
+------------------------------------------+
1 row in set (0.00 sec)

4. 字段更新时间

mysql> create  table t_field_update(a int(32), b timestamp not null default current_timestamp on update current_timestamp);

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

mysql>select * from t_field_update;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2017-11-29 11:17:48 |  -- 上面使用了now(6)，但是这里没有微秒，是因为定义的时候就是timestamp
+------+---------------------+  -- 如果写成timestamp(6),就可以显示微秒
1 row in set (0.00 sec)

mysql> update t_field_update set a=10 where a=1;  -- 只更新a字段
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_field_update;    
+------+---------------------+
| a    | b                   |
+------+---------------------+
|   10 | 2017-11-29 11:18:39 |  -- 发现b字段跟着改变了
+------+---------------------+
1 row in set (0.00 sec)

--
-- 测试timestamp(6)
--
mysql> create table t_time_disp(a int(10),b timestamp(6) not null default current_timestamp(6) on update current_timestamp(6));   -- 定义了微妙

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

mysql> select * from t_time_disp;
+------+----------------------------+
| a    | b                          |
+------+----------------------------+
|    1 | 2017-11-29 11:19:53.377309 |    -- 插入了now(6), 这里就显示了6位微秒
+------+----------------------------+
1 row in set (0.00 sec)

面试题

MySQL 中varchar 与char 的区别以及varchar(50)中的50 代表的涵义？
(1)、varchar 与char 的区别
在单字节字符集下， char（ N） 在内部存储的时候总是定长，而且没有变长字段长度列表中。在多字节字符
集下面， char(N)如果存储的字节数超过N，那么char（ N）将和varchar（ N）没有区别。在多字节字符
集下面，如果存储的字节数少于N，那么存储N 个字节，后面补空格，补到N 字节长度。都存储变长的数据和变长字段长度列
表。varchar(N)无论是什么字节字符集，都是变长的，即都存储变长数据和变长字段长度列表。
(2)、varchar(50)中50 的涵义
最多存放50 个字符，varchar(50)和(200)存储hello 所占空间一样，但后者在排序时会消耗更多内存，因为
order by col 采用fixed_length 计算col 长度(memory 引擎也一样)。在早期MySQL 版本中， 50 代表字节数，现在代表字符数。
(3)、int（20）中20 的涵义
是指显示字符的长度
不影响内部存储，只是影响带zerofill 定义的int 时，前面补多少个0，易于报表展示
(4)、mysql 为什么这么设计
对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

六. MySQL JSON类型

1. JSON介绍

什么是 JSON ？
- JSON 指的是 JavaScript 对象表示法（JavaScript Object Notation）
- JSON 是轻量级的文本数据交换格式
- JSON 独立于语言
- JSON 具有自我描述性，更易理解
MySQL5.7.8开始支持JSON数据类型。
对比存储在字符串，JSON格式的JSON数据类型提供了这些优点：
- 自动验证存储在JSON列中的JSON文档。无效的文档会产生错误。
- 优化的存储格式

官方文档(JSON类型)

2. JSON格式示例

--这个 employee 对象是包含 3 个员工记录（对象）的数组。
{
"employees": [
{ "firstName":"John" , "lastName":"Doe" }, 
{ "firstName":"Anna" , "lastName":"Smith" }, 
{ "firstName":"Peter" , "lastName":"Jones" }
]
}

3. JSON VS BLOB

JSON
- JSON数据可以做有效性检查;
- JSON使得查询性能提升;
- JSON支持部分属性索引，通过虚拟列的功能可以对JSON中的部分数据进行索引;
BLOB
- BLOB类型无法在数据库层做约束性检查;
- BLOB进行查询，需要遍历所有字符串;
- BLOB做只能做指定长度的索引;

5.7之前，只能把JSON当作BLOB进行存储。数据库层面无法对JSON数据做一些操作，只能由应用程序处理。

4.结构化和非结构化

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

--
-- SQL创建User表
--
create table user (
    id bigint not null auto_increment,
    user_name varchar(10),
    age int,
    primary key(id)
);

#
# JSON定义的User表
#

db.user.insert({
    user_name:"xiaowen",
    age:30
})

db.createCollection("user")

5. JSON操作示例

5.1 JSON入门

--
-- 创建带json字段的表
--
CREATE TABLE t_user (
    uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    email VARCHAR(128) NOT NULL,
    address VARCHAR(256) NOT NULL,
    UNIQUE KEY (name),
    UNIQUE KEY (email) 
)charset=utf8mb4;

mysql> INSERT INTO t_user VALUES (NULL,'Gavin','gczheng@139','Shanghai ...'),(NULL,'midou','midou@mail','Beijing ...'),(NULL,'xiaowen','xiao@mail','Guangzhou ...');
INSERT INTO t_user VALUES (NULL,'midou','midou@mail','Beijing ...');
INSERT INTO t_user VALUES (NULL,'xiaowen','xiao@mail','Guangzhou ...');
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected


mysql> select * from t_user;
+-----+-------+-------------+---------------+
| uid | name  | email       | address       |
+-----+-------+-------------+---------------+
|   1 | gczheng | gczheng@139.com | Shanghai ...  |
|   2 | midou   | midou@139.com   | Beijing ...   |
|   3 | xiaowen   | xiaowen@139.com   | Guangzhou ... |
+-----+-------+-------------+---------------+
3 rows in set

mysql> DROP TABLE IF EXISTS t_user_json;
    CREATE TABLE t_user_json(
	uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data JSON
);


-- JSON_OBJECT将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value)

mysql> insert into t_user_json 
SELECT uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data
FROM t_user;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user_json;
+-----+----------------------------------------------------------------------+
| uid | data                                                                 |
+-----+----------------------------------------------------------------------+
|   1 | {"name": "gczheng", "email": "gczheng@139.com", "address": "Shanghai ..."} |
|   2 | {"name": "midou", "email": "midou@139.com", "address": "Beijing ..."}      |
|   3 | {"name": "xiaowen", "email": "xiaowen@139.com", "address": "Guangzhou ..."}    |
+-----+----------------------------------------------------------------------+
3 rows in set

--JSON_EXTRACT() 是JSON提取函数，$.address 就是一个 JSON path，表示定位文档的 address 字段
JSON path 


mysql> SELECT uid,JSON_EXTRACT(data,'$.address') from t_user_json;
+-----+--------------------------------+
| uid | JSON_EXTRACT(data,'$.address') |
+-----+--------------------------------+
|   1 | "Shanghai ..."                 |
|   2 | "Beijing ..."                  |
|   3 | "Guangzhou ..."                |
+-----+--------------------------------+
3 rows in set


mysql> SELECT uid,JSON_EXTRACT(data,'$.address2') from t_user_json;
+-----+---------------------------------+
| uid | JSON_EXTRACT(data,'$.address2') |
+-----+---------------------------------+
|   1 | NULL                            |
|   2 | NULL                            |
|   3 | NULL                            |
+-----+---------------------------------+
3 rows in set

--JSON_INSERT() 是JSON 插入函数

mysql>  UPDATE t_user_json set data = json_insert(data,"$.address2","HangZhou ...") where uid = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT uid,JSON_EXTRACT(data,'$.address1') from t_user_json;
+-----+---------------------------------+
| uid | JSON_EXTRACT(data,'$.address1') |
+-----+---------------------------------+
|   1 | NULL                            |
|   2 | NULL                            |
|   3 | NULL                            |
+-----+---------------------------------+
3 rows in set


mysql> SELECT * from t_user_json;
+-----+--------------------------------------------------------------------------------------------------+
| uid | data                                                                                             |
+-----+--------------------------------------------------------------------------------------------------+
|   1 | {"name": "gczheng", "email": "gczheng@139.com", "address": "Shanghai ...", "address2": "HangZhou ..."} |
|   2 | {"name": "midou", "email": "midou@139.com", "address": "Beijing ..."}                                  |
|   3 | {"name": "xiaowen", "email": "xiaowen@139.com", "address": "Guangzhou ..."}                                |
+-----+--------------------------------------------------------------------------------------------------+
3 rows in set


-- JSON_MERGE将两个或以上的JSON对象融合 

mysql> select json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2')) 
from t_user_json;
+-----------------------------------------------------------------------------+
| json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2')) |
+-----------------------------------------------------------------------------+
| ["Shanghai ...", "HangZhou ..."]                                            |
| NULL                                                                        |
| NULL                                                                        |
+-----------------------------------------------------------------------------+
3 rows in set



mysql> begin;
UPDATE t_user_json set data = json_array_append(data,"$.address",JSON_EXTRACT(data,'$.address2'))
where JSON_EXTRACT(data,'$.address2') IS NOT NULL AND uid >0;
select JSON_EXTRACT(data,'$.address') from t_user_json;
UPDATE t_user_json set data = JSON_REMOVE(data,'$.address2') where uid>0;
commit;
Query OK, 0 rows affected

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
+----------------------------------+
| JSON_EXTRACT(data,'$.address')   |
+----------------------------------+
| ["Shanghai ...", "HangZhou ..."] |
| "Beijing ..."                    |
| "Guangzhou ..."                  |
+----------------------------------+
3 rows in set

Query OK, 1 row affected
Rows matched: 3  Changed: 1  Warnings: 0
Query OK, 0 rows affected

5.2 JSON常用函数介绍

create table demo(id int unsigned primary key auto_increment,comment json);
insert into demo(id,name) values(1,'{"programmers": [{"email": "aaaa", "lastName": "McLaughlin", "firstName": "Brett"}, {"email": "bbbb", "lastName": "Hunter", "firstName": "Jason"}]}');

-- 检查第一层的key值 json_keys
-- select json_keys(comment) from demo


-- 从JSON中提取 json_extract
-- select json_extract(comment,'$.programmers[0].email') from demo 


-- 从Json中去除元素 json_remove
-- select json_extract(comment,'$.programmers'),json_remove(comment,'$.programmers[0]') from demo


-- Json是否包含当前路径 json_contains_path
-- select json_contains_path(comment,"all","$.programmers[0].firstName") from demo 


-- 判断JSON当前路径对象类型 JSON_TYPE
-- select JSON_TYPE(comment),JSON_TYPE(comment->"$.programmers[0].firstName"),JSON_TYPE(comment->"$.programmers")="ARRAY" from demo


-- 创建数组对象 JSON_ARRAY
-- SELECT JSON_ARRAY('a', 1, RAND());


-- 将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value) JSON_OBJECT
-- SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'),JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def'); 


-- 将两个或以上的JSON对象融合 JSON_MERGE
-- SELECT JSON_MERGE('["a", 1]', '{"key": "value"}'),JSON_MERGE('{"a": 2,"b":"2"}','{"key": "value"}'),JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');


-- 用户定义的变量不能JSON数据类型 函数 COLLATION(返回字符串参数的排序方式)
-- SET @j = JSON_OBJECT('key', 'value'); SELECT CHARSET(@j), COLLATION(@j);


-- 因为utf8mb4_bin是二进制排序规则，JSON值比较是区分大小写的。
-- SELECT JSON_ARRAY('x') = JSON_ARRAY('X');


-- JSON对大小写敏感,SQL非空不敏感
-- SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL'),ISNULL(null), ISNULL(Null), ISNULL(NULL);


-- 将JSON中元素替换;如果位置不存在，则追加 JSON_SET
-- SET @j = '["a", {"b": [true, false]}, [10, 20]]';SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][3]', 3);


-- 向JSON中添加元素,原来位置存在数据不会替换 JSON_INSERT
-- SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);


-- 替换JSON中原有值,不存在的不会替换 JSON_REPLACE
-- SELECT JSON_REPLACE(@j, '$[1].b[0]', JSON_OBJECT('key', 'value'), '$[2][2]', 2);


-- 移除JSON元素列表 JSON_REMOVE
-- SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');


-- 判断JSON中是否包含'值',在这个路径下 JSON_CONTAINS
-- SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a');


-- 去掉JSON格式""号 json_unquote , ->> 这两个是等价的
-- select json_unquote(comment->"$.programmers[1].email"), comment->>"$.programmers[1].email"from demo

--
-- json_remove 从json记录中删除数据
-- 原型 : JSON_REMOVE(json_doc, path[, path] ...)
--
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的元素["b", "c"]
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select * from user;
+-----+------------------------------------------------------+
| uid | data                                                 |
+-----+------------------------------------------------------+
|   1 | {"age": 18, "name": "xiaowen", "address": ["SZ", "BJ"]}  |  -- address_2 的字段删除了
|   2 | {"age": 28, "mail": "xuge@163.com", "name": "xuge"}    |
|   4 | {"age": 33, "name": "fanghao", "email": "fanghao@163.com"} |
+-----+------------------------------------------------------+
3 rows in set (0.00 sec)

官方文档(JSON函数)

5.3 JSON创建索引

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

官方文档--JSON创建索引

新建表时创建JSON索引

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

mysql> show create table test_index_1；
-- -----省略表格线-----
| test_index_1 | CREATE TABLE `test_index_1` (
  `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)

mysql> insert into test_index_1(data) values ('{"name":"xiaowen", "age":18, "address":"SH"}');
Query OK, 1 row affected (0.04 sec)

mysql> insert into test_index_1(data) values ('{"name":"xuge", "age":28, "address":"SZ"}');      
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_index_1;
+---------------------------------------------+---------+
| data                                        | gen_col |
+---------------------------------------------+---------+
| {"age": 18, "name": "xiaowen", "address": "SH"} | "xiaowen"   |
| {"age": 28, "name": "xuge", "address": "SZ"} | "xuge"   |
+---------------------------------------------+---------+
2 rows in set (0.00 sec)

mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col="xiaowen";  -- 如果这样做，为空，原因如下
Empty set (0.00 sec)

mysql> select hex('"');
+----------+
| hex('"') |
+----------+
| 22       |  -- 双引号的 16进制
+----------+
1 row in set (0.00 sec)

mysql> select hex(gen_col) from test_index_1;
+--------------+
| hex(gen_col) |
+--------------+
| 226A696D22   |  -- 双引号本身也作为了存储内容
| 22746F6D22   |
+--------------+
2 rows in set (0.00 sec)

mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col='"xiaowen"';  -- 使用'"xiaowene"',用单引号括起来
+----------+
| username |
+----------+
| "xiaowen"    |  -- 找到了对应的数据
+----------+
1 row in set (0.00 sec)

mysql> explain select json_extract(data,"$.name") as username from test_index_1 where gen_col='"xiaowen"'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_1
   partitions: NULL
         type: ref 
possible_keys: idx    -- 使用了 key idx
          key: idx
      key_len: 43
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

---
--- 建立表的时候去掉双引用
---

mysql> create table test_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.13 sec)

mysql> show create table test_index_2;
-- -----省略表格线-----
| test_index_2 | CREATE TABLE `test_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.00 sec)

mysql> insert into test_index_2(data) values ('{"name":"xiaowen", "age":18, "address":"SH"}');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_index_2(data) values ('{"name":"xuge", "age":28, "address":"SZ"}');
Query OK, 1 row affected (0.02 sec)

mysql> select json_extract(data,"$.name") as username from test_index_2 where gen_col="xiaowen";  -- 未加单引号
+----------+
| username |
+----------+
| "xiaowen"    |  -- 可以找到数据
+----------+
1 row in set (0.00 sec)

mysql> explain select json_extract(data,"$.name") as username from test_index_2 where gen_col="xiaowen"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_2
   partitions: NULL
         type: ref
possible_keys: idx   -- 使用了 key idx
          key: idx
      key_len: 43
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

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

--
-- 使用之前的user表操作
--
mysql> show create table user;
-- -----省略表格线-----
| user  | CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
-- -----省略表格线-----
1 row in set (0.00 sec)

mysql> select * from user;
+-----+------------------------------------------------------+
| uid | data                                                 |
+-----+------------------------------------------------------+
|   1 | {"age": 18, "name": "xiaowen", "address": ["SZ", "BJ"]}  |
|   2 | {"age": 28, "mail": "xuge@163.com", "name": "xuge"}    |
|   4 | {"age": 33, "name": "fanghao", "email": "fanghao@163.com"} |
+-----+------------------------------------------------------+

mysql> alter table user 
    -> add user_name varchar(32)
    -> generated always as (json_extract(data,"$.name")) virtual;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- virtual 关键字是不将该列的字段值存储，对应的是stored

mysql> select user_name from user;  
+-----------+
| user_name |
+-----------+
| "xiaowen"     |
| "xuge"     |
| "fanghao"    |
+-----------+
3 rows in set (0.00 sec)

mysql> alter table user add index idx(user_name);          
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user where user_name='"xiaowen"';  -- 加单引号
+-----+-----------------------------------------------------+-----------+
| uid | data                                                | user_name |
+-----+-----------------------------------------------------+-----------+
|   1 | {"age": 18, "name": "xiaowen", "address": ["SZ", "BJ"]} | "xiaowen"     |
+-----+-----------------------------------------------------+-----------+
1 row in set (0.00 sec)

mysql> explain select * from user where user_name='"xiaowen"'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: idx   -- 使用了 key 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 user;
-- -----省略表格线-----
| user  | CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  `user_name` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL,
  `user_name2` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL,
  PRIMARY KEY (`uid`),
  KEY `idx` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
-- -----省略表格线-----
1 row in set (0.00 sec)

6.附录

--
-- 姜boss演示JSON的SQL
--
drop table if exists User;

CREATE TABLE User (
    uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    email VARCHAR(256) NOT NULL,
    address VARCHAR(512) NOT NULL,
    UNIQUE KEY (name),
    UNIQUE KEY (email)
);

INSERT INTO User VALUES (NULL,'gczheng','gczheng@139.com','Shanghai ...');
INSERT INTO User VALUES (NULL,'midou','midou@139.com','Beijing ...');
INSERT INTO User VALUES (NULL,'xiaowen','xiaowen@139.com','Guangzhou ...');

SELECT * FROM User;

ALTER TABLE User ADD COLUMN address2 VARCHAR(512) NOT NULL;
ALTER TABLE User ADD COLUMN passport VARCHAR(64) NOT NULL;

DROP TABLE IF EXISTS UserJson;

CREATE TABLE UserJson(
	uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

truncate table UserJson;

insert into UserJson 
SELECT 
    uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data
FROM
    User;
    
SELECT * FROM UserJson; 

SELECT uid,JSON_EXTRACT(data,'$.address2') from UserJson;
    
UPDATE UserJson
set data = json_insert(data,"$.address2","HangZhou ...")
where uid = 1;

SELECT JSON_EXTRACT(data,'$.address[1]') from UserJson;

select json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2')) 
from UserJson;

begin;
UPDATE UserJson
set data = json_array_append(data,"$.address",JSON_EXTRACT(data,'$.address2'))
where JSON_EXTRACT(data,'$.address2') IS NOT NULL AND uid >0;
select JSON_EXTRACT(data,'$.address') from UserJson;
UPDATE UserJson
set data = JSON_REMOVE(data,'$.address2')
where uid>0;
commit;

posted @ 2017-11-27 18:01 貔貅小米豆阅读(264) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

米豆小书童

小弟读过两年书,尘世中一米豆小书童

第6章：MySQL之数据类型

第6章：MySQL之数据类型

一. INT类型

1. INT类型的分类

2. INT类型的使用

3. INT(N)

4. AUTO_INCREMENT

二. 数字类型

1. 数字类型的分类

三. 字符串类型

1. 字符串类型介绍

2. N和字符集

3.BLOB和TEXT

四. 字符集

1. 常见的字符集

2. collation

五. 集合类型

1. 集合类型的排序

2. 集合类型的排序

六. 日期类型

1. TIMESTAMP和DATETIME

2. 微秒

3. 时间函数

4. 字段更新时间

面试题

六. MySQL JSON类型

1. JSON介绍

2. JSON格式示例

3. JSON VS BLOB

4.结构化和非结构化

5. JSON操作示例

5.1 JSON入门

5.2 JSON常用函数介绍

5.3 JSON创建索引

6.附录

公告