MySQL 自动递增自定义值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5228408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Auto Increment Custom Values
提问by Ryan P
I am trying to make a column in a mysql database that auto increments by one but goes from 0-Z and then rolls.
我正在尝试在 mysql 数据库中创建一列，该列自动递增 1，但从 0-Z 开始，然后滚动。
For example 000, 001, 002, ..., 009, 00A, 00B, ..., 00Z, 010, ..., 0ZZ, ..., 100.
例如 000, 001, 002, ..., 009, 00A, 00B, ..., 00Z, 010, ..., 0ZZ, ..., 100。
I would like to have the database create the column through an auto incrementing field.
我想让数据库通过自动递增字段创建列。
The ideas I have are:
我的想法是：
- Create a column for each character that goes from 0-36, then auto increment row N (where N is the least significant digit) by 1. Then add a trigger on each column to add 1 to column N-1 when column N reaches 36.
- Create a table with 36 rows where each row contains a character 0-Z and pull the appropriate character from the table with similar carry logic from the above
- Create a stored procedure to do the appropriate logic from item 1
- Have the actual program generate a value and insert it into the table
- have a regular auto incrementing value and calculate the next value in the sequence (this is the least optimal as it makes it difficult to parse by a person just looking in the database)
- 为每个从 0 到 36 的字符创建一列，然后将第 N 行（其中 N 是最低有效数字）自动增加 1。然后在每列上添加一个触发器，当 N 列达到 36 时将 1 添加到 N-1 列.
- 创建一个包含 36 行的表，其中每行包含一个字符 0-Z，并使用与上述类似的进位逻辑从表中提取适当的字符
- 创建一个存储过程以执行第 1 项中的相应逻辑
- 让实际程序生成一个值并将其插入表中
- 有一个规则的自动递增值并计算序列中的下一个值（这是最不理想的，因为它使得仅仅在数据库中查看的人难以解析）
I was hoping that there was something elegant which would allow for this like a built in mechanism to do this that I just do not know. I have no knowledge on stored procedures / triggers so help with it would be greatly appreciated. I think the easiest way would be to have a lookup table for the characters and when row 36 is reached it is reset to 0 and then there is a carry to row N-1.
我希望有一些优雅的东西可以让这个像一个内置的机制来做到这一点，我只是不知道。我对存储过程/触发器一无所知，因此将不胜感激。我认为最简单的方法是为字符创建一个查找表，当到达第 36 行时，它被重置为 0，然后有一个到第 N-1 行的进位。
采纳答案by Stephen
Based on your comments, my recommendation is to do the following:
根据您的意见，我的建议是执行以下操作：
Use a regular integer auto_increment column as the primary key for the row, and then have a column of type varchar or one of the *text types (depending on your mysql server version and data storage requirements) to store your "identifier" that the customer uses.
使用常规整数 auto_increment 列作为行的主键，然后使用 varchar 类型或 *text 类型之一（取决于您的 mysql 服务器版本和数据存储要求）的列来存储客户的“标识符”使用。
The identifier can be auto-generated using a trigger.
标识符可以使用触发器自动生成。
If you're going to do lookups based on the identifier (i.e. perhaps the user enters an identifier to "jump to" a record) you will want an index on that column.
如果您要根据标识符进行查找（即，用户可能输入一个标识符以“跳转到”记录），您将需要该列上的索引。
回答by Ivan Cachicatari
You can generate custom auto-increment values using stored procedures, as described in:
您可以使用存储过程生成自定义自动递增值，如下所述：
http://en.latindevelopers.com/ivancp/2012/custom-auto-increment-values/
http://en.latindevelopers.com/ivancp/2012/custom-auto-increment-values/
You will get keys like:
你会得到像这样的钥匙：
SELECT * FROM custom_autonums;
+----+------------+------------+
| id | seq_1 | seq_2 |
+----+------------+------------+
| 4 | 001-000001 | DBA-000001 |
| 5 | 001-000002 | DBA-000002 |
| 6 | 001-000003 | DBA-000003 |
| 7 | 001-000676 | DBA-000004 |
| 8 | 001-000677 | DBA-000005 |
| 9 | 001-000678 | DBA-000006 |
But you can modify the stored procedures to generate your pattern: 000, 001, 002, ..., 009, 00A, 00B, ..., 00Z, 010, ..., 0ZZ, ..., 100
但是您可以修改存储过程以生成您的模式：000, 001, 002, ..., 009, 00A, 00B, ..., 00Z, 010, ..., 0ZZ, ..., 100
