0

Is it possible to natively auto-increment letters in MySql? If not can you suggest and alternative based on the description of my application below?

Background:

I am building a Warehouse management application. In order to locate items within the warehouse I think I will number shelf columns 1,2,3.. and shelf rows with a,b,c,...aa,ab,ac.... To locate a particular cell in a shelf you would get a combination of the two, for exmaple, the widgets are located at '4d'. To do this I will have a ShelfColumn table with an auto-incrementing primary keys and a ShelfRow table with auto-incrementing letters. An associative table will join the two to form the unique shelf reverences to locate my goods.

andrew
  • 5,096
  • 10
  • 43
  • 57

2 Answers2

2

No, you cannot auto-increment letters, nor should you need to.

Quick story about auto_increment PK in MySQL: it's special in that sense that you should never rely on its behaviour. It is also used by InnoDB to determine how to physically save data to the hard drive.

Therefore, it's the best if you just let auto_increment increment numbers, without interfering and trying to add letters to it.

On the other hand, what's the need for labelling shelf rows with letters? I see no reason why not to label it by number. You'd get a nice matrix out of it, and anyone would know instantly what Column 10 Row 25 is and how to locate it.

N.B.
  • 13,688
  • 3
  • 45
  • 55
  • You would also usually need to know the actual 'aisle' as well, in places I have worked these have always been identified using a letter but as you said there is no reason you couldn't do `(aisle | row | column) => (3 | 10 | 4)` – T I Jan 26 '12 at 10:33
  • I guess I could use just numbers. It's just that letters make it easier for people to use the system. They understand that letters are used for aisles and there is less confusion. Does anyone else have suggestions on how to categorise locations in a warehouse, bearing in mind that like a lot of warehouse it is not just clean shelves with uniform size areas. There are also some lofts and drawers etc. – andrew Feb 01 '12 at 20:36
1

I don't think autoincrement would support this sort of functionality. After all it would have to know about every language and every character set to know what to autoincrement to, as well as what it should do when it gets to the last character in a given alphabet, never mind what do do with non-character symbols (punctuation, whitespace, control-codes, etc).

You'll either have to implement letter selection in your application logic, or use a numerical value for your rows and convert them to letters on output (a for 1, b for 2, aa for 27, ab for 28, etc).

GordonM
  • 31,179
  • 15
  • 87
  • 129