1
drop table if exists t1,t2;
3
select left(_utf8 0xD0B0D0B1D0B2,1);
4
left(_utf8 0xD0B0D0B1D0B2,1)
6
select right(_utf8 0xD0B0D0B2D0B2,1);
7
right(_utf8 0xD0B0D0B2D0B2,1)
9
select locate('he','hello');
12
select locate('he','hello',2);
13
locate('he','hello',2)
15
select locate('lo','hello',2);
16
locate('lo','hello',2)
18
select locate('HE','hello');
21
select locate('HE','hello',2);
22
locate('HE','hello',2)
24
select locate('LO','hello',2);
25
locate('LO','hello',2)
27
select locate('HE','hello' collate utf8_bin);
28
locate('HE','hello' collate utf8_bin)
30
select locate('HE','hello' collate utf8_bin,2);
31
locate('HE','hello' collate utf8_bin,2)
33
select locate('LO','hello' collate utf8_bin,2);
34
locate('LO','hello' collate utf8_bin,2)
36
select locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2);
37
locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2)
39
select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2);
40
locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2)
42
select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2);
43
locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2)
45
select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin);
46
locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin)
48
select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin);
49
locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin)
51
select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1);
52
length(_utf8 0xD0B1) bit_length(_utf8 0xD0B1) char_length(_utf8 0xD0B1)
60
select 'A' like 'a' collate utf8_bin;
61
'A' like 'a' collate utf8_bin
63
select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%');
64
_utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%')
66
select convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8);
67
convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8)
69
select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8);
70
CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8)
72
select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8);
73
CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8)
90
SELECT 'a' = 'a ' collate utf8_bin;
91
'a' = 'a ' collate utf8_bin
93
SELECT 'a\0' < 'a' collate utf8_bin;
94
'a\0' < 'a' collate utf8_bin
96
SELECT 'a\0' < 'a ' collate utf8_bin;
97
'a\0' < 'a ' collate utf8_bin
99
SELECT 'a\t' < 'a' collate utf8_bin;
100
'a\t' < 'a' collate utf8_bin
102
SELECT 'a\t' < 'a ' collate utf8_bin;
103
'a\t' < 'a ' collate utf8_bin
105
CREATE TABLE t1 (a char(10) character set utf8 not null);
106
INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
107
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
108
hex(a) STRCMP(a,'a') STRCMP(a,'a ')
114
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
115
insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es')
117
select insert("aa",100,1,"b"),insert("aa",1,3,"b");
118
insert("aa",100,1,"b") insert("aa",1,3,"b")
120
select char_length(left(@a:='тест',5)), length(@a), @a;
121
char_length(left(@a:='тест',5)) length(@a) @a
123
create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d");
124
show create table t1;
126
t1 CREATE TABLE `t1` (
127
`date_format("2004-01-19 10:10:10", "%Y-%m-%d")` varchar(10) CHARACTER SET utf8 DEFAULT NULL
128
) ENGINE=MyISAM DEFAULT CHARSET=latin1
130
date_format("2004-01-19 10:10:10", "%Y-%m-%d")
134
set LC_TIME_NAMES='fr_FR';
135
create table t1 (s1 char(20) character set latin1);
136
insert into t1 values (date_format('2004-02-02','%M'));
137
select hex(s1) from t1;
141
create table t1 (s1 char(20) character set koi8r);
142
set LC_TIME_NAMES='ru_RU';
143
insert into t1 values (date_format('2004-02-02','%M'));
144
insert into t1 values (date_format('2004-02-02','%b'));
145
insert into t1 values (date_format('2004-02-02','%W'));
146
insert into t1 values (date_format('2004-02-02','%a'));
147
select hex(s1), s1 from t1;
149
E6C5D7D2C1CCD1 Февраля
151
F0CFCEC5C4C5CCD8CEC9CB Понедельник
154
set LC_TIME_NAMES='en_US';
156
create table t1 (s1 char(1) character set utf8);
157
insert into t1 values (_koi8r'��');
159
Warning 1265 Data truncated for column 's1' at row 1
160
select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
161
s1 hex(s1) char_length(s1) octet_length(s1)
164
create table t1 (s1 tinytext character set utf8);
165
insert into t1 select repeat('a',300);
167
Warning 1265 Data truncated for column 's1' at row 1
168
insert into t1 select repeat('�',300);
170
Warning 1265 Data truncated for column 's1' at row 1
171
insert into t1 select repeat('a�',300);
173
Warning 1265 Data truncated for column 's1' at row 1
174
insert into t1 select repeat('�a',300);
176
Warning 1265 Data truncated for column 's1' at row 1
177
insert into t1 select repeat('��',300);
179
Warning 1265 Data truncated for column 's1' at row 1
180
select hex(s1) from t1;
182
616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161
183
D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F
184
61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F
185
D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61
186
D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F
187
select length(s1),char_length(s1) from t1;
188
length(s1) char_length(s1)
195
create table t1 (s1 text character set utf8);
196
insert into t1 select repeat('a',66000);
198
Warning 1265 Data truncated for column 's1' at row 1
199
insert into t1 select repeat('�',66000);
201
Warning 1265 Data truncated for column 's1' at row 1
202
insert into t1 select repeat('a�',66000);
204
Warning 1265 Data truncated for column 's1' at row 1
205
insert into t1 select repeat('�a',66000);
207
Warning 1265 Data truncated for column 's1' at row 1
208
insert into t1 select repeat('��',66000);
210
Warning 1265 Data truncated for column 's1' at row 1
211
select length(s1),char_length(s1) from t1;
212
length(s1) char_length(s1)
219
create table t1 (s1 char(10) character set utf8);
220
insert into t1 values (0x41FF);
222
Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1
223
select hex(s1) from t1;
227
create table t1 (s1 varchar(10) character set utf8);
228
insert into t1 values (0x41FF);
230
Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1
231
select hex(s1) from t1;
235
create table t1 (s1 text character set utf8);
236
insert into t1 values (0x41FF);
238
Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1
239
select hex(s1) from t1;
243
create table t1 (a text character set utf8, primary key(a(360)));
244
ERROR 42000: Specified key was too long; max key length is 1332 bytes
245
CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8;
246
INSERT INTO t1 VALUES ( 'test' );
247
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;
250
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test';
253
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test';
257
create table t1 (a char(255) character set utf8);
258
insert into t1 values('b'),('b');
259
select * from t1 where a = 'b';
263
select * from t1 where a = 'b' and a = 'b';
267
select * from t1 where a = 'b' and a != 'b';
270
set collation_connection=utf8_general_ci;
271
drop table if exists t1;
273
select repeat(' ', 64) as s1, repeat(' ',64) as s2
276
show create table t1;
278
t1 CREATE TABLE `t1` (
279
`s1` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
280
`s2` varchar(64) CHARACTER SET utf8 DEFAULT NULL
281
) ENGINE=MyISAM DEFAULT CHARSET=latin1
283
insert into t1 values('aaa','aaa');
284
insert into t1 values('aaa|qqq','qqq');
285
insert into t1 values('gheis','^[^a-dXYZ]+$');
286
insert into t1 values('aab','^aa?b');
287
insert into t1 values('Baaan','^Ba*n');
288
insert into t1 values('aaa','qqq|aaa');
289
insert into t1 values('qqq','qqq|aaa');
290
insert into t1 values('bbb','qqq|aaa');
291
insert into t1 values('bbb','qqq');
292
insert into t1 values('aaa','aba');
293
insert into t1 values(null,'abc');
294
insert into t1 values('def',null);
295
insert into t1 values(null,null);
296
insert into t1 values('ghi','ghi[');
297
select HIGH_PRIORITY s1 regexp s2 from t1;
316
select 'вася' rlike '[[:<:]]вася[[:>:]]';
317
'вася' rlike '[[:<:]]вася[[:>:]]'
319
select 'вася ' rlike '[[:<:]]вася[[:>:]]';
320
'вася ' rlike '[[:<:]]вася[[:>:]]'
322
select ' вася' rlike '[[:<:]]вася[[:>:]]';
323
' вася' rlike '[[:<:]]вася[[:>:]]'
325
select ' вася ' rlike '[[:<:]]вася[[:>:]]';
326
' вася ' rlike '[[:<:]]вася[[:>:]]'
328
select 'васяz' rlike '[[:<:]]вася[[:>:]]';
329
'васяz' rlike '[[:<:]]вася[[:>:]]'
331
select 'zвася' rlike '[[:<:]]вася[[:>:]]';
332
'zвася' rlike '[[:<:]]вася[[:>:]]'
334
select 'zвасяz' rlike '[[:<:]]вася[[:>:]]';
335
'zвасяz' rlike '[[:<:]]вася[[:>:]]'
337
CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci);
338
ALTER TABLE t1 ADD COLUMN b CHAR(20);
341
create table t1 (a enum('aaaa','проба') character set utf8);
342
show create table t1;
344
t1 CREATE TABLE `t1` (
345
`a` enum('aaaa','проба') CHARACTER SET utf8 DEFAULT NULL
346
) ENGINE=MyISAM DEFAULT CHARSET=latin1
347
insert into t1 values ('проба');
351
create table t2 select ifnull(a,a) from t1;
352
show create table t2;
354
t2 CREATE TABLE `t2` (
355
`ifnull(a,a)` varchar(5) CHARACTER SET utf8 DEFAULT NULL
356
) ENGINE=MyISAM DEFAULT CHARSET=latin1
362
create table t1 (c varchar(30) character set utf8, unique(c(10)));
363
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
364
insert into t1 values ('aaaaaaaaaa');
365
insert into t1 values ('aaaaaaaaaaa');
366
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
367
insert into t1 values ('aaaaaaaaaaaa');
368
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
369
insert into t1 values (repeat('b',20));
370
select c c1 from t1 where c='1';
373
select c c2 from t1 where c='2';
376
select c c3 from t1 where c='3';
379
select c cx from t1 where c='x';
382
select c cy from t1 where c='y';
385
select c cz from t1 where c='z';
388
select c ca10 from t1 where c='aaaaaaaaaa';
391
select c cb20 from t1 where c=repeat('b',20);
395
create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb;
396
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
397
insert into t1 values ('aaaaaaaaaa');
398
insert into t1 values ('aaaaaaaaaaa');
399
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
400
insert into t1 values ('aaaaaaaaaaaa');
401
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
402
insert into t1 values (repeat('b',20));
403
select c c1 from t1 where c='1';
406
select c c2 from t1 where c='2';
409
select c c3 from t1 where c='3';
412
select c cx from t1 where c='x';
415
select c cy from t1 where c='y';
418
select c cz from t1 where c='z';
421
select c ca10 from t1 where c='aaaaaaaaaa';
424
select c cb20 from t1 where c=repeat('b',20);
428
create table t1 (c char(3) character set utf8, unique (c(2)));
429
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
430
insert into t1 values ('a');
431
insert into t1 values ('aa');
432
insert into t1 values ('aaa');
433
ERROR 23000: Duplicate entry 'aa' for key 'c'
434
insert into t1 values ('b');
435
insert into t1 values ('bb');
436
insert into t1 values ('bbb');
437
ERROR 23000: Duplicate entry 'bb' for key 'c'
438
insert into t1 values ('а');
439
insert into t1 values ('аа');
440
insert into t1 values ('ааа');
441
ERROR 23000: Duplicate entry 'аа' for key 'c'
442
insert into t1 values ('б');
443
insert into t1 values ('бб');
444
insert into t1 values ('ббб');
445
ERROR 23000: Duplicate entry 'бб' for key 'c'
446
insert into t1 values ('ꪪ');
447
insert into t1 values ('ꪪꪪ');
448
insert into t1 values ('ꪪꪪꪪ');
449
ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'
451
create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb;
452
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
453
insert into t1 values ('a');
454
insert into t1 values ('aa');
455
insert into t1 values ('aaa');
456
ERROR 23000: Duplicate entry 'aa' for key 'c'
457
insert into t1 values ('b');
458
insert into t1 values ('bb');
459
insert into t1 values ('bbb');
460
ERROR 23000: Duplicate entry 'bb' for key 'c'
461
insert into t1 values ('а');
462
insert into t1 values ('аа');
463
insert into t1 values ('ааа');
464
ERROR 23000: Duplicate entry 'аа' for key 'c'
465
insert into t1 values ('б');
466
insert into t1 values ('бб');
467
insert into t1 values ('ббб');
468
ERROR 23000: Duplicate entry 'бб' for key 'c'
469
insert into t1 values ('ꪪ');
470
insert into t1 values ('ꪪꪪ');
471
insert into t1 values ('ꪪꪪꪪ');
472
ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'
475
c char(10) character set utf8,
476
unique key a using hash (c(1))
478
show create table t1;
480
t1 CREATE TABLE `t1` (
481
`c` char(10) CHARACTER SET utf8 DEFAULT NULL,
482
UNIQUE KEY `a` (`c`(1)) USING HASH
483
) ENGINE=MEMORY DEFAULT CHARSET=latin1
484
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
485
insert into t1 values ('aa');
486
ERROR 23000: Duplicate entry 'a' for key 'a'
487
insert into t1 values ('aaa');
488
ERROR 23000: Duplicate entry 'a' for key 'a'
489
insert into t1 values ('б');
490
insert into t1 values ('бб');
491
ERROR 23000: Duplicate entry 'б' for key 'a'
492
insert into t1 values ('ббб');
493
ERROR 23000: Duplicate entry 'б' for key 'a'
494
select c as c_all from t1 order by c;
503
select c as c_a from t1 where c='a';
506
select c as c_a from t1 where c='б';
511
c char(10) character set utf8,
512
unique key a using btree (c(1))
514
show create table t1;
516
t1 CREATE TABLE `t1` (
517
`c` char(10) CHARACTER SET utf8 DEFAULT NULL,
518
UNIQUE KEY `a` (`c`(1)) USING BTREE
519
) ENGINE=MEMORY DEFAULT CHARSET=latin1
520
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
521
insert into t1 values ('aa');
522
ERROR 23000: Duplicate entry 'a' for key 'a'
523
insert into t1 values ('aaa');
524
ERROR 23000: Duplicate entry 'a' for key 'a'
525
insert into t1 values ('б');
526
insert into t1 values ('бб');
527
ERROR 23000: Duplicate entry 'б' for key 'a'
528
insert into t1 values ('ббб');
529
ERROR 23000: Duplicate entry 'б' for key 'a'
530
select c as c_all from t1 order by c;
539
select c as c_a from t1 where c='a';
542
select c as c_a from t1 where c='б';
547
c char(10) character set utf8,
550
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
551
insert into t1 values ('aa');
552
ERROR 23000: Duplicate entry 'a' for key 'a'
553
insert into t1 values ('aaa');
554
ERROR 23000: Duplicate entry 'a' for key 'a'
555
insert into t1 values ('б');
556
insert into t1 values ('бб');
557
ERROR 23000: Duplicate entry 'б' for key 'a'
558
insert into t1 values ('ббб');
559
ERROR 23000: Duplicate entry 'б' for key 'a'
560
select c as c_all from t1 order by c;
569
select c as c_a from t1 where c='a';
572
select c as c_a from t1 where c='б';
576
create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10)));
577
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
578
insert into t1 values ('aaaaaaaaaa');
579
insert into t1 values ('aaaaaaaaaaa');
580
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
581
insert into t1 values ('aaaaaaaaaaaa');
582
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
583
insert into t1 values (repeat('b',20));
584
select c c1 from t1 where c='1';
587
select c c2 from t1 where c='2';
590
select c c3 from t1 where c='3';
593
select c cx from t1 where c='x';
596
select c cy from t1 where c='y';
599
select c cz from t1 where c='z';
602
select c ca10 from t1 where c='aaaaaaaaaa';
605
select c cb20 from t1 where c=repeat('b',20);
609
create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2)));
610
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
611
insert into t1 values ('a');
612
insert into t1 values ('aa');
613
insert into t1 values ('aaa');
614
ERROR 23000: Duplicate entry 'aa' for key 'c'
615
insert into t1 values ('b');
616
insert into t1 values ('bb');
617
insert into t1 values ('bbb');
618
ERROR 23000: Duplicate entry 'bb' for key 'c'
619
insert into t1 values ('а');
620
insert into t1 values ('аа');
621
insert into t1 values ('ааа');
622
ERROR 23000: Duplicate entry 'аа' for key 'c'
623
insert into t1 values ('б');
624
insert into t1 values ('бб');
625
insert into t1 values ('ббб');
626
ERROR 23000: Duplicate entry 'бб' for key 'c'
627
insert into t1 values ('ꪪ');
628
insert into t1 values ('ꪪꪪ');
629
insert into t1 values ('ꪪꪪꪪ');
630
ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'
633
c char(10) character set utf8 collate utf8_bin,
634
unique key a using hash (c(1))
636
show create table t1;
638
t1 CREATE TABLE `t1` (
639
`c` char(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
640
UNIQUE KEY `a` (`c`(1)) USING HASH
641
) ENGINE=MEMORY DEFAULT CHARSET=latin1
642
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
643
insert into t1 values ('aa');
644
ERROR 23000: Duplicate entry 'a' for key 'a'
645
insert into t1 values ('aaa');
646
ERROR 23000: Duplicate entry 'a' for key 'a'
647
insert into t1 values ('б');
648
insert into t1 values ('бб');
649
ERROR 23000: Duplicate entry 'б' for key 'a'
650
insert into t1 values ('ббб');
651
ERROR 23000: Duplicate entry 'б' for key 'a'
652
select c as c_all from t1 order by c;
661
select c as c_a from t1 where c='a';
664
select c as c_a from t1 where c='б';
669
c char(10) character set utf8 collate utf8_bin,
670
unique key a using btree (c(1))
672
show create table t1;
674
t1 CREATE TABLE `t1` (
675
`c` char(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
676
UNIQUE KEY `a` (`c`(1)) USING BTREE
677
) ENGINE=MEMORY DEFAULT CHARSET=latin1
678
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
679
insert into t1 values ('aa');
680
ERROR 23000: Duplicate entry 'a' for key 'a'
681
insert into t1 values ('aaa');
682
ERROR 23000: Duplicate entry 'a' for key 'a'
683
insert into t1 values ('б');
684
insert into t1 values ('бб');
685
ERROR 23000: Duplicate entry 'б' for key 'a'
686
insert into t1 values ('ббб');
687
ERROR 23000: Duplicate entry 'б' for key 'a'
688
select c as c_all from t1 order by c;
697
select c as c_a from t1 where c='a';
700
select c as c_a from t1 where c='б';
705
c char(10) character set utf8 collate utf8_bin,
708
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
709
insert into t1 values ('aa');
710
ERROR 23000: Duplicate entry 'a' for key 'a'
711
insert into t1 values ('aaa');
712
ERROR 23000: Duplicate entry 'a' for key 'a'
713
insert into t1 values ('б');
714
insert into t1 values ('бб');
715
ERROR 23000: Duplicate entry 'б' for key 'a'
716
insert into t1 values ('ббб');
717
ERROR 23000: Duplicate entry 'б' for key 'a'
718
select c as c_all from t1 order by c;
727
select c as c_a from t1 where c='a';
730
select c as c_a from t1 where c='б';
735
str varchar(255) character set utf8 not null,
738
INSERT INTO t1 VALUES ('str');
739
INSERT INTO t1 VALUES ('str2');
740
select * from t1 where str='str';
745
str varchar(255) character set utf8 not null,
748
INSERT INTO t1 VALUES ('str');
749
INSERT INTO t1 VALUES ('str2');
750
select * from t1 where str='str';
755
str varchar(255) character set utf8 not null,
756
key str using btree (str(2))
758
INSERT INTO t1 VALUES ('str');
759
INSERT INTO t1 VALUES ('str2');
760
select * from t1 where str='str';
765
str varchar(255) character set utf8 not null,
766
key str using hash (str(2))
768
INSERT INTO t1 VALUES ('str');
769
INSERT INTO t1 VALUES ('str2');
770
select * from t1 where str='str';
775
str varchar(255) character set utf8 not null,
778
INSERT INTO t1 VALUES ('str');
779
INSERT INTO t1 VALUES ('str2');
780
select * from t1 where str='str';
784
CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8;
785
INSERT INTO t1 VALUES ('test');
786
SELECT a FROM t1 WHERE a LIKE '%te';
791
subject varchar(255) character set utf8 collate utf8_unicode_ci,
792
p varchar(15) character set utf8
793
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
794
INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員IDが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか? その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057');
795
INSERT INTO t1 VALUES ('aaa','bbb');
796
SELECT length(subject) FROM t1;
800
SELECT length(subject) FROM t1 ORDER BY 1;
806
id int unsigned NOT NULL auto_increment,
807
list_id smallint unsigned NOT NULL,
810
INDEX(list_id, term(4))
811
) ENGINE=MYISAM CHARSET=utf8;
812
INSERT INTO t1 SET list_id = 1, term = "letterc";
813
INSERT INTO t1 SET list_id = 1, term = "letterb";
814
INSERT INTO t1 SET list_id = 1, term = "lettera";
815
INSERT INTO t1 SET list_id = 1, term = "letterd";
816
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
819
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
822
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
825
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
831
id int unsigned NOT NULL auto_increment,
832
list_id smallint unsigned NOT NULL,
835
INDEX(list_id, term(19))
836
) ENGINE=MyISAM CHARSET=utf8;
837
INSERT INTO t1 set list_id = 1, term = "test�test";
838
INSERT INTO t1 set list_id = 1, term = "testetest";
839
INSERT INTO t1 set list_id = 1, term = "test�test";
840
SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");
845
SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest");
850
SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");
861
) engine=innodb character set=utf8;
862
insert into t1 values(1,'foo'),(2,'foobar');
863
select * from t1 where b like 'foob%';
866
alter table t1 engine=innodb;
867
select * from t1 where b like 'foob%';
872
a enum('петя','вася','анюта') character set utf8 not null default 'анюта',
873
b set('петя','вася','анюта') character set utf8 not null default 'анюта'
875
create table t2 select concat(a,_utf8'') as a, concat(b,_utf8'')as b from t1;
876
show create table t2;
878
t2 CREATE TABLE `t2` (
879
`a` varchar(5) CHARACTER SET utf8 NOT NULL DEFAULT '',
880
`b` varchar(15) CHARACTER SET utf8 NOT NULL DEFAULT ''
881
) ENGINE=MyISAM DEFAULT CHARSET=latin1
884
select 'c' like '\_' as want0;
887
SELECT SUBSTR('вася',-2);
890
create table t1 (id integer, a varchar(100) character set utf8 collate utf8_unicode_ci);
891
insert into t1 values (1, 'Test');
892
select * from t1 where soundex(a) = soundex('Test');
895
select * from t1 where soundex(a) = soundex('TEST');
898
select * from t1 where soundex(a) = soundex('test');
902
select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB);
903
soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)
905
select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB));
906
hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB))
908
select soundex(_utf8 0xD091D092D093);
909
soundex(_utf8 0xD091D092D093)
911
select hex(soundex(_utf8 0xD091D092D093));
912
hex(soundex(_utf8 0xD091D092D093))
914
SET collation_connection='utf8_general_ci';
915
create table t1 select repeat('a',4000) a;
917
insert into t1 values ('a'), ('a '), ('a\t');
918
select collation(a),hex(a) from t1 order by a;
924
select @@collation_connection;
925
@@collation_connection
927
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
928
insert into t1 values('abcdef');
929
insert into t1 values('_bcdef');
930
insert into t1 values('a_cdef');
931
insert into t1 values('ab_def');
932
insert into t1 values('abc_ef');
933
insert into t1 values('abcd_f');
934
insert into t1 values('abcde_');
935
select c1 as c1u from t1 where c1 like 'ab\_def';
938
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
942
drop table if exists t1;
943
create table t1 as select repeat(' ', 64) as s1;
944
select collation(s1) from t1;
948
insert into t1 values ('a'),('ae'),(_latin1 0xE4);
949
insert into t1 values ('o'),('oe'),(_latin1 0xF6);
950
insert into t1 values ('s'),('ss'),(_latin1 0xDF);
951
insert into t1 values ('u'),('ue'),(_latin1 0xFC);
952
select s1, hex(s1) from t1 order by s1, binary s1;
966
select group_concat(s1 order by binary s1) from t1 group by s1;
967
group_concat(s1 order by binary s1)
977
SET collation_connection='utf8_bin';
978
create table t1 select repeat('a',4000) a;
980
insert into t1 values ('a'), ('a '), ('a\t');
981
select collation(a),hex(a) from t1 order by a;
987
select @@collation_connection;
988
@@collation_connection
990
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
991
insert into t1 values('abcdef');
992
insert into t1 values('_bcdef');
993
insert into t1 values('a_cdef');
994
insert into t1 values('ab_def');
995
insert into t1 values('abc_ef');
996
insert into t1 values('abcd_f');
997
insert into t1 values('abcde_');
998
select c1 as c1u from t1 where c1 like 'ab\_def';
1001
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
1006
user varchar(255) NOT NULL default ''
1007
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1008
INSERT INTO t1 VALUES ('one'),('two');
1009
SELECT CHARSET('a');
1012
SELECT user, CONCAT('<', user, '>') AS c FROM t1;
1017
create table t1 (f1 varchar(1) not null) default charset utf8;
1018
insert into t1 values (''), ('');
1019
select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
1020
concat(concat(_latin1'->',f1),_latin1'<-')
1024
select convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8);
1025
convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8)
1028
create table t1 (a varchar(10)) character set utf8;
1029
insert into t1 values ('test');
1030
select ifnull(a,'') from t1;
1034
select repeat(_utf8'+',3) as h union select NULL;
1038
select ifnull(NULL, _utf8'string');
1039
ifnull(NULL, _utf8'string')
1042
create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci);
1043
insert into t1 values ('I'),('K'),('Y');
1044
select * from t1 where s1 < 'K' and s1 = 'Y';
1048
select * from t1 where 'K' > s1 and s1 = 'Y';
1053
create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci);
1054
insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');
1055
select * from t1 where s1 > 'd' and s1 = 'CH';
1060
select * from t1 where 'd' < s1 and s1 = 'CH';
1065
select * from t1 where s1 = 'cH' and s1 <> 'ch';
1068
select * from t1 where 'cH' = s1 and s1 <> 'ch';
1072
create table t1 (a varchar(255)) default character set utf8;
1073
insert into t1 values (1.0);
1077
city varchar(20) not null,
1079
) character set=utf8;
1080
insert into t1 values (1,'Durban North');
1081
insert into t1 values (2,'Durban');
1082
select * from t1 where city = 'Durban';
1085
select * from t1 where city = 'Durban ';
1089
create table t1 (x set('A', 'B') default 0) character set utf8;
1090
ERROR 42000: Invalid default value for 'x'
1091
create table t1 (x enum('A', 'B') default 0) character set utf8;
1092
ERROR 42000: Invalid default value for 'x'
1095
`id` int(20) NOT NULL auto_increment,
1096
`country` varchar(100) NOT NULL default '',
1097
`shortcode` varchar(100) NOT NULL default '',
1098
`operator` varchar(100) NOT NULL default '',
1099
`momid` varchar(30) NOT NULL default '',
1100
`keyword` varchar(160) NOT NULL default '',
1101
`content` varchar(160) NOT NULL default '',
1102
`second_token` varchar(160) default NULL,
1103
`gateway_id` int(11) NOT NULL default '0',
1104
`created` datetime NOT NULL default '0000-00-00 00:00:00',
1105
`msisdn` varchar(15) NOT NULL default '',
1107
UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`),
1108
KEY `IX_mobile_originated_message_keyword` (`keyword`),
1109
KEY `IX_mobile_originated_message_created` (`created`),
1110
KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`)
1111
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
1112
INSERT INTO t1 VALUES
1113
(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми','ИМРИ.АФИМИМ.АЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'),
1114
(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890');
1116
`msisdn` varchar(15) NOT NULL default '',
1117
`operator_id` int(11) NOT NULL default '0',
1118
`created` datetime NOT NULL default '0000-00-00 00:00:00',
1119
UNIQUE KEY `PK_user` (`msisdn`)
1120
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1121
INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');
1122
SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890';
1124
ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми 1234567890
1127
create table t1 (a char(20) character set utf8);
1128
insert into t1 values ('123456'),('андрей');
1129
alter table t1 modify a char(2) character set utf8;
1131
Warning 1265 Data truncated for column 'a' at row 1
1132
Warning 1265 Data truncated for column 'a' at row 2
1133
select char_length(a), length(a), a from t1 order by a;
1134
char_length(a) length(a) a
1139
select 'andre%' like 'andreñ%' escape 'ñ';
1140
'andre%' like 'andreñ%' escape 'ñ'
1143
select 'a\\' like 'a\\';
1146
select 'aa\\' like 'a%\\';
1149
create table t1 (a char(10), key(a)) character set utf8;
1150
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
1151
select * from t1 where a like "abc%";
1155
select * from t1 where a like concat("abc","%");
1159
select * from t1 where a like "ABC%";
1163
select * from t1 where a like "test%";
1166
select * from t1 where a like "te_t";
1169
select * from t1 where a like "%a%";
1174
select * from t1 where a like "%abcd%";
1177
select * from t1 where a like "%abc\d%";
1182
a varchar(255) NOT NULL default '',
1184
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
1185
insert into t1 values (_utf8 0xe880bd);
1186
insert into t1 values (_utf8 0x5b);
1187
select hex(a) from t1;
1193
create table t1 (a varchar(255)) default charset=utf8;
1194
select * from t1 where find_in_set('-1', a);
1197
create table t1 (a int);
1198
insert into t1 values (48),(49),(50);
1200
select distinct char(a) from t1;
1206
CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8);
1207
INSERT INTO t1 VALUES(REPEAT('a', 100));
1208
CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1;
1209
SELECT LENGTH(bug) FROM t2;
1214
CREATE TABLE t1 (item varchar(255)) default character set utf8;
1215
INSERT INTO t1 VALUES (N'\\');
1216
INSERT INTO t1 VALUES (_utf8'\\');
1217
INSERT INTO t1 VALUES (N'Cote d\'Ivoire');
1218
INSERT INTO t1 VALUES (_utf8'Cote d\'Ivoire');
1219
SELECT item FROM t1 ORDER BY item;
1227
DROP TABLE IF EXISTS t1;
1229
Note 1051 Unknown table 't1'
1230
CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
1231
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
1232
INSERT INTO t1 VALUES('uu');
1234
Table Op Msg_type Msg_text
1235
test.t1 check status OK
1236
INSERT INTO t1 VALUES('uU');
1238
Table Op Msg_type Msg_text
1239
test.t1 check status OK
1240
INSERT INTO t1 VALUES('uu');
1242
Table Op Msg_type Msg_text
1243
test.t1 check status OK
1244
INSERT INTO t1 VALUES('uuABC');
1246
Table Op Msg_type Msg_text
1247
test.t1 check status OK
1248
INSERT INTO t1 VALUES('UuABC');
1250
Table Op Msg_type Msg_text
1251
test.t1 check status OK
1252
INSERT INTO t1 VALUES('uuABC');
1254
Table Op Msg_type Msg_text
1255
test.t1 check status OK
1256
alter table t1 add b int;
1257
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);
1258
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2);
1259
delete from t1 where b=1;
1260
INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);
1262
Table Op Msg_type Msg_text
1263
test.t1 check status OK
1264
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);
1265
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4);
1266
delete from t1 where b=3;
1267
INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);
1269
Table Op Msg_type Msg_text
1270
test.t1 check status OK
1273
create table t1 (s1 char(5) character set utf8);
1274
insert into t1 values
1275
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
1276
create index it1 on t1 (s1);
1277
select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%';
1278
before_delete_general_ci
1280
delete from t1 where s1 = 'Y';
1281
select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%';
1282
after_delete_general_ci
1286
create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci);
1287
insert into t1 values
1288
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
1289
create index it1 on t1 (s1);
1290
select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%';
1291
before_delete_unicode_ci
1293
delete from t1 where s1 = 'Y';
1294
select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%';
1295
after_delete_unicode_ci
1299
create table t1 (s1 char(5) character set utf8 collate utf8_bin);
1300
insert into t1 values
1301
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
1302
create index it1 on t1 (s1);
1303
select s1 as before_delete_bin from t1 where s1 like 'ペテ%';
1306
delete from t1 where s1 = 'Y';
1307
select s1 as after_delete_bin from t1 where s1 like 'ペテ%';
1312
create table t1 (a varchar(30) not null primary key)
1313
engine=innodb default character set utf8 collate utf8_general_ci;
1314
insert into t1 values ('あいうえおかきくけこさしすせそ');
1315
insert into t1 values ('さしすせそかきくけこあいうえお');
1316
select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%';
1319
select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ';
1324
create table t1 (a varchar(30) not null primary key)
1325
engine=innodb default character set utf8 collate utf8_unicode_ci;
1326
insert into t1 values ('あいうえおかきくけこさしすせそ');
1327
insert into t1 values ('さしすせそかきくけこあいうえお');
1328
select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%';
1331
select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ';
1336
create table t1 (a varchar(30) not null primary key)
1337
engine=innodb default character set utf8 collate utf8_bin;
1338
insert into t1 values ('あいうえおかきくけこさしすせそ');
1339
insert into t1 values ('さしすせそかきくけこあいうえお');
1340
select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%';
1343
select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ';
1348
CREATE TABLE t1 (id int PRIMARY KEY,
1349
a varchar(16) collate utf8_unicode_ci NOT NULL default '',
1351
f varchar(128) default 'XXX',
1353
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1354
INSERT INTO t1(id, a, b) VALUES
1355
(1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
1356
(4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
1357
(7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
1358
(10, 'eeeee', 40), (11, 'bbbbbb', 60);
1359
SELECT id, a, b FROM t1;
1372
SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
1378
SELECT id, a FROM t1 WHERE a='bbbbbb';
1382
SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b;
1389
a CHAR(13) DEFAULT '',
1391
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
1392
INSERT INTO t1 VALUES
1393
('Käli Käli 2-4'), ('Käli Käli 2-4'),
1394
('Käli Käli 2+4'), ('Käli Käli 2+4'),
1395
('Käli Käli 2-6'), ('Käli Käli 2-6');
1396
INSERT INTO t1 SELECT * FROM t1;
1398
a CHAR(13) DEFAULT '',
1400
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
1401
INSERT INTO t2 VALUES
1402
('Kali Kali 2-4'), ('Kali Kali 2-4'),
1403
('Kali Kali 2+4'), ('Kali Kali 2+4'),
1404
('Kali Kali 2-6'), ('Kali Kali 2-6');
1405
INSERT INTO t2 SELECT * FROM t2;
1406
SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';
1412
SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';
1418
EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';
1419
id select_type table type possible_keys key key_len ref rows Extra
1420
1 SIMPLE t1 range a a 53 NULL 4 Using where; Using index
1421
EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4';
1422
id select_type table type possible_keys key key_len ref rows Extra
1423
1 SIMPLE t1 ref a a 53 const 4 Using where; Using index
1424
EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';
1425
id select_type table type possible_keys key key_len ref rows Extra
1426
1 SIMPLE t2 range a a 14 NULL 4 Using where; Using index
1427
EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4';
1428
id select_type table type possible_keys key key_len ref rows Extra
1429
1 SIMPLE t2 ref a a 14 const 4 Using where; Using index
1432
a char(255) DEFAULT '',
1434
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
1435
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1436
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1439
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1440
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1446
a char(255) DEFAULT ''
1447
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
1448
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1449
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1450
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1454
ALTER TABLE t1 ADD KEY (a(10));
1455
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1462
id int(11) NOT NULL default '0',
1463
tid int(11) NOT NULL default '0',
1465
INDEX idx(tid, val(10))
1466
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
1467
INSERT INTO t1 VALUES
1468
(40988,72,'VOLN� ADSL'),(41009,72,'VOLN� ADSL'),
1469
(41032,72,'VOLN� ADSL'),(41038,72,'VOLN� ADSL'),
1470
(41063,72,'VOLN� ADSL'),(41537,72,'VOLN� ADSL Office'),
1471
(42141,72,'VOLN� ADSL'),(42565,72,'VOLN� ADSL Combi'),
1472
(42749,72,'VOLN� ADSL'),(44205,72,'VOLN� ADSL');
1473
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL';
1483
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';
1493
SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL';
1503
ALTER TABLE t1 DROP KEY idx;
1504
ALTER TABLE t1 ADD KEY idx (tid,val(11));
1505
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';
1516
create table t1(a char(200) collate utf8_unicode_ci NOT NULL default '')
1517
default charset=utf8 collate=utf8_unicode_ci;
1518
insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65'));
1519
explain select distinct a from t1;
1520
id select_type table type possible_keys key key_len ref rows Extra
1521
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary
1522
select distinct a from t1;
1525
explain select a from t1 group by a;
1526
id select_type table type possible_keys key key_len ref rows Extra
1527
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
1528
select a from t1 group by a;
1532
create table t1(a char(10)) default charset utf8;
1533
insert into t1 values ('123'), ('456');
1535
select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
1536
id select_type table type possible_keys key key_len ref rows Extra
1537
1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
1538
1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer
1539
select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
1544
CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8;
1545
INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa');
1552
SELECT DISTINCT id FROM t1;
1557
SELECT DISTINCT id FROM t1 ORDER BY id;
1564
a varchar(26) not null
1565
) default character set utf8;
1566
insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');
1569
abcdefghijklmnopqrstuvwxyz
1570
alter table t1 change a a varchar(20) character set utf8 not null;
1572
Warning 1265 Data truncated for column 'a' at row 1
1575
abcdefghijklmnopqrst
1576
alter table t1 change a a char(15) character set utf8 not null;
1578
Warning 1265 Data truncated for column 'a' at row 1
1582
alter table t1 change a a char(10) character set utf8 not null;
1584
Warning 1265 Data truncated for column 'a' at row 1
1588
alter table t1 change a a varchar(5) character set utf8 not null;
1590
Warning 1265 Data truncated for column 'a' at row 1
1596
a varchar(4000) not null
1597
) default character set utf8;
1598
insert into t1 values (repeat('a',4000));
1599
alter table t1 change a a varchar(3000) character set utf8 not null;
1601
Warning 1265 Data truncated for column 'a' at row 1
1602
select length(a) from t1;
1607
select hex(char(1 using utf8));
1608
hex(char(1 using utf8))
1610
select char(0xd1,0x8f using utf8);
1611
char(0xd1,0x8f using utf8)
1613
select char(0xd18f using utf8);
1614
char(0xd18f using utf8)
1616
select char(53647 using utf8);
1617
char(53647 using utf8)
1619
select char(0xff,0x8f using utf8);
1620
char(0xff,0x8f using utf8)
1623
Warning 1300 Invalid utf8 character string: 'FF8F'
1624
select convert(char(0xff,0x8f) using utf8);
1625
convert(char(0xff,0x8f) using utf8)
1628
Warning 1300 Invalid utf8 character string: 'FF8F'
1629
set sql_mode=traditional;
1630
select char(0xff,0x8f using utf8);
1631
char(0xff,0x8f using utf8)
1634
Error 1300 Invalid utf8 character string: 'FF8F'
1635
select char(195 using utf8);
1636
char(195 using utf8)
1639
Error 1300 Invalid utf8 character string: 'C3'
1640
select char(196 using utf8);
1641
char(196 using utf8)
1644
Error 1300 Invalid utf8 character string: 'C4'
1645
select char(2557 using utf8);
1646
char(2557 using utf8)
1649
Error 1300 Invalid utf8 character string: 'FD'
1650
select convert(char(0xff,0x8f) using utf8);
1651
convert(char(0xff,0x8f) using utf8)
1654
Error 1300 Invalid utf8 character string: 'FF8F'
1655
select hex(convert(char(2557 using latin1) using utf8));
1656
hex(convert(char(2557 using latin1) using utf8))
1658
select hex(char(195));
1661
select hex(char(196));
1664
select hex(char(2557));
1668
create table t1 (a char(1)) default character set utf8;
1669
create table t2 (a char(1)) default character set utf8;
1670
insert into t1 values('a'),('a'),(0xE38182),(0xE38182);
1671
insert into t1 values('i'),('i'),(0xE38184),(0xE38184);
1672
select * from t1 union distinct select * from t2;
1680
create table t1 (a char(10), b varchar(10));
1681
insert into t1 values ('bar','kostja');
1682
insert into t1 values ('kostja','bar');
1683
prepare my_stmt from "select * from t1 where a=?";
1685
execute my_stmt using @a;
1689
execute my_stmt using @a;
1693
execute my_stmt using @a;
1695
drop table if exists t1;
1696
drop table if exists t1;
1697
drop view if exists v1, v2;
1699
create table t1(col1 varchar(12) character set utf8 collate utf8_unicode_ci);
1700
insert into t1 values('t1_val');
1701
create view v1 as select 'v1_val' as col1;
1702
select coercibility(col1), collation(col1) from v1;
1703
coercibility(col1) collation(col1)
1705
create view v2 as select col1 from v1 union select col1 from t1;
1706
select coercibility(col1), collation(col1)from v2;
1707
coercibility(col1) collation(col1)
1711
create view v1 as select 'v1_val' collate utf8_swedish_ci as col1;
1712
select coercibility(col1), collation(col1) from v1;
1713
coercibility(col1) collation(col1)
1715
create view v2 as select col1 from v1 union select col1 from t1;
1716
select coercibility(col1), collation(col1) from v2;
1717
coercibility(col1) collation(col1)
1723
create table t1 (a varchar(10) character set latin1, b int);
1724
insert into t1 values ('a',1);
1725
select concat(a, if(b>10, N'x', N'y')) from t1;
1726
concat(a, if(b>10, N'x', N'y'))
1728
select concat(a, if(b>10, N'æ', N'ß')) from t1;
1729
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
1732
create table t1 (a varchar(10) character set latin1, b int);
1733
insert into t1 values ('a',1);
1734
select concat(a, if(b>10, _utf8'x', _utf8'y')) from t1;
1735
concat(a, if(b>10, _utf8'x', _utf8'y'))
1737
select concat(a, if(b>10, _utf8'æ', _utf8'ß')) from t1;
1738
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
1741
create table t1 (a varchar(10) character set latin1, b int);
1742
insert into t1 values ('a',1);
1743
select concat(a, if(b>10, _utf8 0x78, _utf8 0x79)) from t1;
1744
concat(a, if(b>10, _utf8 0x78, _utf8 0x79))
1746
select concat(a, if(b>10, _utf8 0xC3A6, _utf8 0xC3AF)) from t1;
1747
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
1750
create table t1 (a varchar(10) character set latin1, b int);
1751
insert into t1 values ('a',1);
1752
select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1;
1753
concat(a, if(b>10, 'x' 'x', 'y' 'y'))
1755
select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1;
1756
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
1759
colA int(11) NOT NULL,
1760
colB varchar(255) character set utf8 NOT NULL,
1762
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1763
INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar');
1765
colA int(11) NOT NULL,
1766
colB varchar(255) character set utf8 NOT NULL,
1767
KEY bad (colA,colB(3))
1768
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1769
INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar');
1770
SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB
1776
SELECT 'н1234567890' UNION SELECT _binary '1';
1780
SELECT 'н1234567890' UNION SELECT 1;
1784
SELECT '1' UNION SELECT 'н1234567890';
1788
SELECT 1 UNION SELECT 'н1234567890';
1792
CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8;
1793
CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);
1794
INSERT INTO t1 (c) VALUES ('н1234567890');
1795
INSERT INTO t2 (b, i) VALUES ('1', 1);
1796
SELECT c FROM t1 UNION SELECT b FROM t2;
1800
SELECT c FROM t1 UNION SELECT i FROM t2;
1804
SELECT b FROM t2 UNION SELECT c FROM t1;
1808
SELECT i FROM t2 UNION SELECT c FROM t1;
1813
set sql_mode=traditional;
1814
select hex(char(0xFF using utf8));
1815
hex(char(0xFF using utf8))
1818
Error 1300 Invalid utf8 character string: 'FF'
1819
select hex(convert(0xFF using utf8));
1820
hex(convert(0xFF using utf8))
1823
Error 1300 Invalid utf8 character string: 'FF'
1824
select hex(_utf8 0x616263FF);
1825
ERROR HY000: Invalid utf8 character string: 'FF'
1826
select hex(_utf8 X'616263FF');
1827
ERROR HY000: Invalid utf8 character string: 'FF'
1828
select hex(_utf8 B'001111111111');
1829
ERROR HY000: Invalid utf8 character string: 'FF'
1830
select (_utf8 X'616263FF');
1831
ERROR HY000: Invalid utf8 character string: 'FF'
1832
set sql_mode=default;
1833
select hex(char(0xFF using utf8));
1834
hex(char(0xFF using utf8))
1837
Warning 1300 Invalid utf8 character string: 'FF'
1838
select hex(convert(0xFF using utf8));
1839
hex(convert(0xFF using utf8))
1842
Warning 1300 Invalid utf8 character string: 'FF'
1843
select hex(_utf8 0x616263FF);
1844
ERROR HY000: Invalid utf8 character string: 'FF'
1845
select hex(_utf8 X'616263FF');
1846
ERROR HY000: Invalid utf8 character string: 'FF'
1847
select hex(_utf8 B'001111111111');
1848
ERROR HY000: Invalid utf8 character string: 'FF'
1849
select (_utf8 X'616263FF');
1850
ERROR HY000: Invalid utf8 character string: 'FF'
1851
DROP TABLE IF EXISTS t1;
1853
predicted_order int NOT NULL,
1854
utf8_encoding VARCHAR(10) NOT NULL
1855
) CHARACTER SET utf8;
1856
INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682');
1857
SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci;
1858
predicted_order hex(utf8_encoding)
1931
73 E0B6BBE0B78AE2808D
1958
100 E0B78AE2808DE0B6BA
1959
101 E0B78AE2808DE0B6BB
1963
select @@collation_connection;
1964
@@collation_connection
1966
select hex(weight_string('a'));
1967
hex(weight_string('a'))
1969
select hex(weight_string('A'));
1970
hex(weight_string('A'))
1972
select hex(weight_string('abc'));
1973
hex(weight_string('abc'))
1975
select hex(weight_string('abc' as char(2)));
1976
hex(weight_string('abc' as char(2)))
1978
select hex(weight_string('abc' as char(3)));
1979
hex(weight_string('abc' as char(3)))
1981
select hex(weight_string('abc' as char(5)));
1982
hex(weight_string('abc' as char(5)))
1983
00410042004300200020
1984
select @@collation_connection;
1985
@@collation_connection
1987
select hex(weight_string('a' LEVEL 1));
1988
hex(weight_string('a' LEVEL 1))
1990
select hex(weight_string('A' LEVEL 1));
1991
hex(weight_string('A' LEVEL 1))
1993
select hex(weight_string('abc' LEVEL 1));
1994
hex(weight_string('abc' LEVEL 1))
1996
select hex(weight_string('abc' as char(2) LEVEL 1));
1997
hex(weight_string('abc' as char(2) LEVEL 1))
1999
select hex(weight_string('abc' as char(3) LEVEL 1));
2000
hex(weight_string('abc' as char(3) LEVEL 1))
2002
select hex(weight_string('abc' as char(5) LEVEL 1));
2003
hex(weight_string('abc' as char(5) LEVEL 1))
2004
00410042004300200020
2005
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
2006
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
2007
20002000430042004100
2008
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
2009
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
2010
FFBEFFBDFFBCFFDFFFDF
2011
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
2012
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
2013
DFFFDFFFBCFFBDFFBEFF
2014
set @@collation_connection=utf8_bin;
2015
select @@collation_connection;
2016
@@collation_connection
2018
select hex(weight_string('a'));
2019
hex(weight_string('a'))
2021
select hex(weight_string('A'));
2022
hex(weight_string('A'))
2024
select hex(weight_string('abc'));
2025
hex(weight_string('abc'))
2027
select hex(weight_string('abc' as char(2)));
2028
hex(weight_string('abc' as char(2)))
2030
select hex(weight_string('abc' as char(3)));
2031
hex(weight_string('abc' as char(3)))
2033
select hex(weight_string('abc' as char(5)));
2034
hex(weight_string('abc' as char(5)))
2035
00610062006300200020
2036
select @@collation_connection;
2037
@@collation_connection
2039
select hex(weight_string('a' LEVEL 1));
2040
hex(weight_string('a' LEVEL 1))
2042
select hex(weight_string('A' LEVEL 1));
2043
hex(weight_string('A' LEVEL 1))
2045
select hex(weight_string('abc' LEVEL 1));
2046
hex(weight_string('abc' LEVEL 1))
2048
select hex(weight_string('abc' as char(2) LEVEL 1));
2049
hex(weight_string('abc' as char(2) LEVEL 1))
2051
select hex(weight_string('abc' as char(3) LEVEL 1));
2052
hex(weight_string('abc' as char(3) LEVEL 1))
2054
select hex(weight_string('abc' as char(5) LEVEL 1));
2055
hex(weight_string('abc' as char(5) LEVEL 1))
2056
00610062006300200020
2057
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
2058
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
2059
20002000630062006100
2060
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
2061
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
2062
FF9EFF9DFF9CFFDFFFDF
2063
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
2064
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
2065
DFFFDFFF9CFF9DFF9EFF
2066
set max_sort_length=5;
2067
select @@max_sort_length;
2070
create table t1 (a varchar(128) character set utf8 collate utf8_general_ci);
2071
insert into t1 values ('a'),('b'),('c');
2072
select * from t1 order by a;
2077
alter table t1 modify a varchar(128) character set utf8 collate utf8_bin;
2078
select * from t1 order by a;
2084
set max_sort_length=default;
2085
DROP TABLE IF EXISTS t1;
2087
predicted_order int NOT NULL,
2088
utf8_encoding VARCHAR(10) NOT NULL
2089
) CHARACTER SET utf8;
2090
INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682');
2091
SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci;
2092
predicted_order hex(utf8_encoding)
2165
73 E0B6BBE0B78AE2808D
2192
100 E0B78AE2808DE0B6BA
2193
101 E0B78AE2808DE0B6BB
2195
CREATE DATABASE `𐀀`;
2196
ERROR HY000: Invalid identifier character string: '𐀀'
2197
CREATE TABLE `𐀀` (a int);
2198
ERROR HY000: Invalid identifier character string: '𐀀'
2199
CREATE TABLE test.t1 SELECT '𐀀';
2200
ERROR 42000: Incorrect column name '𐀀'
2202
ERROR HY000: Invalid identifier character string: '𐀀'