å 转ä¸å«äººçå¦ä¹ æç« å个å¼åååºç¡
http://logzgh.itpub.net/post/3185/467401
å¨ä»¥åç¨oracleçæ¶åï¼å¾å°å ³äºå®çcollationæ¹æ³ï¼ä½æ¯å¨mysqlä¸ï¼è¿ç¹ä¸å 注æçè¯ï¼å´æå¯è½ä¼åºç°é®é¢ã
é®é¢æ¯è¿æ ·çï¼
ä¸å¼ testç表ï¼å符ééç¨çlatin1ã
select to_id from test where to_id='cn象_ç';
+---------------+
| to_id |
+---------------+
| cné¶_é¶ |
| cn象_ç |
+---------------+
2 rows in set (0.00 sec)
åcn象_ççæ°æ®,å± ç¶æcné¶_é¶çæ°æ®ä¹ååæ¥äºã
è¿æ¾ç¶æ¯ä¸å 许çã
æ¥çå®ä»¬çç¼ç ï¼
([email protected]_offlog1a)[test]> select hex('cné¶_é¶');
+----------------+
| hex('cné¶_é¶') |
+----------------+
| 636ECCD55FCCD5 |
+----------------+
1 row in set (0.00 sec)
([email protected]_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ç±»åçæ ¡å¯¹è§åï¼è¿æ ·ä¸å®¹æåºéã
å说说æèªå·±çä½ä¼
è§å¾ character set latin1 collate latin1_bin å°±æ¯èçç VARCHAR BINARY çæ¹è¿ï¼åªæ¯æ°ççå ç¨ character set å®å符éï¼åç¨æ¤å符éååå _bin å®æ ¡å¯¹è§å为äºè¿å¶çï¼ä»èç¡®ä¿ä¸ææ¥è¯¢æ£ç¡®ã
åæµè¯äºä¸ä¸ï¼ææ¤å段å±æ§æ¹ä¸ºä¸å¸¦ BINARY ç
ALTER TABLE `comment_content_1_01` CHANGE `thread` `thread` VARCHAR( 50 ) DEFAULT NULL
ç¶ååç表ç»æç¡®å®åæ  `thread` varchar(50) default NULL, å³ä¸å¸¦ character set latin1 collate latin1_bin äºï¼å¯è§character set latin1 collate latin1_bin å°±æ¯èçç VARCHAR BINARY çæ¹è¿ã
æ¤å¤è¿è¯»å°æ´æ¹ä¾¿çåæ³ï¼ä¸ç¨é个æ¹å段å±æ§ï¼èåªè¦è¡¨æ ¼çº§å«çcollation为latin1_binå°±è¡äºã
æµè¯ï¼
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;
Â
å³ä¾¿åæ¥æ²¡å®åå段ç collateï¼ç°å¨ä¹å ¨é½æ¯ collate latin1_bin äºã