Die Fallstricke und Lösungen, die durch den Standardwert von sql_mode in MySQL 5.7 verursacht werden

Während der normalen Projektentwicklung, wenn die MySQL-Version von 5.6 auf 5.7 aktualisiert wird. Wenn ein DBA die Auswirkungen eines Upgrades einer Datenbankversion berücksichtigt, sind im Allgemeinen mehrere Punkte zu beachten:

sql_modus
Optimiererschalter

Der Hauptinhalt dieses Artikels sind die Fallstricke, die durch den Standard-SQL-Mode-Wert nach dem Upgrade von MySQL auf Version 5.7 entstehen, sowie die entsprechenden Lösungen.

Fall 1: ONLY_FULL_GROUP_BY

Problembeschreibung

Nach dem Upgrade der MySQL-Version von 5.6 auf 5.7 werden einige SQL-Ausführungsfehler gemeldet. Die Fehlerinformationen lauten wie folgt:

FEHLER 1055 (42000): Ausdruck Nr. 3 der XXXXXX-Liste ist nicht in der GROUP BY-Klausel enthalten und enthält die nicht aggregierte Spalte „XXXXX.XXXXXX“, die nicht funktional von den Spalten in der GROUP BY-Klausel abhängig ist. Dies ist nicht kompatibel mit sql_mode=only_full_group_by

Der Grund für dieses Problem ist, dass sich der Standardwert von sql_mode nach dem Upgrade von Version 5.6 auf Version 5.7 geändert hat. Der Standardwert von sql_mode in Version 5.7 ist ONLY_FULL_GROUP_BY. Die Bedeutung dieser Option bedeutet, dass für SQL, das GROUP BY für Abfragen verwendet, Felder, die nicht in GROUP BY erscheinen, nicht im SELECT-Teil erscheinen dürfen, d. h. die von SELECT abgefragten Felder müssen in GROUP BY erscheinen oder Aggregatfunktionen verwenden.

Lösung

Lösung 1 (nicht empfohlen): Ändern Sie den sql_mode-Wert der Version 5.7 und entfernen Sie ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY wird verwendet, um SQL-Spezifikationen zu stärken. Sein Zweck besteht darin, die Ergebnisse von SQL-Abfragen standardisierter und genauer zu machen.

Ohne die Spezifikationsbeschränkung ONLY_FULL_GROUP_BY darf das folgende SQL ausgeführt werden: SELECT a,b,c FROM t GROUP BY a. SQL gruppiert nach dem Wert des Felds a. Wenn derselbe Feldwert a mehreren b- oder c-Werten entspricht, sind die b- und c-Werte in den Abfrageergebnissen unsicher.

Lösung 2: SQL neu schreiben

Fall 2: NO_ZERO_DATE & NO_ZERO_IN_DATE & Zeitzone

Problembeschreibung

Fehlerbehebung Phase 1

Nach dem Upgrade der MySQL-Version von 5.6 auf 5.7 ist der Tabellenerstellungsprozess fehlgeschlagen:

mysql> CREATE TABLE `t_manager` (
  .....
  -> `CREATE_DATETIME` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` Zeitstempel NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Änderungszeit',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Löschstatus 1: Gelöscht 0: Nicht gelöscht',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Aktivierungsstatus 1: Aktivieren 0: Deaktivieren',
  -> PRIMÄRSCHLÜSSEL (`CACHE_ID`)
  ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
FEHLER 1067 (42000): Ungültiger Standardwert für 'MODIFY_DATETIME'

Die Fehlermeldung besagt, dass der Standardwert des Felds MODIFY_DATETIME ungültig ist. Da ich gerade ein Upgrade von Version 5.6 auf 5.7 durchgeführt habe, habe ich den Standardwert sql_mode in 5.7 überprüft. Es stellt sich heraus, dass es zwei Optionen gibt, die Auswirkungen haben können:

NO_ZERO_DATE
KEINE_NULL_IM_DATUM

Fehlerbehebung Phase 2

Die Lösung besteht also darin, den Standardwert gemäß den Anforderungen von NO_ZERO_DATE und NO_ZERO_IN_DATE festzulegen und den Standardwert des Felds MODIFY_DATETIME auf „1001-01-01 01:01:01“ festzulegen. Als Ergebnis wird festgestellt, dass die Tabelle nicht erfolgreich erstellt werden kann:

mysql>CREATE TABLE `t_manager` (
  .....
  -> `CREATE_DATETIME` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` Zeitstempel NOT NULL DEFAULT '1001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Änderungszeit',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Löschstatus 1: Gelöscht 0: Nicht gelöscht',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Aktivierungsstatus 1: Aktivieren 0: Deaktivieren',
  -> PRIMÄRSCHLÜSSEL (`CACHE_ID`)
  ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
FEHLER 1067 (42000): Ungültiger Standardwert für 'MODIFY_DATETIME'

Ich habe alle sql_mode-Werte überprüft und festgestellt, dass sie alle den Spezifikationen entsprachen, aber die Tabelle konnte trotzdem nicht erfolgreich erstellt werden. Ich musste im offiziellen Handbuch nachschlagen, um die Einführung zum Zeitstempel zu finden:

Der Datentyp TIMESTAMP wird für Werte verwendet, die sowohl Datums- als auch Zeitanteile enthalten. TIMESTAMP hat einen Bereich von '1970-01-01 00:00:01' UTC bis '2038-01-19 03:14:07' UTC.

Fehlerbehebung Phase 3

Es ist ersichtlich, dass die offizielle Definition des Wertebereichs des Zeitstempelfelds „1970-01-01 00:00:01“ bis „2038-01-19 03:14:07“ lautet. Es stellt sich heraus, dass der von uns festgelegte Standardwert nicht innerhalb des Zeitstempelbereichs liegt. Ändern Sie daher den Standardwert erneut:

mysql>CREATE TABLE `t_manager` (
  .....
  -> `CREATE_DATETIME` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` Zeitstempel NOT NULL DEFAULT '1970-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Änderungszeit',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Löschstatus 1: Gelöscht 0: Nicht gelöscht',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Aktivierungsstatus 1: Aktivieren 0: Deaktivieren',
  -> PRIMÄRSCHLÜSSEL (`CACHE_ID`)
  ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
FEHLER 1067 (42000): Ungültiger Standardwert für 'MODIFY_DATETIME'

Schade! Die Tabelle lässt sich immer noch nicht erfolgreich erstellen. Ich war mit meinem Latein am Ende und bat einen Kollegen um Hilfe. Er sagte, er würde es auf seiner eigenen Maschine versuchen, und die gleiche Anweisung funktionierte erfolgreich auf seinem MySQL, ebenfalls Version 5.7.23.

Ich komme einfach nicht dahinter.

In einem Wutanfall verglich ich die Parameterwerte beider Seiten und fand tatsächlich die Ursache des Unterschieds.

Schauen wir uns noch einmal den durch das Zeitstempelfeld definierten Bereich an:

Der Datentyp TIMESTAMP wird für Werte verwendet, die sowohl Datums- als auch Zeitanteile enthalten. TIMESTAMP hat einen Bereich von '1970-01-01 00:00:01' UTC bis '2038-01-19 03:14:07' UTC.

Dieser Zeitbereich bezieht sich auf den Zeitbereich der Zeitzone UTC. Wenn die Testumgebung auf die Zeitzone CST East 8 eingestellt ist, muss der entsprechende Zeitbereich ebenfalls um 8 Stunden erhöht werden. Daher wird der Standardwert des Zeitstempelfelds in „1970-01-01 08:00:01“ geändert und die Tabelle schließlich erfolgreich erstellt.

mysql>CREATE TABLE `mn_cache_refresh_manager` (
  ......
  -> `CREATE_DATETIME` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` Zeitstempel NOT NULL DEFAULT '1970-01-01 08:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Änderungszeit',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Löschstatus 1: Gelöscht 0: Nicht gelöscht',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Aktivierungsstatus 1: Aktivieren 0: Deaktivieren',
  -> PRIMÄRSCHLÜSSEL (`CACHE_ID`)
  ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Abfrage OK, 0 Zeilen betroffen (0,02 Sek.)

Zusammenfassen

Oben sind die Fallstricke und Lösungen aufgeführt, die der vom Herausgeber eingeführte Standardwert von sql_mode in MySQL 5.7 mit sich bringt. Ich hoffe, es wird allen helfen. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und der Herausgeber wird Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!