So begrenzen Sie die Anzahl der Datensätze in einer Tabelle in MySQL

Es gibt keine einfache Antwort darauf, wie man die Anzahl der Datensätze in einer Tabelle in MySQL begrenzen kann. Beispielsweise kann das Ausführen eines Befehls oder das einfache Festlegen eines Parameters das Problem nicht perfekt lösen. Als nächstes werde ich einige optionale Lösungen anbieten.

Bei Datenbanken gibt es im Allgemeinen zwei Problemlösungen:一種是在應用端,另外一種是在數據庫端.

Zunächst auf der Datenbankseite (假設表硬性限制為1W條記錄):

1. Lösung auslösen

Die Idee des Triggers ist ganz einfach. Vor jedem Einfügen eines neuen Datensatzes wird geprüft, ob die Anzahl der Datensätze in der Tabelle die begrenzte Anzahl erreicht hat. Wenn nicht, wird mit dem Einfügen fortgefahren; wenn die Anzahl erreicht ist, wird zuerst ein neuer Datensatz eingefügt und dann der älteste Datensatz gelöscht, oder umgekehrt. Um zu vermeiden, dass jedes Mal die gesamte Tabelle gescannt werden muss, um die Gesamtzahl der Datensätze in der Tabelle zu überprüfen, ist eine andere Tabelle vorgesehen, die als Zähler für die aktuelle Tabelle verwendet wird. Vor dem Einfügen müssen Sie nur die Zählertabelle überprüfen. Um diese Anforderung zu erfüllen, werden zwei Trigger und eine Zählertabelle benötigt.
t1 ist die Tabelle, deren Datensatzanzahl begrenzt werden muss, und t1_count ist die Zählertabelle:

mysql:ytt_new>Tabelle erstellen t1(id int auto_increment Primärschlüssel, r1 int);
Abfrage OK, 0 Zeilen betroffen (0,06 Sek.)
   
mysql:ytt_new>Tabelle erstellen t1_count(cnt smallint unsigned);
Abfrage OK, 0 Zeilen betroffen (0,04 Sek.)
   
mysql:ytt_new>insert t1_count set cnt=0;
Abfrage OK, 1 Zeile betroffen (0,11 Sek.)

Sie müssen zwei Trigger schreiben, einen für die Einfügeaktion:

TRENNUNGSZEICHEN $$

VERWENDEN Sie `ytt_new`$$

TRIGGER DROP /*!50032 WENN EXISTIERT */ `tr_t1_insert`$$

ERSTELLEN
    /*!50017 DEFINER = "ytt"@"%" */
    TRIGGER `tr_t1_insert` NACH INSERT AUF `t1` 
    FÜR JEDE REIHE BEGINNEN
       AKTUALISIEREN t1_count SETZEN cnt= cnt+1;
    ENDE;
$$

TRENNUNGSZEICHEN ;

Der andere ist der Löschaktionsauslöser:

TRENNUNGSZEICHEN $$

VERWENDEN Sie `ytt_new`$$

TRIGGER DROP /*!50032 WENN EXISTIERT */ `tr_t1_delete`$$

ERSTELLEN
    /*!50017 DEFINER = "ytt"@"%" */
    TRIGGER `tr_t1_delete` NACH DEM LÖSCHEN BEI `t1` 
    FÜR JEDE REIHE BEGINNEN
       AKTUALISIEREN t1_count SETZEN cnt= cnt-1;
    ENDE;
$$

TRENNUNGSZEICHEN ;

Erstellen Sie 10.000 Datensätze für Tabelle t1 und erreichen Sie die Obergrenze:

mysql:ytt_new>füge t1 (r1) mit rekursivem tmp(a,b) ein als (select 1,1 union all select a+1,ceil(rand()*20) from tmp where a<10000 ) select b from tmp;
Abfrage OK, 10000 Zeilen betroffen (0,68 Sek.)
Datensätze: 10000 Duplikate: 0 Warnungen: 0

Die Zählertabelle t1_count zeichnet 1W auf.

mysql:ytt_new>wähle cnt aus t1_count aus;
+----------+
|Anzahl|
+----------+
| 10000 |
+----------+
1 Zeile im Satz (0,00 Sek.)

Vor dem Einfügen müssen Sie feststellen, ob die Zählertabelle das Limit erreicht hat. Wenn ja, löschen Sie zuerst die alten Datensätze. Ich habe eine gespeicherte Prozedur geschrieben, um einfach die Logik zu klären:

TRENNUNGSZEICHEN $$

VERWENDEN Sie `ytt_new`$$

VERFAHREN LÖSCHEN, WENN `sp_insert_t1` VORHANDEN IST$$

CREATE DEFINER=`ytt`@`%` PROCEDURE `sp_insert_t1`(
    IN f_r1 INT
    )
BEGINNEN
      DECLARE v_cnt INT DEFAULT 0;
      Wählen Sie cnt in v_cnt aus t1_count;
      WENN v_cnt >=10000 DANN
        LÖSCHEN AUS t1, ORDER BY ID ASC LIMIT 1;
      ENDE, WENN;
      EINFÜGEN IN t1(r1) WERTE (f_r1);          
    ENDE$$

TRENNUNGSZEICHEN ;

An diesem Punkt kann die gespeicherte Prozedur aufgerufen werden:

mysql:ytt_new>call sp_insert_t1(9999);
Abfrage OK, 1 Zeile betroffen (0,02 Sek.)

mysql:ytt_new>Wählen Sie count(*) aus t1 aus;
+----------+
| Anzahl(*) |
+----------+
| 10000 |
+----------+
1 Zeile im Satz (0,01 Sek.)

Die Verarbeitungslogik dieser gespeicherten Prozedur kann auch zu einer Stapelverarbeitung weiter optimiert werden. Wenn Sie beispielsweise jedes Mal doppelt so viele Tabellendatensätze zwischenspeichern, lautet die Beurteilungslogik: Vor 20.000 Datensätzen只插入新記錄，并不刪除老記錄.當到達2W條后，一次性刪除舊的1W條記錄.

Diese Lösung hat folgende Nachteile:

1. Die Datensatzaktualisierung der Zählertabelle wird durch Einfügen/Löschen ausgelöst. Wenn die Tabelle abgeschnitten ist, wird die Zählertabelle nicht aktualisiert, was zu inkonsistenten Daten führt.
2. Wenn Sie die Tabelle löschen, wird auch der Trigger gelöscht. Sie müssen den Trigger neu erstellen und die Zählertabelle zurücksetzen.
3. In die Tabelle kann nur über einen einzigen Eintrag, beispielsweise eine gespeicherte Prozedur, geschrieben werden, und es kann kein anderer Eintrag verwendet werden.

2. Partitionstabellenlösung

Erstellen Sie eine range . Die erste Partition hat 10.000 Datensätze. Die zweite Partition ist die Standardpartition. Wenn die Anzahl der Tabellendatensätze das Limit erreicht, löschen Sie die erste Partition und passen Sie die Partitionsdefinition neu an.

Erste Definition der Partitionstabelle:

mysql:ytt_new>Tabelle t1 erstellen (id int auto_increment Primärschlüssel, r1 int) Partition nach Bereich (id) (Partition p1 Werte kleiner als (10001), Partition p_max Werte kleiner als (Maximalwert));
Abfrage OK, 0 Zeilen betroffen (0,45 Sek.)

Finden Sie heraus, ob die erste Partition voll ist:

mysql:ytt_new>Wählen Sie count(*) aus t1-Partition(p1);
+----------+
| Anzahl(*) |
+----------+
| 10000 |
+----------+
1 Zeile im Satz (0,00 Sek.)

Löschen Sie die erste Partition und ändern Sie die Größe der Partitionstabelle:

mysql:ytt_new>Tabelle t1 ändern, Partition p1 löschen;
Abfrage OK, 0 Zeilen betroffen (0,06 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql:ytt_new>alter table t1 reorganisiere Partition p_max in (Partition p1-Werte kleiner als (20001), Partition p_max-Werte kleiner als (maxvalue));
Abfrage OK, 0 Zeilen betroffen (0,60 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

Die Vorteile dieses Ansatzes liegen auf der Hand:

1. Der Tabelleneinfügungseintrag kann beliebig, eine INSERT-Anweisung, eine gespeicherte Prozedur oder eine Importdatei sein.
2. Das Entfernen der ersten Partition ist ein DROP-Vorgang, der sehr schnell ist.
   

Es gibt aber auch Nachteile: Es dürfen keine Lücken in den Tabellendatensätzen vorhanden sein. Wenn es Lücken gibt, muss die Definition der Partitionstabelle geändert werden. Wenn Sie beispielsweise den Maximalwert der Partition p1 auf 20001 ändern, hat dies keine Auswirkungen auf die Gesamtzahl der Datensätze in der Abrufpartition, selbst wenn die Hälfte der Datensätze in dieser Partition diskontinuierlich ist.

3. Allgemeine Tablespace-Lösung

Berechnen Sie im Voraus, wie viel Speicherplatz für 10.000 Datensätze in dieser Tabelle erforderlich ist, und weisen Sie dann eine Zone auf der Festplatte zu, um die Daten dieser Tabelle zu speichern.
Mounten Sie die Partition und fügen Sie sie als alternatives Verzeichnis für InnoDB Tablespace (/tmp/mysql/)。

mysql:ytt_new>Tabellenbereich ts1 erstellen, Datendatei '/tmp/mysql/ts1.ibd' hinzufügen, Engine Innodb;
Abfrage OK, 0 Zeilen betroffen (0,11 Sek.)
mysql:ytt_new>Tabelle t1 ändern, Tabellenbereich ts1;
Abfrage OK, 0 Zeilen betroffen (0,12 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

Ich habe eine grobe Berechnung durchgeführt, und sie ist nicht sehr genau, sodass der Datensatz möglicherweise einige Fehler enthält, aber die Bedeutung ist sehr klar: Warten Sie, bis die Tabelle „TABELLE IST VOLL“ meldet.

mysql:ytt_new>t1(r1)-Werte einfügen (200);
FEHLER 1114 (HY000): Die Tabelle 't1' ist voll

mysql:ytt_new>Wählen Sie count(*) aus t1 aus;
+----------+
| Anzahl(*) |
+----------+
|10384|
+----------+
1 Zeile im Satz (0,20 Sek.)

Wenn die Tabelle voll ist, entfernen Sie den Tabellenbereich, löschen Sie die Tabelle und fügen Sie dann neue Datensätze ein .

mysql:ytt_new>Tabelle ändern t1 Tabellenbereich innodb_file_per_table;
Abfrage OK, 0 Zeilen betroffen (0,18 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql:ytt_new>Tabellenbereich ts1 löschen;
Abfrage OK, 0 Zeilen betroffen (0,13 Sek.)

mysql:ytt_new>Tabelle t1 abschneiden;
Abfrage OK, 0 Zeilen betroffen (0,04 Sek.)

Die andere besteht in der Verarbeitung auf der Anwendungsseite:

Sie können Tabellendaten vorab auf der Anwendungsseite zwischenspeichern und sie dann nach Erreichen einer begrenzten Anzahl von Datensätzen stapelweise auf die Datenbankseite schreiben. Bevor Sie in die Datenbank schreiben, löschen Sie einfach die Tabelle.
Beispiel: Die Daten in Tabelle t1 werden in der Datei t1.csv zwischengespeichert. Wenn t1.csv 1W Zeilen erreicht, löscht die Datenbank die Tabellendaten und importiert t1.csv .

Abschluss:

In der MyISAM-Ära von MySQL wurde das Tabellenattribut max_rows verwendet, um die Anzahl der Datensätze in der Tabelle zu schätzen, aber es war keine feste Regel. Es war ähnlich dem, was ich oben über die Verwendung eines allgemeinen Tabellenbereichs zur Begrenzung der Anzahl der Datensätze in der Tabelle geschrieben habe. In InnoDB -Ära gibt es keine intuitive Methode, und das Problem wird eher durch die oben aufgeführten Methoden gelöst. Die spezifische Lösung, die Sie wählen, hängt von Ihren Anforderungen ab.

Dies ist das Ende dieses Artikels zum Begrenzen der Anzahl von Datensätzen in einer Tabelle in MySQL. Weitere Informationen zum Begrenzen der Anzahl von Datensätzen in einer Tabelle in MySQL 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!