使用 LOAD DATA INFILE 将 csv 上传到 mysql 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17666671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using LOAD DATA INFILE to upload csv into mysql table
提问by john k
I'm using LOAD DATA INFILE to upload a .csv into a table.
我正在使用 LOAD DATA INFILE 将 .csv 上传到表格中。
This is the table I have created in my db:
这是我在我的数据库中创建的表：
CREATE TABLE expenses (entry_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entry_id),
ss_id INT, user_id INT, cost FLOAT, context VARCHAR(100), date_created DATE);
This is some of the sample data I'm trying to upload (some of the rows have data for every column, some are missing the date column):
这是我尝试上传的一些示例数据（有些行包含每一列的数据，有些缺少日期列）：
1,1,20,Sandwiches after hike, 1,1,45,Dinner at Yama, 1,2,40,Dinner at Murphys, 1,1,40.81,Dinner at Yama, 1,2,1294.76,Flight to Taiwan,1/17/2011 1,2,118.78,Grand Hyatt @ Seoul,1/22/2011 1,1,268.12,Seoul cash withdrawal,1/8/2011
Here is the LOAD DATA command which I can't get to work:
这是我无法开始工作的 LOAD DATA 命令：
LOAD DATA INFILE '/tmp/expense_upload.csv'
INTO TABLE expenses (ss_id, user_id, cost, context, date)
;
This command completes, uploads the correct number of rows into the table but every field is NULL. Anytime I try to add FIELDS ENCLOSED BY ',' or LINES TERMINATED BY '\r\n' I get a syntax error.
此命令完成后，将正确数量的行上传到表中，但每个字段都为 NULL。每当我尝试添加 FIELDS ENCLOSED BY ',' 或 LINES TERMINATED BY '\r\n' 时，我都会收到语法错误。
Other things to note: the csv was created in MS Excel.
其他注意事项：csv 是在 MS Excel 中创建的。
If anyone has tips or can point me in the right direction it would be much appreciated!
如果有人有提示或可以指出我正确的方向，将不胜感激！
回答by peterm
First of all I'd change FLOATto DECIMALfor cost
首先，我会改变FLOAT，以DECIMAL对cost
CREATE TABLE expenses
(
entry_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ss_id INT,
user_id INT,
cost DECIMAL(19,2), -- use DECIMAL instead of FLOAT
context VARCHAR(100),
date_created DATE
);
Now try this
现在试试这个
LOAD DATA INFILE '/tmp/sampledata.csv'
INTO TABLE expenses
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' -- or \r\n
(ss_id, user_id, cost, context, @date_created)
SET date_created = IF(CHAR_LENGTH(TRIM(@date_created)) > 0,
STR_TO_DATE(TRIM(@date_created), '%m/%d/%Y'),
NULL);
What id does:
id 的作用：
- it uses correct syntax for specifying fields and columns terminators
- since your date values in the file are not in a proper format, it first reads a value to a user/session variable then if it's not empty it converts it to a date, otherwise assigns
NULL. The latter prevents you from getting zero dates0000-00-00.
- 它使用正确的语法来指定字段和列终止符
- 由于文件中的日期值格式不正确，它首先将值读取到用户/会话变量，然后如果它不为空，则将其转换为日期，否则将
NULL. 后者可防止您获得零日期0000-00-00。
回答by Gordon Linoff
Here is my advice. Load the data into a staging table where all the columns are strings and then insert into the final table. This allows you to better check the results along the way:
这是我的建议。将数据加载到所有列都是字符串的临时表中，然后插入到最终表中。这使您可以更好地检查结果：
CREATE TABLE expenses_staging (entry_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(entry_id),
ss_id varchar(255),
user_id varchar(255),
cost varchar(255),
context VARCHAR(100),
date_created varchar(255)
);
LOAD DATA INFILE '/tmp/expense_upload.csv'
INTO TABLE expenses_staging (ss_id, user_id, cost, context, date);
This will let you see what is really being loaded. You can then load this data into the final table, doing whatever data transformations are necessary.
这将让您看到真正加载的内容。然后，您可以将此数据加载到最终表中，执行任何必要的数据转换。
