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

Во-первых, я прошу прощения, если это было задано раньше – действительно, я уверен, что это так, но я не могу найти / не могу решить, что искать, чтобы найти его.

Мне нужно создать уникальный идентификатор быстрой ссылки, основанный на названии компании. Так, например:

 Название компании
 Сплит-столярные изделия smit0001
 Смит и Джонс Консалтинг smit0002
 Ковры Smithsons smit0003

Все они будут храниться в столбце varchar в таблице MySQL. Данные будут собраны, экранированы и вставлены как «HTML -> PHP -> MySQL». Идентификаторы должны быть в формате, указанном выше, четыре буквы, затем четыре цифры (изначально по крайней мере – когда я smit9999 до smit9999 он просто перейдет на 5 цифр).

Я могу справиться с созданием 4 букв из названия компании, я просто strtolower() через это имя, пока не strtolower() 4 альфа-символа, и strtolower() но тогда мне нужно получить следующий доступный номер.

Каков наилучший / простой способ сделать это, чтобы исключить возможность дублирования?

На данный момент я думаю:

 $fourLetters = 'smit'; $query = "SELECT `company_ref` FROM `companies` WHERE `company_ref` LIKE '$fourLetters%' ORDER BY `company_ref` DESC LIMIT 1"; $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); 

Но я вижу, что это создает проблему, если два пользователя попытаются ввести названия компаний, которые приведут к тому же ID в одно и то же время. Я буду использовать уникальный индекс в столбце, поэтому он не приведет к дублированию в базе данных, но это все равно вызовет проблему.

Может ли кто-нибудь подумать о том, как заставить MySQL работать для меня, когда я вставляю, а не вычислять его в PHP заранее?

Обратите внимание, что фактический код будет OO и будет обрабатывать ошибки и т. Д. – Я просто ищу мысли о том, есть ли лучший способ выполнить эту конкретную задачу, это больше о SQL, чем о чем-либо еще.

РЕДАКТИРОВАТЬ

Я думаю, что предложение EmmanuelN об использовании триггера MySQL может быть способом справиться с этим, но:

  • Я недостаточно разбираюсь в MySQL, особенно с триггерами, чтобы заставить это работать, и хотел бы поэтапный пример создания, добавления и использования триггера.
  • Я все еще не уверен, приведет ли это к исключению возможности создания двух идентичных идентификаторов. Посмотрите, what happens if two rows are inserted at the same time that result in the trigger running simultaneously, and produce the same reference? Is there any way to lock the trigger (or a UDF) in such a way that it can only have one concurrent instance? what happens if two rows are inserted at the same time that result in the trigger running simultaneously, and produce the same reference? Is there any way to lock the trigger (or a UDF) in such a way that it can only have one concurrent instance? ,

Или я был бы открыт для любых других предложенных подходов к этой проблеме.

Если вы используете 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