技术分享MySQL如何限制一张表的记录数

　　作者：杨涛涛
　　资深数据库专家，专研MySQL十余年。擅长MySQL、PostgreSQL、MongoDB等开源数据库相关的备份恢复、SQL调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供MySQL相关技术支持、MySQL相关课程培训等工作。
　　本文来源：原创投稿
　　爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
　　背景
　　本文又是来源于客户咨询的问题：能否控制单表在一个固定的记录数，比如说1W条，超过不让插入新记录或者说直接抛出错误？
　　关于这个问题，没有一个简化的答案，比如执行一条命令或者说简单设置一个参数都不能完美解决。接下来我给出一些可选解决方案。
　　正文
　　对数据库来讲，一般问题的解决方案无非有两种，一种是在应用端；另外一种是在数据库端。
　　首先是在数据库端（假设表硬性限制为1W条记录）：
　　一、触发器解决方案：
　　触发器的思路很简单，每次插入新记录前，检查表记录数是否到达限定数量，数量未到，继续插入；数量达到，先插入一条新记录，再删除最老的记录，或者反着来也行。为了避免每次检测表总记录数全表扫，规划另外一张表，用来做当前表的计数器，插入前，只需查计数器表即可。要实现这个需求，需要两个触发器和一张计数器表。
　　t1为需要限制记录数的表，t1count为计数器表：mysql：yttnewcreatetablet1（idintautoincrementprimarykey，r1int）；
　　QueryOK，0rowsaffected（0。06sec）
　　mysql：yttnewcreatetablet1count（cntsmallintunsigned）；
　　QueryOK，0rowsaffected（0。04sec）
　　mysql：yttnewinsertt1countsetcnt0；
　　QueryOK，1rowaffected（0。11sec）
　　得写两个触发器，一个是插入动作触发：DELIMITER
　　USEyttnew
　　DROPTRIGGER！50032IFEXISTStrt1insert
　　CREATE
　　！50017DEFINERytt
　　TRIGGERtrt1insertAFTERINSERTONt1
　　FOREACHROWBEGIN
　　UPDATEt1countSETcntcnt1；
　　END；
　　DELIMITER；
　　另外一个是删除动作触发：DELIMITER
　　USEyttnew
　　DROPTRIGGER！50032IFEXISTStrt1delete
　　CREATE
　　！50017DEFINERytt
　　TRIGGERtrt1deleteAFTERDELETEONt1
　　FOREACHROWBEGIN
　　UPDATEt1countSETcntcnt1；
　　END；
　　DELIMITER；
　　给表t1造1W条数据，达到上限：mysql：yttnewinsertt1（r1）withrecursivetmp（a，b）as（select1，1unionallselecta1，ceil（rand20）fromtmpwherea10000）
　　QueryOK，10000rowsaffected（0。68sec）
　　Records：10000Duplicates：0Warnings：0
　　计数器表t1count记录为1W。mysql：yttnewselectcntfromt1
　　cnt
　　10000
　　1rowinset（0。00sec）
　　插入前需要判断计数器表是否到达限制，如果到了这个限制则删除老旧记录先。我写一个存储过程简单理下逻辑：DELIMITER
　　USEyttnew
　　DROPPROCEDUREIFEXISTSspinsertt1
　　CREATEDEFINERyttPROCEDUREspinsertt1（
　　INfr1INT
　　）
　　BEGIN
　　DECLAREvcntINTDEFAULT0；
　　SELECTcntINTOvcntFROMt1
　　IFvcnt10000THEN
　　DELETEFROMt1ORDERBYidASCLIMIT1；
　　ENDIF；
　　INSERTINTOt1（r1）VALUES（fr1）；
　　END
　　DELIMITER；
　　此时，调用存储过程即可实现：mysql：yttnewcallspinsertt1（9999）；
　　QueryOK，1rowaffected（0。02sec）
　　mysql：yttnewselectcount（）fromt1；
　　count（）
　　10000
　　1rowinset（0。01sec）
　　这个存储过程的处理逻辑也可以继续优化为一次批量处理。比如每次多缓存一倍的表记录数，判断逻辑变为在2W条以前，只插入新记录，并不删除老记录，当到达2W条后，一次性删除旧的1W条记录。
　　这种方案有以下几个缺陷：
　　计数器表的记录更新是由insertdelete触发，如果对表进行truncate则计数器表不触发更新从而数据不一致。
　　对表进行drop操作则触发器也跟着删除，需要重建触发器，重置计数器表。
　　对表写入只能是类似存储过程这样的单一入口，不能是其他入口。
　　二、分区表解决方案
　　建立一个range分区，第一个分区有1W条记录，第二个分区为默认分区，等表记录数达到限制后，删除第一个分区，重新调整分区定义即可。
　　分区表初始定义：mysql：yttnewcreatetablet1（idintautoincrementprimarykey，r1int）partitionbyrange（id）（partitionp1valueslessthan（10001），partitionpmaxvalueslessthan（maxvalue））；
　　QueryOK，0rowsaffected（0。45sec）
　　查找第一个分区是否已满：mysql：yttnewselectcount（）fromt1partition（p1）；
　　count（）
　　10000
　　1rowinset（0。00sec）
　　删除第一个分区，并且重新调整分区表：mysql：yttnewaltertablet1droppartitionp1；
　　QueryOK，0rowsaffected（0。06sec）
　　Records：0Duplicates：0Warnings：0
　　mysql：yttnewaltertablet1reorganizepartitionpmaxinto（partitionp1valueslessthan（20001），partitionpmaxvalueslessthan（maxvalue））；
　　QueryOK，0rowsaffected（0。60sec）
　　Records：0Duplicates：0Warnings：0
　　这种方法的优势很明显：
　　表插入入口可以很随机，INSERT语句、存储过程、导文件都行。
　　删除第一个分区是一个DROP操作，非常快。
　　但也有缺点：表记录不能有空隙，如果有空隙，就得改变分区表定义。比如把分区p1的最大值改为20001，那即使在这个分区里有一半的记录不连续，也不影响检索分区里的总记录数。
　　三、通用表空间解决方案
　　提前计算好这张表1W条记录需要多少磁盘空间，之后在磁盘上划分一个区专门来存放这张表的数据。
　　挂载划好的分区，添加为InnoDB表空间的备选目录（tmpmysql）。mysql：yttnewcreatetablespacets1adddatafiletmpmysqlts1。
　　QueryOK，0rowsaffected（0。11sec）
　　mysql：yttnewaltertablet1tablespacets1；
　　QueryOK，0rowsaffected（0。12sec）
　　Records：0Duplicates：0Warnings：0
　　我大致算了下，不是很准确，所以记录上可能有点误差，不过意思已经很明确：等表报TABLEISFULL后即可。mysql：yttnewinsertt1（r1）values（200）；
　　ERROR1114（HY000）：Thetablet1isfull
　　mysql：yttnewselectcount（）fromt1；
　　count（）
　　10384
　　1rowinset（0。20sec）
　　表满后移除表空间，清空表，再插入新记录。mysql：yttnewaltertablet1
　　QueryOK，0rowsaffected（0。18sec）
　　Records：0Duplicates：0Warnings：0
　　mysql：yttnewdroptablespacets1；
　　QueryOK，0rowsaffected（0。13sec）
　　mysql：yttnewtruncatetablet1；
　　QueryOK，0rowsaffected（0。04sec）
　　另外一个就是在应用端处理：
　　可以提前在应用端缓存表数据，达到限定的记录数后再批量写入数据库端，写入数据库前，先清空表即可。
　　举个例子：表t1数据缓存到文件t1。csv，当t1。csv到达1W行时，数据库端清空表数据，导入t1。csv。
　　结语
　　之前MySQL在MyISAM时代，表属性maxrows来预估表的记录数，但也不是硬性规定，类似我上面写的使用通用表空间来达到限制表记录数的作用；到了InnoDB时代就没有一个直观的方法，更多是靠以上列出来的方法来解决这个问题，具体选哪个方案，还是得看需求。
　　文章推荐：
　　技术分享TiDB对大事务的简单拆分
　　技术分享MySQL内部临时表是怎么存放的
　　新特性解读MySQL8。0通用表达式（WITH）深入用法
　　社区近期动态
　　本文关键字：限制表记录数MySQL通用表空间