MySQL 命令“LOAD DATA INFILE"有问题.我需要将第一行设置为标题 [英] Having trouble with MySQL Command 'LOAD DATA INFILE". I need to set first row as headers
本文介绍了MySQL 命令“LOAD DATA INFILE"有问题.我需要将第一行设置为标题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的代码.
TRUNCATE TABLE dsw_data.inventory_sss2;
SET AUTOCOMMIT=0;
LOAD DATA INFILE 'c:/inetpub/wwwroot/Data/inventory_sss.csv' INTO TABLE dsw_data.inventory_sss2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r';
COMMIT;
这是由此产生的错误
Error Code: 1366. Incorrect integer value: 'CUST NO' for column 'sno' at row 1"
我希望 CSV 的第一行是表头,其他行是数据.
I want the first row of the CSV to be the table headers, and the other rows to be the data.
另外,我如何指定哪些行应该是数字(如果需要,我不确定).
Also how do I specify which rows should be numeric (If this is even needed, I'm not sure).
我在这里和谷歌搜索过,但没有看到代码示例来设置我想要的标题.
I have searched here and google, but haven't seen code samples to set the headers like I want.
推荐答案
要回答您的第一个问题,您需要使用 IGNORE 1 LINES
语法:
To answer your first question, you need to use the IGNORE 1 LINES
syntax:
TRUNCATE TABLE dsw_data.inventory_sss2;
SET AUTOCOMMIT=0;
LOAD DATA INFILE 'c:/inetpub/wwwroot/Data/inventory_sss.csv'
INTO TABLE dsw_data.inventory_sss2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r';
IGNORE 1 LINES
COMMIT;
更新为显式命名列并将主键设置为空以使用自动增量:
Updated to explicitly name the columns and set the primary key to null to use auto-increment:
TRUNCATE TABLE dsw_data.inventory_sss2;
SET AUTOCOMMIT=0;
LOAD DATA INFILE 'c:/inetpub/wwwroot/Data/inventory_sss.csv'
INTO TABLE dsw_data.inventory_sss2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r';
IGNORE 1 LINES
(your_first_column, your_second_column,...)
SET sno = NULL
COMMIT;
这篇关于MySQL 命令“LOAD DATA INFILE"有问题.我需要将第一行设置为标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文