MySQL collation方法收集

收集１：

问题是这样的： 
一张test的表，字符集采用的latin1。 

select to_id from test where to_id='cn象_王'; 
+---------------+ 
| to_id | 
+---------------+ 
| cn陶_陶 | 
| cn象_王 | 
+---------------+ 
2 rows in set (0.00 sec) 

取cn象_王的数据,居然把cn陶_陶的数据也取回来了。 
这显然是不允许的。 

查看它们的编码： 

(root@im_offlog1a)[test]> select hex('cn陶_陶'); 
+----------------+ 
| hex('cn陶_陶') | 
+----------------+ 
| 636ECCD55FCCD5 | 
+----------------+ 
1 row in set (0.00 sec) 

(root@im_offlog1a)[test]> select hex('cn象_王'); 
+----------------+ 
| hex('cn象_王') | 
+----------------+ 
| 636ECFF35FCDF5 | 
+----------------+ 
1 row in set (0.00 sec) 

编码的确是不一样的，但是为什么mysql会认为这两条记录是一样的呢？ 

一开始我们就把问题定位于collation引起的问题。 

show variables查看 
| collation_connection | latin1_swedish_ci 
| collation_database | latin1_swedish_ci 
| collation_server | latin1_swedish_ci 


手工把这些参数修改为latin1_bin，结果居然一样。这下感觉真是奇怪了。 

这里先解释一下mysql collation的命名规则： 
它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 

比如latin1字符集有以下几种校正规则： 

校对规则 含义 
latin1_german1_ci 德国DIN-1 
latin1_swedish_ci 瑞典/芬兰 
latin1_danish_ci 丹麦/挪威 
latin1_german2_ci 德国 DIN-2 
latin1_bin 符合latin1编码的二进制 
latin1_general_ci 多种语言(西欧) 
latin1_general_cs 多种语言(西欧ISO),大小写敏感 
latin1_spanish_ci 现代西班牙 


最后我们将表格重建，手工指定表格级别的collation为latin1_bin。 
这个问题就得到了解决。 


那么问题又来了，为什么我前面手工测试latin1_bin时不生效呢？ 

原来MySQL按照下面的方式选择表字符集和 校对规则： 
如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。 
如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 
否则，采用服务器字符集和服务器校对规则。 

而我们在建表的时候指定了character set，所以它永远是采用对应的默认的校对规则。 

当然我们其实也没必要重建表格，只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin这样转换即可。 

另外建议collation都尽量采用字符集相应的bin类型的校对规则，这样不容易出错。

收集２：

show create table comment_content_1_01;

当当前表结构是下面这样时：

CREATE TABLE `comment_content_1_01` (

  `content_id` int(11) NOT NULL AUTO_INCREMENT,

  `thread` varchar(50) DEFAULT NULL,

  `uname` varchar(100) DEFAULT NULL,

  `nick` varchar(100) DEFAULT NULL,

  `uid` int(11) unsigned DEFAULT NULL,

  `content` text,

  `post_time` datetime DEFAULT NULL,

  `post_ip` int(10) unsigned DEFAULT NULL,

  `status` enum('unaudit','normal','deleted') NOT NULL DEFAULT 'unaudit',

  PRIMARY KEY (`content_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

只修改一列的校验规则可以用：

 ALTER TABLE `comment_content_1_01` CHANGE `thread` `thread` VARCHAR( 50 ) COLLATE latin1_bin DEFAULT NULL;

表变为

CREATE TABLE `comment_content_1_01` (

  `content_id` int(11) NOT NULL AUTO_INCREMENT,

  `thread` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,

  `uname` varchar(100) DEFAULT NULL,

  `nick` varchar(100) DEFAULT NULL,

  `uid` int(11) unsigned DEFAULT NULL,

  `content` text,

  `post_time` datetime DEFAULT NULL,

  `post_ip` int(10) unsigned DEFAULT NULL,

  `status` enum('unaudit','normal','deleted') NOT NULL DEFAULT 'unaudit',

  PRIMARY KEY (`content_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

修改表中所有字符列为指定校验规则可以用：

ALTER TABLE `comment_content_1_01` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

表结构变为：

CREATE TABLE `comment_content_1_01` (

  `content_id` int(11) NOT NULL AUTO_INCREMENT,

  `thread` varchar(50) COLLATE latin1_bin DEFAULT NULL,

  `uname` varchar(100) COLLATE latin1_bin DEFAULT NULL,

  `nick` varchar(100) COLLATE latin1_bin DEFAULT NULL,

  `uid` int(11) unsigned DEFAULT NULL,

  `content` text COLLATE latin1_bin,

  `post_time` datetime DEFAULT NULL,

  `post_ip` int(10) unsigned DEFAULT NULL,

  `status` enum('unaudit','normal','deleted') COLLATE latin1_bin NOT NULL DEFAULT 'unaudit',

  PRIMARY KEY (`content_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

可是当此时执行：

ALTER TABLE `comment_content_1_01` CHANGE `thread` `thread` VARCHAR( 50 )　DEFAULT NULL;

是没有用的，因为不设校验collate,就会按表指定的设定。

可以采用先把表改为没有collate的，

ALTER TABLE `comment_content_1_01` CONVERT TO CHARACTER SET latin1;

然后整个表就都不带collate,之后逐个加入需要collate的列。