如何获得下一个字母数字ID基于现有的值从MySQL - How to get next alpha-numeric ID based on existing value from MySQL

首先，如果之前有人问过这个问题，我很抱歉——事实上，我肯定有人问过，但我找不到它/不知道要搜索什么才能找到它。

我需要根据公司名称生成唯一的快速参考id。例如:

<>之前公司名称史密斯细木工史密斯和琼斯咨询公司Smithsons地毯smit0003之前

这些都将存储在MySQL表的varchar列中。数据将被收集、转义并像'HTML -> PHP -> MySQL'那样插入。ID应该是上面描述的格式，四个字母，然后四个数字(至少最初-当我到达smit9999时，它会溢出到5个数字)。

我可以处理从公司名称中生成4个字母，我将简单地逐级处理名称，直到我收集了4个alpha字符，并将其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事先?

请注意，实际的代码将是面向对象的，将处理错误等-我只是在寻找是否有更好的方法来完成这个特定的任务的想法，它更多的是关于SQL而不是其他任何东西。

编辑

我认为@EmmanuelN的使用MySQL触发器的建议可能是处理这个问题的方法，但是:

我对MySQL不够好，特别是触发器，让这个工作，并希望一步一步的例子创建，添加和使用触发器。
我仍然不确定这是否会消除生成两个相同ID的可能性。参见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"将在key_inrement中以1开头，key_prefix "jone"将在key_inrement中以1开头，等等。

优点:

你不需要做任何计算数字的事情。

缺点:

你确实有一个跨2列的键分割。
InnoDB不支持

使用触发器和表来唯一保存company_ref的解决方案如何?做了一个更正-如果您希望编号从每个唯一的4char序列的1开始，则引用表必须是MyISAM。

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，为什么不使用显式事务来获取排他性行锁，并防止另一个连接读取同一行之前，你已经完成了基于它设置一个新的ID ?

(当然，在触发器中进行计算会使锁保持的时间更短。)

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:还测试了没有返回初始行的情况(您可能认为没有要加锁的初始行)，这也可以工作。

确保在Reference列上有唯一的约束。
获取当前最大顺序引用的方式与您在示例代码中所做的相同。在转换为(int)之前，您实际上不需要修剪零，'0001'是一个有效的整数。
滚动一个循环并在里面插入。
在插入后检查受影响的行。您还可以检查SQL状态是否存在重复键错误，但是如果没有受影响的行，则很好地表明插入失败是因为插入了现有的Reference值。
如果没有受影响的行，则增加序号，并再次滚动循环。如果您有非零受影响的行，则完成并插入唯一标识符。

避免引用列的重复值的最简单方法是添加唯一约束。因此，如果多个进程尝试设置相同的值，MySQL将拒绝第二次尝试并抛出错误。

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

如果我面对你的情况，我会在应用层内处理公司引用id的生成，如果设置不正确，触发器会变得混乱。

一个可以在InnoDB上运行的hack版本

将对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中的函数