客戶端傳送請求到伺服器，然後再從伺服器接受返回資料，中間經歷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會放棄索引掃描
案例
症狀：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字符集與亂碼

相關文章