C1G军火库

大家在写sql 语句的时候， 如果是 select .. where 类型的语句， 有注意到条件的前后顺序吗？我今天做个小实验。
比如查询地址里 包含“海口市”及“振兴路” 两个关键字的数据， 一般时候可能会用
select * from dm_addr where addr like ‘%海口市%’ and addr like ‘%振兴路%’ 的形式，但其实这种查询效率不高，原因在于条件的前后顺序。以下是测试结果

mysql> select count(1) from dm_addr where addr like ‘%振兴路%’ and addr like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 96 |
+———-+
1 row in set (0.82 sec)

mysql> select count(1) from dm_addr where addr like ‘%海口市%’ and addr like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 96 |
+———-+
1 row in set (0.91 sec)

我做了很多次查询
where addr like ‘%海口市%’ and addr like ‘%振兴路%’ 总是比 where addr like ‘%振兴路%’ and addr like ‘%海口市%’
慢，原因是查询是先处理第一条件，然后在处理第二个条件，先查询出所有 包含“海口市”的记录，再在这些记录中查询包含”振兴路” 的记录。 对于地址情况， 包含“海口市”的记录可能大于”振兴路” 的记录，我做测试的表有2万条数据， 已经有大概0.1s的差别。如果碰到大数据，这个差别会更大。
按以上的分析结果， 我们写SQL语句的时候， 对于select …where … 类型的语句，应该把查询结果范围小的条件放在前面，查询范围大的条件放在后面，这样会提高效率。

对于OR的情况：
测试如下：

mysql> select count(1) from dm_addr where addr like ‘%海口市%’ or addr like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 39168 |
+———-+
1 row in set (0.84 sec)
mysql> select count(1) from dm_addr where addr like ‘%振兴路%’ or addr like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 39168 |
+———-+
1 row in set (0.95 sec)

OR语句的处理过程是这样， 对于每一条地址， 逐个分析条件，如果符合第一个条件，就不做第二条件的判断，那么我们应该把容易做判断的条件放在前面， 比如查询地址里 包含“海口市”或“振兴路” 个关键字的数据，按地址一般排法，XX市应该在XX路前。所以如果用第一种方式，那就效率高，判断有海口市就可以确认这行地址有效了。 所以在写OR类型的条件时， 应该把容易判断的条件写在前面。

再做一个NOT … OR .. NOT … 测试，查询不包含 海口市 或者不包含 振兴路 的数据
mysql> select count(1) from dm_addr where addr not like ‘%海口市%’ or addr not like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 38752 |
+———-+
1 row in set (0.92 sec)

mysql> select count(1) from dm_addr where addr not like ‘%振兴路%’ or addr not like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 38752 |
+———-+
1 row in set (0.86 sec)

因为每条数据都有海口市， 所以对于第一个查询，判断第一个条件都失败了， 都需要判断第二个。
对于第二个查询， 只要判断第一个条件为真就可以确定这个记录， 不需要判断第一个条件，
所以第二个查询效率高过第一个。

结论：对于select … where … 类型的语句。
对于OR条件， 需要把命中率高的条件放在前面。
对于AND条件，需要把条件限制范围小的条件放在前面。
希望这些实验能提高phper 的程序效率。以上测试是对于mysql 5.0 win 做的。好象记得ORACLE刚好相反， 是先判断最后的条件。

php 里查询汉字的拼音首字母已经有很多参考的代码了。
现在给出在mysql 里实现的， 测试环境是mysql-5.0.27-win32

1、建立拼音首字母资料表

2、建立mysql 函数

3、先测试一下
mysql> select hzcode(’南海龙王’);
+——————–+
| hzcode(’南海龙王’) |
+——————–+
| N |
+——————–+
1 row in set (0.00 sec)

4、建立个测试表

5、测试

mysql> select * from f1;
+——+——-+
| name | pykey |
+——+——-+
| 张三 | NULL |
| 李四 | NULL |
| 王五 | NULL |
| 赵六 | NULL |
| 钱七 | NULL |
+——+——-+
5 rows in set (0.00 sec)

mysql> update f1 set pykey = hzcode(name);
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from f1;
+——+——-+
| name | pykey |
+——+——-+
| 张三 | Z |
| 李四 | L |
| 王五 | W |
| 赵六 | Z |
| 钱七 | Q |
+——+——-+
5 rows in set (0.00 sec)

这样就很方便地在MYSQL里查询汉字的首字母了。 类似地也可以直接在MYSQL得到汉字拼音。 不过需要拼音表，函数写法也不一样。

转自http://www.coolcode.cn/?p=51

用 MySQL 很久了，一直被 MySQL 中没有嵌套查询所困扰，虽然 MySQL 4.1 中支持这个特性，但是我的 PHP 的程序都使用的 MySQL 函数库，而不是 MySQLi 函数库，所以 4.1 的特性用不上。

以前遇到需要嵌套查询的地方，一般转化为两个表的连接查询来解决。可是最近做的一个程序里面，发现需要嵌套查询的地方是对同一个表进行的两次查询，所以没法直接转化为两个表的连接查询。要转化为用 PHP 代替 MySQL 做这种事实在是太麻烦了（以前就干过这种傻事），于是一直想找个简单的替代法。也许是灵感突发，也许是最近天天再翻 MySQL 手册对 MySQL 的查询有了更深入的理解，今天忽然想到可以用表的别名来解决这个问题。

以前一直以为表的别名只是为了能够让 SQL 语句能够简短一些，没有其他用处，今天经过实验才发现，原来给表起不同的别名可以让一个表当多个表用。举个我程序中的例子来说明一下：

我的程序中有一个分类表，为了实现无限多级分类，我在分类表中有一个 parent_id 字段，这个字段保存的是这条记录所代表的分类的父分类 id 号，如果这个字段为 null，则表示顶级分类。现在我需要删除一个分类，这个分类可能有子分类，因此，删除这个分类以后，它的子分类的 parent_id 号就需要改变一下，而不能继续指向这个不存在的分类，因此我希望删除这个分类以后，它的子分类的父分类变为它的父分类。遇到这种情况，以前我就不知道该怎么办好，现在好了，用下面的语句就可以了：

这里需要注意的一点是，锁定表的时候，一定要把要操作的表和表的别名都锁定，否则下面的语句会出错。

当我兴奋的把这个发现告诉 Legend 时，他说他早就这么用了。早知道了也不告诉我，鄙视一下。顺便跟大家共享一下这个“发现”。如果你也早知道了，不要鄙视我！

http://dev.mysql.com/doc/refman/5.1/zh/triggers.html

使用php mysql_insert_id() 函数将会返回错误值

应使用SELECT LAST_INSERT_ID();需紧跟insert语句。

以下是手册描述:

php:

mysql_insert_id() 将 MySQL 内部的 C API 函数 mysql_insert_id() 的返回值转换成 long（PHP 中命名为 int）。如果 AUTO_INCREMENT 的列的类型是 BIGINT，则 mysql_insert_id() 返回的值将不正确。可以在 SQL 查询中用 MySQL 内部的 SQL 函数 LAST_INSERT_ID() 来替代。

mysql> SELECT LAST_INSERT_ID();
        -> 195

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

SELECT concat(year,lpad(month,2,0),lpad(day,2,0)) as mydate,week(concat(year,lpad(month,2,0),lpad(day,2,0)) ,3) as myweek FROM t_status WHERE concat(year,lpad(month,2,0),lpad(day,2,0)) >=20050606 GROUP BY myweek ORDER BY mydate DESC

当db中记录跨年时，使用week group by 应改成yearweek

SELECT concat(year,lpad(month,2,0),lpad(day,2,0)) as mydate,yearweek(concat(year,lpad(month,2,0),lpad(day,2,0)) ,3) as myweek FROM t_status WHERE concat(year,lpad(month,2,0),lpad(day,2,0)) >=20050606 GROUP BY myweek ORDER BY mydate DESC

当我们需要对mysql数据库进行备份或导入时，可使
用mysqldump及mysql这两个命令完成，安装mysql客户
端工具后在安装目录的bin目录下可以找到，如果您没
安装，也可以直接由http://www.west263.com/test/
mysqldump.exe和http://www.west263.com/test/mysql.exe处下载。

１．备份服务器数据：

mysqldump -h服务器IP地址 -u用户名 -p密码 –opt
数据库名>备份数据库名

例如：

mysqldump -h218.89.170.62 -udemo -pdemo –opt demo>bkup.sql

这条命令将服务器218.89.170.62上的demo这个数据库
备份到本地计算机当前目录的bkup.sql这个文件中，
这样，当数据库不小心损坏或数据丢失时，就可以由
bkup.sql这个备份文件恢复了

2.　导入.sql数据到mysql数据库

mysql -h服务器IP地址 -u用户名 -p密码 -f -D 数据
库名 <备份文件名

例如：

mysql -h218.89.170.62 -udemo -pdemo -f -D demo 这条命令会将bkup.sql这个文件中的数据重新恢复到
服务器demo数据库中。

村子Jeanx
http://www.phpx.com/happy/top95999.html

乱码问题：
在MySQL4.1的安装过程中有缺省character的设置在下图中有显示

如果您所在的服务器有没有选择缺省为GB则会使用UFT8就会出现乱码

或者

只要在你连到数据库后，先执行这句
mysql_query(”Set Names ‘uft8′”);
其中 uft8 可改成你需要的编码，e.g. gb2312 等
这个命令也可以用于 mysql 得命令行，从而可以显示正确的文字

连接数据库出现
——————————————————————————————-
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
——————————————————————————————-
由于MySQL 4.1版本开始密码的hash算法改变，所以连接数据库
时可能会出现Client does not support authentication protocol问题。

可以通过一下两种方法解决
其一:
mysql> SET PASSWORD FOR
-> ’some_user’@’some_host’ = OLD_PASSWORD(’newpwd’);

其二:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(’newpwd’)
-> WHERE Host = ’some_host’ AND User = ’some_user’;
mysql> FLUSH PRIVILEGES;

关系数据库设计之时是要遵守一定的规则的。尤其是数据库设计范式 现简单介绍1NF（第一范式），2NF（第二范式），3NF（第三范式）和BCNF，另有第四范式和第五范式留到以后再介绍。 在你设计数据库之时，若能符合这几个范式，你就是数据库设计的高手。

第一范式（1NF）：在关系模式R中的每一个具体关系r中，如果每个属性值 都是不可再分的最小数据单位，则称R是第一范式的关系。例：如职工号，姓名，电话号码组成一个表（一个人可能有一个办公室电话 和一个家里电话号码） 规范成为1NF有三种方法：
一是重复存储职工号和姓名。这样，关键字只能是电话号码。
二是职工号为关键字，电话号码分为单位电话和住宅电话两个属性
三是职工号为关键字，但强制每条记录只能有一个电话号码。
以上三个方法，第一种方法最不可取，按实际情况选取后两种情况。

第二范式（2NF）：如果关系模式R（U，F）中的所有非主属性都完全依赖于任意一个候选关键字，则称关系R 是属于第二范式的。
例：选课关系 SCI（SNO，CNO，GRADE，CREDIT）其中SNO为学号， CNO为课程号，GRADEGE 为成绩，CREDIT 为学分。 由以上条件，关键字为组合关键字（SNO，CNO）
在应用中使用以上关系模式有以下问题：
a.数据冗余，假设同一门课由40个学生选修，学分就 重复40次。
b.更新异常，若调整了某课程的学分，相应的元组CREDIT值都要更新，有可能会出现同一门课学分不同。
c.插入异常，如计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。
d.删除异常，若学生已经结业，从当前数据库删除选修记录。某些门课程新生尚未选修，则此门课程及学分记录无法保存。
原因：非关键字属性CREDIT仅函数依赖于CNO，也就是CREDIT部分依赖组合关键字（SNO，CNO）而不是完全依赖。
解决方法：分成两个关系模式 SC1（SNO，CNO，GRADE），C2（CNO，CREDIT）。新关系包括两个关系模式，它们之间通过SC1中的外关键字CNO相联系，需要时再进行自然联接，恢复了原来的关系

第三范式（3NF）：如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系R是属于第三范式的。
例：如S1（SNO，SNAME，DNO，DNAME，LOCATION） 各属性分别代表学号，
姓名，所在系，系名称，系地址。
关键字SNO决定各个属性。由于是单个关键字，没有部分依赖的问题，肯定是2NF。但这关系肯定有大量的冗余，有关学生所在的几个属性DNO，DNAME，LOCATION将重复存储，插入，删除和修改时也将产生类似以上例的情况。
原因：关系中存在传递依赖造成的。即SNO -> DNO。 而DNO -> SNO却不存在，DNO -> LOCATION, 因此关键辽 SNO 对 LOCATION 函数决定是通过传递依赖 SNO -> LOCATION 实现的。也就是说，SNO不直接决定非主属性LOCATION。
解决目地：每个关系模式中不能留有传递依赖。
解决方法：分为两个关系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）
注意：关系S中不能没有外关键字DNO。否则两个关系之间失去联系。

BCNF：如果关系模式R（U，F）的所有属性（包括主属性和非主属性）都不传递依赖于R的任何候选关键字，那么称关系R是属于BCNF的。或是关系模式R，如果每个决定因素都包含关键字（而不是被关键字所包含），则RCNF的关系模式。
例：配件管理关系模式 WPE（WNO，PNO，ENO，QNT）分别表仓库号，配件号，职工号，数量。有以下条件
a.一个仓库有多个职工。
b.一个职工仅在一个仓库工作。
c.每个仓库里一种型号的配件由专人负责，但一个人可以管理几种配件。
d.同一种型号的配件可以分放在几个仓库中。
分析：由以上得 PNO 不能确定QNT，由组合属性（WNO，PNO）来决定，存在函数依赖（WNO，PNO） -> ENO。由于每个仓库里的一种配件由专人负责，而一个人可以管理几种配件，所以有组合属性（WNO，PNO）才能确定负责人，有（WNO，PNO）-> ENO。因为 一个职工仅在一个仓库工作，有ENO -> WNO。由于每个仓库里的一种配件由专人负责，而一个职工仅在一个仓库工作，有 （ENO，PNO）-> QNT。
找一下候选关键字，因为（WNO，PNO） -> QNT，（WNO，PNO）-> ENO ，因此 （WNO，PNO）可以决定整个元组，是一个候选关键字。根据ENO->WNO，（ENO，PNO）->QNT，故（ENO，PNO）也能决定整个元组，为另一个候选关键字。属性ENO，WNO，PNO 均为主属性，只有一个非主属性QNT。它对任何一个候选关键字都是完全函数依赖的，并且是直接依赖，所以该关系模式是3NF。
分析一下主属性。因为ENO->WNO，主属性ENO是WNO的决定因素，但是它本身不是关键字，只是组合关键字的一部分。这就造成主属性WNO对另外一个候选关键字（ENO，PNO）的部 分依赖，因为（ENO，PNO）-> ENO但反过来不成立，而P->WNO，故（ENO，PNO）-> WNO 也是传递依赖。
虽然没有非主属性对候选关键辽的传递依赖，但存在主属性对候选关键字的传递依赖，同样也会带来麻烦。如一个新职工分配到仓库工作，但暂时处于实习阶段，没有独立负责对某些配件的管理任务。由于缺少关键字的一部分PNO而无法插入到该关系中去。又如某个人改成不管配件了去负责安全，则在删除配件的同时该职工也会被删除。
解决办法：分成管理EP（ENO，PNO，QNT），关键字是（ENO，PNO）工作EW（ENO，WNO）其关键字是ENO
缺点：分解后函数依赖的保持性较差。如此例中，由于分解,函数依赖（WNO，PNO）-> ENO 丢失了, 因而对原来的语义有所破坏。没有体现出每个仓库里一种部件由专人负责。有可能出现 一部件由两个人或两个以上的人来同时管理。因此，分解之后的关系模式降低了部分完整性约束。

一个关系分解成多个关系，要使得分解有意义，起码的要求是分解后不丢失原来的信息。这些信息不仅包括数据本身，而且包括由函数依赖所表示的数据之间的相互制约。进行分解的目标是达到更高一级的规范化程度，但是分解的同时必须考虑两个问题：无损联接性和保持函数依赖。有时往往不可能做到既有无损联接性，又完全保持函数依赖。需要根据需要进行权衡。

1NF直到BCNF的四种范式之间有如下关系：
BCNF包含了3NF包含2NF包含1NF

小结：
目地：规范化目的是使结构更合理，消除存储异常，使数据冗余尽量小，便于插入、删除和更新
原则：遵从概念单一化 “一事一地”原则，即一个关系模式描述一个实体或实体间的一种联系。规范的实质就是概念的单一化。
方法：将关系模式投影分解成两个或两个以上的关系模式。
要求：分解后的关系模式集合应当与原关系模式”等价”，即经过自然联接可以恢复原关系而不丢失信息，并保持属性间合理的联系。

注意：一个关系模式结这分解可以得到不同关系模式集合，也就是说分解方法不是唯一的。最小冗余的要求必须以分解后的数据库能够表达原来数据库所有信息为前提来实现。其根本目标是节省存储空间，避免数据不一致性，提高对关系的操作效率，同时满足应用需求。实际上，并不一定要求全部模式都达到BCNF不可。有时故意保留部分冗余可能更方便数据查询。尤其对于那些更新频度不高，查询频度极高的数据库系统更是如此。

在关系数据库中，除了函数依赖之外还有多值依赖，联接依赖的问题，从而提出了第四范式，第五范式等更高一级的规范化要求。在此，以后再谈。

字符集支持
MySQL 4.1版本改进了对字符集处理的支持。这里描述的特性是MySQL 4.1.1 里已经
实现的。(MySQL 4.1.0里有一些，不过不包含这里的全部特性，并且有的实现也是不同的)

本章讨论下面的主题：

·什么是字符集和 collations?
·The multiple-level default system
·MySQL 4.1 里新的语法
·Affected functions and operations
·Unicode 支持
·每种特殊字符集和 collation 的含义

现在在 MyISAM, MEMORY (HEAP), 和 (从 MySQL 4.1.2开始) InnoDB 等存储引擎里包含
字符集支持. ISAM 存储引擎不包含字符集支持; 也没有计划加入, 因为 ISAM 已经被
淘汰了

10.1 字符集和Collations的一般介绍

一个character set (字符集)是一组符号和编码，而一个 collation 是在一个字符集里
比较字符的一套规则，让我们通过一个虚构的字符集例子来说明区别。

假设我们有个四个字母的字母表：`A’, `B’, `a’, `b’.我们给每个字母一个编号：
`A’ = 0, `B’ = 1, `a’ = 2, `b’ = 3. 字母`A’ 是一个符号，而数字0是 `A’ 的
encoding(编码)，而这四个字母和他们的编码合起来就是一个字符集(character set)。

现在，假设我们要比较两个字符串的值，`A’ 和`B’，最简单的方法是看编码，`A’ 是 0
而 `B’是 1. 因为0比1小，我们就说`A’ 比 `B’ 小。现在，我们就算已经对我们的字符
集使用了一个collation，collation 是一组规则(在这个例子里只有一条规则)：
"比较编码".我们把所有可能的 collation 中最简单的这种叫做binary collation

但是如果我们想让大写字母和小写字母一样怎么办？那么我们就得有两条规则：
（1）把小写字母`a’ 和 `b’ 看作跟 `A’ 和 `B’相等；
（2）然后比较编码。
我们称这是一个case-insensitive collation（不区分大小写的 collation）.
这比binary collation 稍微复杂了一点。

在实际生活中，大多数字符集都包含很多字符：不是仅仅`A’和`B’ 而是整个字母表，
有时是多个字母表或者东方书写系统里几千的字符，和很多专有符号和标点符。
并且在实际生活中，大多数的collations 有很多规则：除了不区分大小写外还有不区分
重音（重音“accent” 是像在德语里字符附加的重音符那样的）和多字符映射。

MySQL 4.1 可以为你做以下事：

·使用各种字符集存储字符串
·使用各种collation比较字符串。
·在同一台服务器上或者同一个数据库甚至同一个表中使用不同的字符集和collation混合
·允许在任何级别上指明字符集和collation

在这些方面，MySQL 4.1 不只远远比MySQL 4.0复杂，也比其他DBMS先进很多。不过要想
有效的使用这些新特性，你需要学习哪些字符集和collation是可用的，怎样把他们改成
默认，还有各种字符串运算符如何操作他们。

10.2 MySQL 里的字符集和Collations

MySQL 服务器可支持多个字符集。要列出可用的字符集，使用 SHOW CHARACTER SET 语句:

输出实际上包含另一列，这里为了让例子在页面上显示更合适，没显示出来

任一给出的字符集至少包含一个collation. 它可能包含多个 collations.

要列出一个字符集的 collations , 使用 SHOW COLLATION 语句. 例如, 要看latin1
(“ISO-8859-1 West European”)的collations, 使用这个语句来找到哪些名字以latin1
开头的collation

latin1 collations 有下列含义:

Collation 含义

Collations 有这些一般特性:

·两个不同字符集没法拥有同一个collation.
·每个字符集有一个默认 collation. 例如, latin1 的默认 collation 是
latin1_swedish_ci.
·collation 的命名有个约定: 他们由所关联的字符集的名字打头，他们通常包含一个
语言名, 并以 _ci (case insensitive大小写不敏感),
或者 _cs (case sensitive大小写敏感), 或者 _bin (binary二进制).

10.3 决定默认字符集和 Collation

有四个级别上的默认字符集和collation设置： 服务器，数据库，表和连接。下面的描述
可能看起来复杂，不过实践中得出多级默认设置可以带来自然而然的结果。

10.3.1 服务器级字符集和 Collation

MySQL服务器有一个服务器级别的字符集和 collation, 不能为空。

MySQL 这样决定服务器级的字符集和collation
·当服务器开始按照有效选项设置
·运行期间按照变量

在服务器级别，决定是很简单的，依靠你执行mysqld时使用的选项来决定服务器字符集
和collation。你可以使用–default-character-set 来指定字符集，并且和这个一起
还可以为collation加上–default-collation 。如果你不指定字符集，就相当于说
–default-character-set=latin1。如果你只指定了字符集(例如,latin1)但是没有指定
collation，就相当于
–default-charset=latin1 –default-collation=latin1_swedish_ci
因为 latin1_swedish_ci是latin1字符集的默认collation, 因此下面三个命令都具有
同样效果：

有个改变这个设置的方法是重新编译，如果你想编译源码来改变默认的服务器字符集和
collation，在configure使加上参数–with-charset 和 –with-collation ，例如：

或者:

mysqld 和configure 都会核实字符集/collation的结合是否有效，如果无效，这两个
程序都会报错并中止。

现行服务器字符集和collation 是和character_set_server 和 collation_server
这两个系统变量的值一样，这些变量可以在运行时更改

10.3.2 数据库字符集和 Collation

每个数据库都有一个数据库字符集和数据库collation,并且不能为空，create DATABASE
和 alter DATABASE 语句有专门指明数据库字符集和collation的可选子句:

例子:

MySQL可以这样选择数据库字符集和数据库collation:

·如果 CHARACTER SET X 和 COLLATE Y 被指定了, 那么字符集是 X collation 是 Y.
·如果 CHARACTER SET X 被指定，但是没有指定 COLLATE, 那么字符集是 X collation
是默认collation.
·否则, 就用服务器字符集和服务器 collation.

MySQL 的 create DATABASE … DEFAULT CHARACTER SET … 语法类似于标准 SQL
的 create SCHEMA … CHARACTER SET … 语法. 因为这样, 就可能在同一个MySQL
服务器上创建具有不同字符集和collation的数据库。

如果在建表的语句里没有指定表的字符集和collation，那么数据库字符集和 collation
就作为表的字符集和collation的默认值. 它们没有别的作用。

默认数据库的字符集和 collation是和 character_set_database 以及
collation_database 这两个系统变量的值一样。 当默认数据库更改时服务程序会设置
这些变量的值。如果没有默认数据库, 变量的值会和配套的服务器级系统变量
character_set_server 以及 collation_server的值一致.

10.3.3 表字符集和 Collation

每个表有一个表字符集以及一个表collation，不能为空。create TABLE 和 alter TABLE
语句有可选子句指定表字符集和collation。

例子:

MySQL 通过下面的方法选择表字符集和collation:

·如果 CHARACTER SET X 和 COLLATE Y 都被指定了, 那么字符集就是 X collation 是Y
·如果只指定了CHARACTER SET X 而没有指定 COLLATE, 那么字符集为 X 并配默认的
collation.
·否则就使用数据库字符集和 collation.

表字符集和 collation 用来在没有指定个别列字符集和列collation的时候做为它们
的默认值。表字符集和 collation 是MySQL 的扩展;在标准SQL里没有这种东西

10.3.4 列字符集和 Collation

每个“character” 列(是指列属性为CHAR, VARCHAR, 或 TEXT的)都有一个列字符集
和一个列collation，不能为空。列定义语句有可选子句指定列字符集和collation：

MySQL 这样选择列字符集和collation:

·如果 CHARACTER SET X 和 COLLATE Y 都被指定了, 那么字符集就是 X collation 就是 Y.
·如果指定了 CHARACTER SET X 但没有指定 COLLATE, 那么字符集是 X 并配默认的collation.
·否则，就用表字符集和 collation.
CHARACTER SET 和 COLLATE 子句是标准SQL.

10.3.5 字符集和 Collation 分配的例子

下面的例子显示了 MySQL 怎样决定默认的字符集和collation的值：

例子1：表＋列定义

这里我们有一个用latin1的字符集和latin1_german1_ci collation的列。
定义非常明显，所以很简单。注意把一个latin1 的列存到一个latin2的表里不会有问题

例子2：表＋列定义

这次我们有一列是latin1字符集加默认的collation。现在，虽然它看上去很自然，
但是默认的collation却不是从表级继承而来。事实上，因为latin1的默认collation
始终是latin1_swedish_ci，所以c1列的collation是latin1_swedish_ci （而不是
latin1_danish_ci).

例子3：表＋列定义

我们有一个默认字符集和默认collation的列。在这个环境下，MySQL向上到表级决定
列字符集和collation。所以，c1的列字符集是latin1，它的collation是
latin1_danish_ci

例子4：数据库＋表＋列定义

我们创建了一个没有指定列字符集和collation的列。我们也没有指定表级字符集和
collation。在这个条件下，MySQL向上到数据库级决定。(数据库的设置变为表的设置，
之后成为列的设置），所以c1的列字符集是latin2，collation是latin2_czech_ci

10.3.6 连接的字符集和Collations

一些字符集和collation和用户对服务器的作用结合。有些在前面已经提及了：

·服务器的字符集和collation和 character_set_server 及 collation_server 变量
的值一样
·默认数据库的字符集和collation和 character_set_database 及 collation_database
变量的值一样.

附加的字符集和collation 变量被引入用来处理服务器和客户端之间连接得通信。每个
客户端都有连接相关的字符集和collation变量。

想想"连接"是什么：是你连到服务器时作的事情。客户端通过这条连接发送SQL语句，
比如查询，服务器则通过这条连接给客户端送回回应，比如查询结果结果集合，这导致
了客户端处理字符集和collation的一些问题，它们每个都可以按照系统变量来回答：

·当查询离开客户端的时候应该是什么字符集的？服务器用character_set_client
这个变量来作为客户端发送查询所用的字符集
·服务器端在接收到了查询以后应该把它翻译到社么字符集里？对于这个，服务程序
用的是character_set_connection 和 collation_connection 这两个变量。
它把客户端送来的查询从character_set_client 转换成character_set_connection
（除了latin1或者utf8 的字符串）。collation_connection 对于比较字符串非常
重要，对于列值比较字符串是没有关系的，因为列拥有高优先级
·当服务程序要送回结果集合或者错误信息给客户端时应该用什么字符集？
character_set_results 变量指示了这个值，这包括了列值，或者列名等结果数据。

你可以调整这些变量的值，或者就使用默认的（那样就可以省略这节了）

有两个语句影响连接字符集设置：
SET NAMES ‘charset_name’
SET CHARACTER SET charset_name

SET NAMES 指出客户端送出的SQL语句里是什么。因此，SET NAMES ‘cp1251′ 就告诉服务
程序"下面将要从这个客户端送来的信息将是使用’cp1251′这个字符集。这也指定了
服务程序送回的结果所用的字符集，（例如如果你用了一个select语句它会指出列值
拥有的字符集）

SET NAMES ‘x’ 语句相当于下面三个语句：

把character_set_connection 设置成x也会把collation_connection 设置成默认
collation x

SET CHARACTER SET 是类似的，不过是把连接字符集和collation设置成那些默认数据库。
SET CHARACTER SET x 语句相当于这三个语句：

当一个客户连接，它向服务程序发送它想使用的字符集的名字，服务程序把
character_set_client, character_set_results, 和 character_set_connection
这些变量设置成那个字符集（事实上，服务程序使用字符集执行了SET NAMES 操作）

如果你不想用默认字符集，使用 Mysql 客户端程序不需要每次启动时执行SET NAMES 。
你可以在mysql 执行语句行加上–default-character-set 这个选项，或者在你的选项
文件里加上。比如，下面的选项文件设置使你每次执行mysql程序时把默认字符集变量
改成 koi8r：

例如：假设column1定义是 CHAR(5) CHARACTER SET latin2。如果你不用SET NAMES
或者 SET CHARACTER SET，那么对于你的 select column1 FROM t 请求，服务程序
会把column1 的所有值用连接建立时客户端指定的字符集来回送。另一方面，如果你
用了 SET NAMES ‘latin1′ or SET CHARACTER SET latin1 ，那么在送回结果之前，
服务程序会把 latin2 的值转成latin1，如果里面有两种字符集里都没有的字符，
转化会有损耗。

如果你不希望服务程序作任何转换，就把character_set_results 设置成 NULL

10.3.7. 字符串文字字符集和collation

每个字符串文字都有自己的字符集和collation，不能为空

一个字符串文字可能有一个可选字符集introducer和COLLATION子句：

例如：

对于简单语句 select ’string’，字符串的字符集和collation是由两个系统变量
character_set_connection 和 collation_connection 定义的。

_charset_name 表达式正式情况下被叫做 introducer .它告诉分析器"下面的字符串
是使用 X 字符集的。"因为这在以前造成很多人的困扰，我们强调一下introducer
并不作任何转换，严格来讲并不改变字符串的值，只是一个符号。introducer 在
标准16进制文字前和数字16进制记法前都是合法的（x’literal’ 和 0xnnnn），
在？前面也是合法的（当在程序设计语言接口里使用预备语句时作参数替换）

例如：

MySQL 这样决定一个文字的字符集和collation：

·如果 _X 和 COLLATE Y 都被指定了，那么字符集就是 X collation 是 Y
·如果 指定了 _X 而没有指定 COLLATE ，那么字符集是 X collation 是 X 的默认
collation
·否则，由系统变量 character_set_connection 和 collation_connection 决定字符集
和collation

例如：
·一个字符集是 latin1 而collation是 latin1_german1_ci 的字符串：

·一个字符集是 latin1 以及其配套默认collation的（latin1_swedish_ci）字符串：

·一个连接默认字符集和collation的字符串：

字符集 introducer 和 COLLATE 子句是符合标准 SQL 规则的工具

10.3.8. 在 SQL 语句里使用 COLLATE

通过 COLLATE 子句，你可以在比较时覆盖替换掉任何默认collation， COLLATE 可以用
在SQL 语句的很多部分里，这里是一些例子：

·在 ORDER BY 里:

·在 AS 里：

·在GROUP BY里 :

·在集合函数里：

·在DISTINCT里

·在where 里：

·在HAVING里：

User Comments
Posted by [name withheld] on January 14 2005 2:33pm

在不同的列/表里：

在collation 之间比较列的时候能够避免出错信息。

10.3.9. COLLATE 子句优先级

COLLATE子句具有高优先级（比||高），所以下面两个表达式是相同的：

10.3.10. BINARY 运算

BINARY 运算是COLLATE 子句的速记法，BINARY ‘x’ 和 ‘x’ COLLATE y 是相同的，
y 是字符集 ‘x’ 的二元collation 的名字。每个字符集都有二元 collation。例如，
latin1 字符集的 collation 是latin1_bin，所以如果列 a 是latin1 字符集，下面
两个语句有同样效果：

10.3.11. 一些决定collation 比较棘手的情况

在绝大多数查询里，MySQL 用什么collation来进行比较操作都是很显而易见的，例如，
在下面的情况里，很显然collation 应该是"列 x 的列 collation":

但是，当卷入了多操作数时，就很难搞了，例如：

这个查询应该使用列 x 的collation 呢，还是使用字符串’Y’ 的？

标准SQL 使用被叫做“coercibility” 的规则来解决这个问题。本质就是：因为
x 和 ‘Y’ 都有collation ,优先使用谁的collation呢？这很复杂，不过下面的规则能
应付大多数情况：

·一个COLLATE 子句的 coercibility 是0 （也就是根本不coercible）

·两个具有不同collation 的字符串连结的 coercibility 是1

·一个列的 collation 的 coercibility 是 2

·一个文字型的collation 的 coercibility 是3。

那些规则这样解决含混：

·使用具有最低 coercibility 值的collation

·如果两边具有相同的 coercibility， 如果两个collation 不同那就是错误。

例如：

COERCIBILITY() 函数可以用来判断一个字符串表达式的coercibility：

User Comments
Posted by Thierry Danard on November 5 2004 10:34pm

对于数据库引擎来说显而易见的排序并不是总那么显而易见（version 4.1).

一个没有带类似于"select concat(mycolumn, ‘%’) from mytable "这样的排序指令
的查询在"mycolumn" 和"%"的字符集不相同的情况下不会工作。

在我这里，整个数据库使用 UTF-8, 默认情况下, ‘%’ 假设是 latin1,
causing an error to be triggered。

10.3.12. Collations Must Be for the Right Character Set

记得说过每个字符集都有一个或者多个collation，每个collation只和一个字符集关联。
因此，下面的语句会导致错误，因为 latin2_bin 这个collation 和 latin1 这个字符集
不配套。

在某些情况下，在 MySQL 4.1 前工作的表达式会在MySQL 4.1以后的版本失败，
如果你在帐号里没有字符集和collation的话。例如，在 MySQL 4.1 前，这个语句
会这样工作：

升级到MySQL 4.1 以后，语句失效：

发生这个的原因是username 使用utf8存储（参看10.6节)，因此， USER() 函数
和文字型字符串’@'具有不同的字符集（当然也是不同collation)：

解决的一个方法是告诉MySQL把文字型字符串翻译成utf8：

另一个方法是把连接的字符集和collation改成utf8，你可以使用SET NAMES ‘utf8′
或者直接设置两个系统变量character_set_connection 和 collation_connection
的值来达到这个目的。

10.3.13. Collation 的效果的一个例子

假设表 T 里的列 X 具有这些 latin1 的列值：

并且假设这些列值可以用下列语句找回：

在这张表中列出了不同collation 的结果值的结果排序

这张表显示了如果我们在一个 ORDER BY 子句里使用不同collation 会有什么样的效果
的例子，导致这种不同排序结果的字符是上面有两个点的 U，在德语里叫做U-曲音，
不过我们叫做U-分音符

·第一列显示了使用瑞典/芬兰 collation 规则的 select 的结果，U-分音符
通过Y归类

·第二列显示了使用德语DIN-1 规则的select 语句的结果，U-分音符通过U归类

·第三列显示了使用德语DIN-2 规则的select 语句的结果，U-分音符通过UE归类

三种不同的collation ，三种不同的结果，这是MySQL 在这里的处理。通过使用合适的
collation，你可以选择你想要的排序次序。