数据类型详解

写在前面

在了解了MySQL相关的基础信息之后，接下来开始学习MySQL中的数据类型。数据库列表由多列构成，每一个字段指定了不同的数据类型。指定字段的数据类型之后就决定了向字段插入的数据的内容。不同的数据类型决定了MySQL在存储它们的时候使用的方式，以及在使用它们的时候应当选择什么运算符号进行运算。

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型，本篇将简单介绍以下数据类型：
（1）、整数类型tinyint、smallint、mediumint、int、bigint；
（2）、浮点小数类型float和double，定点小数类型decimal。
（3）、日期/时间类型：包括year、time、date、datetime和timestamp。
（4）、字符串类型：包括char、varchar、binary、varbinary、blob、text、enum和set等。字符串又分为文本字符串和二进制字符串。

整数类型

数值型数据类型主要用于存储数字。MySQL提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，则所需要需要的存储空间也就越大。MySQL提供的整数类型有：tinyint、smallint、mediumint、int和bigint。整数类型的属性字段可以添加auto_increment自增约束条件：

从上表可以看出，不同类型整数存储所需的字节数是不同的，可以根据占用字节数来求出每一种数据类型的取值范围，如tinyint需要1个字节(8个bits)来存储，那么tinyint无符号取值范围为0 ~ 2⁸-1 (0255)，如果开发者插入负值则会报错，而有符号取值范围为-2⁷ ~ 2⁷-1 (-128127)，以上整数类型的有符号和无符号取值范围如下所示：
| 类型 | 字节数 | 无符号值范围 | 有符号值范围 |
| :————————-: | :—-: | :——————: | :——————————–: |
| tinyint[(n)] [unsigned] | 1 | [0,2⁸-1] | [-2⁷,2⁷-1] |
| smallint[(n)] [unsigned] | 2 | [0,2¹⁶-1] | [-2¹⁵,2¹⁵-1] |
| mediumint[(n)] [unsigned] | 3 | [0,2²⁴-1] | [-2²³,2²³-1] |
| int[(n)] [unsigned] | 4 | [0,2³²-1] | [-2³¹,2³¹-1] |
| bigint[(n)] [unsigned] | 8 | [0,2⁶⁴-1] | [-2⁶³,2⁶³-1] |

请注意上表中的[]包含的内容是可选的，默认是有符号类型，如果想使用无符号类型，则需要在类型后面添加unsigned参数。

有符号类型

例子1：演示有符号类型，依次执行如下语句：

mysql> create database envybook;
Query OK, 1 row affected (0.17 sec)

mysql> use envybook;
Database changed

mysql> create table demo1(
    -> a1 tinyint
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> insert into demo1 values(-pow(2,7)),(pow(2,7)-1);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from demo1;
+------+
| a1   |
+------+
| -128 |
|  127 |
+------+
2 rows in set (0.00 sec)

mysql> insert into demo1 values(pow(2,7));
ERROR 1264 (22003): Out of range value for column 'a1' at row 1

可以看到demo1表中a1字段是tinyint有符号类型，取值范围为[-2⁷,2⁷-1]，因此当开发者插入 2⁷ 数据时就报出无法插入的异常。

无符号类型

说完了有符号类型，接下来再来聊一聊无符号类型。

例子2：演示无符号类型，依次执行如下语句：

mysql> create table demo2(
    -> b1 tinyint unsigned
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> insert into demo2 values(-1);
ERROR 1264 (22003): Out of range value for column 'b1' at row 1

mysql> insert into demo2 values(pow(2,8)+1);
ERROR 1264 (22003): Out of range value for column 'b1' at row 1

mysql> insert into demo2 values(0),(pow(2,8));
ERROR 1264 (22003): Out of range value for column 'b1' at row 2

mysql> insert into demo2 values(0),(pow(2,8)-1);
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from demo2;
+------+
| b1   |
+------+
|    0 |
|  255 |
+------+
2 rows in set (0.00 sec)

可以看到demo2表中b1字段是tinyint无符号类型，取值范围为[0,2⁸-1]，因此当开发者插入 2⁸ 数据时就报出无法插入的异常。

类型(n)说明

在日常开发过程中，都会在定义整型的时候使用int(n)，需要注意一下两点：
（1）无论n等于多少，int永远只占用4个字节；n表示的是该数据类型指定的显示宽度，也就是能够显示的数值中数字的个数。

如声明一个int类型的字段year int(4);表示在year字段中的数据一般只显示4位数字的宽度。

（2）显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数。当数值的位数小于指定的宽度时，默认会由空格填充，如果开发者想用0填充，只需在定义字段类型的时候使用zerofill关键字；当数值的位数大于指定的宽度时，只要该值不超过给类型整数的取值范围，数值依然可以插入，且能显示出来。

例子3：加深对于类型(n)相关说明的理解和印象：

mysql> CREATE TABLE demo3(
    -> a1 int,
    -> a2 int(5),
    -> a3 int(5) unsigned,
    -> a4 int(5) zerofill,
    -> a5 int(5) unsigned zerofill,
    -> a6 int unsigned,
    -> a7 int zerofill,
    -> a8 int unsigned zerofill
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql> insert into demo3 values(2,2,2,2,2,2,2,2),(66,66,66,66,66,66,66,66),(88888,88888,88888,88888,88888,88888,88888,88888);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> show create table demo3;
| Table | Create Table                                                   
| demo3 | CREATE TABLE `demo3` (
  `a1` int(11) DEFAULT NULL,
  `a2` int(5) DEFAULT NULL,
  `a3` int(5) unsigned DEFAULT NULL,
  `a4` int(5) unsigned zerofill DEFAULT NULL,
  `a5` int(5) unsigned zerofill DEFAULT NULL,
  `a6` int(10) unsigned DEFAULT NULL,
  `a7` int(10) unsigned zerofill DEFAULT NULL,
  `a8` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以发现，使用show create table demo3;命令查看demo3表的创建语句时，发现它和我们原始的创建语句并不一致。尤其是字段a4，原始的a4字段使用的是无符号，但是我们使用了zerofill时，就自动将无符号提升为有符号，这一点需要引起注意。

可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。如tinyint有符号数和无符号数的取值范围分别为：[-127,127]和[0,255]，int默认的显示宽度为3。整数类型的默认显示宽度与其对应类型无符号数最大值十进制的长度相同。

例子4：，以tinyint为例，看看当插入数字1时，其前面是不是以两个0进行填充：

mysql> create table demo4(
    -> a tinyint zerofill
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> insert into demo4 values(1);
Query OK, 1 row affected (0.12 sec)

mysql> select * from demo4;
+------+
| a    |
+------+
|  001 |
+------+
1 row in set (0.00 sec)

从执行结果可以看出，前面的确是以两个0进行填充，这也从侧面说明了tinyint类型默认显示宽度为3。

但是显示宽度只用于显示，并不能限制取值范围和占用空间。如int(3)依旧会占用4个字节的存储空间，且允许的最大值也不是999，而是int整型所允许的最大值。

不同的整数类型有不同的取值范围，且需要不同的存储空间，因此应该根据实际需要来选择合适的类型，这样有利于提高查询效率和节省存储空间。

小数类型

小数类型包括浮点数和定点数。浮点数有两种：单精度浮点型(float)和双精度浮点型(double)；而定点类型只有一种decimal。

（1）小数类型都可以使用(M,N)来表示，其中M表示精度，表示总共的位数，N是标度，表示小数的位数。

类型名称	说明	存储需求
float	单精度浮点型	4个字节
double	双精度浮点型	8个字节
decimal(M,D) ,DEC	压缩的”严格”定点数	M+2个字节

（2）decimal类型不同于float和double，decimal实际上是以串存放的，decimal可能的最大取值范围与double一样，但是其有效的取值范围由M和D的值来决定的。如果改变M而固定D，则其取值范围将随M的变大而变大。decimal的存储空间并不是固定的，而是由其精度M来决定的，占用 M+2个字节。

（3）float和double在不指定精度时，默认会按照实际的精度（注意这个由计算机硬件和操作系统来决定），而decimal如果不指定精度，则默认为(10,0)。

例子5：加深对上述3点的印象：

mysql> create table demo5(
    -> a float(5,2),
    -> b double(5,2),
    -> c decimal(5,2)
    -> );
Query OK, 0 rows affected (0.41 sec)

mysql> insert into demo5 values(1,1,1),(2.1,2.1,2.1)
    -> ,(3.123,3.123,3.123),(4.125,4.125,4.125),
    -> (5.115,5.115,5.115),(6.126,6.126,6.126),
    -> (7.116,7.116,7.116),(8.1151,8.1151,8.1151),
    -> (9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501),
    -> (11.12501,11.12501,11.12501);
Query OK, 11 rows affected, 9 warnings (0.22 sec)
Records: 11  Duplicates: 0  Warnings: 9

mysql> select * from demo5;
+-------+-------+-------+
| a     | b     | c     |
+-------+-------+-------+
|  1.00 |  1.00 |  1.00 |
|  2.10 |  2.10 |  2.10 |
|  3.12 |  3.12 |  3.12 |
|  4.12 |  4.12 |  4.13 |
|  5.12 |  5.12 |  5.12 |
|  6.13 |  6.13 |  6.13 |
|  7.12 |  7.12 |  7.12 |
|  8.12 |  8.12 |  8.12 |
|  9.13 |  9.13 |  9.13 |
| 10.12 | 10.12 | 10.12 |
| 11.13 | 11.13 | 11.13 |
+-------+-------+-------+
11 rows in set (0.00 sec)

接下来需要对这个执行结果进行分析：
（a）查看c字段的输出信息，可以发现c是decimal类型，它采用的是四舍五入；
（b）查看a和b字段的输出，尤其注意4.125这个数据，可以发现a和b字段的值都是4.12，因为它采用的是四舍五入成双；

四舍五入成双，所谓的四舍五入成双，是指5以下舍弃，5以上进位，遇到数字5分情况处理。如果5后面还有不为0的任何数字，则直接进位；如果后面没有数字，则需要看5前面的数字，如果是奇数，则进位；如果是偶数，则将5舍弃掉。

（c）decimal插入的数据超过精度时，会触发警告。

例子6：通过定义decimal类型来插入一些超过精度的数据，来验证它的确会触发警告：

mysql> create table demo6(
    -> a float,
    -> b double,
    -> c decimal
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> insert into demo6 values(1,1,1),(1.234,1.234,1.4),(1.234,0.01,1.5);
Query OK, 3 rows affected, 2 warnings (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> select * from demo6;
+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
|     1 |     1 |    1 |
| 1.234 | 1.234 |    1 |
| 1.234 |  0.01 |    2 |
+-------+-------+------+
3 rows in set (0.02 sec)

从执行结果中可以看出，字段a和b的数据正确插入，字段c被截断了。

浮点数float和double如果没有指定精度和标度，则按照实际进行显示；decimal如果不写精度和标度，那么小数点后面的会进行四舍五入，且插入的时候会有警告。

（d）浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围，不过浮点数的确定是会引起精度问题。

例子7：浮点型和定点数求和运算，这里直接计算前面demo5和demo6数据表中a、b、c字段的和：

mysql> select sum(a),sum(b),sum(c) from demo5;
+--------+--------+--------+
| sum(a) | sum(b) | sum(c) |
+--------+--------+--------+
|  67.21 |  67.21 |  67.22 |
+--------+--------+--------+
1 row in set (0.08 sec)

mysql> select sum(a),sum(b),sum(c) from demo6;
+--------------------+--------------------+--------+
| sum(a)             | sum(b)             | sum(c) |
+--------------------+--------------------+--------+
| 3.4679999351501465 | 2.2439999999999998 |      4 |
+--------------------+--------------------+--------+
1 row in set (0.00 sec)

可以看到float和double字段存在精度丢失问题，而decimal定点类型则不会。

在MySQL，定点数以字符串形式存储，在货币，科学计数等高精度情况下，必须使用decimal类型，且不能对浮点数进行减法和比较运算，这个非常容易出错，需要特别注意。

日期和时间类型

MySQL中有多种表示日期的数据类型，主要有：year、time、date、datetime、timestamp等。year只显示年份信息，time只显示时分秒。每一种类型都有合法的取值范围，当指定了不合法的值时，系统默认将0值插入到数据库中。

year

year类型是一个单字节类型用于表示年，在存储时只需要1个字节，通常使用三种方式来表示年。
(a) 以4位字符串或者4位数字格式表示的year，取值范围为'1901'~'2155'。输入格式为’YYYY’或者YYYY。如开发者输入’2020’或者2020时，插入到数据库中的值均是2020。

(b) 以2位字符串格式表示的year，取值范围为'00'到'99'。请注意'00' ~ '69'和'70' ~ '99'的范围值分别被转换为2000 ~ 2069和1970 ~ 1999。其中'0'和'00'的作用相同。插入超过取值范围的值将被转换为2000。

(c) 以2位数字格式表示的year，取值范围为1~99。请注意1~69和70~99的范围值分别被转换为2001 ~ 2069和1970 ~ 1999。需要注意的是这里的0值将被转换为0000，而不是2000。

请注意(b)和(c)方法有些地方是不同的，当开发者想插入2000年时，如果使用(c)数字格式的0来表示year，那么实际上插入数据库的值为0000，而不是期望的2000。此时只有使用字符串格式的'0'或者'00'才可以被正确的解释为2000。非法的year值将被转换为0000。

例子8：创建数据表temp3，定义数据类型为year的字段a，然后向表中插入2020、’2020’和’2188’等值。

mysql>create table temp3(a year);

mysql>insert into temp3 values(2020),('2020');

mysql> insert into temp3 values('2188');
ERROR 1264 (22003): Out of range value for column 'a' at row 1

执行该语句后，MySQL给出了一条错误提示，可以使用show语句来查看错误信息：

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1264 | Out of range value for column 'a' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

可以看到，后来插入的值'2188'超过了year类型的取值范围，此时并不能正常的执行插入操作，因此temp3表中是不存在相应的数据，开发者可以查看temp3数据表进行确认：

mysql> select * from temp3;
+------+
| a    |
+------+
| 2020 |
| 2020 |
+------+
2 rows in set (0.00 sec)

也就说说插入数值类型的2020或者字符串类型的'2020'都是没问题的，但是当插入值'2188'时，由于超出了year类型的取值范围，因此无法进行正常的插入操作。

例子9：向数据表temp3中的字段a插入2位字符串表示的year值，分别为’0’、’00’、’88’和’11’等值。

mysql> delete from temp3;

mysql> insert into temp3 values('0'),('00'),('88'),('11');

mysql> select * from temp3;
+------+
| a    |
+------+
| 2000 |
| 2000 |
| 1988 |
| 2011 |
+------+
4 rows in set (0.00 sec)

从执行结果中可以看到字符串’0’和’00’作用是相同的，都表示2000年，而’88’则表示1988年，’11’表示2011年。

例子10：向数据表temp3中的字段a插入2位数字表示的year值，分别为0、00、88和11等值。

mysql> delete from temp3;

mysql> insert into temp3 values(0),(00),(88),(11);

mysql> select * from temp3;
+------+
| a    |
+------+
| 0000 |
| 0000 |
| 1988 |
| 2011 |
+------+
4 rows in set (0.00 sec)

从执行结果中可以看到数字0和00作用是相同的，都表示0000，而非2000，这一点需要注意。而88则表示1988年，11表示2011年，这些都和使用字符串表示的结果相同。

time

time类型一般用于只需要时间信息的值，在存储时需要3个字节，格式为HH:MM:SS。其中HH表示小时，MM表示分钟，SS表示秒。time类型的取值范围为-838:59:59 ~ 838:59:59，小时部分会如此大的原因是因为time类型不仅可以用于表示一天的时间(必须小于24小时)，还可以是某个事件过去的时间或者两个事件之间的时间间隔(可以大于24小时，或者为负值)。通常使用两种方式来表示time。

（a）第一种'D HH:MM:SS'格式的字符串，当然还可以使用下面这种“非严格”的写法：’HH:MM:SS’、’HH:MM’、’DHH:MM’、’D HH’或’SS’。这里的D表示日，可以取0 ~ 34之间的值。在插入数据库时，D被转换为小时进行保存，格式为’D*24 +HH’。
（b）第二种'HHMMSS'格式的、没有间隔符的字符串或者是HHMMSS格式的数值，注意这个没有格式的HHMMSS数值，必须是有意义的，也就是分钟和秒钟都必须在60以内，至于时针通常不会出错。举个例子来说，’101618’表示’10:16:18’，但是’108836’却是没意义的错误值，因为88大于60，此时将其插入数据库中只会变成00:00:00，这一点需要引起注意。

为time列分配简写值需要注意，如果简写值中不存在冒号，则MySQL假定最右边两位是秒，其余依次进位，同时MySQL解释time值为过去的时间，而不是当天的时间。

举个例子，开发者可能认为’1314’为13:14:00，但是实际上MySQL会将它们解释为00:13:14。又比如’15’和15则会被解释为00:00:15。但是如果开发者使用了冒号，那么MySQL肯定被看作当天的时间，也就是说’13:14’会被认为13:14:00，而不是之前的00:13:14。

例子11：创建数据表temp4，定义数据类型为time的字段t，并向表中插入’11:18:18’、’22:22’、’3 11:11’、’4 06’和’10’等值。

mysql> create table temp4(t time);

mysql> insert into temp4 values('11:18:18'),('22:22'),('3 11:11'),('4 06'),('10');`

mysql> select * from temp4;
+-----------+
| t         |
+-----------+
| 11:18:18  |
| 22:22:00  |
| 83:11:00  |
| 102:00:00 |
| 00:00:10  |
+-----------+
5 rows in set (0.00 sec)

可以看到’11:18:18’被转换为11:18:18；’22:22’被转换为 22:22:00 ；’3 11:11’被转换为83:11:00；’4 06’被转换为102:00:00，’10’被转换为00:00:10。

在使用’D HH’格式时，小时一定要使用双位数值，如果是小于10的小时数，应当在前面添加0。

例子12：向数据表temp4中插入’112017’、111028、’0’和108829等值。

mysql> delete from temp4;

mysql> insert into temp4 values('112017'),(111028),('0');

接着再执行下面的插入操作：

mysql> insert into temp4 values(108829);
ERROR 1292 (22007): Incorrect time value: '108829' for column 't' at row 1

执行该语句后，MySQL给出了一条错误提示，可以使用show语句来查看错误信息：

mysql>show warnings;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1292 | Incorrect time value: '108829' for column 't' at row 4 |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，插入的’108829’值超出了范围，因为108829中的分钟部分超出了60，因此根本插入不了数据。之后查询一下temp4表中的数据：

mysql> select * from temp4;
+----------+
| t        |
+----------+
| 11:20:17 |
| 11:10:28 |
| 00:00:00 |
+----------+
3 rows in set (0.00 sec)

可以看到’112017’被转换为11:20:17；111028被转换为11:10:28；’0’被转换为00:00:00；108829由于是错误的数据，因此无法被插入到数据表中。

开发者还可以使用系统日期函数current_time、now()等向time字段列中插入值。

例子13：向数据表temp4中插入系统的当前时间。

mysql> delete from temp4;

mysql> insert into temp4 values(current_time),(now());

mysql> select * from temp4;
+----------+
| t        |
+----------+
| 14:47:46 |
| 14:47:46 |
+----------+
2 rows in set (0.00 sec)

这样就将当前系统的时间插入到time类型的列中。

字符串类型

下图列举的是MySQL5.0中支持的字符串类型：

char和varchar类型

（1）char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。

（2）char(n)，若存入字符数小于n，则以空格补于其后，查询的时候再将空格删除，因此char类型存储的字符串末尾是不能有空格的，而varchar则保留尾部的空格。

（3）char(n)占用固定长度，举个例子，如char(5)，不管是存入几个字符，都将占用5个字节；varchar(n)则分情况：当n<=255时，则存入的实际字符数+1个字节；当n>255时，则存入的实际字符数+2个字节。也就是说当使用varchar(5)时，存入4个字符，将占用5个字节。

（4）char类型的字符串检索速度比varcahr类型的快很多。

varchar和text类型

（1）varchar可指定n，但是text类型不可指定。varchar(n)存储分情况：当n<=255时，则存入的实际字符数+1个字节；当n>255时，则存入的实际字符数+2个字节。text是实际字符数+2个字节。

（2）text类型不能有默认值。

（3）varchar可直接创建索引，text创建索引需要指定前多少个字符。

（4）varchar查询速度快于text，且在两者都创建索引的情况下，text的索引似乎不起作用。

二进制数据blob和text类型

（1）blob类型以二进制方式存储数据，不区分英文大小写；而text类型以文本方式存储数据，区分英文大小写。
（2）blob存储的数据只能整体读出。一般用于对图片进行存储，在Java中对应于byte[]数组。
（3）text类型可以指定存储的字符集；而blob类型不用指定字符集。

enum枚举类型

枚举类型，它的值范围需要在创建表时，通过枚举方式显式指定，对于1-255个成员的枚举需要1个字节存储；对于255-65535个成员，需要2个字节来存储。最多允许有65535个成员。ENUM类型忽略大小写，当插入值不在枚举的范围内时，不会报错，会将第一个值插入。

set类型

（1）set也是一个字符串对象，里面可以包含64个成员，根据成员数量的不同，集合占用存储也不同。其中1-8个成员，占1个字节；9-16个成员，占2个字节；17-24个成员，占3个字节；25-32个成员，占4个字节；33-64个成员，占8个字节。

（2）set类型可以一次插入多个成员，enum类型一次只能插入一个成员。

（3）set类型允许从集合中选择一至多个元素进行组合，因此对于输入的值只要是允许范围内的，都可以正确插入到set类型中；而对于重复的成员，则只取一次。

Java数据类型和MySql数据类型对应表

下表展示了Java数据类型和MySql数据类型之间的对应关系，在实际工作中可按照表中所列的对应关系，合理选择对应的数据类型：

数据类型选择

前面学习了这么多数据类型，那么在实际工作中应当如何选择使用呢？这里笔者结合实际经验和网上的说法，给出一些建议：
（1）尽量选择较小的类型。一般建议选择可以正确存储数据的最小数据类型，数据类型越小，占用磁盘、CPU和内存越小。
（2）尽量避免NULL值。除非是特殊情况，正常都尽量不使用NULL值，存在NULL值的列会使索引、值比较较为复杂。
（3）浮点型建议统一使用decimal。
（4）记录时间选择使用int或者bigint类型，将时间转换为时间戳格式，如秒、毫秒等进行存储，便于查询的时候走索引。