3. MySQL支持的数据类型

3.1数值类型

MySQL支持所有标准SQL中的数值类型，主要有整数、浮点数、定点数、位类型。表3-1列出了MySQL5.0中支持的所有数值类型。关键字INT是INTEGER的同名词，DEC是DECIMAL的同名词。

3.1.1 整数类型

(1) 对于整型数据，MySQL支持在类型名称后面的小括号内指定显示宽度。如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，zerofill就是用"0"填充的意思，就是在数字位数不够的空间用字符"0"填满。

设置了宽度限制后，如果插入大于宽度限制的值，不会有任何影响，只不过宽度格式已经失去了它本来的意义。

(2)可选属性UNSIGNED、AUTO_INCREMENT。

UNSIGNED保存无符号数（非负数）可设置此选项。

AUTO_INCREMENT产生唯一标识符或顺序值，一个表中只能有一个AUTO_INCREMENT列。对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。

# 创建表t1mysql> create table t1 (id1 int,id2 int(5));Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1| int(11) | YES  | | NULL||| id2| int(5)  | YES  | | NULL||+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into t1 values(128,128);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------+------+| id1  | id2  |+------+------+|  128 |  128 |+------+------+1 row in set (0.00 sec)# 修改表字段属性为zerofillmysql> alter table t1 modify id1 int zerofill;Query OK, 1 row affected (0.05 sec)Records: 1  Duplicates: 0  Waings: 0mysql> alter table t1 modify id2 int(5) zerofill;Query OK, 1 row affected (0.05 sec)Records: 1  Duplicates: 0  Waings: 0# 不满足宽度0填充mysql> select * from t1;+------------+-------+| id1| id2|+------------+-------+| 0000000128 | 00128 |+------------+-------+1 row in set (0.00 sec)# 插入大于宽度值的数据对于存储没有影响mysql> insert into t1 values(128, 1280001);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------------+---------+| id1| id2 |+------------+---------+| 0000000128 |00128 || 0000000128 | 1280001 |+------------+---------+2 rows in set (0.00 sec)# 更改表字段属性为UNSIGNEDmysql> mysql> alter table t1 add id3 tinyint UNSIGNED;Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Waings: 0# MySQL自动为zerofill列添加unsigned属性mysql> desc t1;+-------+---------------------------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+---------------------------+------+-----+---------+-------+| id1| int(10) unsigned zerofill | YES  | | NULL||| id2| int(5) unsigned zerofill  | YES  | | NULL||| id3| tinyint(3) unsigned| YES  | | NULL||+-------+---------------------------+------+-----+---------+-------+3 rows in set (0.00 sec)# 插入负数提示值越界mysql> insert into t1 values(-1,-1,-1);ERROR 1264 (22003): Out of range value for column 'id1' at row 1# 定义AUTO_INCREMENT列的方式CREATE TABLE A1(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);CREATE TABLE A1(ID INT NOT NULL,PRIMARY KEY(ID));CREATE TABLE A1(ID INT AUTO_INCREMENT NOT NULL,UNIQUE(ID));

3.1.2 小数类型

MySQL小数类型分为浮点数(float,double)和定点数(decimal)。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

(1)浮点数和定点数都可以用类型名称后加"(M,D)"的方式来进行表示。表示该值一共显示M位(整数位+小数位)，其中D位位于小数点后面，支持四舍五入，一般浮点数不这样指定，非标准用法。而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。

# 类型名称加(M,D)|(精度,标度) 定义一个float(7,4)的列可以显示-999.9999;如果插入999.00005，近似结果为999.0001;

(2)浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，小数位会截断，并按四舍五入处理。

mysql> create table t1(id1 float(3,2),id2 double(3,2),id3 decimal(3,2));Query OK, 0 rows affected (0.02 sec)# double类型的id2字段插入的数据超出标度 小数位截断四舍五入mysql> insert into t1 values(2.55,2.555,2.55);mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 2.55 | 2.56 | 2.55 |+------+------+------+1 row in set (0.00 sec)# decimal类型的id3字段插入的数据超出标度 会给出警告提示mysql> insert into t1 values(2.55,2.55,2.555); Query OK, 1 row affected, 1 waing (0.01 sec)# 提示id3字段的数据被截断处理mysql> show waings;+-------+------+------------------------------------------+| Level | Code | Message  |+-------+------+------------------------------------------+| Note  | 1265 | Data truncated for column 'id3' at row 1 |+-------+------+------------------------------------------+1 row in set (0.00 sec)mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 2.55 | 2.56 | 2.55 || 2.55 | 2.55 | 2.56 |+------+------+------+2 rows in set (0.00 sec)# 更改数据类型，省略精度和标度mysql> alter table t1 modify id1 float;mysql> alter table t1 modify id2 double;mysql> alter table t1 modify id3 decimal; # float,double省略精度和标度则会按照实际精度值显示 decimal省略后精度标度默认为(10,0)mysql> desc t1;+-------+---------------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id1| float | YES  | | NULL||| id2| double| YES  | | NULL||| id3| decimal(10,0) | YES  | | NULL||+-------+---------------+------+-----+---------+-------+3 rows in set (0.00 sec)# 插入数据mysql> insert into t1 values(2.555,2.555,2.555);Query OK, 1 row affected, 1 waing (0.00 sec)mysql> show waings;+-------+------+------------------------------------------+| Level | Code | Message  |+-------+------+------------------------------------------+| Note  | 1265 | Data truncated for column 'id3' at row 1 |+-------+------+------------------------------------------+1 row in set (0.00 sec)# float和double类型的字段值没有发生影响，decimal类型的字段发生截断处理mysql> select * from t1;+-------+-------+------+| id1| id2| id3  |+-------+-------+------+|  2.55 |  2.56 |3 ||  2.55 |  2.55 |3 || 2.555 | 2.555 |3 |+-------+-------+------+

3.1.3 BIT(位)类型

(1)用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin() （显示为二进制格式）或者hex()函数进行读取。

(2)数据以十进制进行插入，首先转化为二进制，如果转化后的位数小于实际定义的位数，则插入失败。

# 省略插入的位数默认为1位mysql> create table t1(id1 BIT);Query OK, 0 rows affected (0.02 sec)# 插入十进制5,会被mysql转化为101进行插入mysql> insert into t1 values(5);# 二进制数据位数101大于定义的BIT位数范围，报错ERROR 1406 (22001): Data too long for column 'id1' at row 1# 修改BIT位数mysql> alter table t1 modify id1 BIT(4);# 再次插入数据5mysql> insert into t1 values(5);# 直接使用SELECT默认看不到结果mysql> select * from t1;+------+| id1  |+------+| |+------+mysql> select bin(id1) from t1;+----------+| bin(id1) |+----------+| 101  |+----------+mysql> insert into t1 values(12);mysql> select bin(id1) from t1;+----------+| bin(id1) |+----------+| 101  || 1100 |+----------+

3.2 字符串

MySQL中的字符串包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等字符串类型。

(1)char和varchar，常用来保存较短的字符串，两者的主要区别在于存储方式的不同：char列的长度固定为创建表时声明的长度，即一旦定义后及时存储数据长度不满足也会(空字符填充)占定义的长度空间；而varchar列中的值为可变字符串，存储长度根据字符串的长度变化。另外，在检索的时候，char列删除了尾部的空格，而varchar则保留这些空格。

mysql> create table vc(v varchar(4),c char(4)); # 插入'ab 'mysql> insert into vc values('ab ','ab ');# char类型字段删除了尾部的空格mysql> select *,length(v),length(c) from vc;+------+------+-----------+-----------+| v| c| length(v) | length(c) |+------+------+-----------+-----------+| ab| ab| 3 | 2 |+------+------+-----------+-----------+mysql> select concat(v,'+'),concat(c,'+'),length(v),length(c) from vc; +---------------+---------------+-----------+-----------+| concat(v,'+') | concat(c,'+') | length(v) | length(c) |+---------------+---------------+-----------+-----------+| ab +  | ab+| 3 | 2 |+---------------+---------------+-----------+-----------+

(2)ENUM类型插入数据的时候是忽略大小写的，对于插入的数据不再ENUM指定范围内的值时，会报错。

# 可以看出枚举量下标从1开始mysql> alter table t add gender enum('M','F');mysql> insert into t values(10, 2);mysql> select * from t;+------+--------+| id| gender |+------+--------+|20 | NULL||10 | F  |+------+--------+2 rows in set (0.00 sec)mysql> insert into t values(30, 1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+--------+| id| gender |+------+--------+|20 | NULL||10 | F  ||30 | M  |+------+--------+

3.3 日期和时间类型

这些类型的主要区别如下：

• 表示年月日，通常用DATE表示。
• 年月日时分秒，通常用DATETIME表示。
• 只用来表示时分秒，通常用TIME表示。
• 如果要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP表示。
• 如果只表示年份，可以用YEAR来表示，它比DATE占用更少的空间。YEAR有2位或4位格式的年。默认是4位格式。在两位格式中允许的值是70_69（19702069）。MySQL以YYYY格式显示YEAR值。

从上表可以看出，每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储。

(1)TIMESTAMP相关特性

• MySQL会给表中的第一个TIMESTAMP类型的字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值
• TIMESTAMP和在不同的时区下显示的时间也是不同的。
• TIMESTAMP支持的事件范围较小，如果插入的时间范围超出，则会报错。
• 如果在一个TIMESTAMP列中插入NULL，则该列值自动设置为当前的日期和时间。在插入或更新一行但不明确给TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间。

mysql> create table t1(d date,t time,dt datetime);mysql> desc t1;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| d | date | YES  | | NULL||| t | time | YES  | | NULL||| dt| datetime | YES  | | NULL||+-------+----------+------+-----+---------+-------+# 使用now函数插入当前系统时间mysql> insert into t1 values(now(),now(),now());#　datetime是date和time的结合体，所以根据实际需要设置相应字段类型mysql> select * from t1;+------------+----------+---------------------+| d  | t| dt  |+------------+----------+---------------------+| 2020-07-17 | 20:01:33 | 2020-07-17 20:01:33 |+------------+----------+---------------------+# timestamp相关知识讲解mysql> create table t(ts timestamp);mysql> show create table t\G;*************************** 1. row ***************************Table: tCreate Table: CREATE TABLE `t` (  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)# 增加同类型字段mysql> alter table t add ts1 timestamp;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Waings: 0# MySQL只会为第一个timestamp设置默认值mysql> desc t;+-------+-----------+------+-----+---------------------+-----------------------------+| Field | Type  | Null | Key | Default | Extra|+-------+-----------+------+-----+---------------------+-----------------------------+| ts| timestamp | NO| | CURRENT_TIMESTAMP| on update CURRENT_TIMESTAMP || ts1| timestamp | NO| | 0000-00-00 00:00:00 | |+-------+-----------+------+-----+---------------------+-----------------------------+# 查看当前时区 中国默认东八区mysql> show variables like 'time_zone';+---------------+--------+| Variable_name | Value  |+---------------+--------+| time_zone | SYSTEM |+---------------+--------+mysql> insert into t values(now(),now());mysql> select * from t;+---------------------+---------------------+| ts  | ts1 |+---------------------+---------------------+| 2020-07-17 20:11:28 | 2020-07-17 20:11:28 |+---------------------+---------------------+mysql> set time_zone='+9:00';mysql> show variables like 'time_zone';+---------------+--------+| Variable_name | Value  |+---------------+--------+| time_zone | +09:00 |+---------------+--------+# 时间自动发生了变化mysql> select * from t;+---------------------+---------------------+| ts  | ts1 |+---------------------+---------------------+| 2020-07-17 21:11:28 | 2020-07-17 21:11:28 |+---------------------+---------------------+# 插入时间范围大于TIMESTAMP的范围会报错mysql> insert into t(t) values('2099-09-09 21:00:00');ERROR 1054 (42S22): Unknown column 't' in 'field list'#插入NULL值，则该列值自动设置为当前的日期和时间mysql> insert into t values(NULL,NULL);mysql> select * from t;+---------------------+---------------------+| ts  | ts1 |+---------------------+---------------------+| 2020-07-17 21:45:32 | 2020-07-17 21:45:32 || 2020-07-17 21:47:25 | 2020-07-17 21:47:25 |+---------------------+---------------------+

(2)datetime相关特性

• YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的分隔符。
• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串或数字，假定字符串或数字对于日期类型是有意义的，则会自动被解释为相应的时间。
• 函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。

mysql> create table t6(dt datetime);mysql> insert into t6 values('2020-07-17 20:20:20');mysql> insert into t6 values('2020/07/17 20*20*20');mysql> insert into t6 values('20200717202020');  mysql> insert into t6 values(20200717202020); mysql> select * from t6;+---------------------+| dt  |+---------------------+| 2020-07-17 20:20:20 || 2020-07-17 20:20:20 || 2020-07-17 20:20:20 || 2020-07-17 20:20:20 |+---------------------+