选择正确的数据类型对于获得高性能至关重要 , 三大原则 :
更小的通常更好 , 尽量使用可正确存储数据的最小数据类型
简单就好 , 简单数据类型的操作通常需要更少的
CPU周期尽量避免
NULL, 包含为NULL的列 , 对MySQL更难优化
1. 整数型
tinyint(m): 1字节, 范围(-128~127)smallint(m): 2个字节, 范围(-32768~32767)mediumint(m): 3个字节, 范围(-8388608~8388607)int(m): 4个字节,范围(-2147483648~2147483647)bigint(m): 8个字节, 范围(+-9.22*10^{18} )
上述数据类型, 如果加修饰符unsigned后,则最大值翻倍
如: tinyint unsigned的取值范围为(0~255) int(m)里的m是表示SELECT查询结果集中的显示宽度 , 并不影响实际的取值范围 , 规定了MySQL的一些交互工具(例如MySQL命令行客户端) 用来显示字符的个数。对于存储和计算来说 , Int(1)和Int(20)是相同的
BOOL , BOOLEAN : 布尔型 , 是TINYINT(1)的同义词。zero值被视为假 , 非zero值视为真
2. 浮点型(float和double),近似值
float(m,d): 单精度浮点型 ,8位精度(4字节)。m总个数 ,d小数位double(m,d): 双精度浮点型,16位精度(8字节)。m总个数 ,d小数位
设一个字段定义为float(6,3) 。如果插入一个数123.45678 ,实际数据库里存的是123.457 , 但总个数还以实际为准 , 即6位。
3. 定点数
在数据库中存放的是精确值,存为十进制。decimal(m,d) , 参数m<65 是总个数 , d<30且d<m是小数位。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。
为了存储精确的数值 , 我们需要为 DECIMAL 数据类型指定总位数和小数位数。这里是 DECIMAL 数据类型的语法 :
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]这里 :
M是总的位数 , 不包含小数点和正负号。D是小数部分的位数。如果D为0则表示没有小数部分。当D省略时 , 默认值为0。UNSIGNED属性表示数值是无符号的。无符号的数字不能是负数。ZEROFILL属性表示当整数部分位数不足时 , 用整数的左侧用0填充。带有ZEROFILL的列将自动具有UNSIGNED属性。这和 [、INT 数据类型一样。
DEC,NUMERIC,FIXED是DECIMAL的同义词。
比如 , 我们定义了如下一个列 :
amount DECIMAL(9, 4);那么 amount 列的值的范围是从 -99999.9999 到 99999.9999。
比如 , 我们定义了如下一个列 :
amount DECIMAL(9);那么 amount 列的值的范围是从 -999999999 到 999999999。
例如 : decimal(18,9)小数点两边将各存储9个数字 , 一共使用9个字节 , 其中小数点前的9个数字用4个字节 , 小数点后的9个数字用4个字节 , 小数点本身占1个字节。浮点类型在存储同样范围的值时 , 通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节。因为需要额外的空间和计算开销 , 所以应该尽量只在对小数进行精确计算时才使用decimal , 例如存储财务数据。但在数据量比较大的时候 , 可以考虑使用bigint代替decimal。
4. 字符串(char,varchar,text)
char(n): 固定长度 , 最多255个字符varchar(n): 可变长度 , 最多65535个字符tinytext: 可变长度 , 最多255个字符text: 可变长度 , 最多65535个字符mediumtext: 可变长度 ,最多2*10^{24}-1个字符longtext: 可变长度 , 最多2*10^{32}-1个字符BINARY(M): 固定长度 , 可存二进制或字符 , 长度为0-M字节VARBINARY(M): 可变长度 , 可存二进制或字符 , 允许长度为0-M字节内建类型 :
ENUM枚举,SET集合。
char和varchar
char(n)若存入字符数小于n , 则以空格补于其后 , 查询之时再将空格去掉 , 所以char类型存储的字符串末尾不能有空格 ,varchar不限于此char(n)固定长度 ,char(4)不管是存入几个字符 , 都将占用4个字节 ,varchar是存入的实际字符数+1个 字节(n< n>255) , 所以varchar(4),存入3个字符将占用4个字节char类型的字符串检索速度要比varchar类型的快
varchar和text :
varchar可指定n,text不能指定 , 内部存储varchar是存入的实际字符数+1个字节(n< n>255) ,text是实际字符数+2个字节。text类型不能有默认值varchar可直接创建索引 ,text创建索引要指定前多少个字符。varchar查询速度快于text数据类型
text
text类型同char、varchar类似 , 都可用于存储字符串 , 一般情况下 , 遇到存储长文本字符串的需求时可以考虑使用text类型。按照可存储大小区分 , text类型同样可分为以下四种:
不过在日常场景中 , 存储字符串还是尽量用varchar , 只有要存储长文本数据时 , 可以使用text类型。对比 varchar , text 类型有以下特点 :
text类型无须指定长度。若数据库未启用严格的
sqlmode, 当插入的值超过text列的最大长度时 , 则该值会被截断插入并生成警告。text类型字段不能有默认值。varchar可直接创建索引 ,text字段创建索引要指定前多少个字符。text类型检索效率比varchar要低。
5. 二进制数据
5.1 BLOB
blob(binary large object) 是一个可以存储二进制文件的容器 , 主要用于存储二进制大对象 , 例如可以存储图片 , 音视频等文件。
其中最常用的就是blob字段类型了 , 最多可存储 65KB 大小的数据 , 一般可用于存储图标或logo图片。不过数据库并不适合直接存储图片 , 如果有大量存储图片的需求 , 请使用对象存储或文件存储 , 数据库中可以存储图片路径来调用。
BLOB和text存储方式不同 : TEXT以文本方式存储 ,英文存储区分大小写 , 而Blob以二进制方式存储 , 不分大小写。
BLOB存储的数据只能整体读出。
TEXT可以指定字符集 , BLOB不用指定字符集。
5.2 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) 类型的字段中的。
提示 : 默认情况下 , MySQL 不可以插入超出该列允许范围的值 , 因而插入数据时要确保插入的值在指定的范围内。
6. 日期时间类型
date: 日期'2008-12-2'time: 时间'12:25:36'datetime: 日期时间'2008-12-2 22:06:44'timestamp: 自动存储记录修改时间YEAR(2),YEAR(4): 年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新 , 这个数据类型的字段可以存放这条记 录最后被修改的时间
6.1 DATA
6.1.1 DATA语法
以下是创建 DATE 列的语法 :
column_name DATE;这里 , 我们无需为 DATE 指定任何属性。
6.1.2 日期实例
可以将 yyyy-mm-dd 或者 yy-mm-dd 格式的日期值插入到 DATE 列中。
如果您使用两位数的年份值 , MySQL 仍然按照以下规则将它转为四位数的年份值 :
00-69范围内的年份值将转换为2000-2069。70-99范围内的年份值将转换为1970-1999。
虽然规则时明确的 , 但是 , 具有两位数的日期值会给读取您代码的人带来困惑 , 因此应该避免使用两位数的年份。
让我们看一下下面的例子。
首先 , 创建一个表 test_date , 它包含 DATE 数据类型的列 created_date。
CREATE TABLE test_date (
id INT AUTO_INCREMENT PRIMARY KEY,
created_date DATE NOT NULL
);接下来 , 在 test_date 表中插入一行 。
INSERT INTO test_date(created_date)
VALUES ('2008-08-08');然后 , 从 test_date 表中查询数据。
SELECT * FROM test_date;+----+--------------+
| id | created_date |
+----+--------------+
| 1 | 2008-08-08 |
+----+--------------+之后 , 使用两位数的年份格式将数据插入 people 表中。
INSERT INTO test_date(created_date)
VALUES ('09-09-09'), ('99-09-09');这里 , 根据转换规则 , 年份 09 将被转换为 2009 , 99 将被转换为 1999。
最后 , 我们可以从 test_date 表中查询数据 , 以检查数据是否根据转换规则进行了转换。
SELECT * FROM test_date;+----+--------------+
| id | created_date |
+----+--------------+
| 1 | 2008-08-08 |
| 2 | 2009-09-09 |
| 3 | 1999-09-09 |
+----+--------------+让我们再尝试一个插入错误日期的语句 :
INSERT INTO test_date(created_date)
VALUES ('2008-02-30');这里MySQL会返回错误 : ERROR 1292 (22007): Incorrect date value: ‘2008-02-30’ for column ‘created_date’ at row 1。
6.1.3 DATA函数
MySQL 提供了许多有用的日期函数 , 允许您有效地操作日期。以下列出了常用的日期函数 :
NOW(): 获取当前日期和时间CURDATE(): 获取当前日期DATE(): 获取日期部分DATE_FORMAT(): 格式化输出日期DATEDIFF(): 计算两个日期之间的天数DATE_ADD(): 在给定日期上增加给定的时间间隔DATE_SUB(): 在给定日期上减少给定的时间间隔DAY(): 返回日期中天MONTH(): 返回月份QUARTER(): 返回季节YEAR(): 返回年份WEEK(): 函数返回给定日期是一年周的第几周WEEKDAY(): 函数返回工作日索引WEEKOFYEAR(): 函数返回日历周
6.1.3.1 NOW()
NOW() 函数用来获取当前日期和时间。请看下面的语句 :
SELECT NOW();+---------------------+
| NOW() |
+---------------------+
| 2022-03-25 06:47:58 |
+---------------------+6.1.3.2 CURDATE()
CURDATE() 用来获取获取当前系统日期。请看下面的语句 :
SELECT CURDATE();+------------+
| CURDATE() |
+------------+
| 2022-03-25 |
+------------+6.1.3.3 DATE()
DATE() 函数用来返回一个日期或者日期时间值的日期部分。请看下面的语句 :
SELECT DATE(NOW());+-------------+
| DATE(NOW()) |
+-------------+
| 2022-03-25 |
+-------------+6.1.3.4 DATE_FORMAT()
要格式化日期值 , 请使用 DATE_FORMAT() 函数。以下语句使用日期格式模式 %m/%d/%Y 格式化当前日期 :
SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;+------------+
| today |
+------------+
| 03/25/2022 |
+------------+6.1.3.5 DATEDIFF()
要计算两个日期值之间的天数 , 请 DATEDIFF() 按如下方式使用该函数 :
SELECT DATEDIFF('2021-01-01','2022-01-01') days;+------+
| days |
+------+
| -365 |
+------+这里 , 如果第一个参数早于第二个参数返回为负数 , 否则返回为正数或者 0。
6.1.3.6 DATE_ADD()
DATE_ADD() 函数用于将天数、周数、月数、年数等添加到给定的日期值 :
SELECT CURDATE() `今天`,
DATE_ADD(CURDATE(), INTERVAL 1 DAY) '一天后',
DATE_ADD(CURDATE(), INTERVAL 1 WEEK) '一周后',
DATE_ADD(CURDATE(), INTERVAL 1 MONTH) '一月后',
DATE_ADD(CURDATE(), INTERVAL 1 YEAR) '一年后';+------------+------------+------------+------------+------------+
| 今天 | 一天后 | 一周后 | 一月后 | 一年后 |
+------------+------------+------------+------------+------------+
| 2022-03-25 | 2022-03-26 | 2022-04-01 | 2022-04-25 | 2023-03-25 |
+------------+------------+------------+------------+------------+6.1.3.7 DATE_SUB()
同样 , DATE_SUB() 被用于从日期中减去一个时间间隔 :
SELECT CURDATE() `今天`,
DATE_SUB(CURDATE(), INTERVAL 1 DAY) '一天前',
DATE_SUB(CURDATE(), INTERVAL 1 WEEK) '一周前',
DATE_SUB(CURDATE(), INTERVAL 1 MONTH) '一月前',
DATE_SUB(CURDATE(), INTERVAL 1 YEAR) '一年前';+------------+------------+------------+------------+------------+
| 今天 | 一天前 | 一周前 | 一月前 | 一年前 |
+------------+------------+------------+------------+------------+
| 2022-03-25 | 2022-03-24 | 2022-03-18 | 2022-02-25 | 2021-03-25 |
+------------+------------+------------+------------+------------+6.1.3.8 DAY, MONTH, QUARTER, YEAR
如果你想获得的日 , 月 , 季度和日期值的年份 , 您可以使用相应 DAY() , MONTH() , QUARTER() , 和 YEAR() 函数 如下 :
SELECT DAY(CURDATE()) `day`,
MONTH(CURDATE()) `month`,
QUARTER(CURDATE()) `quarter`,
YEAR(CURDATE()) `year`;+------+-------+---------+------+
| day | month | quarter | year |
+------+-------+---------+------+
| 25 | 3 | 1 | 2022 |
+------+-------+---------+------+6.1.3.9 WEEK, WEEKDAY, WEEKOFYEAR
WEEK 函数返回周数 , WEEKDAY 函数返回工作日索引 , WEEKOFYEAR 函数返回日历周。
SELECT WEEKDAY(CURDATE()) `weekday`,
WEEK(CURDATE()) `week`,
WEEKOFYEAR(CURDATE()) `weekofyear`;+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
| 4 | 12 | 12 |
+---------+------+------------+6.2 DATETIME
在 MySQL 中 , 您使用 DATETIME 来存储包含日期和时间的值。当您从 DATETIME 列中查询数据时 , MySQL DATETIME 列的值以下格式显示 :
YYYY-MM-DD HH:MM:SS默认情况下 , DATETIME 值范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59 。
一个 DATETIME 值使用5个字节进行存储。此外 , 一个 DATETIME 值可以包括一个尾随小数秒 , 格式为 : YYYY-MM-DD HH:MM:SS[.fraction]。 例如 , 2015-12-20 10:01:00.999999 。当包括小数秒精度时 , DATETIME 值需要更多的存储空间 , 如下表所示:
例如 , 2015-12-20 10:01:00.999999 需要 8 个字节 , 5 个字节用于 2015-12-20 10:01:00 和 3 个字节用于 .999999。 而 2015-12-20 10:01:00.9 只需要 6 个字节 , 1 个字节用于小数秒精度。
请注意 , 在 MySQL 5.6.4 之前 , DATETIME 值需要 8 个字节的存储空间 , 而不是 5 个字节。
6.2.1 MySQL 日期时间与时间戳
MySQL 提供了另一种类似于 DATETIME 的 时间数据类型 TIMESTAMP 。
TIMESTAMP 需要 4 个字节 , 而 DATETIME 需要 5 个字节。TIMESTAMP 和 DATETIME 二者都需要额外字节存储小数秒。
TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 。如果要存储超过 2038 年的时间值 , 则应使用 DATETIME 代替 TIMESTAMP 。
MySQL TIMESTAMP 以 UTC 值存储。但是 , MySQL 将 DATETIME 值按原样存储 , 没有时区。让我们看看下面的例子。
首先 , 将当前连接的时区设置为 +00:00 。
SET time_zone = '+00:00';接下来 , 使用以下语句创建一个表名为 timestamp_n_datetime , 该表由两列组成 : ts 和 dt 分别是 TIMESTAMP 和 DATETIME 数据类型。
CREATE TABLE timestamp_n_datetime (
id INT AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP,
dt DATETIME
);然后 , 将当前日期和时间插入 timestamp_n_datetime 表的 ts 和 dt 列中 ,
INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());之后 , 从 timestamp_n_datetime 表中查询数据。
SELECT ts, dt
FROM timestamp_n_datetime;+---------------------+---------------------+
| ts | dt |
+---------------------+---------------------+
| 2022-03-28 02:52:15 | 2022-03-28 02:52:15 |
+---------------------+---------------------+DATETIME 和 TIMESTAMP 列中的值都相同。
最后 , 将连接的时区设置为 +03:00 并 timestamp_n_datetime 再次从表中查询数据。
SET time_zone = '+03:00';
SELECT ts, dt
FROM timestamp_n_datetime;+---------------------+---------------------+
| ts | dt |
+---------------------+---------------------+
| 2022-03-28 05:52:15 | 2022-03-28 02:52:15 |
+---------------------+---------------------+如您所见 , TIMESTAMP 列中的值不同。这是因为 TIMESTAMP 当我们更改时区时 , TIMESTAMP 列以 UTC 存储日期和时间值 , 该列的值会根据新时区进行调整。
这意味着如果您使用 TIMESTAMP 数据来存储日期和时间值 , 则在将数据库移动到位于不同时区的服务器时应该认真考虑。
6.2.2 日期时间函数
以下语句使用 NOW()函数将变量 @dt 设置为当前日期和时间。
SET @dt = NOW();要查询 @dt 变量的值 , 请使用以下 SELECT 语句 :
SELECT @dt;+---------------------+
| @dt |
+---------------------+
| 2022-03-28 05:56:27 |
+---------------------+6.2.2.1 MySQL 日期函数
要从 DATETIME 值中提取日期部分 , 请按如下方式使用 DATE 函数 :
SELECT DATE(@dt);+------------+
| DATE(@dt) |
+------------+
| 2022-03-28 |
+------------+如果您想基于日期查询数据但列中存储的数据基于日期和时间 , 则此函数非常有用。
让我们看看下面的例子。
CREATE TABLE test_datetime (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME
);
INSERT INTO test_datetime(created_at)
VALUES('2015-11-05 14:29:36');假设您想知道在 2015-11-05 上创建了哪一行。如果您使用以下查询 :
SELECT * FROM test_datetime
WHERE created_at = '2015-11-05';它不返回任何行。
这是因为 created_at 列不仅包含日期 , 还包含时间。要纠正它 , 请按如下方式使用 DATE 函数 :
SELECT * FROM test_datetime
WHERE DATE(created_at) = '2015-11-05';+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2015-11-05 14:29:36 |
+----+---------------------+它按预期返回一行。如果表中有很多行 , MySQL 必须执行全表扫描以定位符合条件的行。
6.2.2.2 MySQL 时间函数
要从 DATETIME 值中提取时间部分 , 请使用 TIME 函数作为以下语句 :
SELECT TIME(@dt);+-----------+
| TIME(@dt) |
+-----------+
| 05:56:27 |
+-----------+6.2.2.3 MySQL YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE 和 SECOND 函数
要从 DATETIME 值中获取年、季度、月、周、日、小时、分钟和秒 , 请使用以下语句中所示的 YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE 和 SECOND函数 :
SELECT
YEAR(@dt),
QUARTER(@dt),
MONTH(@dt),
WEEK(@dt),
DAY(@dt),
HOUR(@dt),
MINUTE(@dt),
SECOND(@dt);+-----------+--------------+------------+-----------+----------+-----------+-------------+-------------+
| YEAR(@dt) | QUARTER(@dt) | MONTH(@dt) | WEEK(@dt) | DAY(@dt) | HOUR(@dt) | MINUTE(@dt) | SECOND(@dt) |
+-----------+--------------+------------+-----------+----------+-----------+-------------+-------------+
| 2022 | 1 | 3 | 13 | 28 | 5 | 56 | 27 |
+-----------+--------------+------------+-----------+----------+-----------+-------------+-------------+6.2.2.4 MySQL DATE_FORMAT 函数
要格式化 DATETIME 值 , 请使用 DATE_FORMAT 函数。例如 , 以下语句根据 %H:%i:%s - %W %M %Y 格式格式化 DATETIME 值 :
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');+-----------------------------------------+
| DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y') |
+-----------------------------------------+
| 05:56:27 - Monday March 2022 |
+-----------------------------------------+语法 :
DATE_FORMAT(date, format)date: 必需的。需要格式化的日期。format: 必需的。格式化模式字符串。
下表整理了 format 中可采用的格式化符号 :
6.2.2.5 MySQL DATE_ADD 函数
DATE_ADD() 函数在指定的日期/时间上加上指定到时间间隔加并返回新的日期/时间。要将间隔添加到 DATETIME 值 , 请按如下方式使用 DATE_ADD函数 :
语法 :
DATE_ADD(date, INTERVAL value unit)date: 必需的。需要操作的日期。value: 必需的。时间/日期间隔。正数和负数都是允许的。unit: 必需的。时间/日期间隔的单位。取以下值MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH
示例1
SELECT @dt `now`,
DATE_ADD(@dt, INTERVAL 1 SECOND) `1 second later`,
DATE_ADD(@dt, INTERVAL 1 MINUTE) `1 minute later`,
DATE_ADD(@dt, INTERVAL 1 HOUR) `1 hour later`,
DATE_ADD(@dt, INTERVAL 1 DAY) `1 day later`,
DATE_ADD(@dt, INTERVAL 1 WEEK) `1 week later`,
DATE_ADD(@dt, INTERVAL 1 MONTH) `1 month later`,
DATE_ADD(@dt, INTERVAL 1 YEAR) `1 year later`\G;*************************** 1. row ***************************
now: 2022-03-28 05:56:27
1 second later: 2022-03-28 05:56:28
1 minute later: 2022-03-28 05:57:27
1 hour later: 2022-03-28 06:56:27
1 day later: 2022-03-29 05:56:27
1 week later: 2022-04-04 05:56:27
1 month later: 2022-04-28 05:56:27
1 year later: 2023-03-28 05:56:27示例2
SELECT
DATE_ADD('2020-06-10', INTERVAL 10 DAY),
DATE_ADD('2020-06-10', INTERVAL -10 DAY)\G; DATE_ADD('2020-06-10', INTERVAL 10 DAY): 2020-06-20
DATE_ADD('2020-06-10', INTERVAL -10 DAY): 2020-05-316.2.2.6 MySQL DATE_SUB 函数
DATE_SUB() 函数在指定的日期/时间上减去指定到时间间隔加并返回新的日期/时间。要从一个 DATETIME 值中减去一个区间 , 请按如下方式使用 DATE_SUB 函数 :
语法 :
DATE_SUB(date, INTERVAL value unit)date:必需的。需要操作的日期。days: 必需的。在date上减去的天数。value: 必需的。时间/日期间隔。正数和负数都是允许的。unit: 必需的。时间/日期间隔的单位。取以下值MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH
示例1
SELECT @dt `now`,
DATE_SUB(@dt, INTERVAL 1 SECOND) `1 second before`,
DATE_SUB(@dt, INTERVAL 1 MINUTE) `1 minute before`,
DATE_SUB(@dt, INTERVAL 1 HOUR) `1 hour before`,
DATE_SUB(@dt, INTERVAL 1 DAY) `1 day before`,
DATE_SUB(@dt, INTERVAL 1 WEEK) `1 week before`,
DATE_SUB(@dt, INTERVAL 1 MONTH) `1 month before`,
DATE_SUB(@dt, INTERVAL 1 YEAR) `1 year before`\G;*************************** 1. row ***************************
now: 2022-03-28 05:56:27
1 second before: 2022-03-28 05:56:26
1 minute before: 2022-03-28 05:55:27
1 hour before: 2022-03-28 04:56:27
1 day before: 2022-03-27 05:56:27
1 week before: 2022-03-21 05:56:27
1 month before: 2022-02-28 05:56:27
1 year before: 2021-03-28 05:56:27示例2
SELECT
DATE_SUB('2020-06-10', INTERVAL 10 DAY),
DATE_SUB('2020-06-10', INTERVAL -10 DAY)\G; DATE_SUB('2020-06-10', INTERVAL 10 DAY): 2020-05-31
DATE_SUB('2020-06-10', INTERVAL -10 DAY): 2020-06-206.2.2.7 MySQL DATE_DIFF 函数
要计算两个 DATETIME 值之间的天数差异 , 请使用 DATEDIFF 函数。请注意 , DATEDIFF 函数在计算中仅考虑 DATETIME 值的日期部分。
SELECT DATEDIFF(@dt, '2022-01-01');+-----------------------------+
| DATEDIFF(@dt, '2022-01-01') |
+-----------------------------+
| 86 |
+-----------------------------+6.3 YEAR
在 MySQL 中 , YEAR 数据类型用来存储年份值。
YEAR 数据类型占用 1 个字节 , YEAR 值的范围为从 1901 到 2155, 还有 0000。
定义为 YEAR 数据类型的列可以接受多种输入格式 , 包括 :
4 位数字的年份值 , 从
1901to2155。4 位数字的年份值的字符串形式 , 从
'1901'到'2155'。2 位数字的年份值 , 从
0到99, 并按如下规则转换为 4 位数年份 :1到69转换为2001到2069。70到99转换为1970到1999。0转换为0000。
2 位数字的年份值的字符串形式 , 从
0到99, 并按如下规则转换为4位数年份:'0'到'69'转换为2000到2069。'70'到'99'转换为1970到1999。
若未启用严格 SQL 模式 , MySQL 会将无效的 YEAR 值转为 0000。在严格 SQL 模式下 , 插入无效的 YEAR 值时会产生错误。
6.3.1 year语法
这是 YEAR 数据类型的语法 :
column_name YEAR在 MySQL 8.0.19 之前的版本中 , YEAR 可以使用显示宽度属性 , 如 : YEAR(2)。不过 , 现在显示宽度属性已经不再被推荐使用。并且 MySQL 8 以后也不支持 YEAR(2) 了。请直接使用 YEAR。
6.3.2 year实例
首先 , 让我们创建表 test_year , 它有包含一个 YEAR 类型的列 created_year。
CREATE TABLE test_year(
id INT AUTO_INCREMENT PRIMARY KEY,
created_year YEAR NOT NULL
);接着 , 让我们使用 4 位数年份插入一行 :
INSERT INTO test_year (created_year)
VALUES (2022);然后 , 让我们使用 2 位数年份插入 2 行 :
INSERT INTO test_year (created_year)
VALUES (10), (98);让我们再插入一个 0 值 :
INSERT INTO test_year (created_year)
VALUES (0), ('0');最后 , 让我们通过查询表中的数据 , 验证一下是否插入正确 :
SELECT * FROM test_year;+----+--------------+
| id | created_year |
+----+--------------+
| 1 | 2022 |
| 2 | 2010 |
| 3 | 1998 |
| 4 | 0000 |
| 5 | 2000 |
+----+--------------+这里 , 我们可以看出 , 数字 0 转换为了 0000 , 而字符串 '0' 转换为了 2000。
7. ENUM
一个 ENUM 是一个字符串的列表 , 它定义了一个列中允许的值 , 列的值只能是创建列时定义的允许值列表中的的一个。
MySQL ENUM 数据类型列适合存储状态和标识等有限数量的固定值的数据。
MySQL ENUM 数据类型具有以下优点 :
列值的可读性更强。
紧凑的数据存储。
MySQL存储ENUM时只存储枚举值对应的数字索引 (1,2,3, …)。
7.1 ENUM语法
要是使用 ENUM 数据类型 , 这是它的语法 :
ENUM ('v1', 'v2', ..., 'vn')这里 ,
ENUM是一个关键字 , 用来声明一个枚举类型。v1到vn是此ENUM类型的可选项列表 , 使用ENUM类型的列的值只能是上面值中的其中一个。枚举值只能是字符串。
要定义 ENUM 列 , 请使用以下语法 :
CREATE TABLE table_name
(col_name ENUM ('v1','v2', ..., 'vn'));在 ENUM 数据类型中 , 您可以拥有多个枚举值。但是 , 将枚举值的数量保持在 20 以下是一种很好的做法。
7.2 ENUM示例
让我们看看下面的例子。
假设 , 我们有一个订单表用来存放电子商务订单。其中 , 订单状态 state 只有四种状态 , 如下表 :
那么我们就要为 state 列使用 ENUM 类型。
7.2.1 使用 MySQL ENUM 列
请使用以下 CREATE TABLE语句 :
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL
);现在 , 我们已经创建好了 orders 表 , 其中 state 列为 ENUM 数据类型 , 并且它将只接受四个值: Unpaid, Paid, Shipped 和 Completed。同时 , 按照列定义时的顺序 , Unpaid, Paid, Shipped 和 Completed 的索引分别为 1, 2, 3, 4。
7.2.2 插入 MySQL ENUM 值
要将插入数据到 ENUM 列中 , 请使用预定义列表中的枚举值。否则 , MySQL 会给出错误。 例如 , 以下语句向 orders 表中插入一个新行。
INSERT INTO orders(title, state)
VALUES ('Apples', 'Paid');除了枚举值 , 您还可以使用枚举成员的数字索引将数据插入到 ENUM 列中。例如 :
INSERT INTO orders(title, state)
VALUES ('Bananas', 2);在这个例子中 , 我们没有使用 Paid 枚举值 , 而是使用了值 2。由于 Paid 的索引是 2 , 所以是可以接受的。
让我们继续向 orders 表中添加更多行 :
INSERT INTO orders(title, state)
VALUES ('Pears', 'Shipped'),
('Peaches', 'Completed');因为我们定义了 state 一个 NOT NULL 列 , 当你插入一个新行而不指定state 列的值时 , MySQL 将使用第一个枚举成员作为默认值。请执行以下 SQL 语句 :
INSERT INTO orders(title) VALUES('Oranges');这里 , 我们没有 state 列指定值 , MySQL 将默认插入第一个枚举成员 Unpaid。
我们可以查询表中的所有行验证刚刚的所有操作。
SELECT * FROM orders;+----+---------+-----------+
| id | title | state |
+----+---------+-----------+
| 1 | Apples | Paid |
| 2 | Bananas | Paid |
| 3 | Pears | Shipped |
| 4 | Peaches | Completed |
| 5 | Oranges | Unpaid |
+----+---------+-----------+在非严格 SQL 模式下 , 如果在 ENUM 列中插入无效值 , MySQL 将使用带有数字索引 0 的空字符串 '' 进行插入。如果启用了严格 SQL 模式 , 尝试插入无效 ENUM 值将导致错误。
请注意 , 如果将 ENUM 列定义为可为空的列 , 则该列可以接受 NULL 值。
7.3 过滤 MySQL ENUM 值
以下语句获取所有的状态为已付款的订单 :
SELECT * FROM orders WHERE state = 'Paid';+----+---------+-------+
| id | title | state |
+----+---------+-------+
| 1 | Apples | Paid |
| 2 | Bananas | Paid |
+----+---------+-------+由于枚举成员 Paid 对应的数字索引是 2 , 因此以下查询返回相同的结果集 :
SELECT * FROM orders WHERE state = 2;7.4 排序MySQL ENUM值
MySQL 根据索引号对 ENUM 值进行排序。因此 , 枚举成员的顺序取决于它们在枚举列表中的定义方式。
因为 , state 列定义为 state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL , 那么当我们对 state 列升序排序时 , Unpaid 是排在最前面的 , 而 Completed 是排在最后面的。
请执行下面的语句并观察输出 :
SELECT * FROM orders ORDER BY state;+----+---------+-----------+
| id | title | state |
+----+---------+-----------+
| 5 | Oranges | Unpaid |
| 1 | Apples | Paid |
| 2 | Bananas | Paid |
| 3 | Pears | Shipped |
| 4 | Peaches | Completed |
+----+---------+-----------+因此 , 如果你需要按照枚举列进行排序 , 那么在创建列时按照要排序的顺序定义枚举值。
7.5 ENUM优缺点
MySQL ENUM 带来一些好处 , 比如可读性和存储效率 , 但是它也有以下缺点 :
更改枚举成员需要使用
ALTER TABLE语句重建整个表 , 这在资源和时间方面都是昂贵的。获取完整的枚举列表很复杂 , 因为您需要访问
information_schema数据库 :SELECT column_type FROM information_schema.COLUMNS WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'state';因为
ENUM它不是SQL标准的 , 因此 , 移植到其他RDBMS可能是一个问题。枚举列表是不可重用的。例如 , 上面
orders表中的状态枚举值 , 不能重用到新建的其他表上。枚举值是字符串 , 不能包含更多的信息。比如我们需要在每个订单状态上添加超时属性。
8. 修饰符
适用所有类型的修饰符 :
NULL: 数据列可包含NULL值 , 默认值NOT NULL: 数据列 不允许包含NULL值 ,*为必填选项DEFAULT: 默认值PRIMARY KEY: 主键 , 所有记录中此字段的值不能重复 , 且不能为NULL。UNIQUE KEY: 唯一键 , 所有记录中此字段的值不能重复 , 但可以为NULL。CHARACTER SET name: 指定一个字符集
适用数值型的修饰符 :
AUTO_INCREMENT: 自动递增 , 适用于整数类型UNSIGNED: 无符号
范例: 关于AUTO_INCREMENT
MariaDB [(none)]> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
# auto_increment_offset 定义初始值
# auto_incremnet_increment 定义步进范例:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> CREATE table t1(id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.05 sec)
MariaDB [test]> insert into t1 values(null);
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)
MariaDB [test]> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
MariaDB [test]> insert into t1 values(null);
ERROR 167 (22003): Out of range value for column 'id' at row 1