Форум пользователей MySQL

dekster · 06.05.2008 19:45:24

В общем вопрос такой:
Существует некая таблица с полем к примеру `id` bigint(20) NOT NULL auto_increment (к примеру строк 500)
Так вот, после выполнения (опять же к примеру )) DELETE FROM some_table WHERE some_table.id=12, у меня остаются в этой таблице id со следующими значениями: 1,2,.... 11,13,.... Так вот вопрос - можно ли заставить БД саму пересчитывать id, чтобы избавиться об образовавшейся "бреши"??

vasya · 06.05.2008 20:58:19

ALTER TABLE some_table MODIFY `id` bigint(20) NOT NULL;
ALTER TABLE some_table DROP PRIMARY KEY;
UPDATE some_table SET id=0;
ALTER TABLE some_table MODIFY `id` bigint(20) NOT NULL auto_increment PRIMARY KEY;

Но это извращение. И выдаст ошибку в случае если установлен режим NO_AUTO_VALUE_ON_ZERO.
Возникает вопрос, а зачем это вообще нужно?

LazY · 07.05.2008 00:27:28

Я бы на вашем месте завел в таблице отдельное поле для сквозной нумерации, заполнить его можно с помощью специальной процедуры.
Удалять записи также лучше бы не напрямую, а при помощи еще одной процедуры, которая после удаления стала бы пересчитывать значение поля-счетчика для нужных записей.

Эти задачи можно реализовать также и по-другому, если использовать в качестве клиентов программы на других языках программирования (php, perl и др.)

dekster · 08.05.2008 18:28:37

LazY написал:
Эти задачи можно реализовать также и по-другому, если использовать в качестве клиентов программы на других языках программирования (php, perl и др.)

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

Всем спасибо )

LazY · 09.05.2008 12:16:24

Очень даже можно и средствами БД.
Для этого нужно сделать несколько несложных вещей (SQL-код, приводимый ниже, можно тупо вставлять в клиент и выполнять - только вставить нужные имена таблицы и столбцов; все пояснения находятся внутри комментариев).
Не стесняйтесь задавать вопросы.
Итак, нужно:

1. Добавить в таблицу столбец, который будет хранить счетчик:

ALTER TABLE some_table ADD COLUMN counter INT AFTER id;

2. Заполнить вновь созданный столбец актуальными значениями. Для этого понадобится процедура, которую тут же и напишем:

DELIMITER $$ /* для написания процедуры нужно поменять маркер конца команды с точки с запятой на какой-нибудь другой иначе клиент не поймет, что мы пишем процедуру, и решит, что мы вводим отдельные команды; в таких случаях часто используется, например, двойной знак доллара */

CREATE PROCEDURE `recount_some_table`()

BEGIN

  DECLARE tmpid, cnt, exit_cycle INT; -- в MySQL все переменные нужно объявлять, причем указывая тип

  DECLARE c CURSOR FOR SELECT id FROM some_table; -- объявляем специальный объект, позволяющий нам работать с многострочными SELECT'ами

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'

    /* HANDLER - это правило, которое говорит, что делать,

    если наступит определенное событие, которое ловится по коду.

    В данном случае, событие - конец данных

    (т.е. когда заканчивается результат SELECT'а) */

    BEGIN

      SET exit_cycle = 1; -- указываем действие - установка флага выхода из цикла в единицу

    END;

  SET cnt = 0; -- устанавливаем счетчик в 0

  OPEN c; -- открываем курсор, к которому обращаемся по его имени - "с"

  L: LOOP -- L - специальная метка, которая служит для именования цикла

    FETCH c INTO tmpid;

    /* записываем результат одной строки из SELECT'а в переменную;

    сколько столбцов в SELECT'е - столько и переменных */

    -- надо посмотреть, не закончились ли данные в курсоре

    IF exit_cycle = 1 THEN

        LEAVE L; -- выходим из блока, помеченого как L;

        /* переменная exit_cycle может быть равна единице только после того, как

        сработал HANDLER на событие '23000' - т.е. когда закончатся данные */

    END IF;

    -- если данные не закончились - обрабатываем их:

    SET cnt = cnt + 1;

    UPDATE some_table SET counter = cnt WHERE id = tmpid;

  END LOOP;

  CLOSE c; -- закрываем курсор (лучше закрывать в конце, хотя и не обязательно)

END $$

DELIMITER ; -- написание продедуры закончено; теперь вернем обычный маркер конца команды

Теперь процедуру нужно вызвать:

CALL recount_some_table;

Процедура пересчитает всю таблицу. Хотя это нужно сделать, каждый раз это повторять нерационально. Очевидно, что счетчик пересчитывать нужно будет только для тех записей, которые были вставлены после удаленной. Поэтому напишем еще одну маленькую процедуру, которая это условие учитывает, и удалять будем с помощью неё:

3.

DELIMITER $$

CREATE PROCEDURE del_from_some_table(IN delid INT)

-- у процедур нужно указывать вид параметра - входной, выходной или входной/выходной

-- нам далее данные из процедуры не нужны, поэтому используем входной

BEGIN

  DELETE FROM some_table WHERE id = delid;

  UPDATE some_table SET counter = counter - 1 WHERE id > delid;

END

$$

DELIMITER ;

Удалять теперь нужно будет не командой
DELETE FROM some_table ... WHERE id = x;
а командой CALL del_from_some_table(x).

Хотя можно просто каждый раз писать два запроса.
Если вдруг случайно удалите простым DELETE'ом, и в счетчике возникнет дырка - всегда можно пересчитать первой процедурой.

Подробнее о хранимом коде в MySQL можно прочитать здесь:
http://dev.mysql.com/doc/refman/5.0/en/ … dures.html

dekster · 10.05.2008 11:02:31

2LazY
Ух... спасибо!!! мощно!!! ща сижу разбираюсь!

evil-751 · 22.06.2017 15:32:21

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

Только вот обратил внимание что если вызывать функцию CALL del_from_some_table(x), то не всегда происходит пересчет строк, приходится дополнительно пересчитывать второй функцией. Чем это может быть вызвано?

Форум пользователей MySQL

#1 06.05.2008 19:45:24

Вопрос про auto_increment

#2 06.05.2008 20:58:19

Re: Вопрос про auto_increment

#3 07.05.2008 00:27:28

Re: Вопрос про auto_increment

#4 08.05.2008 18:28:37

Re: Вопрос про auto_increment

LazY написал:

#5 09.05.2008 12:16:24

Re: Вопрос про auto_increment

#6 10.05.2008 11:02:31

Re: Вопрос про auto_increment

#7 22.06.2017 15:32:21

Re: Вопрос про auto_increment

Board footer