I have to generate a combinations of 6 alphanumerics characters between 000000 and ZZZZZZ. Of course, I also want the combinations with mixed numbers and letters.

Example :

000000      
000001      
000002      
...         
ZZZZZZ      

How can I do that in MySQL please ?

CodePudding user response：

It is probably not the right approach to actually create and store this list, as it will be 36⁶ = 2,176,782,336 rows (yes, that's over 2 billion) rows long.

Instead, I can offer you the next best thing - a way to get the n-th item of the "list" on the fly!

What you are looking for is essentially a number system in base 36 (using the 10 digits and 26 letters as symbols to represent the number's value instead of only the digits as you would have in base 10). It's similar to hexadecimal (base 16), but instead of using 0-9 and A-F it uses 0-9 and A-Z.

Luckily, MySQL already has a function convert numbers between different bases, and that is CONV. You can instruct it to convert your index from base 10 to base 36 and you'll get what you need - and as final touch, you can use LPAD to add leading zeroes to the values that would otherwise have less than six characters:

SELECT LPAD(CONV(12345678, 10, 36), 6, '0');

07CLZI

CodePudding user response：

If you're using MySQL 8.x you can do it with a CTE that you join with itself 6 times.

with chars as (
    SELECT '0' AS c
    UNION ALL
    SELECT '1' AS c
    UNION ALL
    SELECT '2' AS c
    ...
    SELECT 'A' AS c
    UNION ALL
    SELECT 'B' AS c
    ...
    UNION ALL
    SELECT 'Z' AS c
)
SELECT CONCAT(c1.c, c2.c, c3.c, c4.c, c5.c, c6.c) AS result
FROM chars AS c1
CROSS JOIN chars AS c2
CROSS JOIN chars AS c3
CROSS JOIN chars AS c4
CROSS JOIN chars AS c5
CROSS JOIN chars AS c6

If you're using 5.x you can define chars as a view, then join it the same way.