The OpenNET Project / Index page

[ новости/++ | форум | wiki | теги ]

Использование одного автоинкрементального счетчика для нескольких таблиц в MySQL
Решение задачи сохранения единого для нескольких таблиц автоинкрементального счетчика. 

Иными словами, как реализовать в MySQL аналог SEQUENCE в PostgreSQL, работающих примерно так:

   CREATE SEQUENCE next_contraсt start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
   CREATE TABLE contract (
    "id"  integer NOT NULL DEFAULT nextval('next_contraсt') PRIMARY KEY,
   ...
   );


Метод 1. 

Создаем таблицу:

   CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb;

При необходимости получения очередного номера счетчика выполняем (фиктивная
вставка данных, необходимая для срабатывания auto_increment):

   INSERT INTO option1 VALUES (NULL);

Получаем текущее значение идентификатора через API-вызов $connection->insert_id(), например, в PHP:

   $last_id = mysql_insert_id();


Метод 2:

Создаем таблицу из одного столбца:

   CREATE TABLE option2 (id int not null primary key) engine=innodb;

Инициализируем первое значение:

   INSERT INTO option2 VALUES (1); # начинаем последовательность с 1

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

   UPDATE option2 SET id=@id:=id+1;
   SELECT @id;

По производительности первый метод заметно быстрее второго:

   Метод 1 с использованием транзакций: 19 сек на выполнение 10000 итераций
   Метод 1 без использования транзакций: 13 сек на выполнение 10000 итераций
   Метод 2 с использованием транзакций: 27 сек на выполнение 10000 итераций
   Метод 2 без использования транзакций: 22 сек на выполнение 10000 итераций

Метод 3.

Использовать функцию LAST_INSERT_ID(), которая возвращает последний
сгенерированный через AUTO_INCREMENT на сервере идентификатор.

   UPDATE option3 SET id = LAST_INSERT_ID(id+1);

Метод 4.

Использование хранимых процедур и триггеров.

Создаем таблицу sequences для хранения счетчика и функцию  nextval('seqname')
для возвращения следующего номера:

   CREATE TABLE IF NOT EXISTS sequences
   (name CHAR(20) PRIMARY KEY,
   val INT UNSIGNED);

   DROP FUNCTION IF EXISTS nextval;

   DELIMITER //

   CREATE FUNCTION nextval (seqname CHAR(20))
   RETURNS INT UNSIGNED
   BEGIN
   INSERT INTO sequences VALUES (seqname,LAST_INSERT_ID(1))
   ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
   RETURN LAST_INSERT_ID();
   END
   //

   DELIMITER ;

Создаем тестовую таблицу data для последующего подключения генератора последовательности:

   CREATE TABLE IF NOT EXISTS data
   (id int UNSIGNED NOT NULL PRIMARY KEY DEFAULT 0,
   info VARCHAR(50));

Создаем триггер для автоматизации инкрементирования счетчика в таблице data:

   DROP TRIGGER nextval;
   CREATE TRIGGER nextval BEFORE INSERT ON data
   FOR EACH ROW SET new.id=IF(new.id=0,nextval('data'),new.id);
   TRUNCATE TABLE data;

Экспериментируем:

   INSERT INTO data (info) VALUES ('bla');
   INSERT INTO data (info) VALUES ('foo'),('bar');
   SELECT * FROM data;

   +----+------+
   | id | info |
   +----+------+
   |  1 | bla  |
   |  2 | foo  |
   |  3 | bar  |
   +----+------+
 
Ключи: mysql, sequence / Лицензия: CC-BY
Раздел:    Корень / Программисту и web-разработчику / SQL и базы данных / MySQL специфика / Оптимизация и администрирование MySQL

Обсуждение [ RSS ]
 
  • 1.1, Alexander, 20:54, 14/10/2010 [ответить] [смотреть все]
  • +/
    что это за детский сад? помоему любой человек понимающий SQL напишет это все с закрытими глазами.. ито как... дважды два, как соль соленая, я даже не знаю как еще выразить свое удивление к данной статьте
     
  • 1.2, ss25, 10:11, 15/10/2010 [ответить] [смотреть все]
  • +/
    Метод 0.
    Использовать PostgreSQL и не иметь себе и людям мозг.
     
  • 1.3, тот_самый, 18:41, 15/10/2010 [ответить] [смотреть все]
  • +/
    обьясните пожалуйста зачем это вообще?
     
     
  • 2.5, dry, 01:14, 16/10/2010 [^] [ответить] [смотреть все]
  • +/
    чтобы иметь unique id в пределах нескольких таблиц или даже базы/схемы. на самом деле очень немного реальных задач, где это необходимо и в корне этой необходимости как правило лежит дурное проектирование.
    тем не менее, в качестве гипотетической задачи - такой хороший камень в огород mysql,
    описанные костыли реально вызывают умиление.
     
     
  • 3.6, Админ Веня, 12:33, 16/10/2010 [^] [ответить] [смотреть все]
  • +/
    +1
    Не дурное проектирование, а простое непонимание нормализации баз данных выше 2-3 уровня(коль уже возникла такая потребность).
    Но вопрос еще: почему, если mysql создавался как более гибкая альтернатива для mSQL, сиквенсы тупо выкинули? Но это вопрос риторики.
     
  • 2.10, zazik, 11:49, 22/10/2010 [^] [ответить] [смотреть все]  
  • +/
    > обьясните пожалуйста зачем это вообще?

    Чтобы написать совет на опеннет, нет?

     
  • 1.4, Аноним, 19:49, 15/10/2010 [ответить] [смотреть все]  
  • +/
    2Alexander ну не скажи я вот 4 вариант с ходу не написал бы но и sql я знаю та... весь текст скрыт [показать]
     
     
  • 2.8, Александр, 17:39, 17/10/2010 [^] [ответить] [смотреть все]  
  • +/
    ну как бе, зная pl sql, у тебя бы этот вопрос вообще не встал, тут просто вариант что из пхп версии инкрементирование перенесли в sql.. разница не велика, разве что в проихводительности
     
  • 1.7, Vanzhiganov, 12:37, 16/10/2010 [ответить] [смотреть все]  
  • +/
    Alexander, вы наверняка не видели костыли пострашнее. По мне так ясно сформулированная логика.
     
     
  • 2.9, Александр, 19:46, 17/10/2010 [^] [ответить] [смотреть все]  
  • +/
    к своему сожалению, за имением опыта, я видел костыли и по-страшнее, но речь та не об этом )
     

    Ваш комментарий
    Имя:         
    E-Mail:      
    Заголовок:
    Текст:



      Закладки на сайте
      Проследить за страницей
    Created 1996-2017 by Maxim Chirkov  
    ДобавитьРекламаВебмастеруГИД  
    Hosting by Ihor