load into mysql_Load file into mysql

问题

I need to load a tab delimited file into mysql database. My database is set up with columns: ID;A;B;C;D;E and my file is a dump of columns ID and D. How can I load this file into my db and replace just columns ID and D, which out changing the values of columns C and E. When I load it in now, columns C and E are changed to DEFAULT:NULL.

回答1:

I already answered a similar question like this here, but in your case, you'd want to load the csv into a temporary table, then use a simple update SQL statement to update the specific columbs from your temporary table to your production table.

回答2:

You can update specific column using this command:

UPDATE the_table

SET D=

WHERE ID=

Then run this command for each row in the tab-delimited file, replacing the D and ID values for each.

回答3:

You can use a stored procedure or a PHP program to do the needful.

For MySQL, the SP would need to open the file using load_file() and storing the value in a variable. Then the program needs to loop through by finding "\n" which stands for new line to get a whole line in a string.

Then the program needs to be find the first tab position using locate() and by using substring() get the first ID col. Then the program needs to find the 4th tab i.e. 3 more tabs by using locate() and its 3rd parameter. This will be the starting position of your D column. Then find the next tab character again using locate() and its 3rd parameter which will give you the end character of the D column. Using substring() get the content of the D column. Use a update command to update the row's D column using ID as the search key in the where clause.

Since the above will loop through all lines you will update all rows of data.

来源：https://stackoverflow.com/questions/10606343/load-file-into-mysql