2

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 ?

3

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

1
  • Thank you for your detailed response. i'm ok with some gaps but didn't want to have a big gap May 23 at 18:25
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 ...

2

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:

    LOAD DATA INFILE ...
        ..., @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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.