DELIMITER //
CREATE PROCEDURE InsertRandomEmployees()
BEGIN
DECLARE v_count INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 如果出现错误,则回滚事务
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
WHILE v_count < 1000 DO
SET @name = CONCAT('员工', FLOOR(RAND() * 100000)); -- 生成随机员工姓名
SET @department = CONCAT('部门', FLOOR(RAND() * 10) + 1); -- 生成随机部门
SET @position = CONCAT('职位', FLOOR(RAND() * 10) + 1); -- 生成随机职务
SET @start_time = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY); -- 生成过去3年内的随机开始时间
SET @end_time = DATE_ADD(@start_time, INTERVAL FLOOR(RAND() * 365) DAY); -- 生成在开始时间之后的随机结束时间
SET @city = CONCAT('城市', FLOOR(RAND() * 100) + 1); -- 生成随机城市
INSERT INTO employees (name, department, position, start_time, end_time, city)
VALUES (@name, @department, @position, @start_time, @end_time, @city);
SET v_count = v_count + 1;
END WHILE;
COMMIT; -- 提交事务
END //
DELIMITER ;
CALL InsertRandomEmployees();
CREATE TABLE employees (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工id',
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '员工姓名',
`department` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '部门',
`position` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '职务',
`start_time` date DEFAULT NULL COMMENT '开始时间',
`end_time` date DEFAULT NULL COMMENT '结束时间',
`city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;