0

I made an export via phpmyadmin the structure of a database ( no data ). Then I created another database and imported in it the previous exported database. When the import was finished then I looked the structure of each table of the new database : I saw that all the tables which has an auto_increment primary key have their auto_increment options not 1 ! So how to make the auto_increment of all tables having an auto_increment key to be 1 when doing an export ?

pheromix
  • 18,213
  • 29
  • 88
  • 158

1 Answers1

0

When you export the table there is an option in PHPMyAdmin to reset the AI,but you can do it just by adding AUTO_INCREMENT = 1 to the table definition. 

CREATE TABLE IF NOT EXISTS `tablename` (
  ...columns...
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1

For an empty table, say at the destination point of this export, another way to reset the auto_increment attribute is to run

TRUNCATE TABLE mydb.tablename;

You can easily do this for all tables dynamically on your new EMPTY SQL database.

Read more here: http://trebleclick.blogspot.co.uk/2009/01/mysql-set-auto-increment-in-phpmyadmin.html

GOT IT!

This is a two step solution, using PHPMyAdmin you goto Table --> Operations and then on the Copy Table panel you copy the table structure without data and uncheck the checkbox Add AUTO_INCREMENT value .

Then you goto your new copied table and export that table.

Martin
  • 22,212
  • 11
  • 70
  • 132
  • there are hundreds of table in my database so its very hard work to write this code for every table ! – pheromix Mar 01 '16 at 09:01
  • Export the table structure, then in the SQL file do a find and replace and add the code, you can easily create a script to insert the text at the end of the correct line – Martin Mar 01 '16 at 09:10
  • if you export all tables into one SQL file then just find and replace to insert the AI criteria. @pheromix – Martin Mar 01 '16 at 09:11
  • it is not only tables that I want to export but also views , triggers and functions ! – pheromix Mar 01 '16 at 09:13
  • no ! at the time it is done table by table then it is not a good practice ! – pheromix Mar 01 '16 at 09:24