1
DROP TABLE IF EXISTS t1;
2
DROP TABLE IF EXISTS t2;
4
latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL
7
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r_general_ci NOT NULL
9
ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1'
11
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL
13
ERROR HY000: Unknown collation: 'some_non_existing_col'
14
INSERT INTO t1 (latin1_f) VALUES (_latin1'A');
15
INSERT INTO t1 (latin1_f) VALUES (_latin1'a');
16
INSERT INTO t1 (latin1_f) VALUES (_latin1'AD');
17
INSERT INTO t1 (latin1_f) VALUES (_latin1'ad');
18
INSERT INTO t1 (latin1_f) VALUES (_latin1'AE');
19
INSERT INTO t1 (latin1_f) VALUES (_latin1'ae');
20
INSERT INTO t1 (latin1_f) VALUES (_latin1'AF');
21
INSERT INTO t1 (latin1_f) VALUES (_latin1'af');
22
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
23
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
24
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
25
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
26
INSERT INTO t1 (latin1_f) VALUES (_latin1'B');
27
INSERT INTO t1 (latin1_f) VALUES (_latin1'b');
28
INSERT INTO t1 (latin1_f) VALUES (_latin1'U');
29
INSERT INTO t1 (latin1_f) VALUES (_latin1'u');
30
INSERT INTO t1 (latin1_f) VALUES (_latin1'UE');
31
INSERT INTO t1 (latin1_f) VALUES (_latin1'ue');
32
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
33
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
34
INSERT INTO t1 (latin1_f) VALUES (_latin1'SS');
35
INSERT INTO t1 (latin1_f) VALUES (_latin1'ss');
36
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
37
INSERT INTO t1 (latin1_f) VALUES (_latin1'Y');
38
INSERT INTO t1 (latin1_f) VALUES (_latin1'y');
39
INSERT INTO t1 (latin1_f) VALUES (_latin1'Z');
40
INSERT INTO t1 (latin1_f) VALUES (_latin1'z');
41
SELECT latin1_f FROM t1 ORDER BY latin1_f;
70
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci;
99
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_german2_ci;
128
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_general_ci;
157
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin;
186
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci;
187
ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1'
188
SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
217
SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
246
SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
275
SELECT latin1_f COLLATE latin1_bin AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
304
SELECT latin1_f COLLATE koi8r_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
305
ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1'
306
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f;
321
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci;
336
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci;
348
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci;
364
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin;
393
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci;
394
ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1'
395
SELECT DISTINCT latin1_f FROM t1;
410
SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1;
411
latin1_f COLLATE latin1_swedish_ci
425
SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1;
426
latin1_f COLLATE latin1_german2_ci
437
SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1;
438
latin1_f COLLATE latin1_general_ci
453
SELECT DISTINCT latin1_f COLLATE latin1_bin FROM t1;
454
latin1_f COLLATE latin1_bin
482
SELECT DISTINCT latin1_f COLLATE koi8r FROM t1;
483
ERROR HY000: Unknown collation: 'koi8r'
484
SHOW CREATE TABLE t1;
486
t1 CREATE TABLE "t1" (
487
"latin1_f" char(32) NOT NULL
488
) ENGINE=MyISAM DEFAULT CHARSET=latin1
490
Field Type Null Key Default Extra
491
latin1_f char(32) NO NULL
492
ALTER TABLE t1 CHANGE latin1_f
493
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin;
494
SHOW CREATE TABLE t1;
496
t1 CREATE TABLE "t1" (
497
"latin1_f" char(32) CHARACTER SET latin1 COLLATE latin1_bin
498
) ENGINE=MyISAM DEFAULT CHARSET=latin1
500
Field Type Null Key Default Extra
501
latin1_f char(32) YES NULL
502
ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_bin;
503
SHOW CREATE TABLE t1;
505
t1 CREATE TABLE "t1" (
506
"latin1_f" char(32) COLLATE latin1_bin
507
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
509
Field Type Null Key Default Extra
510
latin1_f char(32) YES NULL
511
SET CHARACTER SET 'latin1';
512
SHOW VARIABLES LIKE 'character_set_client';
514
character_set_client latin1
515
SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
516
charset('a') collation('a') coercibility('a') 'a'='A'
517
latin1 latin1_swedish_ci 4 1
518
explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
519
id select_type table type possible_keys key key_len ref rows filtered Extra
520
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
522
Note 1003 select charset('a') AS "charset('a')",collation('a') AS "collation('a')",coercibility('a') AS "coercibility('a')",('a' = 'A') AS "'a'='A'"
523
SET CHARACTER SET koi8r;
524
SHOW VARIABLES LIKE 'collation_client';
526
SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
527
charset('a') collation('a') coercibility('a') 'a'='A'
528
latin1 latin1_swedish_ci 4 1
529
SET CHARACTER SET 'DEFAULT';
530
ERROR 42000: Unknown character set: 'DEFAULT'
533
(s1 CHAR(5) COLLATE latin1_german1_ci,
534
s2 CHAR(5) COLLATE latin1_swedish_ci);
535
SELECT * FROM t1 WHERE s1 = s2;
536
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
539
(s1 CHAR(5) COLLATE latin1_german1_ci,
540
s2 CHAR(5) COLLATE latin1_swedish_ci,
541
s3 CHAR(5) COLLATE latin1_bin);
542
INSERT INTO t1 VALUES ('a','A','A');
543
SELECT * FROM t1 WHERE s1 = s2;
544
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
545
SELECT * FROM t1 WHERE s1 = s3;
547
SELECT * FROM t1 WHERE s2 = s3;
553
(s1 char(10) COLLATE latin1_german1_ci,
554
s2 char(10) COLLATE latin1_swedish_ci,
557
INSERT INTO t1 VALUES ('a','a');
558
INSERT INTO t1 VALUES ('b','b');
559
INSERT INTO t1 VALUES ('c','c');
560
INSERT INTO t1 VALUES ('d','d');
561
INSERT INTO t1 VALUES ('e','e');
562
INSERT INTO t1 VALUES ('f','f');
563
INSERT INTO t1 VALUES ('g','g');
564
INSERT INTO t1 VALUES ('h','h');
565
INSERT INTO t1 VALUES ('i','i');
566
INSERT INTO t1 VALUES ('j','j');
567
EXPLAIN SELECT * FROM t1 WHERE s1='a';
568
id select_type table type possible_keys key key_len ref rows Extra
569
1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition
570
EXPLAIN SELECT * FROM t1 WHERE s2='a';
571
id select_type table type possible_keys key key_len ref rows Extra
572
1 SIMPLE t1 ref s2 s2 11 const 1 Using index condition
573
EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
574
id select_type table type possible_keys key key_len ref rows Extra
575
1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition
576
EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;
577
id select_type table type possible_keys key key_len ref rows Extra
578
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
579
EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
580
id select_type table type possible_keys key key_len ref rows Extra
581
1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition; Using MRR
582
EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
583
id select_type table type possible_keys key key_len ref rows Extra
584
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
585
EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci);
586
id select_type table type possible_keys key key_len ref rows Extra
587
1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition; Using MRR
588
EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci);
589
id select_type table type possible_keys key key_len ref rows Extra
590
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
591
EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
592
id select_type table type possible_keys key key_len ref rows Extra
593
1 SIMPLE t1 range s1 s1 11 NULL 1 Using index condition; Using MRR
594
EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;
595
id select_type table type possible_keys key key_len ref rows Extra
596
1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where
598
create table t1(f1 varchar(10) character set latin2 collate latin2_hungarian_ci, key(f1));
599
insert into t1 set f1=0x3F3F9DC73F;
600
insert into t1 set f1=0x3F3F1E563F;
601
insert into t1 set f1=0x3F3F;
602
check table t1 extended;
603
Table Op Msg_type Msg_text
604
test.t1 check status OK
606
create table t1 (a varchar(2) character set latin7 collate latin7_general_ci,key(a));
607
insert into t1 set a=0x4c20;
608
insert into t1 set a=0x6c;
609
insert into t1 set a=0x4c98;
610
check table t1 extended;
611
Table Op Msg_type Msg_text
612
test.t1 check status OK