Wie kann ich testen, ob eine SQL-Anweisung "CREATE TABLE" einer bestehenden Tabelle entspricht?

Question

Für jede Tabelle in meiner MySQL-Datenbank habe ich eine mytablename.sql Datei, die die CREATE TABLE Anweisung für diese Tabelle.

Ich möchte einen Test hinzufügen, um zu überprüfen, dass niemand eine Spalte in der Live-Datenbank hinzugefügt/geändert hat, ohne diese Datei zu aktualisieren - das heißt, ich möchte überprüfen, ob ich eine leere Datenbank mit diesen Skripten neu erstellen kann.

Wie kann ich dies zuverlässig tun?

Eine Möglichkeit wäre die Verwendung von SHOW COLUMNS FROM mytable (oder DESCRIBE mytable ), die in der Befehlszeile in tabellarischer Form wie folgt ausgegeben würde:

+---------+------------------+------+-----+---------------------+----------------+
| 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 |                | 
+---------+------------------+------+-----+---------------------+----------------+

Erstellen Sie dann eine temporäre Tabelle und vergleichen Sie die Ergebnisse.

Das wäre in Ordnung, außer wenn der Live-Datenbank irgendwelche Spalten hinzugefügt wurden, dann sind die Ergebnisse möglicherweise nicht in der gleichen Zeilenreihenfolge.

Leider scheint es nicht möglich zu sein, die ORDER BY con SHOW COLUMNS .

Eine andere Möglichkeit wäre die Verwendung von SHOW CREATE TABLE sondern auch Informationen wie zum Beispiel die AUTO_INCREMENT Gegenwert, der mir egal ist.

Gibt es eine bessere Möglichkeit, dies zu tun?

Answer 1

Du hast geschrieben:

Eine Möglichkeit wäre die Verwendung von SHOW COLUMNS ... in der Befehlszeile .... [aber die Spalten] sind möglicherweise nicht in der gleichen Zeilenreihenfolge.

Sie können diese Reihenfolge erzwingen, post hoc . Auf einem UNIX-ähnlichen System können Sie einfach:

$ mysql -Bse 'SHOW COLUMNS FROM possibly_altered' | sort

und vergleichen Sie diese mit ähnlich sortierten und sortierten SHOW Ausgabe in einer temporären Tabelle, die von Ihrem .sql Datei. (Die -Bs unterdrückt etwas davon mysql(1) ausgefallene Formatierungen und Kopfzeilen, die nicht benötigt werden und für einen eher programmatischen Vergleich etwas unpraktisch sind).

Answer 2

Ich habe dies mit einem etwas ghetto/duct-tape PHP-Shell-Skript über Datenbankserver (dev/production setup) in der Vergangenheit getan. Angenommen, Sie haben 2 Server, und einer von ihnen ist "richtig":

• eine Liste der zu prüfenden Spalten für jede Tabelle erstellen. SHOW CREATE TABLE funktioniert, oder einfach SELECT * FROM x LIMIT 1 wenn Sie nicht an möglicherweise geänderten Datentypen/Standardwerten interessiert sind.
• array_sort() die Feldnamen alphabetisch sortieren und in einer Schleife alle möglichen Änderungen vergleichen, die Sie interessieren.

Ich empfehle das aber nicht! Ich habe dies verwendet, weil die Systemadministratoren nichts davon hielten, einigen Outsourcing-Leuten volle DB-Berechtigungen zu gewähren, also war es nur ein schnelles Sicherheitsnetz, nichts so Geplantes oder Dauerhaftes wie Unit-Tests.

---

Ein eleganterer und saubererer Weg, dies zu tun (allerdings nur für MySQL5+?), wäre die Verwendung der information_schema Systemdatenbank, insbesondere die COLUMNS Tisch. Wenn Sie nicht über 2 Server verfügen, wäre eine separate Datenbank zum Speichern/Kopieren von Spalteninformationen (z. B. ein Klon von information_schema mit dem neuesten "richtigen" Schema) ebenfalls geeignet. Auf diese Weise könnten Sie auch neue/gelöschte Tabellen, Indizes, Trigger, Prozeduren, Benutzer/Berechtigungen usw. vergleichen.