Die Tücken der automatischen Inkrementierung numerischer MySQL-Typen

Beim Entwurf von Tabellenstrukturen gehören numerische Typen zu den am häufigsten verwendeten Typen. Allerdings ist die sinnvolle Verwendung numerischer Typen nicht so einfach wie gedacht, zum Beispiel:

• Wie entwirft man einen automatisch inkrementierenden Primärschlüssel für ein massives, gleichzeitig ablaufendes Internetgeschäft? Reicht es aus, INT zu verwenden?
• Wie gestaltet man den Kontostand? Ist die Verwendung des Typs DECIMAL narrensicher?

Alle der oben genannten Punkte sind falsch!

Obwohl der numerische Typ einfach erscheinen mag, kann das oben erwähnte Problem des „unvollständigen Designdenkens“ beim Entwurf der Tabellenstrukturarchitektur leicht auftreten (insbesondere bei massiven gleichzeitigen Internetszenarien).

Nummerntypen

Ganzzahltypen

Die MySQL-Datenbank unterstützt die vom SQL-Standard unterstützten Integer-Typen: INT und SMALLINT. Darüber hinaus unterstützt die MySQL-Datenbank auch Integer-Typen wie TINYINT, MEDIUMINT und BIGINT (Tabelle 1 zeigt den Speicherplatz und den Wertebereich verschiedener Integer-Typen):

Im Integer-Typ gibt es vorzeichenbehaftete und vorzeichenlose Attribute, die den Wertebereich des Integer-Typs darstellen. Der Standardwert ist vorzeichenbehaftet. Beim Entwerfen empfehle ich nicht, absichtlich vorzeichenlose Attribute zu verwenden, da bei manchen Datenanalysen die von SQL zurückgegebenen Ergebnisse möglicherweise nicht Ihren Wünschen entsprechen.

Schauen wir uns ein Beispiel einer „Verkaufstabelle“ an, deren Tabellenstruktur und Daten wie folgt sind. Es ist wichtig zu beachten, dass die Spalte sale_count das unsignierte Attribut verwendet (d. h. die Spalte ist für die Speicherung von Werten größer oder gleich 0 ausgelegt):

mysql> ANZEIGEN ERSTELLEN TABELLE sale_G

*************************** 1. Reihe ***************************

       Tabelle: Verkauf

Tabelle erstellen: CREATE TABLE `sale` (

  `sale_date` Datum NICHT NULL,

  `sale_count` int unsigned DEFAULT NULL,

  PRIMÄRSCHLÜSSEL (`sale_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 Zeile im Satz (0,00 Sek.)


mysql> AUSWÄHLEN * FROM Verkauf;

+------------+------------+

| Verkaufsdatum | Verkaufsanzahl |

+------------+------------+

| 01.01.2020 | 10000 |

| 01.02.2020 | 8000 |

| 01.03.2020 | 12000 |

| 01.04.2020 | 9000 |

| 01.05.2020 | 10000 |

| 01.06.2020 | 18000 |

+------------+------------+

6 Zeilen im Satz (0,00 Sek.)

Dabei stellt sale_date das Verkaufsdatum und sale_count die Verkaufsmenge pro Monat dar. Nun besteht die Anforderung, dass der Chef die Veränderungen des monatlichen Verkaufsvolumens zählen möchte, um Geschäftsentscheidungen zu treffen. Diese SQL-Anweisung erfordert einen Non-Equijoin, ist aber nicht allzu schwer zu schreiben:

WÄHLEN 

    s1.sale_date, s2.sale_count - s1.sale_count AS diff

AUS

    Verkauf s1

        LINKS VERBINDEN

    Verkauf s2 ON DATE_ADD(s2.Verkaufsdatum, INTERVALL 1 MONAT) = s1.Verkaufsdatum

BESTELLEN NACH Verkaufsdatum;

Da die Spalte sale_count jedoch während der Ausführung das unsignierte Attribut verwendet, wird das folgende Ergebnis ausgegeben:

FEHLER 1690 (22003): BIGINT UNSIGNED-Wert liegt außerhalb des Bereichs in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)'

Wie Sie sehen, weist MySQL den Benutzer darauf hin, dass das berechnete Ergebnis außerhalb des Bereichs liegt. Tatsächlich erfordert MySQL, dass vorzeichenlose Werte nach der Subtraktion vorzeichenlos bleiben, da sonst ein Fehler gemeldet wird.

Um diesen Fehler zu vermeiden, müssen Sie den Datenbankparameter sql_mode auf NO_UNSIGNED_SUBTRACTION setzen, um das Vorzeichen des Subtraktionsergebnisses zuzulassen und so das gewünschte Endergebnis zu erhalten:

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';

Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

WÄHLEN


    s1.Verkaufsdatum,

    IFNULL(s2.Verkaufszahl - s1.Verkaufszahl,'') AS diff

AUS

    Verkauf s1

    LINKS BEITRETEN Verkauf s2

    ON DATE_ADD(s2.Verkaufsdatum, INTERVALL 1 MONAT) = s1.Verkaufsdatum

BESTELLEN NACH Verkaufsdatum;


+------------+-------+

| Verkaufsdatum | Unterschied |

+------------+-------+

| 01.01.2020 | |

| 01.02.2020 | 2000 |

| 01.03.2020 | -4000 |

| 01.04.2020 | 3000 |

| 01.05.2020 | -1000 |

| 01.06.2020 | -8000 |

+------------+-------+

6 Zeilen im Satz (0,00 Sek.)

Gleitkommatypen und hochpräzise Typen

Zu den häufig verwendeten digitalen Typen zählen neben Ganzzahltypen auch Gleitkommatypen und hochpräzise Typen.
Frühere Versionen von MySQL verfügten über die Gleitkommatypen Float und Double, diese Typen sind jedoch nicht hochpräzise und keine SQL-Standardtypen. Daher werden sie für die Verwendung in realen Produktionsumgebungen nicht empfohlen. Andernfalls sind während der Berechnungen die endgültigen Berechnungsergebnisse aufgrund von Problemen mit dem Präzisionstyp falsch.
Noch wichtiger ist, dass ab MySQL 8.0.17 beim Erstellen einer Tabelle mit dem Float- oder Double-Typ die folgende Warnung ausgegeben wird: MySQL erinnert Benutzer daran, dass sie die oben genannten Gleitkommatypen nicht verwenden sollten, und erinnert sogar daran, dass Gleitkommatypen in späteren Versionen veraltet sein werden

Die Angabe der Ziffernanzahl für Gleitkomma-Datentypen ist veraltet und wird in einer zukünftigen Version entfernt.

Unter den numerischen Typen kann der hochpräzise DECIMAL-Typ verwendet werden. Beim Deklarieren einer Spalte dieses Typs können (und müssen) Sie die Genauigkeit und die Skalierung angeben, zum Beispiel:

Gehalt DECIMAL(8,2)

Dabei ist 8 die Genauigkeit (mit Genauigkeit ist die Anzahl der im Wert gespeicherten führenden Ziffern gemeint) und 2 der Maßstab (mit Maßstab ist die Anzahl der nach dem Dezimalpunkt gespeicherten Ziffern gemeint). Normalerweise kann beim Entwurf von Tabellenstrukturen der Typ DECIMAL verwendet werden, um Benutzergehälter, Kontostände und andere Dienste mit einer Genauigkeit von zwei Dezimalstellen darzustellen.

Bei der Verwendung in Internetgeschäften mit vielen gleichzeitigen Vorgängen wird der Typ DECIMAL für das Betragsfeld jedoch nicht empfohlen. Stattdessen wird der Integer-Typ INT empfohlen (die Gründe werden weiter unten analysiert).

Praktischer Entwurf einer Geschäftstabellenstruktur

Integer-Typ und Auto-Increment-Design

In realen Geschäftsszenarien werden Ganzzahltypen am häufigsten verwendet, um die Menge eines Artikels im Geschäftsleben darzustellen. Beispielsweise die Verkaufsmenge in der obigen Tabelle oder die Lagermenge und Kaufhäufigkeit im E-Commerce. In der Wirtschaft werden Integer-Typen außerdem häufig und wichtig als Primärschlüssel einer Tabelle verwendet, d. h. um eine Datenzeile eindeutig zu identifizieren.
Integer in Kombination mit dem Attribut auto_increment kann die Selbstinkrementierungsfunktion realisieren. Wenn Sie jedoch Auto-Increment als Primärschlüssel im Tabellenstrukturdesign verwenden, sollten Sie den folgenden beiden Punkten besondere Aufmerksamkeit schenken. Wenn Sie nicht aufpassen, kann dies einen katastrophalen Schlag für das Geschäft bedeuten:

• Verwenden Sie BIGINT als Primärschlüssel anstelle von INT;
• Der Auto-Inkrement-Wert ist nicht persistent und kann zurückverfolgt werden (vor MySQL 8.0).

Aus Tabelle 1 können wir ersehen, dass der maximale Bereich von INT bei 4,2 Milliarden liegt. Bei der Anwendung in realen Internet-Geschäftsszenarien ist es leicht, den Maximalwert zu erreichen. Beispielsweise haben einige Flusstabellen und Protokolltabellen ein tägliches Datenvolumen von 10 Millionen. Nach 420 Tagen wird die Obergrenze des INT-Typs erreicht.
Verwenden Sie daher bei Verwendung einer automatisch inkrementierten Ganzzahl als Primärschlüssel immer BIGINT statt INT. Verwenden Sie INT nicht, um 4 Bytes zu sparen. Wenn die Obergrenze erreicht ist, ist das Ändern der Tabellenstruktur eine enorme Belastung und ein großer Aufwand.
Dies führt zu einer interessanten Frage: Wie ist die Leistung der Datenbank, wenn die Obergrenze des INT-Typs erreicht wird? Wird es wieder 1? Wir können dies mit der folgenden SQL-Anweisung überprüfen:

mysql> CREATE TABLE t (

    -> ein INT AUTO_INCREMENT PRIMARY KEY

    -> );


mysql> IN t-WERTE EINFÜGEN (2147483647);

Abfrage OK, 1 Zeile betroffen (0,01 Sek.)


mysql> INSERT INTO t-WERTE (NULL);

FEHLER 1062 (23000): Doppelter Eintrag „2147483647“ für Schlüssel „t.PRIMARY“

Es ist ersichtlich, dass beim Erreichen der INT-Obergrenze bei der erneuten Durchführung der automatischen Inkrementeinfügung ein Duplikatsfehler gemeldet wird und die MySQL-Datenbank ihn nicht automatisch auf 1 zurücksetzt.
Das zweite Problem, das besondere Aufmerksamkeit erfordert, besteht darin, dass der Auto-Inkrement-Wert vor MySQL 8.0 nicht dauerhaft ist und es bei dem Auto-Inkrement-Wert möglicherweise zu Backtracking-Problemen kommt!

mysql> AUSWÄHLEN * VON t;

+---+

| ein |

+---+

| 1 |

| 2 |

| 3 |

+---+

3 Zeilen im Satz (0,01 Sek.)


mysql> LÖSCHEN AUS t, WO a = 3;

Abfrage OK, 1 Zeile betroffen (0,02 Sek.)


mysql> ANZEIGEN CREATE TABLE t\G

*************************** 1. Reihe ***************************

       Tabelle: t

Tabelle erstellen: CREATE TABLE `t` (

  `a` int NICHT NULL AUTO_INCREMENT,

  PRIMÄRSCHLÜSSEL (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 Zeile im Set (0,00 Sek.

Es ist ersichtlich, dass nach dem Löschen des Datensatzes mit dem Auto-Inkrement-Wert 3 der nächste Auto-Inkrement-Wert immer noch 4 ist (AUTO_INCREMENT = 4). Hier liegt kein Fehler vor und das Auto-Inkrement wird nicht zurückverfolgt. Wenn die Datenbank jedoch zu diesem Zeitpunkt neu gestartet wird, wird der Startwert der automatischen Inkrementierung der Tabelle t nach dem Start der Datenbank wieder 3, dh der Wert der automatischen Inkrementierung wird zurückverfolgt. Die Einzelheiten lauten wie folgt:

mysql> ANZEIGEN CREATE TABLE t\G

*************************** 1. Reihe ***************************

       Tabelle: t

Tabelle erstellen: CREATE TABLE `t` (

  `a` int NICHT NULL AUTO_INCREMENT,

  PRIMÄRSCHLÜSSEL (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 Zeile im Satz (0,00 s

Um dieses Problem vollständig zu lösen, gibt es zwei Methoden:

• Aktualisieren Sie die MySQL-Version auf Version 8.0. Der automatisch inkrementierte Wert jeder Tabelle bleibt dauerhaft erhalten.
• Wenn Sie die Datenbankversion nicht aktualisieren können, wird dringend davon abgeraten, den Auto-Increment-Datentyp als Primärschlüssel in der zentralen Geschäftstabelle zu verwenden.

Tatsächlich wird beim Entwurf einer massiven Internetarchitektur im Hinblick auf eine bessere Skalierbarkeit der verteilten Architektur in der Zukunft davon abgeraten, den Integer-Typ als Primärschlüssel zu verwenden. Der String-Typ wird eher empfohlen.

Gestaltung des Fondsfeldes

Da es sich bei den Feldern im Geschäftsdesign von Benutzerguthaben, Fondskontoständen, digitalen Geldbörsen, Wechselgeld usw. ausschließlich um Kapitalfelder handelt, verwenden Programmierer für die Feldauswahl normalerweise den Typ DECIMAL, da dieser auf die Minute genau sein kann, z. B.: DECIMAL(8,2).

CREATE TABLE Benutzer (

  Benutzer-ID BIGINT AUTO_INCREMENT,

  Geld DECIMAL(8,2) NICHT NULL,

  ......

)

In den Designstandards für Massen-Internetdienste wird der Typ DECIMAL nicht empfohlen. Stattdessen wird empfohlen, DECIMAL in einen Integer-Typ zu konvertieren. Mit anderen Worten: Es wird empfohlen, Gelder eher in Cent als in Yuan aufzubewahren. Beispielsweise wird 1 Yuan als Ganzzahltyp 100 in der Datenbank gespeichert.

Wenn der Wertebereich des Betragsfeldes mit DECIMAL ausgedrückt wird, wie definieren Sie die Länge? Da es sich beim Typ DECIMAL um ein Feld mit variabler Länge handelt, reicht es nicht aus, ein Betragsfeld als DECIMAL(8,2) zu definieren. Dies kann nur bedeuten, dass der maximale Speicherwert 999999,99 beträgt, also Millionen von Geldbeträgen gespeichert werden können.

Der Betrag des Benutzers muss in einem Bereich von mindestens mehreren zehn Milliarden gespeichert sein, während der BIP-Betrag des Statistikamts mehrere zehn Billionen erreichen kann. Es ist schwierig, die Definition mit dem Typ DECIMAL zu vereinheitlichen.
Ein weiterer wichtiger Punkt ist, dass der DECIMAL-Typ eine binäre Kodierungsmethode ist und seine Berechnungseffizienz weitaus weniger effizient ist als die von Integer-Typen. Daher wird empfohlen, BIG INT zum Speichern von betragsbezogenen Feldern zu verwenden.

Felder werden in einem separaten Speicher gespeichert, aber trotzdem kann BIG INT Beträge im Gigabyte-Bereich speichern. Hier gilt: 1 Billion = 1 Billion.

Der Vorteil hiervon besteht darin, dass alle Betragsfelder Felder mit fester Länge sind, also 8 Bytes belegen und eine hohe Speichereffizienz aufweisen. Ein weiterer Punkt ist, dass die direkte Ganzzahlberechnung effizienter ist.
Beachten Sie, dass wir beim Datenbankdesign großen Wert auf die Speicherung mit fester Länge legen, da die Speicherung mit fester Länge eine bessere Leistung bietet.

Schauen wir uns an, wie Datensätze in der Datenbank gespeichert werden. Es läuft ungefähr wie folgt ab:

Wenn eine Aktualisierung erfolgt, kann der ursprüngliche Speicherplatz von Datensatz 1 nach der Aktualisierung nicht mehr den Speicherplatz von Datensatz 1 aufnehmen. Daher markiert die Datenbank Datensatz 1 als gelöscht und sucht neuen Speicherplatz für Datensatz 1, beispielsweise:

In der obigen Abbildung gibt *Datensatz 1 den ursprünglich von Datensatz 1 belegten Speicherplatz an. Dieser Speicherplatz wird zu fragmentiertem Speicherplatz und kann nicht mehr verwendet werden, sofern der Tabellenspeicherplatz nicht manuell defragmentiert wird.

Wie stellen Sie also die Daten mit Dezimalstellen dar, wenn Sie BIG INT zum Speichern eines Betragsfelds verwenden? Tatsächlich kann dieser Teil vollständig vom Front-End gehandhabt und angezeigt werden. Was die Datenbank selbst betrifft, muss sie nur nach Punkten gespeichert werden.

Dies ist das Ende dieses Artikels über die Fallstricke der automatischen Inkrementierung von MySQL-Zahlentypen. Weitere relevante Inhalte zur automatischen Inkrementierung von MySQL-Zahlentypen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!