How to random a zero leading string field mysql [d

问题:

This question already has an answer here:

• Adding a leading zero to some values in column in MySQL 3 answers

I'm not sure if you understand my question correctly. So may I explain a bit. I have a customer table which is needed to test with different number (no matter if it real). So this is the script:

customer_db

+----+------------+
| ID |   NUMBER   |
+----+------------+
| 1  | 0812345678 |
+----+------------+
| 2  | 0812345678 |
+----+------------+
| 3  | 0812345678 |
+----+------------+
.
.
.
|100 | 0812345678 |

According to the table. I ran this script:

UPDATE customer_db SET number = FLOOR(0812345678 + rand()*1000000);

Doing this. I expect the field remains the same format with leading '081' and random the rest 6 digits. But it's not. The table becomes these :

+----+------------+
| ID |   NUMBER   |
+----+------------+
| 1  | 812246797 |
+----+------------+
| 2  | 816548798 |
+----+------------+
| 3  | 815787898 |
+----+------------+
.
.
.
|100 | 813454687 |

It 9 digits instead of 10. Because the leading '0' is missing. What should I do to remain the leading '0' after the random.

回答1:

Like @B-and-P describes in his comment. You can do this using LPAD.

UPDATE 
    customer_db
SET
    number = LPAD(FLOOR(number + rand()*1000000),10,0)

LPAD uses 3 parameters; string, total amount of characters and last but not least which character should be used for padding.

回答2:

As an alternative to @Benz answer, you can try this (works on my end):

INSERT INTO Table1 (`NUMBER`)
VALUES 
(CONCAT('081',rand()*100000000))

table schema

CREATE TABLE IF NOT EXISTS `table1` (
`ID` int(11) DEFAULT NULL,
`NUMBER` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;