Databases
StackOverflow2013
Postsdbo
POHow can I test that a SQL "CREATE TABLE" statement is equivalent to an existing table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I test that a SQL "CREATE TABLE" statement is equivalent to an existing table?
    text
    copied!<p>For each table in my MySQL database I have a <code>mytablename.sql</code> file which contains the <code>CREATE TABLE</code> statement for that table.</p> <p>I want to add a test to check that noone has added/altered a column in the live database without updating this file - that it, I want to check that I can re-create an empty database using these scripts.</p> <p>How can I do this reliably?</p> <p>One option would be using <code>SHOW COLUMNS FROM mytable</code> (or <code>DESCRIBE mytable</code>), which, on the command line would output in a tabular form like this:</p> <pre><code>+---------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------------------+----------------+ | pk_guid | int(10) unsigned | NO | PRI | NULL | auto_increment | | d_start | datetime | NO | MUL | 0000-00-00 00:00:00 | | | d_end | datetime | NO | MUL | 0000-00-00 00:00:00 | | +---------+------------------+------+-----+---------------------+----------------+ </code></pre> <p>And then create a temporary table and compare the results.</p> <p>This would be fine, except that if any columns have been added to the live database then the results might not be in the same row order.</p> <p>Unfortunately, it doesn't seem to be possible to use <code>ORDER BY</code> with <code>SHOW COLUMNS</code>.</p> <p>Another option would be to use <code>SHOW CREATE TABLE</code>, but that includes information such as the <code>AUTO_INCREMENT</code> counter value, which I don't care about.</p> <p>Is there a better way of doing this?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload