INTERVAL 数据类型

本文介绍的是 INTERVAL 数据类型，用于在表中直接存储时间跨度值；它不同于 SQL 表达式中通用的 INTERVAL 关键字语法。前者用于定义列的数据类型，后者常见于 DATE_ADD()、DATE_SUB() 等日期运算表达式中。

INTERVAL 数据类型适合需要直接保存“时间跨度”而不是“具体时间点”的场景。例如，业务系统需要保存账期、合同期限、保留周期、任务耗时或超时阈值时，可以将这些值直接写入 INTERVAL 列，并在 SQL 中结合日期函数执行时间运算。

使用场景

  • 存储按“年-月”表示的周期值，例如账期、合同期限、保留周期等。
  • 存储按“天-时-分-秒”表示的持续时间，例如任务耗时、超时时长、宽限时间等。
  • 在 SQL 中直接将时间间隔与 DATE 或 DATETIME 值组合，执行 DATE_ADD()、DATE_SUB() 等日期运算。

使用前提与开启方式

INTERVAL 数据类型由全局系统变量 pkdb_extra_data_type 控制，默认值为 OFF。

在创建 INTERVAL 列之前，先确认并开启该变量：

SHOW GLOBAL VARIABLES LIKE 'pkdb_extra_data_type';
SET GLOBAL pkdb_extra_data_type = ON;

开启后，后续新建的 INTERVAL 列会在 SHOW CREATE TABLE 和 DESCRIBE 中显示为 interval year(p) to month 或 interval day(p) to second。

关闭该变量时，INTERVAL 关键字本身不会报语法错，但后续新建列会按普通整数类型处理：

  • INTERVAL YEAR(p) TO MONTH 会按 INT(p) 处理。
  • INTERVAL DAY(p) TO SECOND 会按 INT(p) 处理。

因此，在使用 INTERVAL 数据类型前，建议先通过 SHOW CREATE TABLE 确认列类型。

INTERVAL 值的类型

当前 INTERVAL 数据类型支持以下两种形式：

  • INTERVAL YEAR[(p)] TO MONTH
  • INTERVAL DAY[(p)] TO SECOND

其中，p 表示前导字段的最大位数，取值范围为 1 到 9；未显式指定时，默认值为 2。

INTERVAL YEAR[(p)] TO MONTH

INTERVAL YEAR[(p)] TO MONTH 用于表示按“年-月”组织的时间跨度。

合法输入格式如下：

[+|-]years-months

格式要求如下：

  • years 的位数范围为 1..p。
  • months 的取值范围为 0..11。
  • 可带正负号。
  • 连字符 - 两侧允许出现空格，但写入后会按规范格式输出。

例如，以下值是合法的：

'2-11'
'10-4'
'-1-1'
' 2 - 11 '
'+2-11'

查询结果会按规范格式显示，例如：

  • '10-4' 显示为 10-4
  • ' 2 - 11 ' 显示为 2-11

INTERVAL DAY[(p)] TO SECOND

INTERVAL DAY[(p)] TO SECOND 用于表示按“天-时-分-秒”组织的时间跨度。

合法输入格式如下：

[+|-]days HH:MM:SS

格式要求如下：

  • days 的位数范围为 1..p。
  • HH 取值范围为 0..23。
  • MM 和 SS 取值范围均为 0..59。
  • days 和时间部分之间至少需要一个空白字符。
  • 当前不支持小数秒。
  • 可带正负号。
  • 输入中允许出现多余空格或非两位的时分秒写法，但写入后会按规范格式输出。

例如，以下值是合法的：

'2 11:04:05'
' 02  3:4:5 '
'-1 1:1:1'
'+2 03:04:05'

查询结果会按规范格式显示，例如：

  • ' 02 3:4:5 ' 显示为 2 03:04:05
  • '-1 1:1:1' 显示为 -1 01:01:01

SQL 示例

下面的示例展示了如何创建两种 INTERVAL 列、写入间隔值，以及将其用于日期加减运算：

SET GLOBAL pkdb_extra_data_type = ON;

DROP TABLE IF EXISTS intervals_demo;

CREATE TABLE intervals_demo (
    id INT PRIMARY KEY,
    ym INTERVAL YEAR(3) TO MONTH,
    ds INTERVAL DAY(3) TO SECOND
);

SHOW CREATE TABLE intervals_demo;
DESCRIBE intervals_demo;

INSERT INTO intervals_demo VALUES
    (1, '2-11', '2 11:04:05'),
    (2, '-1-1', '-1 01:01:01'),
    (3, '10-4', ' 02  3:4:5 ');

SELECT id, ym, ds FROM intervals_demo ORDER BY id;

SELECT
    DATE_ADD('2012-12-12', INTERVAL '1-1' YEAR TO MONTH) AS add_ym,
    DATE_SUB('2012-12-12 00:00:00', INTERVAL '1 01:01:01' DAY TO SECOND) AS sub_ds;

DROP TABLE intervals_demo;

在上面的查询结果中，ym 和 ds 会以规范格式显示。例如，第 3 行写入的 ' 02 3:4:5 ' 会显示为 2 03:04:05。

对于日期运算：

  • DATE_ADD('2012-12-12', INTERVAL '1-1' YEAR TO MONTH) 返回 2014-01-12。
  • DATE_SUB('2012-12-12 00:00:00', INTERVAL '1 01:01:01' DAY TO SECOND) 返回 2012-12-10 22:58:59。

注意事项与限制

  • 当前仅支持两种 INTERVAL 数据类型形式：INTERVAL YEAR[(p)] TO MONTH 和 INTERVAL DAY[(p)] TO SECOND。
  • p 的取值范围为 1 到 9；未指定时默认值为 2。
  • 向 INTERVAL YEAR TO MONTH 列写入非法值时，语句会报错，错误信息为 Invalid Interval value。
  • 向 INTERVAL DAY TO SECOND 列写入非法值时，语句会报错，错误信息同样为 Invalid Interval value。
  • 对 INTERVAL YEAR TO MONTH 来说，月份字段必须在 0..11 范围内；例如 1-12、88-18 都是非法值。
  • 对 INTERVAL DAY TO SECOND 来说，时间部分必须完整写成 HH:MM:SS，且当前不支持小数秒；例如 1 24:00:00、1 00:60:00、1 00:00、1 00:00:00.1 都是非法值。
  • 关闭 pkdb_extra_data_type 后，使用 INTERVAL 定义的新列会退化为 INT(p)，不会显示为 interval 类型。因此在建表后，建议通过 SHOW CREATE TABLE 再次确认真实列类型。

相关文档

下载 PDF