【mysql 存储过程】数据库从菜鸟走向大神系列3

1、创建数据库

create database cgjr

2. 建表

我们新建一张学生表和教师表如下：

CREATE TABLE student(  id INT NOT NULL AUTO_INCREMENT,  first_name VARCHAR(10) NOT NULL,  last_name VARCHAR(10) NOT NULL,  sex VARCHAR(5) NOT NULL,  score INT NOT NULL,  PRIMARY KEY (`id`));CREATE TABLE teacher(  id INT NOT NULL AUTO_INCREMENT,  first_name VARCHAR(10) NOT NULL,  last_name VARCHAR(10) NOT NULL,  sex VARCHAR(5) NOT NULL,  PRIMARY KEY (`id`));

3. 创建存储过程

这里按照1000的批次来进行插入，提升整体执行效率。另外使用了floor、substring和RAND等函数来协助生成随机数据。

SQL函数不了解可以看 mysql官方文档

/**增加学生数据的存储过程-- **/DROP PROCEDURE IF EXISTS add_student;  DELIMITER //    create PROCEDURE add_student(in num INT)    BEGIN        DECLARE rowid INT DEFAULT 0;        DECLARE firstname CHAR(1);        DECLARE name1 CHAR(1);        DECLARE name2 CHAR(1);        DECLARE lastname VARCHAR(3) DEFAULT '';        DECLARE sex CHAR(1);        DECLARE score CHAR(2);        SET @exedata = "";        WHILE rowid < num DO            SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1);             SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',floor(1+43*RAND()),1);             SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',floor(1+43*RAND()),1);             SET sex=SUBSTRING('男女',floor(1+2*RAND()),1);            SET score= FLOOR(40 + (RAND() *60));            SET rowid = rowid + 1;            IF ROUND(RAND())=0 THEN             SET lastname =name1;            END IF;            IF ROUND(RAND())=1 THEN            SET lastname = CONCAT(name1,name2);            END IF;            IF length(@exedata)>0 THEN            SET @exedata = CONCAT(@exedata,',');            END IF;            SET @exedata=concat(@exedata,"('",firstname,"','",lastname,"','",sex,"','",score,"')");            IF rowid%1000=0            THEN                 SET @exesql =concat("insert into student(first_name,last_name,sex,score) values ", @exedata);                prepare stmt from @exesql;                execute stmt;                DEALLOCATE prepare stmt;                SET @exedata = "";            END IF;        END WHILE;        IF length(@exedata)>0         THEN            SET @exesql =concat("insert into student(first_name,last_name,sex,score) values ", @exedata);            prepare stmt from @exesql;            execute stmt;            DEALLOCATE prepare stmt;        END IF;     END //DELIMITER ;/**增加教师数据的存储过程-- **/DROP PROCEDURE IF EXISTS add_teacher;  DELIMITER //    create PROCEDURE add_teacher(in num INT)    BEGIN        DECLARE rowid INT DEFAULT 0;        DECLARE firstname CHAR(1);        DECLARE name1 CHAR(1);        DECLARE name2 CHAR(1);        DECLARE lastname VARCHAR(3) DEFAULT '';        DECLARE sex CHAR(1);        SET @exedata = "";        WHILE rowid < num DO            SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1);             SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',floor(1+43*RAND()),1);             SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',floor(1+43*RAND()),1);             SET sex=SUBSTRING('男女',floor(1+2*RAND()),1);            SET rowid = rowid + 1;            IF ROUND(RAND())=0 THEN             SET lastname =name1;            END IF;            IF ROUND(RAND())=1 THEN            SET lastname = CONCAT(name1,name2);            END IF;            IF length(@exedata)>0 THEN            SET @exedata = CONCAT(@exedata,',');            END IF;            SET @exedata=concat(@exedata,"('",firstname,"','",lastname,"','",sex,"')");            IF rowid%1000=0            THEN                 SET @exesql =concat("insert into teacher(first_name,last_name,sex) values ", @exedata);                prepare stmt from @exesql;                execute stmt;                DEALLOCATE prepare stmt;                SET @exedata = "";            END IF;        END WHILE;        IF length(@exedata)>0         THEN            SET @exesql =concat("insert into teacher(first_name,last_name,sex) values ", @exedata);            prepare stmt from @exesql;            execute stmt;            DEALLOCATE prepare stmt;        END IF;     END //DELIMITER ;

4、调用存储过程

call add_student(3)call add_teacher(10)

5、结果

6、创建事件定时跑存储过程

create event test_event_add_student on schedule every 30 second do call add_student(3);

7、开启事件

mysql> show variables like 'event_scheduler';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | OFF   |+-----------------+-------+1 row in setmysql> set global event_scheduler = on;Query OK, 0 rows affectedmysql>