目录

mysql数据类型

整型数据类型

浮点数类型和定点数类型

1、日期时间类型

1、YEAR

2、TIME

3、DATE类型

4、DATETIME

5、TIMESTAMP

2、字符串类型

1、CHAR 和 VARCHAR类型：

2、TEXT类型

3、ENUM类型

4、SET类型

二进制字符串类型

1、BIT类型

2、BINARY和VARBINARY 类型

3、BLOB类型

总结：如何选择数据类型

1.整数和浮点数

2、浮点数和定点数

3、日期与时间类型

4、CHAR与VARCHAR之间的特点与选择

mysql数据类型

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型

(1)数值数据类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、

浮点小数数据类型FLOAT和DOUBLE,定点小数类型DECIMAL.

(2)日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP.

(3)字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXTENUM和SET等。

字符串类型又分为文本字符串和二进制字符串。

整型数据类型

在“创建数据表”中，有如下创建表的语句：

create table tb emp1
(
id int(11),
name varchar(25),
deptId int(11),
salary float
);

id字段的数据类型为INT(11)，注意到后面的数字11，这表示的是该数据类型指定的显示宽度，

指定能够显示的数值中数字的个数。例如，假设声明一个INT 类型的字段: year INT(4)

该声明指明，在year字段中的数据一般只显示4位数字的宽度，-128~127 算上符号有四个位数。

在这里要注意:显示宽度和数据类型的取值范围是无关的。

显示宽度只是指明 MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充

如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，

而且能够显示出来

例如：假如向year 字段插入一个数值 19999，当使用SELECT 查询该列值的时候，MySOL显示的将是完整的带有5位数字的 19999，而不是4位数字的值。

其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，

如果不指定，则系统为每一种类型指定默认的宽度值。

创建表tmpl,其中字段x,y,Z,m,n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,

SoL语如下：

create table tmp1
(
x tinyint,
y smallint ,
z mediumint,
m int,
n bigint
);

执行成功之后，便用DESC查看表结构，结果如下：

可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一 种数据类型

可以取到取值范围内的所有值。例如 TINYINT 有符号数和无符号数的取值范围分别为-128~127和0~255，由于负号占了一个数字位，因此TINYINT 默认的显示宽度为4。

同理其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。

不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此，应该根据实际需要选择

最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，

现实生活中很多地方需要用到带小数的数值，下面将介绍 MySOL 中支持的小数类型。

浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。

浮点类型有两种:单精度浮点类型(FLOAT)和双精度浮点类型(DOUBLE)。

定点类型只有一种:DECIMAL。浮点类型和定点类型都可以用(M，N)来表示，

其中 M 称为精度，表示总共的位数: N 称为标度，是表示小数的位数（m为总共的位数，n为小数点后几位）

表5.3列出了MySOL中的小数类型和存储需求。

DECIMAL 类型不同于FLOAT 和 DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与 DOUBLE 一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。

从表5.3可以看到，DECIMAL的存储空间并不是固定的，而由其精度值M决定，占用M+2个字节。

FLOAT类型的取值范围如下:

有符号的取值范围:-3.402823466E+38~-1.175494351E-38

无符号的取值范围:0和1.175494351E-38~3.402823466E+38

DOUBLE类型的取值范围如下:

有符号的取值范围:-1.7976931348623157E+308~-2.2250738585072014E-308

无符号的取值范围:0和2.2250738585072014E-308~1.7976931348623157E+308

创建表tmp2，其中字段x,y,z 数据类型依次为 FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，

向表中插入数据5.12、5.15和5.23，

SQL语如下:

 create table tmp2 
 (
 x float(5,1),
 y double(5,1),
 z decimal(5,1)
 ); 

向表中插入数据：

insert into tmp2 values (5.12,5.15,5.123); 

可以看到在插入数据时，MySQL给出了一个警告信息，使用SHOWWARNINGS;

语句查看如下：

可以看到FLOAT和DOUBLE在进行四舍五入时没有给出警告，而给出z字段数值被截断的警告。

查看结果:

select * from tmp2;

FLOAT和DOUBLE 在不指定精度时，默认会按照实际的精度(由计算机硬件和操作系统决定)，

DECIMAL如不指定精度，默认为(10，0)。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围;

它的缺点是会引起精度问题。

在MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候 (如货币，科学数据等)

使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，

所以在使用浮点型时需要注意，并尽量避免做浮点数比较。

1、日期时间类型

1、YEAR

YEAR类型是一个单字节类型用于表示年，在存储时只需要1个字节。

可以使用各种格式指定YEAR值，如下所示:

(1)以4位字符串或者4位数字格式表示的YEAR，范围为“1901"~“2155"。

输入格式为'YYYY’或者YYYY，例如，输入'2010'或2010，插入到数据库的值均为2010

(2)以2位字符串格式表示的YEAR，范围为“00’到“99’。

‘00’~69’和“70’~99’范围的值分别被转换为2000~2069和1970-1999范围的YEAR值。“0’与“0’的作用相同。(00~69对应2000~2069，70~99对应1970-1999)

插入超过取值范围的值将被转换为 2000。

(3)以2位数字表示的YEAR，范围为1~99。1~69和70~99 范围的值分别被转换为2001~2069 和1970~1999 范围的YEAR值。注意在这里0值将被转换为0000而不是2000。

注意：两位整数范围与两位字符串范围稍有不同，

例如:插入2000年，读者可能会使用数字格式的0表示YEAR，实际上，插入数据库的值为0000，

而不是所希望的 2000。只有使用字符串格式的0或00，才可以被正确地解释为2000。

非法YEAR值将被转换为 0000。

比如：

创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入 2010，’2010’，’2166’，

SQL语句如下：首先创建表tmp3：

create table tmp3
(
 y year 
); 

向表中插入数据：

insert into tmp3 values(2010),('2010');

再次向表中插入数据：

insert into tmp3 values ('2166'); 

ERROR 1264 (22003): Out of range value for column 'y' at row 1

语句执行之后，MySQL给出了一条错误提示，使用SHOW查看错误信息：

show warnings;

可以看到，插入的第3个值2166 超过了YEAR类型的取值范围，

此时不能正常的执行插入操作，查看结果: 

 select * from tmp3;

由结果可以看到，当插入值为数值类型的2010或者字符串类型的'2010’时，都正确地储存到了数据库中;而当插入值“2166’时，由于超出了 YEAR 类型的取值范围，因此，不能插入值。

再举例：向tmp3表中y字段插入2位字符串表示的YEAR值，分别为’0’、’00’、’77’和’10’，

SQL语句如下：首先删除表中的数据：

 delete from tmp3;

向表中插入数据：

 insert into tmp3 values('0'),('00'),('77'),('10'); 

查看结果：

 select * from tmp3;

由结果可以看到，字符串’0’和’00’的作用相同，分别都转换成了2000年；

’77’转换为1977；’10’转换为2010。

再举例：向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0、78和11，

SQL语句如下：首先删除表中的数据：

 delete from tmp3;

向表中插入数据：

insert into tmp3 values(0),(78),(11); 

查看结果：

select * from tmp3;

由结果可以看到，0被转换为0000；78被转换为1978；11被转换为2011。

2、TIME

TIME类型用于只需要时间信息的值，在存储时需要3个字节。

格式为"HH:MM:SS"

HH表示小时 MM表示分钟 SS表示秒。

TIME类型的取值范围为-838:59:59~838:59:59

小时部分会如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于24小时)

还可能是某个事件过去的时间或两个事件之间的时间间隔 (可以大于 24 小时，或者甚至为负)。

可以使用各种格式指定TIME值，如下所示:

(1)“D HH:MM:SS’格式的字符串。还可以使用下面任何一种“非严格”的语法:"HH:MM:SS"、

‘HH:MM’，‘D HH’或“SS’。这里的D表示日，可以取0~34之间的值。在插入数据库时，D被转换为小时保存，格式为“D*24+HH”。

(2)‘HHMMSS’格式的、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。

例如:“101112’被理解为“10:11:12’，但“109712’是不合法的(它有一个没有意义的分钟部分,97超过59了)，存储时将变为00:00:00。

注意：

为TIME 列分配简写值时应注意:如果没有冒号，MySQL 解释值时，最右边的两位表示秒。 (MySQL解释TIME 值为过去的时间而不是当天的时间)。

例如，我们可能认为“1112’和1112表示11:12:00(即11点过12分)，

但MySQL将它们解释为00:11:12(即11分12秒)。

同样“12’和12 被解释为 00:00:12。

相反，TIME 值中如果使用冒号则肯定被看作当天的时间。

也就是说，“11:12’表示 11:12:00，而不是00:11:12。

比如：创建数据表tmp4，定义数据类型为TIME的字段t，

向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’，SQL语句如下：

首先创建表tmp4，

create table tmp4
(
 t time
 ); 

向表中插入数据：

insert into tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'), ('10'); 

查看结果：

select  *  from tmp4; 

由结果可以看到，’10:05:05‘ 被转换为被转换为 10:05:05; ‘23:23’被转换为23:23:00;

’2 10:10’被转换为58:10:00，‘3 02’被转换为74:00:00; “10’被转换成

00:00:10。

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

向表tmp4中插入值’101112’，111213,’0’，107010，SQL语句如下：

首先删除表中的数据：

delete from  tmp4; 

向表中插入数据：

insert into tmp4 values('101112'),(111213),('0'); 

再向表中插入数据：

insert into tmp4 values (107010); 

ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1

可以看到，在插入数据时，MySQL给出了一个错误提示信息，

使用SHOW WARNINGS;查看错误信息，如下所示：

可以看到，第二次在插入记录的时候，数据超出了范围，原因是107010的

分钟部分超过了60,分钟部分是不会超过60的，

查看结果：

select * from tmp4; 

由结果可以看到，’101112’被转换为10:11:12；111213被转换为11:12:13；

’0’被转换为00:00:00；107010因为是不合法的值，因此不能被插入。

也可以使用系统日期函数向TIME字段列插入值。

比如：向tmp4表中插入系统当前时间，SQL语句如下：

首先删除表中的数据：

delete from tmp4; 

向表中插入数据：

insert into tmp4 values (CURRENT_TIME) ,(NOW()); 

查看结果：

select * from tmp4;

由结果可以看到，获取系统当前的日期时间插入到TIME类型列，

因为输入语句的时间不确定，因此获取的值与这里的可能是不同的，

但都是系统当前的日期时间值。

CURRENT_TIME——当前时间

NOW()—— 函数返回当前的日期和时间。

查看目前的时间方法：

date

3、DATE类型

DATE 类型用在仅需要日期值时，没有时间部分，在存储时需要3个字节。

日期格式为YYYY-MM-DD’ 其中YYYY 表示年; MM表示月;DD表示日。

在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，

只要符合 DATE 的日期格式即可如下:

(1)以‘YYYY-MM-DD’或者’YYYYMMDD‘字符串格式表示的日期，取值范围为1000-01-01’~9999-12-3’。

例如，输入“2012-12-31’或者“20121231’，插入数据库的日期都为2012-12-31。

(2)以’YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期，

在这里YY表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。

MySOL使用以下规则解释两位年值:“00~69’范围的年值转换为“2000~2069’;

“70~99’范围的年值转换为“1970~1999’。

例如，输入“12-12-31’，插入数据库的日期为2012-12-31;输入'981231’，插入数据的日期为1998-12-31。

(3)以YY-MM-DD或者YYMMDD数字格式表示的日期与前面相似，

00-69 范围的年值转换为2000~2069;70~99范围的年值转换为19701999。

例如输入12-12-31插入数数据的日期为1998-12-31。

(4)使用CURRENTDATE或者NOW()，插入当前系统日期。

比如：

创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”

字符串格式日期，SQL语句如下：

首先创建表tmp5：

create table tmp5
(
d date
); 

向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式日期：

insert into tmp5 values('1998-08-08'),('19980808'),('20101010'); 

查看插入结果：

select * from tmp5; 

可以看到，各个不同类型的日期值都正确的插入到了数据表中。

再比如：向tmp5表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期，SQL

语句如下：

首先删除表中的数据：

delete from tmp5; 

向表中插入“YY-MM-DD”和“YYMMDD”格式日期：

insert into tmp5 values ('99-09-09'),( '990909'), ('000101') , ('111111'); 

查看插入结果：

select * from tmp5;

例子2：向tmp5表中插入YYYYMMDD 和 YYMMDD 数字格式日期，SQL语

句如下：

首先删除表中的数据：

delete from tmp5; 

向表中插入YY-MM-DD和YYMMDD数字格式日期：

insert into  tmp5 values (19990909),(990909), (000101) ,(111111); 

查看插入结果：

select * from tmp5;

例子3：向 tmp5 表中插入系统当前日期，SQL 语句如下：

① 删除表中的数据：

delete from tmp5; 

② 向表中插入系统当前日期：

insert into tmp5 values( CURRENT_DATE() ),( NOW() ); 

③ 查看插入结果：

select * from tmp5;

CURRENT_DATE 只返回当前日期值，不包括时间部分；

NOW() 函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。

MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。

例如，“98-11-3”、“98.11.3”、“98/11/3”和“98@11@3”是等价的，这些值都可以正确地插入到数据库中。

4、DATETIME

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要8字节。

日期格式为 YYYY-MM-DD HH:MM:SS。

其中，YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，

只要符合 DATETIME 的日期格式即可。

1) 以“YYYY-MM-DD HH:MM:SS”或者“YYYYMMDDHHMMSS”字符串格式表示的值，

取值范围为1000-01-01 00:00:00~9999-12-3 23:59:59。

例如:

输入“2012-12-31 05:05:05”或者“20121231050505”，

插入数据库的 DATETIME 值都为 2012-12-31 05: 05: 05。

2) 以“YY-MM-DD HH:MM:SS”或者“YYMMDDHHMMSS”字符串格式表示的日期，

在这里 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。

3) 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。

例如:

输入“20121231050505”，插入数据库的 DATETIME 为 2012-12-31 05:05:05；

输入“981231050505”，插入数据的 DATETIME 为 1998-12-31 05: 05:05。

举例：

创建数据表 tmp6，定义数据类型为 DATETIME 的字段 dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和时间值，

SQL 语句如下：

① 创建表 tmp6：

create table tmp6(dt datetime); 

② 向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式日期：

insert into tmp6 values(19990909090909), (101010101010); 

查看插入结果：

select * from tmp6; 

可以看到，各个不同类型的日期值都正确地插入到了数据表中。

举例2：向 tmp6 表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”

字符串格式日期和时间值，SQL 语句如下：

① 删除表中的数据：

delete from tmp6; 

② 向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式日期：

insert into tmp6 values('99-09-09 09:09:09'),('990909090909'), ('101010101010');  

查看插入结果：

select * from tmp6; 

举例3：向 tmp6 表中插入“YYYYMMDDHHMMSS”和“YYMMDDHHMMSS”

数字格式日期和时间值，SQL 语句如下：

① 删除表中的数据：

delete from tmp6; 

② 向表中插入“YYYYMMDDHHMMSS”和“YYMMDDHHMMSS”数字格式日期

和时间：

insert into tmp6 values(19990909090909), (101010101010); 

查看插入结果：

select * from tmp6; 

举例4：向 tmp6 表中插入系统当前日期和时间值，SQL 语句如下：

① 删除表中的数据：

delete from tmp6; 

② 向表中插入系统当前日期：

insert into tmp6 values( NOW() ); 

查看插入结果：

select * from tmp6; 

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@3111^30^45 是等价的，这些值都可以正确地插入数据库。

5、TIMESTAMP

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，

日期格式为“YYYY-MM-DD HH:MM:SS”，在存储时需要 4 字节。

TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，

为 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC。

其中，UTC（Coordinated Universal Time）为世界标准时间，

因此在插入数据时，要保证在合法的取值范围内。

show variables like "%time zone" ——查看mysqli的时区设置

使用date命令 date+"%Z" /查看当前操作系统的时区date-R

sudo timedatectl set-ntp true 同步系统时间与网络时间

举例1:

创建数据表 tmp7，定义数据类型为 TIMESTAMP 的字段 ts，向表中插入值

19950101010101、950505050505、1996-02-02 02:02:02、97@03@03 03@03@03、121212121212、NOW()，

SQL 语句如下：

① 创建数据表 tmp7：

create table tmp7
(
ts timestamp
);

② 向表中插入数据：

insert into tmp7 values ('19950101010101'),('950505050505'), ('1996-02-02 02:02:02'), ('97@03@03 03@03@03'), (121212121212), ( NOW() ); 

查看插入结果：

select * from tmp7; 

由结果可以看到，“19950101010101”被转换为 1995-01-01 01:01:01；

“950505050505”被转换为 1995-05-05 05:05:05；

“1996-02-02 02:02:02”被转换为 1996-02-02 02:02:02；

“97@03@03 03@03@03”被转换为 1997-03-03 03:03:03；

121212121212 被转换为 2012-12-12 12:12:12；

NOW() 被转换为系统当前日期时间 2023-09-18 17:27:57。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个

最大的区别就是：

DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存

储什么，与时区无关；

而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时

对当前时区进行转换，

检索时再转换回当前时区。查询时，不同时区显示的时间值是不同的。

举例1：向 tmp7 表中插入当前日期，查看插入值，更改时区为东 10 区，

再次查看插入值，

SQL 语句如下：

① 删除表中的数据：

delete from tmp7; 

② 向表中插入系统当前日期：

insert into tmp7 values( NOW() ); 

③ 查看当前时区下的日期值：

select * from tmp7; 

查询结果为插入时的日期值。我国所在时区一般为东 8 区，下面修改当前时区为东 10 区，

SQL 语句如下：

set time_zone='+10:00'; 

再次查看插入时的日期值：

select * from tmp7; 

由结果可以看到，因为东 10 区时间比东 8 区快 2 个小时，

所以查询的结果经过时区转换之后，显示的值增加了 2 小时。

类似地，如果时区每减小一个值，则查询显示的日期中的小时数减 1。

如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，那么结果 值的时间部分

将被设置为“00:00:00”，因为 DATE 值未包含时间信息。如果为一个DATE 对象分配一个DATETIME 或 TIMESTAMP 值，那么结果值的时间部分将被删除， 因为 DATE 值未包含时间信息。

2、字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据比如图片和声音的二进制数据。

MySQL 支持两类字符型数据:文本字符串和二进制字符串。

本小节主要讲解文本字符串类型,文本字符串可以进行区分或者不区分大小写的串比较,另外还可以进行模式匹配查找。

MySQL中文本字符串类型指CHAR、VARCHAR、TEXT、ENUM和SET。

表5.5列出了MySQL中的文本字符串数据类型。

VARCHAR和TEXT类型与下一小节讲到的BLOB一样是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用 L 表示)，而不是取决于类型的最大可能尺寸。

例如，一个VARCHAR(10)列能保存最大长度为 10个字符的一个字符串，实际的存储需要是字符串的长度L，加上1个字节以记录字符串的长度。对于字符“abcd”，L是4而存储要求是5个字节。本章节介绍了这些数据类型的作用以及如何在查询中使用这些类型。

1、CHAR 和 VARCHAR类型：

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(4)和VARCHAR(4)列，说明CHAR和VARCHAR之间的差别，如表5.6所示。

对比结果可以看到，CHAR(4) 定义了固定长度为4 的列，不管存入的数据长度为多少所占用的空间均为4个字节。VARCHAR(4)定义的列所占的字节数为实际长度加1。

当查询时CHAR(4)和VARCHAR(4)的值并不一定相同。

举例：

创建tmp8表，定义字段ch和vch数据类型依次为CHAR(4)、VARCHAR(4)向表中插入 数据“ab ”，

SQL语句如下：

创建表tmp8：

create table tmp8
(
ch char(4),
vch varchar(4)
);

输入数据：

insert into tmp8 VALUES('ab ', 'ab '); 

查询结果：

select concat('(', ch, ')'), concat('(',vch,')') from tmp8; 

从查询结果可以看到，ch 在保存“ab ”时将末尾的两个空格删除了，而 vch 字段保留了末尾的两个空格。

2、TEXT类型

TEXT列保存非二进制字符串，如文章内容、评论等。

当保存或查询TEXT列的值时，不删除尾部空格。

Text类型分为4种:TINYTEXT、TEXT、MEDIUMTEXT和 LONGTEXT不同的TEXT类型的存储空间和数据长度不同。

(1)TINYTEXT最大长度为255 (2^8-1)字符的TEXT列

(2)TEXT最大长度为65535(2^16-1)字符的TEXT列。

(3)MEDIUMTEXT 最大长度为 16 777 215(2^24-1)字符的TEXT列

(4)LONGTEXT最大长度为4 294 967 295或4GB(2^32-1)字符的TEXT列

3、ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。

语法格式如下:

字段名 ENUM('值1’，‘值2’ ... ‘值n')

字段名指将要定义的字段，值n 指枚举列表中的第n个值。ENUM类型的字段在取值时

只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，

其尾部的空格将自动被删除。

ENUM 值在内部用整数表示，每个枚举值均有一个索引值:列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。

枚举最多可以有65535个元素。例如定义ENUM类型的列(first，second，third)，

该列可以取的值和每个值的索引如表5.7所示。

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有的枚举值前。这一点也可以从表5.7中看到在这里，有一个方法可以查看列成员的索引值，

举例：

创建表tmp9，定义ENUM类型的列enm('first'，'second'，'third')，查看列成员的索引值，

SQL语句如下：首先，创建tmp9表：

create table tmp9
( 
enm enum('first','second','third')
); 

插入各个列值：

insert into tmp9 values('first'),('second') ,('third') , (NULL); 

查看索引值：

select enm,enm+0 from tmp9;

可以看到，这里的索引值和前面所述的相同

ENUM 列总有一个默认值。如果将ENUM 列声明为NULL，NULL值则为该列的一个有效值，

并且默认值为NULL。若ENUM 列被声明为NOT NULL，其默认值为允许的值列表第1个元素。

举例：创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，列表值为

('excellent','good', 'bad')，向表tmp10中插入数据’good’，1，2，3，’best’，

SQL语句如下：

首先，创建数据表：

create table tmp10 
(
soc int, 
level enum('excellent', 'good','bad') 
); 

插入数据：

insert into tmp10 values(70,'good'), (90,1),(75,2),(50,3); 

再次插入数据：

insert into tmp10 values (100,'best'); 

ERROR 1265 (01000): Data truncated for column 'level' at row 1

这里系统提示错误信息，可以看到，由于字符串值“best”不在ENUM列表中，

所以对数据进行了阻止插入操作，查询结果如下：

select * from tmp10; 

由结果可以看到，因为ENUM 列表中的值在MySOL中都是以编号序列存储的，

因此，插入列表中的值“good”或者插入其对应序号“2’的结果是相同的;“best”不是列表中的值，因此不能插入数据。

4、SET类型

SET 是一个字符串对象，可以有零或多个值，SET 列最多可以有 64 个成员，其值为

表创建时规定的一列值。指定包括多个 SET 成员的SET列值时，各成员之间用逗号间隔开。

语法格式如下:

SET ('值1'，'值2'，··· ‘值n’)

与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。

当创建表时，SET成员值的尾部空格将自动被删除。但与ENUM类型不同的是，

ENUM类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的

列值中选择多个字符的联合。

如果插入SET字段中列值有重复，则 MSQL 自动删除重复的值:插入 SET 字段的值的

顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示;如果插入了不正确

的值，默认情况下，MySQL将忽视这些值，并给出警告。

举例：

创建表tmp11，定义SET类型的字段s，取值列表为('a', 'b', 'c', 'd')，

插入数据('a')，('a,b,a')，('c,a,d')，('a,x,b,y')，SQL语句如下：

首先创建表tmp11：

create table tmp11 
( 
s set('a', 'b', 'c', 'd')
); 

插入数据：

insert into tmp11 values('a'),('a,b,a'),('c,a,d');

再次插入数据：

insert into tmp11 values ('a,x,b,y');

ERROR 1265 (01000): Data truncated for column 's' at row 1

由于插入了SET列不支持的值，因此MySQL给出错误提示。

查看结果：

select * from tmp11; 

从结果可以看到，对于SET来说如果插入的值为重复的，则只取一个，

例如“a,b,a”，则结果为“a,b”；

如果插入了不按顺序排列值，则自动按顺序插入，例如“c,a,d”，结果为“a,c,d”；

如果插入了不正确值，该值将被阻止插入，例如插入值“a,x,b,y”。

========================================================================

二进制字符串类型

前面讲解了存储文本的字符串类型，这一小节将讲解 MySQL中存储二进制数据的字符串类型。MySQL中的二进制数据类型有:BIT、BINARY、VARBINARY、TINYBLOB、BLOBMEDIUMBLOB和LONGBLOB，

本节将讲解各类二进制字符串类型的特点和使用方法。

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)类型的字段中的。

举例：创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15、16。

首先创建表tmp12，SQL语句如下：

create table tmp12
(
b bit(4) 
);

插入数据：

insert into tmp12 VALUES(2),(9),(15);

查询插入结果：

select bin(b+0) from tmp12;

b+0表示将二进制的结果转换为对应的数字的值，BIN() 函数将数字转换为二进制。

从结果可以看到，成功的将3个数插入到表中。

默认情况下，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到最大值之间。

例如:指定列数据类型为 VARBINARY(20)，如果插入的值的长度只有 10，则实际存储空间为10加1，即其实际占用的空间为字符串的实际长度加1。

举例：

创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb， 并向表中插入数据’5’，比较两个字段的存储空间。

首先创建表tmp13，输入SQL语句如下：

create table tmp13
( 
b binary(3), 
vb varbinary(30) 
); 

插入数据：

insert into tmp13 VALUES(5,5);

查看两个字段存储数据的长度：

select length(b),length(vb) from tmp13;

length()——算字段的长度

可以看到，b字段的值数据长度为3，而vb字段的数据长度仅为插入的一个字符的长度1。

如果想要进一步确认’5’在两个字段中不同的存储方式，输入如下语句：

select b,vb,b = '5', b='5\0\0',vb='5',vb = '5\0\0' from tmp13; 

由执行结果可以看出，b字段和vb字段的长度是截然不同的，因为b字段不足的空间填充了’\0’，而vb字段则没有填充。

3、BLOB类型

BLOB是一个二进制大对象,用来存储可变数量的数据。

BLOB类型分为4种:TINYBLOBBLOB、MEDIUMBLOB和LONGBLOB，它们可容纳值的最大长度不同。

BLOB列存储的是二进制字符串(字节字符串);

TEXT列存储的是非二进制字符串(字符字符串)。

BLOB列没有字符集，并且排序和比较基于列值字节的数值:

TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

总结：如何选择数据类型

MySQL 提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应

使用最精确的类型。即在所有可以表示该列值的类型中，该类型使用的存储最少。

1.整数和浮点数

如果不需要小数部分，则使用整数来保存数据:

如果需要表示小数部分，则使用浮点数类型。

对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。

例如，如果列的值的范围为1~99999，若使用整数，则MEDIUMINT UNSIGNED是最好

若要存储小数则使用FLOAT类型。

浮点类型包括FLOAT和DOUBLE 类型。DOUBLE 类型精度比FLOAT 类型高，因此如要求存储精度较高时，应选择DOUBLE类型。

2、浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数 DECIMAL的优势是:

在长度一定的情况下，浮点数能表示更大的数据范围，但是由于浮点数容易产生误差，

因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySOL中是以

字符串存储的，用于定义货币等对精确度要求较高的数据。

在数据迁移中， float(M,D)是非标准 SQL定义，数据库迁移可能会出现问题，最好不要这样使用。

另外两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要小心。如果进行数值比较，最好使用 DECIMAL类型。

3、日期与时间类型

MySQL对于不同种类的日期和时间有很多的数据类型，比如YEAR和TIME。

如果只需要记录年份，则使用YEAR类型即可;

如果只记录时间，只需使用TIME类型

如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME类型。

由于TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期

最好使用DATETIME。

TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并

没有 指定TIMESTAMP这个列值时，MySOL会把TIMESTAMP列设为当前的时间。因此当需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，另外TIMESTAMP在空间上比DATETIME更有效。

4、CHAR与VARCHAR之间的特点与选择

CHAR和VARCHAR的区别如下CHAR是固定长度字符，VARCHAR是可变长度字符。

CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格

CHAR是固定长度，所以它的处理速度比VARCHAR 的速度要快，但是它的缺点就是

浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用VARCHAR类型来实现。

存储引擎对于选择CHAR和VARCHAR的影响

对于MyISAM存储引擎:最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。

对于InnoDB存储引擎:使用可变长度的数据列，因为InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，

但由于VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 IO 和数据存储总量比较好，用时间换空间。

一般数字用int，字母用varchar，文本用text，图片用blob