| blob | f826199bb1f178cfadc4d4f56006ef05179a53c9 |
1 set names latin1;
2 set @@collation_connection=latin1_german2_ci;
3 select @@collation_connection;
4 @@collation_connection
5 latin1_german2_ci
6 drop table if exists t1;
7 create table t1 (a char (20) not null, b int not null auto_increment, index (a,b));
8 insert into t1 (a) values ('ä'),('ac'),('ae'),('ad'),('Äc'),('aeb');
9 insert into t1 (a) values ('üc'),('uc'),('ue'),('ud'),('Ü'),('ueb'),('uf');
10 insert into t1 (a) values ('ö'),('oc'),('Öa'),('oe'),('od'),('Öc'),('oeb');
11 insert into t1 (a) values ('s'),('ss'),('ß'),('ßb'),('ssa'),('ssc'),('ßa');
12 insert into t1 (a) values ('eä'),('uü'),('öo'),('ää'),('ääa'),('aeae');
13 insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É'),('a');
14 select a,b from t1 order by a,b;
15 a b
16 a 1
17 a 2
18 ac 1
19 ad 1
20 ä 1
21 ae 2
22 ää 1
23 aeae 2
24 ääa 1
25 aeb 1
26 Äc 1
27 é 1
28 É 2
29 eä 1
30 o 1
31 oc 1
32 od 1
33 ö 1
34 oe 2
35 Öa 1
36 oeb 1
37 Öc 1
38 öo 1
39 q 1
40 s 1
41 ss 1
42 ß 2
43 ssa 1
44 ßa 2
45 ßb 1
46 ssc 1
47 u 1
48 uc 1
49 ud 1
50 ue 1
51 Ü 2
52 ueb 1
53 üc 1
54 uf 1
55 uü 1
56 select a,b from t1 order by upper(a),b;
57 a b
58 a 1
59 a 2
60 ac 1
61 ad 1
62 ä 1
63 ae 2
64 ää 1
65 aeae 2
66 ääa 1
67 aeb 1
68 Äc 1
69 é 1
70 É 2
71 eä 1
72 o 1
73 oc 1
74 od 1
75 ö 1
76 oe 2
77 Öa 1
78 oeb 1
79 Öc 1
80 öo 1
81 q 1
82 s 1
83 ss 1
84 ß 2
85 ssa 1
86 ßa 2
87 ßb 1
88 ssc 1
89 u 1
90 uc 1
91 ud 1
92 ue 1
93 Ü 2
94 ueb 1
95 üc 1
96 uf 1
97 uü 1
98 select a from t1 order by a desc;
99 a
100 uü
101 uf
102 üc
103 ueb
104 Ü
105 ue
106 ud
107 uc
108 u
109 ssc
110 ßb
111 ßa
112 ssa
113 ß
114 ss
115 s
116 q
117 öo
118 Öc
119 oeb
120 Öa
121 oe
122 ö
123 od
124 oc
125 o
126 eä
127 É
128 é
129 Äc
130 aeb
131 ääa
132 aeae
133 ää
134 ae
135 ä
136 ad
137 ac
138 a
139 a
140 check table t1;
141 Table Op Msg_type Msg_text
142 test.t1 check status OK
143 select * from t1 where a like "ö%";
144 a b
145 ö 1
146 Öa 1
147 Öc 1
148 öo 1
149 select * from t1 where a like binary "%É%";
150 a b
151 É 2
152 select * from t1 where a like "%Á%";
153 a b
154 a 1
155 a 2
156 ac 1
157 ad 1
158 ae 2
159 aeae 2
160 ääa 1
161 aeb 1
162 Öa 1
163 ssa 1
164 ßa 2
165 select * from t1 where a like "%U%";
166 a b
167 u 1
168 uc 1
169 ud 1
170 ue 1
171 ueb 1
172 uf 1
173 uü 1
174 select * from t1 where a like "%ss%";
175 a b
176 ss 1
177 ssa 1
178 ssc 1
179 drop table t1;
180 select strcmp('ä','ae'),strcmp('ae','ä'),strcmp('aeq','äq'),strcmp('äq','aeq');
181 strcmp('ä','ae') strcmp('ae','ä') strcmp('aeq','äq') strcmp('äq','aeq')
182 0 0 0 0
183 select strcmp('ss','ß'),strcmp('ß','ss'),strcmp('ßs','sss'),strcmp('ßq','ssq');
184 strcmp('ss','ß') strcmp('ß','ss') strcmp('ßs','sss') strcmp('ßq','ssq')
185 0 0 0 0
186 select strcmp('ä','af'),strcmp('a','ä'),strcmp('ää','aeq'),strcmp('ää','aeaeq');
187 strcmp('ä','af') strcmp('a','ä') strcmp('ää','aeq') strcmp('ää','aeaeq')
188 -1 -1 -1 -1
189 select strcmp('ss','ßa'),strcmp('ß','ssa'),strcmp('sßa','sssb'),strcmp('s','ß');
190 strcmp('ss','ßa') strcmp('ß','ssa') strcmp('sßa','sssb') strcmp('s','ß')
191 -1 -1 -1 -1
192 select strcmp('ö','oö'),strcmp('Ü','uü'),strcmp('ö','oeb');
193 strcmp('ö','oö') strcmp('Ü','uü') strcmp('ö','oeb')
194 -1 -1 -1
195 select strcmp('af','ä'),strcmp('ä','a'),strcmp('aeq','ää'),strcmp('aeaeq','ää');
196 strcmp('af','ä') strcmp('ä','a') strcmp('aeq','ää') strcmp('aeaeq','ää')
197 1 1 1 1
198 select strcmp('ßa','ss'),strcmp('ssa','ß'),strcmp('sssb','sßa'),strcmp('ß','s');
199 strcmp('ßa','ss') strcmp('ssa','ß') strcmp('sssb','sßa') strcmp('ß','s')
200 1 1 1 1
201 select strcmp('u','öa'),strcmp('u','ö');
202 strcmp('u','öa') strcmp('u','ö')
203 1 1
204 select strcmp('sä', 'ßa'), strcmp('aä', 'äx');
205 strcmp('sä', 'ßa') strcmp('aä', 'äx')
206 -1 -1
207 create table t1 (a varchar(10), key(a), fulltext (a));
208 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
209 select * from t1 where a like "abc%";
210 a
211 abc
212 abcd
213 select * from t1 where a like "test%";
214 a
215 test
216 select * from t1 where a like "te_t";
217 a
218 test
219 select * from t1 where match a against ("te*" in boolean mode)+0;
220 a
221 test
222 drop table t1;
223 create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
224 show create table t1;
225 Table Create Table
226 t1 CREATE TABLE `t1` (
227 `word` varchar(255) COLLATE latin1_german2_ci NOT NULL,
228 `word2` varchar(255) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
229 KEY `word` (`word`)
230 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
231 insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
232 update t1 set word2=word;
233 select word, word=binary 0xdf as t from t1 having t > 0;
234 word t
235 ß 1
236 select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
237 word t
238 ss 1
239 ß 1
240 select * from t1 where word=binary 0xDF;
241 word word2
242 ß ß
243 select * from t1 where word=CAST(0xDF as CHAR);
244 word word2
245 ss ss
246 ß ß
247 select * from t1 where word2=binary 0xDF;
248 word word2
249 ß ß
250 select * from t1 where word2=CAST(0xDF as CHAR);
251 word word2
252 ss ss
253 ß ß
254 select * from t1 where word='ae';
255 word word2
256 ä ä
257 ae ae
258 select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
259 word word2
260 ä ä
261 ae ae
262 select * from t1 where word between binary 0xDF and binary 0xDF;
263 word word2
264 ß ß
265 select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
266 word word2
267 ss ss
268 ß ß
269 select * from t1 where word like 'ae';
270 word word2
271 ae ae
272 select * from t1 where word like 'AE';
273 word word2
274 ae ae
275 select * from t1 where word like binary 0xDF;
276 word word2
277 ß ß
278 select * from t1 where word like CAST(0xDF as CHAR);
279 word word2
280 ß ß
281 drop table t1;
282 CREATE TABLE t1 (
283 autor varchar(80) NOT NULL default '',
284 PRIMARY KEY (autor)
285 );
286 INSERT INTO t1 VALUES ('Powell, B.'),('Powell, Bud.'),('Powell, L. H.'),('Power, H.'),
287 ('Poynter, M. A. L. Lane'),('Poynting, J. H. und J. J. Thomson.'),('Pozzi, S(amuel-Jean).'),
288 ('Pozzi, Samuel-Jean.'),('Pozzo, A.'),('Pozzoli, Serge.');
289 SELECT * FROM t1 WHERE autor LIKE 'Poz%' ORDER BY autor;
290 autor
291 Pozzi, S(amuel-Jean).
292 Pozzi, Samuel-Jean.
293 Pozzo, A.
294 Pozzoli, Serge.
295 DROP TABLE t1;
296 CREATE TABLE t1 (
297 s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
298 );
299 show create table t1;
300 Table Create Table
301 t1 CREATE TABLE `t1` (
302 `s1` char(5) COLLATE latin1_german2_ci DEFAULT NULL
303 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
304 INSERT INTO t1 VALUES ('Ü');
305 INSERT INTO t1 VALUES ('ue');
306 SELECT DISTINCT s1 FROM t1;
307 s1
308 Ü
309 SELECT s1,COUNT(*) FROM t1 GROUP BY s1;
310 s1 COUNT(*)
311 Ü 2
312 SELECT COUNT(DISTINCT s1) FROM t1;
313 COUNT(DISTINCT s1)
314 1
315 SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1;
316 FIELD('ue',s1) FIELD('Ü',s1) s1='ue' s1='Ü'
317 1 1 1 1
318 1 1 1 1
319 DROP TABLE t1;
320 create table t1 select repeat('a',4000) a;
321 delete from t1;
322 insert into t1 values ('a'), ('a '), ('a\t');
323 select collation(a),hex(a) from t1 order by a;
324 collation(a) hex(a)
325 latin1_german2_ci 6109
326 latin1_german2_ci 61
327 latin1_german2_ci 6120
328 drop table t1;
329 drop table if exists t1;
330 create table t1 as select repeat(' ', 64) as s1;
331 select collation(s1) from t1;
332 collation(s1)
333 latin1_german2_ci
334 delete from t1;
335 insert into t1 values ('a'),('ae'),(_latin1 0xE4);
336 insert into t1 values ('o'),('oe'),(_latin1 0xF6);
337 insert into t1 values ('s'),('ss'),(_latin1 0xDF);
338 insert into t1 values ('u'),('ue'),(_latin1 0xFC);
339 select s1, hex(s1) from t1 order by s1, binary s1;
340 s1 hex(s1)
341 a 61
342 ae 6165
343 ä E4
344 o 6F
345 oe 6F65
346 ö F6
347 s 73
348 ss 7373
349 ß DF
350 u 75
351 ue 7565
352 ü FC
353 select group_concat(s1 order by binary s1) from t1 group by s1;
354 group_concat(s1 order by binary s1)
355 a
356 ae,ä
357 o
358 oe,ö
359 s
360 ss,ß
361 u
362 ue,ü
363 drop table t1;
364 SET NAMES latin1;
365 CREATE TABLE t1 (
366 col1 varchar(255) NOT NULL default ''
367 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 collate latin1_german2_ci;
368 INSERT INTO t1 VALUES ('ß'),('ss'),('ss');
369 ALTER TABLE t1 ADD KEY ifword(col1);
370 SELECT * FROM t1 WHERE col1='ß' ORDER BY col1, BINARY col1;
371 col1
372 ss
373 ss
374 ß
375 DROP TABLE t1;
376 create table t1 (s1 char(5) character set latin1 collate latin1_german2_ci);
377 insert into t1 values (0xf6) /* this is o-umlaut */;
378 select * from t1 where length(s1)=1 and s1='oe';
379 s1
380 ö
381 drop table t1;