mysql利用存储过程造随机数据

mysql
–建表
create table dxp_qgm_test_three(
id bigint(20) PRIMARY KEY,
dom_datatime VARCHAR(255),
dom_id bigint(20),
dom_string varchar(255));

–创建存储过程

SELECT CONCAT(
‘2018-11-’ ,
LPAD(FLOOR(1 + RAND() * 30), 2, 0),-- 01-30
’ ',
LPAD(FLOOR(RAND() * 24), 2, 0), – 00-23
‘:’,
LPAD(FLOOR(RAND() * 60), 2, 0),-- 00-59
‘:’,
LPAD(FLOOR(RAND() * 60), 2, 0) – 00-59
) ‘rand time’;

create table dxp_qgm_test_three
(
id bigint(20) PRIMARY KEY,
dom_datatime VARCHAR(255),
dom_id bigint(20),
dom_string varchar(255));

DROP PROCEDURE add_vote_memory;
CREATE PROCEDURE add_vote_memory(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE char_str varchar(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’;
DECLARE return_str varchar(255) DEFAULT ‘’;
DECLARE dom_datatime VARCHAR(255);
SELECT CONCAT(‘2018-11-’ , LPAD(FLOOR(1 + RAND() * 30), 2, 0),’ ‘,LPAD(FLOOR(RAND() * 24), 2, 0), ‘:’,LPAD(FLOOR(RAND() * 60), 2, 0),’:’,LPAD(FLOOR(RAND() * 60), 2, 0)) INTO dom_datatime;
WHILE (i <= n) DO
SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1)); INSERT INTO dspsrc.dxp_qgm_test_three (id, dom_datatime, dom_id, dom_string) VALUES (i,dom_datatime, FLOOR(RAND() * 10),return_str );
COMMIT;
SET i = i + 1;
END WHILE;
END

//执行造数据
CALL add_vote_memory (10000);