2
set @@collation_connection=latin1_german2_ci;
3
select @@collation_connection;
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;
56
select a,b from t1 order by upper(a),b;
98
select a from t1 order by a desc;
141
Table Op Msg_type Msg_text
142
test.t1 check status OK
143
select * from t1 where a like "�%";
149
select * from t1 where a like binary "%�%";
152
select * from t1 where a like "%�%";
165
select * from t1 where a like "%U%";
174
select * from t1 where a like "%ss%";
180
select strcmp('�','ae'),strcmp('ae','�'),strcmp('aeq','�q'),strcmp('�q','aeq');
181
strcmp('�','ae') strcmp('ae','�') strcmp('aeq','�q') strcmp('�q','aeq')
183
select strcmp('ss','�'),strcmp('�','ss'),strcmp('�s','sss'),strcmp('�q','ssq');
184
strcmp('ss','�') strcmp('�','ss') strcmp('�s','sss') strcmp('�q','ssq')
186
select strcmp('�','af'),strcmp('a','�'),strcmp('��','aeq'),strcmp('��','aeaeq');
187
strcmp('�','af') strcmp('a','�') strcmp('��','aeq') strcmp('��','aeaeq')
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','�')
192
select strcmp('�','o�'),strcmp('�','u�'),strcmp('�','oeb');
193
strcmp('�','o�') strcmp('�','u�') strcmp('�','oeb')
195
select strcmp('af','�'),strcmp('�','a'),strcmp('aeq','��'),strcmp('aeaeq','��');
196
strcmp('af','�') strcmp('�','a') strcmp('aeq','��') strcmp('aeaeq','��')
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')
201
select strcmp('u','�a'),strcmp('u','�');
202
strcmp('u','�a') strcmp('u','�')
204
select strcmp('s�', '�a'), strcmp('a�', '�x');
205
strcmp('s�', '�a') strcmp('a�', '�x')
207
create table t1 (a varchar(10), key(a), fulltext (a)) engine=myisam;
208
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
209
select * from t1 where a like "abc%";
213
select * from t1 where a like "test%";
216
select * from t1 where a like "te_t";
219
select * from t1 where match a against ("te*" in boolean mode)+0;
223
create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
224
show create table t1;
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 '',
230
) ENGINE=PBXT 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;
236
select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
240
select * from t1 where word=binary 0xDF;
243
select * from t1 where word=CAST(0xDF as CHAR);
247
select * from t1 where word2=binary 0xDF;
250
select * from t1 where word2=CAST(0xDF as CHAR);
254
select * from t1 where word='ae';
258
select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
262
select * from t1 where word between binary 0xDF and binary 0xDF;
265
select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
269
select * from t1 where word like 'ae';
272
select * from t1 where word like 'AE';
275
select * from t1 where word like binary 0xDF;
278
select * from t1 where word like CAST(0xDF as CHAR);
283
autor varchar(80) NOT NULL default '',
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;
291
Pozzi, S(amuel-Jean).
297
s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
299
show create table t1;
301
t1 CREATE TABLE `t1` (
302
`s1` char(5) COLLATE latin1_german2_ci DEFAULT NULL
303
) ENGINE=PBXT DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
304
INSERT INTO t1 VALUES ('�');
305
INSERT INTO t1 VALUES ('ue');
306
SELECT DISTINCT s1 FROM t1;
309
SELECT s1,COUNT(*) FROM t1 GROUP BY s1;
312
SELECT COUNT(DISTINCT s1) FROM t1;
315
SELECT FIELD('ue',s1), FIELD('�',s1), s1='ue', s1='�' FROM t1;
316
FIELD('ue',s1) FIELD('�',s1) s1='ue' s1='�'
320
create table t1 select repeat('a',4000) a;
322
insert into t1 values ('a'), ('a '), ('a\t');
323
select collation(a),hex(a) from t1 order by a;
325
latin1_german2_ci 6109
327
latin1_german2_ci 6120
331
col1 varchar(255) NOT NULL default ''
332
) ENGINE=MyISAM DEFAULT CHARSET=latin1 collate latin1_german2_ci;
333
INSERT INTO t1 VALUES ('�'),('ss'),('ss');
334
ALTER TABLE t1 ADD KEY ifword(col1);
335
SELECT * FROM t1 WHERE col1='�' ORDER BY col1, BINARY col1;
341
create table t1 (s1 char(5) character set latin1 collate latin1_german2_ci);
342
insert into t1 values (0xf6) /* this is o-umlaut */;
343
select * from t1 where length(s1)=1 and s1='oe';