Shulou(Shulou.com)06/03 Report--

How to use MySQL to limit the number of records in a table, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

As far as the database is concerned, there are no more than two solutions to general problems, one is on the application side, and the other is on the database side.

First, on the database side (assuming that the table is rigidly limited to 1W records):

I. trigger solution

The idea of the trigger is very simple: before each insertion of a new record, check whether the number of table records has reached the limit, and continue to insert; if the number reaches, insert a new record first, then delete the oldest record, or vice versa. In order to avoid scanning the total number of records of each test table, plan another table, which is used as the counter of the current table. Just check the counter table before insertion. To achieve this requirement, you need two triggers and a counter table.

T1 is the table that needs to limit the number of records, 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 have to write two triggers, one is to insert the action to trigger:

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 triggered by the delete action:

DELIMITER $$USE `tr_t1_ 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 1W pieces of data for table T1 to reach the upper limit:

Mysql:ytt_new > insert T1 (R1) with recursive tmp (insert b) as (select 1Magne1 rand () * 20) from tmp where aselect cnt from T1 accountabilityLay + | cnt | +-+ | 10000 | +-+ 1 row in set (0.00 sec)

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

DELIMITER $$USE `ytt_ new` $$DROP PROCEDURE IF EXISTS `sp_insert_ t1` $$CREATE DEFINER= `ytt` @ `% `PROCEDURE `ytt` (IN f_r1 INT) BEGIN DECLARE v_cnt INT DEFAULT 0; SELECT cnt INTO v_cnt FROM T1 counters; 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, the stored procedure can be called:

Mysql:ytt_new > call sp_insert_t1 (9999); Query OK, 1 row affected (0.02 sec) mysql:ytt_new > select count (*) from t1bomblism + | count (*) | +-+ | 10000 | +-+ 1 row in set (0.01 sec)

The processing logic of this stored procedure can also continue to be optimized for one batch processing. For example, if more than twice the number of table records is cached each time, the judgment logic is changed to insert only new records before 2W entries, and do not delete old records. When 2W records are reached, the old 1W records are deleted at one time.

This scheme has the following drawbacks:

The record update of the counter table is triggered by insert/delete. If the table is truncate, the counter table does not trigger the update and the data is inconsistent.

If you drop the table, the trigger will be deleted, and you need to rebuild the trigger and reset the counter table.

Writing to a table can only be a single entry such as a stored procedure, not another entry.

2. Partition table solution

Create a range partition, the first partition has 1W records, and the second partition is the default partition. When the number of table entries reaches the limit, delete the first partition and readjust the partition definition.

Initial definition of the 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 (sec)

Delete the first partition and readjust the partition table:

Mysql:ytt_new > alter table T1 drop partition p1bot query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0mysql: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 obvious:

Table insertion entries can be random, such as INSERT statements, stored procedures, and import files.

Deleting the first partition is a DROP operation, which is very fast.

But there are also drawbacks: table records cannot have gaps, and if there are gaps, you have to change the definition of the partitioned table. For example, if you change the maximum value of partition p1 to 20001, even if half of the records in this partition are not contiguous, it does not affect the total number of records in the retrieval partition.

III. General tablespace solution

Calculate in advance how much disk space is required for the 1W records of this table, and then divide an area on the disk to store the data of the table.

Mount the partition and add it as an alternate directory for the 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 made a rough calculation, but it is not very accurate, so there may be some errors in the record, but the meaning is very clear: wait until the form is "TABLE IS FULL".

Mysql:ytt_new > insert T1 (R1) values; ERROR 1114 (HY000): The table 't1' is fullmysql:ytt_new > select count (*) from T1 + | count (*) | +-+ | 10384 | +-+ 1 row in set (0.20 sec)

Remove the tablespace when the table is full, empty the table, and then insert a new record.

Mysql:ytt_new > alter table T1 tablespace innodb_file_per_table;Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0mysql:ytt_new > drop tablespace ts1;Query OK, 0 rows affected (0.13 sec) mysql:ytt_new > truncate table T1 politics query OK, 0 rows affected (0.04 sec)

The other is to handle it on the application side:

You can cache the table data on the application side in advance, and then write it in batch to the database side after reaching the limited number of records. Before writing to the database, you can empty the table first.

For example: table T1 data is cached in the file t1.csv, and when t1.csv reaches 1W rows, the database side clears the table data and imports t1.csv.

After reading the above, do you know how to use MySQL to limit the number of records in a table? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.