MySql基础之数据类型精讲（重点）_mysql数据库常见数据类型

1、MySQL中的数据类型

常见数据类型的属性，如下：

2、整数类型

2.1、类型介绍

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。
它们的区别如下表所示：

2.2、可选属性

宽度

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

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

• 整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认
  的宽度值。

举例：

CREATE TABLE test_int1 
( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );
1
2

查看表结构 （MySQL5.7中显式如下，MySQL8中不再显式范围）

• TINYINT有符号数和无符号数的取值范围分别为-128~127和0~255
• 由于负号占了一个数字位，因此TINYINT默认的显示宽度为4
• 同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。

举例：

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL )

DESC test_int2; 

INSERT INTO test_int2(f1,f2,f3) 
VALUES(1,123,123); 

INSERT INTO test_int2(f1,f2) 
VALUES(123456,123456); 

INSERT INTO test_int2(f1,f2,f3) 
VALUES(123456,123456,123456);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

UNSIGNED

• UNSIGNED ： 无符号类型（非负）
• 所有的整数类型都有一个可选的属性UNSIGNED（无符号属性）
• 无符号整数类型的最小取值为0
• 所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型
• int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)

CREATE TABLE test_int3(
f1 INT UNSIGNED );
1
2

ZEROFILL

• ZEROFILL ：0填充，（如果某列是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 进行填充。

3、浮点类型

3.1、类型介绍

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

问题1：FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？

FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。

问题2：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于
有符号数取值范围大于等于零的部分呢？

MySQL 存储浮点数的格式为： 符号(S) 、 尾数(M) 和 阶码(E) 。因此，无论有没有符号，MySQL 的浮
点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于
零的部分。

3.2、数据精度说明

浮点数类型有个缺陷，就是不精准。下面我来重点解释一下为什么 MySQL 的浮点数不够精准。比如，我
们设计一个表，有f1这个字段，插入值分别为0.47,0.44,0.19，我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。
而使用sum之后查询：

CREATE TABLE test_double2(
f1 DOUBLE 
);

INSERT INTO test_double2 
VALUES(0.47),(0.44),(0.19);
1
2
3
4
5
6

• MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据
• 都是采用二进制的方式来进行存储的
• 比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。如果尾数不是 0 或 5（比如 9.624），你就无法用一个二进制数来精确表达。进而，就只好在取值允许的范围内进行四舍五入。

4、定点类型

MySQL中的定点数类型只有 DECIMAL 一种类型。

• 使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。
• 例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
• DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。
• 定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。
• 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

5、位类型：BIT

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

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

CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64) 
);

INSERT INTO test_bit1(f1) 
VALUES(1); 

#Data too long for column 'f1' at row 1 
INSERT INTO test_bit1(f1) 
VALUES(2); 

INSERT INTO test_bit1(f2) 
VALUES(23);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6、日期与时间类型

MySQL8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

6.1、YEAR类型

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要 1个字节 的存储空间。

• 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
• 以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
    1）当取值为01到69时，表示2001到2069；
    2）当取值为70到99时，表示1970到1999；
    3）当取值整数的0或00添加的话，那么是0000年；
    4）当取值是日期/字符串的’0’添加的话，是2000年。

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)， 从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

举例：

CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4) );

INSERT INTO test_year 
VALUES('2020','2021');
1
2
3
4
5
6

6.2、DATE类型

DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示日期。需要 3个字节 的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

• 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
• 使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。

举例：

CREATE TABLE test_date1(
f1 DATE );

INSERT INTO test_date1 
VALUES ('2020-10-01'), ('20201001'),(20201001);
1
2
3
4
5

Oracle方式

CREATE TABLE test_date2(
f1 DATE );

--方式一：
INSERT INTO test_date2 
VALUES (sysdate);
--方式二：
INSERT INTO test_date2 
VALUES (to_date('2021-12-12','yyyy-MM-dd'));
1
2
3
4
5
6
7
8
9

6.3、TIME类型

• TIME类型用来表示时间，不包含日期部分。
• 在MySQL中，需要 3个字节 的存储空间来存储TIME类型的数据
• 可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。
• TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

举例：

CREATE TABLE test_time1(
f1 TIME );

INSERT INTO test_time1 VALUES('12:30:29'), ('60:35:29')
1
2
3
4

6.4、DATETIME类型

• DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间
• 在格式上为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒
• 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。（以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式）

举例：

CREATE TABLE test_datetime1( 
dt DATETIME 
);

INSERT INTO test_datetime1 
VALUES ('2021-01-01 06:50:30'), ('20210101065030');

INSERT INTO test_datetime1 
VALUES (CURRENT_TIMESTAMP()), (NOW());
1
2
3
4
5
6
7
8
9

6.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存储的同一个时间值，在不同的时区查询时会显示不同的时间。

举例：

CREATE TABLE test_timestamp1( 
ts TIMESTAMP 
);

INSERT INTO test_timestamp1 
VALUES ('2022-01-20 03:14:07');

INSERT INTO test_timestamp1 
VALUES (CURRENT_TIMESTAMP()), (NOW());
1
2
3
4
5
6
7
8
9

Oracle方式

CREATE TABLE test_timestamp2( 
ts TIMESTAMP 
);

-- 方式一：
INSERT INTO test_timestamp2 
VALUES (CURRENT_TIMESTAMP);
-- 方式二：
INSERT INTO test_timestamp2 
VALUES (to_date('2021-10-26 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
-- 方式三
INSERT INTO test_timestamp2 
VALUES (to_timestamp('2020-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));

1
2
3
4
5
6
7
8
9
10
11
12
13
14

TIMESTAMP和DATETIME的区别

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

7. 文本字符串类型

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

7.1、CHAR与VARCHAR类型

• CHAR和VARCHAR类型都可以存储比较短的字符串
• oracle长度默认表示的是字节

CHAR类型

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

举例：

CREATE TABLE test_char1( 
c1 CHAR, 
c2 CHAR(5) 
);

INSERT INTO test_char1 
VALUES('a','Tom');

INSERT INTO test_char1(c2) 
VALUES(' a b ');
1
2
3
4
5
6
7
8
9
10

• mysql会将后面的空格省略，前面和中间不省略
• oracle所有的空格都会补全

VARCHAR类型

• VARCHAR(M) 定义时， 必须指定 长度M，否则报错
• MySQL5.0版本以上，varchar(20)：指的是20字符
• VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节（记录占用长度）。

#Column length too big for column 'NAME' (max = 21845); 
CREATE TABLE test_varchar2( 
NAME VARCHAR(65535) #错误 
);
1
2
3
4

7.2、TEXT类型

TEXT用来保存文本类型的字符串如下：

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键

举例：

CREATE TABLE test_text( 
tx TEXT 
);

INSERT INTO test_text 
VALUES('atguigu '); 

SELECT CHAR_LENGTH(tx) 
FROM test_text; #10
1
2
3
4
5
6
7
8
9

说明在保存和查询数据时，并没有删除TEXT类型的数据尾部的空格。

8、ENUM类型

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

举例：

CREATE TABLE test_enum( 
season ENUM('春','夏','秋','冬','unknow') 
);

INSERT INTO test_enum 
VALUES('春'),('秋'); 

# 忽略大小写 
INSERT INTO test_enum 
VALUES('UNKNOW');
1
2
3
4
5
6
7
8
9
10

9、SET类型

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

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

SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一次
选择多个成员，这一点与ENUM类型不同。

举例：

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

10、二进制字符串类型

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

MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。

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) ，否则报错。

举例：

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3), 
# f3 VARBINARY,
f4 VARBINARY(10) 
);

INSERT INTO test_binary1(f1,f2) 
VALUES('a','a'); 

INSERT INTO test_binary1(f1,f2) 
VALUES('尚','尚');#失败

INSERT INTO test_binary1(f2,f4) 
VALUES('ab','ab');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

BLOB类型

• BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
• 可以存储一个二进制的大对象，比如 图片、音频和视频等。

TEXT和BLOB的使用注意事项：

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

• 如果需要对大文本字段进行模糊查询，MySQL 提供了前缀索引。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值

• 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的 碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

11、JSON 类型

• 在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"}}');
1
2
3
4
5
6
7

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

通过“->”和“->>”符号，从JSON字段中正确查询出了指定的JSON数据的值。