mysql如何随机化零前导字符串字段 [英] How to random a zero leading string field 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如何随机化零前导字符串字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!