#1. 字符串类型

字符串类型有两个CHAR，VARCHAR

字符串类型	值的长度	长度范围	占用的存储空间
CHAR(M)	M	0<=M<=255	M个字节
VARCHAR(M)	M	0<=M<=65535	M+1个字节

#1.1 CHAR类型

CHAR(M)类型一般需要预先定义字符串长度，如果不指定(M)，则表示长度默认是1个字节
如果保存是，数据的实际长度比CHAR类型声明的长度小，则会在右侧空格填充以达到指定长度，当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格
定义CHAR类型字段时，声明的字段长度即为CHAR类型字段的所占存储空间的这数

例：

mysql> CREATE TABLE chartest(
    -> c1 CHAR,
    -> c2 CHAR(5));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO chartest VALUES("1","wql"),("2","wql  ");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

//插入的空格被清除
mysql> SELECT * FROM chartest;
+------+------+
| c1   | c2   |
+------+------+
| 1    | wql  |
| 2    | wql  |
+------+------+
2 rows in set (0.00 sec)

CHAR类型中一个汉字占用两长度

#1.2 VARCHAR类型

VARCHAR(M)定义时，必须指定长度M，否则报错
MySQL4.0版本以下，VARCHAR(20)：指的是20个子节，如果存放UTF8汉字时，只能存6个(每一个汉字3字节)
在MySQL5.0以上版本，VARCHAR(20)：指的是20个字符
检索VARCHAR类型的字段数据时，会保留数据尾部的空格，VARCHAR类型的字段所占的存储空间为字符串实际长度加

例：

mysql> CREATE TABLE varchartest(
    -> c2 VARCHAR(5));
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO chartest VALUES("空想家");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM chartest;
+------+
| c2   |
+------+
| 空想家|
+------+
2 rows in set (0.00 sec)

#1.3 CHAR和VARCHAR的选择

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

场景1：存储很短。比如门牌号码101，201……这样很短的信息应该用CHAR，因为VARCHAR还要占byte用于存储信息长度
场景2：固定长度。比如UUID作为主键，那用CHAR应该更合适，因为他固定长度，VARCHAR动态根据长度的特性就不适用
场景3：十分频繁改变的colu mn。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而对于char就不需要

具体存储引擎的情况：

MYISAM数据存储引擎：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表的静态化，从而使数据检索更快，用空间换时间
InnoDB存储引擎：建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度(所有数据行都使用指向数据列值的头指针)，而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短而且固定长度，其他都考虑varchar。这样，对磁盘I/O和数据存储总量比较好
MEMORY存储引擎：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理

#2. 文本类型

在MySQL中，TEXT用来保存文本类型的字符串(文本本质上就是长字符)，总共包括4种类型，分别为TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT类型

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同

文本类型	特点	长度	长度范围	占用的存储空间
TINYTEXT	小文本，可变长度	L	0<= L <=255	L+2个字节
TEXT	文本，可变长度	L	0<= L <=65535	L+2个字节
MEDIUMTEXT	中等文本，可变长度	L	0<= L <= 16777215	L+3个字节
LONGTEXT	大文本，可变长度	L	0 <= L <=4294967295(相当于4G)	L+4个字节

注：由于实际存储的长度不确定，MySQL不允许TEXT类型的字段作为主键。遇到这种情况，只能采用CHAR(M)或VARCHAR(M)

例：

mysql> CREATE TABLE texttest(
    -> t1 TINYTEXT,
    -> T2 TEXT,
    -> t3 MEDIUMTEXT);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC texttest;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| t1    | tinytext   | YES  |     | NULL    |       |
| T2    | text       | YES  |     | NULL    |       |
| t3    | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO texttest VALUES("啦啦啦啦啦啦啦啦","啦啦啦啦","啦啦啦啦啦啦啦啦");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM texttest;
+------------------+----------+------------------+
| t1               | T2       | t3               |
+------------------+----------+------------------+
| 啦啦啦啦啦啦啦啦 | 啦啦啦啦 | 啦啦啦啦啦啦啦啦 |
+------------------+----------+------------------+
1 row in set (0.00 sec)

开发经验：TEXT文本类型，可以存储比较大的文本段，搜索速度比较慢。因此如果不是特别大的内容，建议使用CHAR或VARCHAR来代替，还有TEXT类型不用加默认值(加了也没用)，而且text和blob类型的数据删除后容易导致"空洞"，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分离出去，单独用一张表

#3. 枚举类型和集合类型

#3.1 枚举类型

ENUM类型也叫枚举类型，ENUM类型的取值范围需要在定义字段时进行指定，设置字段值时，ENU类型只允许从成员中选取单个值，不能一次选取多个值

其所需的存储空间由定义ENUM类型时指定的成员个数决定其所需的存储空间由定义ENUM类型时指定的成员个数决定

枚举类型	长度	长度范围占用的存储空间	ENUM
EMUN	L	1<=L<=65535	1或2个字节

当ENUM类型包含1~255个成员时，需要1个字节的存储空间
当ENUM类型包含256 ~ 65535个成员时，需要2个字节的存储空间
ENUM类型的成员个数上限为65535

例：

mysql> CREATE TABLE enumtest(
    -> e1 ENUM('春季','夏季','秋季','冬季'));
Query OK, 0 rows affected (0.00 sec)

mysql> DESC enumtest;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| e1    | enum('春季','夏季','秋季','冬季') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO enumtest VALUES('夏季');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM enumtest;
+------+
| e1   |
+------+
| 夏季 |
+------+
1 row in set (0.00 sec)

mysql> INSERT INTO enumtest VALLUES("夏季,秋季");
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 'VALLUES("夏季,秋季")' at line 1

注：
- 在插入时忽然大小写
- 可以为空

#3.2 SET集合类型

SET表示一个字符串对象，可以包含0个或多个成员，当成员个数的上限威64，设置字段时，可以取值范围内的0个或多个值

当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的

成员个数范围(L为实际成员个数)	占用的存储空间
1<= L <=8	1个字节
9<= L <=16	2个字节
17<= L <=24	3个字节
25<= L <=32	4个字节
33<= L <=64	8个字节

SET类型在存储数据时，成员个数越多，其占用的存储空间就越大

注：SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同

例：

mysql> CREATE TABLE settest(
    -> s1 SET("夏季","秋季","冬季","春季"));
Query OK, 0 rows affected (0.00 sec)

mysql> DESC settest;
+-------+----------------------------------+------+-----+---------+-------+
| Field | Type                             | Null | Key | Default | Extra |
+-------+----------------------------------+------+-----+---------+-------+
| s1    | set('夏季','秋季','冬季','春季') | YES  |     | NULL    |       |
+-------+----------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO settest VALUES("夏季,秋季");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM settest;
+-----------+
| s1        |
+-----------+
| 夏季,秋季 |
+-----------+
1 row in set (0.00 sec)

#4. 二进制字符串类型

MySQL中的二进制字符串类主要存储一些二进制数据，比如存储图片，音频和视频等二进制数据
MySQL中支持的二进制字符串类型主要包括BINARY，VARBINARY，BLOB，MEDIUMBLOB和LONGBLOB类型

#4.1BINARY和VARBINARY

BIINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串：
- BINARY(M)的概念：固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储1个字节。例如：BINARY(8)表示最多存储8字节，如果字段值不足(M)个字节，将在右边填充'\0'以补齐长度

VARCHAR(M)概念：可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARCHAR类型的数据除了存储数据本身外，还需要1或2字节来存储数据的字节数。VARCHAR类型必须指定(M)

二进制字符串类型	特点	值的长度	占用空间
BINARY(M)	固定长度	M(0 <=M<=255)	M个字节
VARBINARY	可变长度	M(0 <=M<=65535)	M+1个字节

例：

mysql> CREATE TABLE binarytest(
    -> b1 BINARY,
    -> b2 BINARY(5),
    -> B4 VARBINARY(5));
Query OK, 0 rows affected (0.00 sec)

mysql> DESC binarytest;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| b1    | binary(1)    | YES  |     | NULL    |       |
| b2    | binary(5)    | YES  |     | NULL    |       |
| B4    | varbinary(5) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO binarytest VALUES('a','asb','daswq');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM binarytest;
+------+-------+-------+
| b1   | b2    | B4    |
+------+-------+-------+
| a    | asb   | daswq |
+------+-------+-------+
1 row in set (0.00 sec)

#4.2 BLOB类型

BLOB是一个二进制的大对象，可以容纳可变数量的数量

MySQL中的BLOB类型包括TINYBLOB，BLOB，MEDIUMBLOB和LONGBLO等4种类型，它们可以然值的最大长度不同，可以存储一个大的二进制的大对象，比如图片，音频和视频等

注：在开发种指向图片等静态资源是不会存在Mysql中的，一般存在分布式文件系统中(如：MinIO,FastDfs)，现在有专门的SSO对象存储服务器

二进制字符串类型	值的长度	长度范围	占用空间
TINYBLOB	L	0<=L<=255	L+1个字节
BLOB	L	0<=L<=65535(相当于64k)	L+2个字节
MEDIUMBLOB	L	0<=L<=16777215(相当于1MB)	L+3个字节
LONGBLOB	L	0<=L<=4294967295(相当于4G)	L+4个字节

TEXT和BLOB的使用注意事项：在使用text和blob字段类型时需要注意以下几点，以便提供数据库的性能

BLOB和TEXT值有自己本身的问题，特别是在执行大量的删除或更新操作时。删除这种值会在数据表中产生很大的"空洞"，以后插入填入这些 '空洞' 的记录可能长度不同。为了提供性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理

如果需要对大文本字段进行模糊查询，MySQL提供了前缀索引，但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如：SELECT *查询就不是一个很好的做法，除非你能确定作为约束条件的WHERE子句找到所需要的数据行

在项目中建议大型的BLOB和TEXT分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以在原数据表定义固定长度的数据格式。这会减少主表的碎片

#5. JSON类型

JSON (JavaScript Object Notation)是一种轻量级的数据交换格式。简洁和清晰的层次结构使得JSON成为理想的数据交换语言。它易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。

JSON可以将JavaScript对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。

在MySQL 5.7中支持JSON数据类型。在MysQL 8.x版本中,JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构,使得在MySQL中存储和读取JSON类型的数据更加方便和高效。创建数据表,表中包含一个JSON类型的字段js。

例：

mysql> CREATE TABLE jsontest(
    -> j1 JSON);
Query OK, 0 rows affected (0.00 sec)

mysql> DESC jsontest;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| j1    | json | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO jsontest(j1) VALUES('{"name":"WQL-KXJ","age":"18","address":{"provider":"chansha","ll":"ll"}}');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM jsontest;
+----------------------------------------------------------------------------------+
| j1                                                                               |
+----------------------------------------------------------------------------------+
| {"age": "18", "name": "WQL-KXJ", "address": {"ll": "ll", "provider": "chansha"}} |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//精准获取
mysql> SELECT j1->'$.name' AS name  FROM jsontest;
+-----------+
| name      |
+-----------+
| "WQL-KXJ" |
+-----------+
1 row in set (0.00 sec)

#6. 类型选择建议

在定义数据类型时，如果确定是整数就选择INT，如果是小数一旦要使用DECIMAL(M,D)，如果是日期和时间类型就选择DATETIME

这样做的好处，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是两面的，可靠性好，并不意味着高效，，比如TEXT虽然使用方便，但是效率不如CHAR()和VARCHAR()

阿里巴巴数据库规范：
1. 【强制】当确定整数类型是正数时，一定要加UNSIGNED无符号数
2. 【强制】小数类型为DECIMAL，禁止使用FLOAT和DOUBLE

在存储时FLOAT和DOUBLE存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果DECIMAL的存储结果超过最大范围，建议将整数和小数分开存储

【强制】如果存储的字符串长度几乎相等，使用CHAR定长字符串类型
【强制】VARCHAR是可变长字符串，不预留分配存储空间，长度不要超过5000.如果存储长度大于此值，定义字段为TEXT，独立出来一张表，用主键来对应，避免影响其他字段索引效率

WQL

MySQL数据类型(二)