创建函数
因为我们平时经常需要创建不同日期的数据，以模拟的场景，覆盖更多的用例，所以这里写了一个返回随机日期的demo。大家可以自行扩展。
DROP FUNCTION IF EXISTS milan_get_rand_time;
CREATE FUNCTION milan_get_rand_time(type VARCHAR(50)) RETURNS VARCHAR(20)
BEGIN
DECLARE _day INT DEFAULT (FLOOR(RAND() * 60)); -- 随机天数 60天以内随机天数
DECLARE _hour INT DEFAULT (FLOOR(RAND() * 24)); -- 随机小时
DECLARE _minute INT DEFAULT (FLOOR(RAND() * 60));-- 随机分
DECLARE _second INT DEFAULT (FLOOR(RAND() * 60));-- 随机秒
DECLARE _myday VARCHAR(20);
IF type = 'before' THEN
set _day =_day;
ELSEIF type='after' THEN
SET _day =-1*_day;
ELSE
SET _day=0;
END IF; SET _myday = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL _day DAY) ,'%Y-%m-%d'); -- 获取一个随机的日期
-- SET _myday = DATE_SUB(CURRENT_DATE(),INTERVAL _day DAY); -- 获取一个随机的日期
SET _myday = CONCAT(_myday,' ',LPAD(_hour,2,0),':',LPAD(_minute,2,0),':',LPAD(_second,2,0)); -- 在这个日期上加上时分秒
RETURN _myday;
END
调用
SELECT milan_get_rand_time('after'); -- 返回 2018-03-02 22:07:14
SELECT milan_get_rand_time('before'); -- 2018-02-02 03:17:10
-- 补位函数
-- 向前补位
select LPAD(1,3,0); --
-- 向后补位
select RPAD(1,3,0); --
创建存储过程
每次获取一条随机数据，插入到表中。用select。。。into。语句。因为milan_table的game_id是主键，为了防止重复写入报错，所以用了ignore忽略错误。
DROP PROCEDURE IF EXISTS milan_test;
CREATE PROCEDURE milan_test(in add_count int,OUT data_count INT) -- in 入参 out 出参
BEGIN
DECLARE _game_id BIGINT;
DECLARE _game_name VARCHAR(50);
-- DELETE FROM milan_table;
WHILE add_count > 0 DO
SELECT game_id,game_name INTO _game_id,_game_name from t_game ORDER BY RAND() LIMIT 1;
INSERT IGNORE INTO milan_table VALUES (_game_id,_game_name,milan_get_rand_time('before'));
SET add_count=add_count-1;
END WHILE;
SET data_count =(select count(*) from milan_table);
SELECT * from milan_table;
end -- 调用：
SET @count =NULL;
CALL milan_test(5,@count);
SELECT @count;
根据游标写入：
DROP PROCEDURE if EXISTS curtest;
CREATE PROCEDURE curtest()
BEGIN
DECLARE _uid INT;
DECLARE _account VARCHAR(20);
DECLARE done INT DEFAULT FALSE;
-- 遍历数据结束标志
DECLARE cur CURSOR FOR SELECT uid,account FROM tmm_tmp_testaccount ;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO _uid,_account;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE tmm_tmp_testaccount set `desc` = NULL where uid =_uid;
END LOOP;
CLOSE cur;
END
跳出存储过程，相当于return语句：
label:BEGIN
DECLARE _uid int DEFAULT (select uid from tmm_tmp_testaccount where flag =_flag limit 1);
IF ISNULL(_uid) THEN
leave label;
END IF;
END
临时表
DROP PROCEDURE if EXISTS temp_table;
CREATE PROCEDURE temp_table()
BEGIN
DROP TABLE if EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table(
uid INT NULL,
n VARCHAR(10) NULL
);
INSERT INTO temp_table (uid,n) VALUES ('','milan1');
INSERT INTO temp_table (uid,n) VALUES ('','milan2');
select * from temp_table;
END