怎樣使用MySQL 限制一張表的記錄數

怎樣使用MySQL 限制一張表的記錄數，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

對數據庫來講，一般問題的解決方案無非有兩種，一種是在應用端；另外一種是在數據庫端。

首先是在數據庫端（假設表硬性限制為1W條記錄）：

一、觸發器解決方案

觸發器的思路很簡單，每次插入新記錄前，檢查表記錄數是否到達限定數量，數量未到，繼續插入；數量達到，先插入一條新記錄，再刪除最老的記錄，或者反著來也行。為了避免每次檢測表總記錄數全表掃，規劃另外一張表，用來做當前表的計數器，插入前，只需查計數器表即可。要實現這個需求，需要兩個觸發器和一張計數器表。
t1為需要限制記錄數的表，t1_count 為計數器表：

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)

得寫兩個觸發器，一個是插入動作觸發：

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 ;

另外一個是刪除動作觸發：

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 ;

給表t1造1W條數據，達到上限：

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

計數器表 t1_count 記錄為1W。

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

插入前需要判斷計數器表是否到達限制，如果到了這個限制則刪除老舊記錄先。我寫一個存儲過程簡單理下邏輯：

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 ;

此時，調用存儲過程即可實現：

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)

這個存儲過程的處理邏輯也可以繼續優化為一次批量處理。 比如每次多緩存一倍的表記錄數，判斷邏輯變為在2W條以前，只插入新記錄，并不刪除老記錄，當到達2W條后，一次性刪除舊的1W條記錄。

這種方案有以下幾個缺陷：

1. 計數器表的記錄更新是由insert/delete觸發，如果對表進行truncate則計數器表不觸發更新從而數據不一致。

2. 對表進行drop 操作則觸發器也跟著刪除，需要重建觸發器，重置計數器表。

3. 對表寫入只能是類似存儲過程這樣的單一入口，不能是其他入口。

二、分區表解決方案

建立一個 range 分區，第一個分區有1W條記錄，第二個分區為默認分區，等表記錄數達到限制后，刪除第一個分區，重新調整分區定義即可。

分區表初始定義：

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)

查找第一個分區是否已滿：

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

刪除第一個分區，并且重新調整分區表：

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

這種方法的優勢很明顯：

1. 表插入入口可以很隨機，INSERT語句、存儲過程、導文件都行。

2. 刪除第一個分區是一個DROP操作，非常快。
   

但也有缺點：表記錄不能有空隙，如果有空隙，就得改變分區表定義。比如把分區p1的最大值改為20001，那即使在這個分區里有一半的記錄不連續，也不影響檢索分區里的總記錄數。

三、通用表空間解決方案

提前計算好這張表1W條記錄需要多少磁盤空間，之后在磁盤上劃分一個區專門來存放這張表的數據。
掛載劃好的分區，添加為 InnoDB 表空間的備選目錄(/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

我大致算了下，不是很準確，所以記錄上可能有點誤差，不過意思已經很明確：等表報 “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)

表滿后移除表空間，清空表，再插入新記錄。

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)

另外一個就是在應用端處理：

可以提前在應用端緩存表數據，達到限定的記錄數后再批量寫入數據庫端，寫入數據庫前，先清空表即可。
舉個例子: 表t1數據緩存到文件t1.csv，當t1.csv到達1W行時，數據庫端清空表數據，導入t1.csv。

看完上述內容，你們掌握怎樣使用MySQL 限制一張表的記錄數的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！