字符集与乱码 · PHP笔记

客户端发送请求到服务器，然后再从服务器接受返回数据，中间经历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.