MySQL数据类型

数据类型

在定义数据类型时，如果确定是整数，就用INT；如果是定点数类型DECIMAL；如果是日期与时间，就用DATETIME。

这样呢确保系统不会因为数据类型定义出错。

阿里《Java开发手册》中有关数据类型的

• 任何字段如果为非负数，必须时UNSIGNED

• 【强制】小数类型为DECIMAL,禁止使用FLOAT和DOUBLE

  • 在存储的时候，FLOAT和DOUBLE都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过DECIMAL的范围

• 【强制】如果存储的字符串长度几乎相等，使用CHAR定长字符串类型

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

MySQL中的数据类型

类型	举例
整数	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点	FLOAT、DOUBLE
顶点数	DECIMAL
位	BIT
日期时间	YEAT、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、MULTINESTRING、MULTIPOLYGON、GROMETRYCOLLECTION

常见数据类型

MySQL关键字	含义
NULL	数列可包含NULL值
NOT NULL	数列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	主动递增，适用于整数型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

数据类型 UNSIGNED;
--无符号数

整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147382548~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

可选属性

M

M:表示显示宽度，M的取值范围时（0，255）。例如，int（5）：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

显示宽度与类型可以存储的值范围无关。从MySQL8.0.17开始，整数数据类型不推荐使用显示宽度属性

CREATE TABLE 表名(
    data1 INT,
    data2 INT(5),
    data3 INT(5) ZEROFILL
)
-- 显示宽度为5。当insert的值不足5位时，使用0填充
-- 当使用ZEROFILL时，自动添加UNSIGNED

UNSIGNED

UNSIGNED：无符号类型（非负).无符号整数类型的最小取值为0

ZEROFILL

ZEROFILL:0填充，（如果某列时ZEROFILL,那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

INT()，必须和UNSIGNED ZEROFILL一起使用才有意义

使用场景

TINYINT:一般用于枚举数据，取值范围小且固定的场景

SMALLINT:较小范围的统计数据

MEDIUMINT:较大整数的计算

INT、INTGER:取值范围足够大，一般情况下不用考虑超限问题，用得最多

BIGINT:只用当处理特别巨大得整数时才会用到

浮点类型

浮点数和定点数类型得特点是可以处理小数

• FLOAT 单精度浮点数 占用4个字节

• DOUBLE 双精度浮点数 占用8个字节

• REAL 默认就是DOUBLE。当SQL模式设定为启用"REAL_AS_FLOAT",那么，MySQL就认为REAL是FLOAT

  SET sql_mode="REAL_AS_FLOAT";
  

精度说明

对于浮点类型，在MySQL中单精度使用4字节，双精度使用8字节

• MySQL允许使用非标准语法：FLOAT(M,D)或DOUBLE(M,D)。M为精度，D为标度。M=整数位+小数位；D=小数位

精度误差

MySQL用4字节存储FLOAT类型数据，用8字节来存储DOUBLE类型数据。无论哪个，都是采用二进制得方式来进行存储的。二进制数无法精确表达，进而，就只好在取值允许的范围内进行四舍五入

因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等

可以用定点数类型DECIMAL

定点数类型

数据类型	字节数	含义
DECIMAL(M,D),DEC,NUMERIC	M+2字节	有效范围由M和D决定

• DECIMAL(M,D)的最大取值范围与DOUBLE类型一样
• 定点数在MySQL内部是以字符串的形式存储

定点与浮点的区别

• 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（计算化学、分子建模、流体动力学）
• 定点数类型取值范围相对小，但精准，没有误差，适合精度要求极高的场景（金额计算）

位类型

BIT类型，如果没有指定（M），默认是1位。这个1位，表示只能存1位的二进制。这里（M）表示二进制的位数，文书最小值为1，最大值为64

二进制字符串类型	长度	长度范围	占用空间
BIT(M)	M	1<=M<=64	约为（M+7）/8个字节

注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。使用SELECT命令查询位字符时，可以用BIT()或HEX()函数进行读取,不然查询输出的将是16进制数据

SELECT 字段1，字段2
FROM 表名;
-- 输出16进制

SELECT BIN(字段1)，HEX(字段2)
FROM 表名;
-- 输出二进制

SELECT 字段1+0，字段2+0
FROM 表名;
-- 输出十进制

日期与时间类型

类型	名称	字节	日期格式	最小值	最大值
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-10 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-10 00:00:00 UTC	2038-01-19 03:14:07 UTC

• 可以使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

  INSERT INTO 字段
  VALUES (NOW(),NOW())
  

TIMESTAMP

• 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间

TIMESTAMP与DATETIME的区别

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映处插入时当地的时区，其他时区的人查看数据必然会有误差

建议

不建议使用DATETIME存储，而是使用时间戳，因为DATETIME虽然直观，但不便于计算

SELECT UNIX_TIMESTAMP();

文本字符串类型

类型	长度	长度范围	占用空间
CHAR(M)	M	0<=M<=255	M个字节
VARCHAR(M)	M	0<=M<=625535	M+1个字节
TINYTEXT	L	0<=L<=255	L+2个字节
TEXT	L	0<=L<=65535	L+2个字节
MEDIUMEXT	L	0<=L<=16777215	L+3个字节
LONGTEXT	L	0<=L<=4294967295	L+4个字节
ENUM	L	0<=L<=65535	1或2个字节
SET	L	0<=L<=64	1,2,3,4或8个字节

CHAR与VARCHAR

• CHAR 固定长度
  • 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。MySQL检索CHAR类型的数据时，CHAR类型字段会去除尾部的空格
• VARCHAR (M)可变长度
  • 必须指定长度（M），否则报错，最大值为21845

类型	空间上	时间上	使用场景
CHAR(M)	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	节省存储空间	效率低	非CAHR的情况

TEXT类型

TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR,VARCHAR

类型	特点	长度	占用存储空间
TINYTEXT	小文本、可变长度	0<=L<=255	L+2个字节
TEXT	文本、可变长度	0<=L<=65535	L+2个字节
MEDIUMTEXT	中等文本、可变长度	0<=L<=16777215	L+3个字节
LONGTEXT	大文本、可变长度	0<=L<=4294947295（相当于4GB）	L+4个字节

• 由于实际存储的长度不确定，MySQL不允许text类型的字段做主键。此时可以用CHAR(M)或VARCHAR(M)

ENUM类型

也叫枚举类型，

文本字符串类型	长度	长度范围	占用存储的空间
ENUM	L	1<=L<=65535	1或2个字节

-- 创建
CREATE TABLE test_enum(
    season ENUM('一','2',...,'unknow')
)

-- 添加
INSERT INTO test_enmu(
    VALUES(1),('2');
)
-- 忽略大小写
-- 也可以看使用索引进行枚举元素的调用
-- 没有限制为NOT NULL时，插入NULL也是有效的

SET类型

一个字符串对象，可以包含0个或多个成员，但成员个数的上限为64

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

• 插入重复的SET类型成员时，MySQL会自动删除重复的成员

  INSERT INTO test_set(s) VALUES ('A,B,C,A');
  -- 重复的A会过滤掉
  

二进制字符串类型

主要存储一些二进制数据，可以存储照片、音频和视频等二进制数据

BINARY与VARBINARY

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

BLOB

二进制大对象

在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中

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

TEXT和BLOB得使用注意事项

1. BLOB和TEXT值会引起自己的一些问题，特别是执行了大量得删除或更新操作的时候。删除这种值会在数据表中留下很大的空洞，以后填入这些空洞的记录可能长度不同。为了提高性能，建议定期使用OPTIMZE TABLE功能对这类表进行碎片化整理
2. 如果需要对大文本字段进行模糊查询，MySQL提供前缀索引，但是仍然要在不必要的时候避免大型的BLOB或TEXT值。例如，SELECT*查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。狗则你可能毫无目的地在网络上传输大量的值
3. 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把元数据表中的数据列转换为固定的数据行格式，那么他就是有意义的。这会减少主表中的碎片,使你得到固定长度数据行的性能优势。他还使你在主数据表上运行SELECT*查询得时候不会通过网络传输大量得BLOB或TEXT值。

JSON类型

JavaScript Object Notation

一种轻量级得数据交换格式

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

创建、插入JSON类型数据

1. 建表时

   CREATE TABLE 表名(
       字段名 json
   );
   

2. 插入JSON数据

   INSERT INTO 表名(字段名)
   VALUES ('{"name":"lf","age":18,"address":{"province":"beijing","city":"beijing"}}')
   

3. 查询JSON类型得字段中数据的某个具体的值，可以使用"->“和”->>"符号

   SELECT 	字段名 -> '$.name' AS NAME,
   		字段名 ->'$.age' AS age,
   		...
   FROM 表名;-- 字段中数据的某个具体的值
   
   SELECT *
   FROM 表名;-- 字段全部
   

空间类型

单值类型：GEOMETRY、POINT、LINESTRING、POLYGON

集合类型：MULTIPOINT、MUTILINESTRING、MULTIPOLYGON、GROMETRYCOLLECTION

• Geometry时所有空间集合类型得基类，其他类型如POINT、LINESTRING、POLYGON都是Grometry的子类

  • POINT，一个坐标值，例如POINT(1 2)，坐标值支持DECIMAL类型，经度（longitude）在前，维度（latitude）在后，用空格分隔
  • LineString，线，由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的（simple）；如果起点和终点重叠，那就是封闭的（closed）。例如LINESTRING(30 10 ,10 30,99 10),点与点之间用逗号分隔，一个点中的经纬度用空格分隔，与POINT格式一致
  • Polygon，多边形。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。简单的就是只有一个外界的情况，例如POLYGON((0 0,10 0,10 10,0 10)).

• Multipoint、MultiLineString、MultiPolygon、GeometryCollection这4种类型都是集合类，是多个Point、LineString或Polygon组合而成

  • Multipoint 点的集合

    MULTIPOINT(10 40),(40 30),(20 20)
    MULTIPOINT(10 40,40 30,20 20)
    

  • MultiLineString 线的集合

    MULTILINESTRING(10 40,40 30,20 20)，(30 10 ,10 30,99 10)
    

  • MultiPolygon 多边形的集合

    MUTIPOLYGON(((40 40,20 45,45 30)),((15 5,40 10,10 20)))
    MUTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20)))
    

  • GeometryCollection 点、线、多边形的集合

    GROMETRYCOLLECTION(
        POINT(1 2),
        LINESTRING(30 10 ,10 30,99 10),
        POLYGON((0 0,10 0,10 10,0 10))
    )