How to insert all the permutations of xxxxx number in MySQL?

Question

I have a table called "numbers"

id (int, auto-increment)

number (varchar)

How can I insert all permutations of a number contating 5 digits [0-9] ?

00000 00001 00002 ... 99999

Answer 1

LPAD(str,len,padstr) - Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD(123, 5,'0');
        -> 00123

MySQL Documentation. 12.5 String Functions. function LPAD

Answer 2

Here is a select statement generating numbers from 0 to 99999 including 0 left padding. Then you can use this with INSERT statement to insert them where you want in a single shot.

SELECT LPAD(F.t*10000+E.num3,5,'0') num FROM
(
    SELECT 100*num1+num2 num3
    FROM (
    SELECT t*10+u num1
    FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B
    ) C,
    (
    SELECT t*10+u num2
    FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B
    ) D
) E,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) F

ORDER BY num

Output is:

+-------+
|  num  |
+-------+
| 00000 |
| 00001 |
| 00002 |
| ...   |
| 99999 |
+-------+