INT类型
| 类型 | 占用空间 | 最小值 | 最大值 |
|---|---|---|---|
| （字节） | （Signed/Unsigned） | （Signed/Unsigned） | |
| 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 |
INT类型的属性
-
UNSIGNED / SIGNED
- 是否有符号
-
简单举例
(root@localhost) [db1]> create table tb1 (a int(5) signed,b tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [db1]> desc tb1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| a | int(5) | YES | | NULL | |
| b | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
(root@localhost) [db1]> insert into tb1 values(-1,1);
Query OK, 1 row affected (0.01 sec)
(root@localhost) [db1]> select * from tb1;
+------+------+
| a | b |
+------+------+
| -1 | 1 |
+------+------+
1 row in set (0.00 sec)
# 对unsigned插入-1会报错
(root@localhost) [db1]> insert into tb1 values(-1,-1);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
- ZEROFILL
- 填充显示宽度，不设置则
int(5)不生效 - 值不做任何修改
- 填充显示宽度，不设置则
(root@localhost) [db1]> delete from tb1;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [db1]> alter table tb1 change column a a int(5) unsigned zerofill;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [db1]> desc tb1;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| a | int(5) unsigned zerofill | YES | | NULL | |
| b | tinyint(3) unsigned | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
(root@localhost) [db1]> insert into tb1 values(1,2);
Query OK, 1 row affected (0.01 sec)
(root@localhost) [db1]> select * from tb1;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+
1 row in set (0.00 sec)
- 仅仅是一个显示属性，超出宽度则直接显示
(root@localhost) [db1]> insert into tb1 values(12000000,2);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [db1]> select * from tb1;
+----------+------+
| a | b |
+----------+------+
| 00001 | 2 |
| 12000000 | 2 |
+----------+------+
2 rows in set (0.01 sec)
- 另外对负数是不被允许的，会报溢出
-
AUTO_INCREMENT
- 主键建议直接
BIGINT拉满，不然后期满了不好扩，会很慢 - 自增的属性
- 每张表一个
- 必须是索引的一部分
- 主键建议直接
-
简单举例
(root@localhost) [db1]> truncate tb1;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [db1]> alter table tb1 change column a a int(3) auto_increment primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [db1]> show create table tb1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`a` int(3) NOT NULL AUTO_INCREMENT,
`b` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> insert into tb1 values(NULL,10);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [db1]> insert into tb1 values(NULL,20);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [db1]> select * from tb1;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 20 |
+---+------+
2 rows in set (0.00 sec)
last_insert_id()：显示最后一次自增的值
(root@localhost) [db1]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.01 sec)
(root@localhost) [db1]> insert into tb1(b) values(30);
Query OK, 1 row affected (0.01 sec)
(root@localhost) [db1]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
-
关于AUTO_INCREMENT属性的回溯问题(5.7版本下)
- 正常运行过程中删除一行是不会回溯的，只会一直增
- 但是重启过后会回溯，即原本最后一次删除的那些主键重启后又出现了
- 原因是重启后
AUTO_INCREMENT的值为select max(auto_increment column) + 1 from table，即当前主键的最值+1为最新的自增值
-
举例说明
- 删除第3行和第5行，此时
AUTO_INCREMENT=6
(root@localhost) [db1]> select * from tb1;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
+---+------+
5 rows in set (0.00 sec)
(root@localhost) [db1]> delete from tb1 where a='3';
Query OK, 1 row affected (0.02 sec)
(root@localhost) [db1]> delete from tb1 where a='5';
Query OK, 1 row affected (0.01 sec)
(root@localhost) [db1]> show create tables tb1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables tb1' at line 1
(root@localhost) [db1]> show create table tb1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`a` int(3) NOT NULL AUTO_INCREMENT,
`b` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 重启数据库，发现
AUTO_INCREMENT=5，再次插入数据发现回溯到了5号
(root@localhost) [(none)]> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
(root@localhost) [db1]> show create table tb1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`a` int(3) NOT NULL AUTO_INCREMENT,
`b` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> select * from tb1;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 20 |
| 4 | 40 |
+---+------+
3 rows in set (0.00 sec)
(root@localhost) [db1]> insert into tb1(b) values(60);
Query OK, 1 row affected (0.01 sec)
(root@localhost) [db1]> select * from tb1;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 20 |
| 4 | 40 |
| 5 | 60 |
+---+------+
4 rows in set (0.00 sec)
- MySQL8.0以后，
AUTO_INCREMENT不会再回溯，因为已经持久化
INT类型 小总结
- 推荐不要使用UNSIGNED
- 范围本质上没有大的改变
- 可能会有溢出现象发生
- 自增INT类型主键建议使用
BIGINT- 自增不会回溯，随着数据量增长后期很难调整
数字类型
- 单精度类型：FLOAT
- 双精度类型：DOUBLE
- 高精度类型：DECIMAL
| 类型 | 占用空间 | 精度 | 精确性 |
|---|---|---|---|
| FLOAT | 4 | 单精度 | 低 |
| DOUBLE | 8 | 双精度 | 低，比FLOAT高 |
| DECIMAL | 变长 | 高精度 | 非常高 |
- 单精度和双精度类型精确性较低，M*G/G不一定等于M。
- 建议直接使用DECIMAL省事还好用
- FLOAT(M,D)/DOUBLE(M,D)/DECIMAL(M,D)表示显示M位整数，其中D位位于小数点后面
- 财务、账务系统必须用DECIMAL类型
相关函数
floor()：向下取整
(root@localhost) [db1]> select floor(1.5),floor(-1.5);
+------------+-------------+
| floor(1.5) | floor(-1.5) |
+------------+-------------+
| 1 | -2 |
+------------+-------------+
1 row in set (0.00 sec)
round()：四舍五入
(root@localhost) [db1]> select round(1.4),round(-1.5);
+------------+-------------+
| round(1.4) | round(-1.5) |
+------------+-------------+
| 1 | -2 |
+------------+-------------+
1 row in set (0.00 sec)
rand()：产生一个0-1之间的随机数floor(i + rand()*(j-i))：用于产生一个i到j的随机值
(root@localhost) [db1]> select rand(),floor(1+rand()*99);
+--------------------+--------------------+
| rand() | floor(1+rand()*99) |
+--------------------+--------------------+
| 0.8705232766182379 | 22 |
+--------------------+--------------------+
1 row 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
指定默认字符集
- 查看MySQL支持的字符集
show charset;
(root@localhost) [db1]> show charset;
+----------+---------------------------------+---------------------+--------+
| 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 |
# ......太长了，不放上去了
- 现在就推荐一种字符集
utf8mb4 - 写入配置文件中
# vim /etc/my.cnf
[mysqld]
...
#charset
character_set_server=utf8mb4
- 修改表字符集为utf8mb4
alter table tablename charset=utf8mb4;：此方法不会修改之前已经创建的列，只会修改后面添加的列
(root@localhost) [db1]> alter table tb2 charset=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [db1]> show create table tb2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2 | CREATE TABLE `tb2` (
`a` char(10) CHARACTER SET utf8 DEFAULT NULL,
`b` varchar(15) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> alter table tb2 add column c varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [db1]> show create table tb2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2 | CREATE TABLE `tb2` (
`a` char(10) CHARACTER SET utf8 DEFAULT NULL,
`b` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
`c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
alter table tablename convert to character set utf8mb4;：此方法会连同之前创建的列一同修改为utf8mb4- 转换操作会锁住整张表，线上可能会导致阻塞，谨慎使用
(root@localhost) [db1]> show create table tb2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2 | CREATE TABLE `tb2` (
`a` char(10) CHARACTER SET utf8 DEFAULT NULL,
`b` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
`c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> alter table tb1 convert to character set utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [db1]> show create table tb2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2 | CREATE TABLE `tb2` (
`a` char(10) CHARACTER SET utf8 DEFAULT NULL,
`b` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
`c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 字符集可以设置到列，但是应用场景很少，了解即可
相关函数
- 查看字节长度和字符长度（面试喜欢问字符类型里面N代表是字节还是字符）
(root@localhost) [db1]> select length('你'),char_length('你');
+---------------+--------------------+
| length('你') | char_length('你') |
+---------------+--------------------+
| 3 | 1 |
+---------------+--------------------+
1 row in set (0.00 sec)
hex()查看对应字符集下的16进制编码
(root@localhost) [db1]> select hex('我');
+------------+
| hex('我') |
+------------+
| E68891 |
+------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> select 0xE68891;
+----------+
| 0xE68891 |
+----------+
| 我 |
+----------+
1 row in set (0.00 sec)
# 插入也可以使用十六进制的值插入
(root@localhost) [db1]> insert into tb2 values(0xE68891,0xE68892,'c');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [db1]> select * from tb2;
+------+------+------+
| a | b | c |
+------+------+------+
| 你 | b | c |
| 我 | 戒 | c |
+------+------+------+
2 rows in set (0.00 sec)
cast(expression AS TYPE)：将任何类型的值转换为具有指定类型的值
(root@localhost) [db1]> select cast('你' as char(5) charset'gbk');
+-------------------------------------+
| cast('你' as char(5) charset'gbk') |
+-------------------------------------+
| 你 |
+-------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> select hex(cast('你' as char(5) charset'gbk'));
+------------------------------------------+
| hex(cast('你' as char(5) charset'gbk')) |
+------------------------------------------+
| C4E3 |
+------------------------------------------+
1 row in set (0.00 sec)
# 插入不同字符集的列中，这里可能会存在不同字符集乱码的问题
(root@localhost) [db1]> insert into tb2 values(0xC4E3,'x','x');
ERROR 1366 (HY000): Incorrect string value: '\xC4\xE3' for column 'a' at row 1
-
upper()：将字符串转换为大写lower()：将字符串转换为小写
(root@localhost) [db1]> select upper('aBc'),lower('AbC');
+--------------+--------------+
| upper('aBc') | lower('AbC') |
+--------------+--------------+
| ABC | abc |
+--------------+--------------+
1 row in set (0.01 sec)
-
concat()：用于字符串的连接concat_ws(分隔符,字符串....)：用指定的分隔符连接各字符串
(root@localhost) [db1]> select concat('hello','sql'),concat_ws(',','hello','world');
+-----------------------+--------------------------------+
| concat('hello','sql') | concat_ws(',','hello','world') |
+-----------------------+--------------------------------+
| hellosql | hello,world |
+-----------------------+--------------------------------+
1 row in set (0.00 sec)
repeat(字符串,次数)：将指定字符串重复指定的次数
(root@localhost) [db1]> select repeat('a',8);
+---------------+
| repeat('a',8) |
+---------------+
| aaaaaaaa |
+---------------+
1 row in set (0.00 sec)
-
lpad(字符串,长度,填充符)用指定填充符将字符串左填充到指定长度rpad(字符串,长度,填充符)从右边填充
(root@localhost) [db1]> select lpad('aaa',8,'*'),rpad('bbb',9,'*');
+-------------------+-------------------+
| lpad('aaa',8,'*') | rpad('bbb',9,'*') |
+-------------------+-------------------+
| *****aaa | bbb****** |
+-------------------+-------------------+
1 row in set (0.00 sec)
字符串排序规则
- 对于binary来说，都是二进制，比较规则没有很大的意义
- 但是对于字符串来说，有以下一些比较规则(collation)
- 不区分大小写
- 默认去除后面多余空格
(root@localhost) [db1]> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
(root@localhost) [db1]> select 'a'='A ';
+---------------+
| 'a'='A ' |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
(root@localhost) [db1]> create table tb3(a char(1) unique key);
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [db1]> insert into tb3 values('a');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [db1]> insert into tb3 values('A');
ERROR 1062 (23000): Duplicate entry 'A' for key 'a'
- 在字段后添加
collate utf8mb4_bin区分大小写 - md5与加盐
- 用户密码不能直接存储，不安全，可以使用md5来进行加密
- 但常用密码的md5值往往有“彩虹表”记录，所以可以在密码后添加固定的字符串一起使用md5加密，称之为加盐
枚举类型(ENUM&SET)
- 字符串类型――集合类型
- ENUM类型最多允许65536个值SET类型最多允许64个值
- 通过sql_mode参数可以用于约束检查
- 例如在性别字段上可以设置
sex enum('male','female')
日期类型
| 占用字节 | 表示范围 | |
|---|---|---|
| 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 : 00 UTC~2038-01-19 03:14:07 UTC |
| YEAR | 1 | YEAR(2): 1970~2070 YEAR(4): 1901~2155 |
| TIME | 3 | -838 : 59 : 59~838 :59 : 59 |
-
MySQL 5.6.4版本开始支持微秒
-
支持类型TIME、DATETIME、TIMESTAMP
-
type_name(fsp) fsp最大为6
(root@localhost) [db1]> select now(6),now(3); +----------------------------+-------------------------+ | now(6) | now(3) | +----------------------------+-------------------------+ | 2022-04-13 08:53:39.055289 | 2022-04-13 08:53:39.055 | +----------------------------+-------------------------+ 1 row in set (0.00 sec)
-
-
TIMESTAMP可以设置时区(root@localhost) [db1]> create table tb4 (day1 datetime,day2 timestamp); Query OK, 0 rows affected (0.01 sec) (root@localhost) [db1]> insert into tb4 values(now(),now()); Query OK, 1 row affected (0.00 sec) (root@localhost) [db1]> select * from tb4; +---------------------+---------------------+ | day1 | day2 | +---------------------+---------------------+ | 2022-04-13 08:55:47 | 2022-04-13 08:55:47 | +---------------------+---------------------+ 1 row in set (0.00 sec) (root@localhost) [db1]> set time_zone='+0:00'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [db1]> select * from tb4; +---------------------+---------------------+ | day1 | day2 | +---------------------+---------------------+ | 2022-04-13 08:55:47 | 2022-04-13 00:55:47 | +---------------------+---------------------+ 1 row in set (0.00 sec)
相关函数
| 函数名 | 函数说明 |
|---|---|
| NOW | 返回SQL执行时的时间 |
| CURRENT_TIMESTAMP | 与NOW函数同义 |
| SYSDATE | 返回执行函数时的时间 |
| DATE_ADD(date,INTERVAL expr unit) | 增加时间 |
| DATE_SUB(date,lNTERVAL expr unit) | 减少时间 |
| DATE_FORMAT | 格式化时间显示 |
-
now()和sysdate()的区别-
now是开始执行的时间
sysdate是函数执行完的时间
-
(root@localhost) [db1]> select now(),sysdate(),sleep(3),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now() | sysdate() | sleep(3) | now() | sysdate() |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2022-04-13 01:02:24 | 2022-04-13 01:02:24 | 0 | 2022-04-13 01:02:24 | 2022-04-13 01:02:27 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (3.01 sec)
-
DATE_ADD(date,INTERVAL expr unit)：在date时间之上增加/减少，即增加负数(root@localhost) [db1]> select date_add(now(),interval 1 day),date_add(now(),interval -1 day); +--------------------------------+---------------------------------+ | date_add(now(),interval 1 day) | date_add(now(),interval -1 day) | +--------------------------------+---------------------------------+ | 2022-04-14 09:05:04 | 2022-04-12 09:05:04 | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) -
DATE_FORMAT()按照指定的格式来格式化日期- 放置在等式左边会导致索引失效，所以一般不建议使用
(root@localhost) [db1]> select date_format(now(),'%Y:%m:%d');
+-------------------------------+
| date_format(now(),'%Y:%m:%d') |
+-------------------------------+
| 2022:04:13 |
+-------------------------------+
1 row in set (0.00 sec)
JSON类型
-
5.7版本支持
-
原生JSON类型替换BLOB类型
- JSON数据有效性检查︰BLOB类型无法在数据库层做这样的约束性检查
- 查询性能的提升：查询不需要遍历所有字符串才能找到数据
- 支持部分属性索引∶通过虚拟列的功能可以对JSON中的部分数据进行索引
-
JSON函数部分等整完MongoDB再回来深入，现在知道其优点就行了
