Как избежать пропусков а autoincrement при добавлении записи в MySQL?

Есть таблица, которая используется для промежуточных данных - назовем ее "temp_table".
В эту таблицу записываются промежуточные данные через Insert (перед началом записи часть данных удаляется через DELETE с определенным условием) - в результате появляются пропуски в поле autoincrement
пример данных до операций
|--id--|--wcell--|--data--|
|--1 --|---w1--- |--sd1---|
|--2 --|---w2--- |--sd2---|
|--3 --|---w1--- |--sd3---|
|--4 --|---w2--- |--sd4---|
next-autoincrement=5
. Удаляем старые данные (DELETE FROM `temp_table` WHERE `wcell`='w2') - после запроса данные в таблице
|--id--|--wcell--|--data--|
|--1 --|---w1--- |--sd1---|
|--3 --|---w1--- |--sd3---|
После чего происходит вставка новых данных, после чего таблица принимает вид
|--id--|--wcell--|--data--|
|--1 --|---w1--- |--sd1---| <--- пропуск - нет записи с id=2
|--3 --|---w1--- |--sd3---| <--- пропуск - нет записи с id=4
|--5 --|---w2--- |--nd4---| < --- новые записи
|--6 --|---w2--- |--nd5---|
|--7 --|---w2--- |--nd6---|
|--8 --|---w2--- |--nd7---|

Собственно вопрос: Как избежать пропусков, чтобы после вставки таблица принимала вид
|--id--|--wcell--|--data--|
|--1 --|---w1--- |--sd1---|
|--2 --|---w2--- |--nd4---| <--- свободный id 2 был занят новыми данными
|--3 --|---w1--- |--sd3---|
|--4 --|---w2--- |--nd5---| <--- свободный id 4 был занят новыми данными
|--5 --|---w2--- |--nd6---| <--- свободных id больше нет - используется autoincrement
|--7 --|---w2--- |--nd7---|

Пока что в голову пришло только одно решение
INSERT INTO `temp_table` ( `id` , `wcell` , `data` )
VALUES (
     (
                    SELECT 
                        IF(
                            `s`.`id`>0,`s`.`id`,`e`.`id`
                        ) AS `id` 
                            FROM 
                            (
                                SELECT
                                    `t1`.`id`+1 AS `id`
                                FROM
                                    `temp_table` AS `t1`
                                LEFT JOIN
                                    `temp_table` AS `t2`
                                        ON
                                            `t1`.`id`+1=`t2`.`id`
                                WHERE
                                    `t2`.`id` IS NULL
                                ORDER BY
                                    `t1`.`id` ASC
                                LIMIT 1
                            ) AS `e`
                            LEFT JOIN
                            (
                                SELECT
                                    `t1`.`id`-1 AS `id`
                                FROM
                                    `temp_table` AS `t1`
                                LEFT JOIN
                                    `temp_table` AS `t2`
                                        ON
                                            `t1`.`id`-1=`t2`.`id`
                                WHERE
                                    `t2`.`id` IS NULL
                                ORDER BY
                                    `t1`.`id` ASC
                                LIMIT 1
                            ) AS `s` ON 1
                ),  'w2' ,'nd#' );

Насколько это верное решение и какие могут всплыть подводные камни при интенсивной вставке/удалении
Движок таблицы InnoDB,
  • Вопрос задан
  • 4134 просмотра
Решения вопроса 1
Tyranron
@Tyranron
@VitaZheltyakov и @Playmore все правильно Вам сказали.
Вы, похоже, реально недооцениваете рост экспоненты.
Допустим у Вас 1 000 000 INSERT'ов в таблицу за секунду. Тогда за 30 лет непрерывной работы в таком режиме, значение вашего инкремента поднимется примерно до 1 000 000 * 86400 * 365 * 30 = 946080000000000 что на 5 десятичных разрядов меньше максимального числа unsigned bigint, а именно 18446744073709551615.
Так что я боюсь, что до Вашего "оттянутого" дня Х не доживете ни Вы, ни Ваши внуки, ни железо на Ваших серверах.
Потому не заморачивайтесь и оставьте в покое autoincrement как есть. В противном случае Вы создаете только лишнюю, но совершенно бесполезную, нагрузку на базу и приложение.
Ответ написан
Пригласить эксперта
Ответы на вопрос 3
Playmore
@Playmore
Опытный разработчик
либо не удаляйте записи и помечайте их флагом deleted либо ничего не изобретайте, потом каша будет это же ключевое поле, индекс.
Ответ написан
INSERT INTO `temp_table` ( `id` , `wcell` , `data` ) values ( select NULL, col2,col3 ...)
Ответ написан
Надо строить логику не привязанную к значениям полей autoincrement (то есть чтобы можно было удалить без последствий). А искать пустые значения - это изначально не правильно.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы