如何随机一个零前导字符串字段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.

这篇关于如何随机一个零前导字符串字段mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！