快捷導(dǎo)航

MySQL 如何限制一張表的記錄數(shù)

更新時(shí)間：2021年09月13日 08:48:45 作者：楊濤濤

能否控制單表在一個(gè)固定的記錄數(shù)，比如說1W條，超過不讓插入新記錄或者說直接拋出錯(cuò)誤？關(guān)于這個(gè)問題，沒有一個(gè)簡(jiǎn)化的答案，比如執(zhí)行一條命令或者說簡(jiǎn)單設(shè)置一個(gè)參數(shù)都不能完美解決。接下來(lái)便介紹MySQL 如何限制一張表的記錄數(shù)來(lái)給出一些可選解決方案

一、觸發(fā)器解決方案

觸發(fā)器的思路很簡(jiǎn)單，每次插入新記錄前，檢查表記錄數(shù)是否到達(dá)限定數(shù)量，數(shù)量未到，繼續(xù)插入；數(shù)量達(dá)到，先插入一條新記錄，再刪除最老的記錄，或者反著來(lái)也行。為了避免每次檢測(cè)表總記錄數(shù)全表掃，規(guī)劃另外一張表，用來(lái)做當(dāng)前表的計(jì)數(shù)器，插入前，只需查計(jì)數(shù)器表即可。要實(shí)現(xiàn)這個(gè)需求，需要兩個(gè)觸發(fā)器和一張計(jì)數(shù)器表。
t1為需要限制記錄數(shù)的表，t1_count 為計(jì)數(shù)器表：

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)

得寫兩個(gè)觸發(fā)器，一個(gè)是插入動(dòng)作觸發(fā)：

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 ;

另外一個(gè)是刪除動(dòng)作觸發(fā)：

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ù)，達(dá)到上限：

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

計(jì)數(shù)器表 t1_count 記錄為1W。

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

插入前需要判斷計(jì)數(shù)器表是否到達(dá)限制，如果到了這個(gè)限制則刪除老舊記錄先。我寫一個(gè)存儲(chǔ)過程簡(jiǎn)單理下邏輯：

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 ;

此時(shí)，調(diào)用存儲(chǔ)過程即可實(shí)現(xiàn)：

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)

這個(gè)存儲(chǔ)過程的處理邏輯也可以繼續(xù)優(yōu)化為一次批量處理。比如每次多緩存一倍的表記錄數(shù)，判斷邏輯變?yōu)樵?W條以前，只插入新記錄，并不刪除老記錄，當(dāng)?shù)竭_(dá)2W條后，一次性刪除舊的1W條記錄。

這種方案有以下幾個(gè)缺陷：

計(jì)數(shù)器表的記錄更新是由insert/delete觸發(fā)，如果對(duì)表進(jìn)行truncate則計(jì)數(shù)器表不觸發(fā)更新從而數(shù)據(jù)不一致。
對(duì)表進(jìn)行drop 操作則觸發(fā)器也跟著刪除，需要重建觸發(fā)器，重置計(jì)數(shù)器表。
對(duì)表寫入只能是類似存儲(chǔ)過程這樣的單一入口，不能是其他入口。

二、分區(qū)表解決方案

建立一個(gè) range 分區(qū)，第一個(gè)分區(qū)有1W條記錄，第二個(gè)分區(qū)為默認(rèn)分區(qū)，等表記錄數(shù)達(dá)到限制后，刪除第一個(gè)分區(qū)，重新調(diào)整分區(qū)定義即可。

分區(qū)表初始定義：

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)

查找第一個(gè)分區(qū)是否已滿：

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

刪除第一個(gè)分區(qū)，并且重新調(diào)整分區(qū)表：

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

這種方法的優(yōu)勢(shì)很明顯：

表插入入口可以很隨機(jī)，INSERT語(yǔ)句、存儲(chǔ)過程、導(dǎo)文件都行。
刪除第一個(gè)分區(qū)是一個(gè)DROP操作，非常快。

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

三、通用表空間解決方案

提前計(jì)算好這張表1W條記錄需要多少磁盤空間，之后在磁盤上劃分一個(gè)區(qū)專門來(lái)存放這張表的數(shù)據(jù)。
掛載劃好的分區(qū)，添加為 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

我大致算了下，不是很準(zhǔn)確，所以記錄上可能有點(diǎn)誤差，不過意思已經(jīng)很明確：等表報(bào) “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)

另外一個(gè)就是在應(yīng)用端處理：

可以提前在應(yīng)用端緩存表數(shù)據(jù)，達(dá)到限定的記錄數(shù)后再批量寫入數(shù)據(jù)庫(kù)端，寫入數(shù)據(jù)庫(kù)前，先清空表即可。
舉個(gè)例子: 表t1數(shù)據(jù)緩存到文件t1.csv，當(dāng)t1.csv到達(dá)1W行時(shí)，數(shù)據(jù)庫(kù)端清空表數(shù)據(jù)，導(dǎo)入t1.csv。

結(jié)語(yǔ)：

之前 MySQL 在 MyISAM 時(shí)代，表屬性 max_rows 來(lái)預(yù)估表的記錄數(shù)，但也不是硬性規(guī)定，類似我上面寫的使用通用表空間來(lái)達(dá)到限制表記錄數(shù)的作用；到了 InnoDB 時(shí)代就沒有一個(gè)直觀的方法，更多是靠以上列出來(lái)的方法來(lái)解決這個(gè)問題，具體選哪個(gè)方案，還是得看需求。

到此這篇關(guān)于MySQL 如何限制一張表的記錄數(shù)的文章就介紹到這了,更多相關(guān)MySQL 限制一張表的記錄數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: