插入mysql extra列导致错误

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 ?

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;

You need to specify the rows you want to insert to

INSERT INTO table2 (col1, col2, col3, col4, col5) VALUES (value1, value2, value3, value4, value5)

Here are your options:

  1. Specify the column names in your insert and don't include the 6th column (which would set the 6th column to null for all rows inserted so the column needs to be nullable)
  2. add a null/default value to the VALUES of the query to insert into the 6th column
  3. Modify the table and remove the 6th column temporarily so you can insert and then add the column back. This would lose any existing values in the 6th column
  4. Insert into a different table (or same table name and different database) that has the same structure as the 5 column table and then run a query to insert into the 6 column table. A single simple query like: INSERT INTO newTable SELECT col1, col2, col3, col4, col5, "default value" FROM newTableCopy

I would suggest option 4, but it does require extra steps.