Mysql之生成随机数,随机字符串,随机姓名

版权声明:本文为CSDN博主「BlueKitty1210」的原创
原文链接:https://blog.csdn.net/xingbaozhen1210/java/article/details/87268621

 


-- 随机 0-1
select floor(rand() * 2);

-- 随机布尔值
select if(floor(rand() * 2) = 1 , '是' , '否');

-- 随机2位小数
select convert(floor(rand() * 70 + 30) / 100,decimal(10,2));

-- 随机N位字符串
select substr(md5(rand()),1,6);

-- 实际应用 , 为某个字段随机赋值
update tb_name
set is_delete = floor(rand() * 2);

-- 随机姓名 可根据需要增加/减少样本
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';

set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';

-- length(@surname)/3 是因为中文字符占用3个长度
select concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));

update user 

set real_name= concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));

————————————————

posted @ 2020-07-13 20:16  Summer_二东  阅读(61)  评论(0编辑  收藏