Detaillierte Erklärung zu sinnvollen Einstellungen des MySQL sql_mode

Sinnvolle Einstellung des MySQL sql_mode

sql_mode ist eine Variable, die leicht übersehen wird. Der Standardwert ist null. Mit dieser Einstellung können einige unzulässige Vorgänge zugelassen werden, z. B. das Einfügen einiger unzulässiger Daten. In der Produktionsumgebung muss dieser Wert auf den strikten Modus eingestellt werden, daher muss auch die Datenbank der Entwicklungs- und Testumgebung entsprechend eingestellt werden, damit Probleme während der Entwicklungs- und Testphase erkannt werden können.

1. Das SQL-Modell wird häufig zur Lösung der folgenden Problemtypen verwendet:

(1) Durch Einstellen des SQL-Modus können Sie die Datenüberprüfung mit unterschiedlichen Strengegraden durchführen und so die Datenbereitschaft effektiv sicherstellen.

(2) Indem wir das SQL-Modell auf den Loose-Modus setzen, können wir sicherstellen, dass die meisten SQL-Anweisungen der Standard-SQL-Syntax entsprechen. Auf diese Weise müssen wir beim Migrieren von Anwendungen zwischen verschiedenen Datenbanken keine größeren Änderungen an den geschäftlichen SQL-Anweisungen vornehmen.

(3) Bevor Sie Daten zwischen verschiedenen Datenbanken migrieren, können Sie den SQL-Modus einstellen, um die Migration von Daten von MySQL in die Zieldatenbank zu erleichtern.

2. Allgemeine Werte von sql_mode sind wie folgt:

1. NUR_VOLLSTÄNDIGE_GRUPPE NACH:

Bei GROUP BY-Aggregationsvorgängen gilt: Wenn die Spalte in SELECT nicht in GROUP BY erscheint, ist dieses SQL ungültig, da die Spalte nicht in der GROUP BY-Klausel enthalten ist.

2. KEIN_AUTO_WERT_AUF_NULL:

Dieser Wert wirkt sich auf Einfügungen in Auto-Increment-Spalten aus. Standardmäßig bedeutet das Einfügen von 0 oder NULL, dass der nächste Auto-Inkrement-Wert generiert wird. Diese Option ist nützlich, wenn der Benutzer einen Wert von 0 einfügen möchte und die Spalte automatisch inkrementiert wird.

3. STRICT_TRANS_TABLES: Strenger Modus

Wenn in diesem Modus ein Wert nicht in eine Transaktionstabelle eingefügt werden kann, wird der aktuelle Vorgang unterbrochen und es gibt keine Einschränkung für nicht-transaktionale Tabellen.

4. KEINE_NULL_IM_DATUM:

Im strikten Modus sind Tages- und Monatswerte von Null nicht zulässig.

5. KEIN_NULLDATUM:

Durch Festlegen dieses Werts lässt die MySQL-Datenbank das Einfügen von Nulldaten nicht zu. Das Einfügen von Nulldaten führt zu einem Fehler statt einer Warnung.

6. FEHLER BEI DIVISION DURCH NULL:

Wenn bei einem INSERT oder UPDATE Daten durch Null geteilt werden, wird ein Fehler und keine Warnung generiert. Wenn dieser Modus nicht angegeben ist, gibt MySQL NULL zurück, wenn der Wert durch Null geteilt wird.

7. NO_AUTO_CREATE_USER:

Verhindern Sie, dass GRANT Benutzer mit leeren Passwörtern erstellt

8. KEIN MOTORERSATZ:

Wenn die erforderliche Speicher-Engine deaktiviert oder nicht kompiliert ist, wird ein Fehler ausgegeben. Wenn dieser Wert nicht festgelegt ist, wird stattdessen die Standardspeicher-Engine verwendet und eine Ausnahme ausgelöst.

9. PIPES_AS_CONCAT:

Behandeln Sie „||“ als einen String-Verkettungsoperator und nicht als einen ODER-Operator. Dies entspricht der Oracle-Datenbank und ähnelt der String-Verkettungsfunktion Concat.

10. ANSI_QUOTES:

Wenn ANSI_QUOTES aktiviert ist, können Sie keine Anführungszeichen zum Zitieren von Zeichenfolgen verwenden, da diese als Bezeichner interpretiert werden.

Die SQL_Mode-Einstellungen von ORACLE entsprechen: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

Wenn Sie MySQL verwenden, können Sie den SQL-Modus von MySQL wie folgt festlegen, damit jeder weiterhin an die Verwendung von Oracle gewöhnt ist:

Fügen Sie die folgende Konfiguration in my.cnf hinzu

[mysqld]
sql_mode = 'NUR_VOLLSTÄNDIGE_GRUPPE_BY, KEIN_AUTOMATISCHER_WERT_ BEI_NULL, STRICT_TRANS_TABLES, KEINE_NULL_IM_DATUM, KEIN_NULL_DATUM,
FEHLER BEI DIVISION DURCH NULL, KEIN AUTOMATISCHES ERSTELLEN VON BENUTZERN, PIPES_AS_CONCAT, ANSI_QUOTES'

3. Hinweise zum Loose-Modus und Strict-Modus:

【1】Hinweis: Die Standard-SQL-Modus-Parameter von MySQL 5.6 und MySQL 5.7 sind unterschiedlich.
Der Modus von 5.6 ist NO_ENGINE_SUBSTITUTION, was tatsächlich einen Nullwert darstellt, was keiner Moduseinstellung entspricht und als lockerer Modus verstanden werden kann.
Der Modus von 5.7 ist STRICT_TRANS_TABLES, also der strikte Modus.

[2] Wenn der Loose-Modus eingestellt ist, werden beim Einfügen von Daten auch falsche Daten akzeptiert und es wird kein Fehler gemeldet.

Beispiel: Wenn ich eine Tabelle erstelle, gibt es in der Tabelle ein Feld mit dem Namen „Name“, und der für den Namen festgelegte Feldtyp ist char(10). Wenn ich Daten einfüge und die Länge eines Datenelements, das dem Namensfeld entspricht, 10 überschreitet, z. B. „1234567890abc“, was die festgelegte Feldlänge von 10 überschreitet, wird kein Fehler gemeldet und die ersten zehn Zeichen werden gespeichert. Mit anderen Worten, Ihre Daten werden als „1234567890“ gespeichert und „abc“ wird weg sein. Wir wissen jedoch, dass die von uns eingegebenen Daten falsch sind, weil sie die Feldlänge überschreiten, aber es wird kein Fehler gemeldet und MySQL verarbeitet und akzeptiert sie selbst. Dies ist die Auswirkung des Loose-Modus.

Tatsächlich sollten wir in Entwicklungs-, Test-, Produktions- und anderen Umgebungen den strikten Modus verwenden. Wenn dieser Fehler auftritt, sollte ein Fehler gemeldet werden. Daher ändert MySQL Version 5.7 den Standardwert von sql_mode in den strikten Modus, und selbst wenn wir MySQL 5.6 verwenden, sollten wir ihn selbst in den strikten Modus ändern.

Darüber hinaus möchten Datenbanken wie MySQL alle Datenoperationen selbst übernehmen, einschließlich der Datenüberprüfung. Tatsächlich sollten wir diese Überprüfungen häufig auf Programmebene des von uns entwickelten Projekts durchführen. Obwohl es beim Schreiben des Projekts mühsam ist, einige Schritte zu schreiben, ist es danach viel bequemer, wenn wir die Datenbank oder das Projekt migrieren. Sie werden feststellen, dass MySQL im Laufe der Zeit neben der Datenüberprüfung noch viele weitere Dinge kann, die Sie in Ihrem Programm tun.

【3】Probleme, die nach dem Wechsel in den strengen Modus auftreten können:

Wenn der Einstellungsmodus NO_ZERO_DATE enthält, lässt die MySQL-Datenbank das Einfügen von Nulldaten nicht zu, und das Einfügen von Nulldaten führt zu einem Fehler statt einer Warnung.
Beispielsweise wird einer TIMESTAMP-Spalte in einer Tabelle (sofern sie nicht als NULL deklariert ist oder eine DEFAULT-Klausel enthält) automatisch ein DEFAULT von „0000-00-00 00:00:00“ (Null-Zeitstempel) zugewiesen, oder die Tagesspalte in der Tabelle dieses Tests ermöglicht standardmäßig die Einfügung eines Nulldatums „0000-00-00“ COMMENT „Datum“. Diese erfüllen offensichtlich nicht die Anforderungen von NO_ZERO_DATE im SQL-Modus und melden einen Fehler.

4. Moduseinstellung und -änderung (am Beispiel der Lösung des obigen Problems):

【Methode 1】:

Führen Sie zuerst „select @@sql_mode“ aus, kopieren Sie den abgefragten Wert und löschen Sie NO_ZERO_IN_DATE, NO_ZERO_DATE, führen Sie dann „set sql_mode = 'modified value'“ oder „set session sql_mode = 'modified value'“ aus.

Beispiel: set session sql_mode='STRICT_TRANS_TABLES'; in den strikten Modus wechseln

Hinweis: Diese Methode ist nur in der aktuellen Sitzung wirksam und wird nicht wirksam, wenn die aktuelle Sitzung geschlossen wird.

【Methode 2】:
Führen Sie zuerst „select @@global.sql_mode“ aus, kopieren Sie den abgefragten Wert und löschen Sie NO_ZERO_IN_DATE und NO_ZERO_DATE. Führen Sie dann „set global sql_mode = 'modified value'“ aus.

Hinweis: Diese Methode ist im aktuellen Dienst wirksam und wird nach einem Neustart des MySQL-Dienstes ungültig.

【Methode 3】:
1. Fügen Sie im MySQL-Installationsverzeichnis oder in der Datei my.cnf (my.ini-Datei unter Windows) sql_mode = hinzu.
"NUR_VOLLSTÄNDIGE_GRUPPE_NUR, STRENGE_TRANS_TABELLEN, FEHLER_FÜR_DIVISION_DURCH_NULL, KEIN_AUTOMATISCHES_ERSTELLEN_BENUTZER, KEIN_ENGINE_SUBSTITUTION"

Fügen Sie my.ini Folgendes hinzu:

[mysqld]

sql-mode="NUR_VOLLSTÄNDIGE_GRUPPE_NUR, STRENGE_TRANS_TABELLEN, FEHLER_FÜR_DIVISION_DURCH_NULL, KEIN_AUTOMATISCHES_ERSTELLEN_BENUTZER, KEIN_ENGINE_SUBSTITUTION"

2. Starten Sie dann MySQL neu. Führen Sie CMD als Administrator aus, führen Sie „net stop mysql“ aus und führen Sie dann „net start mysql“ aus.

Hinweis: Diese Methode ist dauerhaft. Natürlich ist ein Neustart des MySQL-Dienstes in der Produktionsumgebung verboten. Verwenden Sie daher „Methode 2 + Methode 3“, um das Online-Problem zu lösen. Selbst wenn der MySQL-Dienst dann eines Tages tatsächlich neu gestartet wird, ist dies dauerhaft.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.