如何随机一个零前导字符串字段mysql
我不确定您是否正确理解了我的问题.所以我可以解释一下.我有一个客户表，需要用不同的数字进行测试(无论它是否真实).所以这是脚本:
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);
这样做.我希望该字段保持与前导081"相同的格式，并随机其余 6 位数字.但事实并非如此.表格变成了这些:
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 |
它是 9 位而不是 10 位.因为缺少前导 '0'.我应该怎么做才能在随机后保持领先的0".
It 9 digits instead of 10. Because the leading '0' is missing. What should I do to remain the leading '0' after the random.
推荐答案
就像@B-and-P 在他的评论中描述的那样.您可以使用 LPAD 来做到这一点.
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 使用 3 个参数；字符串，字符总数以及最后但并非最不重要的应该使用哪个字符进行填充.
LPAD uses 3 parameters; string, total amount of characters and last but not least which character should be used for padding.
相关文章