1. 整数类型
数据类型 | 存储(Bytes) | 范围(有符号) | 范围(无符号) |
TINYINT | 1 | (-128,127) | (0,255) |
SMALLINT | 2 | (-32 768,32 767) | (0,65 535) |
MEDIUMINT | 3 | (-8 388 608,8 388 607) | (0,16 777 215) |
INT(INTEGER) | 4 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) |
BIGINT | 8 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) |
【注】 INT(11) 后面的数字 11 表示该数据类型显示宽度,指定能够显示的数值中数字的个数。
2. 浮点数类型和定点数类型
MySQL 中使用浮点数和定点数表示小数
浮点类型有两种: 单精度浮点类型(FLOAT) 和双精度浮点类型(DOUBLE).
定点类型只有一种:DECIMAL.
浮点类型和定点类型都可以用(M,N)来表示,其中M称为精度,表示总共的位数;N 称为标度,表示小数的位数。
数据类型 | 存储(Bytes) | 范围(有符号) | 范围(无符号) |
FLOAT | 4 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) |
DOUBLE | 8 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
DECIMAL(M,D),DEC | M+2 | 依赖于M和D的值 | 依赖于M和D的值 |
【注】:不论是定点类型还是浮点类型,如果用户指定的精度超出精度范围,就会四舍五入进行处理。
1. FLOAT 和 DOUBLE 在不指定精度时,默认会采用实际的精度(由计算机硬件和操作系统决定),DECIMAL 如不指定精度则默认为(10.0).
2. 浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围,它的缺点是会引起精度丢失问题。
3. 在MySOL 中,定点数以字符串的形式存储,在对精度要求比较高的时候(如货币、科学数据等)使用DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点型时需要注意,并应尽量避免做浮点教比较,
3. 日期与时间类型
类型名称 | 日期格式 | 日期范围 | 存储(Bytes) |
YEAR | YYYY | 1901~2155 | 1 |
TIME | HH:MM:SS | -838:59:59~838:59:59 | 3 |
DATE | YYYY-MM-DD | 1000-01-01~9999-12-31 | 3 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~9999-12-31 23:59:59 | 8 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC~2038-01-19 03:14:07 UTC | 4 |
(1). YEAR
YEAR 类型是一个单字节类型,用于表示年,在存储时只需要1个字节。可以使用各种格式指定 YEAR 值,如下所示:
a. 以4位字符串或者4位数字格式表示的 YEAR,范围为 '1901' ~ '2155'。输入格式为 'YYYY' 或者 YYYY ,例如,输入 '2010' 或 2010 ,插入到数据库的值均为2010。
b. 以2 位字串格式表示的YEAR,范围为 '00' 到 '99'.。 '00' ~'69' 和 '70'~'99' 范围的值分别被转换为 2000~2069 和 1970- 1999 范围的 YEAR 值。'0' 与'00' 的作用相同。插入超过取值范围的值将被转换为2000。
c. 以2 位数字表示的 YEAR, 范围为1~ 99。1~69 和 70~99 范围的值分别被转换为 2001-2069 和 1970~ 1999 范 围 的 YEAR 值。注意: 在这里0 值将被转换为0000,而不是2000。
(2). 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'、'D HH:MM'、'D HH'或‘SS'。这里的D 表示日,可以取0~34 之间的值。在插入数据库时,D被转换为小时保存,格式为“D*24 + HH”。
b. 'HMMSS'格式的、没有间隔符的字符串或者HHMMSS 格式的数值。例如’101112‘被理解为“10:11:12', 但‘109712’是不合法的(它的分钟部分没有意义),存储时将变为 00:00:00。
【注】:
为 TIME 列分配简写值时应注意; 如果没有冒号,MySQL 解释值时,会假定最右边的两位表示秒。例如,读者可能认为‘1112’和1112 表示11:12:00(即11点12分),但MySQL 将它们解释为OO:11:12(即11分12 秒)。同样‘12’和12 被解释为OO:00 12.相反,TIME 值中如果使用冒号,则肯定被看作当天的时间。也就是说,'11:12' 表示11:12:00,而不是OO:11:12.
【例】向 tmp1 表中插入系统当前时间,SQL语句如下:
创建表 tmp1:
mysql> CREATE TABLE temp1 (t TIME );
向表中插入数据:
mysql> INSERT INTO temp1 VALUES (CURRENT_TIME),(NOW());
(CURRENT_TIME),(NOW());
查看结果:
mysql> SELECT * FROM temp1;
+----------+
| t |
+----------+
| 12:34:19 |
| 12:34:19 |
+----------+
(3). DATE 类型
DATE 类型只用于输入日期值时,没有时间部分,在存储时需要3 个字节。日期格式为 'YYYY-MM-DD',其中 YYYY 表示年,MM 表示月,DD 表示日。在给DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可,如下:
a. 以 'YYYY-MM-DD' 或者 YYYYMMDD 字符串格式表示的日期,取值范围为 '1000-01-01' ~ '9999-12-31' .
b. 以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示的日期,YY 表示两位的年值。包含两位年值的日期容易让人混淆,因为不知道世纪。MySQL 使用以下规则解释两位年值: 00~69 范围的年值转换为 2000~-2069 : 70~99 范围的年值转换为1970-1999。例如,输入'12-12-31' 插入数据库的期为 2012-12-31: 输入 '981231' 插入数据库的日期为1998-12-31。
c. 以 YYYYMMDD 或者 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069; 70~ 99 范围的年值转换为1970~1999。
d. 使用CURRENT_DATE 或者NOW( ), 插入当前系统日期。
【例】创建数据表 tmp2,定义数据类型为 DATE 的字段 d,向表中插入YYYY-MM-DD 和 YYYYMMDD 、YY-MM-DD 和YYMMDD字符串格式 YY-MM-DD 和 YYMMDD 数字格式与系统当前的日期,SQL.语句如下:
创建 tmp2:
mysql> CREATE table tmp2 (d date) ;
向表中插入 YYYY-MM-DD 和 YYYYMMDD 、 YY-MM-DD 、YYMMDD字符串格式的日期:
mysql> INSERT into tmp2 values ('1998-08-08'),('19980808'),('99-09-09'),('990909');
查看插入结果:
mysql> select * from tmp2;
+------------+
| d |
+------------+
| 1998-08-08 |
| 1998-08-08 |
| 1999-09-09 |
| 1999-09-09 |
+------------+
删除表中的数据:
mysql> DELETE FROM tmp2;
向表中插入 YYYYMMDD 和 YYMMDD 数字格式的日期:
mysql> INSERT into tmp2 values (990909),(19990909);
查看插入结果:
mysql> select * from tmp2;
+------------+
| d |
+------------+
| 1999-09-09 |
| 1999-09-09 |
+------------+
删除表中的数据:
mysql> DELETE FROM tmp2;
向表中插入系统当前日期:
mysql> INSERT INTO tmp2 values (current_date),(now() );
查看插入结果:
mysql> select * from tmp2;
+------------+
| d |
+------------+
| 2018-04-20 |
| 2018-04-20 |
+------------+
CURRENT_DATE 只返回当前日期值,不包括时间部分: NOW( )函数返回日期和时间值,在保存到数据库时,只保留其日期部分。
【注】
MySQL 允许“不严格”语法: 任何标点符号都可以用作日期部分之间的间隔符。例如,‘98-11-31’、‘98.11.31’、‘98/11/31' 和 '98@11@31' 是等价的,这些值也可以正确地插入数据库中。
(4). DATETIME
DATETIME 类型的字段可以同时输入日期和时间信息,在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS', 其中YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。在给DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可,如下所示:
a. 以 'YYYY-MM-DD HH:MM:SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的值,取值范围为 '1000-01-01 00:00:00'~ '9999-12-31 23:59:59'。例如,输入 '2012-12-31 05: 05: 05' 或者 '20121231050505',插入数据库的DATETME 值都为2012-12-31 05: 05:05。
b. 以 'YY-MM-DD HH:M:SS' 或者 'YYMMDDHHMSS' 字符串格式表示的日期,在这里YY表示两位的年值。与前面相同,'00~69' 范围的年值转换为 '2000-2069','70-99' 范围的年值转换为 '1970~1999' 例如,输入 ‘12-12-31 05:05:05' ,插入数据库的DATETIME为 2012-12-31 05:05:05. 输入 '980505050505 ' 插入数据库的DATETIME为 1998-05-05 05:05:05。
c. 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMSS 数字格式表示的日期和时间。例如,输入20121231050505,插入数据库的DATETIME 为 2012-12-31 05:05:05; 输入 981231 050505 插入数据的 DATETIME 为 1998-12-31 05:05: 05.
【例】建数据表tmp3,定义数据类型为DATETIME 的字段dt,向表中插入 YYYY-MM-DD HH:MM:SS 和 YYYYMMDDHHMMSS 、YY-MM-DD HH:MM:SS、YYMMDDHHMMSS 字符串格式和 YYMMDDHHMMSS 、YYYYMMDDHHMMSS 数字格式、系统当前 的日期和时间值,SQL 语句如下:
首先创建表tmp6:
mysql> CREATE TABLE tmp3 (dt DATETIME);
向表中插入 YYYY-MM-DD HH:MM:SS 和 YYYYMMDDHHMMSS 字符串格式的日期:
mysql> INSERT INTO tmp3 VALUES ('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');
查看插入结果:
mysql> SELECT * FROM tmp3;
+---------------------+
| dt |
+---------------------+
| 1998-08-08 08:08:08 |
| 1998-08-08 08:08:08 |
| 2010-10-10 10:10:10 |
+---------------------+
删除表中的数据:
DELETE FROM tmp3;
向表中插入 YY-MM-DD HH:MM:SS 和 YYMMDDHHMMSS 字符串格式的日期:
ysql> INSERT INTO tmp3 VALUES ('99-09-09 09:09:09'),('990909090909'),('101010101010');
查看插入结果:
mysql> SELECT * FROM tmp3;
+---------------------+
| dt |
+---------------------+
| 1999-09-09 09:09:09 |
| 1999-09-09 09:09:09 |
| 2010-10-10 10:10:10 |
+---------------------+
删除表中的数据:
DELETE FROM tmp3;
向表中插入 YYMMDDHHMMSS 、YYYYMMDDHHMMSS 数字格式的日期:
mysql> INSERT INTO tmp3 VALUES ( 19990909090909),(101010101010);
查看插入结果:
mysql> SELECT * FROM tmp3;
+---------------------+
| dt |
+---------------------+
| 1999-09-09 09:09:09 |
| 2010-10-10 10:10:10 |
+---------------------+
删除表中的数据:
mysql> DELETE FROM tmp3;
向表中插入系统当前的日期和时间值:
mysql> INSERT INTO tmp3 VALUES (NOW());
查看插入结果:
mysql> SELECT * FROM tmp3;
+---------------------+
| dt |
+---------------------+
| 2018-04-20 20:26:20 |
+---------------------+
NOW( ) 函数返回当前系统的日期和时间值,格式为 YYYY-MM-DD HH:MM:SS
MySQL 允许“不严格”语法: 任何标点符号都可以用作日期部分或时间部分中的间隔符。例如,‘98-12-31 11:30:45' '98.12.31 11+30+45'、 ' 98/12/31 11*30*45' 和 '98@12@31 11^30^45 ' 是等价的,这些值都可以正确地插入数据库中。
(5). TIMESTAMP
TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定为 19 个字符,日期格式为YYYY-MM-DD HH MM:SS, 在存储时需要 4 个宁节。但是TIMESTAMP 列的取值范围小于DATEITME 的取值范围,为 '1970-01-01 00:0:01' UTC ~ '2038-01-19 03:14:07’UTC,其中, UTC(coordinated Universal Time)为世界标准时间,因此在插入数据时,要保证在合法的取值范围内。
【例】创建数据表 tmp4 ,定义数据类型为 TIMESTAMP 的字段 ts ,向表中插入值 ’19950010101010' 、' 950505050505’、'1996-02-02 02:02:02' 、'97@03@03 03@03@03' 、121212121212、Now(), SQL 语句如下:
CREATE TABLE tmp4 (ts TIMESTAMP);
向表中插入数据:
mysql> INSERT INTO tmp4 values ('19950101010101'),
-> ('950505050505'),
-> ( '1996-02-02 02:02:02'),
-> ('97@03@03 03@03@03'),
-> (121212121212),
-> (now() );
查看插入结果:
mysql> select * from tmp4;
+---------------------+
| ts |
+---------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2018-04-20 21:03:20 |
+---------------------+
TIMESTAMP 与 DATETIME 除了存储宇节和支持的范围不同外,还有一个最大的区别就是: DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关; 而 TIMESTAMP 值的存储是以 UTC (世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
【例】向tmp4 表中插入当前日期,查看插入值;更改时区为东10 区,再次查看插入值,SQL 语句如下:
首先删除表中的数据:
DELETE FROM tmp4;
向表中插入系统当前日期:
mysql> insert into tmp4 values (now());
查看当前时区下的日期值:
mysql> select * from tmp4;
+---------------------+
| ts |
+---------------------+
| 2018-04-20 21:51:20 |
修改当前时区为东 10 区,SQL 语句如下:
mysql> set time_zone = '+10:00';
查看当前时区下的日期值:
mysql> select * from tmp4;
+---------------------+
| ts |
+---------------------+
| 2018-04-20 23:51:20 |
+---------------------+
【注】:
如果为DATETIME 或TMESTAMP 对象分配一个DATE值,则结果值的时间部被设置为00:00:00 ,因为DATE 值不包含时间信息,如果为DATE 对象分配一DATETIME 或TIMESTAMP 值,则结果值的时间部分被删除,因为DATE值不包含时间信息。
4. 字符串类型
字符串类型的字段除了可以存储字符串数据之外,还可以存储其他数据,比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较,以及模式匹配查找。MySQL中的字符串类型有CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET。
VARCHAR、BLOB 和 TEXT 类型是变长类型,对于其存储需求取决于列值的实际长度(在表中用L 表示),而不是取决于关型的最大可能尺寸。例如,一个VARCHAR(10)列能保存最大长度为10 个字符的一个字符串,实际的存储需要是字符串的长度L,加上1个字节以记录字符串的长度。对于字符abcd, L 是4 而存储娄求是5 个字节。
类型名称 | 说明 | 存储需求 |
CHAR(M) | 固定长度非二进制字符串 | M 字节,1<=M<=255 |
VARCHAR(M) | 变长非二进制字符串 | L+1 字节,在此L<=M,1<=M<=65535 |
TINYTEXT | 非常小的非二进制字符串 | L+1 字节, 在此 L< 2^8 |
TEXT | 小的非二进制字符串 | L+1 字节, 在此 L< 2^16 |
MEDIUMTEXT | 中等大小的非二进制字符串 | L+1 字节, 在此 L< 2^24 |
LONGTEXT | 大的非二进制字符串 | L+1 字节, 在此 L< 2^32 |
ENUM | 枚举类型,只能有一个枚举字符串值 | 1或2 个字节,取决于枚举值的数目(最大值65535) |
SET | 字符串对象,可以有零个或多个 SET 成员 | 1、2、3、4或8个字节,取决于集合成员的数量(最多64个成员) |
(I). CHAR 和VARCHAR 类型
CHAR(M) 为固定长度字符串,在定义时要指定字符串列长。当保存时在右侧填充空格以达到]指定的长度。N 表示列长度,M的范围是0一255 个字符。例如,CHAR(4)定义了一个定长度的字符串列,其包含的字符个数最大为4。当检索到CHAR 值时,尾部的空格将被删除。
VARCHAR(M) 是长度可变的字符串,M 表示最大列长度。M 的范围是0--65535. VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为符串的实阿长度加1。例如,VARCHAR(50)定义了一个最大长度为 50 的字符串,如果插入的字符串只有10 个字符,则实际存储的字符串为10 个字符和一个字符串结束字符。在保存和检索VARCHAR 的值时尾部的空格仍保留。
(2).TEXT 类型
TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格T。Text 类型分为4 种: TINYTEXT 、TEXT、MEDIUMTEXT 、LONGTEXT. 不同的 TEXT 类型的存储空间和数据长度也不同。
·| TINYTEXT: 最大长度为 255(2^8 -1)个字符的TEXT列。
·| TEXT: 最大长度为65 535(2^16-1) 个字符的TEXT 列。
·| MEDIUMTEXT: 最大长度为 16777215 (2^24-1) 个字符的TEXT列。
·| LONGTEXT: 最大长度为 4294967295 或 4GB(2^32-1) 个字符的TEXT 列。
(3).ENUM 类型
ENUM 是一个字符串对象,其值为创建表时在列规定中校举的一列值。语法格式如下:
字段名 ENUM ('值1','值2',...,值'n')
字段名指将要定义的字段,值 n 指枚举列表中的第 n 个值。ENUM 类型的字段在取值时,只能在指定的枚举列表中取,而且一次只能收一个。如果创建的成员中有空格,则其尾部的空格自动被删除。ENUIM 值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从 1 开始编号,MySOL 存储的就是这个索引编号,枚举最多可以有65535个元 素。
ENUM 值依照列索引顺序排列,并且空字符串排在非空字符串之前,NULL 值排在其他所有的枚举值之前。
【例】创建表tmp1,定义ENUM 类型的列 enm ( ‘first ', 'second' , 'third') ,查看列成员的索引值,SQL 语句如下:
首先,创建tmp1表:
mysql> create table tmp1( enm ENUM('first' , 'second', 'third'));
插入各个列值:
mysql> insert into tmp1 values ('first'),('second'),('third'),(null);
查看索引值
mysql> select enm, enm+0 FROM tmp1;
+--------+-------+
| enm | enm+0 |
+--------+-------+
| first | 1 |
| second | 2 |
| third | 3 |
| NULL | NULL |
+--------+-------+
提示
ENUM列总有一个默认值,如果将 ENUM 声明为 NULL ,NULL 值则为该列的一个有效值,并且默认值为NULL,如果ENUM 列被声明为 NOT ENUM 其默认值为允许的值列表的第1个元素。
【例】 创建表tmp2,定义INT 类型的字段 soc, ENUM 类型的字段 level, 其值为 (‘excellent',‘good’,‘bad ’),向表tmp2 中插入数据 ‘good’、1、2、3、‘best', SQL 语句如下:
首先,创建数据表:
mysql> create table tmp2(
-> soc INT,
-> level enum('excellent','good','bad')
-> );
插入数据:
mysql> insert into tmp2 values (70,'good'),(90, 1),(75,2),(50,3);
再次插入数据:
mysql> insert into tmp2 values ( 100, 'best');
ERROR 1265 (01000): Data truncated for column 'level' at row 1
系统提示错误信息,可以看到,由于字符串值 'best’不在 ENUM 列表中,所以对数据进行了阻止插入操作。查询结果如下:
mysql> select * from tmp2;
+------+-----------+
| soc | level |
+------+-----------+
| 70 | good |
| 90 | excellent |
| 75 | good |
| 50 | bad |
+------+-----------+
由结果可以看到,因为 ENUM 类型列的值在 MySQL 中都是以编号序列存储的,因此,插入列表中的值 good 或者插入其对应序号 2 的结果是相同的;best 不是列表中的值,因此不能插入数据。
(4).SET类型
SET 是一个字符串对象,可以有零个或多个值,SET 列最多可以有 64 个成员,其值为创建表时规定的一列值。SET 的各值之间用逗号 (,) 分隔。语法格式如下:
SET ('值1','值2',... ,‘值n')
与 ENUM 类型相同,SET 的值在内部用整数表示,每一个值都有一个索引编号。当创建表时,SET成员值的尾部空格将自动被删除。但与 ENUM 类型不同的是,ENUM 类型的字段只能从定义的列值中选择一个值插入,而 SET 类型的列可以从定义的列值中选择多个字符联合插入。
如果插入SET 字段中的列值有重复,则MySQL 自动删除重复的值;插入SET 字段的值的顺序并不重要,MySQL 会在存入数据库时,按照定义的顺序显示; 如果插入了不正确的值,默认情况下,MySQL将忽视这些值,并给出警告。
【例】 创建表tmp3, 定义SET类型的字段s,取值列农为('a', 'b', 'c', 'd'), 插入数据('a')、('a,b,a')、('c,a,d')、('a,x,b,y')。
首先创建表tmp3,SQL 语句如下:
mysql> create table tmp3 ( s SET('a','b','c','d'));
插入数据:
mysql> insert into tmp3 values ('a'),('a,b,a'),('c,a,d');
再次插入数据:
mysql> insert into tmp3 values ('a,x,b,y');
ERROR 1265 (01000): Data truncated for column 's' at row 1
由于插入了 SET 列不支持的值,因此 MySQL 给出错误提示。
查看结果
mysql> select * from tmp3;
+-------+
| s |
+-------+
| a |
| a,b |
| a,c,d |
+-------+
从结果可以看到,对于 SET 来说,如果插入的值为重复的,则只取一个。例如,插入 'a,b,a',则结果为 'a,b' 。如果插入了不按顺序排列的值,则自动按顺序插入。例如,插入 'c,a.d’,结果为“a.c.d”。如果插入了不正确值,该值将被阻止插入,例如,插入值“a,x,b,y”,会给出错误信息。
5. 二进制类型
MySQL 支持两类字符型数据: 文本字符串和二进制字符串。MySQL 中的二进制数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
类型名称 | 说明 | 存储(Bytes) |
BIT(M) | 位字段类型 | 大约(M+7)/8 个字节 |
BINARY(M) | 固定长度二进制字符串 | M |
VARBINARY(M) | 可变长度二进制字符串 | M+1 |
TINYBLOB(M) | 非常小的BLOB | L+1字节,L<2^8 |
BLOB(M) | 小BLOB | L+2字节,L<2^16 |
MEDIUMBLOB(M) | 中等大小的BLOB | L+3字节,L<2^24 |
LONGBLOB(M) | 非常大的BLOB | L+4字节,L<2^32 |
(1). BIT 类型
BIT 是位字段类型。M 表示值的位数,范围为1~64。如果M 被省略,默认为1。如果 BIT(M) 列分配的值的长度小于M 位,在值的左边用0 填充。例如,为BIT(6)列分一个值 b'101', 其效果与分配 b'000101' 相同,BIT数据类型用来保存位字段值,例如,以二进制的形式保存数据 13,13 的二进制形式为1101,在这里需要位数至少为 4 位的 BIT 类型,即可以定义列类型为BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。
【例】 创建表tmp4 . 定义 BIT(4) 类型的字段b,向表中插入数据2、9、15.
首先创建表tmp4,SQL语句如下:
mysql> create table tmp4( b BIT(4) );
插入数据:
mysql> insert into tmp4 values (2),(9),(15);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
查询插入结果:
mysql> select BIN( b+0) from tmp4;
+-----------+
| BIN( b+0) |
+-----------+
| 10 |
| 1001 |
| 1111 |
+-----------+
b+0 表示将二进制的结果转换为对应的数字的值,BIN() 函数将数字转换为二进制形式。
默认情况下,MySQL 不可以插入超出列允许范围的值,因而插入数据时要确保插入的值在指定的范围内。
(2). BINARY 和 VARBINARY 类型
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR, 不同的是它们包含二进制字节字符串。其使用的语法格式如下:
列名称 BINARY (M) 或者 VARBINARY (M)
BINARY 类型的长度是固定的,指定长度之后,不足最大长度的,将在右边填充“\0”补齐以达到指定长度。例如,指定列数据类型为BINARY(3),当插入“a”时,存储的内容实际为“a\0\0”,当插入“ab”时,实际存储的内容为“ab\0”,不管存储的内容是否达到指定的长度,其存储空间均为指定的值 M。
VARBINARY 类型的长度是可变的,其长度可以在0 到最大值之间。例如,指定列数据类型为 VARBNARY20), 如果插入的值的长度只有10.则实际存储空间为10 加1,即其实际占用的空间为字符串的实际长度加1。
(3). BLOB 类型
BLOB 是一个二进制大对象,用来存储可变数量的数据。BLOB 类型分为4种 TINYBLOB、BLOB、MEDIUMBLOB 和LONGBLOB,它们可容纳值的最大长度不同。
BLOB 列存储的是二进制字符串(字节字符串); TEXT 列存储的是非二进制字符中(字符字符串);BLOB 列没有字符集,并且排序和比较基于列值字节的数值; TEXT 列有一个字符集,并且根据字符集对值进行排序和比较。
【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版