欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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ù)都不能完美解決。接下來便介紹MySQL 如何限制一張表的記錄數(shù)來給出一些可選解決方案

關(guān)于MySQL 如何限制一張表的記錄數(shù),這沒有一個(gè)簡(jiǎn)化的答案,比如執(zhí)行一條命令或者說簡(jiǎn)單設(shè)置一個(gè)參數(shù)都不能完美解決。接下來我給出一些可選解決方案。

對(duì)數(shù)據(jù)庫(kù)來講,一般問題的解決方案無非有兩種,一種是在應(yīng)用端;另外一種是在數(shù)據(jù)庫(kù)端。

首先是在數(shù)據(jù)庫(kù)端(假設(shè)表硬性限制為1W條記錄):

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

觸發(fā)器的思路很簡(jiǎn)單,每次插入新記錄前,檢查表記錄數(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ǔ)過程簡(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è)缺陷:

  1. 計(jì)數(shù)器表的記錄更新是由insert/delete觸發(fā),如果對(duì)表進(jìn)行truncate則計(jì)數(shù)器表不觸發(fā)更新從而數(shù)據(jù)不一致。
  2. 對(duì)表進(jìn)行drop 操作則觸發(fā)器也跟著刪除,需要重建觸發(fā)器,重置計(jì)數(shù)器表。
  3. 對(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ì)很明顯:

  1. 表插入入口可以很隨機(jī),INSERT語句、存儲(chǔ)過程、導(dǎo)文件都行。
  2. 刪除第一個(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ù)庫(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é)語:

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

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

相關(guān)文章

  • 教你如何在?MySQL?數(shù)據(jù)庫(kù)中支持完整的Unicode

    教你如何在?MySQL?數(shù)據(jù)庫(kù)中支持完整的Unicode

    UTF-8?是一種可變寬度編碼,它使用一到四個(gè)?8?位字節(jié)對(duì)每個(gè)符號(hào)進(jìn)行編碼,永遠(yuǎn)不要在MySQL中使用?utf8——總是使用?utf8mb4,對(duì)mysql支持?Unicode相關(guān)知識(shí)感興趣的朋友一起看看吧
    2023-01-01
  • 最新評(píng)論