1
DROP TABLE IF EXISTS t1;
2
SET @test_character_set= 'ucs2';
3
SET @test_collation= 'ucs2_general_ci';
4
SET @safe_character_set_server= @@character_set_server;
5
SET @safe_collation_server= @@collation_server;
6
SET @safe_character_set_client= @@character_set_client;
7
SET @safe_character_set_results= @@character_set_results;
8
SET character_set_server= @test_character_set;
9
SET collation_server= @test_collation;
12
CREATE TABLE t1 (c CHAR(10), KEY(c));
13
SHOW FULL COLUMNS FROM t1;
14
Field Type Collation Null Key Default Extra Privileges Comment
15
c char(10) ucs2_general_ci YES MUL NULL
16
INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
17
SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
23
CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
24
SHOW FULL COLUMNS FROM t1;
25
Field Type Collation Null Key Default Extra Privileges Comment
26
c1 varchar(15) ucs2_general_ci YES MUL NULL
27
INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
28
SELECT c1 as want3results from t1 where c1 like 'l%';
33
SELECT c1 as want3results from t1 where c1 like 'lo%';
38
SELECT c1 as want1result from t1 where c1 like 'loc%';
41
SELECT c1 as want1result from t1 where c1 like 'loca%';
44
SELECT c1 as want1result from t1 where c1 like 'locat%';
47
SELECT c1 as want1result from t1 where c1 like 'locati%';
50
SELECT c1 as want1result from t1 where c1 like 'locatio%';
53
SELECT c1 as want1result from t1 where c1 like 'location%';
57
create table t1 (a set('a') not null);
58
insert into t1 values (),();
60
Warning 1364 Field 'a' doesn't have a default value
61
select cast(a as char(1)) from t1;
65
select a sounds like a from t1;
69
select 1 from t1 order by cast(a as char(1));
77
level smallint unsigned);
80
t1 CREATE TABLE `t1` (
81
`name` varchar(10) DEFAULT NULL,
82
`level` smallint(5) unsigned DEFAULT NULL
83
) ENGINE=MyISAM DEFAULT CHARSET=ucs2
84
insert into t1 values ('string',1);
85
select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
86
concat(name,space(level)) concat(name, repeat(' ',level))
91
SET character_set_server= @safe_character_set_server;
92
SET collation_server= @safe_collation_server;
93
SET character_set_client= @safe_character_set_client;
94
SET character_set_results= @safe_character_set_results;
96
SET character_set_connection=ucs2;
97
select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
98
'a' = 'a' 'a' = 'a ' 'a ' = 'a'
100
select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
101
'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a'
103
select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
104
'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0'
106
select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
107
'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a '
109
select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
110
'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0'
112
select 'a a' > 'a', 'a \0' < 'a';
113
'a a' > 'a' 'a \0' < 'a'
115
select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a';
116
binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a'
118
SET CHARACTER SET koi8r;
119
CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2);
120
INSERT INTO t1 VALUES (_koi8r'ďż˝',_koi8r'ďż˝'), (X'2004',X'2004');
121
SELECT hex(word) FROM t1 ORDER BY word;
125
SELECT hex(word2) FROM t1 ORDER BY word2;
130
INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020');
131
SELECT hex(word) FROM t1 ORDER BY word;
135
SELECT hex(word2) FROM t1 ORDER BY word2;
140
SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'0421');
141
LPAD(_ucs2 X'0420',10,_ucs2 X'0421')
142
����������
143
SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'04210422');
144
LPAD(_ucs2 X'0420',10,_ucs2 X'04210422')
145
����������
146
SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423');
147
LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423')
148
����������
149
SELECT LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
150
LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423')
151
����������
152
SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'0421');
153
RPAD(_ucs2 X'0420',10,_ucs2 X'0421')
154
����������
155
SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'04210422');
156
RPAD(_ucs2 X'0420',10,_ucs2 X'04210422')
157
����������
158
SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423');
159
RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423')
160
����������
161
SELECT RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
162
RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423')
163
����������
164
CREATE TABLE t1 SELECT
165
LPAD(_ucs2 X'0420',10,_ucs2 X'0421') l,
166
RPAD(_ucs2 X'0420',10,_ucs2 X'0421') r;
167
SHOW CREATE TABLE t1;
169
t1 CREATE TABLE `t1` (
170
`l` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT '',
171
`r` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
172
) ENGINE=MyISAM DEFAULT CHARSET=latin1
174
create table t2(f1 Char(30));
175
insert into t2 values ("103000"), ("22720000"), ("3401200"), ("78000");
176
select lpad(f1, 12, "-o-/") from t2;
184
SET character_set_connection=ucs2;
185
select @@collation_connection;
186
@@collation_connection
188
create table t1 as select repeat(' ',10) as a union select null;
189
alter table t1 add key(a);
190
show create table t1;
192
t1 CREATE TABLE `t1` (
193
`a` varchar(10) CHARACTER SET ucs2 DEFAULT NULL,
195
) ENGINE=MyISAM DEFAULT CHARSET=latin1
196
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
197
explain select * from t1 where a like 'abc%';
198
id select_type table type possible_keys key key_len ref rows Extra
199
1 SIMPLE t1 range a a 23 NULL 1 Using where; Using index
200
explain select * from t1 where a like concat('abc','%');
201
id select_type table type possible_keys key key_len ref rows Extra
202
1 SIMPLE t1 range a a 23 NULL 1 Using where; Using index
203
select * from t1 where a like "abc%";
207
select * from t1 where a like concat("abc","%");
211
select * from t1 where a like "ABC%";
215
select * from t1 where a like "test%";
218
select * from t1 where a like "te_t";
221
select * from t1 where a like "%a%";
226
select * from t1 where a like "%abcd%";
229
select * from t1 where a like "%abc\d%";
233
select 'AA' like 'AA';
236
select 'AA' like 'A%A';
239
select 'AA' like 'A%%A';
242
select 'AA' like 'AA%';
245
select 'AA' like '%AA%';
248
select 'AA' like '%A';
251
select 'AA' like '%AA';
254
select 'AA' like 'A%A%';
257
select 'AA' like '_%_%';
260
select 'AA' like '%A%A';
263
select 'AAA'like 'A%A%A';
266
select 'AZ' like 'AZ';
269
select 'AZ' like 'A%Z';
272
select 'AZ' like 'A%%Z';
275
select 'AZ' like 'AZ%';
278
select 'AZ' like '%AZ%';
281
select 'AZ' like '%Z';
284
select 'AZ' like '%AZ';
287
select 'AZ' like 'A%Z%';
290
select 'AZ' like '_%_%';
293
select 'AZ' like '%A%Z';
296
select 'AZ' like 'A_';
299
select 'AZ' like '_Z';
302
select 'AMZ'like 'A%M%Z';
305
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2);
306
INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����');
307
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
308
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
309
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
310
SELECT * FROM t1 WHERE a LIKE '%����%';
318
����������
319
����������
320
����������
321
����������
322
����������
323
����������
324
����������
325
����������
326
����������
327
����������
328
����������
329
����������
330
SELECT * FROM t1 WHERE a LIKE '%���%';
338
����������
339
����������
340
����������
341
����������
342
����������
343
����������
344
����������
345
����������
346
����������
347
����������
348
����������
349
����������
350
SELECT * FROM t1 WHERE a LIKE '����%';
358
����������
359
����������
360
����������
361
����������
362
����������
363
����������
364
����������
365
����������
366
����������
367
����������
368
����������
369
����������
370
SELECT * FROM t1 WHERE a LIKE '����%' COLLATE ucs2_bin;
373
����������
375
CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))
376
ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci;
377
INSERT INTO t1 (word) VALUES ("cat");
378
SELECT * FROM t1 WHERE word LIKE "c%";
381
SELECT * FROM t1 WHERE word LIKE "ca_";
384
SELECT * FROM t1 WHERE word LIKE "cat";
387
SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630025';
390
SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F';
394
select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066);
395
insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066)
397
select insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066);
398
insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066)
403
bar INT(11) default 0,
407
COLLATE ucs2_general_ci ;
408
INSERT INTO t1 (word) VALUES ("aar");
409
INSERT INTO t1 (word) VALUES ("a");
410
INSERT INTO t1 (word) VALUES ("aardvar");
411
INSERT INTO t1 (word) VALUES ("aardvark");
412
INSERT INTO t1 (word) VALUES ("aardvara");
413
INSERT INTO t1 (word) VALUES ("aardvarz");
414
EXPLAIN SELECT * FROM t1 ORDER BY word;
415
id select_type table type possible_keys key key_len ref rows Extra
416
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
417
SELECT * FROM t1 ORDER BY word;
425
EXPLAIN SELECT word FROM t1 ORDER BY word;
426
id select_type table type possible_keys key key_len ref rows Extra
427
1 SIMPLE t1 index NULL PRIMARY 130 NULL 6 Using index
428
SELECT word FROM t1 ORDER by word;
442
COLLATE ucs2_general_ci;
443
INSERT INTO t1 (word) VALUES ("aar");
444
INSERT INTO t1 (word) VALUES ("a");
445
INSERT INTO t1 (word) VALUES ("aardvar");
446
INSERT INTO t1 (word) VALUES ("aardvark");
447
INSERT INTO t1 (word) VALUES ("aardvara");
448
INSERT INTO t1 (word) VALUES ("aardvarz");
449
EXPLAIN SELECT * FROM t1 ORDER BY WORD;
450
id select_type table type possible_keys key key_len ref rows Extra
451
1 SIMPLE t1 index NULL PRIMARY 130 NULL 6 Using index
452
SELECT * FROM t1 ORDER BY word;
463
bar INT(11) AUTO_INCREMENT,
467
COLLATE ucs2_general_ci ;
468
INSERT INTO t1 (word) VALUES ("aar");
469
INSERT INTO t1 (word) VALUES ("a" );
470
INSERT INTO t1 (word) VALUES ("aardvar");
471
INSERT INTO t1 (word) VALUES ("aardvark");
472
INSERT INTO t1 (word) VALUES ("aardvara");
473
INSERT INTO t1 (word) VALUES ("aardvarz");
474
EXPLAIN SELECT * FROM t1 ORDER BY word;
475
id select_type table type possible_keys key key_len ref rows Extra
476
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
477
SELECT * FROM t1 ORDER BY word;
485
EXPLAIN SELECT word FROM t1 ORDER BY word;
486
id select_type table type possible_keys key key_len ref rows Extra
487
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
488
SELECT word FROM t1 ORDER BY word;
497
SELECT HEX(_ucs2 0x0);
500
SELECT HEX(_ucs2 0x01);
503
SELECT HEX(_ucs2 0x012);
506
SELECT HEX(_ucs2 0x0123);
509
SELECT HEX(_ucs2 0x01234);
512
SELECT HEX(_ucs2 0x012345);
515
SELECT HEX(_ucs2 0x0123456);
518
SELECT HEX(_ucs2 0x01234567);
519
HEX(_ucs2 0x01234567)
521
SELECT HEX(_ucs2 0x012345678);
522
HEX(_ucs2 0x012345678)
524
SELECT HEX(_ucs2 0x0123456789);
525
HEX(_ucs2 0x0123456789)
527
SELECT HEX(_ucs2 0x0123456789A);
528
HEX(_ucs2 0x0123456789A)
530
SELECT HEX(_ucs2 0x0123456789AB);
531
HEX(_ucs2 0x0123456789AB)
533
SELECT HEX(_ucs2 0x0123456789ABC);
534
HEX(_ucs2 0x0123456789ABC)
536
SELECT HEX(_ucs2 0x0123456789ABCD);
537
HEX(_ucs2 0x0123456789ABCD)
539
SELECT HEX(_ucs2 0x0123456789ABCDE);
540
HEX(_ucs2 0x0123456789ABCDE)
542
SELECT HEX(_ucs2 0x0123456789ABCDEF);
543
HEX(_ucs2 0x0123456789ABCDEF)
545
SELECT hex(cast(0xAA as char character set ucs2));
546
hex(cast(0xAA as char character set ucs2))
548
SELECT hex(convert(0xAA using ucs2));
549
hex(convert(0xAA using ucs2))
551
CREATE TABLE t1 (a char(10) character set ucs2);
552
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
553
SELECT HEX(a) FROM t1;
561
CREATE TABLE t1 (a varchar(10) character set ucs2);
562
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
563
SELECT HEX(a) FROM t1;
571
CREATE TABLE t1 (a text character set ucs2);
572
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
573
SELECT HEX(a) FROM t1;
581
CREATE TABLE t1 (a mediumtext character set ucs2);
582
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
583
SELECT HEX(a) FROM t1;
591
CREATE TABLE t1 (a longtext character set ucs2);
592
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
593
SELECT HEX(a) FROM t1;
601
create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`);
602
insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c');
603
select s1 from t1 where s1 > 'a' order by s1;
608
create table t1(a char(1)) default charset = ucs2;
609
insert into t1 values ('a'),('b'),('c');
610
alter table t1 modify a char(5);
611
select a, hex(a) from t1;
618
set @str1 = 'select ?';
619
set @str2 = convert(@str1 using ucs2);
620
prepare stmt1 from @str2;
621
execute stmt1 using @ivar;
625
create table t1 (a enum('x','y','z') character set ucs2);
626
show create table t1;
628
t1 CREATE TABLE `t1` (
629
`a` enum('x','y','z') CHARACTER SET ucs2 DEFAULT NULL
630
) ENGINE=MyISAM DEFAULT CHARSET=latin1
631
insert into t1 values ('x');
632
insert into t1 values ('y');
633
insert into t1 values ('z');
634
select a, hex(a) from t1 order by a;
639
alter table t1 change a a enum('x','y','z','d','e','ďż˝','ďż˝','ďż˝') character set ucs2;
640
show create table t1;
642
t1 CREATE TABLE `t1` (
643
`a` enum('x','y','z','d','e','ďż˝','ďż˝','ďż˝') CHARACTER SET ucs2 DEFAULT NULL
644
) ENGINE=MyISAM DEFAULT CHARSET=latin1
645
insert into t1 values ('D');
646
insert into t1 values ('E ');
647
insert into t1 values ('ďż˝');
648
insert into t1 values ('ďż˝');
649
insert into t1 values ('ďż˝');
650
select a, hex(a) from t1 order by a;
661
create table t1 (a set ('x','y','z','ďż˝','ďż˝','ďż˝') character set ucs2);
662
show create table t1;
664
t1 CREATE TABLE `t1` (
665
`a` set('x','y','z','ďż˝','ďż˝','ďż˝') CHARACTER SET ucs2 DEFAULT NULL
666
) ENGINE=MyISAM DEFAULT CHARSET=latin1
667
insert into t1 values ('x');
668
insert into t1 values ('y');
669
insert into t1 values ('z');
670
insert into t1 values ('x,y');
671
insert into t1 values ('x,y,z,ďż˝,ďż˝,ďż˝');
672
select a, hex(a) from t1 order by a;
678
x,y,z,ďż˝,ďż˝,ďż˝ 0078002C0079002C007A002C00E4002C00F6002C00FC
680
create table t1(a enum('a','b','c')) default character set ucs2;
681
insert into t1 values('a'),('b'),('c');
682
alter table t1 add b char(1);
685
select * from t1 order by a;
691
SET collation_connection='ucs2_general_ci';
692
create table t1 select repeat('a',4000) a;
694
insert into t1 values ('a'), ('a '), ('a\t');
695
select collation(a),hex(a) from t1 order by a;
697
ucs2_general_ci 00610009
699
ucs2_general_ci 00610020
701
select @@collation_connection;
702
@@collation_connection
704
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
705
insert into t1 values('abcdef');
706
insert into t1 values('_bcdef');
707
insert into t1 values('a_cdef');
708
insert into t1 values('ab_def');
709
insert into t1 values('abc_ef');
710
insert into t1 values('abcd_f');
711
insert into t1 values('abcde_');
712
select c1 as c1u from t1 where c1 like 'ab\_def';
715
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
719
drop table if exists t1;
720
create table t1 as select repeat(' ', 64) as s1;
721
select collation(s1) from t1;
725
insert into t1 values ('a'),('ae'),(_latin1 0xE4);
726
insert into t1 values ('o'),('oe'),(_latin1 0xF6);
727
insert into t1 values ('s'),('ss'),(_latin1 0xDF);
728
insert into t1 values ('u'),('ue'),(_latin1 0xFC);
729
select s1, hex(s1) from t1 order by s1, binary s1;
743
select group_concat(s1 order by binary s1) from t1 group by s1;
744
group_concat(s1 order by binary s1)
755
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
756
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);
757
INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
758
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
759
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
760
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));
761
SELECT hex(concat(repeat(0xF1F2, 10), '%'));
762
hex(concat(repeat(0xF1F2, 10), '%'))
763
F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F20025
765
SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
767
2 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
768
3 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
769
4 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
772
SET collation_connection='ucs2_bin';
773
create table t1 select repeat('a',4000) a;
775
insert into t1 values ('a'), ('a '), ('a\t');
776
select collation(a),hex(a) from t1 order by a;
782
select @@collation_connection;
783
@@collation_connection
785
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
786
insert into t1 values('abcdef');
787
insert into t1 values('_bcdef');
788
insert into t1 values('a_cdef');
789
insert into t1 values('ab_def');
790
insert into t1 values('abc_ef');
791
insert into t1 values('abcd_f');
792
insert into t1 values('abcde_');
793
select c1 as c1u from t1 where c1 like 'ab\_def';
796
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
801
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
802
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);
803
INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
804
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
805
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
806
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));
807
SELECT hex(concat(repeat(0xF1F2, 10), '%'));
808
hex(concat(repeat(0xF1F2, 10), '%'))
809
F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F20025
811
SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
813
2 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
814
3 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
815
4 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
817
select hex(substr(_ucs2 0x00e400e50068,1));
818
hex(substr(_ucs2 0x00e400e50068,1))
820
select hex(substr(_ucs2 0x00e400e50068,2));
821
hex(substr(_ucs2 0x00e400e50068,2))
823
select hex(substr(_ucs2 0x00e400e50068,3));
824
hex(substr(_ucs2 0x00e400e50068,3))
826
select hex(substr(_ucs2 0x00e400e50068,-1));
827
hex(substr(_ucs2 0x00e400e50068,-1))
829
select hex(substr(_ucs2 0x00e400e50068,-2));
830
hex(substr(_ucs2 0x00e400e50068,-2))
832
select hex(substr(_ucs2 0x00e400e50068,-3));
833
hex(substr(_ucs2 0x00e400e50068,-3))
836
SET collation_connection='ucs2_swedish_ci';
837
CREATE TABLE t1 (Field1 int(10) default '0');
838
INSERT INTO t1 VALUES ('-1');
843
CREATE TABLE t1 (Field1 int(10) unsigned default '0');
844
INSERT INTO t1 VALUES ('-1');
846
Warning 1264 Out of range value for column 'Field1' at row 1
849
SELECT CONVERT(103, CHAR(50) UNICODE);
850
CONVERT(103, CHAR(50) UNICODE)
852
SELECT CONVERT(103.0, CHAR(50) UNICODE);
853
CONVERT(103.0, CHAR(50) UNICODE)
855
SELECT CONVERT(-103, CHAR(50) UNICODE);
856
CONVERT(-103, CHAR(50) UNICODE)
858
SELECT CONVERT(-103.0, CHAR(50) UNICODE);
859
CONVERT(-103.0, CHAR(50) UNICODE)
862
a varchar(255) NOT NULL default '',
864
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE ucs2_general_ci;
865
insert into t1 values (0x803d);
866
insert into t1 values (0x005b);
867
select hex(a) from t1;
872
create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB;
873
insert into t1 values('a');
874
create index t1f1 on t1(f1);
875
select f1 from t1 where f1 like 'a%';
879
create table t1 (utext varchar(20) character set ucs2);
880
insert into t1 values ("lily");
881
insert into t1 values ("river");
882
prepare stmt from 'select utext from t1 where utext like ?';
884
execute stmt using @param1;
888
execute stmt using @param1;
892
select utext from t1 where utext like '%%';
897
deallocate prepare stmt;
899
a char(10) unicode not null,
902
insert into t1 values (repeat(0x201f, 10));
903
insert into t1 values (repeat(0x2020, 10));
904
insert into t1 values (repeat(0x2021, 10));
905
explain select hex(a) from t1 order by a;
906
id select_type table type possible_keys key key_len ref rows Extra
907
1 SIMPLE t1 index NULL a 20 NULL 3 Using index
908
select hex(a) from t1 order by a;
910
201F201F201F201F201F201F201F201F201F201F
911
2020202020202020202020202020202020202020
912
2021202120212021202120212021202120212021
913
alter table t1 drop index a;
914
select hex(a) from t1 order by a;
916
201F201F201F201F201F201F201F201F201F201F
917
2020202020202020202020202020202020202020
918
2021202120212021202120212021202120212021
920
CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci);
921
INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ');
922
SELECT id, MIN(s) FROM t1 GROUP BY id;
927
drop table if exists bug20536;
929
create table bug20536 (id bigint not null auto_increment primary key, name
930
varchar(255) character set ucs2 not null);
931
insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'");
932
select md5(name) from bug20536;
934
f4b7ce8b45a20e3c4e84bef515d1525c
935
48d95db0d8305c2fe11548a3635c9385
936
select sha1(name) from bug20536;
938
e0b52f38deddb9f9e8d5336b153592794cb49baf
939
677d4d505355eb5b0549b865fcae4b7f0c28aef5
940
select make_set(3, name, upper(name)) from bug20536;
941
make_set(3, name, upper(name))
944
select export_set(5, name, upper(name)) from bug20536;
945
export_set(5, name, upper(name))
946
test1,TEST1,test1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1
947
'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2'
948
select export_set(5, name, upper(name), ",", 5) from bug20536;
949
export_set(5, name, upper(name), ",", 5)
950
test1,TEST1,test1,TEST1,TEST1
951
'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2'
953
status enum('active','passive') collate latin1_general_ci
954
NOT NULL default 'passive'
956
SHOW CREATE TABLE t1;
958
t1 CREATE TABLE `t1` (
959
`status` enum('active','passive') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'passive'
960
) ENGINE=MyISAM DEFAULT CHARSET=latin1
961
ALTER TABLE t1 ADD a int NOT NULL AFTER status;
963
status enum('active','passive') collate ucs2_turkish_ci
964
NOT NULL default 'passive'
966
SHOW CREATE TABLE t2;
968
t2 CREATE TABLE `t2` (
969
`status` enum('active','passive') CHARACTER SET ucs2 COLLATE ucs2_turkish_ci NOT NULL DEFAULT 'passive'
970
) ENGINE=MyISAM DEFAULT CHARSET=latin1
971
ALTER TABLE t2 ADD a int NOT NULL AFTER status;
973
select password(name) from bug20536;
977
select old_password(name) from bug20536;
981
select quote(name) from bug20536;
987
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
988
set names ucs2 collate ucs2_bin;
989
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
990
set character_set_client= ucs2;
991
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
992
set character_set_client= concat('ucs', substr('2', 1));
993
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
994
CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci);
995
INSERT INTO t1 VALUES('abcd');
996
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
1001
CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3));
1002
INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
1009
create table t1 (utext varchar(20) character set ucs2);
1010
insert into t1 values ("lily");
1011
insert into t1 values ("river");
1012
prepare stmt from 'select utext from t1 where utext like ?';
1014
execute stmt using @param1;
1018
execute stmt using @param1;
1022
select utext from t1 where utext like '%%';
1027
deallocate prepare stmt;
1029
set character_set_connection=ucs2;
1030
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
1031
soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb')
1033
select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb'));
1034
hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb'))
1035
0048003000300030 00480034003100340032 004900350031003200330031
1036
select 'mood' sounds like 'mud';
1037
'mood' sounds like 'mud'
1039
select hex(soundex(_ucs2 0x041004110412));
1040
hex(soundex(_ucs2 0x041004110412))
1042
select hex(soundex(_ucs2 0x00BF00C0));
1043
hex(soundex(_ucs2 0x00BF00C0))
1046
create table t1(a blob, b text charset utf8, c text charset ucs2);
1047
select data_type, character_octet_length, character_maximum_length
1048
from information_schema.columns where table_name='t1';
1049
data_type character_octet_length character_maximum_length
1054
create table t1 (a char(1) character set ucs2);
1055
insert into t1 values ('a'),('b'),('c');
1056
select hex(group_concat(a)) from t1;
1057
hex(group_concat(a))
1058
0061002C0062002C0063
1059
select collation(group_concat(a)) from t1;
1060
collation(group_concat(a))
1064
create table t1 (a char(1) character set latin1);
1065
insert into t1 values ('a'),('b'),('c');
1066
set character_set_connection=ucs2;
1067
select hex(group_concat(a separator ',')) from t1;
1068
hex(group_concat(a separator ','))
1070
select collation(group_concat(a separator ',')) from t1;
1071
collation(group_concat(a separator ','))
1075
create table t1 (s1 char(1) character set ascii, s2 char(1) character set ucs2);
1076
insert into t1 (s1) values (0x7f);
1077
update t1 set s2 = s1;
1078
select hex(s2) from t1;
1081
select hex(convert(s1 using latin1)) from t1;
1082
hex(convert(s1 using latin1))
1085
create table t1 (a varchar(15) character set ascii not null, b int);
1086
insert into t1 values ('a',1);
1087
select concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062)) from t1;
1088
concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062))
1090
select concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062)) from t1;
1091
concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062))
1093
select * from t1 where a=if(b<10,_ucs2 0x0061,_ucs2 0x0062);
1096
select * from t1 where a=if(b>10,_ucs2 0x0061,_ucs2 0x0062);
1098
select concat(a,if(b<10,_ucs2 0x00C0,_ucs2 0x0062)) from t1;
1099
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
1100
select concat(a,if(b>10,_ucs2 0x00C0,_ucs2 0x0062)) from t1;
1101
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
1102
select concat(a,if(b<10,_ucs2 0x0062,_ucs2 0x00C0)) from t1;
1103
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
1104
select concat(a,if(b>10,_ucs2 0x0062,_ucs2 0x00C0)) from t1;
1105
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
1106
select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062);
1107
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '='
1108
select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0);
1109
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '='
1111
set collation_connection=ucs2_general_ci;
1112
drop table if exists t1;
1114
select repeat(' ', 64) as s1, repeat(' ',64) as s2
1117
show create table t1;
1119
t1 CREATE TABLE `t1` (
1120
`s1` varchar(64) CHARACTER SET ucs2 DEFAULT NULL,
1121
`s2` varchar(64) CHARACTER SET ucs2 DEFAULT NULL
1122
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1124
insert into t1 values('aaa','aaa');
1125
insert into t1 values('aaa|qqq','qqq');
1126
insert into t1 values('gheis','^[^a-dXYZ]+$');
1127
insert into t1 values('aab','^aa?b');
1128
insert into t1 values('Baaan','^Ba*n');
1129
insert into t1 values('aaa','qqq|aaa');
1130
insert into t1 values('qqq','qqq|aaa');
1131
insert into t1 values('bbb','qqq|aaa');
1132
insert into t1 values('bbb','qqq');
1133
insert into t1 values('aaa','aba');
1134
insert into t1 values(null,'abc');
1135
insert into t1 values('def',null);
1136
insert into t1 values(null,null);
1137
insert into t1 values('ghi','ghi[');
1138
select HIGH_PRIORITY s1 regexp s2 from t1;
1156
select hex(char(0x41 using ucs2));
1157
hex(char(0x41 using ucs2))
1160
set collation_connection=ucs2_general_ci;
1161
select @@collation_connection;
1162
@@collation_connection
1164
select hex(weight_string('a'));
1165
hex(weight_string('a'))
1167
select hex(weight_string('A'));
1168
hex(weight_string('A'))
1170
select hex(weight_string('abc'));
1171
hex(weight_string('abc'))
1173
select hex(weight_string('abc' as char(2)));
1174
hex(weight_string('abc' as char(2)))
1176
select hex(weight_string('abc' as char(3)));
1177
hex(weight_string('abc' as char(3)))
1179
select hex(weight_string('abc' as char(5)));
1180
hex(weight_string('abc' as char(5)))
1181
00410042004300200020
1182
select @@collation_connection;
1183
@@collation_connection
1185
select hex(weight_string('a' LEVEL 1));
1186
hex(weight_string('a' LEVEL 1))
1188
select hex(weight_string('A' LEVEL 1));
1189
hex(weight_string('A' LEVEL 1))
1191
select hex(weight_string('abc' LEVEL 1));
1192
hex(weight_string('abc' LEVEL 1))
1194
select hex(weight_string('abc' as char(2) LEVEL 1));
1195
hex(weight_string('abc' as char(2) LEVEL 1))
1197
select hex(weight_string('abc' as char(3) LEVEL 1));
1198
hex(weight_string('abc' as char(3) LEVEL 1))
1200
select hex(weight_string('abc' as char(5) LEVEL 1));
1201
hex(weight_string('abc' as char(5) LEVEL 1))
1202
00410042004300200020
1203
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
1204
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
1205
20002000430042004100
1206
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
1207
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
1208
FFBEFFBDFFBCFFDFFFDF
1209
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
1210
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
1211
DFFFDFFFBCFFBDFFBEFF
1212
set collation_connection=ucs2_bin;
1213
select @@collation_connection;
1214
@@collation_connection
1216
select hex(weight_string('a'));
1217
hex(weight_string('a'))
1219
select hex(weight_string('A'));
1220
hex(weight_string('A'))
1222
select hex(weight_string('abc'));
1223
hex(weight_string('abc'))
1225
select hex(weight_string('abc' as char(2)));
1226
hex(weight_string('abc' as char(2)))
1228
select hex(weight_string('abc' as char(3)));
1229
hex(weight_string('abc' as char(3)))
1231
select hex(weight_string('abc' as char(5)));
1232
hex(weight_string('abc' as char(5)))
1233
00610062006300200020
1234
select @@collation_connection;
1235
@@collation_connection
1237
select hex(weight_string('a' LEVEL 1));
1238
hex(weight_string('a' LEVEL 1))
1240
select hex(weight_string('A' LEVEL 1));
1241
hex(weight_string('A' LEVEL 1))
1243
select hex(weight_string('abc' LEVEL 1));
1244
hex(weight_string('abc' LEVEL 1))
1246
select hex(weight_string('abc' as char(2) LEVEL 1));
1247
hex(weight_string('abc' as char(2) LEVEL 1))
1249
select hex(weight_string('abc' as char(3) LEVEL 1));
1250
hex(weight_string('abc' as char(3) LEVEL 1))
1252
select hex(weight_string('abc' as char(5) LEVEL 1));
1253
hex(weight_string('abc' as char(5) LEVEL 1))
1254
00610062006300200020
1255
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
1256
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
1257
20002000630062006100
1258
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
1259
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
1260
FF9EFF9DFF9CFFDFFFDF
1261
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
1262
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
1263
DFFFDFFF9CFF9DFF9EFF