【MySQL】第十六部分 MySQL数据类型详解

【MySQL】第十六部分 MySQL数据类型详解

---

---

16. MySQL数据类型详解

16.1 整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
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

上述类型使用的场景:

TINYINT：一般用于枚举数据，比如系统设定特定的身份。

SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等。

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

BIGINT：只有当你处理特别巨大的整数时才会用到。比如: 双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

在实际的工作中,先确保数据不会超过取值范围,再去考虑如何节省空间,否则得不偿失.

16.2 浮点类型

浮点数和定点数类型的特点是可以处理小数，可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。 MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE。

• FLOAT 表示单精度浮点数；
• DOUBLE 表示双精度浮点数；
  

FLOAT 和 DOUBLE 它们之间主要的区别是: FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大.

浮点数类型精度会有误差:

CREATE TABLE test1(
	f1 DOUBLE
);

INSERT INTO test1
VALUES(0.47),(0.44),(0.19);

SELECT SUM(f1) 
FROM test1;

16.3 定点数类型

数据类型	字节数	含义
DECIMAL(M,D),DEC,NUMERIC	M+2字节	有效范围由M和D决定 ( 0<=M<=65，0<=D<=30) M表示总位数,D表示小数点保留几位

例如: 定义DECIMAL（6,2）的类型，表示该取值范围是 -9999.99 到 9999.99。

浮点数 vs 定点数

浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大,但是精度会缺失,适用场景: 需要取值范围大,但是可以容许微小误差的业务场景.

定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 比如涉及金额计算的场景.

ALTER TABLE test1 ADD f2 DECIMAL(6,2)

INSERT INTO test1(f2)
VALUES(0.47),(0.44),(0.19);

SELECT SUM(f2)
FROM test1;

16.4 位类型 BIT

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

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

BIT类型，如果没有指定(M)，默认是1位,表示只能存1位的二进制值。在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

CREATE TABLE test1(
	f1 BIT(8),
	f2 BIT
)

INSERT INTO test1 
VALUES(12,0)

SELECT * FROM test1

16.5 日期和时间类型

• 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:07UTC

TIMESTAMP和DATETIME的区别：

1. TIMESTAMP存储空间比较小，表示的日期时间范围也比较小

2. 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。

3. 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。

4. TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

在工作中,用得最多的日期时间类型，就是 DATETIME。

CREATE TABLE test_time(
	f1 YEAR,
	f2 DATE,
	f3 TIME,
	f4 DATETIME,
	f5 TIMESTAMP
)

INSERT INTO test_time
VALUES (CURRENT_DATE,CURRENT_DATE,CURRENT_TIME,NOW(),NOW())

SELECT * FROM test_time

16.6 文本字符串类型

文本字符串总体上分为CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET等类型。

16.6.1 CHAR VS VARCHAR类型

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

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

VARCHAR(M)类型必须指定长度M，否则报错,MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。

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

情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度。

情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

16.6.2 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和BLOB类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

16.6.3 ENUM类型

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

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

CREATE TABLE test_enum
(
	gender ENUM("男","女"),
	season ENUM("春","夏","秋","冬")
)

INSERT INTO test_enum
VALUES("男","秋")

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL,NULL);

# 允许按指定索引位置的枚举值
INSERT INTO test_enum
VALUES(1,2)

SELECT * FROM test_enum

16.6.4 SET类型

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')
);

INSERT INTO test_set (s) VALUES ('A'), ('A,B');

#插入重复的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');

SELECT *
FROM test_set;

16.7 二进制字符串类型

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

16.7.1 BINARY和VARBINARY

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

类似于CHAR和VARCHAR,区别只是它们存储的是二进制字符串

BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储1个字节。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充’\0’以补齐指定长度。

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

16.7.2 BLOB类型

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

二进制字符串类型	值的长度	长度范围	占用空间
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 个字节

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

16.8 JSON 类型

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

CREATE TABLE test_json(
	js json
);

INSERT INTO test_json (js) 
VALUES ('{"name":"lilei", "age":18, "address":{"province":"shanghai", "city":"shanghai"}}');

综上

1. 定义整数的时候,就用INT
2. 定义浮点数的时候,就用DECIMAL(M,D)
3. 定义时间和日期的时候,就用DATETIME
4. 定义字符串的时候,根据实际的需求选择CHAR或者VARCHAR
5. 定义字段为TEXT,独立出一张表,用主键来对应,避免影响其他字段索引的效率

---

总结

以上就是今天要讲的内容，希望对大家有所帮助！！！