在Mysql中定义函数:生成100万条随机中文姓名

发布于 2020-07-21  155 次阅读


最近在工作中终于遇到了数据量太大以至于必须优化sql的场景,但公司的数据库是不好操作的,所以我在本地数据库中模拟了100w条数据,学到了以前没有接触过的知识点:在mysql中创建函数

随机生成汉字姓名

delimiter $$
CREATE  FUNCTION geUserName() RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE xing varchar(2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';

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

    DECLARE I_xing int DEFAULT LENGTH(xing) / 3;
    DECLARE I_ming int DEFAULT LENGTH(ming) / 3;
    DECLARE return_str varchar(2056) DEFAULT '';

    SET return_str = CONCAT(return_str, substring(xing, floor(1 + RAND() * I_xing), 1));
    SET return_str = CONCAT(return_str, substring(ming, floor(1 + RAND() * I_ming), 1));

    IF RAND() > 0.400 THEN
        SET return_str = CONCAT(return_str, substring(ming, floor(1 + RAND() * I_ming), 1));
    END IF;

    RETURN return_str;
END

原理十分的简单,通过随机数生成随机选取一个姓单字+一或两个名单字,通过字符串拼接合成姓名,作为返回值返回回去;通过实验,在100万条数据相同的姓名不超过5个。

在Mysql中使用函数插入100w条数据

delimiter $$
create function mock_new_data()
returns int
begin
    declare num int default 1000000;
    declare i int default 0;
    while i<num do
        insert into table_name(name, age, bir, uuid) VALUES (geUserName(),FLOOR(100 + RAND() * (1 - 100 + 1)),current_timestamp,UUID());
        set i = i + 1;
        end while ;
    return i;
end $$;

姓名列中插入的数据是通过上面的函数生成的,时间则是mysql自带的常量,UUID也是自带的,唯有年龄是通过一个式子得到的

若要在m ≤ R ≤ n 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(m + RAND() * (n – m + 1))

插入完成后的索引优化

未创建索引前,在100w条数据中执行这样一条sql需要253ms,

select * from table_name where name = '罗轩儿';

创建索引

create index table_name_name_index on table_name(name);

再次执行查询,则只需要53秒,快了5倍;


忍耐无法忍耐的事物,才是真正的忍耐。