Unterschiede zwischen MySQL CHAR und VARCHAR beim Speichern und Lesen

Einführung

Kennen Sie wirklich den Unterschied zwischen CHAR- und VARCHAR-Typen beim Speichern und Lesen?

Lassen Sie mich zunächst einige Schlussfolgerungen ziehen:

1. Beim Speichern wird CHAR vor dem Speichern immer mit Leerzeichen aufgefüllt, unabhängig davon, ob der Benutzer beim Einfügen von Daten am Ende Leerzeichen einfügt.

2. Beim Speichern füllt VARCHAR vor dem Speichern keine Leerzeichen aus. Wenn der Benutzer jedoch beim Einfügen ausdrücklich Leerzeichen hinzufügt, wird es so gespeichert, wie es ist, und nicht gelöscht.

3. Beim Lesen von Daten löscht CHAR immer nachstehende Leerzeichen (auch wenn beim Schreiben Leerzeichen enthalten sind).

4. Beim Lesen von Daten ruft VARCHAR den zuvor gespeicherten Wert immer wahrheitsgetreu ab (wenn beim Speichern ein nachstehendes Leerzeichen vorhanden ist, bleibt dieses weiterhin erhalten und das nachstehende Leerzeichen wird nicht wie bei CHAR gelöscht).

Nachfolgend wird der Testüberprüfungsprozess beschrieben.

1. Testen Sie den CHAR-Typ

Tabellenstruktur:

TABELLE ERSTELLEN `tchar` (
 `id` int(10) unsigned NICHT NULL STANDARD '0',
 `c1` char(20) NICHT NULL STANDARD '',
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

Fügen Sie einige Datensätze ein:

in tchar-Werte einfügen (1, concat('a', repeat(' ',19)));
in tchar-Werte einfügen (2, concat(' ', repeat('a',19)));
in Tchar-Werte einfügen (3, 'a');
in Tchar-Werte einfügen (4, '');
in tchar-Werte einfügen (5, '');

Sehen Sie sich die Speicherstruktur an:

(1) INFIMUM Datensatzoffset:99 Heap-Nr.:0 ...
(2) SUPREMUM-Datensatzoffset:112, Heap-Nr.:1 ...
(3) normaler Datensatz-Offset:126 Heap-Nr.:2 ... <- ID=1
(4) normaler Datensatz-Offset:169 Heap-Nr.:3 ... <- ID=2
(5) normaler Datensatz-Offset:212 Heapno:4 ... <- ID=3
(6) normaler Datensatz-Offset:255 Heapno:5 ... <- ID=4
(7) normaler Datensatz-Offset:298 Heapno:6 ... <- ID=5

Sind Sie ein wenig verwirrt, wenn Sie dieses Zeug sehen? Erinnern Sie sich an das Tool, das ich Ihnen empfohlen habe? Schauen Sie hier: innblock | InnoDB-Seitenbeobachtungstool.

Wie Sie sehen, nimmt jeder Datensatz unabhängig von der Länge der Zeichenfolge tatsächlich 43 (169-126=43) Bytes ein. Daher gilt Schlussfolgerung 1.
Kurz gesagt, der Ursprung von 43 Bytes:
DB_TRX_ID, 6 Bytes.
DB_ROLL_PTR, 7 Bytes.
ID, Int, 4 Bytes.
c1, char(20), 20 Bytes; da es vom Typ CHAR ist, ist ein zusätzliches Byte erforderlich.
Jeder Datensatz benötigt immer zusätzlich 5 Byte Kopfinformationen (Zeilenkopf).
Dies ergibt insgesamt 43 Bytes.

Schauen wir uns die Ergebnisse des Lesens der Tchar-Tabelle an:

Wählen Sie ID, Concat ('000', c1, '$$$'), Länge (c1) aus tchar.
+----+----------------------------+------------+
| ID | concat('000',c1,'$$$') | Länge(c1) |
+----+----------------------------+------------+
| 1 | 000a$$$ | 1 | <- nachstehende Leerzeichen entfernen | 2 | 000 aaaaaaaaaaaaaaaaaaaaa$$$ | 20 |
| 3 | 000a$$$ | 1 |
| 4 | 000$$$ | 0 | <- Entfernen Sie das Leerzeichen am Ende. Das Ergebnis ist dasselbe wie bei id=5 | 5 | 000$$$ | 0 |
+----+----------------------------+------------+

2. Testen Sie den VARCHAR-Typ

Tabellenstruktur:

CREATE TABLE `tvarchar` (
 `id` int(10) unsigned NICHT NULL STANDARD '0',
 `c1` varchar(20) NICHT NULL STANDARD '',
 PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8mb4

Fügen Sie einige Datensätze ein:

in tvarchar-Werte einfügen (1, concat('a', repeat(' ',19)));
in tvarchar-Werte einfügen (2, concat(' ', repeat('a',19)));
in tvarchar-Werte einfügen (3, 'a');
in tvarchar-Werte einfügen (4, '');
in tvarchar-Werte einfügen (5, '');
in tvarchar-Werte einfügen (6, '');

Sehen Sie sich die Speicherstruktur an:

(1) INFIMUM Datensatzoffset:99 Heap-Nr.:0 ...
(2) SUPREMUM-Datensatzoffset:112, Heap-Nr.:1 ...
(3) normaler Datensatz-Offset:126 Heap-Nr.:2 ... <- ID=1
(4) normaler Datensatz-Offset:169 Heap-Nr.:3 ... <- ID=2
(5) normaler Datensatz-Offset:212 Heapno:4 ... <- ID=3
(6) normaler Datensatz-Offset:236 Heapno:5 ... <- ID=4
(7) normaler Datensatz-Offset:260 Heapno:6 ... <- ID=5
(8) normaler Datensatz-Offset:283 Heapno:7 ... <- ID=6

Es ist ersichtlich, dass die Byteanzahl mehrerer Datensätze wie folgt lautet: 43, 43, 24, 24, 23, 23 (der letzte Datensatz ist derselbe wie der Datensatz mit der ID = 5).
Das obige Ergebnis ist etwas überraschend, nicht wahr? Insbesondere der Datensatz mit der ID=1 (eingefügt mit „a… gefolgt von 19 Leerzeichen“), der ebenfalls 43 Bytes verbraucht. Dies beweist die obige Schlussfolgerung 2.
Ebenso belegen die beiden Datensätze mit ID=3 und ID=4 beide 24 Bytes und die beiden Datensätze mit ID=5 und ID=6 beide 23 Bytes (es gibt keine zusätzliche Byteanzahl zum Speichern der Zeichenfolge, nur 4 Bytes für die ID-Spalte).

Schauen wir uns die Ergebnisse des Lesens der tvarchar-Tabelle an:

Wählen Sie ID, Concat ('000', c1, '$$$'), Länge (c1) aus tvarchar.
+----+----------------------------+------------+
| ID | concat('000',c1,'$$$') | Länge(c1) |
+----+----------------------------+------------+
| 1 | 000a $$$ | 20 | <- nachstehende Leerzeichen werden im Leseergebnis nicht entfernt | 2 | 000 aaaaaaaaaaaaaaaaaaaa$$$ | 20 |
| 3 | 000a$$$ | 1 |
| 4 | 000 $$$ | 1 | <- Dieses Leerzeichen wird im Leseergebnis nicht gelöscht | 5 | 000$$$ | 0 |
| 6 | 000$$$ | 0 |
+----+----------------------------+------------+

Generell können zwei Schlussfolgerungen gezogen werden:
1. Aus den Leseergebnissen geht hervor, dass die Spalte vom Typ CHAR beim Speichern Leerzeichen verbraucht, tatsächlich jedoch nur beim Lesen (die Leerzeichen werden auf Anzeigeebene gelöscht).
2. Aus den Leseergebnissen geht hervor, dass die Spalte vom Typ VARCHAR zusätzliche Leerzeichen enthält. Tatsächlich werden diese Leerzeichen nur beim Lesen wiederhergestellt (aber diese Leerzeichen werden beim tatsächlichen physischen Speichern immer noch gelöscht).

Sehen wir uns abschließend an, was in der Dokumentation steht:

Bei der Speicherung von CHAR-Werten werden diese rechtsseitig mit Leerzeichen aufgefüllt,
Kurz gesagt, CHAR-Spalten werden mit Leerzeichen am Ende gespeichert, um die Länge zu verkürzen.

Beim Abrufen von CHAR-Werten werden nachstehende Leerzeichen entfernt, es sei denn, die
Der SQL-Modus PAD_CHAR_TO_FULL_LENGTH ist aktiviert.
Kurz gesagt werden CHAR-Spalten gelesen, ohne dass nachstehende Leerzeichen vorhanden sind, sofern nicht der SQL-Modus-Wert PAD_CHAR_TO_FULL_LENGTH=1 festgelegt ist.

VARCHAR-Werte werden beim Speichern nicht aufgefüllt.
Kurz gesagt, beim Speichern von VARCHAR wird kein nachstehendes Leerzeichen hinzugefügt.

Nachfolgende Leerzeichen bleiben beim Speichern und Abrufen von Werten erhalten, in
Konformität mit Standard-SQL. Kurz gesagt werden Leerzeichen angezeigt, wenn ein VARCHAR gelesen wird.

Die in den obigen Tests verwendeten Versionen und Umgebungen sind:

mysql> Version auswählen()\G
...
version(): 8.0.15

mysql> wähle @@sql_mode\G
...
@@sql_mode: NUR_VOLLSTÄNDIGE_GRUPPE_DURCH, STRENGE_TRANS_TABELLEN, KEINE_NULL_IM_DATUM, KEIN_NULL_DATUM, FEHLER_FÜR_DIVISION_DURCH_NULL, KEINE_ENGINE_SUBSTITUTION

Referenzdokumentation

11.4.1 Die CHAR- und VARCHAR-Typen, https://dev.mysql.com/doc/refman/5.7/en/char.html

Oben finden Sie ausführliche Informationen zum Unterschied zwischen MySQL CHAR- und VARCHAR-Speicher. Weitere Informationen zu MySQL CHAR und VARCHAR finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!