關于MySQL 如何限制一張表的記錄數，這沒有一個簡化的答案，比如執行一條命令或者說簡單設置一個參數都不能完美解決。接下來我給出一些可選解決方案。
對數據庫來講，一般問題的解決方案無非有兩種，一種是在應用端；另外一種是在數據庫端。
首先是在數據庫端（假設表硬性限制為1W條記錄）：
一、觸發器解決方案
觸發器的思路很簡單，每次插入新記錄前，檢查表記錄數是否到達限定數量，數量未到，繼續插入；數量達到，先插入一條新記錄，再刪除最老的記錄，或者反著來也行。為了避免每次檢測表總記錄數全表掃，規劃另外一張表，用來做當前表的計數器，插入前，只需查計數器表即可。要實現這個需求，需要兩個觸發器和一張計數器表。
t1為需要限制記錄數的表，t1_count 為計數器表：
|
1
2
3
4
5
6
7
8
|
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) |
得寫兩個觸發器，一個是插入動作觸發：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 ; |
另外一個是刪除動作觸發：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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條數據，達到上限：
|
1
2
3
|
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。
|
1
2
3
4
5
6
7
|
mysql:ytt_new>select cnt from t1_count;+-------+| cnt |+-------+| 10000 |+-------+1 row in set (0.00 sec) |
插入前需要判斷計數器表是否到達限制，如果到了這個限制則刪除老舊記錄先。我寫一個存儲過程簡單理下邏輯：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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 ; |
此時，調用存儲過程即可實現：
|
1
2
3
4
5
6
7
8
9
10
|
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條記錄。
這種方案有以下幾個缺陷：
- 計數器表的記錄更新是由insert/delete觸發，如果對表進行truncate則計數器表不觸發更新從而數據不一致。
- 對表進行drop 操作則觸發器也跟著刪除，需要重建觸發器，重置計數器表。
- 對表寫入只能是類似存儲過程這樣的單一入口，不能是其他入口。
二、分區表解決方案
建立一個 range 分區，第一個分區有1W條記錄，第二個分區為默認分區，等表記錄數達到限制后，刪除第一個分區，重新調整分區定義即可。
分區表初始定義：
|
1
2
|
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) |
查找第一個分區是否已滿：
|
1
2
3
4
5
6
7
|
mysql:ytt_new>select count(*) from t1 partition(p1);+----------+| count(*) |+----------+| 10000 |+----------+1 row in set (0.00 sec) |
刪除第一個分區，并且重新調整分區表：
|
1
2
3
4
5
6
7
|
mysql:ytt_new>alter table t1 drop partition p1;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 |
這種方法的優勢很明顯：
- 表插入入口可以很隨機，INSERT語句、存儲過程、導文件都行。
- 刪除第一個分區是一個DROP操作，非常快。
但也有缺點：表記錄不能有空隙，如果有空隙，就得改變分區表定義。比如把分區p1的最大值改為20001，那即使在這個分區里有一半的記錄不連續，也不影響檢索分區里的總記錄數。
三、通用表空間解決方案
提前計算好這張表1W條記錄需要多少磁盤空間，之后在磁盤上劃分一個區專門來存放這張表的數據。
掛載劃好的分區，添加為 InnoDB 表空間的備選目錄(/tmp/mysql/)。
|
1
2
3
4
5
|
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” 后即可。
|
1
2
3
4
5
6
7
8
9
10
|
mysql:ytt_new>insert t1 (r1) values (200);ERROR 1114 (HY000): The table 't1' is fullmysql:ytt_new>select count(*) from t1;+----------+| count(*) |+----------+| 10384 |+----------+1 row in set (0.20 sec) |
表滿后移除表空間，清空表，再插入新記錄。
|
1
2
3
4
5
6
7
8
9
|
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;Query OK, 0 rows affected (0.04 sec) |
另外一個就是在應用端處理：
可以提前在應用端緩存表數據，達到限定的記錄數后再批量寫入數據庫端，寫入數據庫前，先清空表即可。
舉個例子: 表t1數據緩存到文件t1.csv，當t1.csv到達1W行時，數據庫端清空表數據，導入t1.csv。
結語：
之前 MySQL 在 MyISAM 時代，表屬性 max_rows 來預估表的記錄數，但也不是硬性規定，類似我上面寫的使用通用表空間來達到限制表記錄數的作用；到了 InnoDB 時代就沒有一個直觀的方法，更多是靠以上列出來的方法來解決這個問題，具體選哪個方案，還是得看需求。
到此這篇關于MySQL 如何限制一張表的記錄數的文章就介紹到這了,更多相關MySQL 限制一張表的記錄數內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://segmentfault.com/a/1190000040637561
