客户端发送请求到服务器，然后再从服务器接受返回数据，中间经历4次字符转换；
1) 客户端到服务器Charater_set_client –> character_set_connection –> character_set_database
2) 服务器到客户端character_set_database -> character_set_connection –> character_set_result
为避免乱码，应做到两点
1) 传入数据库的字符集与实际表数据字符集一致(默认character_set_database)，即Charater_set_client = character_set_connection = character_set_database
2) 数据库返回字符集与前台程序一致，即character_set_result与前台JAVA或PHP程序编码一致
假设前台程序使用GBK而数据库采用UTF8，须如下设置避免乱码
character_set_client = character_set_connection = utf8
character_set_result = gbk
应该先设置客户端字符集，然后set names/character set更改设置；
Set names等同于
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;
Set character set等同于
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = @@character_set_database;
而mysql/mysqladmin/mysqlimport默认采用latin1，--default-character-set等同于set names
SP默认采用character_set_database字符集，若character_set_database发生改动则需重建SP以保持一致；
If there is no CHARACTER SET attribute, the database character set and collation in effect at routine creation time are used. (The database character set and collation are given by the value of the character_set_database and collation_database system variables.)
If you change the database default character set or collation, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults.
http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html
当SP所用字符集与character_set_connection不一致时，SP会放弃索引扫描
案例 http://stackoverflow.com/questions/16752922/mysql-stored-procedure-is-slower-20-times-than-standard-query
症状：SP比query慢几十倍，没有用到索引；mySQL 5.0.51 on windows server 2008 R2 64 bit.
原版为select * from table1 where (@param1 IS NULL OR col1=@param1) union all
后改进为SELECT * FROM table1 WHERE col1=@param1 AND col2=@param2
SELECT * FROM table1 WHERE col1=‘test‘ AND col2=‘test‘ //Executed in 0.020s
After i called the my sp:
CALL MySp(‘test‘,‘test‘) //Executed in 0.466s
CREATE DEFINER = `myDbName`@`%`
PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
BEGIN
SELECT * FROM table1 WHERE col1=param1 AND col2=param2
END
profile显示SP的99%时间耗费在sending data
解决：After having changed charsets to UTF-8 (especially for the two used), which is used for the connection anyways, keys are taken into account within the stored procedure!
For stored procedure parameter database character set is used by default (see that manual quote), not connection character set. This should explain the difference of execution in SP and outside SP, and the fact that if you set character set explicitly (to UTF-8?) indexes are used.
mysql字符集与乱码
原文：http://blog.itpub.net/15480802/viewspace-1331116/
