字符集與亂碼 · 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.