目录
关于mysql 如何限制一张表的记录数，这没有一个简化的答案，比如执行一条命令或者说简单设置一个参数都不能完美解决。接下来我给出一些可选解决方案。
对数据库来讲，一般问题的解决方案无非有两种，一种是在应用端；另外一种是在数据库端。
首先是在数据库端（假设表硬性限制为1w条记录）：
一、触发器解决方案
触发器的思路很简单，每次插入新记录前，检查表记录数是否到达限定数量，数量未到，继续插入；数量达到，先插入一条新记录，再删除最老的记录，或者反着来也行。为了避免每次检测表总记录数全表扫，规划另外一张表，用来做当前表的计数器，插入前，只需查计数器表即可。要实现这个需求，需要两个触发器和一张计数器表。
t1为需要限制记录数的表，t1_count 为计数器表：
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)
得写两个触发器，一个是插入动作触发：
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 ;
另外一个是删除动作触发：
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条数据，达到上限：
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
计数器表 t1_count 记录为1w。
mysql:ytt_new>select cnt from t1_count; +-------+ | cnt | +-------+ | 10000 | +-------+ 1 row in set (0.00 sec)
插入前需要判断计数器表是否到达限制，如果到了这个限制则删除老旧记录先。我写一个存储过程简单理下逻辑：
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 ;
此时，调用存储过程即可实现：
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)
这个存储过程的处理逻辑也可以继续优化为一次批量处理。 比如每次多缓存一倍的表记录数，判断逻辑变为在2w条以前，只插入新记录，并不删除老记录，当到达2w条后，一次性删除旧的1w条记录。
这种方案有以下几个缺陷：
- 计数器表的记录更新是由insert/delete触发，如果对表进行truncate则计数器表不触发更新从而数据不一致。
- 对表进行drop 操作则触发器也跟着删除，需要重建触发器，重置计数器表。
- 对表写入只能是类似存储过程这样的单一入口，不能是其他入口。
二、分区表解决方案
建立一个 range 分区，第一个分区有1w条记录，第二个分区为默认分区，等表记录数达到限制后，删除第一个分区，重新调整分区定义即可。
分区表初始定义：
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)
查找第一个分区是否已满：
mysql:ytt_new>select count(*) from t1 partition(p1); +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec)
删除第一个分区，并且重新调整分区表：
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
这种方法的优势很明显：
- 表插入入口可以很随机，insert语句、存储过程、导文件都行。
- 删除第一个分区是一个drop操作，非常快。
但也有缺点：表记录不能有空隙，如果有空隙，就得改变分区表定义。比如把分区p1的最大值改为20001，那即使在这个分区里有一半的记录不连续，也不影响检索分区里的总记录数。
三、通用表空间解决方案
提前计算好这张表1w条记录需要多少磁盘空间，之后在磁盘上划分一个区专门来存放这张表的数据。
挂载划好的分区，添加为 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
我大致算了下，不是很准确，所以记录上可能有点误差，不过意思已经很明确：等表报 “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)
另外一个就是在应用端处理：
可以提前在应用端缓存表数据，达到限定的记录数后再批量写入数据库端，写入数据库前，先清空表即可。
举个例子: 表t1数据缓存到文件t1.csv，当t1.csv到达1w行时，数据库端清空表数据，导入t1.csv。
结语：
之前 mysql 在 myisam 时代，表属性 max_rows 来预估表的记录数，但也不是硬性规定，类似我上面写的使用通用表空间来达到限制表记录数的作用；到了 innodb 时代就没有一个直观的方法，更多是靠以上列出来的方法来解决这个问题，具体选哪个方案，还是得看需求。
到此这篇关于mysql 如何限制一张表的记录数的文章就介绍到这了,更多相关mysql 限制一张表的记录数内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！