關于MySQL 如何限制一張表的記錄數(shù)，這沒有一個簡化的答案，比如執(zhí)行一條命令或者說簡單設置一個參數(shù)都不能完美解決。接下來我給出一些可選解決方案。
對數(shù)據(jù)庫來講，一般問題的解決方案無非有兩種，一種是在應用端；另外一種是在數(shù)據(jù)庫端。
首先是在數(shù)據(jù)庫端（假設表硬性限制為1W條記錄）：
一、觸發(fā)器解決方案
觸發(fā)器的思路很簡單，每次插入新記錄前，檢查表記錄數(shù)是否到達限定數(shù)量，數(shù)量未到，繼續(xù)插入；數(shù)量達到，先插入一條新記錄，再刪除最老的記錄，或者反著來也行。為了避免每次檢測表總記錄數(shù)全表掃，規(guī)劃另外一張表，用來做當前表的計數(shù)器，插入前，只需查計數(shù)器表即可。要實現(xiàn)這個需求，需要兩個觸發(fā)器和一張計數(shù)器表。
t1為需要限制記錄數(shù)的表，t1_count 為計數(shù)器表：
|
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) |
得寫兩個觸發(fā)器，一個是插入動作觸發(fā)：
|
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 ; |
另外一個是刪除動作觸發(fā)：
|
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條數(shù)據(jù)，達到上限：
|
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 |
計數(shù)器表 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) |
插入前需要判斷計數(shù)器表是否到達限制，如果到了這個限制則刪除老舊記錄先。我寫一個存儲過程簡單理下邏輯：
|
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 ; |
此時，調(diào)用存儲過程即可實現(xiàn)：
|
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) |
這個存儲過程的處理邏輯也可以繼續(xù)優(yōu)化為一次批量處理。 比如每次多緩存一倍的表記錄數(shù)，判斷邏輯變?yōu)樵?W條以前，只插入新記錄，并不刪除老記錄，當?shù)竭_2W條后，一次性刪除舊的1W條記錄。
這種方案有以下幾個缺陷：
- 計數(shù)器表的記錄更新是由insert/delete觸發(fā)，如果對表進行truncate則計數(shù)器表不觸發(fā)更新從而數(shù)據(jù)不一致。
- 對表進行drop 操作則觸發(fā)器也跟著刪除，需要重建觸發(fā)器，重置計數(shù)器表。
- 對表寫入只能是類似存儲過程這樣的單一入口，不能是其他入口。
二、分區(qū)表解決方案
建立一個 range 分區(qū)，第一個分區(qū)有1W條記錄，第二個分區(qū)為默認分區(qū)，等表記錄數(shù)達到限制后，刪除第一個分區(qū)，重新調(diào)整分區(qū)定義即可。
分區(qū)表初始定義：
|
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) |
查找第一個分區(qū)是否已滿：
|
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) |
刪除第一個分區(qū)，并且重新調(diào)整分區(qū)表：
|
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 |
這種方法的優(yōu)勢很明顯：
- 表插入入口可以很隨機，INSERT語句、存儲過程、導文件都行。
- 刪除第一個分區(qū)是一個DROP操作，非常快。
但也有缺點：表記錄不能有空隙，如果有空隙，就得改變分區(qū)表定義。比如把分區(qū)p1的最大值改為20001，那即使在這個分區(qū)里有一半的記錄不連續(xù)，也不影響檢索分區(qū)里的總記錄數(shù)。
三、通用表空間解決方案
提前計算好這張表1W條記錄需要多少磁盤空間，之后在磁盤上劃分一個區(qū)專門來存放這張表的數(shù)據(jù)。
掛載劃好的分區(qū)，添加為 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 |
我大致算了下，不是很準確，所以記錄上可能有點誤差，不過意思已經(jīng)很明確：等表報 “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) |
另外一個就是在應用端處理：
可以提前在應用端緩存表數(shù)據(jù)，達到限定的記錄數(shù)后再批量寫入數(shù)據(jù)庫端，寫入數(shù)據(jù)庫前，先清空表即可。
舉個例子: 表t1數(shù)據(jù)緩存到文件t1.csv，當t1.csv到達1W行時，數(shù)據(jù)庫端清空表數(shù)據(jù)，導入t1.csv。
結(jié)語：
之前 MySQL 在 MyISAM 時代，表屬性 max_rows 來預估表的記錄數(shù)，但也不是硬性規(guī)定，類似我上面寫的使用通用表空間來達到限制表記錄數(shù)的作用；到了 InnoDB 時代就沒有一個直觀的方法，更多是靠以上列出來的方法來解決這個問題，具體選哪個方案，還是得看需求。
到此這篇關于MySQL 如何限制一張表的記錄數(shù)的文章就介紹到這了,更多相關MySQL 限制一張表的記錄數(shù)內(nèi)容請搜索服務器之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://segmentfault.com/a/1190000040637561
