將傑奇章節數據庫表jieqi_article_chapter按照ceil(書號/10000)進行分表為jieqi_article_chapter_分表號
然後當主表刪除數據的時候，同步刪除分表的數據
使用navicat創建觸發器，下面是操作方法
1、使用navicat連接到數據庫
使用navicat連接到數據庫，過程就不說明了，很簡單。
2、打開數據庫，在數據庫名上面右鍵-新建查詢
3、執行命令
可以使用php來批量生成命令
<?php
// PHP Script to generate complete delete trigger for 99 tables
// Table name pattern and columns list
$table_columns = [
'chapterid', 'siteid', 'sourceid', 'sourcecid', 'sourcecorder', 'articleid', 'articlename', 'volumeid', 'posterid', 'poster', 'postdate', 'lastupdate',
'chaptername', 'chapterorder', 'size', 'saleprice', 'salenum', 'totalcost', 'attachment', 'summary', 'isimage', 'isvip', 'pages', 'chaptertype', 'power', 'display'
];
// Start the trigger creation script
$trigger_sql = "DELIMITER $$\n\n";
$trigger_sql .= "CREATE TRIGGER after_delete_jieqi_article_chapter\n";
$trigger_sql .= "AFTER DELETE ON jieqi_article_chapter\n";
$trigger_sql .= "FOR EACH ROW\n";
$trigger_sql .= "BEGIN\n";
// Start with the first condition using IF, not ELSEIF
$trigger_sql .= " IF OLD.articleid BETWEEN 1 AND 10000 THEN\n";
$trigger_sql .= " DELETE FROM jieqi_article_chapter_1 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;\n";
// Loop through 99 tables and create delete statements dynamically
for ($i = 2; $i <= 99; $i++) {
// Determine the range of articleid for each table
$start_articleid = ($i - 1) * 10000 + 1;
$end_articleid = $i * 10000;
// Prepare the delete statement for each table
$trigger_sql .= " ELSEIF OLD.articleid BETWEEN $start_articleid AND $end_articleid THEN\n";
$trigger_sql .= " DELETE FROM jieqi_article_chapter_$i WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;\n";
}
// Close the trigger SQL
$trigger_sql .= " END IF;\n";
$trigger_sql .= "END $$\n";
$trigger_sql .= "DELIMITER ;\n";
// Output the generated trigger SQL
echo $trigger_sql;
?>
輸入下面的命令到框中，生成觸發器
因為不支持動態數據獲取分表名，所以只能固定寫死
寫了99個分表，也就是可以存99萬本書，應該沒問題了。如果還有更多，自己額外補充
DELIMITER $$
CREATE TRIGGER after_delete_jieqi_article_chapter
AFTER DELETE ON jieqi_article_chapter
FOR EACH ROW
BEGIN
-- 根據 articleid 判斷屬於哪個分表
IF OLD.articleid BETWEEN 1 AND 10000 THEN
DELETE FROM jieqi_article_chapter_1 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 10001 AND 20000 THEN
DELETE FROM jieqi_article_chapter_2 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 20001 AND 30000 THEN
DELETE FROM jieqi_article_chapter_3 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 30001 AND 40000 THEN
DELETE FROM jieqi_article_chapter_4 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 40001 AND 50000 THEN
DELETE FROM jieqi_article_chapter_5 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 50001 AND 60000 THEN
DELETE FROM jieqi_article_chapter_6 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 60001 AND 70000 THEN
DELETE FROM jieqi_article_chapter_7 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 70001 AND 80000 THEN
DELETE FROM jieqi_article_chapter_8 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 80001 AND 90000 THEN
DELETE FROM jieqi_article_chapter_9 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 90001 AND 100000 THEN
DELETE FROM jieqi_article_chapter_10 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 100001 AND 110000 THEN
DELETE FROM jieqi_article_chapter_11 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 110001 AND 120000 THEN
DELETE FROM jieqi_article_chapter_12 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 120001 AND 130000 THEN
DELETE FROM jieqi_article_chapter_13 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 130001 AND 140000 THEN
DELETE FROM jieqi_article_chapter_14 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 140001 AND 150000 THEN
DELETE FROM jieqi_article_chapter_15 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 150001 AND 160000 THEN
DELETE FROM jieqi_article_chapter_16 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 160001 AND 170000 THEN
DELETE FROM jieqi_article_chapter_17 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 170001 AND 180000 THEN
DELETE FROM jieqi_article_chapter_18 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 180001 AND 190000 THEN
DELETE FROM jieqi_article_chapter_19 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 190001 AND 200000 THEN
DELETE FROM jieqi_article_chapter_20 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 200001 AND 210000 THEN
DELETE FROM jieqi_article_chapter_21 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 210001 AND 220000 THEN
DELETE FROM jieqi_article_chapter_22 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 220001 AND 230000 THEN
DELETE FROM jieqi_article_chapter_23 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 230001 AND 240000 THEN
DELETE FROM jieqi_article_chapter_24 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 240001 AND 250000 THEN
DELETE FROM jieqi_article_chapter_25 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 250001 AND 260000 THEN
DELETE FROM jieqi_article_chapter_26 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 260001 AND 270000 THEN
DELETE FROM jieqi_article_chapter_27 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 270001 AND 280000 THEN
DELETE FROM jieqi_article_chapter_28 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 280001 AND 290000 THEN
DELETE FROM jieqi_article_chapter_29 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 290001 AND 300000 THEN
DELETE FROM jieqi_article_chapter_30 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 300001 AND 310000 THEN
DELETE FROM jieqi_article_chapter_31 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 310001 AND 320000 THEN
DELETE FROM jieqi_article_chapter_32 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 320001 AND 330000 THEN
DELETE FROM jieqi_article_chapter_33 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 330001 AND 340000 THEN
DELETE FROM jieqi_article_chapter_34 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 340001 AND 350000 THEN
DELETE FROM jieqi_article_chapter_35 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 350001 AND 360000 THEN
DELETE FROM jieqi_article_chapter_36 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 360001 AND 370000 THEN
DELETE FROM jieqi_article_chapter_37 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 370001 AND 380000 THEN
DELETE FROM jieqi_article_chapter_38 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 380001 AND 390000 THEN
DELETE FROM jieqi_article_chapter_39 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 390001 AND 400000 THEN
DELETE FROM jieqi_article_chapter_40 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 400001 AND 410000 THEN
DELETE FROM jieqi_article_chapter_41 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 410001 AND 420000 THEN
DELETE FROM jieqi_article_chapter_42 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 420001 AND 430000 THEN
DELETE FROM jieqi_article_chapter_43 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 430001 AND 440000 THEN
DELETE FROM jieqi_article_chapter_44 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 440001 AND 450000 THEN
DELETE FROM jieqi_article_chapter_45 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 450001 AND 460000 THEN
DELETE FROM jieqi_article_chapter_46 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 460001 AND 470000 THEN
DELETE FROM jieqi_article_chapter_47 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 470001 AND 480000 THEN
DELETE FROM jieqi_article_chapter_48 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 480001 AND 490000 THEN
DELETE FROM jieqi_article_chapter_49 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 490001 AND 500000 THEN
DELETE FROM jieqi_article_chapter_50 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 500001 AND 510000 THEN
DELETE FROM jieqi_article_chapter_51 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 510001 AND 520000 THEN
DELETE FROM jieqi_article_chapter_52 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 520001 AND 530000 THEN
DELETE FROM jieqi_article_chapter_53 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 530001 AND 540000 THEN
DELETE FROM jieqi_article_chapter_54 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 540001 AND 550000 THEN
DELETE FROM jieqi_article_chapter_55 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 550001 AND 560000 THEN
DELETE FROM jieqi_article_chapter_56 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 560001 AND 570000 THEN
DELETE FROM jieqi_article_chapter_57 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 570001 AND 580000 THEN
DELETE FROM jieqi_article_chapter_58 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 580001 AND 590000 THEN
DELETE FROM jieqi_article_chapter_59 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 590001 AND 600000 THEN
DELETE FROM jieqi_article_chapter_60 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 600001 AND 610000 THEN
DELETE FROM jieqi_article_chapter_61 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 610001 AND 620000 THEN
DELETE FROM jieqi_article_chapter_62 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 620001 AND 630000 THEN
DELETE FROM jieqi_article_chapter_63 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 630001 AND 640000 THEN
DELETE FROM jieqi_article_chapter_64 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 640001 AND 650000 THEN
DELETE FROM jieqi_article_chapter_65 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 650001 AND 660000 THEN
DELETE FROM jieqi_article_chapter_66 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 660001 AND 670000 THEN
DELETE FROM jieqi_article_chapter_67 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 670001 AND 680000 THEN
DELETE FROM jieqi_article_chapter_68 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 680001 AND 690000 THEN
DELETE FROM jieqi_article_chapter_69 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 690001 AND 700000 THEN
DELETE FROM jieqi_article_chapter_70 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 700001 AND 710000 THEN
DELETE FROM jieqi_article_chapter_71 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 710001 AND 720000 THEN
DELETE FROM jieqi_article_chapter_72 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 720001 AND 730000 THEN
DELETE FROM jieqi_article_chapter_73 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 730001 AND 740000 THEN
DELETE FROM jieqi_article_chapter_74 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 740001 AND 750000 THEN
DELETE FROM jieqi_article_chapter_75 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 750001 AND 760000 THEN
DELETE FROM jieqi_article_chapter_76 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 760001 AND 770000 THEN
DELETE FROM jieqi_article_chapter_77 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 770001 AND 780000 THEN
DELETE FROM jieqi_article_chapter_78 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 780001 AND 790000 THEN
DELETE FROM jieqi_article_chapter_79 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 790001 AND 800000 THEN
DELETE FROM jieqi_article_chapter_80 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 800001 AND 810000 THEN
DELETE FROM jieqi_article_chapter_81 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 810001 AND 820000 THEN
DELETE FROM jieqi_article_chapter_82 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 820001 AND 830000 THEN
DELETE FROM jieqi_article_chapter_83 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 830001 AND 840000 THEN
DELETE FROM jieqi_article_chapter_84 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 840001 AND 850000 THEN
DELETE FROM jieqi_article_chapter_85 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 850001 AND 860000 THEN
DELETE FROM jieqi_article_chapter_86 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 860001 AND 870000 THEN
DELETE FROM jieqi_article_chapter_87 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 870001 AND 880000 THEN
DELETE FROM jieqi_article_chapter_88 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 880001 AND 890000 THEN
DELETE FROM jieqi_article_chapter_89 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 890001 AND 900000 THEN
DELETE FROM jieqi_article_chapter_90 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 900001 AND 910000 THEN
DELETE FROM jieqi_article_chapter_91 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 910001 AND 920000 THEN
DELETE FROM jieqi_article_chapter_92 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 920001 AND 930000 THEN
DELETE FROM jieqi_article_chapter_93 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 930001 AND 940000 THEN
DELETE FROM jieqi_article_chapter_94 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 940001 AND 950000 THEN
DELETE FROM jieqi_article_chapter_95 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 950001 AND 960000 THEN
DELETE FROM jieqi_article_chapter_96 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 960001 AND 970000 THEN
DELETE FROM jieqi_article_chapter_97 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 970001 AND 980000 THEN
DELETE FROM jieqi_article_chapter_98 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
ELSEIF OLD.articleid BETWEEN 980001 AND 990000 THEN
DELETE FROM jieqi_article_chapter_99 WHERE articleid = OLD.articleid AND chapterid = OLD.chapterid;
END IF;
END $$
DELIMITER ;
執行後將會生成一個名字為after_delete_jieqi_article_chapter的觸發器
4、檢查是否生效
在主表刪除一個數據，然後查看分表是否同步刪除
經過觀察，發現同步刪除了。
5、使用navicat查看觸發器
1）打開數據庫
2）雙擊表，打開數據庫的表
比如此項中是打開jieqi_article_chapter
3）按下鍵盤的ctrl+D打開設計表
4）點擊觸發器
可以看到已經創建好的觸發器
如果想修改和刪除該觸發器，可以在這裡操作
到此為止。
發布者：彬彬筆記，轉載請註明出處：https://www.binbinbiji.com/zh-hant/jianzhanjiaoliu/3634.html
