Форум MySQL

Итак поставлена задача
Необходимо получить нарастающую последовательность номера
по таблице InnoDB, при этом не должно быть пропусков номеров и ни в коем случае не должно быть задвоений. (При вставке новой записи с новым GroupID должна получить новый номер +1)

CREATE TABLE T1(   PNum INT(11) UNSIGNED DEFAULT NULL,   ID VARCHAR(25) NOT NULL DEFAULT '',   GroupID VARCHAR(25) DEFAULT NULL   PRIMARY KEY (ID),   INDEX GroupID (GroupID),   INDEX PNum (PNum) ) ENGINE = INNODB CHARACTER SET cp1251 COLLATE cp1251_general_ci;

Как бы это получить без существенного снижения производительности.
Т.е. я делаю триггер примерно следующего вида.

CREATE DEFINER = 'root'@'localhost' TRIGGER T1_before_insert BEFORE INSERT ON T1 FOR EACH ROW BEGIN   DECLARE NumUD  INT;     SELECT  MAX(PNum) + 1  INTO  Num_D   FROM   T1;     IF isnull(Num_D) THEN       SET Num_D =1;     END IF;     SET NEW.PNum = Num_D;   END IF; END

Это тестовый пример в реальности там еще добавляется куча условий.
Однако боюсь, что пока отрабатывается вставка одной записи т.е. до COMMITA, вторая запись так же получить этот же номер.

Думал на тему лочить переменную со вставляемым номером, а вторая запись будет при неудачном Get_lock() по циклу наращивать номер до того времени пока не получит блокировку, но при Rollback первой записи будет появляться пропуск.
Сделать
SELECT MAX(PNum) + 1 INTO Num_D FROM T1 FOR UPDATE;
Тоже вроде как нет гарантии что после получения максимального значений кто нибудь его еще раз не получит до окончания вставки первой записи.
Подскажите в какую сторону смотреть ?
В крайнем случае лучше получить 1 пропуск на 1000 записей чем 1 задвоение на эту же тысячу.

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

Цель автоматическое присвоение номера.
Понятно что можно с клиента
сделать MAX(Pnum)
Попытаться залочить переменную с этим номером
если не удалось то по циклу прибавляем +1
делаем SELECT на предмет наличия этого номера
если есть то в цикл
если нет то пытаемся опять лочить переменную
и до тех пор пока не удалось залочить
потом делаем встаку на сервер
после чего делаем RELEASE_LOCK переменнной

Но во первых что то боязно с переменными связываться, во вторых возможен пропуск (но это лучше чем задвоение)
p.s. уникальный индекс на номер повешать не могу т.к. в пределах GroupID номер может повторятся. (история корректировок)
На выводе не устраивает т.к. номер должен присвоиться записи ( в реальном виде он даже имеет тип VARCHAR и первые 6 символов присваиваются на клиенте они же еще участвуют при
SELECT MAX(PNum)+1 FROM T1 WHERE PNum LIKE CONCAT(New.PNum,'%')
в триггере.
Но возможность задвоения меня настораживает.
Поэтому и спросил кто что посоветует, может решение более идеальное чем то что я привел выше (получение этого номера с клиента)

>Цель автоматическое присвоение номера.
А каково назначение номера? Почему важно, чтобы не было пропусков? Ведь существует еще проблема удаления строк, что должно быть с этими номерами, если из середины таблицы будет удалена запись?

приложение построено так, что физического удаления не предусмотрено (история корректировок и удалений) хранится в таблице. При удалении просто ставится флаг что запись удалена.

Тогда непонятно требование неразрывности нумерации... если будут удаленные записи (пусть даже не физически) нумерация активных записей все-равно получится разрывной. В этом случае вам проще всего воспользоваться атрибутом AUTO_INCREMENT, помеченный им столбец автоматически генерирует следующее число при передаче в INSERT значения NULL (делает это очень быстро, гарантирует отсутствие дублей). Эти числа никогда не повторяются, правда, они не гарантируют неразрывность нумерации (но она у вас и по схеме не гарантируется).

Ну хорошо
А если сделать после того как произведена вставка
проверку на дубль

SELECT DISTINCT GroupID FROM T1 WHERE Pnum=Полученный_номер;

и если будет возвращено количество записей больше 1
то делаем

UPDATE T1,   (SELECT max(PNum)+1 AS maxNum    FROM      T1) AS tmp_t1 SET   T1.PNum = maxNum WHERE   T10.ID = 'Значение_ID';

Как этот вариант ?

Если опасаетесь дублей - проще проиндексировать столбец уникальным ключом - это гарантирует их отсутствие.

Я уже писал что проиндексировать не получится
т.к. PNUM не может быть уникален,
т.е. он может повторяться в пределах группы по GroupID
но при этом он не должен повторяться в разных GroupID

тогда нужно общий индекс на два поля

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

Однако, опять же не понятно, почему хотите добиться уникальности именно в пределах группы, а не таблицы. Я не спорю, есть такие задачи (правда очень редко и всеми правдами и неправдами стараются их вынести с физического столбца базы данных). Однако, если не менять условия скорее всего в плане производительности вы ничего лучше того, что в первом посте не придумаете... и тут кстати, индекс по двум полям вам может здорово пригодиться в плане ускорения MAX(PNum).

Индекс тут не поможет

PNum GroupID ID
1 X1 123
1 X1 124
1 X1 125
2 O1 126
2 O1 127
3 Y1 128
3 Y1 129
3 Y1 130
все указанное выше правильно (для системы), теперь
вставляется ошибочный номер (тот который уже есть но с другим GroupID)
2 N1 131
Вот я не вижу тут какой идекс не даст этого сделать даже составной
Составной уникальный PNum+GroupID не даст вставить из первого списка уже вторую запись
но при этом ошибочную запись пропустит на ура.
А по поводу ускорения ну да помог бы не уникальный составной GroupID+ID если бы строился
запрос типа
SELECT MAX(ID) FROM T1 WHERE GROUP ID= ......
А в приведенном примере (т.е. по требованию к системе такого нет).
Ну что ж спасибо за диалог
Для себя сделал следующий выход на триггере до вставки определяется вариант номера с учетом того что есть в таблице (как это приведено в первом посте)
После чего делается Commit
и тут же выполняется хранимая функция (с передачей ей идентификатора записи),
которая делает проверку на дубликат если проверка прошла удачно, то возвращается присвоеннй номер. Если выявлен дубликат то делается UPDATE на новый Max(PNUM) уже в цикле по окончания выдается новый номер PNUM
Пока выход только такой.

В этой системе
PNum GroupID ID
1 X1 123
1 X1 124
1 X1 125
2 O1 126
2 O1 127
3 Y1 128
3 Y1 129
3 Y1 130
Вы для какого столбца выбираете номера? По приведенной в первом посте схеме? ID? А разве он не должен выглядеть так?
PNum GroupID ID
1 X1 1
1 X1 2
1 X1 3
2 O1 1
2 O1 2
3 Y1 1
3 Y1 2
3 Y1 3

нет не должен - ID это просто идентификатор записи в таблице он уникален по всей таблице.
первый столбец - это и есть номер
второй столбец - это идентификатор группы
а третий это ID Он по всей таблице уникален

Оп-па намек понял
нужен порядковый номер записи в группе теоретически тогда да
если брать индекс

PNum + номер записи в группе (для первой записи он всегда будет 1 ) то да вроде как неплохой выход

Спасибо за наводку что то я сразу ступил

да не все так просто
порядковый номер может сопадать для удаленных карточек
и удаленных кариточек может быть больше чем одна
( в крайнем случае в системе сейчас так есть )
Это придется с ними как то бодаться

Возможно, я чего-то не понял...
Почему не использовать PK и autoincrement?

Кстати, как сделать, чтобы при этом отсчёт начинался с нулевого значения, а не с единицы?

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

>Кстати, как сделать, чтобы при этом отсчёт начинался с нулевого значения, а не с единицы?
>зачем? вас никоим образом не должен колебать идентификатор строки

Да он меня и не колебает вовсе...
Просто у меня в движке сайта используются таблицы тем и материалов (по темам, ессно).
Заполнил таблицы. Проверил работу движка, а потом решил в материалы помещать варианты главной страницы. Чтобы не нарушать идею работы алгоритма думал для них использовать нулевое значение поля связи с таблицей тем, где соответствующее поле с автоинкрементом. А меньше единицы уже никак...
Задачу-то я решил, но красота решения пострадала.

Так что вопрос, в общем, теоретический. Можно, конечно, перезагрузить содержимое таблиц заново с учётом упомянутого изменения.
Но удивило меня, что везде отсчёт с нуля начинается, а тут... Неожиданность, короче, для меня.