插入mysql extra列导致错误

Question

Basically I have a table with 20million rows and 5 columns(fields), when I do a mysql dump then I want to import the rows to another table that has 6 columns it returns an error because the the columns are not the same

INSERT INTO table2 VALUES (value1, value2, value3, value4, value5)

I would like to insert all the rows with the 6th column that way I don't get the error. I can't really edit the .sql dump its 2gb. Is there a way to do it? please remember the destination table (where the data is to be dumped) already contains a considerable amount of data (8million rows) so altering the table would take forever any suggestions ?

Answer 1

There are several ways to do this.

First, there are ways to edit 2GB files (depending on your OS). For Windows, I had good experience with EmEditor that can handle Search & Replace operations on large files.

Second, you can do it in two stages: 1. Open the dump file into a table with 5 columns, e.g. temp. 2. Copy from that table to your destination table.

INSERT INTO table2 (col1, col2, col3, col4, col5) 
SELECT (col1, col2, col3, col4, col5) 
FROM temp;

This is assuming col6 in table2, for which you don't have data, has a default value. Otherwise:

INSERT INTO table2 (col1, col2, col3, col4, col5, col6) 
SELECT (col1, col2, col3, col4, col5, 'anyDefaultValue') 
FROM temp;