从CSV导入MySql [英] MySql importing from CSV

问题描述

我有一个包含三列的表，即NODEID，X，Y.NODEID是主键，它被设置为INT(4)为AUTOINCREMENT.我希望通过通过phpmyadmin导入从CSV导入该表来向该表添加更多数据.问题:

I have a table with three columns, NODEID, X, Y. NODEID is the primary key and it is set as an INT(4) to be AUTOINCREMENT. I wish to add more data to this table by importing it from a CSV via the phpmyadmin import. Question:

1. CSV的格式是什么样的?
2. 这是否可能，或者基本上只是导入以CSV替换整个数据?

到目前为止，CSV如下:

As of now the CSV looks like:

1,-105.057578,39.785603
2,-105.038646,39.771132
3,-105.013045,39.771727
5,-105.045721,39.762055
6,-105.031777,39.76206
7,-105.046015,39.72835
8,-105.029796,39.728304
10,-104.930863,39.754579
11,-104.910624,39.754644
13,-104.930959,39.74367
16,-105.045802,39.685253
17,-105.032149,39.688557
18,-105.060891,39.657622
20,-105.042257,39.644086

等...

推荐答案

将phpmyadmin将运行的SQL更改为此:

Change the SQL that phpmyadmin will run to this:

LOAD DATA INFILE '*FILEPATH*'
INTO TABLE *table*
(X, Y);

(您只需更改最后一行) 而且您的csv应该看起来像

(You will only have to change the last line) And your csv should look like

-105.057578,39.785603
-105.038646,39.771132
-105.013045,39.771727
-105.045721,39.762055
-105.031777,39.76206
-105.046015,39.72835

最后一行告诉MySQL仅查找数据的这两列，并为其他任何列插入null. NULL值将按预期自动递增.

The last line tells MySQL to look for only those two columns of data and insert null for any other columns. The NULL value will be auto-incremented as expected.

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