PHPME_CMS开发笔记

1.2009-02-15

left join连接查询的高级使用

SELECT a.*,b.surports,b.againsts,count(c.id) FROM phpme_news as a left join phpme_digg as b on(b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) where a.cat_id in(45) group by id order by id desc limit 0,10
 
 
2.2009-02-19
union all连接查询的高级应用
select z.* from (SELECT a.*,b.cat_name FROM phpme_news as a left join phpme_category as b on(a.cat_id=b.cat_id) where tags like ‘% 测试标签 %’
union all
SELECT a.*,b.cat_name FROM phpme_article as a left join phpme_category as b on(a.cat_id=b.cat_id) where tags like ‘% 测试标签 %’
union all
SELECT a.*,b.cat_name FROM phpme_download as a left join phpme_category as b on(a.cat_id=b.cat_id) where tags like ‘% 测试标签 %’) as z order by z.time desc limit 0,10
 
 
3.2009-02-21
获取搜索的结果数
select sum(total) from (select count(*) as total from phpme_news as a where title like ‘%php atr%’ union select count(*) as total from phpme_news as a where title like ‘%php%’ union select count(*) as total from phpme_news as a where title like ‘%atr%’ ) as z
 
获取搜索的结果
select z.* from(SELECT a.*,b.surports,b.againsts,count(c.id),d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 and a.title like ‘%巴西 总统%’ group by a.id union SELECT a.*,b.surports,b.againsts,count(c.id),d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 and a.title like ‘%巴西%’ group by id union SELECT a.*,b.surports,b.againsts,count(c.id),d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 and a.title like ‘%总统%’ group by id ) as z order by z.time desc limit 0,20
 
关于count聚集函数的别名
SELECT a.*,b.surports,b.againsts,count(c.id) as comment,d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 group by id order by id desc limit 0,20
 
 
4.2009-02-22
关于多表不同列的SELECT联合查询问题。
由于表的列不同,是不能够使用union all联合查询的,它会提示错误,
我是这么解决的
 
select z.* from (
 SELECT a.id,a.title,a.time,a.clicked,a.author,a.cat_id,a.thumb,a.content,a.tags,a.content_url,b.cat_name,c.surports,c.againsts,count(d.id) FROM phpme_news as a left join phpme_category as b on(a.cat_id=b.cat_id) left join phpme_digg as c on (c.content_id=a.id and c.cat_id=a.cat_id) left join phpme_comment as d on(a.id=d.content_id and a.cat_id=d.cat_id) where tags like ‘% 测试标签 %’ group by id
 union all
 SELECT a.id,a.title,a.time,a.clicked,a.author,a.cat_id,a.thumb,a.content,a.tags,a.content_url,b.cat_name,c.surports,c.againsts,count(d.id) FROM phpme_article as a left join phpme_category as b on(a.cat_id=b.cat_id) left join phpme_digg as c on (c.content_id=a.id and c.cat_id=a.cat_id) left join phpme_comment as d on(a.id=d.content_id and a.cat_id=d.cat_id) where tags like ‘% 测试标签 %’ group by id
 union all
 SELECT a.id,a.title,a.time,a.clicked,a.author,a.cat_id,a.thumb,a.content,a.tags,a.content_url,b.cat_name,c.surports,c.againsts,count(d.id) FROM phpme_download as a left join phpme_category as b on(a.cat_id=b.cat_id) left join phpme_digg as c on (c.content_id=a.id and c.cat_id=a.cat_id) left join phpme_comment as d on(a.id=d.content_id and a.cat_id=d.cat_id) where tags like ‘% 测试标签 %’ group by id
 ) as z order by z.time desc limit 0,10
 
即不能对一张表完全查询,而只对所关心的列查出来,这样三张表查出来的列都是一样的。
不要用a.*。
 
 
DIGG获取相关内容
select z.* from (
 SELECT a.id,a.title,a.time,a.clicked,a.author,a.cat_id,a.thumb,a.content,a.tags,a.content_url,b.cat_name,c.surports,c.againsts,count(d.id) as comment FROM phpme_news as a left join phpme_category as b on(a.cat_id=b.cat_id) left join phpme_digg as c on (c.content_id=a.id and c.cat_id=a.cat_id) left join phpme_comment as d on(a.id=d.content_id and a.cat_id=d.cat_id) group by id
 union all
 SELECT a.id,a.title,a.time,a.clicked,a.author,a.cat_id,a.thumb,a.content,a.tags,a.content_url,b.cat_name,c.surports,c.againsts,count(d.id) as comment FROM phpme_article as a left join phpme_category as b on(a.cat_id=b.cat_id) left join phpme_digg as c on (c.content_id=a.id and c.cat_id=a.cat_id) left join phpme_comment as d on(a.id=d.content_id and a.cat_id=d.cat_id) group by id
 union all
 SELECT a.id,a.title,a.time,a.clicked,a.author,a.cat_id,a.thumb,a.content,a.tags,a.content_url,b.cat_name,c.surports,c.againsts,count(d.id) as comment FROM phpme_download as a left join phpme_category as b on(a.cat_id=b.cat_id) left join phpme_digg as c on (c.content_id=a.id and c.cat_id=a.cat_id) left join phpme_comment as d on(a.id=d.content_id and a.cat_id=d.cat_id) group by id
 ) as z ,phpme_digg as y where z.id=y.content_id and z.cat_id=y.cat_id order by z.surports desc,z.againsts asc,z.id desc limit 0,10

5.2009-03-16
看来那位SYU说的对,我居然还在用用LIKE进行站内搜索,我现在才发现,这并不是什么效率、百万级、千万级或者优化的问题,LIKE根本不适合搜索引擎,虽然MYSQL的全文检索对中文支持不好,可以我想总会有办法变通的,就像PHPCMS、DEDECMS、DISCUZ!等等一系列强大的PHP程序一样,我想我也能做到。
对了,DISCUZ!,目前跟我一样用的是LIKE,所以它的搜索效率不高,囧TL…
目前可能会花比较多的时间对PHPME_CMS进行测试,虽然以后求职或者个人的需要,但是PHPME_BLOG暂时不忙开发,
慢慢来,再过一些时间,慢慢来…

mysql全文检索对中文支持的不好
我测试过
的确如此
搜索字符串必须为4个字及以上
比如‘中亚地区’
如果是‘中国’则没有结果
不过检索速度绝对快一个数量级以上
可以用大数据量的表试试

为什么要设置 Mysql 的 ft_min_word_len=1 ?
从 Mysql 4.0 开始就支持全文索引功能,但是 Mysql 默认的最小索引长度是 4。如果是英文默认值是比较合理的,但是中文绝大部分词都是2个字符,这就导致小于4个字的词都不能被索引,全文索引功能就形同虚设了。国内的空间商大部分可能并没有注意到这个问题,没有修改 Mysql 的默认设置。
为什么要用全文索引呢?
一般的数据库搜索都是用的SQL的 like 语句,like 语句是不能利用索引的,每次查询都是从第一条遍历至最后一条,查询效率极其低下。一般数据超过10万或者在线人数过多,like查询都会导致数据库崩溃。这也就是为什么很多程序都只提供标题搜索的原因了,因为如果搜索内容,那就更慢了,几万数据就跑不动了。
Mysql 全文索引是专门为了解决模糊查询提供的,可以对整篇文章预先按照词进行索引,搜索效率高,能够支持百万级的数据检索。
如果您使用的是自己的服务器,请马上进行设置,不要浪费了这个功能。
如果您使用的是虚拟主机,请马上联系空间商修改配置。首先,Mysql 的这个默认值对于中文来说就是一个错误的设置,修改设置等于纠正了错误。其次,这个配置修改很简单,也就是几分钟的事情,而且搜索效率提高也降低了空间商数据库宕掉的几率。如果你把本篇文章发给空间商,我相信绝大部分都会愿意改的。

设置方法:
请联系服务器管理员修改 my.ini (Linux 下是 my.cnf ) ,在 [mysqld] 后面加入一行“ft_min_word_len=1”,然后重启Mysql,再登录网站后台(模块管理->全站搜索)重建全文索引,否则将无法使用全站搜索功能。

6.2009-03-17

PHPME_CMS搜索引擎完成初步开发,这是PHPME_CMS特有的搜索引擎,结合了PHPCMS,DEDECMS的设计思路,
鉴于MYSQL对中文的支持不太好,不管什么中文编码,少了4个字就不能全文检索,
根据它的一些原因,
首先将中文内容分词,分词是关键,可以自定义分词字典,根据字典中的排序量进行分词,
我这里建了个收集用户搜索关键字的表,用于收集关键字然后在后台分词,当然,为了排除一些恶意的用户,
搜索量必须大于自定义的一定数才会被定义为分词,
分词的一些关键:
中国人
一般的分词:中国 国人
好一点的分词: 中国 人
我的分词方法:中国人(“中国人”的排序量高于“中国”)

将一个文章标题与内容结合,相同的分词去掉,然后将这些分词转换为ASCII码。
为什么不转换成区位码呢?因为我比较喜欢ASCII。
这样“PHP”三个字节的数也能搜出来
每个UTF-8编码的汉字会有6个数的ASCII码,中间用空格分割,满足MYSQL全文检索的要求,
搜索起来很快,就算是千万级的搜索也不会上秒!!!!