SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 06.05.2008 19:45:24

dekster
Участник
Зарегистрирован: 06.05.2008
Сообщений: 3

Вопрос про auto_increment

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

Неактивен

 

#2 06.05.2008 20:58:19

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

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

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.
Возникает вопрос, а зачем это вообще нужно?

Неактивен

 

#3 07.05.2008 00:27:28

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

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

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

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

Неактивен

 

#4 08.05.2008 18:28:37

dekster
Участник
Зарегистрирован: 06.05.2008
Сообщений: 3

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

LazY написал:

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

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

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

Неактивен

 

#5 09.05.2008 12:16:24

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

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

Очень даже можно и средствами БД.
Для этого нужно сделать несколько несложных вещей (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

Неактивен

 

#6 10.05.2008 11:02:31

dekster
Участник
Зарегистрирован: 06.05.2008
Сообщений: 3

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

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

Неактивен

 

#7 22.06.2017 15:32:21

evil-751
Участник
Зарегистрирован: 22.06.2017
Сообщений: 1

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

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson