查询出wordpres有死链的文章

前2天清理了下文章，而且清理文章较多，有几百篇，那么密然会留下死链的。外部死链无法处理，而内部死链可以通过查询再修改。

今天抽空写了2个存储过程，执行后可查询出文章的ID。

– 调用函数
CREATE DEFINER=`root`@`%` PROCEDURE `getnoconn`(out idlist varchar(8000))
BEGIN
declare done int;
declare temp_list varchar(8000);
declare temp_id varchar(50);
declare r_value varchar(2000);
declare cur_qry1 CURSOR for
select CONCAT(‘m/’,id,’.h’) s from wp_posts where wp_posts.post_status=’private’ order by id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set temp_list = ’00′;
open cur_qry1;
posLoop:LOOP
FETCH  cur_qry1 into temp_id;
IF (done=1)  THEN  LEAVE posLoop;  END IF;
set r_value = null;
call getnoconn_sub(temp_id,@z);
set r_value = @z;
if r_value is not null then
if INSTR(temp_list,ifnull(r_value,’000000000000′)) = 0 then
set temp_list = CONCAT(temp_list,’,',r_value);
end if;
end if;
SET done=0;
END LOOP posLoop;
CLOSE cur_qry1;
set idlist = temp_list;
END

– 子函数
CREATE DEFINER=`root`@`%` PROCEDURE `getnoconn_sub`(in s_id varchar(30), out idlist varchar(8000))
BEGIN
declare done int;
declare temp_str varchar(50);
declare temp_list varchar(8000);
declare cur_qry1 CURSOR for
select id from wp_posts where  wp_posts.post_status=’publish’ and wp_posts.post_content like CONCAT(‘%’,s_id,’%') order by id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur_qry1;
posLoop:LOOP
FETCH  cur_qry1 into temp_str;
IF (done=1)  THEN  LEAVE posLoop;  END IF;
set temp_list = CONCAT(IFNULL(temp_list,”),’,',temp_str);
SET done=0;
END LOOP posLoop;
CLOSE cur_qry1;
set idlist = temp_list;
END

执行语句：

call getnoconn(@a);
select @a;

查询死链文章的ID后，看了文章不算多，通过手动修正文章的死链。