MyISAM and InnoDB auto_increment difference

Рубрика: MySQL | 22 August 2008, 09:53 | Vadim Voituk

Заметил интересную особенность автоинкрементного поля в InnoDB MySQL Engine

В отличии от MyISAM, значение автоинкрементного счетчика может повторяться.
Пример:

(root@localhost) [test]> CREATE TABLE `innodb` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

(root@localhost) [test]> INSERT INTO innodb (id) VALUE(null);
(root@localhost) [test]> INSERT INTO innodb (id) VALUE(null);
(root@localhost) [test]> INSERT INTO innodb (id) VALUE(null);

(root@localhost) [test]> SELECT * FROM innodb;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@localhost) [test]> DELETE FROM innodb WHERE id=3;

После этого делаем рестарт MySQL-сервера и выполняем:

(root@localhost) [test]> INSERT INTO innodb (id) VALUE(null);

(root@localhost) [test]> SELECT * FROM innodb;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

И что же мы видим? - Опять запись с id=3, успешно удаленная в самый раз перед рестартом.

При использовании MyISAM, новая запись имела бы id=4 (как впрочем и ожидалось).

Немного погуглив, выяснил что "это не баг - это фича":

AUTO_INCREMENT column behavior is different between MyISAM and InnoDB:
- MyISAM: Maximum value +1 that has been inserted up to now
- InnoDB: SELECT MAX(auto_increment_column) + 1

Чем чревато данное поведение, думаю рассказывать не стоит.
Выход тут один - при использовании InnoDB в обязательном порядке все связи строить внешними ключами с каскадным удалением.

Комментариев: 8

8 Responses to “MyISAM and InnoDB auto_increment difference”

Комментарии:

  1. 02077461

    August 22nd, 2008 at 10:04 am

    Собственно говоря, столкнулся с тем, что любой более-менее серьезный проект (т.е. все проекты) нужно вести с таблицами в InnoDB, а наличие внешних ключей — это не просто хороший стиль программирования, а необходимое условие при проектировании БД.

  2. disserman

    August 22nd, 2008 at 10:52 am

    ето еще ничего. иногда мускл умудряется и UNIQUE KEY два раза один и тот же пропустить, если софт эту логику на базу возлагает и пихает что попало в надежде что лишнее откинется.

    особенно довольны этим replication slaves, собственно одна из главных причин почему даже master-slave репликация на мускле покамест удел лабораторий.

  3. Vadim Voituk

    August 22nd, 2008 at 11:02 am

    disserman,
    Для начала поспорю с тем, что Master-Slave-репликация – удел лабораторий. Уже достаточно давно работаю с ней на продакшн-услугах и пока все ОК. Хотя и есть нюансы.
    Например раз и два.

    Насчет же пропускания unique key на репликации – такое действительно возможно при использовании statement-based репликации (MySQL ниже версии 5), да и то в случае нарушения нормальной работы репликации (сбои, обрывы, рассинхронизация данных и тд). При использовании row-based
    репликации – все эти проблемы уходят в прошлое.

  4. BOLK

    August 22nd, 2008 at 4:21 pm

    Это написано где-то в документации, просто надо её читать.

  5. corsair

    August 22nd, 2008 at 4:41 pm

    А я люблю PostgreSQL. Ось так!

  6. Vadim Voituk

    August 22nd, 2008 at 4:45 pm

    BOLK,
    А кто её читает от А до Я? Обычно по читают только нужные главы и разделы по мере надобности.

    corsair,
    Разведем holy-war?

  7. Юра

    May 12th, 2009 at 11:54 am

    A чем чревато данное поведение?

  8. md5

    March 4th, 2010 at 12:12 pm

    Йурочка, это чревато появлением посторонних объектов на месте “удаленных”, если вы где забыли подчистить ссылку на эту “удаленную” позицию (ну и если вы конечно не используете FK)

Leave a Reply