Export and import a table without a specific column

All we need is an easy explanation of the problem, so here it is.

I’m trying to export a table with an Auto Increment rowid column and then import it in another server.

Considering that the column is primary key and can’t accept duplicate keys if i import rows after backup from old server, new rows with same rowid would be added to new server.

i also don’t want to change start number for Auto Increment in new server and cause gaps in rows.

so i thought one solution can be exporting new rows without rowid and letting new server to assign new rowid to them. but how can i do that directly with mysql or with help with phpMyAdmin ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Another solution is to use LOAD DATA [LOCAL] INFILE, but override the columns list so the auto-increment value is inserted into a dummy variable:

Here’s a demo:

I have a text file I call c.csv:

1       foo     bar
2       baz     bam
3       blee    bloo

I want to insert into mytable, but id’s 1,2,3 are already occupied. I want the csv data to be inserted, but given new id’s.

mysql> select * from mytable;
| id | col1 | col2 |
|  1 | text | text |
|  2 | text | text |
|  3 | text | text |

Here’s a solution:

mysql> load data local infile 'c.csv' into table mytable (@dummy, col1, col2);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from mytable;
| id | col1 | col2 |
|  1 | text | text |
|  2 | text | text |
|  3 | text | text |
|  4 | foo  | bar  |
|  5 | baz  | bam  |
|  6 | blee | bloo |

It added the three rows from my csv file, but the id column on each row of input was detoured into the dummy variable, so the rows had to use the default, allocating new auto-increment id’s.

P.S.: You need to let go of your desire to have no gaps in the sequence if id’s. Gaps are normal. You may delete some rows in the future. You may attempt an insert but it gets rolled back or gets an error. There are also cases where InnoDB generates non-consecutive id values by design (read https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html for details).

Method 2

Here is a quick and dirty way to accomplish the task:

  1. Create a new table on the old server using the same schema minus the primary key
  2. Copy the records from the source table to the dummy table
  3. Export the data using mysqldump or something in phpMyAdmin
  4. Change the INSERT INTO {table} strings in the export to have the correct table name
  5. Import the data to the new server

There are likely other ways, such as a temporary before TRIGGER that will replace the id with NULL so that the auto-increment continues, but the above method would likely be the fastest …

Method 3

As you insert, add a big number (big enough to be past the existing ids) to the id column for the incoming rows.

Plan A: Load into a temp table, then INSERT .. SELECT id+100000, ... FROM temp.

Plan B:

        ..., @id = id, ...
        SET id = @id + 100000

Caveat: You must not be inserting into the table from other sources during the load; it could create an id that will conflict.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply