MySQL 自动递增自定义值
我正在尝试在 mysql 数据库中创建一列，该列自动递增 1，但从 0-Z 开始，然后滚动。
例如 000, 001, 002, ..., 009, 00A, 00B, ..., 00Z, 010, ..., 0ZZ, ..., 100。
我想让数据库通过自动递增创建列场地。
我的想法是：
- 为从 0 到 36 的每个字符创建一列，然后将第 N 行（其中 N 是最低有效数字）自动递增 1。然后在每列上添加一个触发器，将 1 添加到第 N 列 -当第 N 列达到 36 时，为 1。
- 创建一个包含 36 行的表，其中每行包含一个字符 0-Z，并使用与上面类似的进位逻辑从表中提取适当的字符
- 第 1 项中的适当逻辑
- 。创建一个存储过程以执行 实际程序生成一个值并将其插入表中，
- 具有定期自动递增值并计算序列中的下一个值（这是最不理想的，因为它使得仅在数据库中查找的人难以解析）
我是希望有一些优雅的东西可以允许像内置机制一样做到这一点，我只是不知道。我对存储过程/触发器一无所知，因此我们将不胜感激。我认为最简单的方法是建立一个字符查找表，当到达第 36 行时，它会重置为 0，然后进位到第 N-1 行。
I am trying to make a column in a mysql database that auto increments by one but goes from 0-Z and then rolls.
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.
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)
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据您的评论，我的建议是执行以下操作：
使用常规整数 auto_increment 列作为行的主键，然后有一列类型为 varchar 或 *text 类型之一（取决于您的 mysql 服务器版本和数据存储要求）来存储客户使用的“标识符”。
可以使用触发器自动生成标识符。
如果您要根据标识符进行查找（即用户可能输入标识符以“跳转到”记录），您将需要该列上的索引。
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.
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.