第
5 
章数据类型与表的约束

学习目标: 

.掌握MySQL中的各种数据类型; 
.掌握MySQL中表的各种约束。
在数据库中,数据表用来组织和保存数据,它由表结构和表中数据构成。
在设计表结构时,经常需要根据实际需求,选择合适的数据类型和约束。本
章介绍数据类型和表中的各种约束。

5.数据类型
1 

使用MySQL数据库存储数据时,不同的数据类型决定了MySQL存储
数据方式的不同。MySQL数据库提供了多种数据类型,本节对这些数据类
型进行讲解。

5.1.1 
数字类型
在数据表中,经常需要存储一些数字,如学生的年龄、商品的价格等,这
需要数字类型进行存储。数字类型包括整数类型、浮点数类型、定点数类型、
bit(位)类型等。

1.整数类型
MySQL提供的整数类型包括tinyint、smalint、mediumint、int和
bigint。整数类型的属性字段可以添加auto_increment自增约束条件。不同
整数类型所对应的字节大小和取值范围不同,如表5-1所示。

表5-
1 
整数类型

g

数据类型字节数无符号取值范围有符号取值范围
tinyint 1 0~281 -27~271 
smalint 2 0~2161 -215~2151 
mediumint 3 0~2241 -223~2231 
int 4 0~2321 -231~2311 
biint 8 0~2641 -263~2631 


68 　MySQL 8.0 数据库原理与应用 
从表5-1中可以看出,不同整数类型所占用的字节数和取值范围都是不同的。其中,占
用字节数最小的是tinyint,占用字节数最大的是bigint。不同整数类型的取值范围可以根
据字节数计算出来,如tinyint类型的整数占用1字节,1字节是8位,那么tinyint类型无符
号数的最大值就是28-1(即255),有符号数的最大值就27-1(即127)。同理,可以算出其
他不同整数类型的取值范围。
需要注意的是,若使用无符号数据类型,需要在数据类型右边加上unsigned关键字来
修饰,如intunsigned表示无符号int类型。
下面通过案例来演示整数类型的使用。
【例5-1】 创建名称为int_test的数据表,字段类型用int和intunsigned来定义。具
体SQL语句与执行结果如下。 
mysql> create table int_test( 
-> i1 int, 
-> i2 int unsigned 
-> ); 
Query OK, 0 rows affected (0.63 sec) 
在数据表int_test中,i1是有符号类型,i2是无符号类型。
【例5-2】 插入整型数据。当数据在合法范围内,可以插入,反之提示错误信息。具体
SQL语句与执行结果如下。 
mysql> insert into int_test values(500,500); 
Query OK, 1 row affected (0.20 sec) 
mysql> insert into int_test values(-500,-500); 
ERROR 1264 (22003): Out of range value for column 'i2' at row 1 
从以上执行结果可以看出,-500超出了无符号int类型i2的取值范围,插入失败, 
MySQL显示了错误信息。
2.浮点数类型
在MySQL中,存储的小数都是使用浮点数或定点数来表示的。浮点数的类型有两种, 
分别是单精度浮点数(float)和双精度浮点数(double),对应的字节大小及其取值范围如
表5-2所示。
表5-2 浮点数类型
数据类型字 节 数负数取值范围非负数取值范围
float 4 -3.402823466E+38~ 
-1.175494351E-38 
0和1.175494351E-38~ 
3.402823466E+38 
double 8 -1.7976931348623157E+308~ 
-2.2250738585072014E-308 
0和2.2250738585072014E-308~ 
1.7976931348623157E+308 
表5-2中列举的取值范围是理论上的极限值,但根据不同的硬件或操作系统,实际范围
可能会小。另外,当浮点数类型使用unsigned修饰为无符号时,取值范围将不包含负数。

第5 章　数据类型与表的约束　 69 
浮点数类型虽然取值范围很大,但是精度并不高。float的精度大约是6位,double的
精度大约是15位。如果超出精度,可能会导致给定的数值与实际保存的数值不一致,发生
精度损失。
为了更好地理解,下面通过案例演示浮点数类型的使用。
【例5-3】 创建名称为float_test的数据表,具体SQL语句与执行结果如下。 
mysql> create table float_test( 
-> f1 float, 
-> f2 float); 
Query OK, 0 rows affected (0.27 sec) 
数据表创建成功。
【例5-4】 插入float类型的数据,具体SQL语句与执行结果如下。 
mysql> insert into float_test values(1234567,1.234567); 
Query OK, 1 row affected (0.04 sec) 
数据插入成功后,可以使用select语句查询表中的数据,执行结果如下。 
mysql> select * from float_test; 
+---------+---------+ 
| f1 | f2 | 
+---------+---------+ 
| 1234570 | 1.23457 | 
+---------+---------+ 
1 row in set (0.00 sec) 
从以上结果中可以看出,当一个数字的整数部分和小数部分加起来达到7位时,第7位
就会被四舍五入。
3.定点数类型
定点数类型(decimal)通过decimal(M,D)设置位数和精度,其中M 表示数值总位数
(不包括“.”和“-”),最大值为65,默认值为10;D表示小数点后的位数,最大值为30,默认
值为0。例如,decimal(6,3)表示的取值范围是-999.999~999.999。系统会自动根据存储
的数据来分配存储空间。如果不允许保存负数,可以通过unsigned修饰。
为了更好地理解,下面通过案例演示定点数类型的使用。
【例5-5】 创建名称为decimal_test的数据表,具体SQL语句与执行结果如下。 
mysql> create table decimal_test( 
-> d1 decimal(5,2), 
-> d2 decimal(5,2)); 
Query OK, 0 rows affected (0.60 sec) 
数据表创建成功。

70 　MySQL 8.0 数据库原理与应用 
【例5-6】 插入一条数据(123.124,123.125),具体SQL语句与执行结果如下。 
mysql> insert into decimal_test values(123.124,123.125); 
Query OK, 1 row affected, 2 warnings (0.05 sec) 
数据插入成功后,出现了两条警告信息,查看警告信息,结果如下。 
mysql> show warnings; 
+-------+------+-----------------------------------------+ 
| Level | Code | Message | 
+-------+------+-----------------------------------------+ 
| Note | 1265 | Data truncated for column 'd1' at row 1 | 
| Note | 1265 | Data truncated for column 'd2' at row 1 | 
+-------+------+-----------------------------------------+ 
2 rows in set (0.04 sec) 
警告信息显示出现了数据截断,可以使用select语句查询表中数据,执行结果如下。 
mysql> select * from decimal_test; 
+--------+--------+ 
| d1 | d2 | 
+--------+--------+ 
| 123.12 | 123.13 | 
+--------+--------+ 
1 row in set (0.00 sec) 
从以上执行结果可以看出,小数部分被四舍五入。
【例5-7】 插入一条数据(999.99,999.999),具体SQL语句与执行结果如下。 
mysql> insert into decimal_test values(999.99,999.999); 
ERROR 1264 (22003): out of range value for column 'd2' at row 1 
从以上执行结果可以看到,数据插入失败。
从例5-6和例5-7可以看出,若小数部分超出范围,会进行四舍五入,并出现数据截断
(datatruncated)警告;若整数部分超出范围,数据会插入失败,提示超出取值范围(outof 
rangevalue)错误。
注意:浮点数类型也可以设置位数和精度,但仍有可能损失精度,在实际使用过程中应
避免使用浮点数类型,以免出现不能人为控制的问题。因此,对于小数类型的数据,建议使
用定点数类型。
4.bit(位)类型
bit(位)类型用于存储二进制数据,语法为bit(M),M 表示位数,范围为1~64。
为了更好地理解,下面通过案例演示bit类型的使用。
【例5-8】 以保存字符A 为例,A 的ASCII码对应十进制65,对应二进制1000001,总
共有7位,因此需要bit(7)保存。具体SQL语句与执行结果如下。

第5 章　数据类型与表的约束　 71 
mysql> select ASCII('A'); 
+------------+ 
| ASCII('A') | 
+------------+ 
| 65 | 
+------------+ 
1 row in set (0.05 sec) 
获取字符A 的ASCII码,结果是65。 
mysql> select bin(65),length(bin(65)); 
+---------+-----------------+ 
| bin(65) | length(bin(65)) | 
+---------+-----------------+ 
| 1000001 | 7 | 
+---------+-----------------+ 
1 row in set (0.10 sec) 
获取字符A 的二进制数,并计算长度,结果为1000001和7。 
mysql> create table bit_test(b bit(7)); 
Query OK, 0 rows affected (0.25 sec) 
数据表创建成功。 
mysql> insert into bit_test values(65); 
Query OK, 1 row affected (0.59 sec) 
插入数据成功。 
mysql> select bin(b) from bit_test; 
+---------+ 
| bin(b) | 
+---------+ 
| 1000001 | 
+---------+ 
1 row in set (0.00 sec) 
查询数据并转换为二进制数显示,结果为1000001。
从以上执行结果可以看出,利用MySQL中的ASCII()、bin()、length()函数可以方便
地查询ASCII码、二进制值和数字长度。bit类型字段在数字插入时转换为二进制保存,但
在利用select查询时,可以转换为对应的字符显示。
5.1.2 时间和日期类型
在处理日期和时间类型的值时,MySQL有不同的数据类型供用户选择。它们可以被

72 　MySQL 8.0 数据库原理与应用
分为简单的日期和时间类型、混合的日期和时间类型。根据要求的精度,子类型在每个分类
型中都可以使用,并且MySQL带有内置功能,可以将多样化的输入格式变为一个标准格
式。日期和时间类型同样有对应的字节数和取值范围,如表5-3所示。
表5-3 日期和时间类型
数据类型字节数取值范围日期格式零 值
year 1 1901~2155 YYYY 0000 
date 4 1000-01-01~9999-12-31 YYYY-MM-DD 0000-00-00 
time 3 -838:59:59~838:59:59 HH:MM:SS 00:00:00 
datetime 8 1000-01-0100:00:00~ 
9999-12-3123:59:59 
YYYY-MM-DD 
HH:MM:SS 
0000-00-00 
00:00:00 
timestamp 4 1970-01-0100:00:01~ 
2038-01-1903:14:07 
YYYY-MM-DD 
HH:MM:SS 
0000-00-00 
00:00:00 
在表5-3中,日期格式YYYY表示年,MM 表示月,DD表示日。每种日期和时间类型
的取值范围都是不同的。需要注意的是,如果插入的数值不合法,系统会自动将对应的零值
插入数据表中。
下面详细讲解日期和时间类型。
1.year类型
year类型用于年份。
【例5-9】 创建名称为year_test的数据表,并插入数据。具体SQL语句与执行结果如下。 
mysql> create table year_test(y year); 
Query OK, 0 rows affected (0.17 sec) 
mysql> insert into year_test values(2022); 
Query OK, 1 row affected (0.14 sec) 
在MySQL中,可以使用以下3种格式指定year类型的值。
(1)使用4位字符串或数字表示,为'1901'~'2155'或1901~2155。例如输入'2022'或
2022,插入数据库中的值均为2022。
(2)使用两位字符串表示,为0' 0'~9' 9',其中,0' 0'~6' 9'的值会被转换为2000~2069的
year值,7' 0'~9' 9'的值会被转换为1970~1999的year值。例如输入2' 2',插入数据表中的值
为2022。
(3)使用两位数字表示,为1~99,其中,1~69的值会被转换为2001~2069的year值, 
70~99的值会被转换为1970~1999的year值。例如输入22,插入数据表中的值为2022。
需要注意的是,当使用year类型时,一定要区分0' '和0。因为字符串格式0' '表示的year 
值是2000,而数字格式的0表示的year值是0000。
2.date类型
date类型用于表示日期值,不包含时间部分。
【例5-10】 创建名称为date_test的数据表,并插入数据。具体SQL语句与执行结果
如下。

第5 章　数据类型与表的约束　 73 
mysql> create table date_test(d date); 
Query OK, 0 rows affected (0.19 sec) 
mysql> insert into date_test values('2022-04-03'); 
Query OK, 1 row affected (0.15 sec) 
在MySQL 中,可以使用以下4种格式指定date类型的值。
(1)以'YYYY-MM-DD'或者'YYYYMMDD'字符串格式表示。例如,输入2' 022-04-03'或
2' 0220403',插入数据库中的日期都为2022-04-03。
(2)以'YY-MM-DD'或者'YYMMDD'字符串格式表示。YY 表示的是年,为'00'~ '99', 
其中,0' 0'~6' 9'的值会被转换为2000~2069的year值,7' 0'~9' 9'的值会被转换为1970~ 
1999的year值。例如输入2' 2-04-03'或2' 20403',插入数据库中的日期都为2022-04-03。
(3)以YY-MM-DD或者YYMMDD数字格式表示。例如输入22-04-03或220403,插
入数据库中的日期都为2022-04-03。
(4)使用current_date输入当前系统日期。
3.time类型
time类型用于表示时间值,它的显示形式一般为HH:MM:SS,其中HH 表示小时, 
MM 表示分,SS表示秒。在MySQL中,可以使用以下3种格式指定time类型的值。
(1)以'HHMMSS'字符串或者HHMMSS数字格式表示。例如输入1' 21212'或121212, 
插入数据库中的时间为12:12:12。
(2)以'D HH:MM:SS'字符串格式表示。其中,D表示日,可以取0~34的值,插入数
据时,小时的值等于(D×24+HH)。例如,输入'211:30:50',插入数据库中的时间为59: 
30:50;输入1' 1:30:50',插入数据库中的时间为1' 1:30:50';输入3' 022:59:59',插入数据库
中的时间为742:59:59。
(3)使用current_time输入当前系统时间。
4.datetime类型
datetime类型用于表示日期和时间,它的显示形式为'YYYY-MM-DD HH:MM:SS', 
其中YYYY表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分,SS表示秒。在
MySQL中可以使用以下4种格式指定datetime类型的值。
(1)以'YYYY-MM-DD HH:MM:SS'或者'YYYYMMDDHHMMSS'字符串格式表示
的日期和时间,取值范围为1' 000-01-0100:00:00'~9' 999-12-3123:59:59'。例如,输入2' 014- 
01-2209:01:23'或'20140122090123',插入数据库中的datetime值都为2014-01-2209: 
01:23。
(2)以'YY-MM-DD HH:MM:SS'或者'YYMMDDHHMMSS'字符串格式表示的日期
和时间,其中YY表示年,取值范围为0' 0'~ 9' 9',与date类型中的YY相同,0' 0'~6' 9'的值会
被转换为2000~2069的year值,7' 0'~9' 9'的值会被转换为1970~1999的year值。
(3)以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和
时间。例如,插入20140122090123或者140122090123,插入数据库中的datetime值都为
2014-01-2209:01:23。
(4)使用now()来输入当前系统的日期和时间。

74 　MySQL 8.0 数据库原理与应用
5.timestamp类型
timestamp(时间戳)类型用于表示日期和时间,它的显示形式与datetime相同,但取值
范围比datetime小,下面介绍几种timestamp类型与datetime类型不同的形式,具体如下。
(1)使用current_timestamp来输入系统当前日期和时间。
(2)无任何输入,或输入null时,实际保存的是系统当前日期和时间。
注意:在MySQL 中,timestamp字段默认情况下会自动设置notnulldefaultcurrent_ 
timestamponupdatecurrenttimestamp属性,具体解释如下。
(1)notnull表示非空约束,该字段将不允许保存null值。
(2)default表示默认约束,当字段无任何输入时,自动设置某个值作为默认值。此处设
为current_timestamp表示使用系统当前日期和时间作为默认值。
(3)onupdate用于当一条记录中的其他字段被update语句修改时,自动更改该字段为
某个值。此处设为current_timestamp表示每次修改时保存修改时的系统日期和时间。
若为timestamp字段手动设置default属性时,该字段将不会自动设置onupdate属性。
5.1.3 字符串类型
字符串类型用来存储字符串数据,除了可以存储字符串数据之外,还可以存储其他数
据,如图片和声音的二进制数据。MySQL支持两类字符型数据:文本字符串和二进制字符
串。文本字符串类型包括char、varchar、text、enum 和set,二进制字符串类型包括binary、
varbinary和blob。
1.char和varchar类型
char和varchar类型的语法格式如下。 
char(M) 
或 
varchar(M) 
char(M)为固定长度字符串,在定义时指定字符串列长。不足指定长度时,在右侧填充
空格,以达到指定的长度。M 表示列长度,M 的范围是0~255个字符。例如char(4)定义
了一个固定长度的字符串列,其包含的字符个数最大是4。当检索到char值时,尾部的空格
将被删除。
varchar(M)是长度可变的字符串,M 表示最大列长度。M 的范围是0~65535。
varchar的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字
符串的实际长度加1。例如,varchar(50)定义了一个最大长度为50的字符串,如果插入的
字符串只有10个字符,则实际存储的字符串为10个字符和一个字符串结束字符。varchar 
在保存和检索值时尾部的空格仍保留。
为了对比char和varchar之间的区别,下面以char(4)和varchar(4)为例进行说明,如
表5-4所示。

第5 章　数据类型与表的约束　 75 
表5-4 char(4)和varchar(4)的对比
插 入 值char(4)存储需求varchar(4)存储需求
' ' 4字节1字节
a' b' 4字节3字节
a' bc' 4字节4字节
a' bcd' 4字节5字节 
从对比结果可以看出,char(4)定义了固定长度为4的列,不管存储的数据长度为多少, 
所占用的空间均为4字节;varchar(4)定义的列所占的字节数为实际长度加1。
2.text类型
text类型保存大文本数据,如文章内容、评论等。当保存或查询text列的值时,不删除
尾部空格。text类型分为4种,不同的text类型的存储范围和数据长度不同,如表5-5 
所示。
表5-5 text类型
数据类型存储范围数据类型存储范围
tinytext 0~28-1字节mediumtext 0~224-1字节
text 0~216-1字节longtext 0~232-1字节 
text类型所保存的最大字符数量取决于字符串实际占用的字节数。
注意:在使用“=”等运算符对char、varchar、text进行比较时,字符串末尾的空格会被
忽略。例如,使用where查询a' '字符串,查询结果中可能包含a后面有空格的情况,反之,如
查询条件字符串末尾有空格,如a' ',空格也会被忽略。
由于数据库对大小写不敏感,因此,char、varchar、text、enum、set类型都不区分大小
写。例如,使用where查询a' '字符串,则'A'和a' '都会被查询出来。而binary、varbinary、blob 
类型区分大小写,这是因为它们使用二进制方式保存数据。
3.enum 类型
enum 类型又称为枚举类型,定义enum 类型的语法格式如下。 
enum('值1','值2','值3',…, '值n') 
在上述格式中,('值1','值2','值3',…,'值n')称为枚举列表,enum 类型的数据只能从枚
举列表中获取,并且只能取一个。
【例5-11】 创建名称为enum_test的数据表,并插入数据。具体SQL语句与执行结果
如下。 
mysql> create table enum_test(sex enum('male','female')); 
Query OK, 0 rows affected (0.36 sec) 
数据表创建成功,插入两条数据并查看。

76 　MySQL 8.0 数据库原理与应用 
mysql> insert into enum_test values('male'),('female'); 
Query OK, 2 rows affected (0.10 sec) 
Records: 2 Duplicates: 0 Warnings: 0 
mysql> select * from enum_test; 
+--------+ 
| sex | 
+--------+ 
| male | 
| female | 
+--------+ 
2 rows in set (0.04 sec) 
插入枚举列表中没有的数据,从执行结果中可以看出,插入失败。 
mysql> insert into enum_test values('ma'); 
ERROR 1265 (01000): Data truncated for column 'sex' at row 1 
enum 值在内部用整数表示,并且每个枚举值均有一个索引值:列表值所允许的成员值
从1开始编号,MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。
4.set类型
set类型用于保存字符串对象,其定义格式与enum 类型相似,语法格式如下。 
set('值1','值2','值3',…, '值n'); 
set类型的列表中最多可以有64个值,且列表中的每个值都有一个顺序编号,为了节省
空间,实际保存在记录中的也是顺序编号,但在select、insert等语句进行操作时,仍然要使
用列表中的值。
set类型与enum 类型的区别在于,可以从列表中选择一个或多个值来保存,多个值之
间用逗号分隔。
【例5-12】 创建名称为set_test的数据表,并插入数据。具体SQL语句与执行结果
如下。 
mysql> create table set_test(t set('book','game','code')); 
Query OK, 0 rows affected (0.15 sec) 
数据表创建成功,插入3条数据并查看。 
mysql> insert into set_test values(''),('book'),('book,game'); 
Query OK, 3 rows affected (0.04 sec) 
Records: 3 Duplicates: 0 Warnings: 0 
mysql> select * from set_test; 
+-----------+ 
| t |