1
DROP TABLE IF EXISTS t1;
3
SET character_set_connection=utf16;
4
select hex('a'), hex('a ');
7
select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
8
'a' = 'a' 'a' = 'a ' 'a ' = 'a'
10
select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
11
'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a'
13
select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
14
'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0'
16
select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
17
'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a '
19
select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
20
'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0'
22
select 'a a' > 'a', 'a \0' < 'a';
23
'a a' > 'a' 'a \0' < 'a'
25
select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a';
26
binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a'
28
select hex(_utf16 0x44);
31
select hex(_utf16 0x3344);
34
select hex(_utf16 0x113344);
37
CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf16;
38
INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004');
39
SELECT hex(word) FROM t1 ORDER BY word;
43
SELECT hex(word2) FROM t1 ORDER BY word2;
48
INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020');
49
SELECT hex(word) FROM t1 ORDER BY word;
53
SELECT hex(word2) FROM t1 ORDER BY word2;
58
SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'0421'));
59
hex(LPAD(_utf16 X'0420',10,_utf16 X'0421'))
60
0421042104210421042104210421042104210420
61
SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'04210422'));
62
hex(LPAD(_utf16 X'0420',10,_utf16 X'04210422'))
63
0421042204210422042104220421042204210420
64
SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'042104220423'));
65
hex(LPAD(_utf16 X'0420',10,_utf16 X'042104220423'))
66
0421042204230421042204230421042204230420
67
SELECT hex(LPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423'));
68
hex(LPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423'))
69
0420042104220423042404250426042704280429
70
SELECT hex(LPAD(_utf16 X'D800DC00', 10, _utf16 X'0421'));
71
hex(LPAD(_utf16 X'D800DC00', 10, _utf16 X'0421'))
72
042104210421042104210421042104210421D800DC00
73
SELECT hex(LPAD(_utf16 X'0421', 10, _utf16 X'D800DC00'));
74
hex(LPAD(_utf16 X'0421', 10, _utf16 X'D800DC00'))
75
D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC000421
76
SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'0421'));
77
hex(RPAD(_utf16 X'0420',10,_utf16 X'0421'))
78
0420042104210421042104210421042104210421
79
SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'04210422'));
80
hex(RPAD(_utf16 X'0420',10,_utf16 X'04210422'))
81
0420042104220421042204210422042104220421
82
SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'042104220423'));
83
hex(RPAD(_utf16 X'0420',10,_utf16 X'042104220423'))
84
0420042104220423042104220423042104220423
85
SELECT hex(RPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423'));
86
hex(RPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423'))
87
0420042104220423042404250426042704280429
88
SELECT hex(RPAD(_utf16 X'D800DC00', 10, _utf16 X'0421'));
89
hex(RPAD(_utf16 X'D800DC00', 10, _utf16 X'0421'))
90
D800DC00042104210421042104210421042104210421
91
SELECT hex(RPAD(_utf16 X'0421', 10, _utf16 X'D800DC00'));
92
hex(RPAD(_utf16 X'0421', 10, _utf16 X'D800DC00'))
93
0421D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00
94
CREATE TABLE t1 SELECT
95
LPAD(_utf16 X'0420',10,_utf16 X'0421') l,
96
RPAD(_utf16 X'0420',10,_utf16 X'0421') r;
99
t1 CREATE TABLE `t1` (
100
`l` varchar(10) CHARACTER SET utf16 NOT NULL DEFAULT '',
101
`r` varchar(10) CHARACTER SET utf16 NOT NULL DEFAULT ''
102
) ENGINE=MyISAM DEFAULT CHARSET=latin1
103
select hex(l), hex(r) from t1;
105
0421042104210421042104210421042104210420 0420042104210421042104210421042104210421
107
create table t1 (f1 char(30));
108
insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000");
109
select lpad(f1, 12, "-o-/") from t1;
117
SET character_set_connection=utf16;
118
select @@collation_connection;
119
@@collation_connection
121
create table t1 as select repeat(' ',10) as a union select null;
122
alter table t1 add key(a);
123
show create table t1;
125
t1 CREATE TABLE `t1` (
126
`a` varchar(10) CHARACTER SET utf16 DEFAULT NULL,
128
) ENGINE=MyISAM DEFAULT CHARSET=latin1
129
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
130
explain select * from t1 where a like 'abc%';
131
id select_type table type possible_keys key key_len ref rows Extra
132
1 SIMPLE t1 range a a 43 NULL 1 Using where; Using index
133
explain select * from t1 where a like concat('abc','%');
134
id select_type table type possible_keys key key_len ref rows Extra
135
1 SIMPLE t1 range a a 43 NULL 1 Using where; Using index
136
select * from t1 where a like "abc%";
140
select * from t1 where a like concat("abc","%");
144
select * from t1 where a like "ABC%";
148
select * from t1 where a like "test%";
151
select * from t1 where a like "te_t";
154
select * from t1 where a like "%a%";
159
select * from t1 where a like "%abcd%";
162
select * from t1 where a like "%abc\d%";
166
select 'AA' like 'AA';
169
select 'AA' like 'A%A';
172
select 'AA' like 'A%%A';
175
select 'AA' like 'AA%';
178
select 'AA' like '%AA%';
181
select 'AA' like '%A';
184
select 'AA' like '%AA';
187
select 'AA' like 'A%A%';
190
select 'AA' like '_%_%';
193
select 'AA' like '%A%A';
196
select 'AAA'like 'A%A%A';
199
select 'AZ' like 'AZ';
202
select 'AZ' like 'A%Z';
205
select 'AZ' like 'A%%Z';
208
select 'AZ' like 'AZ%';
211
select 'AZ' like '%AZ%';
214
select 'AZ' like '%Z';
217
select 'AZ' like '%AZ';
220
select 'AZ' like 'A%Z%';
223
select 'AZ' like '_%_%';
226
select 'AZ' like '%A%Z';
229
select 'AZ' like 'A_';
232
select 'AZ' like '_Z';
235
select 'AMZ'like 'A%M%Z';
239
SET character_set_connection=utf16;
240
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16);
241
INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА');
242
INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж');
243
INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж');
244
INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ');
245
SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a;
265
SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a;
285
SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a;
305
SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf16_bin ORDER BY BINARY a;
310
CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))
311
ENGINE=MyISAM CHARACTER SET utf16;
312
INSERT INTO t1 (word) VALUES ("cat");
313
SELECT * FROM t1 WHERE word LIKE "c%";
316
SELECT * FROM t1 WHERE word LIKE "ca_";
319
SELECT * FROM t1 WHERE word LIKE "cat";
322
SELECT * FROM t1 WHERE word LIKE _utf16 x'00630025';
325
SELECT * FROM t1 WHERE word LIKE _utf16 x'00630061005F';
329
select insert(_utf16 0x006100620063,10,2,_utf16 0x006400650066);
330
insert(_utf16 0x006100620063,10,2,_utf16 0x006400650066)
332
select insert(_utf16 0x006100620063,1,2,_utf16 0x006400650066);
333
insert(_utf16 0x006100620063,1,2,_utf16 0x006400650066)
338
bar INT(11) default 0,
342
COLLATE utf16_general_ci ;
343
INSERT INTO t1 (word) VALUES ("aar");
344
INSERT INTO t1 (word) VALUES ("a");
345
INSERT INTO t1 (word) VALUES ("aardvar");
346
INSERT INTO t1 (word) VALUES ("aardvark");
347
INSERT INTO t1 (word) VALUES ("aardvara");
348
INSERT INTO t1 (word) VALUES ("aardvarz");
349
EXPLAIN SELECT * FROM t1 ORDER BY word;
350
id select_type table type possible_keys key key_len ref rows Extra
351
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
352
SELECT * FROM t1 ORDER BY word;
360
EXPLAIN SELECT word FROM t1 ORDER BY word;
361
id select_type table type possible_keys key key_len ref rows Extra
362
1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index
363
SELECT word FROM t1 ORDER by word;
377
COLLATE utf16_general_ci;
378
INSERT INTO t1 (word) VALUES ("aar");
379
INSERT INTO t1 (word) VALUES ("a");
380
INSERT INTO t1 (word) VALUES ("aardvar");
381
INSERT INTO t1 (word) VALUES ("aardvark");
382
INSERT INTO t1 (word) VALUES ("aardvara");
383
INSERT INTO t1 (word) VALUES ("aardvarz");
384
EXPLAIN SELECT * FROM t1 ORDER BY WORD;
385
id select_type table type possible_keys key key_len ref rows Extra
386
1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index
387
SELECT * FROM t1 ORDER BY word;
398
bar INT(11) AUTO_INCREMENT,
402
COLLATE utf16_general_ci ;
403
INSERT INTO t1 (word) VALUES ("aar");
404
INSERT INTO t1 (word) VALUES ("a" );
405
INSERT INTO t1 (word) VALUES ("aardvar");
406
INSERT INTO t1 (word) VALUES ("aardvark");
407
INSERT INTO t1 (word) VALUES ("aardvara");
408
INSERT INTO t1 (word) VALUES ("aardvarz");
409
EXPLAIN SELECT * FROM t1 ORDER BY word;
410
id select_type table type possible_keys key key_len ref rows Extra
411
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
412
SELECT * FROM t1 ORDER BY word;
420
EXPLAIN SELECT word FROM t1 ORDER BY word;
421
id select_type table type possible_keys key key_len ref rows Extra
422
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
423
SELECT word FROM t1 ORDER BY word;
432
SELECT hex(cast(0xAA as char character set utf16));
433
hex(cast(0xAA as char character set utf16))
435
SELECT hex(convert(0xAA using utf16));
436
hex(convert(0xAA using utf16))
438
CREATE TABLE t1 (a char(10) character set utf16);
439
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
440
SELECT HEX(a) FROM t1;
448
CREATE TABLE t1 (a varchar(10) character set utf16);
449
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
450
SELECT HEX(a) FROM t1;
458
CREATE TABLE t1 (a text character set utf16);
459
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
460
SELECT HEX(a) FROM t1;
468
CREATE TABLE t1 (a mediumtext character set utf16);
469
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
470
SELECT HEX(a) FROM t1;
478
CREATE TABLE t1 (a longtext character set utf16);
479
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
480
SELECT HEX(a) FROM t1;
488
create table t1(a char(1)) default charset utf16;
489
insert into t1 values ('a'),('b'),('c');
490
alter table t1 modify a char(5);
491
select a, hex(a) from t1;
498
set @str1 = 'select ?';
499
set @str2 = convert(@str1 using utf16);
500
prepare stmt1 from @str2;
501
execute stmt1 using @ivar;
505
create table t1 (a enum('x','y','z') character set utf16);
506
show create table t1;
508
t1 CREATE TABLE `t1` (
509
`a` enum('x','y','z') CHARACTER SET utf16 DEFAULT NULL
510
) ENGINE=MyISAM DEFAULT CHARSET=latin1
511
insert into t1 values ('x');
512
insert into t1 values ('y');
513
insert into t1 values ('z');
514
select a, hex(a) from t1 order by a;
519
alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf16;
520
show create table t1;
522
t1 CREATE TABLE `t1` (
523
`a` enum('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf16 DEFAULT NULL
524
) ENGINE=MyISAM DEFAULT CHARSET=latin1
525
insert into t1 values ('D');
526
insert into t1 values ('E ');
527
insert into t1 values ('ä');
528
insert into t1 values ('ö');
529
insert into t1 values ('ü');
530
select a, hex(a) from t1 order by a;
541
create table t1 (a set ('x','y','z','ä','ö','ü') character set utf16);
542
show create table t1;
544
t1 CREATE TABLE `t1` (
545
`a` set('x','y','z','ä','ö','ü') CHARACTER SET utf16 DEFAULT NULL
546
) ENGINE=MyISAM DEFAULT CHARSET=latin1
547
insert into t1 values ('x');
548
insert into t1 values ('y');
549
insert into t1 values ('z');
550
insert into t1 values ('x,y');
551
insert into t1 values ('x,y,z,ä,ö,ü');
552
select a, hex(a) from t1 order by a;
558
x,y,z,ä,ö,ü 0078002C0079002C007A002C00E4002C00F6002C00FC
560
create table t1(a enum('a','b','c')) default character set utf16;
561
insert into t1 values('a'),('b'),('c');
562
alter table t1 add b char(1);
565
select * from t1 order by a;
572
SET collation_connection='utf16_general_ci';
573
create table t1 select repeat('a',4000) a;
575
insert into t1 values ('a'), ('a '), ('a\t');
576
select collation(a),hex(a) from t1 order by a;
578
utf16_general_ci 00610009
579
utf16_general_ci 0061
580
utf16_general_ci 00610020
582
select @@collation_connection;
583
@@collation_connection
585
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
586
insert into t1 values('abcdef');
587
insert into t1 values('_bcdef');
588
insert into t1 values('a_cdef');
589
insert into t1 values('ab_def');
590
insert into t1 values('abc_ef');
591
insert into t1 values('abcd_f');
592
insert into t1 values('abcde_');
593
select c1 as c1u from t1 where c1 like 'ab\_def';
596
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
601
SET collation_connection='utf16_bin';
602
create table t1 select repeat('a',4000) a;
604
insert into t1 values ('a'), ('a '), ('a\t');
605
select collation(a),hex(a) from t1 order by a;
611
select @@collation_connection;
612
@@collation_connection
614
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
615
insert into t1 values('abcdef');
616
insert into t1 values('_bcdef');
617
insert into t1 values('a_cdef');
618
insert into t1 values('ab_def');
619
insert into t1 values('abc_ef');
620
insert into t1 values('abcd_f');
621
insert into t1 values('abcde_');
622
select c1 as c1u from t1 where c1 like 'ab\_def';
625
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
629
select hex(substr(_utf16 0x00e400e50068,1));
630
hex(substr(_utf16 0x00e400e50068,1))
632
select hex(substr(_utf16 0x00e400e50068,2));
633
hex(substr(_utf16 0x00e400e50068,2))
635
select hex(substr(_utf16 0x00e400e50068,3));
636
hex(substr(_utf16 0x00e400e50068,3))
638
select hex(substr(_utf16 0x00e400e50068,-1));
639
hex(substr(_utf16 0x00e400e50068,-1))
641
select hex(substr(_utf16 0x00e400e50068,-2));
642
hex(substr(_utf16 0x00e400e50068,-2))
644
select hex(substr(_utf16 0x00e400e50068,-3));
645
hex(substr(_utf16 0x00e400e50068,-3))
647
select hex(substr(_utf16 0x00e400e5D800DC00,1));
648
hex(substr(_utf16 0x00e400e5D800DC00,1))
650
select hex(substr(_utf16 0x00e400e5D800DC00,2));
651
hex(substr(_utf16 0x00e400e5D800DC00,2))
653
select hex(substr(_utf16 0x00e400e5D800DC00,3));
654
hex(substr(_utf16 0x00e400e5D800DC00,3))
656
select hex(substr(_utf16 0x00e400e5D800DC00,-1));
657
hex(substr(_utf16 0x00e400e5D800DC00,-1))
659
select hex(substr(_utf16 0x00e400e5D800DC00,-2));
660
hex(substr(_utf16 0x00e400e5D800DC00,-2))
662
select hex(substr(_utf16 0x00e400e5D800DC00,-3));
663
hex(substr(_utf16 0x00e400e5D800DC00,-3))
666
create table t1 (utext varchar(20) character set utf16);
667
insert into t1 values ("lily");
668
insert into t1 values ("river");
669
prepare stmt from 'select utext from t1 where utext like ?';
671
execute stmt using @param1;
675
execute stmt using @param1;
679
select utext from t1 where utext like '%%';
684
deallocate prepare stmt;
686
a char(10) character set utf16 not null,
689
insert into t1 values (repeat(0x201f, 10));
690
insert into t1 values (repeat(0x2020, 10));
691
insert into t1 values (repeat(0x2021, 10));
692
explain select hex(a) from t1 order by a;
693
id select_type table type possible_keys key key_len ref rows Extra
694
1 SIMPLE t1 index NULL a 40 NULL 3 Using index
695
select hex(a) from t1 order by a;
697
201F201F201F201F201F201F201F201F201F201F
698
2020202020202020202020202020202020202020
699
2021202120212021202120212021202120212021
700
alter table t1 drop index a;
701
select hex(a) from t1 order by a;
703
201F201F201F201F201F201F201F201F201F201F
704
2020202020202020202020202020202020202020
705
2021202120212021202120212021202120212021
708
status enum('active','passive') character set utf16 collate utf16_general_ci
709
NOT NULL default 'passive'
711
SHOW CREATE TABLE t1;
713
t1 CREATE TABLE `t1` (
714
`status` enum('active','passive') CHARACTER SET utf16 NOT NULL DEFAULT 'passive'
715
) ENGINE=MyISAM DEFAULT CHARSET=latin1
716
ALTER TABLE t1 ADD a int NOT NULL AFTER status;
717
SHOW CREATE TABLE t1;
719
t1 CREATE TABLE `t1` (
720
`status` enum('active','passive') CHARACTER SET utf16 NOT NULL DEFAULT 'passive',
722
) ENGINE=MyISAM DEFAULT CHARSET=latin1
725
CREATE TABLE t1 (a varchar(64) character set utf16, b decimal(10,3));
726
INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
728
SELECT *, hex(a) FROM t1;
730
1.1 1.100 0031002E0031
731
2.1 2.100 0032002E0031
733
create table t1 (utext varchar(20) character set utf16);
734
insert into t1 values ("lily");
735
insert into t1 values ("river");
736
prepare stmt from 'select utext from t1 where utext like ?';
738
execute stmt using @param1;
742
execute stmt using @param1;
746
select utext from t1 where utext like '%%';
751
deallocate prepare stmt;
753
set character_set_connection=utf16;
754
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
755
soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb')
757
select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb'));
758
hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb'))
759
0048003000300030 00480034003100340032 004900350031003200330031
760
select 'mood' sounds like 'mud';
761
'mood' sounds like 'mud'
763
select hex(soundex(_utf16 0x041004110412));
764
hex(soundex(_utf16 0x041004110412))
766
select hex(soundex(_utf16 0x00BF00C0));
767
hex(soundex(_utf16 0x00BF00C0))
770
create table t1(a blob, b text charset utf16);
771
select data_type, character_octet_length, character_maximum_length
772
from information_schema.columns where table_name='t1';
773
data_type character_octet_length character_maximum_length
778
set collation_connection=utf16_general_ci;
779
select @@collation_connection;
780
@@collation_connection
782
select hex(weight_string('a'));
783
hex(weight_string('a'))
785
select hex(weight_string('A'));
786
hex(weight_string('A'))
788
select hex(weight_string('abc'));
789
hex(weight_string('abc'))
791
select hex(weight_string('abc' as char(2)));
792
hex(weight_string('abc' as char(2)))
794
select hex(weight_string('abc' as char(3)));
795
hex(weight_string('abc' as char(3)))
797
select hex(weight_string('abc' as char(5)));
798
hex(weight_string('abc' as char(5)))
800
select hex(weight_string(_utf16 0xD800DC00));
801
hex(weight_string(_utf16 0xD800DC00))
803
select hex(weight_string(_utf16 0xD800DC01));
804
hex(weight_string(_utf16 0xD800DC01))
806
select @@collation_connection;
807
@@collation_connection
809
select hex(weight_string('a' LEVEL 1));
810
hex(weight_string('a' LEVEL 1))
812
select hex(weight_string('A' LEVEL 1));
813
hex(weight_string('A' LEVEL 1))
815
select hex(weight_string('abc' LEVEL 1));
816
hex(weight_string('abc' LEVEL 1))
818
select hex(weight_string('abc' as char(2) LEVEL 1));
819
hex(weight_string('abc' as char(2) LEVEL 1))
821
select hex(weight_string('abc' as char(3) LEVEL 1));
822
hex(weight_string('abc' as char(3) LEVEL 1))
824
select hex(weight_string('abc' as char(5) LEVEL 1));
825
hex(weight_string('abc' as char(5) LEVEL 1))
827
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
828
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
830
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
831
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
833
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
834
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
836
set collation_connection=utf16_bin;
837
select @@collation_connection;
838
@@collation_connection
840
select hex(weight_string('a'));
841
hex(weight_string('a'))
843
select hex(weight_string('A'));
844
hex(weight_string('A'))
846
select hex(weight_string('abc'));
847
hex(weight_string('abc'))
849
select hex(weight_string('abc' as char(2)));
850
hex(weight_string('abc' as char(2)))
852
select hex(weight_string('abc' as char(3)));
853
hex(weight_string('abc' as char(3)))
855
select hex(weight_string('abc' as char(5)));
856
hex(weight_string('abc' as char(5)))
858
select @@collation_connection;
859
@@collation_connection
861
select hex(weight_string('a' LEVEL 1));
862
hex(weight_string('a' LEVEL 1))
864
select hex(weight_string('A' LEVEL 1));
865
hex(weight_string('A' LEVEL 1))
867
select hex(weight_string('abc' LEVEL 1));
868
hex(weight_string('abc' LEVEL 1))
870
select hex(weight_string('abc' as char(2) LEVEL 1));
871
hex(weight_string('abc' as char(2) LEVEL 1))
873
select hex(weight_string('abc' as char(3) LEVEL 1));
874
hex(weight_string('abc' as char(3) LEVEL 1))
876
select hex(weight_string('abc' as char(5) LEVEL 1));
877
hex(weight_string('abc' as char(5) LEVEL 1))
879
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
880
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
882
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
883
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
885
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
886
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
889
set collation_connection=utf16_general_ci;
890
select position('bb' in 'abba');
891
position('bb' in 'abba')
893
create table t1 (a varchar(10) character set utf16) engine=heap;
894
insert into t1 values ('a'),('A'),('b'),('B');
895
select * from t1 where a='a' order by binary a;
899
select hex(min(binary a)),count(*) from t1 group by a;
900
hex(min(binary a)) count(*)
904
select char_length('abcd'), octet_length('abcd');
905
char_length('abcd') octet_length('abcd')
907
select char_length(_utf16 0xD800DC00), octet_length(_utf16 0xD800DC00);
908
char_length(_utf16 0xD800DC00) octet_length(_utf16 0xD800DC00)
910
select char_length(_utf16 0xD87FDFFF), octet_length(_utf16 0xD87FDFFF);
911
char_length(_utf16 0xD87FDFFF) octet_length(_utf16 0xD87FDFFF)
913
select left('abcd',2);
916
select hex(left(_utf16 0xD800DC00D87FDFFF, 1));
917
hex(left(_utf16 0xD800DC00D87FDFFF, 1))
919
select hex(right(_utf16 0xD800DC00D87FDFFF, 1));
920
hex(right(_utf16 0xD800DC00D87FDFFF, 1))
922
create table t1 (a varchar(10) character set utf16);
923
insert into t1 values (_utf16 0xD800);
924
ERROR HY000: Invalid utf16 character string: 'D800'
925
insert into t1 values (_utf16 0xDC00);
926
ERROR HY000: Invalid utf16 character string: 'DC00'
927
insert into t1 values (_utf16 0xD800D800);
928
ERROR HY000: Invalid utf16 character string: 'D800D8'
929
insert into t1 values (_utf16 0xD800E800);
930
ERROR HY000: Invalid utf16 character string: 'D800E8'
931
insert into t1 values (_utf16 0xD8000800);
932
ERROR HY000: Invalid utf16 character string: 'D80008'
933
insert into t1 values (_utf16 0xD800DC00);
934
insert into t1 values (_utf16 0xD800DCFF);
935
insert into t1 values (_utf16 0xDBFFDC00);
936
insert into t1 values (_utf16 0xDBFFDCFF);
937
select hex(a) from t1;
944
create table t1 (s1 varchar(50) character set ucs2);
945
insert into t1 values (0xdf84);
946
alter table t1 modify column s1 varchar(50) character set utf16;
948
Warning 1366 Incorrect string value: '\xDF\x84' for column 's1' at row 1
949
select hex(s1) from t1;
953
create table t1 (s1 varchar(5) character set ucs2, s2 varchar(5) character set utf16);
954
insert into t1 (s1) values (0xdf84);
955
update t1 set s2 = s1;
957
Warning 1366 Incorrect string value: '\xDF\x84' for column 's2' at row 1
958
select hex(s2) from t1;
962
create table t1 (a char(10)) character set utf16;
963
insert into t1 values ('a ');
964
select hex(a) from t1;
968
select upper('abcd'), lower('ABCD');
969
upper('abcd') lower('ABCD')
971
create table t1 (a varchar(10) character set utf16);
972
insert into t1 values (123456);
973
select a, hex(a) from t1;
975
123456 003100320033003400350036
977
select hex(soundex('a'));
980
create table t1 (a enum ('a','b','c')) character set utf16;
981
insert into t1 values ('1');
987
select hex(conv(convert('123' using utf16), -10, 16));
988
hex(conv(convert('123' using utf16), -10, 16))
990
select hex(conv(convert('123' using utf16), 10, 16));
991
hex(conv(convert('123' using utf16), 10, 16))
994
set character_set_connection=utf16;
998
select 1.1 + '1.2xxx';
1002
Warning 1292 Truncated incorrect DOUBLE value: ''
1003
select left('aaa','1');
1006
create table t1 (a int);
1007
insert into t1 values ('-1234.1e2');
1008
insert into t1 values ('-1234.1e2xxxx');
1010
Warning 1265 Data truncated for column 'a' at row 1
1011
insert into t1 values ('-1234.1e2 ');
1018
create table t1 (a int);
1019
insert into t1 values ('1 ');
1020
insert into t1 values ('1 x');
1022
Warning 1265 Data truncated for column 'a' at row 1
1028
create table t1 (a varchar(17000) character set utf16);
1030
Note 1246 Converting column 'a' from VARCHAR to TEXT
1031
show create table t1;
1033
t1 CREATE TABLE `t1` (
1034
`a` mediumtext CHARACTER SET utf16
1035
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1037
create table t1 (a varchar(250) character set utf16 primary key);
1038
show create table t1;
1040
t1 CREATE TABLE `t1` (
1041
`a` varchar(250) CHARACTER SET utf16 NOT NULL,
1043
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1045
create table t1 (a varchar(334) character set utf16 primary key);
1046
ERROR 42000: Specified key was too long; max key length is 1332 bytes
1047
create table t1 (a char(1) character set utf16);
1048
insert into t1 values (0xD800DC00),(0xD800DCFF),(0xDB7FDC00),(0xDB7FDCFF);
1049
insert into t1 values (0x00C0), (0x00FF),(0xE000), (0xFFFF);
1050
select hex(a), hex(@a:=convert(a using utf8)), hex(convert(@a using utf16)) from t1;
1051
hex(a) hex(@a:=convert(a using utf8)) hex(convert(@a using utf16))
1052
D800DC00 F0908080 D800DC00
1053
D800DCFF F09083BF D800DCFF
1054
DB7FDC00 F3AFB080 DB7FDC00
1055
DB7FDCFF F3AFB3BF DB7FDCFF
1061
set collation_connection=utf16_general_ci;
1062
drop table if exists t1;
1064
select repeat(' ', 64) as s1, repeat(' ',64) as s2
1067
show create table t1;
1069
t1 CREATE TABLE `t1` (
1070
`s1` varchar(64) CHARACTER SET utf16 DEFAULT NULL,
1071
`s2` varchar(64) CHARACTER SET utf16 DEFAULT NULL
1072
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1074
insert into t1 values('aaa','aaa');
1075
insert into t1 values('aaa|qqq','qqq');
1076
insert into t1 values('gheis','^[^a-dXYZ]+$');
1077
insert into t1 values('aab','^aa?b');
1078
insert into t1 values('Baaan','^Ba*n');
1079
insert into t1 values('aaa','qqq|aaa');
1080
insert into t1 values('qqq','qqq|aaa');
1081
insert into t1 values('bbb','qqq|aaa');
1082
insert into t1 values('bbb','qqq');
1083
insert into t1 values('aaa','aba');
1084
insert into t1 values(null,'abc');
1085
insert into t1 values('def',null);
1086
insert into t1 values(null,null);
1087
insert into t1 values('ghi','ghi[');
1088
select HIGH_PRIORITY s1 regexp s2 from t1;