MySQL 如何限制一張表的記錄數(shù)
關(guān)于MySQL 如何限制一張表的記錄數(shù)，這沒有一個簡化的答案，比如執(zhí)行一條命令或者說簡單設(shè)置一個參數(shù)都不能完美解決。接下來我給出一些可選解決方案。
對數(shù)據(jù)庫來講，一般問題的解決方案無非有兩種，一種是在應(yīng)用端；另外一種是在數(shù)據(jù)庫端。
首先是在數(shù)據(jù)庫端（假設(shè)表硬性限制為1W條記錄）：
一、觸發(fā)器解決方案
觸發(fā)器的思路很簡單，每次插入新記錄前，檢查表記錄數(shù)是否到達(dá)限定數(shù)量，數(shù)量未到，繼續(xù)插入；數(shù)量達(dá)到，先插入一條新記錄，再刪除最老的記錄，或者反著來也行。為了避免每次檢測表總記錄數(shù)全表掃，規(guī)劃另外一張表，用來做當(dāng)前表的計數(shù)器，插入前，只需查計數(shù)器表即可。要實現(xiàn)這個需求，需要兩個觸發(fā)器和一張計數(shù)器表。
t1為需要限制記錄數(shù)的表，t1_count 為計數(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)
得寫兩個觸發(fā)器，一個是插入動作觸發(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 ;
另外一個是刪除動作觸發(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
計數(shù)器表 t1_count 記錄為1W。
mysql:ytt_new>select cnt from t1_count; +-------+ | cnt | +-------+ | 10000 | +-------+ 1 row in set (0.00 sec)
插入前需要判斷計數(shù)器表是否到達(dá)限制，如果到了這個限制則刪除老舊記錄先。我寫一個存儲過程簡單理下邏輯：
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)：
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條以前，只插入新記錄，并不刪除老記錄，當(dāng)?shù)竭_(dá)2W條后，一次性刪除舊的1W條記錄。
這種方案有以下幾個缺陷：
- 計數(shù)器表的記錄更新是由insert/delete觸發(fā)，如果對表進行truncate則計數(shù)器表不觸發(fā)更新從而數(shù)據(jù)不一致。
- 對表進行drop 操作則觸發(fā)器也跟著刪除，需要重建觸發(fā)器，重置計數(shù)器表。
- 對表寫入只能是類似存儲過程這樣的單一入口，不能是其他入口。
二、分區(qū)表解決方案
建立一個 range 分區(qū)，第一個分區(qū)有1W條記錄，第二個分區(qū)為默認(rèn)分區(qū)，等表記錄數(shù)達(dá)到限制后，刪除第一個分區(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)
查找第一個分區(qū)是否已滿：
mysql:ytt_new>select count(*) from t1 partition(p1); +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec)
刪除第一個分區(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)勢很明顯：
- 表插入入口可以很隨機，INSERT語句、存儲過程、導(dǎo)文件都行。
- 刪除第一個分區(qū)是一個DROP操作，非常快。
但也有缺點：表記錄不能有空隙，如果有空隙，就得改變分區(qū)表定義。比如把分區(qū)p1的最大值改為20001，那即使在這個分區(qū)里有一半的記錄不連續(xù)，也不影響檢索分區(qū)里的總記錄數(shù)。
三、通用表空間解決方案
提前計算好這張表1W條記錄需要多少磁盤空間，之后在磁盤上劃分一個區(qū)專門來存放這張表的數(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)確，所以記錄上可能有點誤差，不過意思已經(jīng)很明確：等表報 “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)
另外一個就是在應(yīng)用端處理：
可以提前在應(yīng)用端緩存表數(shù)據(jù)，達(dá)到限定的記錄數(shù)后再批量寫入數(shù)據(jù)庫端，寫入數(shù)據(jù)庫前，先清空表即可。
舉個例子: 表t1數(shù)據(jù)緩存到文件t1.csv，當(dāng)t1.csv到達(dá)1W行時，數(shù)據(jù)庫端清空表數(shù)據(jù)，導(dǎo)入t1.csv。
結(jié)語：
之前 MySQL 在 MyISAM 時代，表屬性 max_rows 來預(yù)估表的記錄數(shù)，但也不是硬性規(guī)定，類似我上面寫的使用通用表空間來達(dá)到限制表記錄數(shù)的作用；到了 InnoDB 時代就沒有一個直觀的方法，更多是靠以上列出來的方法來解決這個問題，具體選哪個方案，還是得看需求。
到此這篇關(guān)于MySQL 如何限制一張表的記錄數(shù)的文章就介紹到這了,更多相關(guān)MySQL 限制一張表的記錄數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家分享了mysql 5.7.14安裝配置方法圖文教程,詳細(xì)介紹了mysql 5.7.14安裝可能遇到的一系列問題，感興趣的朋友可以參考一下2016-08-08
這篇文章主要介紹了查看修改MySQL表結(jié)構(gòu)命令，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
這篇文章主要介紹了mysql中的replace函數(shù)替換字符串問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
linux配置mysql數(shù)據(jù)庫遠(yuǎn)程連接失敗的解決方法
這篇文章主要為大家詳細(xì)介紹了linux配置mysql數(shù)據(jù)庫遠(yuǎn)程連接失敗的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-06-06
MYSQL的select 學(xué)習(xí)筆記...2007-02-02
在使用mysql時，有時需要查詢出某個字段不重復(fù)的記錄，雖然mysql提供 有distinct這個關(guān)鍵字來過濾掉多余的重復(fù)記錄只保留一條，但往往只用它來返回不重復(fù)記錄的條數(shù)，而不是用它來返回不重記錄的所有值。2017-12-12
這篇文章主要給大家介紹了關(guān)于MySQL中int最大值的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
這篇文章主要介紹了mysql中如何判斷是否支持分區(qū)的相關(guān)資料,需要的朋友可以參考下2015-10-10
教你如何在?MySQL?數(shù)據(jù)庫中支持完整的Unicode