0

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?

1
  • Integers already fit your requirement of "a textual representation of letters and number (only!)". Why do you need to bring letters into the mix? Why do you need to display meaningless (to the end user) numbers, or even more meaningless letters?
    – mustaccio
    Jul 15, 2016 at 15:07

1 Answer 1

2

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.

4
  • Interesting, but with the data I have for now it seems to not have a fixed length. Although this approach does solve the issue, for initial records, with low IDs, looks weird to display one or two letters/numbers Jul 15, 2016 at 15:20
  • Add a large(ish) number to each one then, solves that problem
    – Philᵀᴹ
    Jul 15, 2016 at 15:21
  • But, in that case would be much better to use a GUID Jul 15, 2016 at 15:22
  • Up to you. I've given you a easily-used alternative...
    – Philᵀᴹ
    Jul 15, 2016 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.