DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 ( latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL ); CREATE TABLE t2 ( latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r_general_ci NOT NULL ); ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' CREATE TABLE t2 ( latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL ); ERROR HY000: Unknown collation: 'some_non_existing_col' INSERT INTO t1 (latin1_f) VALUES (_latin1'A'); INSERT INTO t1 (latin1_f) VALUES (_latin1'a'); INSERT INTO t1 (latin1_f) VALUES (_latin1'AD'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ad'); INSERT INTO t1 (latin1_f) VALUES (_latin1'AE'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ae'); INSERT INTO t1 (latin1_f) VALUES (_latin1'AF'); INSERT INTO t1 (latin1_f) VALUES (_latin1'af'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Ä'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ä'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Å'); INSERT INTO t1 (latin1_f) VALUES (_latin1'å'); INSERT INTO t1 (latin1_f) VALUES (_latin1'B'); INSERT INTO t1 (latin1_f) VALUES (_latin1'b'); INSERT INTO t1 (latin1_f) VALUES (_latin1'U'); INSERT INTO t1 (latin1_f) VALUES (_latin1'u'); INSERT INTO t1 (latin1_f) VALUES (_latin1'UE'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ue'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Ü'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ü'); INSERT INTO t1 (latin1_f) VALUES (_latin1'SS'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ss'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ß'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Y'); INSERT INTO t1 (latin1_f) VALUES (_latin1'y'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Z'); INSERT INTO t1 (latin1_f) VALUES (_latin1'z'); SELECT latin1_f FROM t1 ORDER BY latin1_f; latin1_f A a AD ad ae AE AF af b B SS ss u U UE ue Y y ü Ü Z z å Å Ä ä ß SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci; latin1_f A a AD ad ae AE AF af b B SS ss u U UE ue Y y ü Ü Z z å Å Ä ä ß SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_german2_ci; latin1_f A a Å å AD ad Ä ae AE ä af AF b B ß ss SS U u ue UE ü Ü Y y Z z SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_general_ci; latin1_f A a AD ad AE ae af AF Ä ä Å å b B ss SS ß U u UE ue ü Ü Y y Z z SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin; latin1_f A AD AE AF B SS U UE Y Z a ad ae af b ss u ue y z Ä Å Ü ß ä å ü SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci; ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A a AD ad ae AE AF af b B SS ss u U UE ue Y y ü Ü Z z å Å Ä ä ß SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A a Å å AD ad Ä ae AE ä af AF b B ß ss SS U u ue UE ü Ü Y y Z z SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A a AD ad AE ae af AF Ä ä Å å b B ss SS ß U u UE ue ü Ü Y y Z z SELECT latin1_f COLLATE latin1_bin AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A AD AE AF B SS U UE Y Z a ad ae af b ss u ue y z Ä Å Ü ß ä å ü SELECT latin1_f COLLATE koi8r_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f; latin1_f count(*) A 2 AD 2 AE 2 AF 2 B 2 SS 2 U 2 UE 2 Ü 4 Z 2 Å 2 Ä 2 ß 1 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci; latin1_f count(*) A 2 AD 2 AE 2 AF 2 B 2 SS 2 U 2 UE 2 Ü 4 Z 2 Å 2 Ä 2 ß 1 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci; latin1_f count(*) A 4 AD 2 AE 4 AF 2 B 2 SS 3 U 2 UE 4 Y 2 Z 2 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci; latin1_f count(*) A 2 AD 2 AE 2 AF 2 Ä 2 Å 2 B 2 SS 2 ß 1 U 2 UE 2 Ü 2 Y 2 Z 2 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin; latin1_f count(*) A 1 AD 1 AE 1 AF 1 B 1 SS 1 U 1 UE 1 Y 1 Z 1 a 1 ad 1 ae 1 af 1 b 1 ss 1 u 1 ue 1 y 1 z 1 Ä 1 Å 1 Ü 1 ß 1 ä 1 å 1 ü 1 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci; ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' SELECT DISTINCT latin1_f FROM t1; latin1_f A AD AE AF Ä Å B U UE Ü SS ß Z SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1; latin1_f COLLATE latin1_swedish_ci A AD AE AF Ä Å B U UE Ü SS ß Z SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1; latin1_f COLLATE latin1_german2_ci A AD AE AF B U UE SS Y Z SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1; latin1_f COLLATE latin1_general_ci A AD AE AF Ä Å B U UE Ü SS ß Y Z SELECT DISTINCT latin1_f COLLATE latin1_bin FROM t1; latin1_f COLLATE latin1_bin A a AD ad AE ae AF af Ä ä Å å B b U u UE ue Ü ü SS ss ß Y y Z z SELECT DISTINCT latin1_f COLLATE koi8r FROM t1; ERROR HY000: Unknown collation: 'koi8r' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `latin1_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SHOW FIELDS FROM t1; Field Type Null Key Default Extra latin1_f char(32) NO NULL ALTER TABLE t1 CHANGE latin1_f latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `latin1_f` char(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SHOW FIELDS FROM t1; Field Type Null Key Default Extra latin1_f char(32) YES NULL ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_bin; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `latin1_f` char(32) COLLATE latin1_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin SHOW FIELDS FROM t1; Field Type Null Key Default Extra latin1_f char(32) YES NULL SET CHARACTER SET 'latin1'; SHOW VARIABLES LIKE 'character_set_client'; Variable_name Value character_set_client latin1 SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' latin1 latin1_swedish_ci 4 1 explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select charset('a') AS `charset('a')`,collation('a') AS `collation('a')`,coercibility('a') AS `coercibility('a')`,('a' = 'A') AS `'a'='A'` SET CHARACTER SET koi8r; SHOW VARIABLES LIKE 'collation_client'; Variable_name Value SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' latin1 latin1_swedish_ci 4 1 SET CHARACTER SET 'DEFAULT'; ERROR 42000: Unknown character set: 'DEFAULT' DROP TABLE t1; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci); SELECT * FROM t1 WHERE s1 = s2; ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' DROP TABLE t1; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci, s3 CHAR(5) COLLATE latin1_bin); INSERT INTO t1 VALUES ('a','A','A'); SELECT * FROM t1 WHERE s1 = s2; ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' SELECT * FROM t1 WHERE s1 = s3; s1 s2 s3 SELECT * FROM t1 WHERE s2 = s3; s1 s2 s3 a A A DROP TABLE t1; SET NAMES latin1; CREATE TABLE t1 (s1 char(10) COLLATE latin1_german1_ci, s2 char(10) COLLATE latin1_swedish_ci, KEY(s1), KEY(s2)); INSERT INTO t1 VALUES ('a','a'); INSERT INTO t1 VALUES ('b','b'); INSERT INTO t1 VALUES ('c','c'); INSERT INTO t1 VALUES ('d','d'); INSERT INTO t1 VALUES ('e','e'); INSERT INTO t1 VALUES ('f','f'); INSERT INTO t1 VALUES ('g','g'); INSERT INTO t1 VALUES ('h','h'); INSERT INTO t1 VALUES ('i','i'); INSERT INTO t1 VALUES ('j','j'); EXPLAIN SELECT * FROM t1 WHERE s1='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref s1 s1 11 const 1 Using where EXPLAIN SELECT * FROM t1 WHERE s2='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref s2 s2 11 const 1 Using where EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref s1 s1 11 const 1 Using where EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range s1 s1 11 NULL 2 Using where EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range s1 s1 11 NULL 2 Using where EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range s1 s1 11 NULL 1 Using where EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where DROP TABLE t1; create table t1(f1 varchar(10) character set latin2 collate latin2_hungarian_ci, key(f1)); insert into t1 set f1=0x3F3F9DC73F; insert into t1 set f1=0x3F3F1E563F; insert into t1 set f1=0x3F3F; check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; create table t1 (a varchar(2) character set latin7 collate latin7_general_ci,key(a)); insert into t1 set a=0x4c20; insert into t1 set a=0x6c; insert into t1 set a=0x4c98; check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) a create table t1 select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` varchar(1) CHARACTER SET latin5 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate latin5_turkish_ci then 2 else 3 end; case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate latin5_turkish_ci then 2 else 3 end 3 select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) abc