Mysql通过存储过程导入Excel数据

1.确认excel结构

2.设计临时表，将excel数据导入临时表

3.通过客户端工具将excel导入临时表

我这里使用的Sqlyog

4.创建存储过程，将临时表数据导入数据库中

DELIMITER $$

USE `hzero_platform`$$

DROP PROCEDURE IF EXISTS `employee_excel`$$

CREATE DEFINER=`root`@`%` PROCEDURE `employee_excel`()
BEGIN
	DECLARE employeeCode VARCHAR(50);
        DECLARE employeeName VARCHAR(100);
        DECLARE phone VARCHAR(50);
        DECLARE email VARCHAR(50);
        DECLARE loginName VARCHAR(50);
        DECLARE unitCode VARCHAR(50);
        DECLARE departCode VARCHAR(50);
        DECLARE positionCode VARCHAR(50);
        DECLARE employeeId INT;
        DECLARE userId INT;
        DECLARE unitId INT;
        DECLARE departId INT;
        DECLARE positionId INT;
        DECLARE done INT DEFAULT 0;
        DECLARE id_list CURSOR FOR
	    SELECT ee.employeeCode, ee.employeeName, ee.phone, ee.email, ee.loginName, ee.unitCode, ee.departCode, ee.positionCode FROM  excel_employee ee ;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT;
            SELECT @p1,@p2;
            ROLLBACK;
    -- 	    自定义错误
            IF @p1='23000' THEN
            SIGNAL SQLSTATE 'BF000' SET MESSAGE_TEXT = @p2;
            ELSE
            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
            END IF;
        END;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
        START TRANSACTION; -- 整个存储过程指定为一个事务
        OPEN id_list;
	    FETCH id_list INTO employeeCode, employeeName, phone, email, loginName, unitCode, departCode, positionCode;
        -- 确认是否取到数据
        SELECT employeeCode, employeeName, phone, email, loginName, unitCode, departCode, positionCode;
        WHILE done<>1 DO
            INSERT INTO hpfm_employee(employee_num, NAME, mobile, email, gender)
            VALUES (employeeCode, employeeName, phone, email, 0);
            -- 获取员工主键
            SELECT LAST_INSERT_ID() INTO employeeId;
            SELECT id INTO userId FROM iam_user
            WHERE login_name=loginName;
            -- 维护员工和用户的关秀
            INSERT INTO hpfm_employee_user(employee_id, user_id)
            VALUES (employeeId, userId);
            SELECT unit_id INTO unitId FROM hpfm_unit
            WHERE unit_code=unitCode;
            SELECT unit_id INTO departId FROM hpfm_unit
            WHERE unit_code=departCode;
            SELECT position_id INTO positionId FROM hpfm_position
            WHERE position_code=positionCode;
            INSERT INTO hpfm_employee_assign(employee_id, unit_company_id, unit_id, position_id, tenant_id)
            VALUES (employeeId, unitId, departId, positionId, 0);
        FETCH id_list INTO employeeCode, employeeName, phone, email, loginName, unitCode, departCode, positionCode;
        END WHILE;
        CLOSE id_list;
        COMMIT ; -- 提交事务
    END$$

DELIMITER ;

5.调用存储过程

CALL employee_excel();