Skip to content

Latest commit

 

History

History
84 lines (64 loc) · 4.25 KB

cert-id.sql.md

File metadata and controls

84 lines (64 loc) · 4.25 KB

Создание уникального ID сертификата в формате год-месяц-номер

Задача: создавать ID сертификатов в формате год (2 знака), месяц (2 знака), порядковый номер в текущем месяце с обнулением 1 числа каждого месяца (4 знака). Например, для 19 июня 2018 года первый сертификат будет 18060001, следующий 18060002.

Решения два:

  • составной ключ год-месяц (триггер) + номер (AUTO_INCREMENT)
  • один ключ, полностью по триггеру

Первому решению я доверяю больше, не будут ошибки вставки, когда два запроса придут одновременно.

Во втором случае в момент работы триггера может возникнуть ситуация, когда SELECT уже отработал, но до фактической вставки данные уже изменились, и ID уже занят. Тогда возникнет ошибка первичного ключа. Это чисто теоретически.

Один ключ, полностью по триггеру

Таблица:

CREATE TABLE `api_certs` (
`ID` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'ID сертификата: год (2 знака), месяц (2 знака), порядковый номер в текущем месяце с обнулением 1 числа каждого месяца (4 знака), f.e. 18060001',
`DATE_CREATED` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT 'Дата создания',
PRIMARY KEY (`ID`)
) ENGINE=INNODB;

Дата создания выбрана не '0000-00-00 00:00:00', так как с версии MySQL 5.7 возникнет ошибка Invalid default value for 'DATE_CREATED'

Триггер:

-- Удаляем старый триггер, если он есть
DROP TRIGGER IF EXISTS `api_certs_before_insert`;

-- Временно меняем разделитель выражений
DELIMITER $$

-- Создаем триггер
CREATE TRIGGER `api_certs_before_insert` BEFORE INSERT ON `api_certs`
  FOR EACH ROW BEGIN
    -- Префикс "год-месяц"
    SET @init_id_prefix=DATE_FORMAT(NOW(), "%y%m");
    -- Следующий номер, нумерация не с 0, а с 1
    SET @init_id=@init_id_prefix * 10000 + 1;
    -- LIKE префикс нужен для исключения ошибки, см объяснение ниже
    SET @init_id_prefix=CONCAT(@init_id_prefix, '%');
    -- Выбираем следующий номер в этом временном периоде, либо @init_id
    SELECT IFNULL(MAX(ID) + 1, @init_id) INTO @newID FROM `api_certs` WHERE `ID` >= @init_id AND `ID` LIKE @init_id_prefix;
    -- Перезаписываем ID вычисленным значением
    SET NEW.ID = @newID;
  END;
$$
-- Возвращаем разделитель выражений
DELIMITER ;

Конструкция ID LIKE @init_id_prefix необходима для защиты от ошибки смены времени на сервере:

  • дата: июнь 2018 года => префикс 1806
  • поменяли дату сервера вперед на 1 месяц => префикс 1807
  • произошла вставка: 1807001
  • вернули дату сервера назад => префикс 1806
  • без условия ID LIKE следующий номер будет 1807002, а не 1806001 (правильный вариант)

Проверим разные варианты вставки:

INSERT INTO `api_certs` VALUES
(DEFAULT, DEFAULT),
(DEFAULT, DEFAULT);

INSERT INTO `api_certs` VALUES (DEFAULT, DEFAULT);
mysql> SELECT * FROM `api_certs`;
+----------+---------------------+
| ID       | DATE_CREATED        |
+----------+---------------------+
| 18060001 | 1000-01-01 00:00:00 |
| 18060002 | 1000-01-01 00:00:00 |
| 18060003 | 1000-01-01 00:00:00 |
+----------+---------------------+