2.MySQL支持的数据类型

不同的变量、常量都有对应的数据类型，以表示数据不同的存储方式、约束和有效范围，Mysql提供了多种数据类型，主要包括数值类型、字符串类型、时间类型和日期类型。不同的MySQL版本支持的数据类型会有所不同。

1. 数值类型

MySQL几乎支持所有SQL标准的数据类型，其中包括严格数据类型（INTERGER、SMALLINT、DECIMAL、NUMERIC）和近似数值数据类型（FLOAT、REAL、DOUBLE和PRECISION），并再次基础上做了扩展。

整数类型

整数类型	字节	最小值	最大值
TINYINT	1	有符号-128 无符号0	有符号127 无符号255
SMALLINT	2	有符号-32768 无符号0	有符号32767 无符号65535
MEDIUMINT	3	有符号-8388608 无符号0	有符号8388607 无符号1677215
INT、INTEGER	4	有符号-2147483648 无符号0	有符号2147483647 无符号4294967295
BIGINT	8	有符号-9,223,372,036,854,775,808 无符号0	有符号9,223,372,036,854,775,807 无符号18,446,744,073,709,551,615

在整数类型中按照存储方式和取值范围的不同，可以将整数类型分为以上几种，如果存储的数超出取值范围就会报“out of range”的错误提示，为了避免此类错误，应该在选择数据类型的时候根据实际业务场景选择合适的数据类型和取值范围。

对于整数类型，可以在数据类型后面通过小括号的方式指定存储长度，比如int(5)就表示存储5个宽度的int类型数据，还可以通过zerofill来使用，如下示例。

创建表t1，包含两个字段c1和c2

create table t1(
	c1 int(5),
	c2 int(11)
);

先向表中插入一条记录，并查看存储结果

insert into t1 values(5,15);
 select * from t1;

可以看到数据正常保存

接下来分别修改c1、c2添加zerofill属性

alter table t1 modify c1 int(5) zerofill;
alter table t2 modify c2 int(11) zerofill;

然后再向表中插入两条数据（1，1）

insert into t1 values(1,1);

可以看到未被使用的宽度会自动被0填充

那么如果插入的数据超出指定的宽度，会不会被截断或者报错导致插入失败，可以尝试插入一条大于指定宽度的数据如(11111111,1)

insert into t1 values(1111111,1);

可以看到并没有报错或者被截断，此时指定的宽度已经没有了意义。

整数类型可以通过UNSIGNED关键字指定是否为有符号数或无符号数，无符号数取值下线是0，上限是有符号数的2倍，如果指定字段有zerofill属性则默认包含UNSIGNED属性，即为无符号数。

另外整数类型可以通过AUTO_INCREMENT来实现自动增加，在需要产生唯一标识符(主键)或顺序值时可以使用该属性。拥有该属性的字段初始值为1，每次增加1。一张表只能含有一个使用了AUTO_INCREMENT属性的字段，如果一个字段想要使用AUTO_INCREMENT属性，应该标注为NOT NULL，并且指定为PRIMARY KEY或ＵＮＩＱＵＥ键。

小数类型

浮点类型	字节	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38
DOUBLE	8	±2.2250738585072014E-308	±1.7976931348623157E+308

定点数类型	字节	最小值	最大值
DEC(M,D)	M+2	最大值范围与DOUBLE类型相同，给定的DECIMAL范围有给定 的M和D决定
DECIMAL(M,D)

MySQL中小数类型包括:浮点类型和定点类型,浮点类型和定点类型都可以通过类型后面加"(M,D)"来表示,其中M表示数据的总宽度,D表示小数位的长度,比如DECIMAL(8,4)能够存储9999.9999,MySQL采用四舍五入,如果存储9999.00009,近似值为9999.0001. 在float和double之后跟(M,D)的作法是不标准的,尽量不要使用,若不指定(M,D),float和double的默认长度根据系统硬件的不同而决定,DECIMAL的默认长度为10,小数位为0.

以下案例说明float,double,decimal之间的不同

创建数据表demo3

create table demo3(
	c1 float(5,2),
	c2 double(5,2),
	c3 decimal(5,2)
);

向表中插入(5.21,5.21,5.21)

insert into demo3 values(5.21,5.21,5.21);

可以看到数据正常插入到了demo3中

接下来插入数据(5.213,5.213,5.21)

insert into demo3 values(5.213,5.213,5.21);

可以看到float和double自动将超出的部分截断后做了保存

最后在表中插入(5.213,5.213,5.213)

insert into demo3 values(5.213,5.213,5.213);

可以看到在向decimal中插入长处指定长度的数据时,会提示一条警告,并将数据截断后做了保存.

BIT类型

位类型	字节	最小值	最大值
BIT(M)	1~8	BIT(1)	BIT(64)

BIT类型用来存储2进制数据,如果创建表时不指定长度默认长度为1.查询时如果直接使用select * from tablename来查询,结果围为null,在查询时必须使用bin()或hex()来查询.如下示例

首先创建表b1,并插入一条数据

create table b1(
	c1 bit
);
insert into b1 values(1);

直接通过select查询

select * from b1;

通过bin()和hex()查询

select  bin(c1),hex(c1) from b1

2. 日期类型

Mysql有多种数据类型表示日期和时间类型,不同版本的MySQL可能存在差异.

不同数据类性主要区别如下:

• 如果要表示年月日,可以使用DATE类型
• 如果要表示年月日时分秒,可以使用DATETIME类型．
• 只表示时分秒，使用TIME类型．
• 如果要经常插入或更新为当前系统日期,可以使用TIMESTAMP,TIMESTAMP值返回后显示YYYY:MM:DD HH:MM:SS格式的字符串,显示的宽度固定为19个字符
• 如果只想表示年份,可以使用YEAR

日期和时间类型	字节	最小值	最大值
DATE	4	1000-1-1	9999-12-31
DATETIME	８	1000-1-1 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2135

MySQL中日期和时间类型的0值表示

日期和事件类型	0值表示
DATE	0000-00-00
DATETIME	0000-00-00 00:00:00
TIMESTAMP	0000000000000000
TIME	00:00:00
YEAR	0000

日期和时间类型中最常用的是DATＥ、DATETIME、TIME三种类型，以下例子展示如果在这三种类型中存入相同的值，存储的结果会有何不同

首先创建一张表，包含date 、time、datetime中者三种类型

create table  t(
	d date,
	t time,
	dt datetime
);

然后向表中插入相同的值

insert into t values(now(),now(),now());

可以看到datetime类型是date和time两者的组合

TIMESTAMP也是一种日期和时间类型，但是和DATETIME有所不同，下面对其做一些测试

创建表ts ，含有一个timestamp类型的字段

create table ts (
	id timestamp
);

可以看到MySQL自动将默认值设为CURRENT_IMESTAMP,我们可以插入一条null值试试

insert into ts values(null);

可以看到插入null值是MySQL会自动插入当前时间

MySQL只会将第一个timestamp字段这只为当前时间，若想添加第二个timestamp字段，会提示错误。

alter table ts add id2 timestamp default ;

TIMESTAMP还有另一个重要的特点就是和时区有关，不同时区的用户同一时刻看到的timestamp不相同。

YEAR类型只表示年份，当系统只需要使用年份时，使用YEAR类型比DATETIME更加节省空间。MySQL会以YYYY形式来检索YEAR值，范围是1901-2135，当使用两位字符串表示年份时，范围为“00-99”

• “00-69“默认被识别为2000-2069
• “70-99”默认被识别为1968-1999

通过以下例子来观察，创建表y1,包含一个YEAR类型字段

create table y1(y year);

插入一个年份

insert into y1 values(20);

可以看到被识别为2020年

然后向表中插入大于69的数据，观察存储结果

insert into y1 values(85);

可以看到被识别为1985年。

日期和时间类型YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS允许不严格语法，即 -或:可以被其他任何字符代替，如YY@MM@DD HH@MM@SS

、YY%MM%DD HH^MM^SS

字符串类型

MySQL支持多种字符串类型，包括了char、varchar、binary、varbinary、blob、emun、set等类型。

字符串类型	字节	描述及存储需求
char(M)	M	M为0~255之间的整数
varchar(M)		M为0-65535之间的整数，值的长度+1个字节
tinyblob		允许长度0~255字节，值的长度+1个字节
blob		允许长度0~65535字节，指的长度+2个字节
mediumblob		允许长度为0~167772150字节，值的长度+3个字节
longblob		允许长度0~4294967295字节，值的长度+4字节
tinytext		允许长度0~255字节，值的长度+2字节
text		允许长度0~65535字节，值的长度+2字节
mediumtext		允许长度0~167772150字节，值的长度+3字节
longtext		允许长度0~4294967295字节，值的长度+4字节
varbinary(M)		允许0~M个字节的变长字节字符串，值的长度+1字节
binary(M)	M	允许0~M个字节的定长字节字符串

char和varchar类型

char和varchar都用来存储较短的字符串。两者最大的区别在于char存储的指定长度的字符串，其取值为0~255；varchar存储的是可变长度的字符串，器存储范围为0-65535字节。char在检索的时候，会去除字符串尾部的空格，二varchar不会去除尾部的字符串。可以通过以下示例来观察两者的区别。

创建表t，包含两个字段c char(4) 、vc varchar(4)

create table t(c char(4), vc varchar(4));

向表t中插入’ab ’

insert into t values('ab  ','ab  ');

查看两个字段存储的长度

select length(c),length(vc) from t;

可以看到char类型字段的长度为2，varchar类型的字段长度为4

binary和varbinary

binary和varbinary与char和varchar类型类似，不同就是它们包含二进制字符串而不包含非二进制字符串，可以通过例子来观察它们是怎么存储数据的。

创建表t1，字段b为binary

create table t1(b binary(3));

通过多种模式来查询字段b

select *,hex(b),b='c',b='c\0',b='c\0\0' from t1;

可以发现当存储c时，在值的最后填充 0x00来达到指定的长度。

enum类型

enum类型的取值范围需要在创建表时显示的指定，1~255成员需要1个字节存储， 255~65535成员需要2个字节存储。具体通过以下示例观察

创建表t2，定义字段gender为枚举类型，成员为M、F

create table t2(gender enum('M','F'));

插入4条不同的记录

insert into t2 values('M'),('1'),('f'),(null);

可以看到enum类型是不区分大小写的。

set类型

set类型和enum类型非常相似，也是一个字符串对象，包含64个成员。成员不同，传出方式也有所不同。

• 1~8成员的集合，占用1字节
• 9~16成员的集合，占用2字节
• 17-24成员的集合，占用3字节
• 25~32成员的集合，占用4字节
• 33~64成员的集合，占用8字节

除存储方式外，set与enum不同的是set可以一次选取多个值，而enum只能选取一个。如下

创建表t3,并插入多组不同的成员

create table t3 (t set('a','b','c','d'));
insert into t values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');

-1597289371668)]

可以看到enum类型是不区分大小写的。

set类型

set类型和enum类型非常相似，也是一个字符串对象，包含64个成员。成员不同，传出方式也有所不同。

• 1~8成员的集合，占用1字节
• 9~16成员的集合，占用2字节
• 17-24成员的集合，占用3字节
• 25~32成员的集合，占用4字节
• 33~64成员的集合，占用8字节

除存储方式外，set与enum不同的是set可以一次选取多个值，而enum只能选取一个。如下

创建表t3,并插入多组不同的成员

create table t3 (t set('a','b','c','d'));
insert into t values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');