浩森小说导出处理

第一步:.sql.gz解压

gunzip  novel_book_20190516_095916.sql.gz

第二步:mysql导入数据库

mysql -u root -p
use novel_book;
source  /opt/data/novel_book_20190516_095916.sql;

第三步:小说相关的数据库表

n_book      小说信息介绍
n_bookinfo  小说章节
ien_chapter 小说章节

第四步:ien_chapter小说章节处理

SELECT * FROM `ien_chapter` WHERE title not like '第%'  or title  not  like '%章%' order by bid asc,id  asc;
SELECT * FROM `ien_chapter` WHERE title not like '第%' and title  like '%.%' order by bid asc,id  asc;
SELECT * FROM `ien_chapter` WHERE title not like '第%' and title  like '%、%' order by bid asc,id  asc;
SELECT *  FROM `ien_chapter` WHERE title   like '第%' and title  not  like '%章%' and title  like '% %' order by bid asc,id  asc;

SELECT *  FROM `ien_chapter` WHERE (title  REGEXP '[^0-9.]')=0 order by bid asc,id  asc;
SELECT * FROM `ien_chapter` WHERE  content  like  concat('%',title,'%')  order by bid asc,id  asc;

update ien_chapter  set title=REPLACE (title,'话：','章 ') where title not like '%第%' or title not like '%章%';
update ien_chapter  set title= CONCAT('第',REPLACE(title,'、','章 '))  WHERE title not like '第%' and title  like '%、%' order by bid asc,id  asc;
update ien_chapter  set title=  REPLACE(title,'、','章 ')  WHERE title  like '第%' and title  like '%、%'  and  title not like '%章%' order by bid asc,id  asc;
update ien_chapter  set title= CONCAT('第',REPLACE(title,'.','章 '))  WHERE title not like '第%' and title  like '%.%' order by bid asc,id  asc; 
update ien_chapter  set title= CONCAT('第',REPLACE(title,'回','章 '))  WHERE title not like '第%' and title  like '%回%' order by bid asc,id  asc;
update ien_chapter  set title= REPLACE(title,' ','章 ')  WHERE title   like '第%' and title  not  like '%章%' and title  like '% %'  order by bid asc,id  asc;
update ien_chapter  set title= REPLACE(title,'：','章 ')  WHERE title   like '第%' and title  not  like '%章%' and title  like '%：%'  order by bid asc,id  asc;
update ien_chapter  set title= REPLACE(title,':','章 ')  WHERE title   like '第%' and title  not  like '%章%' and title  like '%:%'  order by bid asc,id  asc;
update ien_chapter  set title= CONCAT('第',REPLACE(title,' ','章 '))  WHERE title not like '第%' and title  like '% %' order by bid asc,id  asc;
update ien_chapter  set title= CONCAT('第',title)  WHERE title not like '第%' and title  like '%章%' order by bid asc,id  asc;

update ien_chapter  set title= REPLACE(title,'第第','第')  WHERE  title like '第第%';
update ien_chapter  set title= REPLACE(title,'章章','章')  WHERE  title like '%章章%';

update ien_chapter  set title= REPLACE(REPLACE(title,'【','第'),'】','章 ')  WHERE  title   like '【%' and title  like '%】%' order by bid asc,id  asc;
update ien_chapter  set title= CONCAT('第',title,'章 ')  WHERE (title  REGEXP '[^0-9.]')=0 order by bid asc,id  asc;
update ien_chapter  set title= REPLACE(title,'卷.','章 ')  WHERE title   like '第%' and  title   like '%卷.%' order by bid asc,id  asc;
update ien_chapter  set title= REPLACE(title,'第一卷','第')  WHERE title   like '第一卷%'   order by bid asc,id  asc;
update ien_chapter  set title= CONCAT('第',title,'章 ')  WHERE title   like '后续%' and title not like '%章%'  order by bid asc,id  asc;
update ien_chapter  set title= CONCAT('第',title,'章 ')  WHERE title   like '番外%' and title not like '%章%'  order by bid asc,id  asc;

update ien_chapter  set title= CONCAT('第',idx,'章 ',title)  WHERE title not like '第%'  or title  not  like '%章%' order by bid asc,id  asc;

//匹配"第xx章" 替换为空
UPDATE ien_chapter  SET title = REPLACE(title, CONCAT(substring_index(title,'章',1),'章'),'');
update ien_chapter  set title= CONCAT('第',idx,'章 ',title)

update ien_chapter  set content= REPLACE(content,title,' ')  WHERE  content  like  concat('%',title,'%')  order by bid asc,id  asc;

第五步:ien_chapter小说章节下载

//导出存储过程
DROP PROCEDURE IF EXISTS NovelExp;

CREATE PROCEDURE NovelExp(in arg INT)  
BEGIN  
     DECLARE sToday INT default(1); 
     DECLARE eToday INT;    

     set sToday = arg;  
     set eToday = sToday + 100; 



     WHILE sToday  < eToday DO

        set @sql=concat("SELECT title,'\n', content  FROM  `ien_chapter` WHERE bid =",sToday," ORDER BY id ASC   into outfile   '/tmp/",sToday,".txt' lines terminated by '\r\n'");
        prepare execsql from @sql;
        EXECUTE execsql;

        SET  sToday = sToday + 1; 
    END WHILE;
END;  

=================================================================
// call  NovelExp(1);
// call  NovelExp(101);

第六步:ien_book(id,image)和 ien_admin_attachment(id,path) 导出小说id,title,path

 SELECT ien_book.id,'||',ien_book.title,'||',ien_admin_attachment.path  FROM ien_book INNER JOIN ien_admin_attachment ON ien_admin_attachment.id = ien_book.image;
 SELECT ien_book.id,'||',ien_book.title,'||',ien_admin_attachment.path  FROM ien_book INNER JOIN ien_admin_attachment ON ien_admin_attachment.id = ien_book.image into outfile   '/tmp/novel.txt' lines terminated by '\r\n';

第七步:下载小说封面，图片以id命名

#!/bin/bash
#备份IFS
OLD_IFS="$IFS"
#设置新的分隔符为;
IFS="||"
#读取文件中的行
while read LINE
  do
     echo $LINE
     #将字符串$LINE分割到数组
     arr=($LINE)
     # ${arr[@]}存储整个数组  
    img=`echo ${arr[4]}  | sed -e 's/^[ \t]*//g'`
    houzhui="${img##*.}"
    #curl $img  -o /tmp/${arr[0]}.$houzhui
    wget -c $img -O /tmp/${arr[0]}.$houzhui
    #echo $img
    #echo "$houzhui"
    # echo ${arr[0]}
    #for s in ${arr[@]}  
    #   do
    #   echo $s
    #    
    #   done
    done <novel.txt
#恢复IFS
IFS="$OLD_IFS"
#linux shell批量告诉下载,保存原始文件名
#wget -x -N -i  1.txt

第八步:重命名图片和小说

#!/bin/bash
#备份IFS
OLD_IFS="$IFS"
#设置新的分隔符为;
IFS="||"
#读取文件中的行
while read LINE
  do
     echo $LINE
     #将字符串$LINE分割到数组
     arr=($LINE)
     # ${arr[@]}存储整个数组  
     oldfile=/opt/novel/txt/${arr[0]}.txt
     newfile=/opt/novel/txt/${arr[2]}.txt
     mv $oldfile $newfile

    done <novel.txt
#恢复IFS
IFS="$OLD_IFS"
#linux shell批量告诉下载,保存原始文件名
#wget -x -N -i  1.txt

浩森小说导出处理

热门