1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
--echo #
--echo # Start of ctype_thai.inc
--echo #
#
# Test Unicode Thai collations based on libthai testcases
#
CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin);
INSERT INTO t1 VALUES ('-กระแย่ง');
INSERT INTO t1 VALUES ('กก');
INSERT INTO t1 VALUES ('กราบ');
INSERT INTO t1 VALUES ('ข่าง');
INSERT INTO t1 VALUES ('ข้าง');
INSERT INTO t1 VALUES ('ข้างกระดาน');
INSERT INTO t1 VALUES ('ข้างขึ้น');
INSERT INTO t1 VALUES ('ข้างควาย');
INSERT INTO t1 VALUES ('ข้างเงิน');
INSERT INTO t1 VALUES ('ข้างแรม');
INSERT INTO t1 VALUES ('ข้างออก');
INSERT INTO t1 VALUES ('ข้างๆ');
INSERT INTO t1 VALUES ('ข้างๆ คูๆ');
INSERT INTO t1 VALUES ('ขาง');
INSERT INTO t1 VALUES ('แข็ง');
INSERT INTO t1 VALUES ('แข่ง');
INSERT INTO t1 VALUES ('แข่งขัน');
INSERT INTO t1 VALUES ('แข้ง');
INSERT INTO t1 VALUES ('แข้งขวา');
INSERT INTO t1 VALUES ('แข็งขัน');
INSERT INTO t1 VALUES ('ทูลเกล้า');
INSERT INTO t1 VALUES ('ทูลเกล้าทูลกระหม่อม');
INSERT INTO t1 VALUES ('ทูลเกล้าฯ');
INSERT INTO t1 VALUES ('บุญญา');
INSERT INTO t1 VALUES ('บุญ-หลง');
INSERT INTO t1 VALUES ('บุญหลง');
INSERT INTO t1 VALUES ('ป่า');
INSERT INTO t1 VALUES ('ป่าน');
INSERT INTO t1 VALUES ('ป้า');
INSERT INTO t1 VALUES ('ป้าน');
INSERT INTO t1 VALUES ('ป๊า');
INSERT INTO t1 VALUES ('ป๊าน');
INSERT INTO t1 VALUES ('ป๋า');
INSERT INTO t1 VALUES ('ป๋าน');
INSERT INTO t1 VALUES ('ปา');
INSERT INTO t1 VALUES ('ปาน');
INSERT INTO t1 VALUES ('แป้ง');
INSERT INTO t1 VALUES ('พณิชย์');
INSERT INTO t1 VALUES ('ม้า');
INSERT INTO t1 VALUES ('ฯพณฯ');
INSERT INTO t1 VALUES ('A');
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('a\'');
INSERT INTO t1 VALUES ('A-');
INSERT INTO t1 VALUES ('a-');
INSERT INTO t1 VALUES ('-a');
INSERT INTO t1 VALUES ('A.');
INSERT INTO t1 VALUES ('a.');
INSERT INTO t1 VALUES ('A-1');
INSERT INTO t1 VALUES ('aa');
INSERT INTO t1 VALUES ('AA');
INSERT INTO t1 VALUES ('A.A.');
INSERT INTO t1 VALUES ('a.a.');
INSERT INTO t1 VALUES ('AAA');
INSERT INTO t1 VALUES ('A.A.A.');
INSERT INTO t1 VALUES ('AAAA');
INSERT INTO t1 VALUES ('A.A.A.L.');
INSERT INTO t1 VALUES ('A.A.A.S.');
INSERT INTO t1 VALUES ('Aachen');
INSERT INTO t1 VALUES ('A.A.E.');
INSERT INTO t1 VALUES ('A.Ae.E.');
INSERT INTO t1 VALUES ('A.A.E.E.');
INSERT INTO t1 VALUES ('AAES');
INSERT INTO t1 VALUES ('AAF');
INSERT INTO t1 VALUES ('A.Agr');
INSERT INTO t1 VALUES ('aah');
INSERT INTO t1 VALUES ('@@@@@');
INSERT INTO t1 VALUES ('0000');
INSERT INTO t1 VALUES ('9999');
INSERT INTO t1 VALUES ('Aalborg');
INSERT INTO t1 VALUES ('aide');
INSERT INTO t1 VALUES ('air');
INSERT INTO t1 VALUES ('@@@air');
INSERT INTO t1 VALUES ('air@@@');
INSERT INTO t1 VALUES ('C.A.F');
INSERT INTO t1 VALUES ('Canon');
INSERT INTO t1 VALUES ('coop');
INSERT INTO t1 VALUES ('co-op');
INSERT INTO t1 VALUES ('COOP');
INSERT INTO t1 VALUES ('CO-OP');
INSERT INTO t1 VALUES ('Copenhegen');
INSERT INTO t1 VALUES ('McArthur');
INSERT INTO t1 VALUES ('Mc Arthur');
INSERT INTO t1 VALUES ('Mc Mahon');
INSERT INTO t1 VALUES ('vice-president');
INSERT INTO t1 VALUES ('vice versa');
INSERT INTO t1 VALUES ('vice-versa');
INSERT INTO t1 VALUES ('10 ลิตร');
INSERT INTO t1 VALUES ('๑๐ ลิตร');
INSERT INTO t1 VALUES ('10 litre');
INSERT INTO t1 VALUES ('10 litre (10 ลิตร)');
INSERT INTO t1 VALUES ('10 ลิตร (10 litre)');
INSERT INTO t1 VALUES ('10 litre (๑๐ ลิตร)');
INSERT INTO t1 VALUES ('๑๐ ลิตร (10 litre)');
INSERT INTO t1 VALUES ('๑๐ ลิตร [10 litre]');
INSERT INTO t1 VALUES ('๑๐ ลิตร {10 litre}');
# Sort the table according to binary 'a'
# To avoid problems with unpredictable order or 'AA' and 'aa'
ALTER TABLE t1 ORDER BY a;
SET @backup_character_set_connection=@@character_set_connection;
SET @backup_collation_connection=@@collation_connection;
SET NAMES utf8;
SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a VARCHAR(30)',
' CHARACTER SET ', @backup_character_set_connection,
' COLLATE ', @backup_collation_connection,
' , ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY'
' , ADD KEY a_id (a, id)');
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET collation_connection=@backup_collation_connection;
SHOW CREATE TABLE t1;
--echo #
--echo # Ascending sort, using filesort
--echo #
EXPLAIN SELECT a FROM t1 ORDER BY a, BINARY a;
SELECT a FROM t1 ORDER BY a, BINARY a;
--echo #
--echo # Descending sort, using filesort
--echo #
EXPLAIN SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
--echo #
--echo # Ascending sort, using index
--echo #
EXPLAIN SELECT a FROM t1 ORDER BY a, id;
SELECT a FROM t1 ORDER BY a, id;
--echo #
--echo # Descending sort, using index
--echo #
EXPLAIN SELECT a FROM t1 ORDER BY a DESC, id DESC;
SELECT a FROM t1 ORDER BY a DESC, id DESC;
DROP TABLE t1;
--echo #
--echo # End of ctype_thai.inc
--echo #
|
