Mysql CSV加载infile [英] Mysql CSV load infile

问题描述

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

I've got a CSV file with 9 columns and I have a MySQL table with 11 columns.

CSV文件如下：

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

，MySQL表格如下：

and the MySQL table looks like:

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

脚本忽略两个错误（但是必须的）MySQL列。

I need to get the script to ignore the two erroneous (but required) MySQL columns.

在导入过程中需要忽略的mysql列是：db_id& nice_date =）

The mysql columns that need to be ignored in the import are: db_id & nice_date =)

这是我到目前为止：

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

推荐答案

$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)'
;

缺少的列将被赋予其DEFAULT值，否则可以通过以下方式指定固定值： / p>

The missing columns will be given their DEFAULT values, or else you can specify fixed values this way:

$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'
;

这篇关于Mysql CSV加载infile的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！