php - Mysql CSV 加载文件

Question

我有一个包含 9 列的 CSV 文件和一个包含 11 列的 MySQL 表。

CSV 文件如下所示:

col1, col2, col3, col4, col5, col6, col7, col8, col9

MySQL 表如下所示:

col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11

我需要让脚本忽略两个错误的(但必需的)MySQL 列。

导入时需要忽略的mysql列有:db_id & nice_date =)

这是我目前所拥有的:

$sql = 'LOAD DATA LOCAL INFILE "../csvtemp/test.csv" 
        INTO TABLE sample 
            FIELDS TERMINATED BY "," 
            OPTIONALLY ENCLOSED BY """" 
            IGNORE 1 LINES'
;

Answer 1

$sql = 'LOAD DATA LOCAL INFILE "../csvtemp/test.csv" 
        INTO TABLE sample 
            FIELDS TERMINATED BY "," 
            OPTIONALLY ENCLOSED BY """" 
            IGNORE 1 LINES
            (col1, col2, col3, col4, col5, col6, col7, col8, col9)'
;

缺失的列将被赋予默认值，或者您可以通过这种方式指定固定值:

$sql = 'LOAD DATA LOCAL INFILE "../csvtemp/test.csv" 
        INTO TABLE sample 
            FIELDS TERMINATED BY "," 
            OPTIONALLY ENCLOSED BY """" 
            IGNORE 1 LINES
            (col1, col2, col3, col4, col5, col6, col7, col8, col9)'
            SET col10 = 'abc', col11 = 'xyz'
;