小说站-维护-清理无效小说存储

1.清理指定id的小说所有内容

DROP PROCEDURE IF EXISTS clear_invalid_novel; 
CREATE PROCEDURE clear_invalid_novel()
BEGIN
    DECLARE bookid varchar(255) DEFAULT '1431094810327994368';
    DELETE FROM book_content WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content0 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content1 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content2 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content3 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content4 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content5 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content6 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content7 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content8 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_content9 WHERE index_id in (SELECT id FROM book_index WHERE book_id=bookid);
    DELETE FROM book_index WHERE book_id=bookid;
    DELETE FROM book WHERE id=bookid;
END;
call clear_invalid_novel();

2.删除无效的小说文本

delete from book_content where index_id not in(SELECT id from book_index);
delete from book_content0 where index_id not in(SELECT id from book_index);
delete from book_content1 where index_id not in(SELECT id from book_index);
delete from book_content2 where index_id not in(SELECT id from book_index);
delete from book_content3 where index_id not in(SELECT id from book_index);
delete from book_content4 where index_id not in(SELECT id from book_index);
delete from book_content5 where index_id not in(SELECT id from book_index);
delete from book_content6 where index_id not in(SELECT id from book_index);
delete from book_content7 where index_id not in(SELECT id from book_index);
delete from book_content8 where index_id not in(SELECT id from book_index);
delete from book_content9 where index_id not in(SELECT id from book_index);

3.整合表内容

create table book_content_test as SELECT id, index_id,content from (
    SELECT * FROM book_content0 B0 WHERE B0.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content1 B1 WHERE B1.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content2 B2 WHERE B2.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content3 B3 WHERE B3.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content4 B4 WHERE B4.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content5 B5 WHERE B5.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content6 B6 WHERE B6.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content7 B7 WHERE B7.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content8 B8 WHERE B8.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') union all  
    SELECT * FROM book_content9 B9 WHERE B9.index_id in (SELECT id FROM book_index WHERE book_id='1431142380391714816') 
) as T1;