4

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:

  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)

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.

Ryan P
  • 6,461
  • 5
  • 25
  • 20

1 Answers1

3

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.

Stephen
  • 18,597
  • 4
  • 32
  • 33
  • 1
    Have you tried this? The auto-increment value isn't generated until after the `BEFORE` triggers have run, and it's too late to modify any columns in the `AFTER` triggers. – Bill Karwin May 26 '14 at 23:21
  • you can certainly modify columns in the AFTER trigger - `UPDATE foo set bar='bas' where id=NEW.id` – Stephen May 28 '14 at 06:49
  • 1
    `ERROR 1442 (HY000): Can't update table 'foo' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.` – Bill Karwin May 28 '14 at 07:04
  • You can of course modify columns in *other* tables, but not the table for which the trigger fired. – Bill Karwin May 28 '14 at 07:05
  • Hmm. I need to find where I implemented something similar to remind me how I actually did it. – Stephen May 28 '14 at 18:23