How to limit the number of records in a table in MySQL

There is no simple answer to how to limit the number of records in a table in MySQL. For example, executing a command or simply setting a parameter cannot solve the problem perfectly. Next I will give some optional solutions.

For databases, there are generally two solutions to problems:一種是在應用端;另外一種是在數據庫端.

First, on the database side (假設表硬性限制為1W條記錄):

1. Trigger Solution

The idea of ​​the trigger is very simple. Before inserting a new record each time, check whether the number of records in the table has reached the limited number. If not, continue inserting; if the number is reached, insert a new record first and then delete the oldest record, or vice versa. In order to avoid scanning the entire table every time to check the total number of records in the table, another table is planned to be used as a counter for the current table. Before inserting, you only need to check the counter table. To achieve this requirement, two triggers and a counter table are needed.
t1 is the table whose number of records needs to be limited, and t1_count is the counter table:

mysql:ytt_new>create table t1(id int auto_increment primary key, r1 int);
Query OK, 0 rows affected (0.06 sec)
   
mysql:ytt_new>create table t1_count(cnt smallint unsigned);
Query OK, 0 rows affected (0.04 sec)
   
mysql:ytt_new>insert t1_count set cnt=0;
Query OK, 1 row affected (0.11 sec)

You need to write two triggers, one for the insert action:

DELIMITER $$

USE `ytt_new`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_insert`$$

CREATE
    /*!50017 DEFINER = 'ytt'@'%' */
    TRIGGER `tr_t1_insert` AFTER INSERT ON `t1` 
    FOR EACH ROW BEGIN
       UPDATE t1_count SET cnt= cnt+1;
    END;
$$

DELIMITER ;

The other is the delete action trigger:

DELIMITER $$

USE `ytt_new`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_delete`$$

CREATE
    /*!50017 DEFINER = 'ytt'@'%' */
    TRIGGER `tr_t1_delete` AFTER DELETE ON `t1` 
    FOR EACH ROW BEGIN
       UPDATE t1_count SET cnt= cnt-1;
    END;
$$

DELIMITER ;

Create 10,000 records for table t1, reaching the upper limit:

mysql:ytt_new>insert t1 (r1) with recursive tmp(a,b) as (select 1,1 union all select a+1,ceil(rand()*20) from tmp where a<10000 ) select b from tmp;
Query OK, 10000 rows affected (0.68 sec)
Records: 10000 Duplicates: 0 Warnings: 0

The counter table t1_count records 1W.

mysql:ytt_new>select cnt from t1_count;
+-------+
|cnt|
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

Before inserting, you need to determine whether the counter table has reached the limit. If so, delete the old records first. I wrote a stored procedure to simply sort out the logic:

DELIMITER $$

USE `ytt_new`$$

DROP PROCEDURE IF EXISTS `sp_insert_t1`$$

CREATE DEFINER=`ytt`@`%` PROCEDURE `sp_insert_t1`(
    IN f_r1 INT
    )
BEGIN
      DECLARE v_cnt INT DEFAULT 0;
      SELECT cnt INTO v_cnt FROM t1_count;
      IF v_cnt >=10000 THEN
        DELETE FROM t1 ORDER BY id ASC LIMIT 1;
      END IF;
      INSERT INTO t1(r1) VALUES (f_r1);          
    END$$

DELIMITER ;

At this point, calling the stored procedure can be achieved:

mysql:ytt_new>call sp_insert_t1(9999);
Query OK, 1 row affected (0.02 sec)

mysql:ytt_new>select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)

The processing logic of this stored procedure can also be further optimized into a batch processing. For example, if you cache twice as many table records each time, the judgment logic becomes that before 20,000 records,只插入新記錄，并不刪除老記錄.當到達2W條后，一次性刪除舊的1W條記錄.

This solution has the following drawbacks:

1. The record update of the counter table is triggered by insert/delete. If the table is truncated, the counter table will not be updated, resulting in inconsistent data.
2. If you drop the table, the trigger will also be deleted. You need to rebuild the trigger and reset the counter table.
3. The table can only be written through a single entry such as a stored procedure, and no other entry can be used.

2. Partition table solution

Create a range partition. The first partition has 10,000 records. The second partition is the default partition. When the number of table records reaches the limit, delete the first partition and readjust the partition definition.

Initial definition of partition table:

mysql:ytt_new>create table t1(id int auto_increment primary key, r1 int) partition by range(id) (partition p1 values ​​less than(10001), partition p_max values ​​less than(maxvalue));
Query OK, 0 rows affected (0.45 sec)

Find out if the first partition is full:

mysql:ytt_new>select count(*) from t1 partition(p1);
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)

Delete the first partition and resize the partition table:

mysql:ytt_new>alter table t1 drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql:ytt_new>alter table t1 reorganize partition p_max into (partition p1 values ​​less than (20001), partition p_max values ​​less than (maxvalue));
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0

The advantages of this approach are clear:

1. The table insertion entry can be random, INSERT statement, stored procedure, or import file.
2. Removing the first partition is a DROP operation, which is very fast.
   

But there are also disadvantages: there cannot be gaps in table records. If there are gaps, the partition table definition must be changed. For example, if you change the maximum value of partition p1 to 20001, even if half of the records in this partition are discontinuous, it will not affect the total number of records in the retrieval partition.

3. General tablespace solution

Calculate in advance how much disk space is required for 10,000 records in this table, and then allocate a zone on the disk to store the data of this table.
Mount the partition and add it as an alternative directory for InnoDB tablespace (/tmp/mysql/)。

mysql:ytt_new>create tablespace ts1 add datafile '/tmp/mysql/ts1.ibd' engine innodb;
Query OK, 0 rows affected (0.11 sec)
mysql:ytt_new>alter table t1 tablespace ts1;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

I did a rough calculation, and it's not very accurate, so there may be some errors in the record, but the meaning is very clear: wait until the table reports "TABLE IS FULL".

mysql:ytt_new>insert t1 (r1) values ​​(200);
ERROR 1114 (HY000): The table 't1' is full

mysql:ytt_new>select count(*) from t1;
+----------+
| count(*) |
+----------+
|10384|
+----------+
1 row in set (0.20 sec)

When the table is full, remove the table space, clear the table, and then insert new records .

mysql:ytt_new>alter table t1 tablespace innodb_file_per_table;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql:ytt_new>drop tablespace ts1;
Query OK, 0 rows affected (0.13 sec)

mysql:ytt_new>truncate table t1;
Query OK, 0 rows affected (0.04 sec)

The other is to process on the application side:

You can cache table data on the application side in advance, and then write it to the database side in batches after reaching a limited number of records. Before writing to the database, just clear the table.
For example: the data in table t1 is cached to file t1.csv . When t1.csv reaches 1W rows, the database clears the table data and imports t1.csv .

Conclusion:

In the MyISAM era of MySQL, the table attribute max_rows was used to estimate the number of records in the table, but it was not a hard and fast rule. It was similar to what I wrote above about using a general tablespace to limit the number of records in the table. In InnoDB era, there is no intuitive method, and the problem is solved more by the methods listed above. The specific solution you choose depends on your needs.

This is the end of this article about how to limit the number of records in a table in MySQL. For more information about how to limit the number of records in a table in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!