MySQL-04 存储引擎和数据类型

本章将对MySQL的存储引擎和数据类型的使用进行详细的讲解。

1 MySQL存储引擎

存储引擎其实就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的；而MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据需要编写自己的存储引擎。

引擎名称	事务	说明
MYISAM	N	MySQL5.6之前的默认引擎，最常用的非事务型存储引擎
CSV	N	以CSV格式存储的非事务性存储引擎，常用于数据交换
Archive	N	只允许查询和新增数据而不允许修改的非事务性存储引擎，用于归档和日志存储
Memory	N	是一种易失性非事务性存储引擎
InnoDB	Y	最常用的事务性存储引擎

1.1 MySQL存储引擎的概念

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的、不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。

这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎（也称作表类型）。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储信息、如何检索这些信息以及需要的数据结合什么性能和功能的时候为其提供最大的灵活性。

1.2 查询MySQL中支持的存储引擎

• 查询支持的全部存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> 

SHOW ENGINES语句可以用“;”结束，也可以用“\g”或者“\G”结束。“\g”与“;”的作用是相同的，“\G”可以让结果显示得更加美观。

• 查询默认的存储引擎
  默认情况下，创建表不指定表的存储引擎，则新表是默认存储引擎的。

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

在创建表的时候，可以通过增加ENGIN关键字设置新表的存储引擎：

CREATE TABLE `tb_admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

也可以使用alter语句进行修改：

alter table tb_admin engine = MyISAM;

1.3 如何选择存储引擎

每种存储引擎都有各自的优势，不能笼统地说谁比谁更好，只有适合不适合。下面将重点介绍几种常用的存储引擎。

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全	不支持	支持	不支持	不支持	不支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引	不支持	不支持	支持	不支持	支持
全文索引	支持	不支持	不支持	不支持	不支持
集群索引	不支持	支持	不支持	不支持	不支持
数据索引	不支持	支持	支持	不支持	支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持	不支持	不支持	不支持	不支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键	不支持	支持	不支持	不支持	不支持

下面根据其不同的特性，给出选择存储引擎的建议。

• （1）InnoDB存储引擎：用于事务处理应用程序，具有众多特性，包括ACID事务支持，支持外键。同时支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高，要求实现并发控制，那选择InnoDB存储引擎有其很大的优势。如果需要频繁地进行更新、删除操作的数据库，也可以选择InnoDB存储引擎，因为该类存储引擎可以实现事务的提交（Commit）和回滚（Rollback）。
• （2）MyISAM存储引擎：管理非事务表，它提供高速存储和检索，以及全文搜索能力。MyISAM存储引擎插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM存储引擎能实现处理的高效率。如果应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎。
• （3）MEMORY存储引擎：MEMORY存储引擎提供“内存中”的表，MEMORY存储引擎的所有数据都在内存中，数据的处理速度快，但安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求，不能建太大的表。所以，这类数据库只使用相对较小的数据库表。
• （4）MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分合适。

2 数据类型

在MySQL数据库中，每一条数据都有其数据类型。MySQL支持的数据类型主要分成3类：数字类型、字符串（字符）类型、日期和时间类型。

2.1 数字类型

MySQL支持所有的ANSI/ISO SQL 92数字类型。这些类型包括准确数字的数据类型（NUMERIC、DECIMAL、INTEGER和SMALLINT），还包括近似数字的数据类型（FLOAT、REAL和DOUBLE PRECISION）。其中的关键词INT是INTEGER的同义词，关键词DEC是DECIMAL的同义词。

在创建表时，使用哪种数字类型，应遵循以下原则。

• （1）选择最小的可用类型，如果值永远不超过127，则使用TINYINT比INT强。
• （2）对于完全都是数字的，可以选择整数类型。
• （3）浮点类型用于可能具有小数部分的数，如货物单价、网上购物交付金额等

数字类型总体可以分成整型和浮点型两类：

2.1.1 整型

数据类型	取值范围	说明	单位
TINYNT	符号值：-127~127 无符号值: 0~255	最小的整数	1字节
BOOL	符号值：-127~127 无符号值: 0~255	最小的整数	1字节
SMALLINT	符号值：-32768~32767 无符号值: 0~65535	小型整数	2字节
MEDIUMINT	符号值：-8388608~8388607 无符号值: 0~16777215	中型整数	3字节
INT,INTEGER	符号值：-2147683648~2147683647 无符号值: 0~4294967295	标准整数	4字节
BIGINT	符号值：-9223372036854775808~9223372036854775807 无符号值: 0~18446744073709551615	大整数	8字节

2.1.1.1 ZEROFILL

对于整数类型，MySQL还支持在类型名称后面的小括号内指定显示宽度，例如：int(5)表示当数据宽度小于5位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，即用“0”填充的意思。

• 数值小于宽度限制时，zerofill用法

mysql> create table t1(id1 int,id2 int(5));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------------+-------+
| id1        | id2   |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)

mysql> 

• 当数值大于宽度限制时，不会对插入数据造成影响

mysql> insert into t1 values(2,2222222);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+------------+---------+
| id1        | id2     |
+------------+---------+
| 0000000001 |   00001 |
| 0000000002 | 2222222 |
+------------+---------+
2 rows in set (0.00 sec)

mysql> 

2.1.1.2 UNSIGNED

所有的整数类型都有一个可选属性UNSIGNED（无符号），如果需要在字段里面报错非负数或者需要较大的上限值时，可以使用此选项，它的取值范围是正常值的下限取0，上限取原值的2倍。

例如，tinyint有符号范围是-128_{+127，而无符号范围是0}255。如果一个列指定为zerofill，则MySQL自动为该列添加UNSIGNED属性。

mysql> desc t1;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| id2   | int(5) unsigned zerofill  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t1 add id3 int(7) unsigned;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| id2   | int(5) unsigned zerofill  | YES  |     | NULL    |       |
| id3   | int(7) unsigned           | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

2.1.1.3 AUTO_INCREMENT

整数类型还有一个属性AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可以利用此属性。此属性只能用于整型类型。

• AUTO_INCREMENT值一般从1开始，每行增加1。
• 在插入NULL到一个AUTO_INCREMENT列时，MySQL插入体格比该列中当前最大值大1的值。
• 一个表最多只能有一个AUTO_INCREMENT列。
• 对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。

create table a1(id int auto_increment not null,primary key);
create table a1(id int auto_increment not null,primary key(id));
create table a1(id int auto_increment not null,unique(id);

2.1.2 浮点

数据类型	取值范围	说明	单位
FLOAT	+(-)3.402823466E+38	单精度浮点数	8或4字节
DOUBLE	+(-)1.7976931348623157E+308 +(-)2.2250738585072014E+308	双精度浮点数	8字节
DEC(M,D) DECIMAL(M,D)	最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定	一般整数	M+2

对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float（单精度）和double（双精度），而定点数则只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

2.1.2.1 (M,D)

浮点数和定点数都可以用类型名称后加(M,D)的方式来表示，表示该值一共显示M位数字（整数位+小数位），其中D位位于小数点后面，M和D又称为精度和标度。

例如：定义为float(7,4)的一个列表可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在float(7,4)列内插入999.00009，近似结果是999.0001。

值得注意的是，浮点数后面跟(M,D)的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。float和double在不指定精时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。

2.1.2.2 浮点数与定点数

• 浮点数：一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入的数据精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入过程不会报错。MySQL中float，double都是浮点数。
• 定点数：以字符串形式存放，可以更精确的保存数据。如果实际插入的数值精度大于实际定义的精度，则MySQL会进行告警（默认的SQLMode下），但数据按照实际精度四舍五入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。MySQL中decimal（或numberic）表示定点数。

用法建议：

1. 浮点数存在误差。
2. 对货币等对精度敏感的数据，应该用定点数表示或存储。
3. 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较。
4. 要注意浮点数中一些特殊值的处理。

2.1.3 位类型

数据类型	取值范围	说明	单位
BIT(M)	符号值：-127~127 无符号值: 0~255 BIT(1)~BIT(64)	最小的整数	1字节~8字节

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

mysql> create table t2 (id bit(8));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(8) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t2 values(128);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;
+------+
| id   |
+------+
| �     |
+------+
1 row in set (0.00 sec)

mysql> select bin(id) from t2;
+----------+
| bin(id)  |
+----------+
| 10000000 |
+----------+
1 row in set (0.01 sec)

mysql> 

如果存储的数据超过限制，则插入数据会提示报错。如，id为bit(8)，8位的int最大为255，写入256会提示错误。

mysql> insert into t2 value(256);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> 
mysql> insert into t2 value(255);
Query OK, 1 row affected (0.02 sec)

mysql> alter table t2 modify id bit(16);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 value(256);
Query OK, 1 row affected (0.02 sec)

mysql> 

2.2 字符串类型

字符串类型可以分为3类：普通的文本字符串类型（CHAR和VARCHAR）、可变类型（TEXT和BLOB）和特殊类型（SET和ENUM）。

创建表时，使用字符串类型时应遵循以下原则。

• （1）从速度方面考虑，要选择固定的列，可以使用CHAR类型。
• （2）要节省空间，使用动态的列，可以使用VARCHAR类型。
• （3）要将列中的内容限制在一种选择，可以使用ENUM类型。
• （4）允许在一个列中有多于一个的条目，可以使用SET类型。
• （5）如果要搜索的内容不区分大小写，可以使用TEXT类型。
• （6）如果要搜索的内容区分大小写，可以使用BLOB类型。

2.2.1 普通的文本字符串类型

即CHAR和VARCHAR类型，CHAR列的长度被固定为创建表所声明的长度，取值在1～255之间；VARCHAR列的值是变长的字符串，取值和CHAR一样

类型	取值范围	说明
[national]char(M)[binary|ASCII|unicode]	0~255个字符	固定长度为M的字符串，其中M的取值范围为0~255。national关键字指定了应该使用的默认字符集。binary关键字指定了数据是否区分大消息（默认是区分大小写的）ASCII关键字指定了在该列中使用latin1字符集。unicode关键字指定了使用UCS字符集
char	0~255个字符	char(M)类似
[national]varchar(M)[binary]	0~255个字符(5.0.3之前) 0~65535个字符(5.0.3以后)	M为0~65535之间的整数，值的长度+1个字节。其他和char(M)类似

• 在检索时，CHAR列会删除尾部的空格，而VARCHAR则保留这些空格。

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

mysql> insert into vc values('ab  ','ab  ');
Query OK, 1 row affected (0.01 sec)

mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.01 sec)

# 给两个字段分别追加一个“+”字符看得更清楚
mysql> 
mysql> select concat(v,'+'),concat(c,'+') from vc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
1 row in set (0.01 sec)

mysql> 

• 不同的存储引擎对CHAR和VARCHAR的使用原则是不同的。

存储引擎	使用建议
MyISAM存储引擎	建议使用固定长度的数据列替代可变长度的数据列
MEMORY存储引擎	目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都作为CHAR类型处理
InnoDB存储引擎	建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此本质上，使用固定长度的CHAR列不一定笔记使用可变长度VARCHAR列性能要好。因此，主要的性能呢因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR俩最小化需要处理的数据行的存储总量和磁盘I/O是比较好的

2.2.2 可变类型

TEXT和BLOB，它们的大小可以改变，TEXT类型适合存储长文本，而BLOB类型适合存储二进制数据，支持任何数据，如文本、声音和图像等。

类型	最大长度（字节数）	说明
TINYBLOB	2^8-1(255)	小BLOB字段，允许长度0~255字节，值的长度+1个字节
BLOB	2^16-1(65535)	常规BLOB字段，允许长度0~65535字节，值的长度+2个字节
MEDIUMBLOB	2^24-1(16777215)	中型BLOB字段，允许长度0~167772150字节，值的长度+3个字节
LONGBLOB	2^32-1(4294967295)	长BLOB字段，允许长度0~4294967295字节，值的长度+4个字节
TINYTEXT	2^8-1(255)	小TEXT字段，允许长度0~255字节，值的长度+2个字节
TEXT	2^16-1(65535)	常规TEXT字段，允许长度0~65535字节，值的长度+2个字节
MEDIUMTET	2^24-1(16777215)	中型TEXT字段，允许长度0~167772150字节，值的长度+3个字节
LONGTEXT	2^32-1(4294967295)	长TEXT字段，允许长度0~4294967295字节，值的长度+4个字节
VARBINARY(M)	M	允许长度0~M个字节的变长字节字符串，值的长度+1个字节
BINARY(M)	M	允许长度0~M个字节的定长字节字符串

2.2.2.1 BINARY和VARBINARY

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

mysql> create table t (c binary(3));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t set c = 'a';
Query OK, 1 row affected (0.00 sec)

# 分别用以下几种模式来查看c列内容
mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;
+------+--------+-------+---------+-----------+
| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a    | 610000 |     0 |       0 |         1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

mysql> 

当保存BINARY值时，在值的最后通过填充“0x00”(零字节)以达到指定的字段定义长度。

2.2.2.2 TEXT和BLOB

在保存较大文本时，通常会选择使用TEXT或BLOB。二者之间主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一瓶文章。

• BLOB和TEXT值会引起一些性能问题，特比是在执行了大量的删除操作时。
  删除操作会在数据表中留下很大的空洞，以后填入这些空洞的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理。
• 可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。
  合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是合成索引只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作是没有用处的）
• 在不必要的时候避免检索大型的BLOB或TEXT值。
• 把BLOB或TEXT列分离到单独的表中。

2.2.3 特殊类型

SET和ENUM

类型	最大值	说明
Enum(“value1”,“value2”,…)	65535	该类型的列只可以容纳所列值之一或为NULL
Set(“value1”,“value2”,…)	64	该类型的列可以容纳一组值或为NULL

2.2.3.1 ENUM

ENUM，即枚举类型，它的值范围需要在创建表时通过枚举方式显示指定，对1_{255个成员的枚举需要1个字节存储;对于255}65535个成员，需要2个字节存储。最多允许有65535个成员。

mysql> create table t11 (gender enum('M','F'));
Query OK, 0 rows affected (0.04 sec)

# 插入4条不同的记录
mysql> insert into t11 values('M'),('1'),('f'),(NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t11;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

mysql> 

• ENUM类型忽略大小写，'M’和’f’都在存储时转为大写。
• 插入不在ENUM范围的值时，不提示错误，而使用ENUM的第一个值’M’代替。
• ENUM类型只允许从值集中选取单个值，而不能一次取多个值。

2.2.3.2 SET

SET和ENUM类型非常相似，也是一个字符串对象，里面可以包含0~64个成员。根据成员的不同，存储上也各不相同：

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

SET与ENUM除了存储外，最大的区别在于SET类型一次可以选取多个成员。

mysql> create table t22 (col set('a','b','c','d'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t22 values('a,b'),('a,d,a'),('a,b'),('a,c'),('a,f');
ERROR 1265 (01000): Data truncated for column 'col' at row 5
mysql> 
mysql> insert into t22 values('a,b'),('a,d,a'),('a,b'),('a,c');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t22
    -> ;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a,b  |
| a,c  |
+------+
4 rows in set (0.00 sec)

mysql> 

• SET类型要求选择的值必须在允许值范围内，否则提示错误。例如a,f，f不在值集中。
• SET类型选择的值，如果出现多次，则相同的值只会出现一次。例如a,d,a，a出现了2次，只会保存一个。

2.3 日期和时间类型

日期和时间类型包括：DATETIME、DATE、TIMESTAMP、TIME和YEAR。其中的每种类型都有其取值的范围，如赋予它一个不合法的值，将会被“0”代替。

类型	占用控件	取值范围	说明	零值表示
DATE	4字节(bytes)	1000-01-01 9999-12-31	日期，格式YYYY-MM-DD	0000-00-00
TIME	3字节	-838:58:59 835:59:59	时间值或持续时间，格式HH:MM:SS	00:00:00
DATETIME	8字节	1000-01-01 00:00:00 9999-12-31 23:59:59	混合日期和时间值，格式YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4字节	UTC’1970-01-01 00:00:00’/2038结束时间是第2147483647秒，北京时间2038-1-19 11:14:07，格林尼治时间2038年1月19日凌晨03:14:07	混合日期和时间值，时间戳	00000000000000
YEAR	1字节	1901~2155	年份可指定两位数和四位数的格式，格式YYYY	0000

每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下会报错，并以零值（见上表）存储。

插入或更新时，日期时间类型允许“不严格”语法，以DATETIME为例（其他日期时间类型雷同）:

• YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。任何符号都可以用作日期部分或时间部分的间隔符。
  例如：14-06-18 14:54:10、14*06*18 14.54.10、14+06+18 14=54=10是等价的。对于包含日期时间的字符串值，如果月、日、时、分、秒的值小于10，不需要指定两位数。
  例如：2014-2-3 2:3:6、2014-02-03 02:03:06是等价的。

• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的字符串。如果字符串对于日期时间类型是合法的就可以解释为日期时间类型。
  例如：20140618145410 和 140618145410将被解释为2014-06-18 14:54:10，但是 20140618145480是不合法的（秒数不合法），将被解释为0000-00-00 00:00:00。

• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字。如果该数字对日期时间类型是合法的就可以解释为日期时间类型。
  例如：20140618145410和140618145410将被解释为2014-06-18 14:54:10 。数值的长度应为6、8、12、14。如果数值长度是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS 格式。如果数值为6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式。

2.3.1 DATETIME

DATETIME用于表示年月日时分秒，是DATE和TIME的组合，并且记录的年份（见上表）比较长久。如果实际应用中有这样的需求，就可以使用DATETIME类型。

DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59的时间。

2.3.2 TIMESTAMP

TIMESTAMP用于表示 年月日 时分秒，但是记录的年份（见上表）比较短暂。

TIMESTAMP和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。

表中的第一个TIMESTAMP列自动设置为系统时间CURRENT_TIMESTAMP。当插入或更新一行，但没有明确给TIMESTAMP列赋值，也会自动设置为当前系统时间。如果表中有第二个TIMESTAMP列，则默认值设置为0000-00-00 00:00:00，而且不能强制修改默认值为CURRENT_TIMESTAMP。

TIMESTAMP的属性受Mysql版本和服务器SQLMode的影响较大。如果记录的日期需要让不同时区的人使用，最好使用TIMESTAMP。

如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。TIMESTAMP值返回后显示为YYYY-MM-DD HH:MM:SS格式的字符串，显示宽度为19字符。如果想要获得数字值，应在TIMESTAMP列添加+0。

• 创建测试表

create table t3 (
	id1 timestamp not null default current_timestamp,
	id2 datetime default null
)

• 查看当前时区

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

mysql> 

时区值为SYSTEM，表示与主机的时区值一致，即东八区。

• 写入当前时间

mysql> insert into t3 values(now(),null);
mysql> select * from t3;
+---------------------+------+
| id1                 | id2  |
+---------------------+------+
| 2022-07-07 10:42:22 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

mysql> 

• 修改时区为东九区，再次查看表中数据

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t3;
+---------------------+------+
| id1                 | id2  |
+---------------------+------+
| 2022-07-07 11:42:22 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

mysql> 

• 恢复时区

mysql> set time_zone='SYSTEM';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t3;
+---------------------+------+
| id1                 | id2  |
+---------------------+------+
| 2022-07-07 10:42:22 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

mysql> 

TIMESTAMP的取值范围是19700101080001到2038年的某一天，因此它不合适存放比较久远的日期。

2.3.3 DATE

DATE 用于表示 年月日，如果实际应用值需要保存 年月日 就可以使用 DATE。

2.3.4 TIME

TIME 用于表示 时分秒，如果实际应用值需要保存 时分秒 就可以使用 TIME。

2.3.5 YEAR

YEAR 用于表示年份，YEAR 有2位（最好使用4位）和4位格式的年。默认是4位。如果实际应用只保存年份，那么用1 bytes保存YEAR类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

在4位格式中，允许的值是1901_{2155和0000。在2位格式中，允许的值是70}69，表示从1970~2069年。

2.3.6 TIMESTAMP和DATETIME比较

• 相同点：
  两者都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期。
• 不同点：
  • 他们的的存储方式，大小(字节)，表示的范围不同。
  • TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
  • DATETIME，不做任何改变，基本上是原样输入和输出。

总结：TIMESTAMP和DATETIME 都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期， 除了存储方式和存储范围以及大小不一样，没有太大区别。但对于跨时区的业务，TIMESTAMP更为合适。

2.3.7 日期和时间相关函数

处理日期和时间字段的函数有很多，有的经常会在查询中使用到，下面介绍下几个相关函数的使用方法。

• CURDATE和CURRENT_DATE两个函数作用相同，返回当前系统的日期值。
• CURTIME和CURRENT_TIME两个函数作用相同，返回当前系统的时间值。
• NOW()和SYSDATE()两个函数作用相同，返回当前系统的日期和时间值。
• UNIX_TIMESTAMP获取UNIX时间戳函数，返回一个以UNIX时间戳为基础的无符号整数。
• FROM_UNIXTIME将UNIX时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数。
• TO_DAYS()提取日期值并返回自公元0年到现在的天数。
• DAY()获取指定日期或时间中的天值。
• DATE()获取指定日期或时间中的日期。
• TIME()获取指定日期或时间中的时间。
• MONTH获取指定日期中的月份。
• WEEK获取指定日期是一年中的第几周。
• YEAR获取年份。
• QUARTER获取日期所在的季度值。
• DATE_ADD和ADDDATE两个函数功能相同，都是向日期添加指定的时间间隔。
• DATE_SUB和SUBDATE两个函数功能相同，都是向日期减去指定的时间间隔。
• ADDTIME时间加法运算，在原始时间上添加指定的时间。
• SUBTIME时间减法运算，在原始时间上减去指定的时间。
• DATEDIFF获取两个日期之间间隔，返回参数 1 减去参数 2 的值。
• DATE_FORMAT格式化指定的日期，根据参数返回指定格式的值。

示例：

CREATE TABLE `sys_test` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `create_by` varchar(32) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  -- `create_date` date  DEFAULT CURRENT_DATE COMMENT '创建日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='测试表';

• 在5.5到5.6.4版本里，对于DEFAULT CURRENT_TIMESTAMP子句，只能TIMESTAMP类型列上指定。
• 而从5.6.5开始以后的版本，对于DEFAULT CURRENT_TIMESTAMP子句可以指定到TIMESTAMP或者DATETIME类型列上。
• InnoDB引擎中date列中无法使用DEFAULT CURRENT_DATE子句。原因是date类型的default value 一定要是一个常量，不能是一个函数或者是表达式。所以不能给MySQL的date类型的列设置默认值。

2.3.7 时间与时间戳之间转换

有些应用生成的时间戳是比这个多出三位，是毫秒表示，如果要转换，需要先将最后三位去掉（标准的10位数字，如果是13位的话可以以除以1000的方式），否则返回NULL

#将时间转换为时间戳unix_timestamp
SELECT UNIX_TIMESTAMP('2019-02-22 13:25:07'); #1550813107
 
#将时间戳转换为时间from_unixtime
SELECT FROM_UNIXTIME(1550813107); #2019-02-22 13:25:07
 
#NOW
SELECT UNIX_TIMESTAMP(NOW()); #1550813420
SELECT FROM_UNIXTIME(1550813420); #2019-02-22 13:30:20

参考MySQL的10位或13位时间戳获取，表示及13位时间戳的存储

2.3.8 按时间日期查询

参考MySQL DATE_FORMAT() 函数

#MySQL 按时间日期查询
DROP TABLE IF EXISTS tb_test;
CREATE TABLE IF NOT EXISTS tb_test(
  id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  name VARCHAR(100) DEFAULT NULL COMMENT '名称',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT '时间测试表';
 
#插入数据
INSERT INTO tb_test (name) VALUES
('luo'),
('lei'),
('luolei'),
('xing'),
('dxx');
 
#修改数据
UPDATE tb_test SET name='luo1', update_time='2021-01-23' WHERE id=100;
UPDATE tb_test SET name='luo2', update_time='2022-02-23' WHERE id=101;
UPDATE tb_test SET name='luo3', update_time='2023-03-23' WHERE id=102;
 
#根据年月日查数据
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m-%d')='2019-02-22';
#根据年月查数据
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m')='2021-01';
#根据年查数据
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y')='2022';
 
#根据日期区间查询数据,并排序 
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y') BETWEEN '2019' AND '2023' ORDER BY update_time ASC;