1call mtr.add_suppression("Can't find record in .*"); 2SET SESSION DEFAULT_STORAGE_ENGINE = MEMORY; 3drop table if exists t1,t2,t3,t4; 4drop database if exists mysqltest; 5create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM; 6insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); 7select id, code, name from t1 order by id; 8id code name 91 1 Tim 102 1 Monty 113 2 David 124 2 Erik 135 3 Sasha 146 3 Jeremy 157 4 Matt 16update ignore t1 set id = 8, name = 'Sinisa' where id < 3; 17select id, code, name from t1 order by id; 18id code name 192 1 Monty 203 2 David 214 2 Erik 225 3 Sasha 236 3 Jeremy 247 4 Matt 258 1 Sinisa 26update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; 27select id, code, name from t1 order by id; 28id code name 293 2 David 304 2 Erik 315 3 Sasha 326 3 Jeremy 337 4 Matt 348 1 Sinisa 3512 1 Ralph 36drop table t1; 37CREATE TABLE t1 ( 38id int(11) NOT NULL auto_increment, 39parent_id int(11) DEFAULT '0' NOT NULL, 40level tinyint(4) DEFAULT '0' NOT NULL, 41PRIMARY KEY (id), 42KEY parent_id (parent_id), 43KEY level (level) 44) engine=MyISAM; 45INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); 46update t1 set parent_id=parent_id+100; 47select * from t1 where parent_id=102; 48id parent_id level 498 102 2 509 102 2 5115 102 2 52update t1 set id=id+1000; 53update t1 set id=1024 where id=1009; 54Got one of the listed errors 55select * from t1; 56id parent_id level 571001 100 0 581003 101 1 591004 101 1 601008 102 2 611009 102 2 621017 103 2 631022 104 2 641024 104 2 651028 105 2 661029 105 2 671030 105 2 681031 106 2 691032 106 2 701033 106 2 711203 107 2 721202 107 2 731020 103 2 741157 100 0 751193 105 2 761040 107 2 771002 101 1 781015 102 2 791006 101 1 801034 106 2 811035 106 2 821016 103 2 831007 101 1 841036 107 2 851018 103 2 861026 105 2 871027 105 2 881183 104 2 891038 107 2 901025 105 2 911037 107 2 921021 104 2 931019 103 2 941005 101 1 951179 105 2 96update ignore t1 set id=id+1; 97select * from t1; 98id parent_id level 991001 100 0 1001003 101 1 1011004 101 1 1021008 102 2 1031010 102 2 1041017 103 2 1051023 104 2 1061024 104 2 1071028 105 2 1081029 105 2 1091030 105 2 1101031 106 2 1111032 106 2 1121033 106 2 1131204 107 2 1141203 107 2 1151020 103 2 1161158 100 0 1171194 105 2 1181041 107 2 1191002 101 1 1201015 102 2 1211006 101 1 1221034 106 2 1231035 106 2 1241016 103 2 1251007 101 1 1261036 107 2 1271018 103 2 1281026 105 2 1291027 105 2 1301184 104 2 1311039 107 2 1321025 105 2 1331038 107 2 1341022 104 2 1351019 103 2 1361005 101 1 1371180 105 2 138update ignore t1 set id=1023 where id=1010; 139select * from t1 where parent_id=102; 140id parent_id level 1411008 102 2 1421010 102 2 1431015 102 2 144explain select level from t1 where level=1; 145id select_type table type possible_keys key key_len ref rows Extra 1461 SIMPLE t1 ref level level 1 const # Using index 147explain select level,id from t1 where level=1; 148id select_type table type possible_keys key key_len ref rows Extra 1491 SIMPLE t1 ref level level 1 const # 150explain select level,id,parent_id from t1 where level=1; 151id select_type table type possible_keys key key_len ref rows Extra 1521 SIMPLE t1 ref level level 1 const # 153select level,id from t1 where level=1; 154level id 1551 1003 1561 1004 1571 1002 1581 1006 1591 1007 1601 1005 161select level,id,parent_id from t1 where level=1; 162level id parent_id 1631 1003 101 1641 1004 101 1651 1002 101 1661 1006 101 1671 1007 101 1681 1005 101 169optimize table t1; 170Table Op Msg_type Msg_text 171test.t1 optimize status OK 172show keys from t1; 173Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 174t1 0 PRIMARY 1 id A # NULL NULL BTREE 175t1 1 parent_id 1 parent_id A # NULL NULL BTREE 176t1 1 level 1 level A # NULL NULL BTREE 177drop table t1; 178CREATE TABLE t1 ( 179gesuchnr int(11) DEFAULT '0' NOT NULL, 180benutzer_id int(11) DEFAULT '0' NOT NULL, 181PRIMARY KEY (gesuchnr,benutzer_id) 182) engine=MyISAM; 183replace into t1 (gesuchnr,benutzer_id) values (2,1); 184replace into t1 (gesuchnr,benutzer_id) values (1,1); 185replace into t1 (gesuchnr,benutzer_id) values (1,1); 186select * from t1; 187gesuchnr benutzer_id 1881 1 1892 1 190drop table t1; 191create table t1 (a int) engine=MyISAM; 192insert into t1 values (1), (2); 193optimize table t1; 194Table Op Msg_type Msg_text 195test.t1 optimize status OK 196delete from t1 where a = 1; 197select * from t1; 198a 1992 200check table t1; 201Table Op Msg_type Msg_text 202test.t1 check status OK 203drop table t1; 204create table t1 (a int,b varchar(20)) engine=MyISAM; 205insert into t1 values (1,""), (2,"testing"); 206delete from t1 where a = 1; 207select * from t1; 208a b 2092 testing 210create index skr on t1 (a); 211insert into t1 values (3,""), (4,"testing"); 212analyze table t1; 213Table Op Msg_type Msg_text 214test.t1 analyze status Engine-independent statistics collected 215test.t1 analyze status OK 216show keys from t1; 217Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 218t1 1 skr 1 a A # NULL NULL YES BTREE 219drop table t1; 220create table t1 (a int,b varchar(20),key(a)) engine=MyISAM; 221insert into t1 values (1,""), (2,"testing"); 222select * from t1 where a = 1; 223a b 2241 225drop table t1; 226CREATE TABLE t1 ( 227user_id int(10) DEFAULT '0' NOT NULL, 228name varchar(100), 229phone varchar(100), 230ref_email varchar(100) DEFAULT '' NOT NULL, 231detail varchar(200), 232PRIMARY KEY (user_id,ref_email) 233)engine=MyISAM; 234INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); 235select * from t1 where user_id=10292; 236user_id name phone ref_email detail 23710292 sanjeev 29153373 sansh777@hotmail.com xxx 23810292 shirish 2333604 shirish@yahoo.com ddsds 23910292 sonali 323232 sonali@bolly.com filmstar 240INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); 241select * from t1 where user_id=10292; 242user_id name phone ref_email detail 24310292 sanjeev 29153373 sansh777@hotmail.com xxx 24410292 shirish 2333604 shirish@yahoo.com ddsds 24510292 sonali 323232 sonali@bolly.com filmstar 246select * from t1 where user_id>=10292; 247user_id name phone ref_email detail 24810292 sanjeev 29153373 sansh777@hotmail.com xxx 24910292 shirish 2333604 shirish@yahoo.com ddsds 25010292 sonali 323232 sonali@bolly.com filmstar 25110293 shirish 2333604 shirish@yahoo.com ddsds 252select * from t1 where user_id>10292; 253user_id name phone ref_email detail 25410293 shirish 2333604 shirish@yahoo.com ddsds 255select * from t1 where user_id<10292; 256user_id name phone ref_email detail 25710291 sanjeev 29153373 sansh777@hotmail.com xxx 258drop table t1; 259CREATE TABLE t1 (a int not null, b int not null,c int not null, 260key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM; 261Warnings: 262Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release 263show index from t1; 264Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 265t1 0 PRIMARY 1 a A # NULL NULL BTREE 266t1 0 PRIMARY 2 b A # NULL NULL BTREE 267t1 0 c 1 c A # NULL NULL BTREE 268t1 0 b 1 b A # NULL NULL BTREE 269t1 1 a 1 a A # NULL NULL BTREE 270t1 1 a_2 1 a A # NULL NULL BTREE 271drop table t1; 272create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY; 273alter table t1 engine=MyISAM; 274insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); 275select * from t1; 276col1 col2 2771 1 2785 2 2792 3 2803 4 2814 4 282update t1 set col2='7' where col1='4'; 283select * from t1; 284col1 col2 2851 1 2865 2 2872 3 2883 4 2894 7 290alter table t1 add co3 int not null; 291select * from t1; 292col1 col2 co3 2931 1 0 2945 2 0 2952 3 0 2963 4 0 2974 7 0 298update t1 set col2='9' where col1='2'; 299select * from t1; 300col1 col2 co3 3011 1 0 3025 2 0 3032 9 0 3043 4 0 3054 7 0 306drop table t1; 307create table t1 (a int not null , b int, primary key (a)) engine = MyISAM; 308create table t2 (a int not null , b int, primary key (a)) engine = MEMORY; 309insert into t1 VALUES (1,3) , (2,3), (3,3); 310select * from t1; 311a b 3121 3 3132 3 3143 3 315insert into t2 select * from t1; 316select * from t2; 317a b 3181 3 3192 3 3203 3 321delete from t1 where b = 3; 322select * from t1; 323a b 324insert into t1 select * from t2; 325select * from t1; 326a b 3273 3 3282 3 3291 3 330select * from t2; 331a b 3321 3 3332 3 3343 3 335drop table t1,t2; 336CREATE TABLE t1 ( 337id int(11) NOT NULL auto_increment, 338ggid varchar(32) binary DEFAULT '' NOT NULL, 339email varchar(64) DEFAULT '' NOT NULL, 340passwd varchar(32) binary DEFAULT '' NOT NULL, 341PRIMARY KEY (id), 342UNIQUE ggid (ggid) 343) ENGINE=MyISAM; 344insert into t1 (ggid,passwd) values ('test1','xxx'); 345insert into t1 (ggid,passwd) values ('test2','yyy'); 346insert into t1 (ggid,passwd) values ('test2','this will fail'); 347ERROR 23000: Duplicate entry 'test2' for key 'ggid' 348insert into t1 (ggid,id) values ('this will fail',1); 349ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 350select * from t1 where ggid='test1'; 351id ggid email passwd 3521 test1 xxx 353select * from t1 where passwd='xxx'; 354id ggid email passwd 3551 test1 xxx 356select * from t1 where id=2; 357id ggid email passwd 3582 test2 yyy 359replace into t1 (ggid,id) values ('this will work',1); 360replace into t1 (ggid,passwd) values ('test2','this will work'); 361update t1 set id=100,ggid='test2' where id=1; 362ERROR 23000: Duplicate entry 'test2' for key 'ggid' 363select * from t1; 364id ggid email passwd 3651 this will work 3663 test2 this will work 367select * from t1 where id=1; 368id ggid email passwd 3691 this will work 370select * from t1 where id=999; 371id ggid email passwd 372drop table t1; 373CREATE TABLE t1 ( 374user_name varchar(12), 375password text, 376subscribed char(1), 377user_id int(11) DEFAULT '0' NOT NULL, 378quota bigint(20), 379weight double, 380access_date date, 381access_time time, 382approved datetime, 383dummy_primary_key int(11) NOT NULL auto_increment, 384PRIMARY KEY (dummy_primary_key) 385) ENGINE=MyISAM; 386INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); 387INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 388INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); 389INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); 390INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); 391select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; 392user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key 393user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1 394user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2 395user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3 396user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4 397user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5 398drop table t1; 399CREATE TABLE t1 ( 400id int(11) NOT NULL auto_increment, 401parent_id int(11) DEFAULT '0' NOT NULL, 402level tinyint(4) DEFAULT '0' NOT NULL, 403KEY (id), 404KEY parent_id (parent_id), 405KEY level (level) 406) engine=MyISAM; 407INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1); 408INSERT INTO t1 values (179,5,2); 409update t1 set parent_id=parent_id+100; 410select * from t1 where parent_id=102; 411id parent_id level 4128 102 2 4139 102 2 41415 102 2 415update t1 set id=id+1000; 416update t1 set id=1024 where id=1009; 417select * from t1; 418id parent_id level 4191001 100 0 4201003 101 1 4211004 101 1 4221008 102 2 4231024 102 2 4241017 103 2 4251022 104 2 4261024 104 2 4271028 105 2 4281029 105 2 4291030 105 2 4301031 106 2 4311032 106 2 4321033 106 2 4331203 107 2 4341202 107 2 4351020 103 2 4361157 100 0 4371193 105 2 4381040 107 2 4391002 101 1 4401015 102 2 4411006 101 1 4421034 106 2 4431035 106 2 4441016 103 2 4451007 101 1 4461036 107 2 4471018 103 2 4481026 105 2 4491027 105 2 4501183 104 2 4511038 107 2 4521025 105 2 4531037 107 2 4541021 104 2 4551019 103 2 4561005 101 1 4571179 105 2 458update ignore t1 set id=id+1; 459select * from t1; 460id parent_id level 4611002 100 0 4621004 101 1 4631005 101 1 4641009 102 2 4651025 102 2 4661018 103 2 4671023 104 2 4681025 104 2 4691029 105 2 4701030 105 2 4711031 105 2 4721032 106 2 4731033 106 2 4741034 106 2 4751204 107 2 4761203 107 2 4771021 103 2 4781158 100 0 4791194 105 2 4801041 107 2 4811003 101 1 4821016 102 2 4831007 101 1 4841035 106 2 4851036 106 2 4861017 103 2 4871008 101 1 4881037 107 2 4891019 103 2 4901027 105 2 4911028 105 2 4921184 104 2 4931039 107 2 4941026 105 2 4951038 107 2 4961022 104 2 4971020 103 2 4981006 101 1 4991180 105 2 500update ignore t1 set id=1023 where id=1010; 501select * from t1 where parent_id=102; 502id parent_id level 5031009 102 2 5041025 102 2 5051016 102 2 506explain select level from t1 where level=1; 507id select_type table type possible_keys key key_len ref rows Extra 5081 SIMPLE t1 ref level level 1 const # Using index 509select level,id from t1 where level=1; 510level id 5111 1004 5121 1005 5131 1003 5141 1007 5151 1008 5161 1006 517select level,id,parent_id from t1 where level=1; 518level id parent_id 5191 1004 101 5201 1005 101 5211 1003 101 5221 1007 101 5231 1008 101 5241 1006 101 525select level,id from t1 where level=1 order by id; 526level id 5271 1003 5281 1004 5291 1005 5301 1006 5311 1007 5321 1008 533delete from t1 where level=1; 534select * from t1; 535id parent_id level 5361002 100 0 5371009 102 2 5381025 102 2 5391018 103 2 5401023 104 2 5411025 104 2 5421029 105 2 5431030 105 2 5441031 105 2 5451032 106 2 5461033 106 2 5471034 106 2 5481204 107 2 5491203 107 2 5501021 103 2 5511158 100 0 5521194 105 2 5531041 107 2 5541016 102 2 5551035 106 2 5561036 106 2 5571017 103 2 5581037 107 2 5591019 103 2 5601027 105 2 5611028 105 2 5621184 104 2 5631039 107 2 5641026 105 2 5651038 107 2 5661022 104 2 5671020 103 2 5681180 105 2 569drop table t1; 570CREATE TABLE t1 ( 571sca_code char(6) NOT NULL, 572cat_code char(6) NOT NULL, 573sca_desc varchar(50), 574lan_code char(2) NOT NULL, 575sca_pic varchar(100), 576sca_sdesc varchar(50), 577sca_sch_desc varchar(16), 578PRIMARY KEY (sca_code, cat_code, lan_code), 579INDEX sca_pic (sca_pic) 580) engine = MyISAM ; 581INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING'); 582select count(*) from t1 where sca_code = 'PD'; 583count(*) 5841 585select count(*) from t1 where sca_code <= 'PD'; 586count(*) 5871 588select count(*) from t1 where sca_pic is null; 589count(*) 5902 591alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); 592select count(*) from t1 where sca_code='PD' and sca_pic is null; 593count(*) 5941 595select count(*) from t1 where cat_code='E'; 596count(*) 5970 598alter table t1 drop index sca_pic, add index (sca_pic, cat_code); 599select count(*) from t1 where sca_code='PD' and sca_pic is null; 600count(*) 6011 602select count(*) from t1 where sca_pic >= 'n'; 603count(*) 6041 605select sca_pic from t1 where sca_pic is null; 606sca_pic 607NULL 608NULL 609update t1 set sca_pic="test" where sca_pic is null; 610delete from t1 where sca_code='pd'; 611drop table t1; 612set @a:=now(); 613CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=MyISAM; 614insert into t1 (a) values(1),(2),(3); 615select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; 616a 6171 6182 6193 620select a from t1 natural join t1 as t2 where b >= @a order by a; 621a 6221 6232 6243 625update t1 set a=5 where a=1; 626select a from t1; 627a 6282 6293 6305 631drop table t1; 632create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM; 633insert into t1 values("hello",1),("world",2); 634select * from t1 order by b desc; 635a b 636world 2 637hello 1 638optimize table t1; 639Table Op Msg_type Msg_text 640test.t1 optimize status OK 641show keys from t1; 642Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 643t1 0 PRIMARY 1 a A # NULL NULL BTREE 644drop table t1; 645create table t1 (i int, j int ) ENGINE=MyISAM; 646insert into t1 values (1,2); 647select * from t1 where i=1 and j=2; 648i j 6491 2 650create index ax1 on t1 (i,j); 651select * from t1 where i=1 and j=2; 652i j 6531 2 654drop table t1; 655CREATE TABLE t1 ( 656a int3 unsigned NOT NULL, 657b int1 unsigned NOT NULL, 658UNIQUE (a, b) 659) ENGINE = MyISAM; 660INSERT INTO t1 VALUES (1, 1); 661SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; 662MIN(B) MAX(b) 6631 1 664drop table t1; 665CREATE TABLE t1 (a int unsigned NOT NULL) engine=MyISAM; 666INSERT INTO t1 VALUES (1); 667SELECT * FROM t1; 668a 6691 670DROP TABLE t1; 671create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM; 672insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); 673explain select * from t1 where a > 0 and a < 50; 674id select_type table type possible_keys key key_len ref rows Extra 6751 SIMPLE t1 system PRIMARY NULL NULL NULL # 676drop table t1; 677create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=MyISAM; 678insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 679LOCK TABLES t1 WRITE; 680insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 681ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 682select id from t1; 683id 6840 6851 6862 68799 688select id from t1; 689id 6900 6911 6922 69399 694UNLOCK TABLES; 695DROP TABLE t1; 696create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=MyISAM; 697insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 698LOCK TABLES t1 WRITE; 699begin; 700insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 701ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 702select id from t1; 703id 7040 7051 7062 70799 708insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); 709Warnings: 710Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' 711commit; 712select id,id3 from t1; 713id id3 7140 0 7152 2 7161 1 71799 2 718100 2 719UNLOCK TABLES; 720DROP TABLE t1; 721create table t1 (a char(20), unique (a(5))) engine=MyISAM; 722drop table t1; 723create table t1 (a char(20), index (a(5))) engine=MyISAM; 724show create table t1; 725Table Create Table 726t1 CREATE TABLE `t1` ( 727 `a` char(20) DEFAULT NULL, 728 KEY `a` (`a`(5)) 729) ENGINE=MyISAM DEFAULT CHARSET=latin1 730drop table t1; 731create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM; 732insert into t1 values (NULL),(NULL),(NULL); 733delete from t1 where a=3; 734insert into t1 values (NULL); 735select * from t1; 736a 7371 7382 7394 740alter table t1 add b int; 741select * from t1; 742a b 7431 NULL 7442 NULL 7454 NULL 746drop table t1; 747create table t1 748( 749id int auto_increment primary key, 750name varchar(32) not null, 751value text not null, 752uid int not null, 753unique key(name,uid) 754) engine=MyISAM; 755insert into t1 values (1,'one','one value',101), 756(2,'two','two value',102),(3,'three','three value',103); 757set insert_id=5; 758replace into t1 (value,name,uid) values ('other value','two',102); 759delete from t1 where uid=102; 760set insert_id=5; 761replace into t1 (value,name,uid) values ('other value','two',102); 762set insert_id=6; 763replace into t1 (value,name,uid) values ('other value','two',102); 764select * from t1; 765id name value uid 7661 one one value 101 7673 three three value 103 7686 two other value 102 769drop table t1; 770create database mysqltest; 771create table mysqltest.t1 (a int not null) engine= MyISAM; 772insert into mysqltest.t1 values(1); 773create table mysqltest.t2 (a int not null) engine= MEMORY; 774insert into mysqltest.t2 values(1); 775create table mysqltest.t3 (a int not null) engine= MEMORY; 776insert into mysqltest.t3 values(1); 777commit; 778drop database mysqltest; 779show tables from mysqltest; 780ERROR 42000: Unknown database 'mysqltest' 781set autocommit=0; 782create table t1 (a int not null) engine= MyISAM; 783insert into t1 values(1),(2); 784truncate table t1; 785commit; 786truncate table t1; 787truncate table t1; 788select * from t1; 789a 790insert into t1 values(1),(2); 791delete from t1; 792select * from t1; 793a 794commit; 795drop table t1; 796set autocommit=1; 797create table t1 (a int not null) engine= MyISAM; 798insert into t1 values(1),(2); 799truncate table t1; 800insert into t1 values(1),(2); 801select * from t1; 802a 8031 8042 805truncate table t1; 806insert into t1 values(1),(2); 807delete from t1; 808select * from t1; 809a 810drop table t1; 811create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM; 812insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 813explain select * from t1 order by a; 814id select_type table type possible_keys key key_len ref rows Extra 8151 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 816explain select * from t1 order by b; 817id select_type table type possible_keys key key_len ref rows Extra 8181 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 819explain select * from t1 order by c; 820id select_type table type possible_keys key key_len ref rows Extra 8211 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 822explain select a from t1 order by a; 823id select_type table type possible_keys key key_len ref rows Extra 8241 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index 825explain select b from t1 order by b; 826id select_type table type possible_keys key key_len ref rows Extra 8271 SIMPLE t1 index NULL b 4 NULL # Using index 828explain select a,b from t1 order by b; 829id select_type table type possible_keys key key_len ref rows Extra 8301 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 831explain select a,b from t1; 832id select_type table type possible_keys key key_len ref rows Extra 8331 SIMPLE t1 ALL NULL NULL NULL NULL # 834explain select a,b,c from t1; 835id select_type table type possible_keys key key_len ref rows Extra 8361 SIMPLE t1 ALL NULL NULL NULL NULL # 837drop table t1; 838create table t1 (t int not null default 1, key (t)) engine=MyISAM; 839desc t1; 840Field Type Null Key Default Extra 841t int(11) NO MUL 1 842drop table t1; 843CREATE TABLE t1 ( 844number bigint(20) NOT NULL default '0', 845cname char(15) NOT NULL default '', 846carrier_id smallint(6) NOT NULL default '0', 847privacy tinyint(4) NOT NULL default '0', 848last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 849last_mod_id smallint(6) NOT NULL default '0', 850last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 851last_app_id smallint(6) default '-1', 852version smallint(6) NOT NULL default '0', 853assigned_scps int(11) default '0', 854status tinyint(4) default '0' 855) ENGINE=MyISAM; 856INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); 857INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); 858INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); 859INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); 860INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); 861INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); 862CREATE TABLE t2 ( 863number bigint(20) NOT NULL default '0', 864cname char(15) NOT NULL default '', 865carrier_id smallint(6) NOT NULL default '0', 866privacy tinyint(4) NOT NULL default '0', 867last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 868last_mod_id smallint(6) NOT NULL default '0', 869last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 870last_app_id smallint(6) default '-1', 871version smallint(6) NOT NULL default '0', 872assigned_scps int(11) default '0', 873status tinyint(4) default '0' 874) ENGINE=MyISAM; 875INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); 876INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); 877INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); 878INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); 879select * from t1; 880number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 8814077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1 8829197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0 883650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1 884302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0 8856014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 886333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 887select * from t2; 888number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 8894077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1 8909197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0 891650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1 892333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 893delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null); 894select * from t1; 895number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 8966014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 897333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 898select * from t2; 899number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 900333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 901select * from t2; 902number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 903333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 904drop table t1,t2; 905create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM; 906BEGIN; 907SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 908SELECT @@tx_isolation,@@global.tx_isolation; 909@@tx_isolation @@global.tx_isolation 910SERIALIZABLE REPEATABLE-READ 911insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); 912select id, code, name from t1 order by id; 913id code name 9141 1 Tim 9152 1 Monty 9163 2 David 917COMMIT; 918BEGIN; 919SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 920insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); 921select id, code, name from t1 order by id; 922id code name 9231 1 Tim 9242 1 Monty 9253 2 David 9264 2 Erik 9275 3 Sasha 928COMMIT; 929BEGIN; 930SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 931insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); 932select id, code, name from t1 order by id; 933id code name 9341 1 Tim 9352 1 Monty 9363 2 David 9374 2 Erik 9385 3 Sasha 9396 3 Jeremy 9407 4 Matt 941COMMIT; 942DROP TABLE t1; 943create table t1 (n int(10), d int(10)) engine=MyISAM; 944create table t2 (n int(10), d int(10)) engine=MyISAM; 945insert into t1 values(1,1),(1,2); 946insert into t2 values(1,10),(2,20); 947UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 948select * from t1; 949n d 9501 10 9511 10 952select * from t2; 953n d 9541 30 9552 20 956drop table t1,t2; 957create table t1 (a int, b int) engine=MyISAM; 958insert into t1 values(20,null); 959select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 960t2.b=t3.a; 961b ifnull(t2.b,"this is null") 962NULL this is null 963select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 964t2.b=t3.a order by 1; 965b ifnull(t2.b,"this is null") 966NULL this is null 967insert into t1 values(10,null); 968select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 969t2.b=t3.a order by 1; 970b ifnull(t2.b,"this is null") 971NULL this is null 972NULL this is null 973drop table t1; 974create table t1 (a varchar(10) not null) engine = MEMORY; 975create table t2 (b varchar(10) not null unique) engine=MyISAM; 976select t1.a from t1,t2 where t1.a=t2.b; 977a 978drop table t1,t2; 979create table t1 (a int not null, b int, primary key (a)) engine = MyISAM; 980create table t2 (a int not null, b int, primary key (a)) engine = MyISAM; 981insert into t1 values (10, 20); 982insert into t2 values (10, 20); 983update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; 984drop table t1,t2; 985CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM; 986INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 987UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; 988SELECT * from t1; 989a b 9901 1 991102 2 992103 3 9934 4 9945 5 9956 6 9967 7 9978 8 9989 9 999drop table t1; 1000CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM; 1001CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM; 1002INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12); 1003INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 1004update t1,t2 set t1.a=t1.a+100; 1005select * from t1; 1006a b 1007101 1 1008102 2 1009103 3 1010104 4 1011105 5 1012106 6 1013107 7 1014108 8 1015109 9 1016110 10 1017111 11 1018112 12 1019update t1,t2 set t1.a=t1.a+100 where t1.a=101; 1020select * from t1; 1021a b 1022201 1 1023102 2 1024103 3 1025104 4 1026105 5 1027106 6 1028107 7 1029108 8 1030109 9 1031110 10 1032111 11 1033112 12 1034update t1,t2 set t1.b=t1.b+10 where t1.b=2; 1035select * from t1; 1036a b 1037201 1 1038102 12 1039103 3 1040104 4 1041105 5 1042106 6 1043107 7 1044108 8 1045109 9 1046110 10 1047111 11 1048112 12 1049update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100; 1050select * from t1; 1051a b 1052201 1 1053102 12 1054103 5 1055104 6 1056105 7 1057106 6 1058107 7 1059108 8 1060109 9 1061110 10 1062111 11 1063112 12 1064select * from t2; 1065a b 10661 1 10672 2 10683 13 10694 14 10705 15 10716 6 10727 7 10738 8 10749 9 1075drop table t1,t2; 1076CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY; 1077CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM; 1078SET AUTOCOMMIT=0; 1079INSERT INTO t1 ( B_ID ) VALUES ( 1 ); 1080INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); 1081ROLLBACK; 1082Warnings: 1083Warning 1196 Some non-transactional changed tables couldn't be rolled back 1084SELECT * FROM t1; 1085B_ID 10861 1087drop table t1,t2; 1088create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM; 1089insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); 1090select distinct parent,child from t1 order by parent; 1091parent child 10920 4 10931 2 10941 3 10952 1 1096drop table t1; 1097create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM; 1098create table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY; 1099insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); 1100insert into t2 (a) select b from t1; 1101insert into t1 (b) select b from t2; 1102insert into t2 (a) select b from t1; 1103insert into t1 (a) select b from t2; 1104insert into t2 (a) select b from t1; 1105insert into t1 (a) select b from t2; 1106insert into t2 (a) select b from t1; 1107insert into t1 (a) select b from t2; 1108insert into t2 (a) select b from t1; 1109insert into t1 (a) select b from t2; 1110insert into t2 (a) select b from t1; 1111insert into t1 (a) select b from t2; 1112insert into t2 (a) select b from t1; 1113insert into t1 (a) select b from t2; 1114insert into t2 (a) select b from t1; 1115insert into t1 (a) select b from t2; 1116insert into t2 (a) select b from t1; 1117insert into t1 (a) select b from t2; 1118select count(*) from t1; 1119count(*) 112029267 1121explain select * from t1 where c between 1 and 2500; 1122id select_type table type possible_keys key key_len ref rows Extra 11231 SIMPLE t1 range c c 5 NULL # Using index condition 1124update t1 set c=a; 1125explain select * from t1 where c between 1 and 2500; 1126id select_type table type possible_keys key key_len ref rows Extra 11271 SIMPLE t1 range c c 5 NULL # Using index condition 1128drop table t1,t2; 1129create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM; 1130insert into t1 (id) values (null),(null),(null),(null),(null); 1131update t1 set fk=69 where fk is null order by id limit 1; 1132SELECT * from t1; 1133id fk 11341 69 11352 NULL 11363 NULL 11374 NULL 11385 NULL 1139drop table t1; 1140create table t1 (a int not null, b int not null, key (a)) engine=MyISAM; 1141insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3); 1142SET @tmp=0; 1143update t1 set b=(@tmp:=@tmp+1) order by a; 1144update t1 set b=99 where a=1 order by b asc limit 1; 1145update t1 set b=100 where a=1 order by b desc limit 2; 1146update t1 set a=a+10+b where a=1 order by b; 1147select * from t1 order by a,b; 1148a b 11492 4 11502 5 11512 6 11523 7 11533 8 11543 9 11553 10 11563 11 11573 12 115813 2 1159111 100 1160111 100 1161drop table t1; 1162create table t1 ( c char(8) not null ) engine=MyISAM; 1163insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 1164insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 1165alter table t1 add b char(8) not null; 1166alter table t1 add a char(8) not null; 1167alter table t1 add primary key (a,b,c); 1168update t1 set a=c, b=c; 1169create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM; 1170insert into t2 select * from t1; 1171delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 1172drop table t1,t2; 1173SET AUTOCOMMIT=1; 1174create table t1 (a integer auto_increment primary key) engine=MyISAM; 1175insert into t1 (a) values (NULL),(NULL); 1176truncate table t1; 1177insert into t1 (a) values (NULL),(NULL); 1178SELECT * from t1; 1179a 11801 11812 1182drop table t1; 1183CREATE TABLE t1 (col1 int(1))ENGINE=MyISAM; 1184CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx 1185(stamp))ENGINE=MyISAM; 1186insert into t1 values (1),(2),(3); 1187insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); 1188Warnings: 1189Warning 1265 Data truncated for column 'stamp' at row 3 1190SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < 1191'20020204120000' GROUP BY col1; 1192col1 11931 11942 11953 11964 1197drop table t1,t2; 1198CREATE TABLE t1 ( 1199`id` int(10) unsigned NOT NULL auto_increment, 1200`id_object` int(10) unsigned default '0', 1201`id_version` int(10) unsigned NOT NULL default '1', 1202`label` varchar(100) NOT NULL default '', 1203`description` text, 1204PRIMARY KEY (`id`), 1205KEY `id_object` (`id_object`), 1206KEY `id_version` (`id_version`) 1207) ENGINE=MyISAM; 1208INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL); 1209CREATE TABLE t2 ( 1210`id` int(10) unsigned NOT NULL auto_increment, 1211`id_version` int(10) unsigned NOT NULL default '1', 1212PRIMARY KEY (`id`), 1213KEY `id_version` (`id_version`) 1214) ENGINE=MyISAM; 1215INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); 1216SELECT t2.id, t1.`label` FROM t2 INNER JOIN 1217(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 1218ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); 1219id label 12203382 Test 1221102 Le Pekin (Test) 12221794 Test de resto 12231822 Test 3 12243524 Societe Test 12253525 Fournisseur Test 1226drop table t1,t2; 1227create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM; 1228create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM; 1229create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=MEMORY; 1230create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY; 1231create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM; 1232create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM; 1233insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 1234insert t2 select * from t1; 1235insert t3 select * from t1; 1236insert t4 select * from t1; 1237insert t5 select * from t1; 1238insert t6 select * from t1; 1239checksum table t1, t2, t3, t4, t5, t6, t7 quick; 1240Table Checksum 1241test.t1 3442722830 1242test.t2 NULL 1243test.t3 NULL 1244test.t4 NULL 1245test.t5 3442722830 1246test.t6 NULL 1247test.t7 NULL 1248Warnings: 1249Error 1146 Table 'test.t7' doesn't exist 1250checksum table t1, t2, t3, t4, t5, t6, t7; 1251Table Checksum 1252test.t1 3442722830 1253test.t2 3442722830 1254test.t3 3442722830 1255test.t4 3442722830 1256test.t5 3442722830 1257test.t6 3442722830 1258test.t7 NULL 1259Warnings: 1260Error 1146 Table 'test.t7' doesn't exist 1261checksum table t1, t2, t3, t4, t5, t6, t7 extended; 1262Table Checksum 1263test.t1 3442722830 1264test.t2 3442722830 1265test.t3 3442722830 1266test.t4 3442722830 1267test.t5 3442722830 1268test.t6 3442722830 1269test.t7 NULL 1270Warnings: 1271Error 1146 Table 'test.t7' doesn't exist 1272drop table t1,t2,t3, t4, t5, t6; 1273create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=MyISAM; 1274insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); 1275select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1; 1276trim(name2) 1277fff 1278sss 1279ttt 1280first 1281second 1282third 12831 12842 12853 1286drop table t1; 1287create table t1 (a int) engine=MyISAM; 1288create table t2 like t1; 1289show create table t2; 1290Table Create Table 1291t2 CREATE TABLE `t2` ( 1292 `a` int(11) DEFAULT NULL 1293) ENGINE=MyISAM DEFAULT CHARSET=latin1 1294drop table t1,t2; 1295flush status; 1296show status like "binlog_cache_use"; 1297Variable_name Value 1298Binlog_cache_use 0 1299show status like "binlog_cache_disk_use"; 1300Variable_name Value 1301Binlog_cache_disk_use 0 1302create table t1 (a int) engine=MyISAM; 1303show status like "binlog_cache_use"; 1304Variable_name Value 1305Binlog_cache_use 0 1306show status like "binlog_cache_disk_use"; 1307Variable_name Value 1308Binlog_cache_disk_use 0 1309begin; 1310delete from t1; 1311commit; 1312show status like "binlog_cache_use"; 1313Variable_name Value 1314Binlog_cache_use 0 1315show status like "binlog_cache_disk_use"; 1316Variable_name Value 1317Binlog_cache_disk_use 0 1318drop table t1; 1319create table t1 (c char(10), index (c,c)) engine=MyISAM; 1320ERROR 42S21: Duplicate column name 'c' 1321create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM; 1322ERROR 42S21: Duplicate column name 'c1' 1323create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM; 1324ERROR 42S21: Duplicate column name 'c1' 1325create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM; 1326ERROR 42S21: Duplicate column name 'c1' 1327create table t1 (c1 char(10), c2 char(10)) engine=MyISAM; 1328alter table t1 add key (c1,c1); 1329ERROR 42S21: Duplicate column name 'c1' 1330alter table t1 add key (c2,c1,c1); 1331ERROR 42S21: Duplicate column name 'c1' 1332alter table t1 add key (c1,c2,c1); 1333ERROR 42S21: Duplicate column name 'c1' 1334alter table t1 add key (c1,c1,c2); 1335ERROR 42S21: Duplicate column name 'c1' 1336drop table t1; 1337create table t1(a int(1) , b int(1)) engine=MyISAM; 1338insert into t1 values ('1111', '3333'); 1339select distinct concat(a, b) from t1; 1340concat(a, b) 134111113333 1342drop table t1; 1343create temporary table t1 (a int) engine=MyISAM; 1344insert into t1 values (4711); 1345truncate t1; 1346insert into t1 values (42); 1347select * from t1; 1348a 134942 1350drop table t1; 1351create table t1 (a int) engine=MyISAM; 1352insert into t1 values (4711); 1353truncate t1; 1354insert into t1 values (42); 1355select * from t1; 1356a 135742 1358drop table t1; 1359create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM; 1360insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3); 1361select * from t1 order by a,b,c,d; 1362a b c d e 13631 1 a 1 1 13642 2 b 2 2 13653 3 ab 3 3 1366explain select * from t1 order by a,b,c,d; 1367id select_type table type possible_keys key key_len ref rows Extra 13681 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1369drop table t1; 1370create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM; 1371insert into t1 values ('8', '6'), ('4', '7'); 1372select min(a) from t1; 1373min(a) 13744 1375select min(b) from t1 where a='8'; 1376min(b) 13776 1378drop table t1; 1379create table t1 (x bigint unsigned not null primary key) engine=MyISAM; 1380insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); 1381select * from t1; 1382x 138318446744073709551600 138418446744073709551601 1385select count(*) from t1 where x>0; 1386count(*) 13872 1388select count(*) from t1 where x=0; 1389count(*) 13900 1391select count(*) from t1 where x<0; 1392count(*) 13930 1394select count(*) from t1 where x < -16; 1395count(*) 13960 1397select count(*) from t1 where x = -16; 1398count(*) 13990 1400explain select count(*) from t1 where x > -16; 1401id select_type table type possible_keys key key_len ref rows Extra 14021 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index 1403select count(*) from t1 where x > -16; 1404count(*) 14052 1406select * from t1 where x > -16; 1407x 140818446744073709551600 140918446744073709551601 1410select count(*) from t1 where x = 18446744073709551601; 1411count(*) 14121 1413drop table t1; 1414set default_storage_engine=MyISAM; 1415drop table if exists t1,t2,t3; 1416--- Testing varchar --- 1417--- Testing varchar --- 1418create table t1 (v varchar(10), c char(10), t text); 1419insert into t1 values('+ ', '+ ', '+ '); 1420set @a=repeat(' ',20); 1421insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 1422Warnings: 1423Note 1265 Data truncated for column 'v' at row 1 1424select concat('*',v,'*',c,'*',t,'*') from t1; 1425concat('*',v,'*',c,'*',t,'*') 1426*+ *+*+ * 1427*+ *+*+ * 1428show create table t1; 1429Table Create Table 1430t1 CREATE TABLE `t1` ( 1431 `v` varchar(10) DEFAULT NULL, 1432 `c` char(10) DEFAULT NULL, 1433 `t` text DEFAULT NULL 1434) ENGINE=MyISAM DEFAULT CHARSET=latin1 1435create table t2 like t1; 1436show create table t2; 1437Table Create Table 1438t2 CREATE TABLE `t2` ( 1439 `v` varchar(10) DEFAULT NULL, 1440 `c` char(10) DEFAULT NULL, 1441 `t` text DEFAULT NULL 1442) ENGINE=MyISAM DEFAULT CHARSET=latin1 1443create table t3 select * from t1; 1444show create table t3; 1445Table Create Table 1446t3 CREATE TABLE `t3` ( 1447 `v` varchar(10) DEFAULT NULL, 1448 `c` char(10) DEFAULT NULL, 1449 `t` text DEFAULT NULL 1450) ENGINE=MyISAM DEFAULT CHARSET=latin1 1451alter table t1 modify c varchar(10); 1452show create table t1; 1453Table Create Table 1454t1 CREATE TABLE `t1` ( 1455 `v` varchar(10) DEFAULT NULL, 1456 `c` varchar(10) DEFAULT NULL, 1457 `t` text DEFAULT NULL 1458) ENGINE=MyISAM DEFAULT CHARSET=latin1 1459alter table t1 modify v char(10); 1460show create table t1; 1461Table Create Table 1462t1 CREATE TABLE `t1` ( 1463 `v` char(10) DEFAULT NULL, 1464 `c` varchar(10) DEFAULT NULL, 1465 `t` text DEFAULT NULL 1466) ENGINE=MyISAM DEFAULT CHARSET=latin1 1467alter table t1 modify t varchar(10); 1468Warnings: 1469Note 1265 Data truncated for column 't' at row 2 1470show create table t1; 1471Table Create Table 1472t1 CREATE TABLE `t1` ( 1473 `v` char(10) DEFAULT NULL, 1474 `c` varchar(10) DEFAULT NULL, 1475 `t` varchar(10) DEFAULT NULL 1476) ENGINE=MyISAM DEFAULT CHARSET=latin1 1477select concat('*',v,'*',c,'*',t,'*') from t1; 1478concat('*',v,'*',c,'*',t,'*') 1479*+*+*+ * 1480*+*+*+ * 1481drop table t1,t2,t3; 1482create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); 1483show create table t1; 1484Table Create Table 1485t1 CREATE TABLE `t1` ( 1486 `v` varchar(10) DEFAULT NULL, 1487 `c` char(10) DEFAULT NULL, 1488 `t` text DEFAULT NULL, 1489 KEY `v` (`v`), 1490 KEY `c` (`c`), 1491 KEY `t` (`t`(10)) 1492) ENGINE=MyISAM DEFAULT CHARSET=latin1 1493select count(*) from t1; 1494count(*) 1495270 1496insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 1497select count(*) from t1 where v='a'; 1498count(*) 149910 1500select count(*) from t1 where c='a'; 1501count(*) 150210 1503select count(*) from t1 where t='a'; 1504count(*) 150510 1506select count(*) from t1 where v='a '; 1507count(*) 150810 1509select count(*) from t1 where c='a '; 1510count(*) 151110 1512select count(*) from t1 where t='a '; 1513count(*) 151410 1515select count(*) from t1 where v between 'a' and 'a '; 1516count(*) 151710 1518select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1519count(*) 152010 1521select count(*) from t1 where v like 'a%'; 1522count(*) 152311 1524select count(*) from t1 where c like 'a%'; 1525count(*) 152611 1527select count(*) from t1 where t like 'a%'; 1528count(*) 152911 1530select count(*) from t1 where v like 'a %'; 1531count(*) 15329 1533explain select count(*) from t1 where v='a '; 1534id select_type table type possible_keys key key_len ref rows Extra 15351 SIMPLE t1 ref v v 13 const # Using where; Using index 1536explain select count(*) from t1 where c='a '; 1537id select_type table type possible_keys key key_len ref rows Extra 15381 SIMPLE t1 ref c c 11 const # Using where; Using index 1539explain select count(*) from t1 where t='a '; 1540id select_type table type possible_keys key key_len ref rows Extra 15411 SIMPLE t1 ref t t 13 const # Using where 1542explain select count(*) from t1 where v like 'a%'; 1543id select_type table type possible_keys key key_len ref rows Extra 15441 SIMPLE t1 range v v 13 NULL # Using where; Using index 1545explain select count(*) from t1 where v between 'a' and 'a '; 1546id select_type table type possible_keys key key_len ref rows Extra 15471 SIMPLE t1 ref v v 13 const # Using where; Using index 1548explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1549id select_type table type possible_keys key key_len ref rows Extra 15501 SIMPLE t1 ref v v 13 const # Using where; Using index 1551alter table t1 add unique(v); 1552ERROR 23000: Duplicate entry '{ ' for key 'v_2' 1553show warnings; 1554Level Code Message 1555Error 1062 Duplicate entry 'a\0001' for key 'v_2' 1556alter table t1 add key(v); 1557Warnings: 1558Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release 1559select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 1560qq 1561*a*a*a* 1562*a *a*a * 1563*a *a*a * 1564*a *a*a * 1565*a *a*a * 1566*a *a*a * 1567*a *a*a * 1568*a *a*a * 1569*a *a*a * 1570*a *a*a * 1571explain select * from t1 where v='a'; 1572id select_type table type possible_keys key key_len ref rows Extra 15731 SIMPLE t1 ref v,v_2 # 13 const # # 1574select v,count(*) from t1 group by v limit 10; 1575v count(*) 1576a 1 1577a 10 1578b 10 1579c 10 1580d 10 1581e 10 1582f 10 1583g 10 1584h 10 1585i 10 1586select v,count(t) from t1 group by v limit 10; 1587v count(t) 1588a 1 1589a 10 1590b 10 1591c 10 1592d 10 1593e 10 1594f 10 1595g 10 1596h 10 1597i 10 1598select v,count(c) from t1 group by v limit 10; 1599v count(c) 1600a 1 1601a 10 1602b 10 1603c 10 1604d 10 1605e 10 1606f 10 1607g 10 1608h 10 1609i 10 1610select sql_big_result v,count(t) from t1 group by v limit 10; 1611v count(t) 1612a 1 1613a 10 1614b 10 1615c 10 1616d 10 1617e 10 1618f 10 1619g 10 1620h 10 1621i 10 1622select sql_big_result v,count(c) from t1 group by v limit 10; 1623v count(c) 1624a 1 1625a 10 1626b 10 1627c 10 1628d 10 1629e 10 1630f 10 1631g 10 1632h 10 1633i 10 1634select c,count(*) from t1 group by c limit 10; 1635c count(*) 1636a 1 1637a 10 1638b 10 1639c 10 1640d 10 1641e 10 1642f 10 1643g 10 1644h 10 1645i 10 1646select c,count(t) from t1 group by c limit 10; 1647c count(t) 1648a 1 1649a 10 1650b 10 1651c 10 1652d 10 1653e 10 1654f 10 1655g 10 1656h 10 1657i 10 1658select sql_big_result c,count(t) from t1 group by c limit 10; 1659c count(t) 1660a 1 1661a 10 1662b 10 1663c 10 1664d 10 1665e 10 1666f 10 1667g 10 1668h 10 1669i 10 1670select t,count(*) from t1 group by t limit 10; 1671t count(*) 1672a 1 1673a 10 1674b 10 1675c 10 1676d 10 1677e 10 1678f 10 1679g 10 1680h 10 1681i 10 1682select t,count(t) from t1 group by t limit 10; 1683t count(t) 1684a 1 1685a 10 1686b 10 1687c 10 1688d 10 1689e 10 1690f 10 1691g 10 1692h 10 1693i 10 1694select sql_big_result t,count(t) from t1 group by t limit 10; 1695t count(t) 1696a 1 1697a 10 1698b 10 1699c 10 1700d 10 1701e 10 1702f 10 1703g 10 1704h 10 1705i 10 1706alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 1707show create table t1; 1708Table Create Table 1709t1 CREATE TABLE `t1` ( 1710 `v` varchar(300) DEFAULT NULL, 1711 `c` char(10) DEFAULT NULL, 1712 `t` text DEFAULT NULL, 1713 KEY `c` (`c`), 1714 KEY `t` (`t`(10)), 1715 KEY `v` (`v`) 1716) ENGINE=MyISAM DEFAULT CHARSET=latin1 1717select count(*) from t1 where v='a'; 1718count(*) 171910 1720select count(*) from t1 where v='a '; 1721count(*) 172210 1723select count(*) from t1 where v between 'a' and 'a '; 1724count(*) 172510 1726select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1727count(*) 172810 1729select count(*) from t1 where v like 'a%'; 1730count(*) 173111 1732select count(*) from t1 where v like 'a %'; 1733count(*) 17349 1735explain select count(*) from t1 where v='a '; 1736id select_type table type possible_keys key key_len ref rows Extra 17371 SIMPLE t1 ref v v 303 const # Using where; Using index 1738explain select count(*) from t1 where v like 'a%'; 1739id select_type table type possible_keys key key_len ref rows Extra 17401 SIMPLE t1 range v v 303 NULL # Using where; Using index 1741explain select count(*) from t1 where v between 'a' and 'a '; 1742id select_type table type possible_keys key key_len ref rows Extra 17431 SIMPLE t1 ref v v 303 const # Using where; Using index 1744explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1745id select_type table type possible_keys key key_len ref rows Extra 17461 SIMPLE t1 ref v v 303 const # Using where; Using index 1747explain select * from t1 where v='a'; 1748id select_type table type possible_keys key key_len ref rows Extra 17491 SIMPLE t1 ref v v 303 const # # 1750select v,count(*) from t1 group by v limit 10; 1751v count(*) 1752a 1 1753a 10 1754b 10 1755c 10 1756d 10 1757e 10 1758f 10 1759g 10 1760h 10 1761i 10 1762select v,count(t) from t1 group by v limit 10; 1763v count(t) 1764a 1 1765a 10 1766b 10 1767c 10 1768d 10 1769e 10 1770f 10 1771g 10 1772h 10 1773i 10 1774select sql_big_result v,count(t) from t1 group by v limit 10; 1775v count(t) 1776a 1 1777a 10 1778b 10 1779c 10 1780d 10 1781e 10 1782f 10 1783g 10 1784h 10 1785i 10 1786alter table t1 drop key v, add key v (v(30)); 1787show create table t1; 1788Table Create Table 1789t1 CREATE TABLE `t1` ( 1790 `v` varchar(300) DEFAULT NULL, 1791 `c` char(10) DEFAULT NULL, 1792 `t` text DEFAULT NULL, 1793 KEY `c` (`c`), 1794 KEY `t` (`t`(10)), 1795 KEY `v` (`v`(30)) 1796) ENGINE=MyISAM DEFAULT CHARSET=latin1 1797select count(*) from t1 where v='a'; 1798count(*) 179910 1800select count(*) from t1 where v='a '; 1801count(*) 180210 1803select count(*) from t1 where v between 'a' and 'a '; 1804count(*) 180510 1806select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1807count(*) 180810 1809select count(*) from t1 where v like 'a%'; 1810count(*) 181111 1812select count(*) from t1 where v like 'a %'; 1813count(*) 18149 1815explain select count(*) from t1 where v='a '; 1816id select_type table type possible_keys key key_len ref rows Extra 18171 SIMPLE t1 ref v v 33 const # Using where 1818explain select count(*) from t1 where v like 'a%'; 1819id select_type table type possible_keys key key_len ref rows Extra 18201 SIMPLE t1 range v v 33 NULL # Using where 1821explain select count(*) from t1 where v between 'a' and 'a '; 1822id select_type table type possible_keys key key_len ref rows Extra 18231 SIMPLE t1 ref v v 33 const # Using where 1824explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1825id select_type table type possible_keys key key_len ref rows Extra 18261 SIMPLE t1 ref v v 33 const # Using where 1827explain select * from t1 where v='a'; 1828id select_type table type possible_keys key key_len ref rows Extra 18291 SIMPLE t1 ref v v 33 const # # 1830select v,count(*) from t1 group by v limit 10; 1831v count(*) 1832a 1 1833a 10 1834b 10 1835c 10 1836d 10 1837e 10 1838f 10 1839g 10 1840h 10 1841i 10 1842select v,count(t) from t1 group by v limit 10; 1843v count(t) 1844a 1 1845a 10 1846b 10 1847c 10 1848d 10 1849e 10 1850f 10 1851g 10 1852h 10 1853i 10 1854select sql_big_result v,count(t) from t1 group by v limit 10; 1855v count(t) 1856a 1 1857a 10 1858b 10 1859c 10 1860d 10 1861e 10 1862f 10 1863g 10 1864h 10 1865i 10 1866alter table t1 modify v varchar(600), drop key v, add key v (v); 1867show create table t1; 1868Table Create Table 1869t1 CREATE TABLE `t1` ( 1870 `v` varchar(600) DEFAULT NULL, 1871 `c` char(10) DEFAULT NULL, 1872 `t` text DEFAULT NULL, 1873 KEY `c` (`c`), 1874 KEY `t` (`t`(10)), 1875 KEY `v` (`v`) 1876) ENGINE=MyISAM DEFAULT CHARSET=latin1 1877select v,count(*) from t1 group by v limit 10; 1878v count(*) 1879a 1 1880a 10 1881b 10 1882c 10 1883d 10 1884e 10 1885f 10 1886g 10 1887h 10 1888i 10 1889select v,count(t) from t1 group by v limit 10; 1890v count(t) 1891a 1 1892a 10 1893b 10 1894c 10 1895d 10 1896e 10 1897f 10 1898g 10 1899h 10 1900i 10 1901select sql_big_result v,count(t) from t1 group by v limit 10; 1902v count(t) 1903a 1 1904a 10 1905b 10 1906c 10 1907d 10 1908e 10 1909f 10 1910g 10 1911h 10 1912i 10 1913drop table t1; 1914create table t1 (a char(10), unique (a)); 1915insert into t1 values ('a '); 1916insert into t1 values ('a '); 1917ERROR 23000: Duplicate entry 'a' for key 'a' 1918alter table t1 modify a varchar(10); 1919insert into t1 values ('a '),('a '),('a '),('a '); 1920ERROR 23000: Duplicate entry 'a ' for key 'a' 1921insert into t1 values ('a '); 1922ERROR 23000: Duplicate entry 'a ' for key 'a' 1923insert into t1 values ('a '); 1924ERROR 23000: Duplicate entry 'a ' for key 'a' 1925insert into t1 values ('a '); 1926ERROR 23000: Duplicate entry 'a ' for key 'a' 1927update t1 set a='a ' where a like 'a%'; 1928select concat(a,'.') from t1; 1929concat(a,'.') 1930a . 1931update t1 set a='abc ' where a like 'a '; 1932select concat(a,'.') from t1; 1933concat(a,'.') 1934a . 1935update t1 set a='a ' where a like 'a %'; 1936select concat(a,'.') from t1; 1937concat(a,'.') 1938a . 1939update t1 set a='a ' where a like 'a '; 1940select concat(a,'.') from t1; 1941concat(a,'.') 1942a . 1943drop table t1; 1944create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 1945show create table t1; 1946Table Create Table 1947t1 CREATE TABLE `t1` ( 1948 `v` varchar(10) DEFAULT NULL, 1949 `c` char(10) DEFAULT NULL, 1950 `t` text DEFAULT NULL, 1951 KEY `v` (`v`(5)), 1952 KEY `c` (`c`(5)), 1953 KEY `t` (`t`(5)) 1954) ENGINE=MyISAM DEFAULT CHARSET=latin1 1955drop table t1; 1956create table t1 (v char(10) character set utf8); 1957show create table t1; 1958Table Create Table 1959t1 CREATE TABLE `t1` ( 1960 `v` char(10) CHARACTER SET utf8 DEFAULT NULL 1961) ENGINE=MyISAM DEFAULT CHARSET=latin1 1962drop table t1; 1963create table t1 (v varchar(10), c char(10)) row_format=fixed; 1964show create table t1; 1965Table Create Table 1966t1 CREATE TABLE `t1` ( 1967 `v` varchar(10) DEFAULT NULL, 1968 `c` char(10) DEFAULT NULL 1969) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED 1970insert into t1 values('a','a'),('a ','a '); 1971select concat('*',v,'*',c,'*') from t1; 1972concat('*',v,'*',c,'*') 1973*a*a* 1974*a *a* 1975drop table t1; 1976create table t1 (v varchar(65530), key(v(10))); 1977insert into t1 values(repeat('a',65530)); 1978select length(v) from t1 where v=repeat('a',65530); 1979length(v) 198065530 1981drop table t1; 1982create table t1(a int, b varchar(12), key ba(b, a)); 1983insert into t1 values (1, 'A'), (20, NULL); 1984explain select * from t1 where a=20 and b is null; 1985id select_type table type possible_keys key key_len ref rows Extra 19861 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index 1987select * from t1 where a=20 and b is null; 1988a b 198920 NULL 1990drop table t1; 1991create table t1 (v varchar(65530), key(v)); 1992Warnings: 1993Note 1071 Specified key was too long; max key length is 1000 bytes 1994drop table t1; 1995SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1996create table t1 (v varchar(65536)); 1997Warnings: 1998Note 1246 Converting column 'v' from VARCHAR to TEXT 1999show create table t1; 2000Table Create Table 2001t1 CREATE TABLE `t1` ( 2002 `v` mediumtext DEFAULT NULL 2003) ENGINE=MyISAM DEFAULT CHARSET=latin1 2004drop table t1; 2005SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2006create table t1 (v varchar(65530) character set utf8); 2007Warnings: 2008Note 1246 Converting column 'v' from VARCHAR to TEXT 2009show create table t1; 2010Table Create Table 2011t1 CREATE TABLE `t1` ( 2012 `v` mediumtext CHARACTER SET utf8 DEFAULT NULL 2013) ENGINE=MyISAM DEFAULT CHARSET=latin1 2014drop table t1; 2015set default_storage_engine=MEMORY; 2016create table t1 (v varchar(16384)) engine=MyISAM; 2017drop table t1; 2018create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM; 2019insert into t1 values ('8', '6'), ('4', '7'); 2020select min(a) from t1; 2021min(a) 20224 2023select min(b) from t1 where a='8'; 2024min(b) 20256 2026drop table t1; 2027CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM; 2028insert into t1 (b) values (1); 2029replace into t1 (b) values (2), (1), (3); 2030select * from t1; 2031a b 20323 1 20332 2 20344 3 2035truncate table t1; 2036insert into t1 (b) values (1); 2037replace into t1 (b) values (2); 2038replace into t1 (b) values (1); 2039replace into t1 (b) values (3); 2040select * from t1; 2041a b 20423 1 20432 2 20444 3 2045drop table t1; 2046create table t1 (rowid int not null auto_increment, val int not null,primary 2047key (rowid), unique(val)) engine=MyISAM; 2048replace into t1 (val) values ('1'),('2'); 2049replace into t1 (val) values ('1'),('2'); 2050insert into t1 (val) values ('1'),('2'); 2051ERROR 23000: Duplicate entry '1' for key 'val' 2052select * from t1; 2053rowid val 20543 1 20554 2 2056drop table t1; 2057CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM; 2058INSERT INTO t1 (GRADE) VALUES (151),(252),(343); 2059SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300; 2060GRADE 2061252 2062SELECT GRADE FROM t1 WHERE GRADE= 151; 2063GRADE 2064151 2065DROP TABLE t1; 2066create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM; 2067create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM; 2068insert into t2 values ('aa','cc'); 2069insert into t1 values ('aa','bb'),('aa','cc'); 2070delete t1 from t1,t2 where f1=f3 and f4='cc'; 2071select * from t1; 2072f1 f2 2073drop table t1,t2; 2074connect a,localhost,root,,; 2075connect b,localhost,root,,; 2076connection a; 2077create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2078insert into t1(a) values (1),(2),(3); 2079commit; 2080connection b; 2081set autocommit = 0; 2082update t1 set b = 5 where a = 2; 2083commit; 2084connection a; 2085create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end | 2086set autocommit = 0; 2087connection a; 2088insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100), 2089(11),(21),(31),(41),(51),(61),(71),(81),(91),(101), 2090(12),(22),(32),(42),(52),(62),(72),(82),(92),(102), 2091(13),(23),(33),(43),(53),(63),(73),(83),(93),(103), 2092(14),(24),(34),(44),(54),(64),(74),(84),(94),(104); 2093connection b; 2094commit; 2095connection a; 2096commit; 2097drop trigger t1t; 2098drop table t1; 2099disconnect a; 2100disconnect b; 2101connect a,localhost,root,,; 2102connect b,localhost,root,,; 2103connection a; 2104create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2105create table t2(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2106create table t3(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2107create table t4(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2108create table t5(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2109insert into t1(a) values (1),(2),(3); 2110insert into t2(a) values (1),(2),(3); 2111insert into t3(a) values (1),(2),(3); 2112insert into t4(a) values (1),(2),(3); 2113insert into t3(a) values (5),(7),(8); 2114insert into t4(a) values (5),(7),(8); 2115insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12); 2116create trigger t1t before insert on t1 for each row begin 2117INSERT INTO t2 SET a = NEW.a; 2118end | 2119create trigger t2t before insert on t2 for each row begin 2120DELETE FROM t3 WHERE a = NEW.a; 2121end | 2122create trigger t3t before delete on t3 for each row begin 2123UPDATE t4 SET b = b + 1 WHERE a = OLD.a; 2124end | 2125create trigger t4t before update on t4 for each row begin 2126UPDATE t5 SET b = b + 1 where a = NEW.a; 2127end | 2128commit; 2129set autocommit = 0; 2130update t1 set b = b + 5 where a = 1; 2131update t2 set b = b + 5 where a = 1; 2132update t3 set b = b + 5 where a = 1; 2133update t4 set b = b + 5 where a = 1; 2134insert into t5(a) values(20); 2135commit; 2136connection b; 2137set autocommit = 0; 2138insert into t1(a) values(7); 2139insert into t2(a) values(8); 2140delete from t2 where a = 3; 2141update t4 set b = b + 1 where a = 3; 2142commit; 2143drop trigger t1t; 2144drop trigger t2t; 2145drop trigger t3t; 2146drop trigger t4t; 2147drop table t1, t2, t3, t4, t5; 2148connection default; 2149disconnect a; 2150disconnect b; 2151create table t1(a date) engine=MyISAM; 2152create table t2(a date, key(a)) engine=MyISAM; 2153insert into t1 values('2005-10-01'); 2154insert into t2 values('2005-10-01'); 2155select * from t1, t2 2156where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; 2157a a 21582005-10-01 2005-10-01 2159drop table t1, t2; 2160create table t1 (id int not null, f_id int not null, f int not null, 2161primary key(f_id, id)) engine=MyISAM; 2162create table t2 (id int not null,s_id int not null,s varchar(200), 2163primary key(id)) engine=MyISAM; 2164INSERT INTO t1 VALUES (8, 1, 3); 2165INSERT INTO t1 VALUES (1, 2, 1); 2166INSERT INTO t2 VALUES (1, 0, ''); 2167INSERT INTO t2 VALUES (8, 1, ''); 2168commit; 2169DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) 2170WHERE mm.id IS NULL; 2171select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) 2172where mm.id is null lock in share mode; 2173id f_id f 2174drop table t1,t2; 2175connect a,localhost,root,,; 2176connect b,localhost,root,,; 2177connection a; 2178create table t1(a int not null, b int, primary key(a)) engine=MyISAM; 2179insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 2180commit; 2181set autocommit = 0; 2182SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2183update t1 set b = 5 where b = 1; 2184connection b; 2185set autocommit = 0; 2186SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2187select * from t1 where a = 7 and b = 3 for update; 2188a b 21897 3 2190connection a; 2191commit; 2192connection b; 2193commit; 2194drop table t1; 2195connection default; 2196disconnect a; 2197disconnect b; 2198CREATE TABLE t1 ( a int ) ENGINE=MyISAM; 2199BEGIN; 2200INSERT INTO t1 VALUES (1); 2201OPTIMIZE TABLE t1; 2202Table Op Msg_type Msg_text 2203test.t1 optimize status OK 2204DROP TABLE t1; 2205