MySQL : incrementing text id in DB

I need to have text IDs in my application. For example, we have acceptable charset azAZ09, and allowed range of IDs [aaa] - [cZ9]. First generated id would be aaa, then aab, aac, aad e.t.c.

How one can return ID & increment lower bound in transaction-开发者_运维技巧fashion? (provided that there are hundreds of concurrent requests and all should have correct result)

To lower the load I guess it's possible to define say 20 separate ranges, and return id from random range - this should reduce contention, but it's not clear how to do single operation in the first place.

Also, please note that number of IDs in range might exceed 2^32.

Another idea is having ranges of 64-bit integers, and converting integer->char id in software code, where it could be done asyncroniously.

Any ideas?

A solution based on long (bigint) numbers, with chars-to-number and number-to-chars conversions done in software, having - say - 32 differents ranges,

• creation of 32 tables range0 .. range31 to lower the load (a unique table with 32 range fields would have its row locked each time a range is written to)

• starting the ranges by setting the auto_increment to a very high value on 64 bits - maybe 63 if you want to avoid the sign problem in your application. Ideally the range value on 5 bits, from 0 to 31. 1 bit sign + 5 bits range = 6 bits. You counter will be from 1 to (2^58-1) or 10^17... that should be enough.

• in software the range number is selected randomly, and the query table name is built accordingly rangei where i goes from 0 to 31.

The create tables commands would be something like, say in Java

  String table;
  String query;
  long increment;

  for (long i=0 ; i<32 ; i++) {
    table = "range"+i;
    increment = (i<<58) + 1;
    query = "CREATE TABLE "+table+" (v bigint auto_increment primary key) auto_increment="+increment;

    do_query(query);
  }

I'd ask a different question to make the same problem easier to solve: how can I use the features of my database to implement my requirement.

Look at what your database offers in terms of managing the sequences. Some of them may handle something like this. If you database doesn't, I'd look at "piggy backing" on it's normal numeric keys, since they already solve all the problems you are anticipating.

What I means is this: Let the database hand out numeric keys as it normally does, starting at 0. Then write two functions to convert between those numbers and your text keys. You have some flexibility here: you can implement this function in the database (using a second column, triggers, or some cool database feature), or at your server layer, like Java. It won't be that complicated. In the end, it's all bits anyhow... you text key is just a different way to slice up the bits.

Good luck!