1、首先建立一张需要的随机测试数据表美高梅网

2020-01-22 11:42栏目:美高梅网上游戏
TAG:

复制代码 代码如下: BEGIN DECLARE `@i` int(11); DECLARE `@siteCount` int(11); DECLARE `@sqlstr` VARCHAR(2560); DECLARE `@sqlinsert` VARCHAR(2560); //以上声明变量 SELECT COUNT(0) into `@siteCount` FROM tbl_base_site; //计算表tbl_base_site的记录总条数 set `@i`=1; WHILE (`@i`-1)*300`@siteCount` DO //while循环执行 SET @sqlstr = CONCAT('CREATE TABLE tbl_base_syslog',`@i`,'(syslog_id INT(11) AUTO_INCREMENT PRIMARY KEY, create_user VARCHAR(32), description text, create_time datetime, site_id INT(11), ip VARCHAR(64), version_id SMALLINT(2), module_identity VARCHAR(64), right_name VARCHAR(64) )'); prepare stmt from @sqlstr; execute stmt; //以上实现动态创建表 SET @sqlinsert = CONCAT('INSERT INTO tbl_base_syslog',`@i`,'( syslog_id, create_user, description, create_time, site_id, ip, version_id, module_identity, right_name) SELECT syslog_id, create_user, description, create_time, site_id, ip, version_id, module_identity, right_name FROM tbl_base_syslog WHERE site_id IN (select tbs.site_id from (select site_id from tbl_base_site limit ',`@i`*300,',300) as tbs) ORDER BY syslog_id'); prepare stmt from @sqlinsert; execute stmt; //以上实现从一张表查询记录插入到动态创建的新表中 SET `@i`= `@i`+1; END WHILE; END

1、首先建立一张需要的随机测试数据表(根据你的需求来建)

CREATE TABLE `sphinx_user` (

            `id` int(11) NOT NULL AUTO_INCREMENT,

            `username` varchar(4) NOT NULL,

            `sex` varchar(5) NOT NULL,

            `score` int(11) NOT NULL,

            `english_name` varchar(10) NOT NULL,

            PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

2、然后再建立一张存储过程  也就是mysql封装的一个函数【根据自己需要可修改逻辑】 

DELIMITER //

CREATE PROCEDURE `auto_create_user`(IN `num` INT)

BEGIN

        DECLARE rowid INT DEFAULT 0;

        DECLARE firstname CHAR(1);

        DECLARE username CHAR(4);

        DECLARE name1 CHAR(1);

        DECLARE name2 CHAR(1);

        DECLARE lastname VARCHAR(3) DEFAULT '';

        DECLARE english_name VARCHAR(10) DEFAULT '';

        DECLARE sex CHAR(1);

        DECLARE score CHAR(2);

        DECLARE chars char(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

        SET @exe_string = "";

        WHILE rowid < num DO

            SET firstname = SUBSTRING('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍卻璩桑桂濮牛寿通边扈燕冀郏浦尚',FLOOR(1+100*RAND()),1);

            SET name1 = SUBSTRING('澄邈德泽海超海阳海荣海逸海昌瀚钰瀚文涵亮涵煦涵蓄涵衍浩皛浩波浩博浩初浩宕浩歌浩广浩邈浩气浩思浩言鸿宝鸿杰运珧运骏运凯运乾维运运晟运莱运华耘豪星爵星',ROUND(1+73*RAND()),1);

            SET name2 = SUBSTRING('波鸿博鸿才鸿畅鸿畴鸿达鸿德鸿飞鸿风鸿福鸿光鸿晖鸿朗鸿文鸿轩鸿煊鸿骞鸿远鸿云鸿哲鸿祯鸿志鸿卓嘉澍光济澎湃彭泽鹏池鹏海浦和浦泽瑞渊越泽博耘德运辰宇辰皓',ROUND(1+73*RAND()),1);

            SET english_name = CONCAT(substring(chars,floor(1+rand()*52),5),'_',substring(chars,floor(1+rand()*52),5));

            SET sex=FLOOR(0 + (RAND() * 2));

            SET score= FLOOR(40 + (RAND() *60));

            SET rowid = rowid + 1;

            IF ROUND(RAND())=0 THEN

版权声明:本文由美高梅开户送58元官网发布于美高梅网上游戏,转载请注明出处:1、首先建立一张需要的随机测试数据表美高梅网