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) DEFAULT NULL,
7
`c` varchar(3) DEFAULT NULL,
8
`e` enum('abc','def','ghi') DEFAULT NULL,
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));
42
Warning 1071 Specified key was too long; max key length is 767 bytes
43
insert into t1 values ("This is a test ");
44
insert into t1 values ("Some sample data");
45
insert into t1 values (" garbage ");
46
insert into t1 values (" This is a test ");
47
insert into t1 values ("This is a test");
48
insert into t1 values ("Hello world");
49
insert into t1 values ("Foo bar");
50
insert into t1 values ("This is a test");
51
insert into t1 values ("MySQL varchar test");
52
insert into t1 values ("test MySQL varchar");
53
insert into t1 values ("This is a long string to have some random length data included");
54
insert into t1 values ("Short string");
55
insert into t1 values ("VSS");
56
insert into t1 values ("Some samples");
57
insert into t1 values ("Bar foo");
58
insert into t1 values ("Bye");
59
select * from t1 where v like 'This is a test' order by v;
63
select * from t1 where v='This is a test' order by v;
68
select * from t1 where v like 'S%' order by v;
73
explain select * from t1 where v like 'This is a test' order by v;
74
id select_type table type possible_keys key key_len ref rows Extra
75
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
76
explain select * from t1 where v='This is a test' order by v;
77
id select_type table type possible_keys key key_len ref rows Extra
78
1 SIMPLE t1 ref v v 767 const 3 Using where
79
explain select * from t1 where v like 'S%' order by v;
80
id select_type table type possible_keys key key_len ref rows Extra
81
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
82
alter table t1 change v v varchar(255);
83
select * from t1 where v like 'This is a test' order by v;
87
select * from t1 where v='This is a test' order by v;
92
select * from t1 where v like 'S%' order by v;
97
explain select * from t1 where v like 'This is a test' order by v;
98
id select_type table type possible_keys key key_len ref rows Extra
99
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
100
explain select * from t1 where v='This is a test' order by v;
101
id select_type table type possible_keys key key_len ref rows Extra
102
1 SIMPLE t1 ref v v 767 const 3 Using where
103
explain select * from t1 where v like 'S%' order by v;
104
id select_type table type possible_keys key key_len ref rows Extra
105
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
106
alter table t1 change v v varchar(256);
107
select * from t1 where v like 'This is a test' order by v;
111
select * from t1 where v='This is a test' order by v;
116
select * from t1 where v like 'S%' order by v;
121
explain select * from t1 where v like 'This is a test' order by v;
122
id select_type table type possible_keys key key_len ref rows Extra
123
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
124
explain select * from t1 where v='This is a test' order by v;
125
id select_type table type possible_keys key key_len ref rows Extra
126
1 SIMPLE t1 ref v v 767 const 3 Using where
127
explain select * from t1 where v like 'S%' order by v;
128
id select_type table type possible_keys key key_len ref rows Extra
129
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
130
alter table t1 change v v varchar(257);
131
select * from t1 where v like 'This is a test' order by v;
135
select * from t1 where v='This is a test' order by v;
140
select * from t1 where v like 'S%' order by v;
145
explain select * from t1 where v like 'This is a test' order by v;
146
id select_type table type possible_keys key key_len ref rows Extra
147
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
148
explain select * from t1 where v='This is a test' order by v;
149
id select_type table type possible_keys key key_len ref rows Extra
150
1 SIMPLE t1 ref v v 767 const 3 Using where
151
explain select * from t1 where v like 'S%' order by v;
152
id select_type table type possible_keys key key_len ref rows Extra
153
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
154
alter table t1 change v v varchar(258);
155
select * from t1 where v like 'This is a test' order by v;
159
select * from t1 where v='This is a test' order by v;
164
select * from t1 where v like 'S%' order by v;
169
explain select * from t1 where v like 'This is a test' order by v;
170
id select_type table type possible_keys key key_len ref rows Extra
171
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
172
explain select * from t1 where v='This is a test' order by v;
173
id select_type table type possible_keys key key_len ref rows Extra
174
1 SIMPLE t1 ref v v 767 const 3 Using where
175
explain select * from t1 where v like 'S%' order by v;
176
id select_type table type possible_keys key key_len ref rows Extra
177
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
178
alter table t1 change v v varchar(259);
179
select * from t1 where v like 'This is a test' order by v;
183
select * from t1 where v='This is a test' order by v;
188
select * from t1 where v like 'S%' order by v;
193
explain select * from t1 where v like 'This is a test' order by v;
194
id select_type table type possible_keys key key_len ref rows Extra
195
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
196
explain select * from t1 where v='This is a test' order by v;
197
id select_type table type possible_keys key key_len ref rows Extra
198
1 SIMPLE t1 ref v v 767 const 3 Using where
199
explain select * from t1 where v like 'S%' order by v;
200
id select_type table type possible_keys key key_len ref rows Extra
201
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
202
alter table t1 change v v varchar(258);
203
select * from t1 where v like 'This is a test' order by v;
207
select * from t1 where v='This is a test' order by v;
212
select * from t1 where v like 'S%' order by v;
217
explain select * from t1 where v like 'This is a test' order by v;
218
id select_type table type possible_keys key key_len ref rows Extra
219
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
220
explain select * from t1 where v='This is a test' order by v;
221
id select_type table type possible_keys key key_len ref rows Extra
222
1 SIMPLE t1 ref v v 767 const 3 Using where
223
explain select * from t1 where v like 'S%' order by v;
224
id select_type table type possible_keys key key_len ref rows Extra
225
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
226
alter table t1 change v v varchar(257);
227
select * from t1 where v like 'This is a test' order by v;
231
select * from t1 where v='This is a test' order by v;
236
select * from t1 where v like 'S%' order by v;
241
explain select * from t1 where v like 'This is a test' order by v;
242
id select_type table type possible_keys key key_len ref rows Extra
243
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
244
explain select * from t1 where v='This is a test' order by v;
245
id select_type table type possible_keys key key_len ref rows Extra
246
1 SIMPLE t1 ref v v 767 const 3 Using where
247
explain select * from t1 where v like 'S%' order by v;
248
id select_type table type possible_keys key key_len ref rows Extra
249
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
250
alter table t1 change v v varchar(256);
251
select * from t1 where v like 'This is a test' order by v;
255
select * from t1 where v='This is a test' order by v;
260
select * from t1 where v like 'S%' order by v;
265
explain select * from t1 where v like 'This is a test' order by v;
266
id select_type table type possible_keys key key_len ref rows Extra
267
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
268
explain select * from t1 where v='This is a test' order by v;
269
id select_type table type possible_keys key key_len ref rows Extra
270
1 SIMPLE t1 ref v v 767 const 3 Using where
271
explain select * from t1 where v like 'S%' order by v;
272
id select_type table type possible_keys key key_len ref rows Extra
273
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
274
alter table t1 change v v varchar(255);
275
select * from t1 where v like 'This is a test' order by v;
279
select * from t1 where v='This is a test' order by v;
284
select * from t1 where v like 'S%' order by v;
289
explain select * from t1 where v like 'This is a test' order by v;
290
id select_type table type possible_keys key key_len ref rows Extra
291
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
292
explain select * from t1 where v='This is a test' order by v;
293
id select_type table type possible_keys key key_len ref rows Extra
294
1 SIMPLE t1 ref v v 767 const 3 Using where
295
explain select * from t1 where v like 'S%' order by v;
296
id select_type table type possible_keys key key_len ref rows Extra
297
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
298
alter table t1 change v v varchar(254);
299
select * from t1 where v like 'This is a test' order by v;
303
select * from t1 where v='This is a test' order by v;
308
select * from t1 where v like 'S%' order by v;
313
explain select * from t1 where v like 'This is a test' order by v;
314
id select_type table type possible_keys key key_len ref rows Extra
315
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
316
explain select * from t1 where v='This is a test' order by v;
317
id select_type table type possible_keys key key_len ref rows Extra
318
1 SIMPLE t1 ref v v 767 const 3 Using where
319
explain select * from t1 where v like 'S%' order by v;
320
id select_type table type possible_keys key key_len ref rows Extra
321
1 SIMPLE t1 range v v 767 NULL 3 Using where; Using filesort
322
alter table t1 change v v varchar(253);
323
alter table t1 change v v varchar(254), drop key v;
324
alter table t1 change v v varchar(300), add key (v(10));
325
select * from t1 where v like 'This is a test' order by v;
329
select * from t1 where v='This is a test' order by v;
335
Warning 1265 Data truncated for column 'v' at row 1
336
select * from t1 where v like 'S%' order by v;
341
explain select * from t1 where v like 'This is a test' order by v;
342
id select_type table type possible_keys key key_len ref rows Extra
343
1 SIMPLE t1 range v v 43 NULL 4 Using where; Using filesort
344
explain select * from t1 where v='This is a test' order by v;
345
id select_type table type possible_keys key key_len ref rows Extra
346
1 SIMPLE t1 ref v v 43 const 4 Using where
348
Warning 1265 Data truncated for column 'v' at row 1
349
explain select * from t1 where v like 'S%' order by v;
350
id select_type table type possible_keys key key_len ref rows Extra
351
1 SIMPLE t1 range v v 43 NULL 3 Using where; Using filesort
353
create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol));
354
insert into t1 values ('test', 'something');
355
update t1 set othercol='somethingelse' where pkcol='test';
360
create table t1 (a int, b varchar(12));
361
insert into t1 values (1, 'A'), (22, NULL);
362
create table t2 (a int);
363
insert into t2 values (22), (22);
364
select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a
369
create table t1 (f1 varchar(6500));
370
create index index1 on t1(f1(10));
371
show create table t1;
373
t1 CREATE TABLE `t1` (
374
`f1` varchar(6500) DEFAULT NULL,
375
KEY `index1` (`f1`(10))
377
alter table t1 modify f1 varchar(255);
378
show create table t1;
380
t1 CREATE TABLE `t1` (
381
`f1` varchar(255) DEFAULT NULL,
382
KEY `index1` (`f1`(10))
384
alter table t1 modify f1 tinytext;
385
show create table t1;
387
t1 CREATE TABLE `t1` (
389
KEY `index1` (`f1`(10))
392
DROP TABLE IF EXISTS t1;
393
CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test');
394
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
395
DROP TABLE IF EXISTS t1;
396
CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test');
397
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
398
DROP TABLE IF EXISTS t1;
399
drop table if exists t1, t2, t3;
405
insert into t3 (id, en, cz) values
406
(1,'en string 1','cz string 1'),
407
(2,'en string 2','cz string 2'),
408
(3,'en string 3','cz string 3');
413
insert into t1 (id, name_id) values (1,1), (2,3), (3,3);
414
create table t2 (id int);
415
insert into t2 (id) values (1), (2), (3);
416
select t1.*, t2.id, t3.en, t3.cz from t1 left join t2 on t1.id=t2.id
417
left join t3 on t1.id=t3.id order by t3.id;
418
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
419
def test t1 t1 id id 1 11 1 Y 32768 0 63
420
def test t1 t1 name_id name_id 1 11 1 Y 32768 0 63
421
def test t2 t2 id id 1 11 1 Y 32768 0 63
422
def test t3 t3 en en 8 1020 11 Y 0 0 45
423
def test t3 t3 cz cz 8 1020 11 Y 0 0 45
425
1 1 1 en string 1 cz string 1
426
2 3 2 en string 2 cz string 2
427
3 3 3 en string 3 cz string 3
428
drop table t1, t2, t3;
429
CREATE TABLE t1 (a varchar(2));
430
INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
431
SELECT a,(a + 0) FROM t1 ORDER BY a;
439
SELECT a,(a DIV 2) FROM t1 ORDER BY a;