MySQL之常用数据类型
一：MySQL中基本数据类型
MySQL数据库表中的每一列都必须具有名称和数据类型。据类型是一个标签，它可以告知存储什么类型的数据，它也标识了SQL如何与存储的数据进行交互。MySQL数据库支持所有标准SQL数值数据类型，涉及大概11种类MySQL数据类型。
整数类型：TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT(相当JAVA中LONG类型)
浮点类型：FLOAT、DOUBLE
定点数类型：DECIMAL
位类型：BIT
日期时间类型：YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型：ENUM
集合类型：SET
二进制字符串类型：BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型：JSON对象、JSON数组
空间数据类型（了解）：
单值类型：GEOMETRY、POINT、LINESTRING、POLYGON；
集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION
二：整数类型
整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。
| 数据类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
| TINYINT | 1 |
-128~127
|
0~255
|
| SMALLINT | 2 |
-32768~32767
|
0~65535
|
| MEDIUMINT | 3 |
-8388608~8388607
|
0~16777215
|
| INT或INTEGER | 4 |
-2147483648~2147483647
|
0~4294967295
|
| BIGINT | 5 |
-9223372036854775808~9223372036854775807
|
0~18446744073709551615
|
1：创建整数类型表及测试
-- 创建数据库 CREATE DATABASE IF NOT EXISTS type_demo CHARACTER SET utf8mb4; -- 创建整数类型字段表 CREATE TABLE IF NOT EXISTS type_demo.table_int_demo1( column1 TINYINT COMMENT '迷你整型', column2 SMALLINT COMMENT '小整型', column3 MEDIUMINT COMMENT '中整型', column4 INT COMMENT '标准整型', column5 BIGINT COMMENT '大整型'); -- 查询创建表结构 DESC type_demo.table_int_demo1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | column1 | tinyint(4) | YES | | NULL | | | column2 | smallint(6) | YES | | NULL | | | column3 | mediumint(9) | YES | | NULL | | | column4 | int(11) | YES | | NULL | | | column5 | bigint(20) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ -- 插入有符号边界值 INSERT INTO type_demo.table_int_demo1 VALUES (127,32767,8388607,2147483647,9223372036854775807), (-128,-32768,-8388608,-2147483648,-9223372036854775808); 插入提示（已插入2条记录）：Records: 2 Duplicates: 0 Warnings: 0 -- 插入有符号超出边界值的数 INSERT INTO type_demo.table_int_demo1 VALUES (999,99999,9999999,9999999999,9999999999999999999), (-999,-99999,-9999999,-9999999999,-9999999999999999999); 插入提示（已插入2条记录）：Records: 2 Duplicates: 0 Warnings: 10 注：这里虽然插入了2条记录，但是每个字段插入的值都是警告 -- 查询表数据 SELECT * FROM type_demo.table_int_demo1 +---------+---------+----------+-------------+----------------------+ | column1 | column2 | column3 | column4 | column5 | +---------+---------+----------+-------------+----------------------+ | 127 | 32767 | 8388607 | 2147483647 | 9223372036854775807 | | -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 | | 127 | 32767 | 8388607 | 2147483647 | 9223372036854775807 | | -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 | +---------+---------+----------+-------------+----------------------+ 注：这时候我们看到有四条记录；1，2两条是我们第一次插入不报警告的；3，4两条超出 边界值的数据也插入成功了，但报了警告，而且插入的数据被自动更改位当前前数据类型的边界值， 所以得出结论：插入超过类型最大值时，会默认取这个字段类型的边界值，但在MySQL8.0则直接报错
2：可选属性【数据显示宽度、无符号、零填充】
在MySQL整型中有三个可选的属性，分别是数据显示宽度、无符号(UNSIGNED)、零填充(ZEROFILL)
Ⅰ：数据显示宽度 M：表示显示宽度，M的取值范围是(0,255)。 例如 INT(5)： 当数据宽度小于5位的时候在数字前面需要用字符填满宽度。 该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，单独指定数据显示宽度则无效。 注意： 如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度
与类型可以存储的值范围无关。但从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。 但是MySQL8.0.17之前的版本中，整型类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值 默认宽度： TINYINT(4)，SMALLINT(6)，MEDIUMINT(9)，INT(11)，BIGINT(20) 为什么会显示这些默认宽度： 以有符号SMALLINT(6)来说，我们可以看出他的取值是-32768~32767，那么我们32768+32767=65535 那么这个65535宽度是5，因为没有被无符号修饰，所以在加上一个负号 '-' 位数，可以得出为 6 以有符号MEDIUMINT(9)来说，我们可以看出他的取值是-8388608~8388607， 那么我们8388608+8388607=16777215，那么这个16777215宽度是8，因为没有被无符号修饰， 所以在加上一个负号 '-' 位数，可以得出为 9，若是有符号则就不用加'-'，宽度直接为8 具体BIGINT(20)为什么有符号是20我不是太了解 基本案例： -- 创建数据库 CREATE DATABASE IF NOT EXISTS type_demo CHARACTER SET utf8mb4; -- 创建整数类型字段表 CREATE TABLE IF NOT EXISTS type_demo.table_int_demo2( column1 TINYINT(10) COMMENT '迷你整型', column4 INT(15) COMMENT '标准整型'); -- 查询数据库表 DESC type_demo.table_int_demo2; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | column1 | tinyint(10) | YES | | NULL | | | column4 | int(15) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ -- 说明： 这时候我们创建2个字段并设置类型宽度，如果不加其它属性，那么它的宽度是无效的 -- 演示： INSERT INTO type_demo.table_int_demo2 VALUES(20,987); SELECT * FROM type_demo.table_int_demo2; +---------+---------+ | column1 | column4 | +---------+---------+ | 20 | 987 | +---------+---------+ 这里我们看不出任何宽度问题 Ⅱ：无符号(UNSIGNED) 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，
如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。 例如： int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。并且数据范围0~4294967295 基本案例： -- 创建数据库 CREATE DATABASE IF NOT EXISTS type_demo CHARACTER SET utf8mb4; -- 创建整数类型字段表 CREATE TABLE IF NOT EXISTS type_demo.table_int_demo3( column2 INT UNSIGNED COMMENT '无符号标准整型', column3 INT COMMENT '标准整型'); -- 查询数据库表 DESC type_demo.table_int_demo3; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | column2 | int(10) unsigned | YES | | NULL | | | column3 | int(11) | YES | | NULL | | +---------+------------------+------+-----+---------+-------+ -- 演示（无符号INT最大可以存储0~4294967295） INSERT INTO type_demo.table_int_demo3 VALUES(4294967295,4294967295); Query OK, 1 row affected, 1 warning (0.37 sec) （有一条警告，因为column3最大支持存储2147483647，而插入4294967295超出） SELECT * FROM type_demo.table_int_demo3; +------------+------------+ | column2 | column3 | +------------+------------+ | 4294967295 | 2147483647 | +------------+------------+ Ⅲ：零填充(ZEROFILL) 零填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，
用0在左边填充，如果超过M位，只要不超过数据存储范围即可。 说明： 之前在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都 是占用 4 bytes 的存储空间。也就是说，int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义。如果整 数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充。 演示： -- 创建数据库 CREATE DATABASE IF NOT EXISTS type_demo CHARACTER SET utf8mb4; -- 创建整数类型字段表 CREATE TABLE IF NOT EXISTS type_demo.table_int_demo4( column1 TINYINT(10) UNSIGNED ZEROFILL COMMENT '迷你整型', column4 INT(15) ZEROFILL COMMENT '标准整型'); -- 查询创建表结构 DESC type_demo.table_int_demo4; +---------+-------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------------------------+------+-----+---------+-------+ | column1 | tinyint(10) unsigned zerofill | YES | | NULL | | | column4 | int(15) unsigned zerofill | YES | | NULL | | +---------+-------------------------------+------+-----+---------+-------+ -- 演示（插入数据） INSERT INTO type_demo.table_int_demo4 VALUES(12,7894); SELECT * FROM type_demo.table_int_demo4; +------------+-----------------+ | column1 | column4 | +------------+-----------------+ | 0000000012 | 000000000007894 | +------------+-----------------+
3：如何选择整型类型
在评估用哪种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：用占用字节数少的整数类型可以节省存储空间；但是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。
如果使用SMALLINT类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范围65535。但使用 INT就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。你要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，建议首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。
三：浮点类型（浮点数）
浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。
| 数据类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
| FLOAT（单精度） | 4 |
(-3.402823466E+38 ~ 1.175494351E-38),0, |
0,(1.175494351E-38~3.402823466E+38) |
| DOUBLE（双精度） | 8 |
(-1.7976931348623157E+308 ~ |
0,(2.2250738585072014E-308 ~ |
REAL默认就是DOUBLE。如果你把SQL模式设定为启用 "REAL_AS_FLOAT"，那么，MySQL就认为REAL是FLOAT。如果要启用“REAL_AS_FLOAT”，可以通过 SET SESSION sql_mode = 'REAL_AS_FLOAT' 修改；（具体看配置文件my.ini里配置的sql_mode）
1：创建浮点类型（浮点数）及测试
说明1：FLOAT占用字节数少，取值范围小；DOUBLE占用字节数多，取值范围也大；
说明2：浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分，是因为MySQL存储浮点数的格式为：符号(S)、尾数(M)和阶码(E)。所以有没有符号，MySQL的浮点数都会存储表示符号的部分。所以无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。
Ⅰ：精度说明（5.7版本）： 对于浮点类型，在MySQL中单精度值使用 4 个字节，双精度值使用 8 个字节。 ①：MySQL允许使用非标准语法（其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）： FLOAT(M,D) 或 DOUBLE(M,D)。这里M称为精度，D称为标度。 (M,D)中 M=整数位+小数位，D=小数位。 M不可大于255（包含255），D不可大于30（包含30） 例如：定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会出问题。 ②：FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。 ③：浮点类型，也可以加 UNSIGNED，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。 ④：不管是否显式设置了精度(M,D)，这里MySQL的处理方案如下： >：如果存储时，整数部分超出了范围，MySQL就会存储当前的最大值 >：如果存储时，小数点部分若超出范围，就分以下情况： #：若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余 的小数位后保存。例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。 #：若四舍五入后，整数部分超出范围，则MySQL则会保存自己的最大值和最小值 如：FLOAT(5,2)存储55646.6666 则最终会保存为999.99 注：从MySQL 8.0.17开始，FLOAT(M,D)和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，
关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。 -- 基本建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_fd_demo1( column1 FLOAT COMMENT '默认单位的float', column2 FLOAT(5,2) COMMENT '带范围的float', column3 FLOAT(5,2) UNSIGNED COMMENT '带范围的无符号float', column4 DOUBLE COMMENT '默认单位的double', column5 DOUBLE(5,2) COMMENT '带范围的double'); -- 建表结构 +---------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+-------+ | column1 | float | YES | | NULL | | | column2 | float(5,2) | YES | | NULL | | | column3 | float(5,2) unsigned | YES | | NULL | | | column4 | double | YES | | NULL | | | column5 | double(5,2) | YES | | NULL | | +---------+---------------------+------+-----+---------+-------+ -- 测试标准方式 INSERT INTO type_demo.table_fd_demo1 VALUES(123.456, 123.456, 123.456, 222.456, 222.456); INSERT INTO type_demo.table_fd_demo1 VALUES(0, 999.994, 999.994, 0, 0); -- 查询结果 +---------+---------+---------+---------+---------+ | column1 | column2 | column3 | column4 | column5 | +---------+---------+---------+---------+---------+ | 123.456 | 123.46 | 123.46 | 222.456 | 222.46 | | 0 | 999.99 | 999.99 | 0 | 0.00 | +---------+---------+---------+---------+---------+ column2和column3和column5设置了类型范围，所以会把超出的小数四舍五入 -- 测试超出范围的方式（以FLOAT测试） INSERT INTO type_demo.table_fd_demo1 VALUES(0, 155566.995, 1678777.995, 0, 0); 会显示警告：Query OK, 1 row affected, 2 warnings (0.38 sec) +---------+---------+---------+---------+---------+ | column1 | column2 | column3 | column4 | column5 | +---------+---------+---------+---------+---------+ | 0 | 999.99 | 999.99 | 0 | 0.00 | +---------+---------+---------+---------+---------+ Ⅱ：精度误差说明 浮点数类型有个缺陷，就是不精准。下面我来重点解释一下为什么 MySQL 的浮点数不够精准。比如，我 们设计一个表，有f1这个字段，插入值分别为0.47,0.44,0.19，我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1
-- 创建表 CREATE TABLE IF NOT EXISTS type_demo.table_fd_demo2(f1 DOUBLE); -- 添加数据 INSERT INTO type_demo.table_fd_demo2 VALUES(0.47),(0.44),(0.19); -- 计算统计 SELECT SUM(f1) FROM type_demo.table_fd_demo2; +--------------------+ | SUM(f1) | +--------------------+ | 1.0999999999999999 | +--------------------+ 查询结果是 1.0999999999999999，虽然误差很小，但确实有误差。你也可以尝试把数据类型改成 FLOAT，然后运行求和查询，
得到的是：1.0999999940395355。显然，误差更大了。那么，为什么会存在这样的误差呢？问题还是出在 MySQL 对浮点类型
数据的存储方式上。 MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。无论哪个，都是采用二进制的方式来进行存储的。
比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。如果尾数不是 0 或 5（比如 9.624），你就无法用
一个二进制数来精确表达。进而，就只好在取值允许的范围内进行四舍五入。 在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。同时，
在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。那么，MySQL 要精准的
数据类型就选择定点数据类型：DECIMAL 。
四：浮点类型（定点数）
| 数据类型 | 字节数 | 含义 |
| DECIMAL(M,D), DEC, NUMERIC | 取决与M和D | 有效范围由M和D决定 |
定点类型： 使用DECIMAL(M,D)的方式表示高精度小数。其中，M被称为精度，D被称为标度。M最大取值65，D最大取值30，M得比D大。 例如，定义DECIMAL(5,2)的类型，表示该列取值范围是-999.99~999.99。 DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。但DECIMAL的存储空间并不是固定的， 由M和D一起决定的存储空间大小。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数 值范围可以更大一些。 定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。当DECIMAL类型不指定精度和标度时， 其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。 浮点数和定点数区别： 浮点数：浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用 于需要取值范围大，又可以容忍微小误差的科学计算场景。 定点数：定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如金额计算） 为什么说DECIMAL(M,D)类型精确： 比如说使用DECIMAL类型存储了一个 13.254 的这个小数；其实在存储时是对数据进行拆分成两个十进制数存储的，所以就变为了一个 13的整数和一个254的整数，使用这种方式就达到数据的精确 如何计算DECIMAL(M,D)实际占用字节： 如：DECIMAL(36,6) 代表可以存储 30位整数和6位小数 然后对这范围进行分组计算空间，如小数点左边为30的个十进制位，右边为6个十进制位 000000000000000000000000000000.000000 然后对其分组，以小数点为起点往左分组和以小数点为起点往右分组，九个位为一组（如后面不足9位则分为一组） 000 000000000 000000000 000000000 . 000000 对照表：（十进制的数字位数不同，存储空间的大小也不同） 分组中包含 1~2 位十进制数 占用存储空间 1字节 分组中包含 3~4 位十进制数 占用存储空间 2字节 分组中包含 5~6 位十进制数 占用存储空间 3字节 分组中包含 7~9 位十进制数 占用存储空间 4字节 这时我们就可以计算出来了 000 000000000 000000000 000000000 . 000000 2 + 4 + 4 + 4 + 3 = 17字节大小 示例：如存储了 1.222222 的小数显示的16进制数为： 80 00 00 00 00 00 00 00 00 00 00 00 00 01 03 64 0E 占用17个字节大小 其中：80 00 00 00 00 00 00 00 00 00 00 00 00 01 为整数 1；最前面的80代表正数符号位 其中：03 64 0E 为小数222222 -- 建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_de_demo1( column1 DEC COMMENT '定点类型M=10，D=0', column2 NUMERIC COMMENT '定点类型M=10，D=0', column3 DECIMAL COMMENT '定点类型M=10，D=0', column4 DECIMAL(5,2) COMMENT '定点类型M=5,D=2'); -- 查询表结构 DESC table_de_demo1; （默认长度10，0） +---------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+-------+ | column1 | decimal(10,0) | YES | | NULL | | | column2 | decimal(10,0) | YES | | NULL | | | column3 | decimal(10,0) | YES | | NULL | | | column4 | decimal(5,2) | YES | | NULL | | +---------+---------------+------+-----+---------+-------+ -- 测试标准插入 INSERT INTO type_demo.table_de_demo1 VALUES(999999999,999999999,999999999,999.99); -- 查询 SELECT * FROM type_demo.table_de_demo1; +-----------+-----------+-----------+---------+ | column1 | column2 | column3 | column4 | +-----------+-----------+-----------+---------+ | 999999999 | 999999999 | 999999999 | 999.99 | +-----------+-----------+-----------+---------+ -- 测试其它操作 INSERT INTO type_demo.table_de_demo1 VALUES(0,0,0,999.995); INSERT INTO type_demo.table_de_demo1 VALUES(0,0,0,99999.99599); -- 查询 SELECT * FROM type_demo.table_de_demo1; +-----------+-----------+-----------+---------+ | column1 | column2 | column3 | column4 | +-----------+-----------+-----------+---------+ | 999999999 | 999999999 | 999999999 | 999.99 | | 0 | 0 | 0 | 999.99 | | 0 | 0 | 0 | 999.99 | +-----------+-----------+-----------+---------+ -- 注：对超出范围的数据则自动转为当前设置的最大范围值
五：位类型
BIT类型中存储的是二进制，类似1010010这样数值
| 二进制字符串类型 | 长度 | 长度范围 | 占用空间 |
| BIT(M) | M | 1<=M<=64 | 约为 (M+7)/8 个字节 |
BIT类型: 如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的位数，位数最小值为1，最大值为64。 注存储范围：当BIT(64)时则可以存储最大 -9223372036854775808 ~ 9223372036854775807 ；在64位为符号位，存负号 -- 创建数据库表 CREATE TABLE IF NOT EXISTS type_demo.table_bit_demo1( column1 BIT COMMENT '位类型 长度为1', column2 BIT(5) COMMENT '位类型 长度为5', column3 BIT(64) COMMENT '位类型 长度为64'); -- 查询创建表结构 DESC table_bit_demo1; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | column1 | bit(1) | YES | | NULL | | | column2 | bit(5) | YES | | NULL | | | column3 | bit(64) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ -- 插入标准数据（注：插入是十进制数） INSERT INTO type_demo.table_bit_demo1 VALUES(1,25,-9223372036854775808); INSERT INTO type_demo.table_bit_demo1 VALUES(1,12,9223372036854775807); 使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取 使用b+0查询数据时，可以直接查询出存储的十进制数据的值 -- 查询表数据 SELECT column1+0, column2+0, column3+0 FROM type_demo.table_bit_demo1; -- 使用十进制 SELECT BIN(column1), BIN(column2), BIN(column3) FROM type_demo.table_bit_demo1; -- 使用二进制 SELECT HEX(column1), HEX(column2), HEX(column3) FROM type_demo.table_bit_demo1; -- 使用十六进制
六：日期与时间类型
日期和时间在我们的系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。在MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异；MySQL5.7和8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
YEAR： 类型通常用来表示 年
DATE： 类型通常用来表示 年、月、日
TIME： 类型通常用来表示 时、分、秒
DATETIME： 类型通常用来表示 年、月、日、时、分、秒
TIMESTAMP： 类型通常用来表示 带时区的年、月、日、时、分、秒
| 类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
| YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
| TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
| DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
| DATETIME |
日期时间 |
8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP |
日期时间 |
4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07 UTC |
1：YEAR类型
YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节的存储空间。
在MySQL中YEAR有两种存储格式：
①：以2位字符串或数字格式表示YEAR类型，其格式为YY，最小值为00，最大值为99
注：以两位存储时取值有特点规范
当取值为01到69时，表示2001到2069；
当取值为70到99时，表示1970到1999；
②：以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155（推荐方式）
注：当取值整数的0或00添加的话，那么是0000年；
注：当取值是日期（如now()函数）/字符串的'0'添加的话，是2000年。
从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)
从MySQL8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。
注：YEAR只支持YEAR(4)或者YEAR方式，两位不是YEAR(2)，而是两位字符（取值范围01~99）
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_year_demo1( column1 YEAR COMMENT '基本YEAR类型（默认YEAR(4)）', column2 YEAR(4) COMMENT 'YEAR类型'); -- 查询创建表结构 DESC type_demo.table_year_demo1; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | column1 | year(4) | YES | | NULL | | | column2 | year(4) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ -- 添加数据并查询 INSERT INTO type_demo.table_year_demo1 VALUES('22','71'),(0,'0'); SELECT * FROM type_demo.table_year_demo1; +---------+---------+ | column1 | column2 | +---------+---------+ | 2022 | 1971 | | 0000 | 2000 | +---------+---------+
2：DATE类型
DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中YYYY表示年份，MM表示月份，DD表示日期。需要3个字节的存储空间。 在向DATE类型的字段插入数据时，同样需要满足一定的格式条件： ①：以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-31（因为0000-00-00和
0000-01-01日期没意义）。YYYYMMDD格式会被转化为YYYY-MM-DD格式。 ②：以YY-MM-DD格式或者YYMMDD格式表示的字符串日期，此格式中，年份为两位数值或字符串时，那么它就和YEAR类型（具体看YEAR类型）
一样，当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。 ③：使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_date_demo1( column1 DATE COMMENT '基本DATE类型'); -- 查询创建表结构 DESC type_demo.table_date_demo1; +---------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------+------+-----+---------+-------+ | column1 | date | YES | | NULL | | +---------+------+------+-----+---------+-------+ -- 添加数据并查询 INSERT INTO type_demo.table_date_demo1 VALUES ('2022-09-13'),('2022/11/14'),('20221115'),(20221116); INSERT INTO type_demo.table_date_demo1 VALUES ('00-01-01'),('22/11/14'),('700101'),(991231); SELECT * FROM type_demo.table_date_demo1; +------------+ | column1 | +------------+ | 2022-09-13 | | 2022-11-14 | | 2022-11-15 | | 2022-11-16 | | 2000-01-01 | | 2022-11-14 | | 1970-01-01 | | 1999-12-31 | +------------+ -- 注：超出范围一律为0000-00-00 INSERT INTO type_demo.table_date_demo1 VALUES('9999-12-32'),('99-12-32'); +------------+ | column1 | +------------+ | 0000-00-00 | | 0000-00-00 | +------------+
3：TIME类型
TIME类型用来表示时间，不包含日期部分。在MySQL中需要3个字节的存储空间来存储TIME类型的数据，可以使用“HH:MM:SS”格式来表示TIME类型，
其中HH表示小时，MM表示分钟，SS表示秒。
在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式：
①：可以使用带有冒号的字符串，比如'D HH:MM:SS'、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'格式，都能被正确地插入TIME类型
的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，
计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
②：可以使用不带有冒号的字符串或者数字，格式为'HHMMSS'或者HHMMSS。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其
自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
③：使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_time_demo1( column1 TIME COMMENT '基本time类型'); -- 查询创建表结构 DESC type_demo.table_time_demo1; +---------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------+------+-----+---------+-------+ | column1 | time | YES | | NULL | | +---------+------+------+-----+---------+-------+ -- 添加数据并查询 INSERT INTO type_demo.table_time_demo1 VALUES('2 22:32:14'),('15:36:22'),('12:18'),('2 12:42'),('3 06'),('42'); SELECT * FROM type_demo.table_time_demo1; +----------+ | column1 | +----------+ | 70:32:14 | (这70:32:14 由 2 * 24 +22 :32 14 得来) | 15:36:22 | | 12:18:00 | | 60:42:00 | | 78:00:00 | | 00:00:42 | +----------+ INSERT INTO type_demo.table_time_demo1 VALUES(223214),('153622'),(1218),(NOW()); +----------+ | column1 | +----------+ | 22:32:14 | | 15:36:22 | | 00:12:18 | | 16:59:23 | +----------+
4：DATETIME类型
DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为DATE类型和TIME类型的组合， 可以表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。 在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件： ①：以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，
最大值为9999-12-31 23:59:59。 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式 ②：以YY-MM-DD HH:MM:SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，
00到69表示2000到2069；70到99表示1970到1999。 ③：使用函数CURRENT_TIMESTAMP()和 NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_datetime_demo1( column1 DATETIME COMMENT '基本datetime类型'); -- 查询创建表结构 DESC type_demo.table_datetime_demo1; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | column1 | datetime | YES | | NULL | | +---------+----------+------+-----+---------+-------+ -- 插入数据 INSERT INTO type_demo.table_datetime_demo1 VALUES ('2021-01-01 06:50:30'), ('20210101065030'); INSERT INTO type_demo.table_datetime_demo1 VALUES ('99-01-01 00:00:00'),('990101000000'),('20-01-01 00:00:00'),('200101000000'); INSERT INTO type_demo.table_datetime_demo1 VALUES (20200101000000), (200101000000), (19990101000000), (990101000000); INSERT INTO type_demo.table_datetime_demo1 VALUES (CURRENT_TIMESTAMP()), (NOW()); SELECT * FROM type_demo.table_datetime_demo1; +---------------------+ | column1 | +---------------------+ | 2021-01-01 06:50:30 | | 2021-01-01 06:50:30 | | 1999-01-01 00:00:00 | | 1999-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 1999-01-01 00:00:00 | | 1999-01-01 00:00:00 | | 2022-11-13 17:36:52 | | 2022-11-13 17:36:52 | +---------------------+
5：TIMESTAMP类型
TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。
但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储"1970-01-01 00:00:01 UTC" ~ "2038-01-19 03:14:07 UTC"之间的时间。
其中UTC表示世界统一时间，也叫作世界标准时间。
存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此TIMESTAMP存储的同一个时间值，
在不同的时区查询时会显示不同的时间。
向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时，两位数值的年份同样符合YEAR类型的规则条件
只不过表示的时间范围要小很多。如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_timestamp_demo1( column1 TIMESTAMP COMMENT '基本TIMESTAMP类型'); -- 查询创建表结构 DESC type_demo.table_timestamp_demo1; +---------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------+------+-----+-------------------+-----------------------------+ | column1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---------+-----------+------+-----+-------------------+-----------------------------+ -- 插入数据 INSERT INTO type_demo.table_timestamp_demo1 VALUES ('1999-01-01 03:04:50'), ('19990101030405'),('99-01-01 03:04:05'),('990101030405'), ('2020@01@01@00@00@00'),('20@01@01@00@00@00'),(CURRENT_TIMESTAMP()),(NOW()); SELECT * FROM type_demo.table_timestamp_demo1; +---------------------+ | column1 | +---------------------+ | 1999-01-01 03:04:50 | | 1999-01-01 03:04:05 | | 1999-01-01 03:04:05 | | 1999-01-01 03:04:05 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2022-11-13 21:49:44 | | 2022-11-13 21:49:44 | +---------------------+
6：TIMESTAMP和DATETIME的区别
①：TIMESTAMP存储空间比较小，表示的日期时间范围也比较小底层存储方式不同，
②：TIMESTAMP底层存储的是毫秒值，距离1970-01-01 0:0:0 0毫秒的毫秒值。
③：两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
④：TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，
其它时区的人查看数据必然会有误差的。
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_td_demo1( column1 DATETIME COMMENT '基本timestamp类型', column2 TIMESTAMP COMMENT '基本timestamp类型'); -- 查询创建表结构 DESC type_demo.table_td_demo1; +---------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------+------+-----+-------------------+-----------------------------+ | column1 | datetime | YES | | NULL | | | column2 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---------+-----------+------+-----+-------------------+-----------------------------+ -- 插入2组数 INSERT INTO type_demo.table_td_demo1 VALUES ('2022-11-11 14:22:22','2022-11-11 14:22:22'),(NOW(),NOW()); -- 基于同一时区的基本查询 SELECT * FROM type_demo.table_td_demo1; +---------------------+---------------------+ | column1 | column2 | +---------------------+---------------------+ | 2022-11-11 14:22:22 | 2022-11-11 14:22:22 | | 2022-11-13 21:54:06 | 2022-11-13 21:54:06 | +---------------------+---------------------+ -- 更改时区信息（具体百度MySQL时区设置） SHOW VARIABLES LIKE '%time_zone'; -- 查询数据库时区 SET time_zone = '+9:00'; -- 设置当前时区为东九区 -- 基于修改过时区的基本查询（基于东九区） SELECT * FROM type_demo.table_td_demo1; +---------------------+---------------------+ | column1 | column2 | +---------------------+---------------------+ | 2022-11-11 14:22:22 | 2022-11-11 15:22:22 | | 2022-11-13 21:54:06 | 2022-11-13 22:54:06 | +---------------------+---------------------+
7：时间总结
用得最多的日期时间类型就是DATETIME。虽然MySQL也支持YEAR（年）、TIME（时间）、DATE（日期），以及TIMESTAMP类型，但是在实际项目中，尽量用DATETIME类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL语句也会更加复杂。
但一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳，因为DATETIME虽然直观，但不便于计算。
七：文本字符串类型
MySQL中文本字符串总体上分为CHAR 、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET等类型。
文本字符串类型 值的长度 长度范围 占用的存储空间 CHAR(M) M 0 <= M <= 255 M个字节 VARCHAR(M) M 0 <= M <= 65535 M+1~2个字节 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 L+4个字节 ENUM L 1 <= L <= 65535 1或2个字节 SET L 0 <= L <= 64 1,2,3,4或8个字节
1：CHAR和VARCHAR类型
CHAR和VARCHAR类型都可以存储比较短的字符串。补充：行大小限制
| 字符串(文本) | 特点 | 长度 | 长度范围 | 占用的存储空间 |
| CHAR(M) | 固定长度 | M | 0<=M<=255 | M个字节 |
| VARCHAR(M) | 可变长度 | M | 0<=M<=65535 | (实际长度+1~2)个字节 |
CHAR类型：
①：CHAR(M)类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
②：如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。
当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
③：定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
-- 注：假设在utf8mb3字符集下定义CHAR(5)；实际是占用5*3=15个字节数
VARCHAR类型：
①：VARCHAR(M)定义时，必须指定长度M，否则报错。
②：MySQL4.0版本以下varchar(20)：指的是20字节，如果存放UTF8mb3汉字时，只能存6个（每个汉字3字节）
MySQL5.0版本以上varchar(20)：指的是20字符。可以存放20个字符
③：检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间
为字符串实际长度加1~2个字节（varchar(1~255)多占用1字节，varchar(256~65535)多占用2字节）。
-- 注：一般VARCHAR长度超过255推荐使用文本字符串TEXT来代替
两者区别：
CHAR：固定长度，但浪费时间；不过效率高；适合存储不大，速度要求高并且长度固定的场景
VARCHAR：可变长度，可以节省存储空间（只占用本身数据+1~2个字节）;但效率低，适合非CHAR长度不固定的情况下
说明：
情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占1~2个
byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
情况2：固定长度的。比如使用uuid作为主键，那用char(32)应该更合适。因为它固定长度；若使用varchar(32)
虽然是存储了，但是还额外有个字段记录varchar当前的长度。
情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个
非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
情况4：
①：MyISAM数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
这样使得整个表静态化，从而使数据检索更快，用空间换时间。
②：MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，
两者都是作为CHAR类型处理的。
③：InnoDB存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和
可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，
由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。
这样节省空间，对磁盘I/O和数据存储总量比较好。
-- CHAR测试 -- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_char_demo1( column1 CHAR COMMENT '默认长度字符串1', column2 CHAR(3) COMMENT '固定字符串3', column3 CHAR(5) COMMENT '固定字符串5'); -- 查询表结构 DESC table_char_demo1; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | column1 | char(1) | YES | | NULL | | | column2 | char(3) | YES | | NULL | | | column3 | char(5) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ -- 添加数据 INSERT INTO type_demo.table_char_demo1 VALUES('a','abc','abcde'),('我','我们的','开心每一天'); INSERT INTO type_demo.table_char_demo1 VALUES('a','aa ',' de'); SELECT * FROM table_char_demo1; +---------+-----------+-----------------+ | column1 | column2 | column3 | +---------+-----------+-----------------+ | a | abc | abcde | | 我 | 我们的 | 开心每一天 | | a | aa | a de | +---------+-----------+-----------------+ -- 计算占用字节数 SELECT LENGTH(column1),LENGTH(column2),LENGTH(column3) FROM table_char_demo1; +-----------------+-----------------+-----------------+ | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) | +-----------------+-----------------+-----------------+ | 1 | 3 | 5 | | 3 | 9 | 15 | | 1 | 2 | 5 | +-----------------+-----------------+-----------------+ 结论1：一个字母在CHAR保存是占用一个字节，汉字为3个字节（utf8mb3下） 结论2：最后一行的第二个字段我们添加时，第三个为空格，但是没被体现和占用 验证结论2： SELECT CONCAT('|',column2,'|') FROM table_char_demo1; +-------------------------+ | CONCAT('|',column2,'|') | +-------------------------+ | |abc| | | |我们的| | | |aa| | +-------------------------+ -- 总结： 所以在CHAR里插入尾部空格则会被自动剔除，但是在前面插入空格则不会被剔除 -- VARCHAR测试： -- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_varchar_demo1( column1 VARCHAR(3) COMMENT '可以添加长度3个字符', column2 VARCHAR(5) COMMENT '可以添加长度5个字符'); -- 查询表结构 DESC table_varchar_demo1; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | column1 | varchar(3) | YES | | NULL | | | column2 | varchar(5) | YES | | NULL | | +---------+------------+------+-----+---------+-------+ -- 添加数据 INSERT INTO type_demo.table_varchar_demo1 VALUES('abc','ab '),('我在','在哪里 '); SELECT * FROM table_varchar_demo1; +---------+-------------+ | column1 | column2 | +---------+-------------+ | abc | ab | | 我在 | 在哪里 | +---------+-------------+ -- 查询长度和测试后面空格 SELECT LENGTH(column1),LENGTH(column2) FROM table_varchar_demo1; +-----------------+-----------------+ | LENGTH(column1) | LENGTH(column2) | +-----------------+-----------------+ | 3 | 5 | | 6 | 11 | +-----------------+-----------------+ SELECT CONCAT('|',column1,'|'),CONCAT('|',column2,'|') FROM table_varchar_demo1; +-------------------------+-------------------------+ | CONCAT('|',column1,'|') | CONCAT('|',column2,'|') | +-------------------------+-------------------------+ | |abc| | |ab | | | |我在| | |在哪里 | | +-------------------------+-------------------------+ 由上面可以看出统计字符时；一个字母系统按照1个字节存储，一个汉字按3字节存储（utf8mb3下） 还要注意的是VARCHAR是可以存储尾部空格的 还要注意的是在普通模式下我们存储的字符超过限制时MySQL会自动截取，并抛出警告；严格模式则报错 INSERT INTO type_demo.table_varchar_demo1 VALUES('123456789','123456789'); SELECT CONCAT('|',column1,'|'),CONCAT('|',column2,'|') FROM table_varchar_demo1; +-------------------------+-------------------------+ | CONCAT('|',column1,'|') | CONCAT('|',column2,'|') | +-------------------------+-------------------------+ | |123| | |12345| | +-------------------------+-------------------------+
2：TEXT类型
TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型。在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度，但需要2个字节记录当前添加的文本数据长度。
| 文本字符串类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
| TINYTEXT | 小文本、可变长度 | L | 0<=L<=255 | 实际长度+1个字节 |
| TEXT[(M)] | 文本、可变长度 | L | 0<=L<=65535 | 实际长度+2个字节 |
| MEDIUMTEXT | 中等文本、可变长度 | L | 0<=L<=16777215 | 实际长度+3个字节 |
| LONGTEXT | 大文本、可变长度 | L | 0<=L<=4294967295(4G) | 实际长度+4个字节 |
注：由于实际存储的长度不确定，MySQL不允许TEXT类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)；注：TEXT系列的类型根据自身的长度需要指定的字节记录其本身文本数据的长度
-- 注：只有TEXT可以给出可选的长度M。 如果这样做，MySQL会将列创建为最小的TEXT类型，该类型足够大，可以容纳M个字符长的值 latin1:每个字符占用一字节；utf8mb3:每个字节占用三字节 说明：如果我在latin1字符集的数据表中建立TEXT(255)，则最终DESC查看表时显示TINYTEXT类型 说明：如果我在utf8mb3字符集的数据表中建立TEXT(255)，则最终DESC查看表时显示TEXT类型 -- 原因 255(字符数) * 3(占用字节) = 765字节 在TEXT长度范围内
-- 测试以TEXT为主（其它都是一样的） -- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_text_demo1( column1 TEXT COMMENT '默认长度字符65535', column2 TEXT(254) COMMENT '最大保存长度字符255', column3 TEXT(65536) COMMENT '最大保存长度字符16777215' )CHARSET latin1; -- 说明：其实我们指定长度后；MySQL会根据当前指定的大小给我们设置合适的文本类型 -- 查询表结构 DESC table_text_demo1; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | column1 | text | YES | | NULL | | | column2 | tinytext | YES | | NULL | | | column3 | mediumtext | YES | | NULL | | +---------+------------+------+-----+---------+-------+ -- 添加数据 INSERT INTO type_demo.table_text_demo1 VALUES('a','bb','ccc '); INSERT INTO type_demo.table_text_demo1 VALUES('a','aa ',' de'); SELECT * FROM table_text_demo1; +---------+---------+---------+ | column1 | column2 | column3 | +---------+---------+---------+ | a | bb | ccc | | a | aa | de | +---------+---------+---------+ -- 计算占用字节数 SELECT LENGTH(column1),LENGTH(column2),LENGTH(column3) FROM table_text_demo1; +-----------------+-----------------+-----------------+ | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) | +-----------------+-----------------+-----------------+ | 1 | 2 | 6 | | 1 | 3 | 5 | +-----------------+-----------------+-----------------+ -- 注意：上面我以latin1字符集测试；若使用utf8mb3则需要进行一个计算
八：ENUM类型
ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时ENUM类型只允许从成员中选取单个值，不能一次选取多个值。其所需要的存储空间由定义ENUM类型时指定的成员个数决定。但是注意的是不推荐使用此类型，因为每次取值和设置值时增加了一步转换操作，利用空间换时间
| 文本字符串类型 | 长度 | 长度范围 | 占用的存储空间 |
| EUNM | L | 1<=L<=65535 | 1~2个字节 |
注：当ENUM类型包含1～255个成员，需要1个字节存储空间；
当包含256～65535个成员时，需要2个字节存储空间。
ENUM类型的成员个数的上限为65535个。
枚举原理：枚举在进行数据规范（定义）的时候，系统会自动建立一个数字与枚举元素的对应关系（关系存放在日志中）；
然后在进行数据插入的时候,系统自动将字符转换成对应的数字存储，然后在进行数据提取的时候，系统自动将
数值转换成对应的字符串显示.
主要作用：
①：规范数据格式，数据只能是规定的数据中的其中一个
②：节省空间（因为最终存储的是1~65535的具体数值；所以每次存储和读取多了一步转换）
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_enum_demo1( column1 ENUM('男','女','secrecy') COMMENT '定义3个元素枚举'); -- 查询表结构 DESC table_enum_demo1; +---------+-----------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------------------------+------+-----+---------+-------+ | column1 | enum('男','女','secrecy') | YES | | NULL | | +---------+-----------------------------+------+-----+---------+-------+ -- 添加数据 -- 基本插入 INSERT INTO type_demo.table_enum_demo1 VALUES('男'),('secrecy'); -- 其它方式插入 INSERT INTO type_demo.table_enum_demo1 VALUES(1),('3'); SELECT * FROM table_enum_demo1; +---------+ | column1 | +---------+ | 男 | | secrecy | | 男 | | secrecy | +---------+ -- 总结：在MySQL中，是可以自动转换数据格式的，比如 SELECT 1 + '1YES'; 得出 2 -- 最终存储的方式 SELECT column1, column1+0 FROM table_enum_demo1; +---------+-----------+ | column1 | column1+0 | +---------+-----------+ | 男 | 1 | | secrecy | 3 | | 男 | 1 | | secrecy | 3 | +---------+-----------+ --其它查询 SELECT column1, LENGTH(column1) FROM table_enum_demo1; +---------+-----------------+ | column1 | LENGTH(column1) | +---------+-----------------+ | 男 | 3 | | secrecy | 7 | | 男 | 3 | | secrecy | 7 | +---------+-----------------+ 因为在查询时显示的数据已经是被转换为具体的内容了
九：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类型不同。
-- 创建表语句 CREATE TABLE IF NOT EXISTS type_demo.table_set_demo1( phone SET('一加','小米','华为','苹果','三星','锤子','VIVO','OPPO') COMMENT '定义枚举'); -- 查询表结构 DESC table_set_demo1; +-----+-----------------------------------------------------------+----+---+-------+-----+ |Field|Type |Null|Key|Default|Extra| +-----+-----------------------------------------------------------+----+---+-------+-----+ |phone|set('一加','小米','华为','苹果','三星','锤子','VIVO','OPPO') |YES | |NULL | | +-----+-----------------------------------------------------------+----+---+-------+-----+ -- 添加数据 INSERT INTO type_demo.table_set_demo1 VALUES('小米,华为'),('华为,三星,VIVO'); SELECT * FROM table_set_demo1; +--------------------+ | phone | +--------------------+ | 小米,华为 | | 华为,三星,VIVO | +--------------------+ -- 说明 其实我们在创建表的时候，有个规律，元素达到8个就为一字节了，元素达到16个就为两字节了，那这么做的目录是 存储时以二进制的方式存储 比如我们查看它具体插入的是什么 SELECT phone + 0 FROM table_set_demo1; +-----------+ | phone + 0 | +-----------+ | 6 | | 84 | +-----------+ 这给我们打印出具体的数值了，其实我们把这个翻译出二进制就可以更好表现出来 比如：84 转换：0 1 0 1 0 1 0 0 颠倒：0 0 1 0 1 0 1 0 对应：一加 小米 华为 苹果 三星 锤子 VIVO OPPO 结论：计算出来的1则代表具体的值；所以MySQL的SET类型是以二进制方式存储的，一个bit位代表一个元素 所以我们在插入具体的数值也是可以的，比如255（对应二进制11111111）正好选中全部元素 INSERT INTO type_demo.table_set_demo1 VALUES(255); +-----------------------------------------------------+ | phone | +-----------------------------------------------------+ | 小米,华为 | | 华为,三星,VIVO | | 一加,小米,华为,苹果,三星,锤子,VIVO,OPPO | +-----------------------------------------------------+
十：二进制字符串类型
1：BINARY与VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
BINARY(M)：为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，
表示只能存储1个字节。例如BINARY(8)，表示最多能存储8个字节，如果字段值不足(M)个字，
将在右边填充'\0'以补齐指定长度。
VARBINARY(M)：为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，
另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。
VARBINARY类型 必须指定(M) ，否则报错。
| 二进制字符串类型 | 特点 | 值的长度 | 占用空间 |
| BINARY(M) | 固定长度 | M（0<= M <= 255） | M个字节 |
| VARBINARY(M) | 可变长度 | M（0<= M <= 65535） | M+1个字节 |
-- BINARY测试 -- 创建表语句 CREATE TABLE IF NOT EXISTS `type_demo`.`table_binary_demo1`( column1 BINARY COMMENT '默认长度1个字节', column2 BINARY(2) COMMENT '长度为2个字节', column3 BINARY(3) COMMENT '长度为3个字节' ) CHARSET utf8mb3; -- 查询表结构 DESC `type_demo`.`table_binary_demo1`； +---------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------+------+-----+---------+-------+ | column1 | binary(1) | YES | | NULL | | | column2 | binary(2) | YES | | NULL | | | column3 | binary(3) | YES | | NULL | | +---------+-----------+------+-----+---------+-------+ -- 添加数据 INSERT INTO type_demo.table_binary_demo1 VALUES('a','bc','def'),('1','23','我'); SELECT * FROM type_demo.table_binary_demo1; +---------+---------+---------+ | column1 | column2 | column3 | +---------+---------+---------+ | a | bc | def | | 1 | 23 | 我 | +---------+---------+---------+ -- 注：为什么可以插入汉字，因为binary为3字节正好可以存储一个utfmb3的汉字，若2字节可以存储一个个gbk汉字 -- 会出现问题的数据插入 INSERT INTO type_demo.table_binary_demo1 VALUES('我','我们','我们的'); +---------+---------+---------+ | column1 | column2 | column3 | +---------+---------+---------+ | æ | æ | 我 | +---------+---------+---------+ -- 这个查询就会出现乱码，因为一个字节不能存储3个字节的一个汉字，所以column1就截取一个字节存储， -- column3可以存储三个字节，所以就直接把‘我’存储了，后面的则丢弃（在MySQL严格模式则报错） -- 比较说明 INSERT INTO type_demo.table_binary_demo1 VALUES('a','b','c'); +---------+---------+---------+ | column1 | column2 | column3 | +---------+---------+---------+ | a | b | c | +---------+---------+---------+ SELECT column1 = 'a' , column2 = 'b' , column3 = 'c' FROM table_binary_demo1; +---------------+---------------+---------------+ | column1 = 'a' | column2 = 'b' | column3 = 'c' | +---------------+---------------+---------------+ | 1 | 0 | 0 | +---------------+---------------+---------------+ 为什么 column1 = 'a' 比较为true，其它则都是0；看似值都一样，其实未存满的字节里面使用了'\0' SELECT column1 = 'a' , column2 = 'b\0' , column3 = 'c\0\0' FROM table_binary_demo1; +---------------+-----------------+-------------------+ | column1 = 'a' | column2 = 'b\0' | column3 = 'c\0\0' | +---------------+-----------------+-------------------+ | 1 | 1 | 1 | +---------------+-----------------+-------------------+ SELECT HEX(column1) , HEX(column2) , HEX(column3) FROM table_binary_demo1; +--------------+--------------+--------------+ | HEX(column1) | HEX(column2) | HEX(column3) | +--------------+--------------+--------------+ | 61 | 6200 | 630000 | +--------------+--------------+--------------+ -- 长度说明 INSERT INTO type_demo.table_binary_demo1 VALUES('1','1','1'); SELECT LENGTH(column1) , LENGTH(column2) , LENGTH(column3) FROM table_binary_demo1; +-----------------+-----------------+-----------------+ | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) | +-----------------+-----------------+-----------------+ | 1 | 2 | 3 | +-----------------+-----------------+-----------------+ -- 长度不足将在右边填充'\0'以补齐指定长度； -- VARBINARY测试 -- 创建表语句 CREATE TABLE IF NOT EXISTS `type_demo`.`table_varbinary_demo1`( column1 VARBINARY(1) COMMENT '设置长度1个字节', column2 VARBINARY(2) COMMENT '设置长度2个字节', column3 VARBINARY(3) COMMENT '设置长度3个字节' ) CHARSET utf8mb3; -- 查询表结构 DESC `type_demo`.`table_varbinary_demo1`; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | column1 | varbinary(1) | YES | | NULL | | | column2 | varbinary(2) | YES | | NULL | | | column3 | varbinary(3) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ -- 添加数据 INSERT INTO type_demo.table_varbinary_demo1 VALUES('a','bc','def'),('1','23','我'); SELECT * FROM type_demo.table_varbinary_demo1; +---------+---------+---------+ | column1 | column2 | column3 | +---------+---------+---------+ | a | bc | def | | 1 | 23 | 我 | +---------+---------+---------+ -- 长度说明 INSERT INTO type_demo.table_varbinary_demo1 VALUES('1','1','1'); SELECT LENGTH(column1) , LENGTH(column2) , LENGTH(column3) FROM table_varbinary_demo1; +-----------------+-----------------+-----------------+ | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) | +-----------------+-----------------+-----------------+ | 1 | 1 | 1 | +-----------------+-----------------+-----------------+ -- 由此证明；VARBINARY是可变长度，变成，只保存需要保存的数据，空出来的空间则不再需要； -- 但是需要使用1~2个字节记录这个数据的长度，255以为1个字节，255~65535则需要2个字节
2：BLOB类型
BLOB是一个二进制大对象，可以容纳可变数量的数据。MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB四种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片、音频和视频等。
需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。
| 二进制字符串类型 | 值长度 | 长度范围 | 占用空间 |
| TINYBLOB | L | 0 <= L <= 255 | L+1 个字节 |
| BLOB[(L)] | L | 0 <= L <= 65535（相当64KB） | L+2 个字节 |
| MEDIUMBLOB | L | 0 <= L <= 16777215（相当16MB） | L+3 个字节 |
| LONGBLOB | L | 0 <= L <= 4294967295（相当于4GB） | L+4 个字节 |
BLOB[(M)]:
最大长度为 65535字节的BLOB列。每个BLOB值都使用2字节长度前缀进行存储，该前缀指示值中的字节数。
对于此类型，可以给出可选的长度M。如果这样做，MySQL会将列创建为最小的BLOB类型，该类型足够大，可以容纳M字节长的值。
注：具体和TEXT类型差不多，只不过TEXT存储的是字符串；而BLOB存储的是二进制
TEXT和BLOB的使用注意事项：
在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。
①：BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会
在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期
使用OPTIMIZE TABLE功能对这类表进行碎片整理 。
②：如果需要对大文本字段进行模糊查询，MySQL提供了前缀索引。但是仍然要在不必要的时候避免检索
大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的
WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。
③：把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可
以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的
碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过
网络传输大量的BLOB或TEXT值。
十一：JSON类型
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式 。简洁和清晰的层次结构使得JSON成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON可以将JavaScript对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。在MySQL5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。
-- JSON测试 -- 创建表语句 CREATE TABLE IF NOT EXISTS `type_demo`.`table_json_demo1`( column1 JSON COMMENT '创建一个JSON类型的字段' ) CHARSET utf8mb3; -- 查询表结构 DESC `type_demo`.`table_json_demo1`； +---------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------+------+-----+---------+-------+ | column1 | json | YES | | NULL | | +---------+------+------+-----+---------+-------+ -- 添加数据 INSERT INTO type_demo.table_json_demo1 VALUES ('{"name":"张三","age":22}'),('{"name":"李四","age":22}'),('{"name":"麻子","age":25}'); SELECT * FROM type_demo.table_json_demo1; +-------------------------------+ | column1 | +-------------------------------+ | {"age": 22, "name": "张三"} | | {"age": 22, "name": "李四"} | | {"age": 25, "name": "麻子"} | +-------------------------------+ -- 检索指定字段 SELECT column1 -> '$.name' '姓名', column1 -> '$.age' '年龄' FROM table_json_demo1; +----------+--------+ | 姓名 | 年龄 | +----------+--------+ | "张三" | 22 | | "李四" | 22 | | "麻子" | 25 | +----------+--------+ -- 更新指定字段 SELECT * FROM table_json_demo1 WHERE column1 -> '$.name' = "张三"; +-------------------------------+ | column1 | +-------------------------------+ | {"age": 22, "name": "张三"} | +-------------------------------+
十二：总结
在定义数据类型时：
如果确定是整数，就用INT；
如果确定是小数，一定用定点数类型DECIMAL(M,D)；
如果是日期与时间，就用DATETIME。
这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过凡事都是有两面的，可靠性好，并不意味着高效。
比如TEXT虽然使用方便，但是效率不如CHAR(M)和VARCHAR(M)。
关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：
阿里巴巴《Java开发手册》之MySQL数据库：
任何字段如果为非负数，必须是 UNSIGNED
【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得
到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并
分开存储。
【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大
于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
.
作者：蚂蚁小哥
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文链接，否则保留追究法律责任的权利。
