1
drop table if exists t1, t2;
2
create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text);
6
`v` VARCHAR(30) COLLATE utf8_general_ci DEFAULT NULL,
7
`c` VARCHAR(3) COLLATE utf8_general_ci DEFAULT NULL,
8
`e` ENUM('abc','def','ghi') DEFAULT NULL,
9
`t` TEXT COLLATE utf8_general_ci
10
) ENGINE=DEFAULT COLLATE = utf8_general_ci
11
insert into t1 values ('abc', 'de', 'ghi', 'jkl');
12
insert into t1 values ('abc ', 'de ', 'ghi', 'jkl ');
13
insert into t1 values ('abc ', 'd ', 'ghi', 'jkl ');
14
select length(v),length(c),length(e),length(t) from t1;
15
length(v) length(c) length(e) length(t)
20
create table t1 (v varchar(20));
21
insert into t1 values('a ');
25
select binary v='a' from t1;
28
select binary v='a ' from t1;
31
insert into t1 values('a');
32
alter table t1 add primary key (v);
33
ERROR 23000: Duplicate entry 'a' for key 'PRIMARY'
35
create table t1 (v varbinary(20));
36
insert into t1 values('a');
37
insert into t1 values('a ');
38
alter table t1 add primary key (v);
40
create table t1 (v varchar(254), index (v));
41
insert into t1 values ("This is a test ");
42
insert into t1 values ("Some sample data");
43
insert into t1 values (" garbage ");
44
insert into t1 values (" This is a test ");
45
insert into t1 values ("This is a test");
46
insert into t1 values ("Hello world");
47
insert into t1 values ("Foo bar");
48
insert into t1 values ("This is a test");
49
insert into t1 values ("MySQL varchar test");
50
insert into t1 values ("test MySQL varchar");
51
insert into t1 values ("This is a long string to have some random length data included");
52
insert into t1 values ("Short string");
53
insert into t1 values ("VSS");
54
insert into t1 values ("Some samples");
55
insert into t1 values ("Bar foo");
56
insert into t1 values ("Bye");
57
select * from t1 where v like 'This is a test' order by v;
61
select * from t1 where v='This is a test' order by v;
66
select * from t1 where v like 'S%' order by v;
71
explain select * from t1 where v like 'This is a test' order by v;
72
id select_type table type possible_keys key key_len ref rows Extra
73
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
74
explain select * from t1 where v='This is a test' order by v;
75
id select_type table type possible_keys key key_len ref rows Extra
76
1 SIMPLE t1 ref v v 1019 const 1 Using where
77
explain select * from t1 where v like 'S%' order by v;
78
id select_type table type possible_keys key key_len ref rows Extra
79
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
80
alter table t1 change v v varchar(255);
81
select * from t1 where v like 'This is a test' order by v;
85
select * from t1 where v='This is a test' order by v;
90
select * from t1 where v like 'S%' order by v;
95
explain select * from t1 where v like 'This is a test' order by v;
96
id select_type table type possible_keys key key_len ref rows Extra
97
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
98
explain select * from t1 where v='This is a test' order by v;
99
id select_type table type possible_keys key key_len ref rows Extra
100
1 SIMPLE t1 ref v v 1023 const 1 Using where
101
explain select * from t1 where v like 'S%' order by v;
102
id select_type table type possible_keys key key_len ref rows Extra
103
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
104
alter table t1 change v v varchar(256);
105
select * from t1 where v like 'This is a test' order by v;
109
select * from t1 where v='This is a test' order by v;
114
select * from t1 where v like 'S%' order by v;
119
explain select * from t1 where v like 'This is a test' order by v;
120
id select_type table type possible_keys key key_len ref rows Extra
121
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
122
explain select * from t1 where v='This is a test' order by v;
123
id select_type table type possible_keys key key_len ref rows Extra
124
1 SIMPLE t1 ref v v 1027 const 1 Using where
125
explain select * from t1 where v like 'S%' order by v;
126
id select_type table type possible_keys key key_len ref rows Extra
127
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
128
alter table t1 change v v varchar(257);
129
select * from t1 where v like 'This is a test' order by v;
133
select * from t1 where v='This is a test' order by v;
138
select * from t1 where v like 'S%' order by v;
143
explain select * from t1 where v like 'This is a test' order by v;
144
id select_type table type possible_keys key key_len ref rows Extra
145
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
146
explain select * from t1 where v='This is a test' order by v;
147
id select_type table type possible_keys key key_len ref rows Extra
148
1 SIMPLE t1 ref v v 1031 const 1 Using where
149
explain select * from t1 where v like 'S%' order by v;
150
id select_type table type possible_keys key key_len ref rows Extra
151
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
152
alter table t1 change v v varchar(258);
153
select * from t1 where v like 'This is a test' order by v;
157
select * from t1 where v='This is a test' order by v;
162
select * from t1 where v like 'S%' order by v;
167
explain select * from t1 where v like 'This is a test' order by v;
168
id select_type table type possible_keys key key_len ref rows Extra
169
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
170
explain select * from t1 where v='This is a test' order by v;
171
id select_type table type possible_keys key key_len ref rows Extra
172
1 SIMPLE t1 ref v v 1035 const 1 Using where
173
explain select * from t1 where v like 'S%' order by v;
174
id select_type table type possible_keys key key_len ref rows Extra
175
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
176
alter table t1 change v v varchar(259);
177
select * from t1 where v like 'This is a test' order by v;
181
select * from t1 where v='This is a test' order by v;
186
select * from t1 where v like 'S%' order by v;
191
explain select * from t1 where v like 'This is a test' order by v;
192
id select_type table type possible_keys key key_len ref rows Extra
193
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
194
explain select * from t1 where v='This is a test' order by v;
195
id select_type table type possible_keys key key_len ref rows Extra
196
1 SIMPLE t1 ref v v 1039 const 1 Using where
197
explain select * from t1 where v like 'S%' order by v;
198
id select_type table type possible_keys key key_len ref rows Extra
199
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
200
alter table t1 change v v varchar(258);
201
select * from t1 where v like 'This is a test' order by v;
205
select * from t1 where v='This is a test' order by v;
210
select * from t1 where v like 'S%' order by v;
215
explain select * from t1 where v like 'This is a test' order by v;
216
id select_type table type possible_keys key key_len ref rows Extra
217
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
218
explain select * from t1 where v='This is a test' order by v;
219
id select_type table type possible_keys key key_len ref rows Extra
220
1 SIMPLE t1 ref v v 1035 const 1 Using where
221
explain select * from t1 where v like 'S%' order by v;
222
id select_type table type possible_keys key key_len ref rows Extra
223
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
224
alter table t1 change v v varchar(257);
225
select * from t1 where v like 'This is a test' order by v;
229
select * from t1 where v='This is a test' order by v;
234
select * from t1 where v like 'S%' order by v;
239
explain select * from t1 where v like 'This is a test' order by v;
240
id select_type table type possible_keys key key_len ref rows Extra
241
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
242
explain select * from t1 where v='This is a test' order by v;
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t1 ref v v 1031 const 1 Using where
245
explain select * from t1 where v like 'S%' order by v;
246
id select_type table type possible_keys key key_len ref rows Extra
247
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
248
alter table t1 change v v varchar(256);
249
select * from t1 where v like 'This is a test' order by v;
253
select * from t1 where v='This is a test' order by v;
258
select * from t1 where v like 'S%' order by v;
263
explain select * from t1 where v like 'This is a test' order by v;
264
id select_type table type possible_keys key key_len ref rows Extra
265
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
266
explain select * from t1 where v='This is a test' order by v;
267
id select_type table type possible_keys key key_len ref rows Extra
268
1 SIMPLE t1 ref v v 1027 const 1 Using where
269
explain select * from t1 where v like 'S%' order by v;
270
id select_type table type possible_keys key key_len ref rows Extra
271
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
272
alter table t1 change v v varchar(255);
273
select * from t1 where v like 'This is a test' order by v;
277
select * from t1 where v='This is a test' order by v;
282
select * from t1 where v like 'S%' order by v;
287
explain select * from t1 where v like 'This is a test' order by v;
288
id select_type table type possible_keys key key_len ref rows Extra
289
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
290
explain select * from t1 where v='This is a test' order by v;
291
id select_type table type possible_keys key key_len ref rows Extra
292
1 SIMPLE t1 ref v v 1023 const 1 Using where
293
explain select * from t1 where v like 'S%' order by v;
294
id select_type table type possible_keys key key_len ref rows Extra
295
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
296
alter table t1 change v v varchar(254);
297
select * from t1 where v like 'This is a test' order by v;
301
select * from t1 where v='This is a test' order by v;
306
select * from t1 where v like 'S%' order by v;
311
explain select * from t1 where v like 'This is a test' order by v;
312
id select_type table type possible_keys key key_len ref rows Extra
313
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
314
explain select * from t1 where v='This is a test' order by v;
315
id select_type table type possible_keys key key_len ref rows Extra
316
1 SIMPLE t1 ref v v 1019 const 1 Using where
317
explain select * from t1 where v like 'S%' order by v;
318
id select_type table type possible_keys key key_len ref rows Extra
319
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
320
alter table t1 change v v varchar(253);
321
alter table t1 change v v varchar(254), drop key v;
322
alter table t1 change v v varchar(300), add key (v(10));
323
select * from t1 where v like 'This is a test' order by v;
327
select * from t1 where v='This is a test' order by v;
333
Error 1406 Data too long for column 'v' at row 1
334
select * from t1 where v like 'S%' order by v;
339
explain select * from t1 where v like 'This is a test' order by v;
340
id select_type table type possible_keys key key_len ref rows Extra
341
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
342
explain select * from t1 where v='This is a test' order by v;
343
id select_type table type possible_keys key key_len ref rows Extra
344
1 SIMPLE t1 ref v v 43 const 1 Using where
346
Error 1406 Data too long for column 'v' at row 1
347
explain select * from t1 where v like 'S%' order by v;
348
id select_type table type possible_keys key key_len ref rows Extra
349
1 SIMPLE t1 ALL v NULL NULL NULL 16 Using where; Using filesort
351
create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol));
352
insert into t1 values ('test', 'something');
353
update t1 set othercol='somethingelse' where pkcol='test';
358
create table t1 (a int, b varchar(12));
359
insert into t1 values (1, 'A'), (22, NULL);
360
create table t2 (a int);
361
insert into t2 values (22), (22);
362
select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a
367
create table t1 (f1 varchar(6500));
368
create index index1 on t1(f1(10));
369
show create table t1;
371
t1 CREATE TABLE `t1` (
372
`f1` VARCHAR(6500) COLLATE utf8_general_ci DEFAULT NULL,
373
KEY `index1` (`f1`(10))
374
) ENGINE=DEFAULT COLLATE = utf8_general_ci
375
alter table t1 modify f1 varchar(255);
376
show create table t1;
378
t1 CREATE TABLE `t1` (
379
`f1` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
380
KEY `index1` (`f1`(10))
381
) ENGINE=DEFAULT COLLATE = utf8_general_ci
382
alter table t1 modify f1 tinytext;
383
show create table t1;
385
t1 CREATE TABLE `t1` (
386
`f1` TEXT COLLATE utf8_general_ci,
387
KEY `index1` (`f1`(10))
388
) ENGINE=DEFAULT COLLATE = utf8_general_ci
390
DROP TABLE IF EXISTS t1;
391
CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test');
392
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
393
DROP TABLE IF EXISTS t1;
394
CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test');
395
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
396
DROP TABLE IF EXISTS t1;
397
drop table if exists t1, t2, t3;
403
insert into t3 (id, en, cz) values
404
(1,'en string 1','cz string 1'),
405
(2,'en string 2','cz string 2'),
406
(3,'en string 3','cz string 3');
411
insert into t1 (id, name_id) values (1,1), (2,3), (3,3);
412
create table t2 (id int);
413
insert into t2 (id) values (1), (2), (3);
414
select t1.*, t2.id, t3.en, t3.cz from t1 left join t2 on t1.id=t2.id
415
left join t3 on t1.id=t3.id order by t3.id;
416
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
417
def test t1 t1 id id 1 11 1 Y 32768 0 63
418
def test t1 t1 name_id name_id 1 11 1 Y 32768 0 63
419
def test t2 t2 id id 1 11 1 Y 32768 0 63
420
def test t3 t3 en en 8 1020 11 Y 0 0 45
421
def test t3 t3 cz cz 8 1020 11 Y 0 0 45
423
1 1 1 en string 1 cz string 1
424
2 3 2 en string 2 cz string 2
425
3 3 3 en string 3 cz string 3
426
drop table t1, t2, t3;
427
CREATE TABLE t1 (a varchar(2));
428
INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
429
SELECT a,(a + 0) FROM t1 ORDER BY a;
437
SELECT a,(a DIV 2) FROM t1 ORDER BY a;