Sprechen Sie über implizite Konvertierung in MySQL

Im Laufe der Arbeit werden Sie auf viele Fälle impliziter Konvertierung stoßen. Implizite Konvertierungen können nicht nur zu langsamen Abfragen führen, sondern auch zu ungenauen Daten. Dieser Artikel veranschaulicht dies anhand mehrerer Fälle aus der Produktion.

Grundlagen

Die Prinzipien der Vergleichsoperationen werden im offiziellen MySQL-Dokument beschrieben: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

Wenn eine der beiden Seiten des Urteilssymbols NULL ist, ist das Ergebnis null, es sei denn, es wird ein sicheres Äquivalenzurteil <=> verwendet

(keine) 05:17:16 >select null = null;
+-------------+
| null = null |
+-------------+
| NULL |
+-------------+
1 Zeile im Satz (0,00 Sek.)

(keine) 05:34:59 >Null auswählen <=> Null;
+-----------------+
| null <=> null |
+-----------------+
| 1 |
+-----------------+
1 Zeile im Satz (0,00 Sek.)

(keine) 05:35:51 >Null auswählen != 1;
+-------------+
| null != 1 |
+-------------+
| NULL |
+-------------+
1 Zeile im Satz (0,00 Sek.)

So ermitteln Sie, ob die linke und die rechte Seite vom gleichen Typ sind. Wenn es sich beispielsweise bei beiden um Zeichenfolgen handelt, vergleichen Sie sie als Zeichenfolgen. Handelt es sich um eine Zahl, wird der Vergleich als Zahl durchgeführt.

Hinweis: Beim häufigeren Vergleich von Zeichenfolgen und Zahlen kann MySQL die Daten nicht über den Index finden, wenn das Zeichenfolgenfeld ein Indexfeld ist, wie im folgenden Beispiel:

(keine) 05:39:42 >Auswahl 1='1';
+----------+
| 1='1' |
+----------+
| 1 |
+----------+
1 Zeile im Satz (0,00 Sek.)

(keine) 05:39:44 >Auswahl 1='1A';
+--------+
| 1='1A' |
+--------+
| 1 |
+--------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

(keine) 05:39:47 >select 1='1 '; ##Nach 1+--------+ steht ein Leerzeichen.
| 1='1 ' |
+--------+
| 1 |
+--------+
1 Zeile im Satz (0,00 Sek.)

MySQL betrachtet die Zahl 1 als gleich „1“, „1_“ und „1A“, daher ist es nicht möglich, den spezifischen Wert durch eine binäre Indexsuche genau zu lokalisieren.

Hexadezimal vergleicht Zeichenfolgen als Binärzeichenfolgen.

So ermitteln Sie, ob die linke Seite des Symbols vom Typ „Zeitstempel“ oder „Datum/Uhrzeit“ ist und die rechte Seite eine Konstante ist. Vor dem Vergleich wird die Konstante in einen Zeittyp konvertiert.

Implizite Konvertierung

Die Feldtypen sind unterschiedlich

In allen anderen Fällen werden die Argumente als Gleitkommazahlen (reelle Zahlen) verglichen.

Zusätzlich zu den anderen oben aufgeführten Vergleichstypen konvertiert das System Felder und Parameter zum Vergleich in Gleitkommatypen. Vergleiche mit Gleitkommazahlen (oder in Gleitkommazahlen umgewandelten Werten) sind Näherungswerte, da solche Zahlen nicht exakt sind. Schauen Sie sich die folgenden zwei Beispiele an

>Wählen Sie '190325171202362933' = 190325171202362931;
+----------------------------------------------+
| '190325171202362933' = 190325171202362931 |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

>Wählen Sie '190325171202362936' = 190325171202362931;
+----------------------------------------------+
| '190325171202362936' = 190325171202362931 |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

Die intuitiv ungleich erscheinenden Werte werden nach der Beurteilung als gleich tatsächlich als 1 zurückgegeben. Dies bringt zwei Probleme mit sich: Der Index kann nicht verwendet werden und die Ergebnisdaten sind ungenau

>Wählen Sie '190325171202362931'+0,0;
+--------------------------+
| '190325171202362931'+0,0 |
+--------------------------+
| 1.9032517120236294e17 |
+--------------------------+
1 Zeile im Satz (0,00 Sek.)

>Wählen Sie '190325171202362936'+0,0;
+--------------------------+
| '190325171202362936'+0,0 |
+--------------------------+
| 1.9032517120236294e17 |
+--------------------------+
1 Zeile im Satz (0,00 Sek.)

Wandeln Sie die obigen Werte in Gleitkommazahlen um. Beide ergeben 1,9032517120236294e17. Wenn sie also gleich sind, sind sie wahr und es wird „True“ zurückgegeben.

in Parametern enthalten mehrere Typen

Für einen bestimmten Fall verweisen wir auf den vorherigen Artikel MySQL-Optimierungsfall 1, in dem die Datentypen im In-Set unterschiedlich sind und der Ausführungsplan den Index nicht verwendet

Es gibt einen Fall genau wie diesen im Taobao MySQL Monthly Report (http://mysql.taobao.org/monthly/2017/12/06/), den ich jedem empfehle. Kurz gesagt, es gibt ein Urteil am Eingang von IN. Wenn die Feldtypen in IN nicht kompatibel sind, wird davon ausgegangen, dass der Index nicht verwendet werden kann.

Die Zuweisungslogik von arg_types_compatible ist:

wenn (Typ_Anzahl == 1) 
    arg_types_compatible = WAHR;

Das heißt, wenn mehr als ein Feldtyp in der IN-Liste erscheint, werden die Typen als inkompatibel betrachtet und der Index kann nicht verwendet werden.

Inkonsistente Zeichensatztypen

Vorbereitung der Umgebung:

TABELLE ERSTELLEN `t1` (
`id` int(11) NICHT NULL AUTO_INCREMENT,
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
Primärschlüssel (`id`),
SCHLÜSSEL `idx_c1` (`c1`),
SCHLÜSSEL `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


TABELLE ERSTELLEN `t2` (
`id` int(11) NICHT NULL AUTO_INCREMENT,
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
Primärschlüssel (`id`),
SCHLÜSSEL `idx_c1` (`c1`),
SCHLÜSSEL `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


in t1(c1,c2) ​​einfügen Werte('a','a'),('b','b'),('c','c'),
('d','d'),('e','e');
in t2(c1,c2) ​​einfügen Werte('a','a'),('b','b'),('c','c'),
('d','d'),('e','e');

Testergebnisse

Zusammenfassung

Ich hoffe, dass Entwickler durch die oben genannten Fälle und die Einführung grundlegender Kenntnisse Umwege vermeiden können. Beim Entwickeln und Schreiben von SQL müssen sie den Feldtyp klar definieren, insbesondere Felder wie id, xxxid, xxxno, die wie numerische Typen aussehen, aber tatsächlich Zeichentypen sein können.

Oben sind die Details der impliziten Konvertierung in MySQL aufgeführt. Weitere Informationen zur impliziten MySQL-Konvertierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!