MySQL 自动递增自定义值
我正在尝试在 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.
例如000, 001, 002, ..., 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.
我的想法是:
- 为从 0 到 36 的每个字符创建一列，然后将第 N 行(其中 N 是最低有效数字)自动增加 1.然后在每列上添加一个触发器，以便在列时将 1 添加到 N-1 列N 达到 36.
- 创建一个包含 36 行的表格，其中每行包含一个字符 0-Z，并使用与上述类似的进位逻辑从表格中提取适当的字符
- 创建一个存储过程来执行第 1 项中的相应逻辑
- 让实际程序生成一个值并将其插入表中
- 有一个有规律的自动递增值并计算序列中的下一个值(这是最不理想的，因为它使得仅仅在数据库中查看的人难以解析)
我希望有一些优雅的东西可以允许它像一个内置的机制来做到这一点，我只是不知道.我对存储过程/触发器一无所知，因此将不胜感激.我认为最简单的方法是为字符创建一个查找表，当到达第 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 类型或 *text 类型之一(取决于您的 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.
相关文章