MySQL 将 CSV 导入 phpmyadmin
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9734486/
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
importing a CSV into phpmyadmin
提问by Udders
I have a CSV that looks like this,
我有一个看起来像这样的 CSV，
candidate_id,show_on_site,first_name,surname,gender,DOB,showdob,Location,height,eyes,hair_colour,hair_length,accents,unions,training,url,visible,availability
,26,urban talent,Strawberry,Shortcake,Female,11 Jan 1942,FALSE,Manchester,5'2,Brown,Black,Mid-length,Native Lancashire,Equity,Urban Talent TV & Drama Workshops,Strawberry-Shortcake---5.4.06.jpg,Yes,Yes
,29,urban talent,Rainbow,Brite,Female,12 Oct 1970,FALSE,Manchester,5'7,Brown,Dark Brown,Long,"Native Manchester, others include - Cheshire, RP, Patois, Standard USA",Equity Member,"BA Acting Studies, Arden School of Theatre<br>Urban Talent TV & Drama Workshops",Rainbow Brite 1_1.jpg,Yes,Yes
,31,urban talent,Webbigail,Vanderquack,Female,4 Jun 1947,FALSE,Manchester,5'0,Hazel,Blonde,Mid-length,"Native Manchester, others include - Liverpool, Cockney, Birmingham, West Country, Standard Scottish, Standard Welch, S Irish",,Manchester School of Acting<br>3 Years at David Johnson Acting Workshops,Webbigail Vanderquack web 1.jpg,Yes,Yes
,33,urban talent,Smurfette,Smurf,Female,1 Jul 1979,FALSE,Manchester,5'2,Dark Brown,Dark Brown,Long,"Native Manchester, others include - Liverpool, RP, Lancashire, Birmingham, Cockney, Devon, Geordie, West Country, Glasgow, Edinburgh, South African, Standard & Southern US, Persian, Asian, Indian ~ good ear for accents",,"Manchester School of Acting, with Mark Hudson<br>North Cheshire Theatre College, with David Johnson<Oldham Theatre Workshop",Smurfette Smurf web 4.jpg,Yes,Yes
Is it possible to just insert this data into the existing columns in my database, all I can seem to it insert it as a new table which then has columns name A, B, C , D, E etc.
是否可以将这些数据插入到我的数据库中的现有列中，我认为它可以将它作为一个新表插入，然后将其作为列名 A、B、C、D、E 等插入。
回答by Ashwin A
In phpMyAdmin, click the table, and then click the Import tab at the top of the page.
在 phpMyAdmin 中，单击表格，然后单击页面顶部的导入选项卡。
Browse and open the csv file. Leave the charset as-is. Uncheck partial import unless you have a HUGE dataset (or slow server). The format should already have selected “CSV” after selecting your file, if not then select it (not using LOAD DATA). If you want to clear the whole table before importing, check “Replace table data with file”. Optionally check “Ignore duplicate rows” if you think you have duplicates in the CSV file. Now the important part, set the next four fields to these values:
浏览并打开 csv 文件。保持字符集不变。除非您有一个巨大的数据集（或慢速服务器），否则取消选中部分导入。选择文件后，格式应该已经选择了“CSV”，如果没有，则选择它（不使用 LOAD DATA）。如果要在导入前清除整个表，请选中“用文件替换表数据”。如果您认为 CSV 文件中有重复项，可选择勾选“忽略重复行”。现在是重要的部分，将接下来的四个字段设置为这些值：
Fields terminated by: ,
Fields enclosed by: “
Fields escaped by: \
Lines terminated by: auto
Currently these match the defaults except for “Fields terminated by”, which defaults to a semicolon.
目前这些匹配默认值，除了“字段终止于”，默认为分号。
Now click the Go button, and it should run successfully.
现在单击 Go 按钮，它应该会成功运行。
回答by Alex Daro
In phpMyAdmin v.4.6.5.2 there's a checkbox option "The first line of the file contains the table column names...." :
在 phpMyAdmin v.4.6.5.2 中有一个复选框选项“文件的第一行包含表列名....”：
回答by Starx
Using the LOAD DATA INFILESQL statement you can import the CSV file, but you can't update data. However, there is a trick you can use.
使用LOAD DATA INFILESQL 语句可以导入 CSV 文件，但不能更新数据。但是，您可以使用一个技巧。
- Create another temporary table to use for the import
Load onto this table from the CSC
LOAD DATA LOCAL INFILE '/file.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3);UPDATE the real table joining the table
UPDATE maintable INNER JOIN temp_table A USING (field1) SET maintable.field1 = temp_table.field1
- 创建另一个用于导入的临时表
从 CSC 加载到此表
LOAD DATA LOCAL INFILE '/file.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3);更新加入表的真实表
UPDATE maintable INNER JOIN temp_table A USING (field1) SET maintable.field1 = temp_table.field1
回答by Ruwantha
This is happen due to the id(auto increment filed missing). If you edit it in a text editor by adding a comma for the ID field this will be solved.
这是由于 id（自动增量文件丢失）而发生的。如果您在文本编辑器中通过为 ID 字段添加逗号来编辑它，这将得到解决。
