【MySQL基础】数据类型

整数类型

整数类型一共有 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	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

CREATE TABLE 表名(
字段1 INT(M),
字段2 INT(5) ZEROFILL,
字段3 INT UNSIGNED
);

说明：

1. M表示显示宽度，取值范围是(0, 255)。
2. 显示宽度和数据类型的取值范围是无关的，只表明最大可能显示的数字个数
3. 当数据宽度小于M的时候，在数字前面需要用字符（默认空格）填满宽度，如果指定了ZEROFILL只是表示不够M位时，用0在左边填充
4. 如果插入的数据宽度超过显示宽度限制，按照类型的实际宽度进行保存。
5. 从MySQL8.0.17开始，整数数据类型不推荐使用显示宽度属性
6. 无符号整数类型的最小取值为0，INT类型默认显示宽度为INT(11)，无符号INT类型默认显示宽度为INT(10)

适用场景

• TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
• SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等
• MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等
• INT、INTEGER：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号
• BIGINT：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等

浮点类型

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）

类型名称	字节数	有符号数值范围	无符号数值范围
FLOAT	4	-3.402823466E+38～-1.175494351E-38, 0, 1.175494351E-38～3.402823466E+38	0 和 1.175494351E-38～3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308～-2.2250738585072014E-308, 0, 2.2250738585072014E-308～1.7976931348623157E+308	0 和 2.2250738585072014E-308～1.7976931348623157E+308

CREATE TABLE 表名(
字段1 FLOAT,
字段2 FLOAT(5,2),
字段3 DOUBLE,
字段4 DOUBLE(5,2) 
);

说明：
浮点数类型有个缺陷，就是不精准

定点数类型

定点数的数据类型，也是存储小数的一种方式。

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

备注：

• 定点数在MySQL内部是以字符串的形式进行存储，当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

浮点数与定点数差异

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

日期和时间类型

类型	名称	字节	日期格式	最小值	最大值
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:07UTC

字符串类型

字符串(文本)类型	特点	长度	长度范围	占用的存储空间
CHAR(M)	固定长度	M	0 <= M <= 255	M个字节
VARCHAR(M)	可变长度	M	0 <= M <= 65535	(实际长度 + 1) 个字节

CHAR类型：

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

VARCHAR类型：

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

文本类型

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。

文本字符串类型	特点	长度	长度范围	占用的存储空间
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（相当于4GB）	L + 4 个字节

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

二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数
据。

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

BLOB类型
BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片、音频和视频等。

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

JSON 类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。JSON可以将 JavaScript对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。
在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效

CREATE TABLE test_json( 
	js json 
);
# 插入语句
INSERT INTO test_json (js) VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');
# 检索JSON类型的字段
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city-> FROM test_json;

备注：
当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号。

位类型

BIT类型中存储的是二进制值，类似010110。

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

注意：
BIT类型，如果没有指定(M)，默认是1位。在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取

ENUM类型

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

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

注意：

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

# 忽略大小写 
INSERT INTO test_enum VALUES('UNKNOW'); 
# 允许按照角标的方式获取指定索引位置的枚举值 
INSERT INTO test_enum VALUES('1'),(3); 
# Data truncated for column 'season' at row 1 
INSERT INTO test_enum VALUES('ab'); 
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的 
INSERT INTO test_enum VALUES(NULL);

SET类型

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

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

# 创建表
CREATE TABLE test_set(
	s SET ('A', 'B', 'C') 
);
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。 
INSERT INTO test_set (s) VALUES ('A,B,C,D');

空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析