PhpMyAdmin export does not include PRIMARY KEY as

问题:

Export of the struture of the same table with PhpMyAdmin:

`DROP TABLE IF EXISTS `test_apprentis`;
CREATE TABLE IF NOT EXISTS `test_apprentis` (
  `a_id` smallint(10) NOT NULL,
  `a_promo_id` smallint(11) NOT NULL,
  `a_cursus` smallint(10) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=3665 DEFAULT CHARSET=utf8;`

Export with mysqldump:

DROP TABLE IF EXISTS `test_apprentis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_apprentis` (
  `a_id` smallint(10) NOT NULL AUTO_INCREMENT,
  `a_promo_id` smallint(11) NOT NULL,
  `a_cursus` smallint(10) DEFAULT NULL,
  PRIMARY KEY (`a_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3665 DEFAULT CHARSET=utf8;

With PhpMyAdmin, there is no AUTO_INCREMENT nor PRIMARY KEY Why ?

回答1:

It is not a silly question, phpMyAdmin used to include the KEYs at the end of the CREATE TABLE statement and their characteristics right next to their column name declaration. Following their 2014 changes log, after version 4.2.0.0 (2014-05-08) the export file structure was changed:

• rfe #1004 Create indexes at the end in SQL export

So we must look the end of the exported file to find all the indexes info

回答2:

I trapped myself as a novice! I have looked at the contents of the window displayed on the screen, without down vertical lift bar! Export by phpMyAdmin adds auto-incremented column information and PRIMARY KEY by ALTER TABLE queries after creating the table.

DROP TABLE IF EXISTS `test_apprentis`;
CREATE TABLE IF NOT EXISTS `test_apprentis` (
  `a_id` smallint(10) NOT NULL,
  `a_promo_id` smallint(11) NOT NULL,
  `a_cursus` smallint(10) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=3665 DEFAULT CHARSET=utf8;

ALTER TABLE `test_apprentis`
  ADD PRIMARY KEY (`a_id`);

ALTER TABLE `test_apprentis`
  MODIFY `a_id` smallint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3665;

Please accept my apologies for this silly question.

回答3:

If you want to revert this behavior to how it used to work, edit the file

phpMyAdmin/libraries/plugins/export/ExportSql.class.php

In the file, the code block starting with the below line needs to be skipped

if (preg_match('@CONSTRAINT|KEY@', $create_query)) {

The easiest way to do that is changing that line to

if (false && preg_match('@CONSTRAINT|KEY@', $create_query)) {

回答4:

Another thing to watch out for in this regard when using export and import is the limit on number of transactions when importing. There is no warning that the number has been exceeded and the excess have not been executed. This means that some tables might be present but not have had their Indexes and/or AUTO_INCREMENTS updated particularly when importing whole databases. Depending on the number of tables and their structure there could be three times that number of transactions required so it would pay to check and adjust the relevant parameter in the php.ini file.