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

整数类型的可选属性有三个：

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

#如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败

代码：

CREATE TABLE test_int2(

f1 INT,

f2 INT(5),

f3 INT(5) ZEROFILL

);



INSERT INTO test_int2(f1,f2)

VALUES(123456,123456);#f2添加的数据超出长度,但仍然添加成功，所以M对添加数据的范围没有影响，只#要没有超出该类型规定的范围就可以添加成功

#当使用ZEROFILL时，自动添加UNSIGNED ;



INSERT INTO test_int2(f3)

VALUES (123),(123456);#M要搭配ZEROFILL使用，当添加的数据不足显示宽度就用0补充



SELECT * FROM test_int2;

答：不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即 显示宽度与类型可以存储的 值范围无关 。从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

UNSIGNED : 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。

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 进行填充。

适用场景

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

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

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

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

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

浮点数

类型介绍

MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

①FLOAT 表示单精度浮点数；

②DOUBLE 表示双精度浮点数；

③REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那 么，MySQL 就认为

REAL 是 FLOAT。

CREATE TABLE test_double1(

f1 FLOAT,

f2 FLOAT(5,2),

f3 DOUBLE,

f4 DOUBLE(5,2)

);



DESC test_double1;



#double测试

INSERT INTO test_double1(f3,f4)

VALUES (123.45,123.456);#小数部分超出显示长度，四舍五入



INSERT INTO test_double1(f3,f4)

VALUES (123.45,1234.456);#整体长度超出显示长度，报错

数据精度说明

MySQL允许使用 非标准语法：

FLOAT(M,D) 或 DOUBLE(M,D) 。这里，M称为 精度 ，D称为 标度 。(M,D)中 M=整数位+小数

位，D=小数位。 D<=M<=255，0<=D<=30。

FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定)来显示。

说明：浮点类型，也可以加 UNSIGNED ，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然

只能表示0-9.99的范围。

处理方法：

如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值

如果存储时，小数点部分若超出范围，就分以下情况：

              若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小 数位后保存。

                若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。

举例

CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
);

DESC test_double1;

#double测试
INSERT INTO test_double1(f3,f4)
VALUES (123.45,123.456);#小数部分超出显示长度，四舍五入

INSERT INTO test_double1(f3,f4)
VALUES (123.45,1234.456);#整体长度超出显示长度，报错

精度误差

如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来

判断两个数是否相等。

举例：

CREATE TABLE test_double2(

f1 DOUBLE

);

INSERT INTO test_double2

VALUES(0.47),(0.44),(0.19);



SELECT SUM(f1)

FROM test_double2;#结果显示并不是值1.1，存在精度误差

定点数

1 类型介绍

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

②DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。 DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。

③定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。

④当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

举例：

CREATE TABLE test_decimal1(

f1 DECIMAL,

f2 DECIMAL(5,2)

);

DESC test_decimal1;



INSERT INTO test_decimal1(f1)

VALUES (123),(123.45);#不指定精度和标度时，其默认为DECIMAL(10,0);小数部分四舍五入



SELECT * FROM test_decimal1;



INSERT INTO test_decimal1(f2)

VALUES(67.567);#存在四舍五入（5，2）--------小数部分只有两位



INSERT INTO test_decimal1(f2)

VALUES (999,995);#四舍五入后超出范围，报错

定点数与浮点数比较

①浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用

于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）

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

举例：

#decimal精度问题

CREATE TABLE test_decimal3(

f1 DECIMAL(5,2)

);



INSERT INTO test_decimal3

VALUES(0.47),(0.44),(0.19);



SELECT SUM(f1)

FROM test_decimal3;#结果显示是值1.1，不存在精度误差

位类型

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

二进制字符串类型                       长度                          长度范围                                   占用空间

BIT(M)                                               M                             1 <= M <= 64                           约为(M + 7)/8个字节

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

注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

举例：

CREATE TABLE test_bit1(

f1 BIT,

f2 BIT(5),

f3 BIT(64)

);                



INSERT INTO test_bit1(f1)

VALUES (0),(1);



SELECT * FROM test_bit1;



INSERT INTO test_bit1(f1)

VALUES (2);#指定(M)，默认是1位，而2转化位二进制是01，占两位超出范围



SELECT BIN(f1),BIN(f2),HEX(f1),HEX(f2)

FROM test_bit1;#bin 表示以二进制显示，hex表示以十六进制显示；



SELECT f1 + 0,f2 + 0

FROM test_bit1;#+0表示以十进制方式显示

日期时间类型

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

YEAR类型-------------->'年'

以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。

以2位字符串格式表示YEAR类型，最小值为00，最大值为99。

                   ①当取值为01到69时，表示2001到2069；

                   ②当取值为70到99时，表示1970到1999；

                   ③当取值整数的0或00添加的话，那么是0000年；

                   ④当取值是日期/字符串的'0'添加的话，是2000年。

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)，

从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

DATE类型--------------->'YYYY-MM-DD'

①以 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() 函数，会插入当前系统的日期。

TIME类型------------------->'HH:MM:SS'，可以表示时间间隔

（1）可以使用带有冒号的

字符串，比如' 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。

（2）可以使用不带有冒号的

字符串或者数字，格式为' HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存

储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示

00:12:10，而不是12:10:00。

（3）使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。

DATETIME类型--------------->'YYYY-MM-DD HH:MM:SS'

可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。

使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间。

TIMESTAMP类型------------------->'YYYY-MM-DD HH:MM:SS'

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时

区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

TIMESTAMP和DATETIME的区别：

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

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

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

④TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能

反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

DATETIME存的是多少就是都少；TIMESTAMP与时区有关，同一个数据存入，在不同时区显示不同

文本字符串类型

文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、

LONGTEXT 、 ENUM 、 SET 等类型。

CHAR与VARCHAR类型

CHAR类型：

①CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。

②如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。（在计算长度时尾部自己添加的空格，不计算在内）

③定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

举例：

CREATE TABLE test_char1(

c1 CHAR,

c2 CHAR(5)

);



INSERT INTO test_char1(c2)

VALUES ('尚硅谷教育');#M表示的时字符个数，不是字节个数



INSERT INTO test_char1(c2)

VALUES ('尚硅谷IT教育');#超出长度，添加失败



SELECT * FROM test_char1;

VARCHAR类型：

①VARCHAR(M) 定义时， 必须指定 长度M，否则报错。

②MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。

③检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。 

举例：

CREATE TABLE test_varchar2(

NAME VARCHAR(65535)#M不能是65535，一个汉字占3个字节，所以M最大值位21845（21845*3=65535）

);

哪些情况使用 CHAR 或 VARCHAR 更好

  char(5)，哪怕只存入1个字符，占空间内存仍是5个字符的空间

  varchar会计算输入的是字母还是汉字，计算字节数开辟空间

TEXT类型（存较大的文本段）

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用

CHAR(M)，或者 VARCHAR(M)。

ENUM类型（多选一）

①当ENUM类型包含1～255个成员时，需要1个字节的存储空间；

②当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。

③ENUM类型的成员个数的上限为65535个。

举例：

CREATE TABLE test_enum(

season ENUM('春','夏','秋','冬','unknow')

);



INSERT INTO test_enum

VALUES ('春'),('秋');

#忽略大小写的

INSERT INTO test_enum

VALUES ('UNKNOW');#添加成功



#可以使用索引进行枚举元素的调用

INSERT INTO test_enum

VALUES(1),('3');#字符型的数字也可



#没有限制非空的情况下可以添加null

INSERT INTO test_enum

VALUES (NULL);

SET类型（多选多）

可以选0个

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

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

选择多个成员，这一点与ENUM类型不同。

注意点：

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



SELECT * FROM test_set;