phpmyadmin schema dump, how to avoid alter table for key definitions?



  • When I dump the mysql db schema with phpmyadmin (5.2.0), I would like to get something like this:

    CREATE TABLE `MEASURE_TABLE` (
      `measureID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      `sensorID` int(10) UNSIGNED NOT NULL,
      `timestamp` int(10) UNSIGNED NOT NULL,
      PRIMARY KEY (`measureID`),
      FOREIGN KEY (`sensorID`) REFERENCES `LOGICAL_SENSOR_TABLE` (`sensorID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    Instead I get something like this (key defined via "ALTER TABLE"):

    DROP TABLE IF EXISTS `MEASURE_TABLE`;
    CREATE TABLE `MEASURE_TABLE` (
      `measureID` bigint UNSIGNED NOT NULL,
      `sensorID` int UNSIGNED NOT NULL,
      `timestamp` int UNSIGNED NOT NULL,
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
    

    ALTER TABLE MEASURE_TABLE
    ADD PRIMARY KEY (measureID),
    ADD KEY timestamp (timestamp),
    ADD KEY sensorID (sensorID);

    ALTER TABLE MEASURE_TABLE
    ADD CONSTRAINT MEASURE_TABLE_ibfk_1 FOREIGN KEY (sensorID) REFERENCES LOGICAL_SENSOR_TABLE (sensorID);

    I'm pretty sure they are equivalent, but for me the first version is much more readable. I had a previous schema dump from phpmyadmin (not done by me), that was in the first format. It was a different version though (4.6.6deb5).

    I couldn't find the option in the export menu, is it possible to change it?



  • I, too, wish the dump had the entire table definition in one spot.

    To get the entire definition afterwards, do

    SHOW CREATE TABLE tablename
    

    Such is often very helpful in programming questions about MySQL/MariaDB.

    Some lame explanations for what you are seeing:

    The PRIMARY KEY is an index and a uniqueness constraint; in most situations, it would be faster (for INSERTs) to have it already declared in the table. FULLTEXT and SPATIAL are probably required to be created after loading the data. For other INDEXes (including UNIQUE), the speed is more debatable; it works either way.

    In particular, ALTERing the PRIMARY KEY always requires copying the table! Very inefficient.

    This forum (and others) are littered with the same question about FOREIGN KEYs -- an error message because the referenced table has not been declared before the FK is declared. The simple fix is to push them to after declaring all tables.

    I guess that the tool you are asking about did something simple that made it work, but without thinking about performance. File a bug report against the tool, if you like. (and add a link here)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2