MySQL自动递增自定义值 [英] MySQL Auto Increment Custom Values

问题描述

我想在一个mysql数据库中创建一个列，自动从1增加，但从0-Z，然后滚动。

I am trying to make a column in a mysql database that auto increments by one but goes from 0-Z and then rolls.

例如
...，009,00A，00B，...，00Z，010，...，0ZZ，...，100。

For example 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.

我的想法是：

1. 为从0-36开始的每个字符创建一列，然后自动增加行N（其中N是最低有效数字）。然后在每列上添加一个触发器以添加1


2. 创建一个包含36行的表，其中每行包含一个字符0-Z，并使用类似的进位逻辑从表中拉出相应的字符上面


3. 创建存储过程以从第1项执行相应的逻辑


4. 让实际程序生成一个值并将其插入表


5. 有一个常规的自动递增值，并计算序列中的下一个值（这是最不理想的，因为它使得难以由只在数据库中查看的人进行解析）

1. 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.
2. 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
3. Create a stored procedure to do the appropriate logic from item 1
4. Have the actual program generate a value and insert it into the table
5. 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)

我希望有一个优雅的东西，这将允许像一个内置的机制，这样做，我只是不知道。我不知道存储过程/触发器，所以帮助它将非常感谢。我认为最简单的方法是有一个字符的查找表，当到达行36时，它被重置为0，然后进入行N-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.

推荐答案

根据您的意见，我的建议是执行以下操作：

Based on your comments, my recommendation is to do the following:

使用常规整数auto_increment列作为主键，然后有一个类型为varchar的列或一个*文本类型（取决于您的mysql服务器版本和数据存储要求），以存储客户使用的标识符。

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.

标识符可以使用触发器自动生成。

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.

这篇关于MySQL自动递增自定义值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！