MySQL – Export and Import Table Without Specific Column

auto-incrementexportimportMySQLphpmyadmin

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 ?

Best Answer

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).

Related Question