Как получить следующий буквенно-цифровой идентификатор на основе существующего значения из MySQL

Если вы используете MyISAM, вы можете создать составной первичный ключ в текстовом поле + поле автоматического увеличения. MySQL будет обрабатывать увеличение числа автоматически. Это отдельные поля, но вы можете получить тот же эффект.

 CREATE TABLE example ( company_name varchar(100), key_prefix char(4) not null, key_increment int unsigned auto_increment, primary key co_key (key_prefix,key_increment) ) ENGINE=MYISAM; 

Когда вы вставляете в таблицу, поле key_increment будет увеличиваться на основе самого высокого значения на основе key_prefix . Поэтому вставка с key_prefix «smit» начнется с 1 в key_inrement , key_prefix «jone» начнется с 1 в key_inrement и т. Д.

Плюсы:

• Вам не нужно ничего делать с вычислением чисел.

Минусы:

• У вас есть ключ, разделенный на 2 столбца.
• Он не работает с InnoDB.

Как насчет этого решения с триггером и таблицей, чтобы держать company_ref уникально. Сделал коррекцию – эталонная таблица должна быть MyISAM, если вы хотите, чтобы нумерация начиналась с 1 для каждой уникальной последовательности 4char.

 DROP TABLE IF EXISTS company; CREATE TABLE company ( company_name varchar(100) DEFAULT NULL, company_ref char(8) DEFAULT NULL ) ENGINE=InnoDB DELIMITER ;; CREATE TRIGGER company_reference BEFORE INSERT ON company FOR EACH ROW BEGIN INSERT INTO reference SET company_ref=SUBSTRING(LOWER(NEW.company_name), 1, 4), numeric_ref=NULL; SET NEW.company_ref=CONCAT(SUBSTRING(LOWER(NEW.company_name), 1, 4), LPAD(CAST(LAST_INSERT_ID() AS CHAR(10)), 4, '0')); END ;; DELIMITER ; DROP TABLE IF EXISTS reference; CREATE TABLE reference ( company_ref char(4) NOT NULL DEFAULT '', numeric_ref int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (company_ref, numeric_ref) ) ENGINE=MyISAM; 

И для полноты здесь есть триггер, который создаст новую ссылку, если название компании будет изменено.

 DROP TRIGGER IF EXISTS company_reference_up; DELIMITER ;; CREATE TRIGGER company_reference_up BEFORE UPDATE ON company FOR EACH ROW BEGIN IF NEW.company_name <> OLD.company_name THEN DELETE FROM reference WHERE company_ref=SUBSTRING(LOWER(OLD.company_ref), 1, 4) AND numeric_ref=SUBSTRING(OLD.company_ref, 5, 4); INSERT INTO reference SET company_ref=SUBSTRING(LOWER(NEW.company_name), 1, 4), numeric_ref=NULL; SET NEW.company_ref=CONCAT(SUBSTRING(LOWER(NEW.company_name), 1, 4), LPAD(CAST(LAST_INSERT_ID() AS CHAR(10)), 4, '0')); END IF; END; ;; DELIMITER ; 

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

(Естественно, при вычислении в триггере будет удерживаться блокировка за меньшее время.)

 mysqli_query($link, "BEGIN TRANSACTION"); $query = "SELECT `company_ref` FROM `companies` WHERE `company_ref` LIKE '$fourLetters%' ORDER BY `company_ref` DESC LIMIT 1 FOR UPDATE"; $last = mysqli_fetch_assoc(mysqli_query($link, $query)); $newNum = ((int) ltrim(substr($last['company_ref'],4),'0')) + 1; $newRef = $fourLetters.str_pad($newNum, 4, '0', STR_PAD_LEFT); mysqli_query($link, "INSERT INTO companies . . . (new row using $newref)"); mysqli_commit($link); 

Изменить: просто чтобы быть уверенным на 100%, я проверил тест вручную, чтобы подтвердить, что вторая транзакция вернет вновь вставленную строку после ожидания, а не в исходную заблокированную строку.

Edit2: Также проверен случай, когда исходная строка не возвращается (там, где вы думаете, что нет начальной строки для блокировки), и это тоже работает.

1. Убедитесь, что у вас есть уникальное ограничение в столбце «Справка».
2. Извлеките текущую максимальную последовательную ссылку так же, как вы ее делаете в своем примере кода. На самом деле вам не нужно обрезать нули перед тем, как применить к (int), «0001» – действительное целое число.
3. Прокрутите петлю и сделайте свою вставку внутри.
4. Проверьте затронутые строки после вставки. Вы также можете проверить состояние SQL для дублирования ключевой ошибки, но наличие нулевых строк является хорошим показателем того, что ваша вставка завершилась неудачно из-за вставки существующего ссылочного значения.
5. Если у вас есть нулевые строки, увеличьте порядковый номер и повторите цикл. Если у вас есть ненулевые затронутые строки, вы закончили и ввели уникальный идентификатор.

Самый простой способ избежать дублирования значений для ссылочного столбца – добавить уникальное ограничение. Поэтому, если несколько процессов пытаются установить одно и то же значение, MySQL отклонит вторую попытку и выдаст ошибку.

 ALTER TABLE table_name ADD UNIQUE KEY (`company_ref`); 

Если бы я столкнулся с вашей ситуацией, я бы справился с созданием ссылочного идентификатора компании на прикладном уровне, триггеры могут стать беспорядочными, если не настроены правильно.

Хакерная версия, которая работает и для InnoDB.

Замените вставку на companies с двумя вставками в транзакции:

  INSERT INTO __keys VALUES (LEFT(LOWER('Smiths Joinery'),4), LAST_INSERT_ID(1)) ON DUPLICATE KEY UPDATE num = LAST_INSERT_ID(num+1); INSERT INTO __companies (comp_name, reference) VALUES ('Smiths Joinery', CONCAT(LEFT(LOWER(comp_name),4), LPAD(LAST_INSERT_ID(), 4, '0'))); 

где:

  CREATE TABLE `__keys` ( `prefix` char(4) NOT NULL, `num` smallint(5) unsigned NOT NULL, PRIMARY KEY (`prefix`) ) ENGINE=InnoDB COLLATE latin1_general_ci; CREATE TABLE `__companies` ( `comp_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `comp_name` varchar(45) NOT NULL, `reference` char(8) NOT NULL, PRIMARY KEY (`comp_id`) ) ENGINE=InnoDB COLLATE latin1_general_ci; 

Обратите внимание:

• latin1_general_ci можно заменить utf8_general_ci ,
• LEFT(LOWER('Smiths Joinery'),4) лучше станет функцией в PHP