第4章 MySQL数据类型
数据类型是数据的一种属性,数据类型可以决定数据的存储格式、有效范围、相应的限制。
MySQL的数据类型包括:
整数类型;
浮点数类型;
定点数类型;
日期和时间类型;
字符串类型;
二进制数据类型。
本章介绍了各种数据类型的含义、特点、使用范围、存储方式。
4.1 MySQL数据类型介绍
4.1.1 整数类型
标准SQL中支持了INTEGER、SMALLINT两类整数类型。
MySQL还扩展支持了TINYINT、MEDIUMINT、BIGINT。
MySQL中,INT类型和INTEGER类型是一样的。
MySQL支持数据类型的名称后面指定该类型的显示宽度。基本形式如下:
数据类型(显示宽度)
说明:
数据类型参数是整数数据类型的名称;
显示宽度参数是指定宽度的数值,是能够显示的最大数据的长度。
例如:
INT(4)是指定INT类型的显示宽度为4。
补充:
在不指定宽度的情况下,每个整数类型都有默认的显示宽度。
例如:
TINYINT类型的默认显示宽度与其有符号数的最小值的显示宽度相同(因为此处负号是占一个位置的)。
在整数类型使用时,还可以搭配使用zerofill参数。
zerofill参数表示数字不足的显示空间由0来填补。
注意:
使用zerofill参数时,MySQL会自动加上UNSIGNED属性。那么,该整数类型只能表示无符号数,其显示宽度比默认宽度小1。
补充:
1.虽然上面提到可以设置显示宽度,但依然可以插入大于显示宽度的值。
2.当插入数据的显示宽度大于设置的显示宽度时,数据依然可以插入。而且,可以完整的显示出来,设置的显示宽度在显示该记录时失效。
整数类型还有一个AUTO_INCREMENT属性。该属性可以使字段成为自增字段。具有该属性的字段,在插入新的记录时,该字段的值都会在前一条记录的基础上加1。
4.1.2 浮点数类型和定点数类型
MySQL中使用浮点数类型和定点数类型类表示小数。
浮点数类型包括:
单精度浮点数(FLOAT型);
双精度浮点型(DOUBLE型)。
定点数类型就是DECIMAL型。
MySQL中可以指定浮点数和定点数的精度。基本形式如下:
数据类型(M,D)
说明:
“数据类型”参数是浮点数或定点数的数据类型名称;
M参数称为精度,是数据的总长度,小数点不占位置;
D参数称为标度,是指小数点后的长度为D。
例如:
FLOAT(6,2)的含义,数据FLOAT型,数据长度为6,小数点后保留2位。按此定义1234.56是符合要求的。
注意:
如果不指定精度,浮点数和定点数有其默认的精度。
FLOAT型和DOUBLE型默认会保存实际精度,但这与操作系统和硬件的精度有关。
DECIMAL类型默认整数位为10,小数位为0,即默认为整数。
提示:
在MySQL中,定点数以字符串形式存储。
因此,其精度比浮点数要高。
而且,浮点数会出现误差,这是浮点数一直存在的缺陷。
如果对数据的精度要求比较高,还是选择定点数(DECIMAL)比较安全。
4.1.3 日期与时间类型
YEAR类型表示时间;
DATE类型表示日期;
TIME类型表示时间;
DATETIME、TIMESTAMP表示日期和时间。
从上表可以看到,每种日期与时间类型都有一个有效范围。
如果插入的值超过了这个范围,系统会报错,并将零值插入到数据库中。
1.YEAR类型
YEAR类型使用1个字节来表示年份。MySQL中以YYYY的形式显示YEAR类型的值。
表示方法:
□使用4位字符串或数字表示。
范围:
1901~2155。
输入格式:
'YYYY'或YYYY。
例如:
输入'2008'或者2008,可直接保存为2008。
如果超过了范围,就会插入0000。
□使用2位字符串表示。
范围:
'00'~'69'转换为2000~2069,'70'~'99'转换为1970~1999。
例如:
输入'35',YEAR值会转换为2035;
输入'90',YEAR值会转换为1990;
'0'和'00'的效果是一样的。
□使用两位数字表示。
范围:
1~69转换为2001~2069,70~99转换为1970~1999。
注意:
2位数字与2位字符串是不一样的。
因为,如果插入0,转换后的YEAR值不是2000,而是0000。
如果只需要记录年份,选择YEAR类型可以节约空间。
注意:
使用YEAR类型时,要区分0和'0'。
向YEAR类型的字段插入0, 存入该字段的年份是0000;
插入'0',存入该字段的年份是2000;
'00'和'0'是一样的效果。
2.TIME类型
TIME类型使用3个字节来表示时间。MySQL中以HH:MM:SS的形式显示TIME类型的值。说明:
HH表示时;
MM表示分,取值范围为0~59;
SS表示秒,取值范围为0~59。
TIME类型的范围可以从‘-838:59:59’~‘838:59:59’。
虽然,小时的范围是0~23,但是,为了表示某种特殊需要的时间间隔,将TIME类型的范围扩大了。而且,还支持了负值。
TIME类型的字段赋值的表示方法如下:
□'D HH:MM:SS'格式的字符串表示。
说明:
D表示天数,取值范围0~34;
保存时,小时的值等于(D*24+HH)。
例如:
输入'2 11:30:50',TIME类型会转换为59:30:50。
注意:
输入时可以不严格按照这个形式,也可以是:
'HH:MM:SS';
'HH:MM';
'D HH:MM';
'D HH';
'SS'。
例如:
输入'30',TIME类型会转换为00:00:30。
□'HHMMSS'格式的字符串、HHMMSS格式的数值表示。
例如:
输入'345454',TIME类型会转换为34:54:54;
输入345454,TIME类型会转换为34:54:54。
输入0或'0',TIME类型会转换为0000:00:00。
□使用CURRENT_TIME或者NOW()输入当前系统时间。
注意:
一个合法的TIME值,如果超出TIME的范围,将被裁为范围最接近的断点。
例如:
'880:00:00'被转换为838:59:59。
无效TIME值,在命令行下,无法被插入到表中。
3.DATE类型
DATE类型使用4个字节来表示日期。
MySQL中是以YYYY-MM-DD的形式显示DATE类型的值。
说明:
YYYY表示年;
MM表示月;
DD表示日。
范围:
DATE类型的范围可以从‘1000-01-01’~‘9999-12-31’。
□'YYYY-MM-DD'或'YYYYMMDD'格式的字符串表示。
范围:
'1000-01-01'~'9999-12-31'。
例如:
输入'4008-2-8',DATE类型将转换为4008-02-08;
输入'20220308',DATA类型将转换为2022-03-08。
MySQL中还支持一些不严格的语法格式,任何标点都可以用来做间隔符。例如:
'YYYY/MM/DD';
'YYYY@MM@DD';
'YYYY.MM.DD'等等。
例如:
输入'2011.3.8',DATE类型将转换为2011-03-08。
□'YY-MM-DD'或者'YYMMDD'格式的字符串表示。
说明:
'YY'的取值,'00'~'69'转换为2000~2069,'70'~'99'转换为1970~1999。
例如:
输入'35-01-02',DATE类型将转换为2035-01-02。
MySQL也支持一些不严格的语法形式,如'YY/MM/DD'、'YY@MM@DD'、'YY.MM.DD'等。例子见书上。
□YYYYMMDD、YYMMDD格式的数字表示。
说明:
'YY'的取值,'00'~'69'转换为2000~2069,'70'~'99'转换为1970~1999。
例如:
输入790808,DATE类型将转换为1979-08-08;
如果输入的值为0,那么DATE类型会转化为0000-00-00。
□使用CURRENT_DATE或NOW()来输入当前系统日期。
4.DATETIME类型
DATETIME类型使用8个字节来表示日期和时间。
MYSQL中以‘YYYY-MM-DD HH:MM:SS’的形式显示DATETIME类型的值。从其形式可以看出,DATETIME类型可以直接用DATE类型和TIME类型组合而成。
给DATETIME类型的字段赋值的表示方法如下:
□'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'格式的字符串表示。
可以表达的范围是:
‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’。
□'YY-MM-DD HH:MM:SS'或'YYMMDDHHMMSS'格式的字符串表示。
取值范围:
'YY','00'~'69'转换为2000~2069,'70'~'99'转换为1970~1999。
□YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字表示。
例如:
输入20080808080808,DATETIME类型转换为2008-08-08 08:08:08。
□使用NOW()来输入当前系统日期和时间。
5.TIMESTAMP类型
TIMESTAMP类型使用4个字节来表示日期和时间。
范围:
1970-01-01 08:00:01~2038-01-19 11:14:07。
MySQL中,TIMESTAMP是以'YYYY-MM-DD HH:MM:SS'的形式显示TIMESTAMP类型的值,形式上与DATETIME类型显示的格式是一样的。
例子见书上。
TIMESTAMP类型的几种与DATETIME类型不同的形式。如下:
(1)使用CURRENT_TIMESTAMP来输入系统当前日期与时间;
(2)输入NULL时,系统会输入系统当前日期与时间;
(3)无任何输入时,系统会输入系统当前日期与时间。
4.1.4 字符串类型
包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET。
1.CHAR类型和VARCHAR类型
CHAR类型和VARCHAR类型都是在创建表时指定了最大长度,基本形式:
字符串类型(M)
“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型;
M参数指定了该字符串的最大长度为M。
例如,CHAR(4)是指数据类型为CHAR类型,最大长度为4。
CHAR类型的长度是固定的,在创建表时就指定了,长度可以是0~255的任意值。
例如:
CHAR(100)就是指定CHAR类型的长度为100。
VARCHAR类型的长度是可变的,在创建表时指定了最大长度,定义时,最大值可以取0~65535之间的任意值。
指定了VARCHAR类型的最大值后,其长度可以在0到最大长度之间。
例如:
VARCHAR(100)的最大长度是100,但是,不是每条记录都要占用100个字节。而是在这个最大值范围内,使用多少,分配多少。
VARCHAR类型实际占用的空间为字符串的实际长度加1,这样可以有效节约系统空间。
2.TEXT类型
TEXT类型是一种特殊的字符串类型。TEXT只能保存字符类型,如新闻内容等。包括:
TINYTEXT;
TEXT;
MEDIUMTEXT;
LONGTEXT。
4种TEXT类型允许的长度和存储空间的对比:
3.ENUM类型
ENUM类型又被称为枚举类型。
在创建表时,ENUM类型的取值范围就以列表的形式指定了。基本形式:
属性名 ENUM('值1', '值2',..., '值n')
说明:
属性名参数指字段的名称;
“值n”参数表示列表中的第n个值,这些值末尾的空格将会被系统直接删除。
ENUM类型的值只能取列表中的一个元素。
其取值列表中最多能有65535个值。
列表中的每个值都有一个顺序排列的编号,MySQL中存入的是这个编号,而不是列表中的值。
如果ENUM类型加上了NOT NULL属性,其默认值为取值列表的第一个元素。
如果不加NOT NULL属性,ENUM类型将允许插入NULL,而且NULL为默认值。
说明例子见书上。
如果只能选取列表中的一个值,就选择ENUM类型;
如果需要选取列表中多个值的组合,则需要选择SET类型。
4.SET类型
在创建表时,SET类型的取值范围就以列表的形式指定了。基本形式:
属性名 SET('值1', '值2', ..., '值n')
说明:
“属性名”参数指字段的名称;
“值n”参数表示列表中的第n个值,这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时,不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。
同ENUM类型一样,列表中的每个值都有一个顺序排列的编号。MySQL中,存入的是这个编号,而不是列表中的值。
插入记录时,SET字段中的元素顺序无关紧要。存入MySQL数据库后,数据库会字段按照定义时的顺序显示。
4.1.5 二进制类型
二进制类型是在数据库中存储二进制数据的数据类型。
包括:
BINARY、VARBINARY、BIT、BLOB、MEDIUMBLOB、LONGBLOB。
各种二进制类型对比:
1. BINARY和VARBINARY类型
BINARY类型和VARBINARY类型都是在创建表时指定了最大长度,基本形式:
字符串类型( M )
说明:
“字符串类型”参数指定了数据类型为BINARY类型还是VARBINARY类型;
M参数指定了该二进制数的最大字节长度为。
例如:
BINARY(10)是指数据类型为BINARY,其最大长度为10。
BINARY类型的长度是固定的,在创建表时就指定了,不足最大长度的空间由“\0”补全。例如:
BINARY(50)就是指定BINARY类型的长度为50。
VARBINARY类型的长度是可变的,在创建表时指定了最大长度。指定好了VARBINARY类型的最大值以后,其长度可以在0到最大长度之间。例如:
VARBINARY(50)的最大字节长度是50,但是,不是每条记录的字节长度都是50。在这个最大值范围内,使用多少分配多少。
VARBINARY类型实际占用的空间为实际长度加1,这样,可以有效节约系统的空间。
2. BIT类型
BIT类型也是在创建表时指定了最大长度,基本形式:
BIT( M )
说明:
“M”指定了该二进制数的最大字节长度为M,M的最大值为64。
例如:
BIT(4)就是数据类型为BIT类型,长度为4,存储的数据是0~15。
在查询BIT数据类型的数据时,要用BIN(字段名+0)来将值转换为二进制显示。
例如:
SELECT BIN(b+0) FROM bt;
详细见书上。
3. BLOB类型
binary large object block 二进制大对象块
BLOB可以用来保存数据量很大的二进制数据,如图片、PDF文档等。
包括:
TINYBLOB;
BLOB;
MEDIUMBLOB;
LONGBLOB。
BLOB类型与TEXT类型很类似。
不同点:
BLOB类型用于存储二进制数据,是根据二进制编码进行比较和排序;
TEXT类型是文本模式进行比较和排序的。
技巧见书上。
4.2 如何选择数据类型
在MySQL中创建表时,需要考虑为字段选择哪种数据类型是最合适的。只有选择了合适的数据类型,才能提高数据库的效率。
选择数据类型的原则:
1. 整数类型和浮点类型
最常用的整数类型是INT类型。
如果需要精确到小数点10位以上,就应该选择DOUBLE类型,而不是FLOAT类型。
2. 浮点数类型和定点数类型
对于浮点数和定点数,当插入值的精度高于实际意义的精度时,系统会自动进行四舍五入处理。
浮点数进行四舍五入时,系统不会报警,定点数会出现警告。
在未指定精度的情况下,浮点数和定点数有其默认的精度。FLOAT型和DOUBLE型默认会保存实际精度,这个精度与操作系统和硬件的精度有关。DECIMAL型默认整数位为10,小数位为0,即默认为整数。
在MySQL中,定点数精度比浮点数要高。而且,浮点数会出现误差。如果要对数据的精度要求比较高,应该选择定点数。
3. CHAR类型和VARCHAR类型
4. 时间和日期
5. ENUM类型和SET类型
6. TEXT类型和BLOB类型
4.3 常见问题及解答
- MySQL中什么数据类型能够储存路径?
- MySQL中如何使用布尔类型?
- MySQL中如何存储JPG图片和MP3音乐?
4.4 小结
参考文献:
1.《MySQL入门很简单》。