MySql importing from 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. What would be the format of the CSV look like?
2. Is this possible or is importing basically just to replace the whole data with the 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

etc...

回答1:

Change the SQL that phpmyadmin will run to this:

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

(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

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.