(MariaDB) Umfassende Erklärung der MySQL-Datentypen und Speichermechanismen

1.1 Übersicht über Datentypen

Der Datentyp ist eine Feldeinschränkung, die begrenzt, welche Art von Daten jedes Feld speichern kann, wie viele Daten es speichern kann und in welchem ​​Format sie gespeichert werden können. MySQL/MariaDB verfügt im Allgemeinen über fünf Arten von Datentypen: Ganzzahlen, Gleitkommazahlen, Zeichenfolgen, Datum und Uhrzeit sowie spezielle ENUM- und SET-Typen.

Die Bedeutung, Einschränkungen und zugehörigen Beschreibungen dieser fünf Datentypen werden in der folgenden Abbildung dargestellt:

Die von jedem Datentyp belegte Byteanzahl finden Sie im offiziellen MariaDB-Handbuch.

1.2 Speichermechanismus und Betriebsart

Der Grund, warum der Datentyp die Datenspeicherlänge eines Felds begrenzen kann, liegt darin, dass der Adressraum beim Erstellen der Tabelle im Speicher streng abgegrenzt wird. Die Länge des Adressraums kann beliebig viele Datenbytes speichern. Natürlich handelt es sich hierbei nur um ein sehr grobes Konzept. Genauere Speichermethoden finden Sie in der Beschreibung weiter unten.

Es gibt zwei Möglichkeiten, den Datentypbereich einzuschränken: Eine besteht darin, den Speicherplatz streng zu begrenzen und nur so viele Daten zu speichern, wie der zugewiesene Speicherplatz zulässt. Daten, die das Limit überschreiten, werden abgeschnitten. Die andere besteht darin, zusätzliche Bytebits zu verwenden, um zu kennzeichnen, ob die Bytes in einem bestimmten Adressraum Daten speichern. Wenn Daten gespeichert sind, werden sie gekennzeichnet, andernfalls werden sie nicht gekennzeichnet.

1.2.1 Integer-Speicher

Hier erklären wir hauptsächlich die Speichermethode für Ganzzahlen. Bei der Speichermethode für Gleitkomma-Datentypen gibt es zu viele Dinge zu beachten.

Bei ganzzahligen Datentypen ist der Speicherplatz streng begrenzt. Der Unterschied zu Zeichen besteht jedoch darin, dass sich aus den 0 und 1 an der Bitposition jedes geteilten Bytes der Wert direkt berechnen lässt und sein Bereich daher anhand der Anzahl der Bitwerte berechnet wird. Ein Byte hat 8 Bits und diese 8 Bits können 2^8=256 Werte bilden. Ebenso haben 2 Bytes insgesamt 2^16=65536 Werte. Ein 4-Byte-Int belegt 32 Bits und kann einen Bereich von 0-2^32 darstellen. Das heißt, Zahlen zwischen 0 und 255 belegen nur ein Byte und Zahlen zwischen 256 und 65535 belegen zwei Bytes.

Beachten Sie, dass der Integer-Datentyp in MySQL/MariaDB den Parameter M verwenden kann, wobei M eine positive Ganzzahl ist, z. B. INT(M), tinyint(M). Das M steht für die Anzeigelänge. Beispielsweise bedeutet int(4), dass bei der Ausgabe eine 4-stellige Ganzzahl angezeigt wird. Wenn die Anzahl der Ziffern des tatsächlichen Werts kleiner als die Breite des Anzeigewerts ist, wird die linke Seite standardmäßig mit Leerzeichen aufgefüllt. Wenn die Anzahl der Ziffern den Grenzwert überschreitet, wird das angezeigte Ergebnis dadurch nicht beeinflusst. Im Allgemeinen wird diese Funktion mit dem Zerofill-Attribut verwendet, um 0 anstelle von Leerzeichen einzufügen. Nach der Verwendung von Zerofill wird die Spalte jedoch automatisch zu einem vorzeichenlosen Feld. Zum Beispiel:

Tabelle erstellen Test3 (ID INT (2) ZEROFILL NICHT NULL);
INSERT INTO test3 VALUES(1),(2),(11),(111);
WÄHLEN Sie die ID aus Test3;
+-----+
|Ich würde|
+-----+
| 01 |
| 02 |
| 11 |
| 111 |
+-----+
4 Zeilen im Satz (0,00 Sek.)

Das einzige, was Sie beachten müssen, ist, dass die Anzeigebreite nur den Anzeigeeffekt beeinflusst und keinen Einfluss auf Vorgänge wie Speicherung, Vergleich, Längenberechnung usw. hat.

1.2.2 Speicherung von Zeichentypen

Dies erklärt hauptsächlich die Speichermethoden und Unterschiede zwischen char und varchar.

Der Char-Typ wird oft als „Stringtyp mit fester Länge“ bezeichnet. Er begrenzt die Länge des Speicherplatzes streng, begrenzt jedoch die Anzahl der Zeichen und nicht die Anzahl der Bytes. In früheren Versionen war jedoch die Anzahl der Bytes begrenzt. Daher speichert char(M) strikt M Zeichen, der nicht benötigte Teil wird mit Leerzeichen aufgefüllt und der über M Zeichen hinausgehende Teil wird direkt abgeschnitten.

Da der Zeichentyp die Fähigkeit besitzt, „mit Leerzeichen aufzufüllen, wenn er zu kurz ist“, wird der nachstehende Leerzeichenteil beim Abrufen von Daten aus dem Adressraum automatisch gelöscht, um die Authentizität der Daten widerzuspiegeln. Dies ist eine Besonderheit von char. Sogar die nachfolgenden Leerzeichen, die wir manuell speichern, werden beim Abrufen als automatisch ausgefüllt betrachtet und somit gelöscht. Das heißt, in der Where-Anweisung haben name='gaoxiaofang ' und name='gaoxiaofang' dasselbe Ergebnis.

Zum Beispiel:

Erstellen Sie die Tabelle test2 (ein char(4)-Zeichensatz utf8mb4);
insert into test2 values('Herzlichen Glückwunsch'),('Herzlichen Glückwunsch zu Ihrer erfolgreichen Beförderung'),('Hallo'),('er ');
wähle concat(a,'x') aus test2;
+-----------------+
| concat(a,'x') |
+-----------------+
| Herzlichen Glückwunsch x |
| Herzlichen Glückwunsch zu deiner x-Geburt |
| hellx |
| Hexe |
+-----------------+
4 Reihen im Set

Aus den obigen Ergebnissen können Sie ersehen, dass char(4) nur 4 Zeichen speichern und die nachstehenden Leerzeichen entfernen kann.

VARCHAR wird häufig als „Zeichenfolgentyp mit variabler Länge“ bezeichnet. Beim Speichern von Daten werden zusätzliche Bytebits verwendet, um zu kennzeichnen, ob ein Byte Daten speichert. Jedes gespeicherte Byte (kein Zeichen) beansprucht ein Bit für die Aufzeichnung, sodass ein zusätzliches Byte insgesamt 256 Bytes und zwei zusätzliche Bytes insgesamt 65536 Bytes markieren können. Allerdings begrenzt MySQL/MariaDB die maximale Speicherkapazität auf 65536 Bytes. Dies bedeutet, dass bei Einzelbyte-Zeichen bis zu 65536 Zeichen gespeichert werden können. Bei Mehrbyte-Zeichen wie UTF8 belegt jedes Zeichen 3 Bytes, sodass bis zu 65536/3 = 21845 UTF8-Zeichen gespeichert werden können.

Daher muss beim Speichern von varchar (M) zusätzlich zur tatsächlichen Datenraumlänge die Bitlänge von 1 oder 2 Bytes berechnet werden. Das heißt, für Einzelbyte-Zeichen beträgt der tatsächlich belegte Speicherplatz M + 1 oder M + 2 Bytes, und für Mehrbyte-Zeichen (z. B. 3 Bytes) beträgt der tatsächlich belegte Speicherplatz M * 3 + 1 oder M * 3 + 2 Bytes.

Da beim Varchar-Speicher zum Aufzeichnen jedes Bytes ein zusätzliches Bit erforderlich ist, werden kürzere Daten nicht automatisch aufgefüllt. Daher werden auch die explizit gespeicherten nachstehenden Leerzeichen gespeichert und auf dem Bit markiert, was bedeutet, dass die nachstehenden Leerzeichen nicht gelöscht werden.

Wie bei char(M) können bei Angabe von varchar(2) nur zwei Byte große Zeichen gespeichert werden. Überschreitet die Anzahl den Grenzwert, werden die Zeichen abgeschnitten.

Bei den Zeichenfolgentypen char, varchar und Text werden beim Vergleichen nachstehende Leerzeichen nicht berücksichtigt. Beim Like-Matching oder beim normalen Matching werden Leerzeichen jedoch berücksichtigt, da die Zeichen beim Abgleich genau sind. Zum Beispiel:

Tabelle erstellen test4(a char(4),b varchar(5));
in test4-Werte einfügen('ab ','ab ');
wähle a='ab ',b='ab ',a=b aus Test4;
+-----------+--------------+-----+
| a='ab ' | b='ab ' | a=b |
+-----------+--------------+-----+
| 1 | 1 | 1 |
+-----------+--------------+-----+
1 Reihe im Set
Wählen Sie ein ähnliches „ab“ aus Test4.
+---------------------------------+
| a wie ‚ab‘ |
+---------------------------------+
| 0 |
+---------------------------------+
1 Reihe im Set

Abschließend ist zu beachten, dass beim Speichern (oder Laden in den Speicher) das Speichern von Werten als numerische Typen platzsparender ist als das Speichern als Zeichentypen oder Datums- und Uhrzeittypen. Da Ganzzahlwerte direkt durch Bitberechnungen gespeichert werden, belegt jede Ganzzahl zwischen 0 und 255 nur ein Byte und jede Ganzzahl zwischen 256 und 65535 zwei Byte. Wenn sie vier Byte belegt, kann sie eine beliebige von Milliarden Ganzzahlen darstellen. Dies spart offensichtlich viel mehr Platz als die Zeichenspeicherung, bei der jedes Zeichen ein Byte belegt. Beispielsweise beansprucht der Wert „100“ drei Bytes, wenn er als Zeichentyp gespeichert wird, aber nur ein Byte, wenn er als numerischer Typ gespeichert wird. Daher behandelt die Datenbank Werte, die nicht in Anführungszeichen eingeschlossen sind, standardmäßig als numerische Werte. Wenn Sie sie explizit als Zeichen- oder Datums- und Uhrzeittypen speichern möchten, sollten Sie Anführungszeichen verwenden, um Mehrdeutigkeiten zu vermeiden.

1.2.3 Speicherung von Datums- und Zeittypen

Datums- und Zeitangaben müssen in Anführungszeichen gespeichert werden, um Mehrdeutigkeiten bei numerischen Daten zu vermeiden. Die Eingabemethode für Datum und Uhrzeit ist sehr locker. Folgende Methoden sind zulässig: Jedes zulässige Trennzeichen, und es wird empfohlen, ein 4-stelliges Jahr zu verwenden.

20110101
01.01.2011 18:40:20 Uhr
01.01.2011 18-40-20
20110101184020

1.2.4 ENUM-Datentyp

Der Datentyp ENUM ist ein Aufzählungstyp. Die Definitionsmethode ist ENUM ('Wert1', 'Wert2', 'Wert3', ...). Beim Einfügen von Daten in ein Feld dieses Typs kann nur einer der Werte oder NULL eingefügt werden. Beim Einfügen anderer Werte oder null (also '') werden diese auf leere Daten gekürzt. Beim Speichern wird die Groß-/Kleinschreibung ignoriert (es wird in Zeichen in ENUM umgewandelt) und nachstehende Leerzeichen werden abgeschnitten.

mysql> Tabelle erstellen test6(id int auto_increment Primärschlüssel,Name char(20),Geschlecht enum('Mail','f'));
mysql> in Test6 einfügen (Name, Geschlecht) Werte ('malongshuai', 'Mail'), ('gaoxiaofang', 'F'), ('wugui', 'x'), ('tuner', null), ('woniu', '');
Abfrage OK, 5 Zeilen betroffen
Datensätze: 5 Duplikate: 0 Warnungen: 2
mysql> Warnungen anzeigen;
+---------+------+------------------------------------------+
| Ebene | Code | Nachricht |
+---------+------+------------------------------------------+
| Warnung | 1265 | Daten für Spalte „Geschlecht“ in Zeile 3 abgeschnitten |
| Warnung | 1265 | Daten für Spalte „Geschlecht“ in Zeile 5 abgeschnitten |
+---------+------+------------------------------------------+
2 Reihen im Set
mysql> wähle * aus test6;
+----+----------+--------+
| ID | Name | Geschlecht |
+----+----------+--------+
| 1 | malongshuai | Mail |
| 2 | gaoxiaofang | f |
| 3 | wugui | |
| 4 | Tuner | NULL |
| 5 | Wonju | |
+----+----------+--------+
5 Reihen im Set

Daten vom Typ ENUM werden über Indexwerte gespeichert. Im Vergleich zum String-Typ sind zum Speichern nur 1 oder 2 Byte erforderlich. Theoretisch wird bei einer Werteanzahl von weniger als 256 nur ein Byte benötigt, bei einer Werteanzahl über 256 aber unter 65536 werden 2 Byte zur Speicherung benötigt. MySQL/MariaDB kann maximal 65536 Werte speichern. Dies ist natürlich eine theoretische Einschränkung. Bei der tatsächlichen Speicherung müssen viele Faktoren berücksichtigt werden. Beispielsweise belegt NULL auch Bits, sodass bei der tatsächlichen Speicherung 250 Werte 2 Bytes erfordern können.

Jeder Wert von ENUM wird durch eine Indexnummer nummeriert. Unabhängig davon, ob das Feld gesucht oder bearbeitet wird, wird der Indexwert für die Bearbeitung verwendet. Der Index von Wert1 = 1, der Index von Wert2 = 2 und so weiter. Beachten Sie aber, dass es zwei spezielle Indexwerte gibt: index=NULL für NULL-Werte und index=0 für leere Daten.

Beispiel: ENUM('a', 'b', 'c'): Wenn Sie '', 'b', 'a', 'c', NULL, 'xxx' nacheinander in das Feld einfügen, werden das erste und das letzte zu leeren Daten gekürzt, ihre Indizes sind 0, der Index des eingefügten NULL ist NULL und die Indexwerte des eingefügten 'b', 'a', 'c' sind jeweils 2,1,3. Die Entsprechung zwischen Indexnummer und Wert lautet also:

Verwenden Sie den ENUM-Index, um Daten abzurufen:

mysql> wähle * aus Test6, wobei Geschlecht = 2 ist;
+----+----------+--------+
| ID | Name | Geschlecht |
+----+----------+--------+
| 2 | gaoxiaofang | f |
+----+----------+--------+
1 Reihe im Set

Es wird insbesondere empfohlen, ENUM nicht zum Speichern numerischer Werte zu verwenden, da alle Vorgänge, egal ob Sortieren, Abrufen oder andere Vorgänge, auf dem Indexwert als Bedingung basieren, was leicht zu Missverständnissen führen kann. Im Folgenden wird beispielsweise ENUM verwendet, um zwei Werte zu speichern und dann Abruf- und Sortiervorgänge durchzuführen.

mysql> Tabelle erstellen test7(id enum('3','1','2'));
mysql> in test7 Werte einfügen('1'),('2'),('3');
# Bei der Suche ist id = 2, aber das Ergebnis ist 1, da 2 von id = 2 der Indexwert von enum ist und der Wert von index = 2 in enum 1 ist
mysql> wähle * aus Test7, wobei ID=2 ist;
+----+
|Ich würde|
+----+
| 1 |
+----+
1 Reihe im Set
# Beim Sortieren nach ID wird auch nach Indexgröße sortiert mysql> select * from test7 order by id asc;
+----+
|Ich würde|
+----+
| 3 |
| 1 |
| 2 |
+----+
3 Reihen im Set

Daher wird dringend empfohlen, Werte nicht in ENUM zu speichern, da selbst Gleitkommawerte leicht mehrdeutig sein können.

1.2.5 SET-Datentyp

Beim SET-Typ wird ähnlich wie bei enum zwischen Groß- und Kleinschreibung unterschieden, nachstehende Leerzeichen werden beim Speichern gelöscht und Null ist ebenfalls ein gültiger Wert. Der Unterschied besteht aber darin, dass mehrere angegebene Werte kombiniert werden können. Beispielsweise kann set('a','b','c','d') 'a, b', 'd, b' usw. speichern. Mehrere Mitglieder werden durch Kommas getrennt. Daher können bei Verwendung mehrerer Mitglieder keine Kommas in den Werten der Mitglieder selbst erscheinen. Wenn der zu speichernde Wert nicht in der festgelegten Liste enthalten ist, wird er auf einen Nullwert gekürzt.

Der vom SET-Datentyp belegte Speicherplatz hängt von der Anzahl der SET-Mitglieder M ab, die als (M+7)/8 aufgerundet berechnet wird. Also: 1-8 Mitglieder belegen 1 Byte;

Die Mitglieder 9-16 belegen 2 Bytes;
Die Mitglieder 17-24 belegen 3 Bytes;
Die Mitglieder 25-32 belegen 4 Bytes;
Die Mitglieder 33-64 belegen 8 Bytes.

MySQL/MariaDB ist auf maximal 64 Mitglieder beschränkt.

Beim Speichern von Daten des SET-Datentyps werden doppelte Mitglieder ignoriert und in der Reihenfolge gespeichert, in der sie aufgezählt werden. Beispiel: Setze ('b', 'b', 'a'). Das Ergebnis der Speicherung von 'a,b,a' und 'b,a,b' ist 'b,a'.

mysql> Tabelle erstellen test8(a set('d','b','a'));
mysql> in Test8-Werte einfügen('b,b,a'),('b,a,b'),('bab');
Abfrage OK, 3 Zeilen betroffen
Datensätze: 3 Duplikate: 0 Warnungen: 1
mysql> wähle * aus test8;
+-----+
| ein |
+-----+
| b,a |
| b,a |
| |
+-----+
3 Reihen im Set

Verwenden Sie find_in_set(set_value,set_column_name), um die Zeile abzurufen, die den angegebenen Set-Wert set_value enthält. So rufen Sie beispielsweise Zeilen ab, in denen Feld a das Element b enthält:

mysql> wähle * aus test8, wobei find_in_set('b',a);
+-----+
| ein |
+-----+
| b,a |
| b,a |
+-----+
2 Reihen im Set

1.3 Datentypattribute: unsigniert

Das unsigned-Attribut macht die numerischen Daten vorzeichenlos. Die Verwendung des vorzeichenlosen Attributs ändert den Bereich des numerischen Datentyps. Beispielsweise liegt der Bereich des vorzeichenbehafteten Typs tinyint zwischen -128 und 127, während der Bereich bei Verwendung des vorzeichenlosen Attributs zwischen 0 und 255 liegt. Gleichzeitig verhindert unsigned auch, dass in die Spalte negative Werte eingefügt werden.

Tabelle erstellen t(a int unsigned,b int unsigned);
in t einfügen, auswählen 1,2;
in t einfügen, auswählen -1,-2;

Wenn in den obigen Anweisungen die zweite Anweisung zum Einfügen einer negativen Zahl ausgeführt wird, wird ein Fehler gemeldet, der darauf hinweist, dass der Bereich außerhalb des gültigen Bereichs liegt.

Die Verwendung von unsigned hat in manchen Fällen durchaus ihren Nutzen. Eine allgemeine ID-Primärschlüsselspalte lässt beispielsweise keine negativen Zahlen zu, was der Implementierung einer Prüfbedingung gleichkommt. Die Verwendung von unsigned kann jedoch manchmal unerwartete Probleme verursachen: Wenn bei numerischen Berechnungen eine negative Zahl erhalten wird, wird ein Fehler gemeldet. Beispielsweise sind in der obigen Tabelle t die Felder a und b beides vorzeichenlose Spalten, und es gibt eine Zeile mit a=1, b=2.

mysql> wähle * aus t;
+---+---+
| ein | b |
+---+---+
| 1 | 2 |
+---+---+
1 Reihe im Set

Wenn zu diesem Zeitpunkt ab berechnet wird, tritt ein Fehler auf. Darüber hinaus führt jede an der Berechnung beteiligte Spalte ohne Vorzeichen zu einer negativen Zahl.

mysql> wähle ab aus t;
1690 – BIGINT UNSIGNED-Wert liegt in ‚(‘test‘.‘t‘.‘a‘ – ‚test‘.‘t‘.‘b‘)‘ außerhalb des gültigen Bereichs.
mysql> wähle a-2 aus t;
1690 – BIGINT UNSIGNED-Wert liegt in „(`test`.`t`.`a` – 2)“ außerhalb des gültigen Bereichs.

Wenn das Ergebnis der Berechnung nicht negativ ist, hat es keine Auswirkung.

mysql> wähle 2-a,a*3 aus t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
| 1 | 3 |
+-----+-----+
1 Reihe im Set

Dies ist kein Fehler in MySQL/MariaDB. Unsigned in der Sprache C weist ähnliche Probleme auf. Dieses Problem kann gelöst werden, indem in MySQL/MariaDB „sql_mode='no_unsigned_subtraction'“ festgelegt wird.

Daher empfehle ich persönlich, das unsignierte Attribut nicht zum Ändern des Felds zu verwenden.

1.4 Datentypattribute: Zerofill

Nachdem Zerofill das Feld geändert hat, wird der Anzeigeteil des unzureichenden Felds mit 0 statt mit Leerzeichen gefüllt. Wenn Zerofill aktiviert ist, wird es automatisch auf unsigned gesetzt. Zerofill wird grundsätzlich erst nach dem Festlegen der Anzeigebreite der Spalte verwendet. Die Anzeigebreite der Spalten wurde oben vorgestellt.

mysql> Tabelle erstellen t1 (id int (4) zerofill);
mysql> wähle * aus t1;
+----------+
|Ich würde|
+----------+
| 0001 |
| 0002 |
| 0011 |
| 83838 |
+----------+
4 Zeilen im Satz (0,00 Sek.)

Zerofill ändert lediglich das Anzeigeergebnis und hat keinen Einfluss auf den gespeicherten Datenwert.

Die oben stehende umfassende Erklärung zum MySQL-Datentyp und Speichermechanismus (MariaDB) ist der gesamte Inhalt, den der Herausgeber mit Ihnen teilt. Ich hoffe, dass er Ihnen als Referenz dienen kann. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.