Innodb и auto_increment
6 мая 2009, 1:39
Автор: rgbeast
Оказалось, что Innodb работает с полями типа auto_increment иначе, чем MyISAM. Есть по крайней мере 2 отличия:
1. InnoDB не хранит на диске значение AUTO_INCREMENT для таблицы. Значение хранится в оперативной памяти, а при перезапуске сервера устанавливается в значение, равное максимальному id в таблице + 1.
2. Если клиент выполнил запрос UPDATE tbl SET id = 'XXX',причем новое значение id больше, чем значение AUTO_INCREMENT для таблицы, то MyISAM автоматически обновит значение AUTO_INCREMENT так, чтобы оно было больше самого большого id в таблице. В аналогичной ситуации Innodb не изменит значение AUTO_INCREMENT и последующая вставка в таблицу может привести к ошибке "Duplicate key".
Отличия приводят к несовместимости некоторых приложений при переходе с MyISAM на InnoDB. Для того, чтобы этого не было, никогда не меняйте id вручную и не меняйте значение AUTO_INCREMENT (изменение не сохранится при перезапуске сервера). Если все же требуется изменить id, то делайте это с помощью запросов SELECT, DELETE и INSERT, а не с помощью запроса UPDATE. Для изменения id можно использовать временную таблицу:
1. InnoDB не хранит на диске значение AUTO_INCREMENT для таблицы. Значение хранится в оперативной памяти, а при перезапуске сервера устанавливается в значение, равное максимальному id в таблице + 1.
2. Если клиент выполнил запрос UPDATE tbl SET id = 'XXX',причем новое значение id больше, чем значение AUTO_INCREMENT для таблицы, то MyISAM автоматически обновит значение AUTO_INCREMENT так, чтобы оно было больше самого большого id в таблице. В аналогичной ситуации Innodb не изменит значение AUTO_INCREMENT и последующая вставка в таблицу может привести к ошибке "Duplicate key".
Отличия приводят к несовместимости некоторых приложений при переходе с MyISAM на InnoDB. Для того, чтобы этого не было, никогда не меняйте id вручную и не меняйте значение AUTO_INCREMENT (изменение не сохранится при перезапуске сервера). Если все же требуется изменить id, то делайте это с помощью запросов SELECT, DELETE и INSERT, а не с помощью запроса UPDATE. Для изменения id можно использовать временную таблицу:
BEGIN;
CREATE TEMPORARY TABLE tmp_i SELECT * FROM my_table WHERE id='$old_id';
DELETE FROM my_table WHERE id='$old_id';
UPDATE tmp_i SET id='$new_id';
INSERT INTO my_table SELECT * FROM tmp_i;
DROP TEMPORARY TABLE tmp_i;
COMMIT;
CREATE TEMPORARY TABLE tmp_i SELECT * FROM my_table WHERE id='$old_id';
DELETE FROM my_table WHERE id='$old_id';
UPDATE tmp_i SET id='$new_id';
INSERT INTO my_table SELECT * FROM tmp_i;
DROP TEMPORARY TABLE tmp_i;
COMMIT;
