MySQL AlphaID Function

MySQL

While trying to optimize a few queries to not do with PHP something I could bring ready-to-use from database, I tried to convert two routines originally created in PHP to encode an integer in its AlphaID representation and then back, from string to integer.

Let's take a deep breath before this can be considered as a Stack Overflow question, I asked before posting

I tried to port them by myself but there were a few problems:

  1. The original routine, in PHP, was able to encode correctly up to the maximum possible value fo a 32bits integer (2147483647), but for some reason my port stops earlier. I wasn't capable to debug further but in some moment after 2000000000 doesn't encode anymore.
  2. Up to the point in which the function works, the encoding is always "one ahead', for example, encoding the integer 2, instead of returning the expected baaac is resulting in baaad, the equivalent of the integer 3 when encoded

I've got suggestions to use AES_ENCRYPT() and its counterpart to decrypt, but that's not the purpose because what I really need, as a requirement of the project, is to display something better characterized as a PRIMARY KEY in a textual representation of letters and number (only!)

I also had suggestions to use hashing functions and I tested MD5(), SHA1() and all combinations of SHA2() but all of them result in very long strings which would be difficult to display on web environment.

And, of course, being able to do such conversion straight in the database would allow me to change INT for BIGINT and have up to 9,223,372,036,854,775,807 records.

So, that said, is there a way to represent integer as AlphaID with MSQL? Or something similar then, I'm open to suggestions.

If not something built-in, and maybe this could be considered as off-topic, how could I fix the implementations I made?

Best Answer

The easiest way to do this with built-in functions would be to just convert the number into base 36:

mysql> select conv(9223372036854775807,10,36);
+---------------------------------+
| conv(9223372036854775807,10,36) |
+---------------------------------+
| 1Y2P0IJ32E8E7                   |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

This allows use of 0-9 A-Z, and is the largest base supported by CONV().

To convert back:

mysql> select conv('1Y2P0IJ32E8E7', 36, 10);
+-------------------------------+
| conv('1Y2P0IJ32E8E7', 36, 10) |
+-------------------------------+
| 9223372036854775807           |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

Documentation for CONV here.

See here, if you don't know how bases work.

Related Question