您好,登錄后才能下訂單哦!
怎樣使用MySQL 限制一張表的記錄數(shù),相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策,為此本文總結(jié)了問題出現(xiàn)的原因和解決方法,通過這篇文章希望你能解決這個(gè)問題。
對(duì)數(shù)據(jù)庫來講,一般問題的解決方案無非有兩種,一種是在應(yīng)用端
;另外一種是在數(shù)據(jù)庫端
。
首先是在數(shù)據(jù)庫端(假設(shè)表硬性限制為1W條記錄
):
觸發(fā)器的思路很簡單,每次插入新記錄前,檢查表記錄數(shù)是否到達(dá)限定數(shù)量,數(shù)量未到,繼續(xù)插入;數(shù)量達(dá)到,先插入一條新記錄,再刪除最老的記錄,或者反著來也行。為了避免每次檢測(cè)表總記錄數(shù)全表掃,規(guī)劃另外一張表,用來做當(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ǔ)過程簡單理下邏輯:
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ǔ)過程這樣的單一入口,不能是其他入口。
建立一個(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語句、存儲(chǔ)過程、導(dǎo)文件都行。
刪除第一個(gè)分區(qū)是一個(gè)DROP操作,非常快。
但也有缺點(diǎn):表記錄不能有空隙,如果有空隙,就得改變分區(qū)表定義。比如把分區(qū)p1的最大值改為20001,那即使在這個(gè)分區(qū)里有一半的記錄不連續(xù),也不影響檢索分區(qū)里的總記錄數(shù)。
提前計(jì)算好這張表1W條記錄需要多少磁盤空間,之后在磁盤上劃分一個(gè)區(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)確,所以記錄上可能有點(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ù)庫端,寫入數(shù)據(jù)庫前,先清空表即可。
舉個(gè)例子: 表t1
數(shù)據(jù)緩存到文件t1.csv
,當(dāng)t1.csv
到達(dá)1W
行時(shí),數(shù)據(jù)庫端清空表數(shù)據(jù),導(dǎo)入t1.csv
。
看完上述內(nèi)容,你們掌握怎樣使用MySQL 限制一張表的記錄數(shù)的方法了嗎?如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容,歡迎關(guān)注億速云行業(yè)資訊頻道,感謝各位的閱讀!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。