创建表:
CREATE TABLE `mhbtest`.`dept` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`deptno` int(11) NULL DEFAULT NULL,
`dname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`loc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4183527 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
创建函数:
DELIMITER $$ // 以$$结尾算作执行
CREATE FUNCTION rand_string( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
DECLARE
chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE
return_str VARCHAR ( 255 ) DEFAULT '';
DECLARE
i INT DEFAULT 0;
WHILE
i < n DO
SET return_str = CONCAT( return_str, SUBSTRING( chars_str, FLOOR(1+ RAND() * 52), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
创建存储过程: 存储过程中调用 函数 rand_string
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
set autocommit = 0;
REPEAT
set i = i +1;
insert into mhbtest.dept(deptno, dname, loc) VALUES ((START+i), rand_string(10), rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
执行函数和存储过程:
CALL insert_dept(200,20);
查询,删除:
show procedure status;
show FUNCTION status;
drop procedure if exists insert_dept;
drop function if exists rand_string;