最近想给本站加上文章索引的页面，就是在一个页面上展示所有的分类及该分类的文章，然后置顶到首页，效果见本站文章索引。开始的思路是：先新建一个页面，然后设置好置顶等，用存储过程更新该页面的内容，用触发器在有新文章发表或文章标题有更新时触发执行存储过程。看起来应该挺顺利的，不过实现过程倒没想象那么好了。

更新索引存储过程

这个存储过程的功能是从数据库里查询出所有的分类以及该分类下的文章的ID和标题，然后用游标生成html内容（wordpress直接存储页面的html内容），再更新为索引页面的内容。

要把下面sql语句里面的 “索引页面的url” 替换成实际的URL再执行。

SET NAMES utf8;

use blog;

delimiter //

drop procedure if exists sp_updateIndex;

create procedure sp_updateIndex ()
begin

    declare categroies varchar(21845);
    declare details varchar(21845);
    declare done int default 0;

    declare lastCatgName varchar(100);
    declare catgName varchar(200);
    declare postId bigint(20);
    declare postTitle varchar(200);

    declare cur cursor for 
        select cast(convert(terms.name using utf8) as char), post.id, cast(convert(post.post_title using utf8) as char)
        from wp_term_taxonomy tax
            join wp_terms terms on tax.term_id = terms.term_id
            join wp_term_relationships rel on tax.term_taxonomy_id = rel.term_taxonomy_id
            join wp_posts post on rel.object_id = post.id
        where tax.taxonomy = 'category' and post.post_status = 'publish' order by tax.term_id desc;

    declare continue handler for not found set done = 1;

    set lastCatgName = '';
    set categroies = '<h3>index</h3><ul>';
    set details = '';

    open cur;

    repeat fetch cur into catgName, postId, postTitle;

        if not done then
            if strcmp(lastCatgName, catgName) != 0 then
                set categroies = concat(categroies, '<li><a href="索引页面的url#', catgName, '">', catgName, '</a></li>');

                if strcmp(lastCatgName, '') = 0 then
                    set details = concat(details, '<br><hr><a name="', catgName, '"></a><h3>', catgName, '</h3><ul>');
                else
                    set details = concat(details, '</ul><br><hr><a name="', catgName, '"></a><h3>', catgName, '</h3><ul>');
                end if;

                set lastCatgName = catgName;
            end if;

            set details = concat(details, '<li><a target="_blank" href="http://coderbee.net/?p=', postId, '">', postTitle, '</a></li>');
        end if;
    until done end repeat ;

    close cur;

    set categroies = concat(categroies, '</ul>');
    set details = concat(details, '</ul>');
    update wp_posts set post_content = concat(categroies, '<!--more-->', details) where id = 697;
end;
//

实现是不怎么复杂的。

乱码坑

碰到的第一个坑是乱码，由于MySQL采用源码安装，且安装时没有指定默认编码，所以MySQL使用的默认是 latin1 字符集，好在wordpress在创建表时把表的编码指定为 utf8，不然处理要更多点，关于编码的解决可以看 MySQL 乱码 与 字符集。

触发器坑

存储过程测试ok后，自然就是用触发器来调用了，触发器也很简单：

SET NAMES utf8;

use blog;

delimiter //

drop trigger if exists trigger_updateIndex;

create trigger trigger_updateIndex after insert on wp_posts for each row begin 
    if new.post_status = 'publish' then
        call sp_updateIndex;
    end if;
end
//

触发器的创建没有问题，但是却出现 ERROR 1442 (HY000): Can't update table 'wp_posts' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 异常。

之所以变成坑是因为我一开始直接在页面上进行测试，所以出错了也看不到，只是发现没效果就觉得出问题了。后来在命令行下直接插入才看到错误信息的。

google一番后，知道这是MySQL的一个限制，不能在触发器里对本表进行更新操作，本表就是触发器创建时用 on 指定的表。网上有一些人的解决方法不适用于我这里，因为需求不同，他们的场景是如果新的记录满足一定的条件就更新新的记录，这样可以用 set 语句结合 before 搞定，而我这里是要更新与新记录不同的某一条记录。所以用触发器是行不通了。

解决的一个方法就是在程序里解决，调用插入成功后在执行这个存储过程，不过对wordpress和php都不了解，就不想这样搞了。

现在用 cron 调度 加脚本的方式来搞，脚本的内容： /absolute/path/to/mysql -u username --password='password' < /absolute/path/to/trigger.sql。

trigger.sql：

use blog;
set names utf8;
call sp_updateIndex;

个人博客的发表频率是很低的，把频率设置为每天一次已完全足够。

2014-01-27 更新，MySQL事件调度器 定时更新

MySQL 5.1 引入的事件调度器可以作为定时任务调度器，取代系统的cron调度，调度时间可以精确到秒，实时性好。

drop event if exists event_updateIndex;

create event event_updateIndex;
on schedule every 24 hour
starts timestamp '2014-01-27 23:50:00'
do call sp_updateIndex;

事件调度的使用可参考： http://coderbee.net/index.php/db/20140127/752

欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。