如何基于MySQL的现有值获取下一个字母数字ID [英] How to get next alpha-numeric ID based on existing value from MySQL
问题描述
首先，我很抱歉以前是否曾问过这个问题-确实可以肯定，但是我找不到它/无法找出要查找的内容.
First, I apologize if this has been asked before - indeed I'm sure it has, but I can't find it/can't work out what to search for to find it.
我需要根据公司名称生成唯一的快速参考ID.例如:
I need to generate unique quick reference id's, based on a company name. So for example:
Company Name Reference
Smiths Joinery smit0001
Smith and Jones Consulting smit0002
Smithsons Carpets smit0003
这些都将存储在MySQL表的varchar列中.数据将像"HTML-> PHP-> MySQL"那样被收集，转义和插入. ID的格式应为上述格式，先是四个字母，然后是四个数字(最初至少是-当我到达smit9999时，它会溢出到5位数字).
These will all be stored in a varchar column in a MySQL table. The data will be collected, escaped and inserted like 'HTML -> PHP -> MySQL'. The ID's should be in the format depicted above, four letters, then four numerics (initially at least - when I reach smit9999 it will just spill over into 5 digits).
我可以根据公司名称生成4个字母，我将简单地逐步浏览该名称，直到收集了4个字母字符，然后按strtolower()键-但是我需要获取下一个可用的数字.
I can deal with generating the 4 letters from the company name, I will simply step through the name until I have collected 4 alpha characters, and strtolower() it - but then I need to get the next available number.
什么是最好/最简单的方法，以消除重复的可能性?
What is the best/easiest way to do this, so that the possibility of duplicates is eliminated?
此刻我在想:
$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的公司名称，我会看到这会导致问题.我将在列上使用唯一索引，因此它不会导致数据库重复，但仍会引起问题.
But I can see this causing a problem if two users try to enter company names that would result in the same ID at the same time. I will be using a unique index on the column, so it would not result in duplicates in the database, but it will still cause a problem.
有人可以在我执行插入操作时想到一种让MySQL为我解决这个问题的方法，而不是事先在PHP中进行计算吗?
Can anyone think of a way to have MySQL work this out for me when I do the insert, rather than calculating it in PHP beforehand?
请注意，实际代码将是OO，并且将处理错误等-我只是在寻找是否有更好的方法来执行此特定任务的想法，它比其他任何事情都更涉及SQL.
Note that actual code will be OO and will handle errors etc - I'm just looking for thoughts on whether there is a better way to do this specific task, it's more about the SQL than anything else.
编辑
我认为@EmmanuelN建议使用MySQL触发器可能是处理此问题的方法，但是:
I think that @EmmanuelN's suggestion of using a MySQL trigger may be the way to handle this, but:
- 我对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?.
否则，我将对解决此问题的任何其他建议方法持开放态度.
Or I would be open to any other suggested approaches to this problem.
推荐答案
如果使用的是MyISAM，则可以在文本字段+自动增量字段上创建复合主键. MySQL将自动处理增加的数字.它们是单独的字段，但是您可以获得相同的效果.
If you are using MyISAM, then you can create a compound primary key on a text field + auto increment field. MySQL will handle incrementing the number automatically. They are separate fields, but you can get the same effect.
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_inrement中插入key_prefix"smit"将以1开始，在key_prefix"jone"中插入key_inrement将以1开始，等等.
When you do an insert into the table, the key_increment field will increment based on the highest value based on key_prefix. So insert with key_prefix "smit" will start with 1 in key_inrement, key_prefix "jone" will start with 1 in key_inrement, etc.
优点:
- 您无需对数字进行任何操作.
缺点:
- 您确实有一个分为两列的键.
- 它不适用于InnoDB.
这篇关于如何基于MySQL的现有值获取下一个字母数字ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
