MySQL, help to understand data export

2

Previously when I exported a database it looked like this:

CREATE TABLE 'config' (
  'ID_Config' int(1) NOT NULL AUTO_INCREMENT,
  'nome_site' varchar(255) DEFAULT NULL,
  'thema' char(50) NOT NULL,
  PRIMARY KEY ('ID_Config')
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
  

The auto_increment and primary key went straight to the CREATE command.

Now when I export it looks like this:

CREATE TABLE IF NOT EXISTS 'config' (
  'Id' int(11) NOT NULL,
  'versao' varchar(10) NOT NULL,
  'sitemodelo' varchar(4) NOT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 ;

--
-- Indexes for table 'config'
--
ALTER TABLE 'config'
 ADD PRIMARY KEY ('Id');

--
-- AUTO_INCREMENT for table 'config'
--
ALTER TABLE 'config'
MODIFY 'Id' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

A class I found on the internet class.leitor_sql.php does not read the rest of the commands, only what is inside CREATE .

Question: Is there a way to change the export setting of mysql in phpMyAdmin?

Thank you.

    
asked by anonymous 05.01.2016 / 18:10

2 answers

1

The problem is with the export template. PhpMyAdmin offers you different types of exports, such as __DB__ and __TABLE__ , for example. Each template comes in a different format. The second format is for __DB__ , if I'm not mistaken, because it first brings all the tables and then all the records. If in the table definition it would bring up preconfigured auto_increment, the inserts that would come at the end of the file would not start with the number 1.

To get the first template, I believe you should export only the structure of the tables, not the entire database.

    
06.01.2016 / 17:20
1

What happens is that the second form generates a data consistency.

The CREATE TABLE IF NOT EXISTS command will create a new table if it does not exist.

So, at that point it may cause data conflict if the auto_increment of the existing table is different from the table you are importing.

So there is a change in the table schema next, separately from the command CREATE .

Use an SQL command importer that supports multiple query recognition. PHPMyAdmin itself, for example.

    
06.01.2016 / 21:31