将 CSV 导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3025648/
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
Import CSV to MySQL
提问by 404error
I have created a database and a table. I have also created all the fields I will be needing. I have created 46 fields including one that is my ID for the row. The CSV doesn't contain the ID field, nor does it contain the headers for the columns. I am new to all of this but have been trying to figure this out. I'm not on here being lazy asking for the answer, but looking for directions.
我已经创建了一个数据库和一个表。我还创建了我将需要的所有字段。我创建了 46 个字段，其中一个是我的行 ID。CSV 不包含 ID 字段，也不包含列的标题。我对这一切都很陌生，但一直在努力解决这个问题。我不是在这里懒惰地寻求答案，而是寻找方向。
I'm trying to figure out how to import the CSV but have it start importing data starting at the 2nd field, since I'm hoping the auto_increment will fill in the ID field, which is the first field I created.
我试图弄清楚如何导入 CSV 但让它从第二个字段开始导入数据，因为我希望 auto_increment 将填写 ID 字段，这是我创建的第一个字段。
I tried these instructions with no luck. Can anyone offer some insight?
我尝试了这些说明但没有运气。任何人都可以提供一些见解吗？
- The column names of your CSV file must match those of your table
- Browse to your required .csv file
- Select CSV using LOAD DATA options
- Check box 'ON' for Replace table data with file
- In Fields terminated bybox, type
, - In Fields enclosed bybox,
" - In Fields escaped bybox,
\ - In Lines terminated bybox,
auto - In Column namesbox, type column name separated by
,likecolumn1,column2,column3 - Check box ON for Use LOCAL keyword.
- CSV 文件的列名必须与表的列名匹配
- 浏览到所需的 .csv 文件
- 使用 LOAD DATA 选项选择 CSV
- 用文件替换表数据的复选框“ON”
- 在以框终止的字段中，键入
, - 在框包围的字段中，
" - 在被盒子逃脱的田野中，
\ - 在由框终止的行中，
auto - 在列名框中，键入以
,类似分隔的列名column1,column2,column3 - 使用 LOCAL 关键字的复选框为 ON 。
Edit:
编辑：
The CSV file is 32.4kb
CSV 文件为 32.4kb
The first row of my CSV is:
我的 CSV 的第一行是：
Test Advertiser,23906032166,119938,287898,,585639051,287898 - Engager - 300x250,88793551,Running,295046551,301624551,2/1/2010,8/2/2010,Active,,Guaranteed,Publisher test,Maintainer test,example-site.com,,All,All,,Interest: Dental; custom geo zones: City,300x250,-,CPM,.49 ,"4,415","3,246",3,0,5.52 ,1.69 ,"2,895",805,0,0,.18 ,.49 ,0,LOAD DATA INFILE 'path/to/file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' SET id=null;
.00 ,IMPRESSIONBASED,NA,USD
回答by webbiedave
You can have MySQL set values for certain columns during import. If your idfield is set to auto increment, you can set it to null during import and MySQL will then assign incrementing values to it. Try putting something like this in the SQL tab in phpMyAdmin:
您可以在导入期间让 MySQL 为某些列设置值。如果您的id字段设置为自动递增，您可以在导入期间将其设置为 null，然后 MySQL 将为其分配递增值。尝试在 phpMyAdmin 的 SQL 选项卡中放置类似的内容：
LOAD DATA INFILE 'filepath' INTO TABLE 'tablename' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (column2, column3, column4);
回答by Kavet Kerek
Please look at this page and see if it has what you are looking for. Should be all you need since you are dealing with just one table. MYSQL LOAD DATA INFILE
请查看此页面，看看它是否包含您要查找的内容。应该是您所需要的，因为您只处理一张桌子。MYSQL 加载数据文件
So for example you might do something like this:
例如，您可能会执行以下操作：
##代码##That should give you an idea. There are of course more options that can be added as seen in the above link.
那应该给你一个想法。当然还有更多选项可以添加，如上面的链接所示。
回答by paiego
be sure to use LOAD DATA LOCALINFILE if the import file is local. :)
如果导入文件是本地文件，请务必使用 LOAD DATA LOCALINFILE。:)
