易栈网-膘叔

假设的应用场景 

我假定博客园要在首页为群组开辟一块空间，推广小组，小组能在首页显示的条件有四个：

1.       页面点击量大于10w

2.       小组人数大于1000

3.       小组帖子数大于10000

4.       小组在2007年之前创建

现在假定4个条件都满足的小组只有两个，太少了，推广位可以推广10个小组；这时候运营人员要求这4个条件中满足3个但是第4个条件不满足的小组算符合条件的小组，如果4个条件都满足就认为这个小组太火了，不需要在首页推广它了。业务逻辑想清楚了，下一步就该写代码了，数据逻辑层的代码的任务假定交给我了，我要考虑满足4个条件中3个成立的sql怎么写。

为了叙事方便，我们假如小组表的名字为Group，相关的条件字段是Pv,UserCount,PostCount,CreateTime：分别表示小组的点击量，人数，帖子数，创建时间. 

我来写sql语句，上面的四个条件满足至少3个，有多少种情况呢？这是一个组合问题，一共有多少种的公式我已经忘记了，我要根据感觉写写看：

这个Sql语句条件还行，但是我们的题目是n个条件m个条件成立，如果多了还这么写，恐怕就很累了，能不能改进呢？答案是肯定的，要不我就不写这篇随笔了，呵呵

如果上面的三个表达式加起来值是3就说明恰好满足三个条件，如果是两个条件就是等于2，如果扩展为n个条件m个条件成立也很容易写，很容易维护、修改。 

这是一个sql条件语句的技巧，希望对你有用。

本文假设的场景纯属虚设，请勿遐想。J

原文地址：http://www.cnblogs.com/yukaizhao/archive/2008/11/14/sql_condition_m_n.html

PS：

顺便说明一下，在mysql中也支持这样的用法（4我没有试过，但是5是支持这样的用法的。）虽然这样的用法比较容易写和维护及修改，但看上去还是有点妖。而且，效率不一定能保证。

问题：主从服务器表类型的选择

一般的共识是主服务器使用innodb，从服务器使用myisam，以便各尽其能。

问题：主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb，因为不涉及查询，所以可以使用varchar等来存储字符串来节省空间，从服务器一般是 myisam，因为涉及查询，所以必须在char和varchar之间仔细权衡，没有varchar, text, blob字段的表是静态表，反之是动态表，静态表的检索效率要比动态表好若干倍，一般来说，所有涉及大结果集的查询都应该尽可能保证在静态表上完成，这里 说一个例子：比如说常见的articles表有title(varchar), body(text)等字段，在做文章列表的时候，因为不是静态表，所以查询不会很快，下面开始重构解决方案：把原来的articles表拆分成 subjects表和contents表，title字段设置为一个足够的char类型放在subjects表里，body字段还保持是text类型放到 contents表里，subjects和contents表之间的关系是一对多，这样，顺带着也方便的实现了多页文章的功能，而且更重要的是在查询文章 列表的时候，操作都是在subjects静态表里完成，效率肯定会比前一种方案提升很多。

问题：主从服务器NOW()函数造成数据不一致

假设在主服务器上执行一条INSERT .... VALUES ( ..., NOW())，那么在从服务器上也会同样执行一条的SQL语句，但是一来主从服务器各自的时间设置可能就不一致，二来主从服务器间的SQL同步也可能存在 时间上的的延迟，这样，NOW()在两台服务器上的结果就可能不一致。解决方法是显而易见的，就是不要使用NOW()，时间的计算在应用程序里完成。这里 介绍一个额外的小技巧：在PHP里如果想获得当前时间的时间戳，不要用time()，而应该使用$_SERVER[‘REQUEST_TIME’] （PHP版本大于5.1有效），这样少做了一次系统调用，更有效率。

问题：主从服务器读写分离时读操作失败

先重现一下问题：比如说添加一条新数据，添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务 器上完成的，浏览新数据的操作实在从服务器上完成的，不过由于主从服务器间SQL同步存在延迟，所以当使用last_insert_id在从服务器上查询 的时候，从服务器很可能还没有还没来得及同步到此记录，所以读操作失败。解决思路也不复杂，在代码里加入一个缓存层（可以使用memcached），新添 加的数据都顺手放到缓存层里一份，新数据的读操作也先查询缓存层，这样就不会再有读操作失败的问题了，当然删除或者更新数据的时候也要顺带着处理好缓存数 据，可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录，对于读取列表的记录的情况，则是无效的。

问题：主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离，从服务器上进行读操作，主服务器进行写操作，这样的话，主服务器上仅保留主键，外键，唯一索引等必要的索引即可，以 便保持数据合法性，而对于那些原本用于优化SELECT操作的索引，可以全部删除，如此的话主服务器的写操作效率会提升很多。

作者：老王
原文：http://hi.baidu.com/thinkinginlamp/blog/item/5d72dd5469b1885fd0090633.html

一直在搜索这块有问题，中文搜索的话，不可避免的会遇到搜索 A 的时候会出现 B 结果的情形，这种情况在MYSQL4.0的时候犹为明显，最近在翻代码的时候，翻到walker以前写的一篇文章，依稀记得大概也是05年左右的事情了吧。

05年这家伙写了不少代码，什么BMP识别啥的。如今也都随着服务器的损坏而烟消云散了。借着google的光，把walker这篇以前的文章再COPY回来。为以后也开发可以做个参考，只是它的这个方法太占数据库空间了。呵呵

如今的walker代码是几乎不写了，以wow为主，可怜的人啊。。。。。http://www.walkerlee.net 是他现在的网站。

文章如下：
/**
*   @author   :   walkerlee
*   @copyright   :   www.neatstudio.com   |   www.walkerlee.net
*/

转载请保留以上信息。

关键字：MySQL   全文检索   全文索引   中文分词   二元分词   区位码   相似度

注：本文使用的MySQL版本为：MySQL   4.0.x

在MySQL4中，是已经开始支持全文检索（索引）的了。但是只是对英文支持全文检索。
由于英文在书写上的特殊性，使得分词算法相对中文来说，简单得多。一般来说，我们可以通过单词与单词之间的空格，以及标点符号来完成这个分词过程。
但是就中文来说，就没有那么简单。MySQL无法对中文做出正确的分词，假设有如下英文句子：

"Hello   world!   Hello   PHP! "

通过上面提及的方法，可以很简单的把这个句子分词为：

1   Hello
2   world
3   PHP

我们再来看看中文的句子：

"你好世界，你好PHP！ "

按照英文的算法，分词如下：

1   你好世界
2   你好PHP

显然是不能满足我们的需要的。

所以，首先我们要做的是，把中文的句子转变为MySQL眼中的英文，以便使得它能以英文分词算法去对句子进行正确的分词处理。
先将上面中文句子进行标点过滤处理，得到以下句子：

你好世界   你好PHP

接着再使用中文分词中较简单实现的二元分词算法对句子进行二元分词，得到以下句子：

你好   好世   世界   你好   PHP

因为把标点符号替换为空格，以及PHP本身为英文字母的关系，可以不用进行二元切分，所以得到上面句子。
这个时候，我们来看看处理过后的句子，会发现，就其书写格式上来说，已经符合英文的书写格式，既以空格，标点来对单词形成自然间隔。只是上面句子没有标点，只有空格而已。
到此，我们已经成功的将中文“翻译”为MySQL能理解的“英文”书写格式。

但是，问题还没解决，首先，MySQL中，ft_min_word_len（分词词汇最小长度）这个参数的默认值为4，也就是4个字母以上长度的单词，才会被考虑，小于4个的，将会被忽略。
如果不改变这个长度，按照上面的分词结果，我们将无法通过   你好，世界，PHP等检索到相关的结果，因为分出来的词太短了，不在MySQL的选择范围内。
我们可以通过修改ft_min_word_len的值，将其设置为2来解决上面问题，但是这样做的话，在检索列表中的原本就为英文的短小词汇，如：PHP，MP3，也会被划入检索范围内，这样做的结果是，出现很多无意义的相关结果。

请看以下列表：

[MP3]   the   look
[MP3]   because   of   you

因为他们都同有MP3在标题中，所以会出现上述提到的问题。

回到ft_min_word_len值的问题，我们之所以要修改他，是为了能让MySQL找到我们的二元分词，但是短小的英文又被“无辜”的卷入，我们目 前要解决的问题就是，如何使得MySQL能检索到二个字的中文词汇，又能忽略掉原本的英数？第一个反应是把中文MD5，这样以上分词就将转化为以下结果：

你好   好世   世界   你好   PHP   =>   b94ae3c6d892b29cf48d9bea819b27b9   f5625345be46432fb0fd51340fcf6679   9067de5206278a93823f9c5dc2c737fd   b94ae3c6d892b29cf48d9bea819b27b9   PHP

这样做，首先是使得中文分词的长度超越了默认的2个字，同时消除了中文的歧义性。（MySQL4对中文的处理有问题），搜索“车轮”时候，不再会出现类似“发动机”结果的问题。（车轮的例子只是为了方便理解而做出的假设）

通过上面的做法，已经解决了分词最小长度的问题，顺利的把中文词汇长度升级，从而达到把中文词汇划入检索范围，把较短的英数划出检索范围。
休息一下，然后发现这个MD5后的字符串是否太长了点……比较占用空间，要不，于是想到区位码，4位数的区位码能表示一个GB汉字，一个词有二个汉字组成，转换为区位码后是8个数字。不但能确定惟一性，也就MD5而已减少了长度。下面是转换后的：

你好   好世   世界   你好   PHP   =>   b94ae3c6d892b29cf48d9bea819b27b9   f5625345be46432fb0fd51340fcf6679   9067de5206278a93823f9c5dc2c737fd   b94ae3c6d892b29cf48d9bea819b27b9   PHP   =>   36672635   26354232   42322971   36672635   PHP

呵呵，是不是比MD5的小了很多呢？最后我们把相同的词汇留一个，多余的删除。得到

36672635   26354232   42322971   PHP

于是就完成了   "你好世界，你好PHP！ "   到   "36672635   26354232   42322971   PHP "   的转换。

通过上面方法结合MySQL全文检索语句，我们可以通过给出一个标题例如： "迈克尔·杰克逊   -《危险之旅之布加勒斯特站》 "找出类似以下的相关标题

迈克尔杰克逊   -《迈克尔杰克逊危险布加勒斯特演唱会》
Michael   Jackson   -《迈克尔杰克逊   罗马尼亚   危险演唱会》
迈克尔杰克Michael   Jackson   -《危险之旅》
迈克尔杰克逊   -《迈克尔杰克逊   美国50annive演唱会危险片段》
迈克尔杰克逊   -《迈克尔杰克逊   终极收藏   原版DVD危险演唱会》
迈克尔杰克逊     杰克逊五兄弟   -《The   Jackson   Motown   25   演唱会》
迈克尔杰克逊   -《迈克尔杰克逊BAD日本Yokohama演唱会》
迈克尔杰克逊   -《迈克尔杰克逊日本大阪演唱会》
迈克尔杰克逊   -《迈克尔杰克逊之胜利-达拉丝演唱会》
迈克尔杰克逊   -《迈克尔杰克逊之胜利演唱会   比丽珍   片段》
迈克尔杰克逊   -《迈克尔杰克逊德国危险演唱会之   billie   jean片段》
迈克尔杰克逊   -《Michael   Jackson   -30周年演唱会》
Michael   Jackson   -《迈克尔杰克逊   马尼拉   历史演唱会》
迈克尔杰克逊   -《1993年美国橄榄球中场休息精彩表演》

表结构   article
title     varchar   200         --------       用于存放标题   (显示用)
ft             text           ----         fulltext     用于存放标题分词结果   (检索用)

首先我们在把标题保存到数据库时候，就已经对标题进行分词转区位码，保存到ft字段中，用于相关性的检索。
然后把给出的标题 "迈克尔·杰克逊   -《危险之旅之布加勒斯特站》 "转为 "34853143   31432291   22910104   01042960   29603143   31434923   46034753   47535414   54143435   34355414   54141828   18282851   28513253   32534325   43254456   44565330 "，最后进行全文检索查询：

SELECT   title,   MATCH(   ft   )   AGAINST(   '34853143   31432291   22910104   01042960   29603143   31434923   46034753   47535414   54143435   34355414   54141828   18282851   28513253   32534325   43254456   44565330 '   IN   BOOLEAN   MODE   )   AS   score  
FROM   article  
WHERE   MATCH(   ft   )   AGAINST(   '34853143   31432291   22910104   01042960   29603143   31434923   46034753   47535414   54143435   34355414   54141828   18282851   28513253   32534325   43254456   44565330 '   IN   BOOLEAN   MODE   )  
ORDER   BY   score   DESC  
LIMIT   0,   5

从SQL   Query上来看，进行了两次全文检索，其实不然，MySQL会将其视为一次，所以不比担心。
同时使用了AS   score，这个score是相似度，分值越高，自然越与给出的标题相近。

二点建议：
1.在实际使用中，挑选score大于1的作为检索结果。
2.检索结果会将本身标题也算入其中，根据score排序，为第一条，别忘记过滤哦   ^_^。

站在用户的立场来说，我们给用户提供了更多的相关内容，站在搜索引擎立场上来说，给关键字提供了更多的相关链接，形成了良好的站内互联结构，提高了搜索引擎对网页的评价。

如果各位碰到错误的不合理的地方，恳请指正，共同进步。谢谢！

参考资料：

1.Monkey的二元分词   作者：Monkey   http://www.baidu.com/s?wd=monkey+%B6%FE%D4%AA%B7%D6%B4%CA&cl=3
2.PHP里如何实现汉字转区位码   提供者：haoyoul   http://zhidao.baidu.com/question/5371961.html
3.对dvbbs.php全文搜索的完全分析   作者：fcicq     http://www.phpx.com/happy/viewthread.php?tid=124691

我只贴一种，其余的去看：http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I'm including full CREATE and INSERT statements so you can follow along):

CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);


INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

备份资料：Copy from ---> http://php.mydict.com/ziliao/7/2006_05/SQLYuJuDaoRuDaoChuDaQuan3016_1.html

/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

更多看详细。。。

» 阅读全文