Implementing Hard Row Limit in MySQL Tables
Question: Designers often face the need to enforce a maximum limit on rows stored in a MySQL table. Is there a definitive method to achieve this restriction beyond merely specifying a hint?
Answer: Setting a maximum row limit is a common database challenge. While the MAX_ROWS property acts as a suggestion rather than an absolute cap, there are indeed ways to impose a hard limit on MySQL tables:
Trigger-Based Mechanism:
DELIMITER $$ CREATE TRIGGER trigger1 BEFORE INSERT ON table1 FOR EACH ROW BEGIN SELECT COUNT(*) INTO @cnt FROM table1; IF @cnt >= 25 THEN CALL sth(); -- raise an error END IF; END $$ DELIMITER ;
Note: COUNT operations can slow down on large InnoDb tables.
MySQL 5.5 Signal/Resignal Statements:
Cron Script Method:
Additional Considerations:
The above is the detailed content of How to Enforce a Hard Row Limit in MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!