1
drop table if exists t1;
2
SET @test_character_set= 'gb2312';
3
SET @test_collation= 'gb2312_chinese_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) gb2312_chinese_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) gb2312_chinese_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=gb2312
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 collation_connection='gb2312_chinese_ci';
97
create table t1 select repeat('a',4000) a;
99
insert into t1 values ('a'), ('a '), ('a\t');
100
select collation(a),hex(a) from t1 order by a;
102
gb2312_chinese_ci 6109
104
gb2312_chinese_ci 6120
106
create table t1 engine=innodb select repeat('a',50) as c1;
107
alter table t1 add index(c1(5));
108
insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
109
select collation(c1) from t1 limit 1;
112
select c1 from t1 where c1 like 'abcdef%' order by c1;
115
select c1 from t1 where c1 like 'abcde1%' order by c1;
120
select c1 from t1 where c1 like 'abcde11%' order by c1;
124
select c1 from t1 where c1 like 'abcde111%' order by c1;
128
select @@collation_connection;
129
@@collation_connection
131
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
132
insert into t1 values('abcdef');
133
insert into t1 values('_bcdef');
134
insert into t1 values('a_cdef');
135
insert into t1 values('ab_def');
136
insert into t1 values('abc_ef');
137
insert into t1 values('abcd_f');
138
insert into t1 values('abcde_');
139
select c1 as c1u from t1 where c1 like 'ab\_def';
142
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
147
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
148
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);
149
INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
150
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
151
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
152
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));
153
SELECT hex(concat(repeat(0xF1F2, 10), '%'));
154
hex(concat(repeat(0xF1F2, 10), '%'))
155
F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F225
157
SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
159
2 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
160
3 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
161
4 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
163
SET collation_connection='gb2312_bin';
164
create table t1 select repeat('a',4000) a;
166
insert into t1 values ('a'), ('a '), ('a\t');
167
select collation(a),hex(a) from t1 order by a;
173
create table t1 engine=innodb select repeat('a',50) as c1;
174
alter table t1 add index(c1(5));
175
insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
176
select collation(c1) from t1 limit 1;
179
select c1 from t1 where c1 like 'abcdef%' order by c1;
182
select c1 from t1 where c1 like 'abcde1%' order by c1;
187
select c1 from t1 where c1 like 'abcde11%' order by c1;
191
select c1 from t1 where c1 like 'abcde111%' order by c1;
195
select @@collation_connection;
196
@@collation_connection
198
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
199
insert into t1 values('abcdef');
200
insert into t1 values('_bcdef');
201
insert into t1 values('a_cdef');
202
insert into t1 values('ab_def');
203
insert into t1 values('abc_ef');
204
insert into t1 values('abcd_f');
205
insert into t1 values('abcde_');
206
select c1 as c1u from t1 where c1 like 'ab\_def';
209
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
214
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
215
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);
216
INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
217
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
218
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
219
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));
220
SELECT hex(concat(repeat(0xF1F2, 10), '%'));
221
hex(concat(repeat(0xF1F2, 10), '%'))
222
F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F225
224
SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
226
2 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
227
3 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
228
4 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2
231
CREATE TABLE t1 (a text) character set gb2312;
232
INSERT INTO t1 VALUES (0xA2A1),(0xD7FE);
233
SELECT hex(a) FROM t1 ORDER BY a;
239
select @@collation_connection;
240
@@collation_connection
242
select hex(weight_string('a'));
243
hex(weight_string('a'))
245
select hex(weight_string('A'));
246
hex(weight_string('A'))
248
select hex(weight_string('abc'));
249
hex(weight_string('abc'))
251
select hex(weight_string('abc' as char(2)));
252
hex(weight_string('abc' as char(2)))
254
select hex(weight_string('abc' as char(3)));
255
hex(weight_string('abc' as char(3)))
257
select hex(weight_string('abc' as char(5)));
258
hex(weight_string('abc' as char(5)))
260
select @@collation_connection;
261
@@collation_connection
263
select hex(weight_string('a' LEVEL 1));
264
hex(weight_string('a' LEVEL 1))
266
select hex(weight_string('A' LEVEL 1));
267
hex(weight_string('A' LEVEL 1))
269
select hex(weight_string('abc' LEVEL 1));
270
hex(weight_string('abc' LEVEL 1))
272
select hex(weight_string('abc' as char(2) LEVEL 1));
273
hex(weight_string('abc' as char(2) LEVEL 1))
275
select hex(weight_string('abc' as char(3) LEVEL 1));
276
hex(weight_string('abc' as char(3) LEVEL 1))
278
select hex(weight_string('abc' as char(5) LEVEL 1));
279
hex(weight_string('abc' as char(5) LEVEL 1))
281
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
282
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
284
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
285
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
287
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
288
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
290
select collation(cast(0xA1A1 as char));
291
collation(cast(0xA1A1 as char))
293
select hex(weight_string(cast(0x6141 as char)));
294
hex(weight_string(cast(0x6141 as char)))
296
select hex(weight_string(cast(0xA1A1 as char)));
297
hex(weight_string(cast(0xA1A1 as char)))
299
select hex(weight_string(cast(0xA1A1 as char) as char(1)));
300
hex(weight_string(cast(0xA1A1 as char) as char(1)))
302
select hex(weight_string(cast(0xA1A1A1A1 as char) as char(1)));
303
hex(weight_string(cast(0xA1A1A1A1 as char) as char(1)))
305
select hex(weight_string(cast(0xA1A1 as char) as char(3)));
306
hex(weight_string(cast(0xA1A1 as char) as char(3)))
308
select hex(weight_string(cast(0xA1A1A1A1 as char) as char(3)));
309
hex(weight_string(cast(0xA1A1A1A1 as char) as char(3)))
311
select hex(weight_string(cast(0x40A1A1 as char) as char(3)));
312
hex(weight_string(cast(0x40A1A1 as char) as char(3)))
314
select hex(weight_string(cast(0x40A1A1A1A1 as char) as char(3)));
315
hex(weight_string(cast(0x40A1A1A1A1 as char) as char(3)))
317
select hex(weight_string(cast(0x40A1A1A1A1A1A1 as char) as char(3)));
318
hex(weight_string(cast(0x40A1A1A1A1A1A1 as char) as char(3)))
320
select hex(weight_string(cast(0x4040A1A1A1A1A1A1 as char) as char(3)));
321
hex(weight_string(cast(0x4040A1A1A1A1A1A1 as char) as char(3)))
323
set collation_connection=gb2312_bin;
324
select @@collation_connection;
325
@@collation_connection
327
select hex(weight_string('a'));
328
hex(weight_string('a'))
330
select hex(weight_string('A'));
331
hex(weight_string('A'))
333
select hex(weight_string('abc'));
334
hex(weight_string('abc'))
336
select hex(weight_string('abc' as char(2)));
337
hex(weight_string('abc' as char(2)))
339
select hex(weight_string('abc' as char(3)));
340
hex(weight_string('abc' as char(3)))
342
select hex(weight_string('abc' as char(5)));
343
hex(weight_string('abc' as char(5)))
345
select @@collation_connection;
346
@@collation_connection
348
select hex(weight_string('a' LEVEL 1));
349
hex(weight_string('a' LEVEL 1))
351
select hex(weight_string('A' LEVEL 1));
352
hex(weight_string('A' LEVEL 1))
354
select hex(weight_string('abc' LEVEL 1));
355
hex(weight_string('abc' LEVEL 1))
357
select hex(weight_string('abc' as char(2) LEVEL 1));
358
hex(weight_string('abc' as char(2) LEVEL 1))
360
select hex(weight_string('abc' as char(3) LEVEL 1));
361
hex(weight_string('abc' as char(3) LEVEL 1))
363
select hex(weight_string('abc' as char(5) LEVEL 1));
364
hex(weight_string('abc' as char(5) LEVEL 1))
366
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
367
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
369
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
370
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
372
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
373
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
375
select collation(cast(0xA1A1 as char));
376
collation(cast(0xA1A1 as char))
378
select hex(weight_string(cast(0x6141 as char)));
379
hex(weight_string(cast(0x6141 as char)))
381
select hex(weight_string(cast(0xA1A1 as char)));
382
hex(weight_string(cast(0xA1A1 as char)))
384
select hex(weight_string(cast(0xA1A1 as char) as char(1)));
385
hex(weight_string(cast(0xA1A1 as char) as char(1)))
387
select hex(weight_string(cast(0xA1A1A1A1 as char) as char(1)));
388
hex(weight_string(cast(0xA1A1A1A1 as char) as char(1)))
390
select hex(weight_string(cast(0xA1A1 as char) as char(3)));
391
hex(weight_string(cast(0xA1A1 as char) as char(3)))
393
select hex(weight_string(cast(0xA1A1A1A1 as char) as char(3)));
394
hex(weight_string(cast(0xA1A1A1A1 as char) as char(3)))
396
select hex(weight_string(cast(0x40A1A1 as char) as char(3)));
397
hex(weight_string(cast(0x40A1A1 as char) as char(3)))
399
select hex(weight_string(cast(0x40A1A1A1A1 as char) as char(3)));
400
hex(weight_string(cast(0x40A1A1A1A1 as char) as char(3)))
402
select hex(weight_string(cast(0x40A1A1A1A1A1A1 as char) as char(3)));
403
hex(weight_string(cast(0x40A1A1A1A1A1A1 as char) as char(3)))
405
select hex(weight_string(cast(0x4040A1A1A1A1A1A1 as char) as char(3)));
406
hex(weight_string(cast(0x4040A1A1A1A1A1A1 as char) as char(3)))