Bei der Verwendung von MySQL aufgetretene Probleme

Hier sind einige Probleme, die bei der Verwendung von MySQL auftreten, und die entsprechenden Lösungen.

sql_mode=only_full_group_by verursacht einen Gruppieren-nach-Abfragefehler

Frage
MySQL führt die GROUP BY-Abfrage aus und meldet einen Fehler:

#1 - Der Ausdruck der SELECT-Liste befindet sich nicht in der GROUP BY-Klausel und enthält eine nicht aggregierte Spalte. Dies ist nicht kompatibel mit sql_mode=only_full_group_by

Lösung

# Stellen Sie den richtigen SQL-Modus ein, um das Problem zu lösen. # Melden Sie sich bei MySQL an.
sudo mysql -hlocalhost -uroot -p123123
SETZEN SIE GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Sie können auch die MySQL-Konfigurationsdatei ändern und den MySQL-Dienst neu starten

# Öffnen Sie die MySQL-Konfigurationsdatei sudo vim /etc/mysql/conf.d/mysql.cnf
# Fügen Sie unten den folgenden Satz hinzu sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# Starten Sie dann sudo service mysql restart neu

Detaillierte Erklärung
Der Standardwert von sql_mode ist null. Mit dieser Einstellung können einige unzulässige Vorgänge zugelassen werden, z. B. das Einfügen einiger unzulässiger Daten. Dieser Wert muss in der Produktionsumgebung 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. Übliche Werte für sql_mode sind wie folgt:

ONLY_FULL_GROUP_BY: Bei GROUP BY-Aggregationsvorgängen ist dieses SQL ungültig, wenn die Spalte in SELECT nicht in GROUP BY erscheint, da die Spalte nicht in der GROUP BY-Klausel enthalten ist.
NO_AUTO_VALUE_ON_ZERO: Dieser Wert betrifft das Einfügen von Auto-Increment-Spalten. Standardmäßig wird durch das Einfügen von 0 oder NULL der nächste Auto-Inkrement-Wert generiert. Diese Option ist nützlich, wenn der Benutzer einen Wert von 0 einfügen möchte und die Spalte automatisch inkrementiert wird.
STRICT_TRANS_TABLES: Wenn in diesem Modus ein Wert nicht in eine Transaktionstabelle eingefügt werden kann, wird der aktuelle Vorgang unterbrochen und es werden keine Einschränkungen für Nicht-Transaktionstabellen auferlegt.
NO_ZERO_IN_DATE: Im strikten Modus dürfen Tag und Monat nicht Null sein.
NO_ZERO_DATE: Setzen Sie diesen Wert. Die MySQL-Datenbank erlaubt nicht die Eingabe eines Nulldatums. Das Einfügen eines Nulldatums führt zu einem Fehler statt einer Warnung.
ERROR_FOR_DIVISION_BY_ZERO: Wenn während eines INSERT oder UPDATE Daten durch Null geteilt werden, wird ein Fehler statt einer Warnung generiert. Wenn dieser Modus nicht angegeben ist, gibt MySQL NULL zurück, wenn der Wert durch Null geteilt wird.
NO_AUTO_CREATE_USER: Deaktivieren Sie GRANT beim Erstellen von Benutzern mit leeren Passwörtern
NO_ENGINE_SUBSTITUTION: Gibt einen Fehler aus, wenn die erforderliche Speicher-Engine deaktiviert oder nicht kompiliert ist. Wenn dieser Wert nicht festgelegt ist, wird stattdessen die Standardspeicher-Engine verwendet und eine Ausnahme ausgelöst.
PIPES_AS_CONCAT: Behandeln Sie "||" als String-Verkettungsoperator statt als OR-Operator, was dasselbe ist wie bei der Oracle-Datenbank und ähnlich der String-Verkettungsfunktion Concat.
ANSI_QUOTES: Wenn ANSI_QUOTES aktiviert ist, können Sie keine Anführungszeichen zum Zitieren von Zeichenfolgen verwenden, da diese als Bezeichner interpretiert werden.

Tabellendaten löschen

Problem: Bei der Durchführung einer Datenmigration oder Datenreparatur ist es häufig erforderlich, die Datentabelle zu löschen oder zurückzusetzen.

Lösung

Mit dem Befehl TRUNCATE TABLE table_name können Sie die Datentabelle zurücksetzen. Der Sinn dieses Befehls besteht darin, die Tabelle zu löschen und dann neu aufzubauen. Bei Tabellen mit Constraints funktioniert dies nicht. Sie müssen zuerst die Fremdschlüssel-Constraints deaktivieren und dann den Löschvorgang durchführen.
Eine andere Methode besteht darin, alle Daten in der Tabelle zu löschen und dann den Startwert der Tabellen-ID auf 1 zu setzen.

# Die erste LösungSELECT @@FOREIGN_KEY_CHECKS; # Den Befehl zur Einzelsignatur-Fremdschlüsseleinschränkung anzeigenSET FOREIGN_KEY_CHECKS=0; # Zuerst die Fremdschlüsseleinschränkung deaktivierenTRUNCATE TABLE table_name; # Anschließend die Tabelle zurücksetzen# Die zweite Lösung wird empfohlenDELETE FROM table_name; # Löschbefehl ohne Where-Bedingung zum Löschen aller DatenALTER TABLE table_name AUTO_INCREMENT=1; # Den Anfangswert des Inkrements zurücksetzen

MySQL benötigt zu viel Speicher

Wenn der Serverspeicher nur 1 GB oder weniger beträgt, müssen Sie die Standardkonfiguration von MySQL ändern. Andernfalls werden Sie feststellen, dass Ihr MySQL problemlos 400 MB oder sogar 800 MB Speicher belegt.

Durch die Optimierung der MySQL-Konfiguration kann der Zweck der Reduzierung des Speicherverbrauchs erreicht werden. Die Konfigurationsdatei befindet sich im Allgemeinen unter [[mysqld]] in vim /etc/my.cnf. Detaillierte Informationen zu MySQL-Konfigurationselementen finden Sie auf der offiziellen Website: Detaillierte Konfigurationselemente auf der offiziellen MySQL-Website. Der Server, den ich jetzt verwende, hat eine Single-Core-CPU und 1 GB Arbeitsspeicher.

# Die maximale Anzahl instrumentierter Tabellenobjekte. Die maximale Anzahl geladener Tabelleninstanzen, Standard -1 adaptive performance_schema_max_table_instances = 600
# Die Anzahl der Tabellendefinitionen, die im Definitionscache gespeichert werden können, Standard -1 adaptive table_definition_cache = 400 
# Die Anzahl der offenen Tabellen für alle Threads. Die maximale Anzahl von Tabellen, die von allen Threads geöffnet werden können, der Standardwert ist 2000 
table_open_cache = 128 
# Die Cachegröße der InnoDB-Engine. Wenn der Start fehlschlägt, reduzieren Sie innodb_buffer_pool_size = 600M 
# Die Puffergröße, die für gemeinsame Abfragevorgänge verwendet werden kann, Thread-exklusiv join_buffer_size = 8M

Starten Sie den MySQL-Dienst nach der Änderung der Konfiguration neu, damit die Konfigurationselemente wirksam werden.

Sie können die aktuellen MySQL-Konfigurationsinformationen auf folgende Arten anzeigen:

# Melden Sie sich bei MySQL an
mysql -hlocalhost -uroot -ppassword
# Alle globalen Konfigurationen anzeigen, globale Variablen anzeigen;
# Den globalen Konfigurationsstatus anzeigen. Globale Status anzeigen.
# Bestimmte Konfigurationselemente filtern. Um die Konfigurationselemente herauszufiltern, die mit „innodb“ beginnen, zeigen Sie folgende globale Variablen wie „innodb%“ an.

Temporäre MySQL-Tabellen müssen Aliase haben

Für durch Abfragen generierte temporäre Tabellen müssen Sie „as“ verwenden, um einen Alias ​​zu definieren, auch wenn dieser später nicht verwendet wird. Andernfalls wird ein Fehler gemeldet: Jede abgeleitete Tabelle muss einen eigenen Alias ​​haben.

MySQL: Tabellenspaltennamen abrufen

Angenommen, der Tabellenname lautet table_name. Mit den folgenden drei Befehlen können Sie die Spaltennamen der Tabelle abfragen.

DESC Tabellenname;

DESCRIBE Tabellenname;

Spalten von Personen anzeigen;