表里有一个自动递增的主键

如果表里有一个自动递增的主键，我们可以在导入数据时忽略该主键列，让MySQL自动生成主键。

修改存储过程的LOAD DATA语句，将自动递增的主键列排除在外，例如假设主键列名为`id`，则可以将LOAD DATA语句修改为：

SET @sql = CONCAT('LOAD DATA LOCAL INFILE "', file_path, '" INTO TABLE ', table_name, ' FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n" IGNORE 1 LINES (', col2, ',', col3, ',', col4, ',', col5, ',', col6, ',', col7, ',', col8, ',', col9, ')');

即将包含主键列的`col1`排除在外，只导入其他列的数据，此时MySQL会自动为每条记录生成一个递增的主键值。

同时，在存储过程中，我们需要将变量`col1`的声明和赋值从代码中删除，因为我们不需要将主键列的列名存储到该变量中。

修改后的存储过程如下所示：

DELIMITER $$
CREATE PROCEDURE import_txt(IN file_path VARCHAR(255), IN table_name VARCHAR(255))
BEGIN
    DECLARE line VARCHAR(255);
    DECLARE col1 VARCHAR(255);
    DECLARE col2 VARCHAR(255);
    DECLARE col3 VARCHAR(255);
    DECLARE col4 VARCHAR(255);
    DECLARE col5 VARCHAR(255);
    DECLARE col6 VARCHAR(255);
    DECLARE col7 VARCHAR(255);
    DECLARE col8 VARCHAR(255);
    DECLARE col9 VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT * FROM information_schema.columns WHERE table_name = table_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET @sql = CONCAT('LOAD DATA LOCAL INFILE "', file_path, '" INTO TABLE ', table_name, ' FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n" IGNORE 1 LINES (', col2, ',', col3, ',', col4, ',', col5, ',', col6, ',', col7, ',', col8, ',', col9, ')');
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO line;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF line.COLUMN_NAME = 'col2' THEN
            SET col2 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col3' THEN
            SET col3 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col4' THEN
            SET col4 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col5' THEN
            SET col5 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col6' THEN
            SET col6 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col7' THEN
            SET col7 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col8' THEN
            SET col8 = CONCAT('`', line.COLUMN_NAME, '`');
        ELSEIF line.COLUMN_NAME = 'col9' THEN
            SET col9 = CONCAT('`', line.COLUMN_NAME, '`');
        END IF;
    END LOOP;
    SET @sql = CONCAT('LOAD DATA LOCAL INFILE "', file_path, '" INTO TABLE ', table_name, ' FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n" IGNORE 1 LINES (', col2, ',', col3, ',', col4, ',', col5, ',', col6, ',', col7, ',', col8, ',', col9, ')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    CLOSE cur;
END$$
DELIMITER ;

使用方法与之前相同，调用存储过程时传入文件路径和表名即可。