1
drop table if exists t1,t2,t3,t4,t5,t6,t7;
2
CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000));
4
Field Type Null Key Default Extra
10
CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000));
12
Note 1246 Converting column 'b' from VARBINARY to BLOB
13
Note 1246 Converting column 'c' from VARCHAR to TEXT
14
CREATE TABLE t4 (c varchar(65530) character set utf8 not null);
16
Note 1246 Converting column 'c' from VARCHAR to TEXT
18
Field Type Null Key Default Extra
22
create table t3 (a long, b long byte);
25
t3 CREATE TABLE `t3` (
28
) ENGINE=MyISAM DEFAULT CHARSET=latin1
31
t4 CREATE TABLE `t4` (
32
`c` mediumtext CHARACTER SET utf8 NOT NULL
33
) ENGINE=MyISAM DEFAULT CHARSET=latin1
34
drop table t1,t2,t3,t4;
35
CREATE TABLE t1 (a char(257) default "hello");
36
ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
37
CREATE TABLE t2 (a char(256));
38
ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
39
CREATE TABLE t1 (a varchar(70000) default "hello");
40
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
41
CREATE TABLE t2 (a blob default "hello");
42
ERROR 42000: BLOB/TEXT column 'a' can't have a default value
43
drop table if exists t1,t2;
44
create table t1 (nr int(5) not null auto_increment,b blob,str char(10), primary key (nr));
45
insert into t1 values (null,"a","A");
46
insert into t1 values (null,"bbb","BBB");
47
insert into t1 values (null,"ccc","CCC");
48
select last_insert_id();
51
select * from t1,t1 as t2;
63
create table t1 (a text);
64
insert into t1 values ('where');
65
update t1 set a='Where';
70
create table t1 (t text,c char(10),b blob, d varbinary(10));
71
insert into t1 values (NULL,NULL,NULL,NULL);
72
insert into t1 values ("","","","");
73
insert into t1 values ("hello","hello","hello","hello");
74
insert into t1 values ("HELLO","HELLO","HELLO","HELLO");
75
insert into t1 values ("HELLO MY","HELLO MY","HELLO MY","HELLO MY");
76
insert into t1 values ("a","a","a","a");
77
insert into t1 values (1,1,1,1);
78
insert into t1 values (NULL,NULL,NULL,NULL);
79
update t1 set c="",b=null where c="1";
81
show full fields from t1;
82
Field Type Collation Null Key Default Extra Privileges Comment
83
t text latin1_swedish_ci YES NULL #
84
c char(10) latin1_swedish_ci YES NULL #
85
b blob NULL YES NULL #
86
d varbinary(10) NULL YES NULL #
88
show full fields from t1;
89
Field Type Collation Null Key Default Extra Privileges Comment
90
t text latin1_swedish_ci YES NULL #
91
c char(10) latin1_swedish_ci YES NULL #
92
b blob NULL YES NULL #
93
d varbinary(10) NULL YES NULL #
95
select t from t1 where t like "hello";
99
select c from t1 where c like "hello";
103
select b from t1 where b like "hello";
106
select d from t1 where d like "hello";
109
select c from t1 having c like "hello";
113
select d from t1 having d like "hello";
116
select t from t1 where t like "%HELLO%";
121
select c from t1 where c like "%HELLO%";
126
select b from t1 where b like "%HELLO%";
130
select d from t1 where d like "%HELLO%";
134
select c from t1 having c like "%HELLO%";
139
select d from t1 having d like "%HELLO%";
143
select d from t1 having d like "%HE%LLO%";
147
select t from t1 order by t;
157
select c from t1 order by c;
167
select b from t1 order by b;
177
select d from t1 order by d;
187
select distinct t from t1;
195
select distinct b from t1;
203
select distinct t from t1 order by t;
211
select distinct b from t1 order by b;
219
select t from t1 group by t;
227
select b from t1 group by b;
235
set option sql_big_tables=1;
236
select distinct t from t1;
244
select distinct b from t1;
252
select distinct t from t1 order by t;
260
select distinct b from t1 order by b;
268
select distinct c from t1;
275
select distinct d from t1;
284
select distinct c from t1 order by c;
291
select distinct d from t1 order by d;
300
select c from t1 group by c;
307
select d from t1 group by d;
316
set option sql_big_tables=0;
317
select distinct * from t1;
321
hello hello hello hello
322
HELLO HELLO HELLO HELLO
323
HELLO MY HELLO MY HELLO MY HELLO MY
326
select t,count(*) from t1 group by t;
334
select b,count(*) from t1 group by b;
342
select c,count(*) from t1 group by c;
349
select d,count(*) from t1 group by d;
359
create table t1 (a text, unique (a(2100)));
360
ERROR 42000: Specified key was too long; max key length is 1332 bytes
361
create table t1 (a text, key (a(2100)));
363
Warning 1071 Specified key was too long; max key length is 1332 bytes
364
show create table t1;
366
t1 CREATE TABLE `t1` (
369
) ENGINE=MyISAM DEFAULT CHARSET=latin1
372
t1_id bigint(21) NOT NULL auto_increment,
373
_field_72 varchar(128) DEFAULT '' NOT NULL,
374
_field_95 varchar(32),
375
_field_115 tinyint(4) DEFAULT '0' NOT NULL,
376
_field_122 tinyint(4) DEFAULT '0' NOT NULL,
377
_field_126 tinyint(4),
378
_field_134 tinyint(4),
380
UNIQUE _field_72 (_field_72),
381
KEY _field_115 (_field_115),
382
KEY _field_122 (_field_122)
384
INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
385
INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
386
INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
388
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
389
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
390
PRIMARY KEY (seq_0_id,seq_1_id)
392
INSERT INTO t2 VALUES (1,1);
393
INSERT INTO t2 VALUES (2,1);
394
INSERT INTO t2 VALUES (2,2);
396
t3_id bigint(21) NOT NULL auto_increment,
397
_field_131 varchar(128),
398
_field_133 tinyint(4) DEFAULT '0' NOT NULL,
399
_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
400
_field_137 tinyint(4),
401
_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
403
_field_142 tinyint(4) DEFAULT '0' NOT NULL,
404
_field_145 tinyint(4) DEFAULT '0' NOT NULL,
405
_field_148 tinyint(4) DEFAULT '0' NOT NULL,
407
KEY _field_133 (_field_133),
408
KEY _field_135 (_field_135),
409
KEY _field_139 (_field_139),
410
KEY _field_142 (_field_142),
411
KEY _field_145 (_field_145),
412
KEY _field_148 (_field_148)
414
INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
415
INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
417
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
418
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
419
PRIMARY KEY (seq_0_id,seq_1_id)
421
INSERT INTO t4 VALUES (1,1);
422
INSERT INTO t4 VALUES (2,1);
424
t5_id bigint(21) NOT NULL auto_increment,
425
_field_149 tinyint(4),
426
_field_156 varchar(128) DEFAULT '' NOT NULL,
427
_field_157 varchar(128) DEFAULT '' NOT NULL,
428
_field_158 varchar(128) DEFAULT '' NOT NULL,
429
_field_159 varchar(128) DEFAULT '' NOT NULL,
430
_field_160 varchar(128) DEFAULT '' NOT NULL,
431
_field_161 varchar(128) DEFAULT '' NOT NULL,
433
KEY _field_156 (_field_156),
434
KEY _field_157 (_field_157),
435
KEY _field_158 (_field_158),
436
KEY _field_159 (_field_159),
437
KEY _field_160 (_field_160),
438
KEY _field_161 (_field_161)
440
INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
441
INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
443
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
444
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
445
PRIMARY KEY (seq_0_id,seq_1_id)
447
INSERT INTO t6 VALUES (1,1);
448
INSERT INTO t6 VALUES (1,2);
449
INSERT INTO t6 VALUES (2,2);
451
t7_id bigint(21) NOT NULL auto_increment,
452
_field_143 tinyint(4),
453
_field_165 varchar(32),
454
_field_166 smallint(6) DEFAULT '0' NOT NULL,
456
KEY _field_166 (_field_166)
458
INSERT INTO t7 VALUES (1,0,'High',1);
459
INSERT INTO t7 VALUES (2,0,'Medium',2);
460
INSERT INTO t7 VALUES (3,0,'Low',3);
461
select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
462
replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
465
1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
468
drop table t1,t2,t3,t4,t5,t6,t7;
469
create table t1 (a blob);
470
insert into t1 values ("empty"),("");
471
select a,reverse(a) from t1;
476
create table t1 (a blob, key (a(10)));
477
insert into t1 values ("bye"),("hello"),("hello"),("hello word");
478
select * from t1 where a like "hello%";
485
f1 int(11) DEFAULT '0' NOT NULL,
486
f2 varchar(16) DEFAULT '' NOT NULL,
488
KEY index_name (f1,f2,f5(16))
490
INSERT INTO t1 VALUES (0,'traktor','1111111111111');
491
INSERT INTO t1 VALUES (1,'traktor','1111111111111111111111111');
492
select count(*) from t1 where f2='traktor';
496
create table t1 (foobar tinyblob not null, boggle smallint not null, key (foobar(32), boggle));
497
insert into t1 values ('fish', 10),('bear', 20);
498
select foobar, boggle from t1 where foobar = 'fish';
501
select foobar, boggle from t1 where foobar = 'fish' and boggle = 10;
505
create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
507
Warning 1101 BLOB/TEXT column 'imagem' can't have a default value
508
insert into t1 (id) values (1);
510
charset(load_file('../std_data_ln/words.dat')),
511
collation(load_file('../std_data_ln/words.dat')),
512
coercibility(load_file('../std_data_ln/words.dat'));
513
charset(load_file('../std_data_ln/words.dat')) collation(load_file('../std_data_ln/words.dat')) coercibility(load_file('../std_data_ln/words.dat'))
515
explain extended select
516
charset(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')),
517
collation(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')),
518
coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'));
519
id select_type table type possible_keys key key_len ref rows filtered Extra
520
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
522
Note 1003 select charset(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `charset(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`,collation(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `collation(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`,coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`
523
update t1 set imagem=load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat') where id=1;
524
select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1;
525
if(imagem is null, "ERROR", "OK") length(imagem)
528
create table t1 select load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat') l;
529
show full fields from t1;
530
Field Type Collation Null Key Default Extra Privileges Comment
531
l longblob NULL YES NULL #
533
create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));
534
insert into t1 (txt) values ('Chevy'), ('Chevy ');
535
ERROR 23000: Duplicate entry 'Chevy ' for key 'txt_index'
536
insert into t1 (txt) values ('Chevy'), ('CHEVY');
537
ERROR 23000: Duplicate entry 'Chevy' for key 'txt_index'
538
alter table t1 drop index txt_index, add index txt_index (txt(20));
539
insert into t1 (txt) values ('Chevy ');
540
select * from t1 where txt='Chevy';
544
select * from t1 where txt='Chevy ';
548
select * from t1 where txt='Chevy ' or txt='Chevy';
552
select * from t1 where txt='Chevy' or txt='Chevy ';
556
select * from t1 where id='1' or id='2';
560
insert into t1 (txt) values('Ford');
561
select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
566
select * from t1 where txt='Chevy' or txt='Chevy ';
570
select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
574
select * from t1 where txt in ('Chevy ','Chevy');
578
select * from t1 where txt in ('Chevy');
582
select * from t1 where txt between 'Chevy' and 'Chevy';
586
select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
590
select * from t1 where txt between 'Chevy' and 'Chevy ';
594
select * from t1 where txt < 'Chevy ';
596
select * from t1 where txt <= 'Chevy';
600
select * from t1 where txt > 'Chevy';
603
select * from t1 where txt >= 'Chevy';
609
create table t1 (id integer primary key auto_increment, txt text, index txt_index (txt (20)));
610
insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
611
select * from t1 where txt='Chevy' or txt is NULL;
616
explain select * from t1 where txt='Chevy' or txt is NULL;
617
id select_type table type possible_keys key key_len ref rows Extra
618
1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
619
select * from t1 where txt='Chevy ';
623
select * from t1 where txt='Chevy ' or txt='Chevy';
627
select * from t1 where txt='Chevy' or txt='Chevy ';
631
select * from t1 where id='1' or id='2';
635
insert into t1 (txt) values('Ford');
636
select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
641
select * from t1 where txt='Chevy' or txt='Chevy ';
645
select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
649
select * from t1 where txt in ('Chevy ','Chevy');
653
select * from t1 where txt in ('Chevy');
657
select * from t1 where txt between 'Chevy' and 'Chevy';
661
select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
665
select * from t1 where txt between 'Chevy' and 'Chevy ';
669
select * from t1 where txt < 'Chevy ';
671
select * from t1 where txt < 'Chevy ' or txt is NULL;
674
select * from t1 where txt <= 'Chevy';
678
select * from t1 where txt > 'Chevy';
681
select * from t1 where txt >= 'Chevy';
686
alter table t1 modify column txt blob;
687
explain select * from t1 where txt='Chevy' or txt is NULL;
688
id select_type table type possible_keys key key_len ref rows Extra
689
1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
690
select * from t1 where txt='Chevy' or txt is NULL;
694
explain select * from t1 where txt='Chevy' or txt is NULL order by txt;
695
id select_type table type possible_keys key key_len ref rows Extra
696
1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where; Using filesort
697
select * from t1 where txt='Chevy' or txt is NULL order by txt;
702
CREATE TABLE t1 ( i int(11) NOT NULL default '0', c text NOT NULL, d varchar(1) NOT NULL DEFAULT ' ', PRIMARY KEY (i), KEY (c(1),d));
703
INSERT t1 (i, c) VALUES (1,''),(2,''),(3,'asdfh'),(4,'');
704
select max(i) from t1 where c = '';
708
create table t1 (a int, b int, c tinyblob, d int, e int);
709
alter table t1 add primary key (a,b,c(255),d);
710
alter table t1 add key (a,b,d,e);
711
show create table t1;
713
t1 CREATE TABLE `t1` (
714
`a` int(11) NOT NULL DEFAULT '0',
715
`b` int(11) NOT NULL DEFAULT '0',
716
`c` tinyblob NOT NULL,
717
`d` int(11) NOT NULL DEFAULT '0',
718
`e` int(11) DEFAULT NULL,
719
PRIMARY KEY (`a`,`b`,`c`(255),`d`),
720
KEY `a` (`a`,`b`,`d`,`e`)
721
) ENGINE=MyISAM DEFAULT CHARSET=latin1
723
CREATE table t1 (a blob);
724
insert into t1 values ('b'),('a\0'),('a'),('a '),('aa'),(NULL);
725
select hex(a) from t1 order by a;
733
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
741
alter table t1 modify a varbinary(5);
742
select hex(a) from t1 order by a;
750
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
758
alter table t1 modify a char(5);
759
select hex(a) from t1 order by a;
767
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
775
alter table t1 modify a binary(5);
776
select hex(a) from t1 order by a;
784
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
793
create table t1 (a text default '');
795
Warning 1101 BLOB/TEXT column 'a' can't have a default value
796
show create table t1;
798
t1 CREATE TABLE `t1` (
800
) ENGINE=MyISAM DEFAULT CHARSET=latin1
801
insert into t1 values (default);
806
set @@sql_mode='TRADITIONAL';
807
create table t1 (a text default '');
808
ERROR 42000: BLOB/TEXT column 'a' can't have a default value
810
CREATE TABLE t (c TEXT CHARSET ASCII);
811
INSERT INTO t (c) VALUES (REPEAT('1',65537));
813
Warning 1265 Data truncated for column 'c' at row 1
814
INSERT INTO t (c) VALUES (REPEAT('2',65536));
816
Warning 1265 Data truncated for column 'c' at row 1
817
INSERT INTO t (c) VALUES (REPEAT('3',65535));
818
SELECT LENGTH(c), CHAR_LENGTH(c) FROM t;
819
LENGTH(c) CHAR_LENGTH(c)