转:mysql utf8字符设定和collation的大小写敏感问题[数据库]
http://kkito.cn/index.php/blog/getArticle/130
本来都连局域网里面的数据库服务器开发，这次项目就我自己用，结果捣腾了半天数据库还是没有完全搞定，超郁闷。
大致有两个问题。
1，utf8设定
很多地方都碰到，但我每次装的时候都比较注意早就选择了这个，这次ubuntu apt-get install，都没做啥设定问题就来了。中文不能正确显示。这个问题碰到很多人问，有次看到qq群里面讨论说存储用的字符集，我觉得不大靠谱，为啥，数据库信息存在存储设备上，八成是用二进制文件形式处理，难道还有文本？既然是二进制管他啥字符集，只关心二进制数据了。
研究了一下，发觉确实跟存储是没有关系的。
- show variables;
…..
| character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 |
…..
显示了很多character_set，直接用sql改也能改，但似乎只在当前连接生效，改了之后可以改变字符集。
至于具体是怎么样一个过程，官方网站上都有详细说明。
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
- What character set is the statement in when it leaves the client?
The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
- What character set should the server translate a statement to after receiving it?
For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.
- What character set should the server translate to before shipping result sets or error messages back to the client?
The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names。
只列出了三个过程，保证这三个过程就ok。
接下来的问题是如何改成默认设置。
/etc/mysql/my.cnf 这个配置文件里面
。。。。
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set=utf8
[mysql]
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server = utf8
collation-server = utf8_general_ci
character-set-client=utf8
character_set_client=utf8
。。。。
设定好/etc/init.d/mysql restart 重启下服务，再次连接进去show variables就可以了。
2，collation 的大小写敏感问题
有个column设置成unique，如果设定一条记录这个值为”a” ，下一条纪录设定为”A”时居然报错是duplicate了，狂ft。第一次发现有这么搓的事情。
问题集中到这几个属性
| collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci |
几个备选项的意思
utf8_bin: compare strings by the binary value of each character in the string utf8_general_ci: compare strings using general language rules and using case-insensitive comparisons utf8_general_cs: compare strings using general language rules and using case-sensitive comparisons
我还是想修改配置能默认设定成utf8_bin或者case insensitive，但是始终找不到正确的配置，my.cnf里面不知道怎么配置。只知道
collation-server = utf8_general_ci
可以改变collation_database和collation_server的属性确不知道如何改变collation_connection。sql直接改估计可以，但是今天忙的没空试验了，ft，明天继续。
