2019年05月18日 20:34 阅读量:1390
第一步:.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