Периоды приложений

Расширяя таблицы с системным версионированием, MariaDB 10.4 поддерживает таблицы с периодами приложений. Периоды времени определяются диапазоном между двумя временными столбцами. Столбцы должны быть одного и того же временного типа данных, т. е. DATE, TIMESTAMP или DATETIME (TIME и YEAR не поддерживаются), и одинаковой ширины.

Использование периодов времени неявно определяет два столбца как NOT NULL. Также добавляется ограничение для проверки того, что первое значение меньше второго. Ограничение не видно для команд SHOW CREATE TABLE. Название этого ограничения предваряется именем периода времени, чтобы избежать конфликтов с другими ограничениями.

Создание таблиц с периодами времени

Для создания таблицы с периодом времени используйте оператор CREATE TABLE с опцией таблицы PERIOD.

CREATE TABLE t1(
   name VARCHAR(50), 
   date_1 DATE,
   date_2 DATE,
   PERIOD FOR date_period(date_1, date_2));

Это создает таблицу с периодом time_period и заполняет ее некоторыми базовыми временными значениями.

Примеры доступны в исходном коде сервера MariaDB по адресу mysql-test/suite/period/r/create.result.

Добавление и удаление периодов времени

Оператор ALTER TABLE теперь поддерживает синтаксис для добавления и удаления периодов времени из таблицы. Для добавления периода используйте предложение ADD PERIOD.

Например:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE
 );

ALTER TABLE rooms ADD PERIOD FOR p(checkin,checkout);

Для удаления периода используйте предложение DROP PERIOD.

ALTER TABLE rooms DROP PERIOD FOR p;

Оба предложения ADD PERIOD и DROP PERIOD включают опцию для обработки того, существует ли период уже:

ALTER TABLE rooms ADD PERIOD IF NOT EXISTS FOR p(checkin,checkout);

ALTER TABLE rooms DROP PERIOD IF EXISTS FOR p;

Удаление по частям

Вы также можете удалить строки, попадающие в определенные периоды времени.

Когда MariaDB выполняет оператор DELETE FOR PORTION, он удаляет строку:

• Если период строки полностью попадает в период удаления, строка удаляется.
• Если период строки перекрывается периодом удаления, строка уменьшается, удаляя перекрытие из первого или второго значения периода строки.
• Если период удаления полностью попадает в период строки, строка разделяется на две строки. Первая строка охватывает период от начала строки до начала периода удаления. Вторая - от конца периода удаления до конца периода строки.

Для проверки этого сначала заполните таблицу данными для работы:

CREATE TABLE t1(
   name VARCHAR(50), 
   date_1 DATE,
   date_2 DATE,
   PERIOD FOR date_period(date_1, date_2));

INSERT INTO t1 (name, date_1, date_2) VALUES
    ('a', '1999-01-01', '2000-01-01'),
    ('b', '1999-01-01', '2018-12-12'),
    ('c', '1999-01-01', '2017-01-01'),
    ('d', '2017-01-01', '2019-01-01');

SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+

Затем выполните оператор DELETE FOR PORTION.

DELETE FROM t1
FOR PORTION OF date_period
    FROM '2001-01-01' TO '2018-01-01';
Query OK, 3 rows affected (0.028 sec)

SELECT * FROM t1 ORDER BY name;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2001-01-01 |
| b    | 2018-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2001-01-01 |
| d    | 2018-01-01 | 2019-01-01 |
+------+------------+------------+

Здесь:

• a не изменяется, так как диапазон полностью выходит за пределы указанной части для удаления.
• b, со значениями от 1999 до 2018, разделяется на две строки: с 1999 по 2000 и с 2018-01 по 2018-12.
• c, со значениями от 1999 до 2017, где только верхнее значение попадает в часть для удаления, уменьшена до 1999-2001.
• d, со значениями от 2017 до 2019, где только нижнее значение попадает в часть для удаления, уменьшена до 2018-2019.

Оператор DELETE FOR PORTION имеет следующие ограничения:

• Предложение FROM...TO должно быть константой.
• Многократное удаление не поддерживается.

Обновление по частям

Синтаксис UPDATE теперь поддерживает UPDATE FOR PORTION, который изменяет строки в зависимости от их появления в диапазоне:

Для тестирования сначала заполните таблицу данными:

TRUNCATE t1;

INSERT INTO t1 (name, date_1, date_2) VALUES
    ('a', '1999-01-01', '2000-01-01'),
    ('b', '1999-01-01', '2018-12-12'),
    ('c', '1999-01-01', '2017-01-01'),
    ('d', '2017-01-01', '2019-01-01');

SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+

Затем выполните обновление:

UPDATE t1 FOR PORTION OF date_period
  FROM '2000-01-01' TO '2018-01-01' 
SET name = CONCAT(name,'_original');

SELECT * FROM t1 ORDER BY name;
+------------+------------+------------+
| name       | date_1     | date_2     |
+------------+------------+------------+
| a          | 1999-01-01 | 2000-01-01 |
| b          | 1999-01-01 | 2000-01-01 |
| b          | 2018-01-01 | 2018-12-12 |
| b_original | 2000-01-01 | 2018-01-01 |
| c          | 1999-01-01 | 2000-01-01 |
| c_original | 2000-01-01 | 2017-01-01 |
| d          | 2018-01-01 | 2019-01-01 |
| d_original | 2017-01-01 | 2018-01-01 |
+------------+------------+------------+

• a не изменяется, так как диапазон полностью выходит за пределы указанной части для удаления.
• b, со значениями от 1999 до 2018, разделяется на две строки: с 1999 по 2000 и с 2018-01 по 2018-12.
• c, со значениями от 1999 до 2017, где только верхнее значение попадает в часть для удаления, уменьшена до 1999-2001.
• d, со значениями от 2017 до 2019, где только нижнее значение попадает в часть для удаления, уменьшена до 2018-2019.
• Исходные строки, затронутые обновлением, имеют "_original" добавленное к имени.

Оператор UPDATE FOR PORTION имеет следующие ограничения:

• Операция не может изменить два временных столбца, используемых периодом времени.
• Операция не может ссылаться на значения периода в выражении SET.
• Выражения FROM...TO должны быть константными.

БЕЗ ПЕРЕКРЫТИЙ

Рассмотрим пример, таблица периодов времени приложения для системы бронирования:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout)
 );

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');

Наша система не должна допускать перекрывающихся бронирований, поэтому четвертая запись выше не должна была быть вставлена. Использование WITHOUT OVERLAPS в уникальном индексе (в данном случае на основе комбинации номера комнаты и периода времени приложения) позволяет нам определить это ограничение в определении таблицы.

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout),
 UNIQUE (room_number, p WITHOUT OVERLAPS)
 );

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');
ERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key 'room_number'

Дополнительные примеры

Неявное изменение из NULL в NOT NULL:

CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `d1` datetime DEFAULT NULL,
  `d2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE t2 ADD PERIOD FOR p(d1,d2);

SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `d1` datetime NOT NULL,
  `d2` datetime NOT NULL,
  PERIOD FOR `p` (`d1`, `d2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Из-за этого ограничения попытка добавить период времени, где уже существуют данные null, завершится неудачей.

CREATE OR REPLACE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `d1` datetime DEFAULT NULL,
  `d2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t2(id) VALUES(1);

ALTER TABLE t2 ADD PERIOD FOR p(d1,d2);
ERROR 1265 (01000): Data truncated for column 'd1' at row 1

См. также

• Таблицы с системным версионированием
• Двухвременные таблицы