MySQL 日期类型及默认设置

一、概述

日期类型在数据库中扮演着重要的角色，用于存储和处理日期和时间数据。在MySQL中，有多种日期类型可供选择，每种类型都有其特定的用途和限制。本文将详细介绍MySQL中常见的日期类型及其默认设置。

二、DATE 类型

DATE 类型用于存储日期。它使用 “YYYY-MM-DD” 格式表示，其中YYYY表示四位数的年份，MM为两位数的月份，DD为两位数的日期。DATE 类型可用于存储范围从 “1000-01-01” 到 “9999-12-31” 的日期。

2.1 创建表格

为了演示 DATE 类型的使用，我们先创建一个名为 employees 的表格，并添加一些样例数据。

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  hire_date DATE
);

INSERT INTO employees (name, hire_date)
VALUES 
  ('张三', '2020-01-01'),
  ('李四', '2019-05-15'),
  ('王五', '2021-03-10');

2.2 日期格式化

在MySQL中，日期的存储格式可能和我们期望的不同。可以使用 DATE_FORMAT 函数将日期格式化成我们想要的样式。

SELECT name, DATE_FORMAT(hire_date, '%Y年%m月%d日') AS formatted_date
FROM employees;

-- Output:
-- +------+----------------+
-- | name | formatted_date |
-- +------+----------------+
-- | 张三 | 2020年01月01日 |
-- | 李四 | 2019年05月15日 |
-- | 王五 | 2021年03月10日 |
-- +------+----------------+

2.3 默认值

当创建表格并指定 DATE 类型时，可以为该列设置默认值。默认值是在插入新行时自动应用的值。

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_name VARCHAR(50),
  event_date DATE DEFAULT '2022-01-01'
);

INSERT INTO events (event_name) VALUES ('新年派对');

在上面的例子中，如果没有指定 “event_date” 的值，它将默认为 ‘2022-01-01’。

三、TIME 类型

TIME 类型用于存储时间。它使用 “HH:MM:SS” 格式表示，其中HH表示小时（00-24），MM为分钟（00-59），SS为秒（00-59）。TIME 类型可用于存储范围从 ‘-838:59:59’ 到 ‘838:59:59’ 的时间。

3.1 创建表格

为了演示 TIME 类型的使用，我们创建一个名为 meetings 的表格，并添加一些样例数据。

CREATE TABLE meetings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  subject VARCHAR(50),
  start_time TIME
);

INSERT INTO meetings (subject, start_time)
VALUES 
  ('项目会议', '10:00:00'),
  ('午餐休息', '12:30:00'),
  ('团队讨论', '15:45:00');

3.2 默认值

与DATE 类型类似，TIME 类型的列也可以设置默认值。默认值在插入新行时应用。

CREATE TABLE tasks (
  id INT PRIMARY KEY AUTO_INCREMENT,
  task_name VARCHAR(50),
  duration TIME DEFAULT '01:00:00'
);

INSERT INTO tasks (task_name) VALUES ('编写报告');

在上例中，如果没有指定 “duration” 的值，它将默认为 ’01:00:00’。

3.3 时间计算

在MySQL中，可以进行时间计算。例如，可以将两个时间相加，或者将一个时间与一个时间间隔相加。下面是一个计算任务预计结束时间的示例:

SELECT task_name, ADDTIME(start_time, duration) AS end_time
FROM meetings;

-- Output:
-- +------------+----------+
-- | task_name  | end_time |
-- +------------+----------+
-- | 项目会议   | 11:00:00 |
-- | 午餐休息   | 13:30:00 |
-- | 团队讨论   | 16:45:00 |
-- +------------+----------+

四、DATETIME 类型

DATETIME 类型用于存储日期和时间。它使用 “YYYY-MM-DD HH:MM:SS” 格式表示，其中YYYY表示四位数的年份，MM为两位数的月份，DD为两位数的日期，HH为小时（00-23），MM为分钟（00-59），SS为秒（00-59）。DATETIME 类型可用于存储范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ 的日期和时间。

4.1 创建表格

为了演示 DATETIME 类型的使用，我们创建一个名为 orders 的表格，并添加一些样例数据。

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(50),
  order_date DATETIME
);

INSERT INTO orders (product_name, order_date)
VALUES 
  ('手机', '2021-01-01 09:30:00'),
  ('电视', '2021-02-15 14:45:00'),
  ('电脑', '2021-03-10 18:20:00');

4.2 默认值

与前面介绍的日期类型类似，DATETIME 类型的列也可以设置默认值。默认值在插入新行时应用。

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_name VARCHAR(50),
  event_datetime DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (event_name) VALUES ('会议');

在上例中，如果没有指定 “event_datetime” 的值，它将默认为当前日期和时间。

4.3 日期和时间函数

MySQL提供了一些日期和时间函数，用于在查询中处理和格式化日期和时间数据。

SELECT 
  product_name, 
  order_date, 
  DATE(order_date) AS order_day, 
  DAYNAME(order_date) AS order_dayname 
FROM orders;

-- Output:
-- +--------------+---------------------+------------+----------------+
-- | product_name | order_date          | order_day  | order_dayname  |
-- +--------------+---------------------+------------+----------------+
-- | 手机         | 2021-01-01 09:30:00 | 2021-01-01 | 星期五         |
-- | 电视         | 2021-02-15 14:45:00 | 2021-02-15 | 星期一         |
-- | 电脑         | 2021-03-10 18:20:00 | 2021-03-10 | 星期三         |
-- +--------------+---------------------+------------+----------------+

在上面的示例中，通过 DATE 函数提取了 order_date 字段中的日期，通过 DAYNAME 函数提取了该日期的星期几。

五、TIMESTAMP 类型

TIMESTAMP 类型用于存储日期和时间，类似于DATETIME。然而，TIMESTAMP 类型有一些特殊的行为和默认设置。

5.1 创建表格

为了演示 TIMESTAMP 类型的使用，我们创建一个名为 logs 的表格，并添加一些样例数据。

CREATE TABLE logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  message VARCHAR(100),
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (message) VALUES ('系统启动');

5.2 自动更新

与前面介绍的日期类型不同，TIMESTAMP 类型的列具有自动更新功能。当插入新行或更新该行时，TIMESTAMP 类型的列将自动更新为当前日期和时间。

UPDATE logs SET message = '系统重启' WHERE id = 1;

在上例中，更新操作将会自动将 log_time 列更新为当前日期和时间。

5.3 TIMESTAMP 和时区

值得注意的是，TIMESTAMP 类型的列的默认行为与时区设置有关。MySQL的默认时区是服务器的时区。在插入或查询 TIMESTAMP 类型的数据时，MySQL将自动根据时区进行转换。

可以使用 @@session.time_zone 查看当前会话的时区设置。

SELECT @@session.time_zone;

-- Output:
-- +----------------------+
-- | @@session.time_zone  |
-- +----------------------+
-- | SYSTEM               |
-- +----------------------+

如果需要更改时区设置，可以使用 SET time_zone 语句进行修改。

SET time_zone = '+8:00';

在上例中，将会话的时区设置为东八区。

六、YEAR 类型

YEAR 类型用于存储年份。它使用四位数表示，范围从1901到2155。YEAR 类型可以存储完整的年份，也可以存储年份的最后两位数。

6.1 创建表格

为了演示 YEAR 类型的使用，我们创建一个名为 vehicles 的表格，并添加一些样例数据。

CREATE TABLE vehicles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  make VARCHAR(50),
  manufacture_year YEAR
);

INSERT INTO vehicles (make, manufacture_year)
VALUES 
  ('奥迪', 2010),
  ('宝马', 2022),
  ('奔驰', 1999);

6.2 存储最后两位数

YEAR 类型的默认行为是存储年份的完整四位数。如果想要只存储年份的最后两位数，可以在插入数据时进行设置。

INSERT INTO vehicles (make, manufacture_year)
VALUES ('大众', YEAR(2005));

在上例中，YEAR 函数会将传入的参数转换为 YEAR 类型，并只存储最后两位数。

七、总结

本文详细介绍了MySQL中常见的日期类型及其默认设置。在使用日期类型时，需要注意不同类型的限制和特殊行为，以及时区设置对于 TIMESTAMP 类型的影响。合理地使用日期类型可以更好地存储和处理日期和时间数据，提高数据库的功能和性能。