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键。
# 创建表t1
mysql> 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)
# 修改表字段属性为zerofill
mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 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)
# 更改表字段属性为UNSIGNED
mysql> mysql> alter table t1 add id3 tinyint UNSIGNED;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 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 warning (0.01 sec)
# 提示id3字段的数据被截断处理
mysql> show warnings;
+-------+------+------------------------------------------+
| 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 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| 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);
# 再次插入数据5
mysql> 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位格式。在两位格式中允许的值是7069（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: t
Create Table: CREATE TABLE `t` (
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 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 Warnings: 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 |
+---------------------+