MySQL中数据类型详细——整型、浮点型、定点数类型、位类型、日期与时间类型

最新推荐文章于 2025-04-21 20:35:29 发布

小珍不烦

最新推荐文章于 2025-04-21 20:35:29 发布

阅读量1.3k

点赞数 14

文章标签： mysql 数据库分类

本文链接：https://blog.youkuaiyun.com/2301_81368482/article/details/146094552

版权

一、数据类型

整数类型：tinyint、smallint、 mediumint、int(或integer)、bigint
浮点类型：float,double
定点数类型：decimal
位类型：bit
日期时间类型：year、date、time，datetime、timestamp
文本字符串类型：char、varchar、tinytext、text、mediumtext、longtext
枚举类型：enum
集合类型：set
二进制字符串类型：binary、varbinary、tinyblob、mediumblob、blob、longblob
json： json对象、json数组

*常见数据类型的属性：
null：数据列可包含NULL值
not null ：数据列不允许包含NULL值
defult ：默认值
primary key ：主键
auto_increment ：自动递增，适用于整数类型
unsigned ：无符号
character set name ：指定一个字符集

二、整数类型

整数类型一共有 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

int(M) 【zerofill】

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

如果设置了显示宽度，而插入的数据宽度超过显示宽度限制时，不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从MySQL8.0.17开始，整数数据类型不推荐使用显示宽度属性。

int UNSIGNED

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

int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。

 create table emp02
    -> (f1 int unsigned);
Query OK, 0 rows affected (0.03 sec)

mysql> desc emp02;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | int unsigned | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

ZEROFILL: 0填充，如果某字段是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性，如果指定了M和MZEROFILL，且添加的数据不够M位时，用0在左边填充。如果超过M位，只要不超过数据存储范围仍然可以添加成功，且按照原始数据位数显示出来

三、浮点数类型

MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、
FLOAT 表示单精度浮点数；
DOUBLE 表示双精度浮点数；

FLOAT 和 DOUBLE 这两种数据类型的区别：
在MySQL中单精度值使用4个字节，双精度值使用8个字节。
FLOAT占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。

FLOAT(M,D)或DOUBLE(M,D)：(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。
例如，定义为FLOAT(5,2)的一个字段则可以显示-999.99~999.99。如果超过这个范围会报错。

FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。
说明：浮点类型，也可以加UNSIGNED，但是不会改变数据范围，例如：FLOAT(3,2)
UNSIGNED仍然只能表示0-9.99的范围。

如果设置了精度(M,D)，这里MySQL的处理方案如下：
如果添加时，整数部分超出了范围，MySQL就会报错，不允许存这样的值
如果添加时，小数点部分若超出范围，就分以下情况：
若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。
若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。如FLOAT(5,2)列内插入999.995和-999.995都会报错。

mysql> desc emp02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| f1    | float       | YES  |     | NULL    |       |
| f2    | float(5,2)  | YES  |     | NULL    |       |
| f3    | double(5,2) | YES  |     | NULL    |       |
| f4    | double      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into emp02
    -> values(123.12,123.12,123.12,123.12);
Query OK, 1 row affected (0.02 sec)

mysql> insert into emp02
    -> (f1,f4)
    -> values(999.999,999.999);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp02
    -> (f2,f3)
    -> values(999.999,999.999);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1

mysql> insert into emp02
    -> (f1,f2)
    -> values(1234.12,1234.12);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1

mysql> insert into emp02
    -> (f3,f4)
    -> values(1234.12,1234.12);
ERROR 1264 (22003): Out of range value for column 'f3' at row 1

浮点数类型有个缺陷，就是计算不精准。

如果我们设计一个表，有num这个字段，类型为float，添加的值分别为0.47,0.44,0.19，对该字段进行求和，
我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询结果并不等于1.1

mysql> desc emp04;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| num   | float | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into emp04
    -> values(0.47),(0.44),(0.19);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from emp04;
+------+
| num  |
+------+
| 0.47 |
| 0.44 |
| 0.19 |
+------+
3 rows in set (0.00 sec)

mysql> select sum(num),sum(num)=1.1 from emp04;
+--------------------+--------------+
| sum(num)           | sum(num)=1.1 |
+--------------------+--------------+
| 1.0999999940395355 |            0 |
+--------------------+--------------+
1 row in set (0.00 sec)

查询结果是 1.0999999940395355！=1.1

在编程中，如果用到浮点数，要特别注意误差问题，**因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。**同时，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。

四、定点数类型

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

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，0<=M<=65，0<=D<=30，D<M。
例如，定义DECIMAL（5,2）的类型，表示该字段的取值范围是-999.99~999.99。

DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节
定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。
当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

mysql> create table emp05
    -> (num decimal(5,2));
Query OK, 0 rows affected (0.03 sec)

mysql> desc emp05;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into emp05
    -> values(0.47),(0.44),(0.19);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select sum(num),sum(num)=1.1 from emp05;
+----------+--------------+
| sum(num) | sum(num)=1.1 |
+----------+--------------+
|     1.10 |            1 |
+----------+--------------+
1 row in set (0.00 sec)

我们设计了一个表，有num这个字段，类型为decimal，添加的值分别为0.47,0.44,0.19，对该字段进行求和，
查询结果是：0.47 + 0.44 + 0.19 = 1.1，该定点数类型decimal解决了浮点数类型计算不精确的问题。

五、位类型

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

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

注意：在向BIT类型的字段中添加数据时，输入的内容是当作十进制数的，要确保该十进制数转换成的二进制数的位数在限定范围内。
使用SELECT命令查询位字段时，默认显示该的是十六进制数，可以用BIN()或HEX()函数进行读取。若需要显示十进制数，则可以在字段后面加0；

 desc emp06;
+--------+--------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| num_01 | bit(1) | YES  |     | NULL    |       |
| num_02 | bit(3) | YES  |     | NULL    |       |
+--------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into emp06
    -> values(1,3),(0,7);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp06;
+----------------+----------------+
| num_01         | num_02         |
+----------------+----------------+
| 0x01           | 0x03           |
| 0x00           | 0x07           |
+----------------+----------------+
2 rows in set (0.00 sec)

mysql> select bin(num_01),bin(num_02) from emp06;
+-------------+-------------+
| bin(num_01) | bin(num_02) |
+-------------+-------------+
| 1           | 11          |
| 0           | 111         |
+-------------+-------------+
2 rows in set (0.00 sec)

mysql> select hex(num_01),hex(num_02) from emp06;
+-------------+-------------+
| hex(num_01) | hex(num_02) |
+-------------+-------------+
| 1           | 3           |
| 0           | 7           |
+-------------+-------------+
2 rows in set (0.01 sec)

mysql> select num_01+0,num_02+0 from emp06;
+----------+----------+
| num_01+0 | num_02+0 |
+----------+----------+
|        1 |        3 |
|        0 |        7 |
+----------+----------+
2 rows in set (0.00 sec)

六、日期与时间类型

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。

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

可以看到，不同数据类型表示的时间内容不同、取值范围不同，而且占用的字节数也不一样，你要根据实际需要灵活选取。

时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 ：原因是 MySQL 设计的 TIME类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

（一）、 YEAR类型

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

在MySQL中，向YEAR类型的字段添加数据时，有几种添加方式：
以4位字符串或数字格式进行添加，其格式为YYYY，最小值为1901，最大值为2155。
以2位字符串或数字格式进行添加，最小值为00，最大值为99。

当取值为01到69时，默认表示2001到2069；
当取值为70到99时，默认表示1970到1999；
当取值整数的0或00添加的话，那么是0000年；
当取值是字符串的’0’添加的话，是2000年。

mysql> desc emp07;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| year_info | year | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into emp07
    -> values("2025"),(2025),("25"),("80"),(0),(00),('0');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from emp07;
+-----------+
| year_info |
+-----------+
|      2025 |
|      2025 |
|      2025 |
|      1980 |
|      0000 |
|      0000 |
|      2000 |
+-----------+
7 rows in set (0.00 sec)

（二）、DATE类型

DATE类型表示日期，没有时间部分，显示时的格式为YYYY-MM-DD，其中，YYYY表示年份，MM表示月份，DD表示日期。

在向DATE类型的字段添加数据时，需要满足一定的格式条件。
以字符串类型的YYYY-MM-DD格式添加，其最小取值为1000-01-01，最大取值为9999-12-03,
以字符串或者数值型YYYYMMDD格式添加时，其最小取值为10000101，最大取值为99991203,
以字符型YY-MM-DD格式以及字符型或数值型YYMMDD格式添加，此格式中，年份为两位数值：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
使用CURRENT_DATE()或者NOW()函数，会添加当前系统的日期。

mysql> desc emp08;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| data_info | date | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into emp08
    -> values('2025-03-07'),('20250307'),(20250307),('25-03-07'),('250307'),(250307),(current_date()),(now());curr
Query OK, 8 rows affected, 1 warning (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 1

mysql> select * from emp08;
+------------+
| data_info  |
+------------+
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
| 2025-03-07 |
+------------+
9 rows in set (0.00 sec)

（三）、TIME类型

TIME类型用来表示时间，不包含日期部分。在MySQL中，需要3个字节的存储空间来存储TIME类型的数据，通常会以“HH:MM:SS”格式来显示TIME类型的数据，其中，HH表示小时，MM表示分钟，SS表示秒。

在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。
（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:20表示12:20:00，而不是00:12:10。
（2）可以使用不带有冒号的字符串或者数字，格式为’HHMMSS’或者HHMMSS。如果添加的是一个不合法的字符串或者数字，比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
（3）使用CURRENT_TIME()或者NOW()，会插入当前系统的时间。

mysql> insert into emp09
    -> values('2 17:10:12'),('17:10:12'),('2 17:10'),('17:10'),('2 17'),('12'),('171012'),(171012),(1012),(current_time()),(now());
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from emp09;
+-----------+
| time_info |
+-----------+
| 65:10:12  |
| 17:10:12  |
| 65:10:00  |
| 17:10:00  |
| 65:00:00  |
| 00:00:12  |
| 17:10:12  |
| 17:10:12  |
| 00:10:12  |
| 17:06:21  |
| 17:06:21  |
+-----------+
11 rows in set (0.00 sec)

（四）、 DATETIME类型

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在显示格式上为DATE类型和TIME类型的组合，表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。

在向DATETIME类型的字段插入数据时，需要满足一定的格式条件。以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格式。以YY-MM-DD HH:MM:SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
使用函数CURRENT_TIMESTAMP()和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

mysql> insert into emp10
    -> values('2025-03-07 17:14:12'),('20250307171412'),(20250307171412),('250307171412'),(250307171412),(current_timestamp),(now());
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from emp10;
+---------------------+
| datetime_info       |
+---------------------+
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:16:39 |
| 2025-03-07 17:16:39 |
+---------------------+
7 rows in set (0.00 sec)

（五）、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类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。

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

向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。

mysql> desc emp11;
+----------------+-----------+------+-----+---------+-------+
| Field          | Type      | Null | Key | Default | Extra |
+----------------+-----------+------+-----+---------+-------+
| timestamp_info | timestamp | YES  |     | NULL    |       |
+----------------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into emp11
    -> values('2025@03@07@17@14@12'),('20250307171412'),(20250307171412),('250307171412'),(250307171412),(current_timestamp),(now());
Query OK, 7 rows affected, 1 warning (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 1

mysql> select emp11;
ERROR 1054 (42S22): Unknown column 'emp11' in 'field list'
mysql> select * from emp11;
+---------------------+
| timestamp_info      |
+---------------------+
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:14:12 |
| 2025-03-07 17:24:04 |
| 2025-03-07 17:24:04 |
+---------------------+
7 rows in set (0.00 sec)

TIMESTAMP和DATETIME的区别：

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

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

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

TIMESTAMP和时区有关。TIMESTAMP会因为时区的不同而显示不同的结果。

mysql> desc emp12;
+----------------+-----------+------+-----+---------+-------+
| Field          | Type      | Null | Key | Default | Extra |
+----------------+-----------+------+-----+---------+-------+
| datetime_info  | datetime  | YES  |     | NULL    |       |
| timestamp_info | timestamp | YES  |     | NULL    |       |
+----------------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into emp12
    -> values(now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp12;
+---------------------+---------------------+
| datetime_info       | timestamp_info      |
+---------------------+---------------------+
| 2025-03-07 17:28:34 | 2025-03-07 17:28:34 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp12;
+---------------------+---------------------+
| datetime_info       | timestamp_info      |
+---------------------+---------------------+
| 2025-03-07 17:28:34 | 2025-03-07 18:28:34 |
+---------------------+---------------------+
1 row in set (0.00 sec)