Day: February 16, 2009

1.2009-02-15

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全文检索的要求，
搜索起来很快，就算是千万级的搜索也不会上秒！！！！