1SET SESSION DEFAULT_STORAGE_ENGINE = MEMORY; 2drop table if exists t1,t2,t3,t4; 3drop database if exists mysqltest; 4create 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; 5insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); 6select id, code, name from t1 order by id; 7id code name 81 1 Tim 92 1 Monty 103 2 David 114 2 Erik 125 3 Sasha 136 3 Jeremy 147 4 Matt 15update ignore t1 set id = 8, name = 'Sinisa' where id < 3; 16select id, code, name from t1 order by id; 17id code name 182 1 Monty 193 2 David 204 2 Erik 215 3 Sasha 226 3 Jeremy 237 4 Matt 248 1 Sinisa 25update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; 26select id, code, name from t1 order by id; 27id code name 283 2 David 294 2 Erik 305 3 Sasha 316 3 Jeremy 327 4 Matt 338 1 Sinisa 3412 1 Ralph 35drop table t1; 36CREATE TABLE t1 ( 37id int(11) NOT NULL auto_increment, 38parent_id int(11) DEFAULT '0' NOT NULL, 39level tinyint(4) DEFAULT '0' NOT NULL, 40PRIMARY KEY (id), 41KEY parent_id (parent_id), 42KEY level (level) 43) engine=MyISAM; 44INSERT 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); 45update t1 set parent_id=parent_id+100; 46select * from t1 where parent_id=102; 47id parent_id level 488 102 2 499 102 2 5015 102 2 51update t1 set id=id+1000; 52update t1 set id=1024 where id=1009; 53Got one of the listed errors 54select * from t1; 55id parent_id level 561001 100 0 571003 101 1 581004 101 1 591008 102 2 601009 102 2 611017 103 2 621022 104 2 631024 104 2 641028 105 2 651029 105 2 661030 105 2 671031 106 2 681032 106 2 691033 106 2 701203 107 2 711202 107 2 721020 103 2 731157 100 0 741193 105 2 751040 107 2 761002 101 1 771015 102 2 781006 101 1 791034 106 2 801035 106 2 811016 103 2 821007 101 1 831036 107 2 841018 103 2 851026 105 2 861027 105 2 871183 104 2 881038 107 2 891025 105 2 901037 107 2 911021 104 2 921019 103 2 931005 101 1 941179 105 2 95update ignore t1 set id=id+1; 96select * from t1; 97id parent_id level 981001 100 0 991003 101 1 1001004 101 1 1011008 102 2 1021010 102 2 1031017 103 2 1041023 104 2 1051024 104 2 1061028 105 2 1071029 105 2 1081030 105 2 1091031 106 2 1101032 106 2 1111033 106 2 1121204 107 2 1131203 107 2 1141020 103 2 1151158 100 0 1161194 105 2 1171041 107 2 1181002 101 1 1191015 102 2 1201006 101 1 1211034 106 2 1221035 106 2 1231016 103 2 1241007 101 1 1251036 107 2 1261018 103 2 1271026 105 2 1281027 105 2 1291184 104 2 1301039 107 2 1311025 105 2 1321038 107 2 1331022 104 2 1341019 103 2 1351005 101 1 1361180 105 2 137update ignore t1 set id=1023 where id=1010; 138select * from t1 where parent_id=102; 139id parent_id level 1401008 102 2 1411010 102 2 1421015 102 2 143explain select level from t1 where level=1; 144id select_type table type possible_keys key key_len ref rows Extra 1451 SIMPLE t1 ref level level 1 const # Using index 146explain select level,id from t1 where level=1; 147id select_type table type possible_keys key key_len ref rows Extra 1481 SIMPLE t1 ref level level 1 const # NULL 149explain select level,id,parent_id from t1 where level=1; 150id select_type table type possible_keys key key_len ref rows Extra 1511 SIMPLE t1 ref level level 1 const # NULL 152select level,id from t1 where level=1; 153level id 1541 1003 1551 1004 1561 1002 1571 1006 1581 1007 1591 1005 160select level,id,parent_id from t1 where level=1; 161level id parent_id 1621 1003 101 1631 1004 101 1641 1002 101 1651 1006 101 1661 1007 101 1671 1005 101 168optimize table t1; 169Table Op Msg_type Msg_text 170test.t1 optimize status OK 171show keys from t1; 172Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 173t1 0 PRIMARY 1 id A # NULL NULL BTREE 174t1 1 parent_id 1 parent_id A # NULL NULL BTREE 175t1 1 level 1 level A # NULL NULL BTREE 176drop table t1; 177CREATE TABLE t1 ( 178gesuchnr int(11) DEFAULT '0' NOT NULL, 179benutzer_id int(11) DEFAULT '0' NOT NULL, 180PRIMARY KEY (gesuchnr,benutzer_id) 181) engine=MyISAM; 182replace into t1 (gesuchnr,benutzer_id) values (2,1); 183replace into t1 (gesuchnr,benutzer_id) values (1,1); 184replace into t1 (gesuchnr,benutzer_id) values (1,1); 185select * from t1; 186gesuchnr benutzer_id 1871 1 1882 1 189drop table t1; 190create table t1 (a int) engine=MyISAM; 191insert into t1 values (1), (2); 192optimize table t1; 193Table Op Msg_type Msg_text 194test.t1 optimize status OK 195delete from t1 where a = 1; 196select * from t1; 197a 1982 199check table t1; 200Table Op Msg_type Msg_text 201test.t1 check status OK 202drop table t1; 203create table t1 (a int,b varchar(20)) engine=MyISAM; 204insert into t1 values (1,""), (2,"testing"); 205delete from t1 where a = 1; 206select * from t1; 207a b 2082 testing 209create index skr on t1 (a); 210insert into t1 values (3,""), (4,"testing"); 211analyze table t1; 212Table Op Msg_type Msg_text 213test.t1 analyze status OK 214show keys from t1; 215Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 216t1 1 skr 1 a A # NULL NULL YES BTREE 217drop table t1; 218create table t1 (a int,b varchar(20),key(a)) engine=MyISAM; 219insert into t1 values (1,""), (2,"testing"); 220select * from t1 where a = 1; 221a b 2221 223drop table t1; 224CREATE TABLE t1 ( 225user_id int(10) DEFAULT '0' NOT NULL, 226name varchar(100), 227phone varchar(100), 228ref_email varchar(100) DEFAULT '' NOT NULL, 229detail varchar(200), 230PRIMARY KEY (user_id,ref_email) 231)engine=MyISAM; 232INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); 233select * from t1 where user_id=10292; 234user_id name phone ref_email detail 23510292 sanjeev 29153373 sansh777@hotmail.com xxx 23610292 shirish 2333604 shirish@yahoo.com ddsds 23710292 sonali 323232 sonali@bolly.com filmstar 238INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); 239select * from t1 where user_id=10292; 240user_id name phone ref_email detail 24110292 sanjeev 29153373 sansh777@hotmail.com xxx 24210292 shirish 2333604 shirish@yahoo.com ddsds 24310292 sonali 323232 sonali@bolly.com filmstar 244select * from t1 where user_id>=10292; 245user_id name phone ref_email detail 24610292 sanjeev 29153373 sansh777@hotmail.com xxx 24710292 shirish 2333604 shirish@yahoo.com ddsds 24810292 sonali 323232 sonali@bolly.com filmstar 24910293 shirish 2333604 shirish@yahoo.com ddsds 250select * from t1 where user_id>10292; 251user_id name phone ref_email detail 25210293 shirish 2333604 shirish@yahoo.com ddsds 253select * from t1 where user_id<10292; 254user_id name phone ref_email detail 25510291 sanjeev 29153373 sansh777@hotmail.com xxx 256drop table t1; 257CREATE TABLE t1 (a int not null, b int not null,c int not null, 258key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM; 259Warnings: 260Note 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 261show index from t1; 262Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 263t1 0 PRIMARY 1 a A # NULL NULL BTREE 264t1 0 PRIMARY 2 b A # NULL NULL BTREE 265t1 0 c 1 c A # NULL NULL BTREE 266t1 0 b 1 b A # NULL NULL BTREE 267t1 1 a 1 a A # NULL NULL BTREE 268t1 1 a_2 1 a A # NULL NULL BTREE 269drop table t1; 270create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY; 271alter table t1 engine=MyISAM; 272insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); 273select * from t1; 274col1 col2 2751 1 2765 2 2772 3 2783 4 2794 4 280update t1 set col2='7' where col1='4'; 281select * from t1; 282col1 col2 2831 1 2845 2 2852 3 2863 4 2874 7 288alter table t1 add co3 int not null; 289select * from t1; 290col1 col2 co3 2911 1 0 2925 2 0 2932 3 0 2943 4 0 2954 7 0 296update t1 set col2='9' where col1='2'; 297select * from t1; 298col1 col2 co3 2991 1 0 3005 2 0 3012 9 0 3023 4 0 3034 7 0 304drop table t1; 305create table t1 (a int not null , b int, primary key (a)) engine = MyISAM; 306create table t2 (a int not null , b int, primary key (a)) engine = MEMORY; 307insert into t1 VALUES (1,3) , (2,3), (3,3); 308select * from t1; 309a b 3101 3 3112 3 3123 3 313insert into t2 select * from t1; 314select * from t2; 315a b 3161 3 3172 3 3183 3 319delete from t1 where b = 3; 320select * from t1; 321a b 322insert into t1 select * from t2; 323select * from t1; 324a b 3253 3 3262 3 3271 3 328select * from t2; 329a b 3301 3 3312 3 3323 3 333drop table t1,t2; 334CREATE TABLE t1 ( 335id int(11) NOT NULL auto_increment, 336ggid varchar(32) binary DEFAULT '' NOT NULL, 337email varchar(64) DEFAULT '' NOT NULL, 338passwd varchar(32) binary DEFAULT '' NOT NULL, 339PRIMARY KEY (id), 340UNIQUE ggid (ggid) 341) ENGINE=MyISAM; 342insert into t1 (ggid,passwd) values ('test1','xxx'); 343insert into t1 (ggid,passwd) values ('test2','yyy'); 344insert into t1 (ggid,passwd) values ('test2','this will fail'); 345ERROR 23000: Duplicate entry 'test2' for key 'ggid' 346insert into t1 (ggid,id) values ('this will fail',1); 347ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 348select * from t1 where ggid='test1'; 349id ggid email passwd 3501 test1 xxx 351select * from t1 where passwd='xxx'; 352id ggid email passwd 3531 test1 xxx 354select * from t1 where id=2; 355id ggid email passwd 3562 test2 yyy 357replace into t1 (ggid,id) values ('this will work',1); 358replace into t1 (ggid,passwd) values ('test2','this will work'); 359update t1 set id=100,ggid='test2' where id=1; 360ERROR 23000: Duplicate entry 'test2' for key 'ggid' 361select * from t1; 362id ggid email passwd 3631 this will work 3643 test2 this will work 365select * from t1 where id=1; 366id ggid email passwd 3671 this will work 368select * from t1 where id=999; 369id ggid email passwd 370drop table t1; 371CREATE TABLE t1 ( 372user_name varchar(12), 373password text, 374subscribed char(1), 375user_id int(11) DEFAULT '0' NOT NULL, 376quota bigint(20), 377weight double, 378access_date date, 379access_time time, 380approved datetime, 381dummy_primary_key int(11) NOT NULL auto_increment, 382PRIMARY KEY (dummy_primary_key) 383) ENGINE=MyISAM; 384INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); 385INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 386INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); 387INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); 388INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); 389select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; 390user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key 391user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1 392user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2 393user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3 394user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4 395user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5 396drop table t1; 397CREATE TABLE t1 ( 398id int(11) NOT NULL auto_increment, 399parent_id int(11) DEFAULT '0' NOT NULL, 400level tinyint(4) DEFAULT '0' NOT NULL, 401KEY (id), 402KEY parent_id (parent_id), 403KEY level (level) 404) engine=MyISAM; 405INSERT 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); 406INSERT INTO t1 values (179,5,2); 407update t1 set parent_id=parent_id+100; 408select * from t1 where parent_id=102; 409id parent_id level 4108 102 2 4119 102 2 41215 102 2 413update t1 set id=id+1000; 414update t1 set id=1024 where id=1009; 415select * from t1; 416id parent_id level 4171001 100 0 4181003 101 1 4191004 101 1 4201008 102 2 4211024 102 2 4221017 103 2 4231022 104 2 4241024 104 2 4251028 105 2 4261029 105 2 4271030 105 2 4281031 106 2 4291032 106 2 4301033 106 2 4311203 107 2 4321202 107 2 4331020 103 2 4341157 100 0 4351193 105 2 4361040 107 2 4371002 101 1 4381015 102 2 4391006 101 1 4401034 106 2 4411035 106 2 4421016 103 2 4431007 101 1 4441036 107 2 4451018 103 2 4461026 105 2 4471027 105 2 4481183 104 2 4491038 107 2 4501025 105 2 4511037 107 2 4521021 104 2 4531019 103 2 4541005 101 1 4551179 105 2 456update ignore t1 set id=id+1; 457select * from t1; 458id parent_id level 4591002 100 0 4601004 101 1 4611005 101 1 4621009 102 2 4631025 102 2 4641018 103 2 4651023 104 2 4661025 104 2 4671029 105 2 4681030 105 2 4691031 105 2 4701032 106 2 4711033 106 2 4721034 106 2 4731204 107 2 4741203 107 2 4751021 103 2 4761158 100 0 4771194 105 2 4781041 107 2 4791003 101 1 4801016 102 2 4811007 101 1 4821035 106 2 4831036 106 2 4841017 103 2 4851008 101 1 4861037 107 2 4871019 103 2 4881027 105 2 4891028 105 2 4901184 104 2 4911039 107 2 4921026 105 2 4931038 107 2 4941022 104 2 4951020 103 2 4961006 101 1 4971180 105 2 498update ignore t1 set id=1023 where id=1010; 499select * from t1 where parent_id=102; 500id parent_id level 5011009 102 2 5021025 102 2 5031016 102 2 504explain select level from t1 where level=1; 505id select_type table type possible_keys key key_len ref rows Extra 5061 SIMPLE t1 ref level level 1 const # Using index 507select level,id from t1 where level=1; 508level id 5091 1004 5101 1005 5111 1003 5121 1007 5131 1008 5141 1006 515select level,id,parent_id from t1 where level=1; 516level id parent_id 5171 1004 101 5181 1005 101 5191 1003 101 5201 1007 101 5211 1008 101 5221 1006 101 523select level,id from t1 where level=1 order by id; 524level id 5251 1003 5261 1004 5271 1005 5281 1006 5291 1007 5301 1008 531delete from t1 where level=1; 532select * from t1; 533id parent_id level 5341002 100 0 5351009 102 2 5361025 102 2 5371018 103 2 5381023 104 2 5391025 104 2 5401029 105 2 5411030 105 2 5421031 105 2 5431032 106 2 5441033 106 2 5451034 106 2 5461204 107 2 5471203 107 2 5481021 103 2 5491158 100 0 5501194 105 2 5511041 107 2 5521016 102 2 5531035 106 2 5541036 106 2 5551017 103 2 5561037 107 2 5571019 103 2 5581027 105 2 5591028 105 2 5601184 104 2 5611039 107 2 5621026 105 2 5631038 107 2 5641022 104 2 5651020 103 2 5661180 105 2 567drop table t1; 568CREATE TABLE t1 ( 569sca_code char(6) NOT NULL, 570cat_code char(6) NOT NULL, 571sca_desc varchar(50), 572lan_code char(2) NOT NULL, 573sca_pic varchar(100), 574sca_sdesc varchar(50), 575sca_sch_desc varchar(16), 576PRIMARY KEY (sca_code, cat_code, lan_code), 577INDEX sca_pic (sca_pic) 578) engine = MyISAM ; 579INSERT 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'); 580select count(*) from t1 where sca_code = 'PD'; 581count(*) 5821 583select count(*) from t1 where sca_code <= 'PD'; 584count(*) 5851 586select count(*) from t1 where sca_pic is null; 587count(*) 5882 589alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); 590select count(*) from t1 where sca_code='PD' and sca_pic is null; 591count(*) 5921 593select count(*) from t1 where cat_code='E'; 594count(*) 5950 596alter table t1 drop index sca_pic, add index (sca_pic, cat_code); 597select count(*) from t1 where sca_code='PD' and sca_pic is null; 598count(*) 5991 600select count(*) from t1 where sca_pic >= 'n'; 601count(*) 6021 603select sca_pic from t1 where sca_pic is null; 604sca_pic 605NULL 606NULL 607update t1 set sca_pic="test" where sca_pic is null; 608delete from t1 where sca_code='pd'; 609drop table t1; 610set @a:=now(); 611CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=MyISAM; 612insert into t1 (a) values(1),(2),(3); 613select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; 614a 6151 6162 6173 618select a from t1 natural join t1 as t2 where b >= @a order by a; 619a 6201 6212 6223 623update t1 set a=5 where a=1; 624select a from t1; 625a 6262 6273 6285 629drop table t1; 630create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM; 631insert into t1 values("hello",1),("world",2); 632select * from t1 order by b desc; 633a b 634world 2 635hello 1 636optimize table t1; 637Table Op Msg_type Msg_text 638test.t1 optimize status OK 639show keys from t1; 640Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 641t1 0 PRIMARY 1 a A # NULL NULL BTREE 642drop table t1; 643create table t1 (i int, j int ) ENGINE=MyISAM; 644insert into t1 values (1,2); 645select * from t1 where i=1 and j=2; 646i j 6471 2 648create index ax1 on t1 (i,j); 649select * from t1 where i=1 and j=2; 650i j 6511 2 652drop table t1; 653CREATE TABLE t1 ( 654a int3 unsigned NOT NULL, 655b int1 unsigned NOT NULL, 656UNIQUE (a, b) 657) ENGINE = MyISAM; 658INSERT INTO t1 VALUES (1, 1); 659SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; 660MIN(B) MAX(b) 6611 1 662drop table t1; 663CREATE TABLE t1 (a int unsigned NOT NULL) engine=MyISAM; 664INSERT INTO t1 VALUES (1); 665SELECT * FROM t1; 666a 6671 668DROP TABLE t1; 669create 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; 670insert 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); 671explain select * from t1 where a > 0 and a < 50; 672id select_type table type possible_keys key key_len ref rows Extra 6731 SIMPLE t1 system PRIMARY NULL NULL NULL # NULL 674drop table t1; 675create 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; 676insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 677LOCK TABLES t1 WRITE; 678insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 679ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 680select id from t1; 681id 6820 6831 6842 68599 686select id from t1; 687id 6880 6891 6902 69199 692UNLOCK TABLES; 693DROP TABLE t1; 694create 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; 695insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 696LOCK TABLES t1 WRITE; 697begin; 698insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 699ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 700select id from t1; 701id 7020 7031 7042 70599 706insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); 707commit; 708select id,id3 from t1; 709id id3 7100 0 7112 2 7121 1 71399 2 714100 2 715UNLOCK TABLES; 716DROP TABLE t1; 717create table t1 (a char(20), unique (a(5))) engine=MyISAM; 718drop table t1; 719create table t1 (a char(20), index (a(5))) engine=MyISAM; 720show create table t1; 721Table Create Table 722t1 CREATE TABLE `t1` ( 723 `a` char(20) DEFAULT NULL, 724 KEY `a` (`a`(5)) 725) ENGINE=MyISAM DEFAULT CHARSET=latin1 726drop table t1; 727create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM; 728insert into t1 values (NULL),(NULL),(NULL); 729delete from t1 where a=3; 730insert into t1 values (NULL); 731select * from t1; 732a 7331 7342 7354 736alter table t1 add b int; 737select * from t1; 738a b 7391 NULL 7402 NULL 7414 NULL 742drop table t1; 743create table t1 744( 745id int auto_increment primary key, 746name varchar(32) not null, 747value text not null, 748uid int not null, 749unique key(name,uid) 750) engine=MyISAM; 751insert into t1 values (1,'one','one value',101), 752(2,'two','two value',102),(3,'three','three value',103); 753set insert_id=5; 754replace into t1 (value,name,uid) values ('other value','two',102); 755delete from t1 where uid=102; 756set insert_id=5; 757replace into t1 (value,name,uid) values ('other value','two',102); 758set insert_id=6; 759replace into t1 (value,name,uid) values ('other value','two',102); 760select * from t1; 761id name value uid 7621 one one value 101 7633 three three value 103 7646 two other value 102 765drop table t1; 766create database mysqltest; 767create table mysqltest.t1 (a int not null) engine= MyISAM; 768insert into mysqltest.t1 values(1); 769create table mysqltest.t2 (a int not null) engine= MEMORY; 770insert into mysqltest.t2 values(1); 771create table mysqltest.t3 (a int not null) engine= MEMORY; 772insert into mysqltest.t3 values(1); 773commit; 774drop database mysqltest; 775show tables from mysqltest; 776ERROR 42000: Unknown database 'mysqltest' 777set autocommit=0; 778create table t1 (a int not null) engine= MyISAM; 779insert into t1 values(1),(2); 780truncate table t1; 781commit; 782truncate table t1; 783truncate table t1; 784select * from t1; 785a 786insert into t1 values(1),(2); 787delete from t1; 788select * from t1; 789a 790commit; 791drop table t1; 792set autocommit=1; 793create table t1 (a int not null) engine= MyISAM; 794insert into t1 values(1),(2); 795truncate table t1; 796insert into t1 values(1),(2); 797select * from t1; 798a 7991 8002 801truncate table t1; 802insert into t1 values(1),(2); 803delete from t1; 804select * from t1; 805a 806drop table t1; 807create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM; 808insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 809explain select * from t1 order by a; 810id select_type table type possible_keys key key_len ref rows Extra 8111 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 812explain select * from t1 order by b; 813id select_type table type possible_keys key key_len ref rows Extra 8141 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 815explain select * from t1 order by c; 816id select_type table type possible_keys key key_len ref rows Extra 8171 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 818explain select a from t1 order by a; 819id select_type table type possible_keys key key_len ref rows Extra 8201 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index 821explain select b from t1 order by b; 822id select_type table type possible_keys key key_len ref rows Extra 8231 SIMPLE t1 index NULL b 4 NULL # Using index 824explain select a,b from t1 order by b; 825id select_type table type possible_keys key key_len ref rows Extra 8261 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 827explain select a,b from t1; 828id select_type table type possible_keys key key_len ref rows Extra 8291 SIMPLE t1 ALL NULL NULL NULL NULL # NULL 830explain select a,b,c from t1; 831id select_type table type possible_keys key key_len ref rows Extra 8321 SIMPLE t1 ALL NULL NULL NULL NULL # NULL 833drop table t1; 834create table t1 (t int not null default 1, key (t)) engine=MyISAM; 835desc t1; 836Field Type Null Key Default Extra 837t int(11) NO MUL 1 838drop table t1; 839CREATE TABLE t1 ( 840number bigint(20) NOT NULL default '0', 841cname char(15) NOT NULL default '', 842carrier_id smallint(6) NOT NULL default '0', 843privacy tinyint(4) NOT NULL default '0', 844last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 845last_mod_id smallint(6) NOT NULL default '0', 846last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 847last_app_id smallint(6) default '-1', 848version smallint(6) NOT NULL default '0', 849assigned_scps int(11) default '0', 850status tinyint(4) default '0' 851) ENGINE=MyISAM; 852INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); 853INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); 854INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); 855INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); 856INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); 857INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); 858CREATE TABLE t2 ( 859number bigint(20) NOT NULL default '0', 860cname char(15) NOT NULL default '', 861carrier_id smallint(6) NOT NULL default '0', 862privacy tinyint(4) NOT NULL default '0', 863last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 864last_mod_id smallint(6) NOT NULL default '0', 865last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 866last_app_id smallint(6) default '-1', 867version smallint(6) NOT NULL default '0', 868assigned_scps int(11) default '0', 869status tinyint(4) default '0' 870) ENGINE=MyISAM; 871INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); 872INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); 873INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); 874INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); 875select * from t1; 876number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 8774077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1 8789197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0 879650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1 880302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0 8816014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 882333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 883select * from t2; 884number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 8854077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1 8869197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0 887650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1 888333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 889delete 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); 890select * from t1; 891number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 8926014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 893333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 894select * from t2; 895number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 896333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 897select * from t2; 898number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 899333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 900drop table t1,t2; 901create 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; 902BEGIN; 903SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 904SELECT @@tx_isolation,@@global.tx_isolation; 905@@tx_isolation @@global.tx_isolation 906SERIALIZABLE REPEATABLE-READ 907insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); 908select id, code, name from t1 order by id; 909id code name 9101 1 Tim 9112 1 Monty 9123 2 David 913COMMIT; 914BEGIN; 915SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 916insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); 917select id, code, name from t1 order by id; 918id code name 9191 1 Tim 9202 1 Monty 9213 2 David 9224 2 Erik 9235 3 Sasha 924COMMIT; 925BEGIN; 926SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 927insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); 928select id, code, name from t1 order by id; 929id code name 9301 1 Tim 9312 1 Monty 9323 2 David 9334 2 Erik 9345 3 Sasha 9356 3 Jeremy 9367 4 Matt 937COMMIT; 938DROP TABLE t1; 939create table t1 (n int(10), d int(10)) engine=MyISAM; 940create table t2 (n int(10), d int(10)) engine=MyISAM; 941insert into t1 values(1,1),(1,2); 942insert into t2 values(1,10),(2,20); 943UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 944select * from t1; 945n d 9461 10 9471 10 948select * from t2; 949n d 9501 30 9512 20 952drop table t1,t2; 953create table t1 (a int, b int) engine=MyISAM; 954insert into t1 values(20,null); 955select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 956t2.b=t3.a; 957b ifnull(t2.b,"this is null") 958NULL this is null 959select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 960t2.b=t3.a order by 1; 961b ifnull(t2.b,"this is null") 962NULL this is null 963insert into t1 values(10,null); 964select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 965t2.b=t3.a order by 1; 966b ifnull(t2.b,"this is null") 967NULL this is null 968NULL this is null 969drop table t1; 970create table t1 (a varchar(10) not null) engine = MEMORY; 971create table t2 (b varchar(10) not null unique) engine=MyISAM; 972select t1.a from t1,t2 where t1.a=t2.b; 973a 974drop table t1,t2; 975create table t1 (a int not null, b int, primary key (a)) engine = MyISAM; 976create table t2 (a int not null, b int, primary key (a)) engine = MyISAM; 977insert into t1 values (10, 20); 978insert into t2 values (10, 20); 979update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; 980drop table t1,t2; 981CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM; 982INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 983UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; 984SELECT * from t1; 985a b 9861 1 987102 2 988103 3 9894 4 9905 5 9916 6 9927 7 9938 8 9949 9 995drop table t1; 996CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM; 997CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM; 998INSERT 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); 999INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 1000update t1,t2 set t1.a=t1.a+100; 1001select * from t1; 1002a b 1003101 1 1004102 2 1005103 3 1006104 4 1007105 5 1008106 6 1009107 7 1010108 8 1011109 9 1012110 10 1013111 11 1014112 12 1015update t1,t2 set t1.a=t1.a+100 where t1.a=101; 1016select * from t1; 1017a b 1018201 1 1019102 2 1020103 3 1021104 4 1022105 5 1023106 6 1024107 7 1025108 8 1026109 9 1027110 10 1028111 11 1029112 12 1030update t1,t2 set t1.b=t1.b+10 where t1.b=2; 1031select * from t1; 1032a b 1033201 1 1034102 12 1035103 3 1036104 4 1037105 5 1038106 6 1039107 7 1040108 8 1041109 9 1042110 10 1043111 11 1044112 12 1045update 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; 1046select * from t1; 1047a b 1048201 1 1049102 12 1050103 5 1051104 6 1052105 7 1053106 6 1054107 7 1055108 8 1056109 9 1057110 10 1058111 11 1059112 12 1060select * from t2; 1061a b 10621 1 10632 2 10643 13 10654 14 10665 15 10676 6 10687 7 10698 8 10709 9 1071drop table t1,t2; 1072CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY; 1073CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM; 1074SET AUTOCOMMIT=0; 1075INSERT INTO t1 ( B_ID ) VALUES ( 1 ); 1076INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); 1077ROLLBACK; 1078Warnings: 1079Warning 1196 Some non-transactional changed tables couldn't be rolled back 1080SELECT * FROM t1; 1081B_ID 10821 1083drop table t1,t2; 1084create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM; 1085insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); 1086select distinct parent,child from t1 order by parent; 1087parent child 10880 4 10891 2 10901 3 10912 1 1092drop table t1; 1093create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM; 1094create table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY; 1095insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); 1096insert into t2 (a) select b from t1; 1097insert into t1 (b) select b from t2; 1098insert into t2 (a) select b from t1; 1099insert into t1 (a) select b from t2; 1100insert into t2 (a) select b from t1; 1101insert into t1 (a) 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; 1114select count(*) from t1; 1115count(*) 111629267 1117explain select * from t1 where c between 1 and 2500; 1118id select_type table type possible_keys key key_len ref rows Extra 11191 SIMPLE t1 range c c 5 NULL # Using index condition 1120update t1 set c=a; 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 1124drop table t1,t2; 1125create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM; 1126insert into t1 (id) values (null),(null),(null),(null),(null); 1127update t1 set fk=69 where fk is null order by id limit 1; 1128SELECT * from t1; 1129id fk 11301 69 11312 NULL 11323 NULL 11334 NULL 11345 NULL 1135drop table t1; 1136create table t1 (a int not null, b int not null, key (a)) engine=MyISAM; 1137insert 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); 1138SET @tmp=0; 1139update t1 set b=(@tmp:=@tmp+1) order by a; 1140update t1 set b=99 where a=1 order by b asc limit 1; 1141update t1 set b=100 where a=1 order by b desc limit 2; 1142update t1 set a=a+10+b where a=1 order by b; 1143select * from t1 order by a,b; 1144a b 11452 4 11462 5 11472 6 11483 7 11493 8 11503 9 11513 10 11523 11 11533 12 115413 2 1155111 100 1156111 100 1157drop table t1; 1158create table t1 ( c char(8) not null ) engine=MyISAM; 1159insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 1160insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 1161alter table t1 add b char(8) not null; 1162alter table t1 add a char(8) not null; 1163alter table t1 add primary key (a,b,c); 1164update t1 set a=c, b=c; 1165create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM; 1166insert into t2 select * from t1; 1167delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 1168drop table t1,t2; 1169SET AUTOCOMMIT=1; 1170create table t1 (a integer auto_increment primary key) engine=MyISAM; 1171insert into t1 (a) values (NULL),(NULL); 1172truncate table t1; 1173insert into t1 (a) values (NULL),(NULL); 1174SELECT * from t1; 1175a 11761 11772 1178drop table t1; 1179CREATE TABLE t1 (col1 int(1))ENGINE=MyISAM; 1180CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx 1181(stamp))ENGINE=MyISAM; 1182insert into t1 values (1),(2),(3); 1183insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); 1184Warnings: 1185Warning 1265 Data truncated for column 'stamp' at row 3 1186SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < 1187'20020204120000' GROUP BY col1; 1188col1 11891 11902 11913 11924 1193drop table t1,t2; 1194CREATE TABLE t1 ( 1195`id` int(10) unsigned NOT NULL auto_increment, 1196`id_object` int(10) unsigned default '0', 1197`id_version` int(10) unsigned NOT NULL default '1', 1198`label` varchar(100) NOT NULL default '', 1199`description` text, 1200PRIMARY KEY (`id`), 1201KEY `id_object` (`id_object`), 1202KEY `id_version` (`id_version`) 1203) ENGINE=MyISAM; 1204INSERT 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); 1205CREATE TABLE t2 ( 1206`id` int(10) unsigned NOT NULL auto_increment, 1207`id_version` int(10) unsigned NOT NULL default '1', 1208PRIMARY KEY (`id`), 1209KEY `id_version` (`id_version`) 1210) ENGINE=MyISAM; 1211INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); 1212SELECT t2.id, t1.`label` FROM t2 INNER JOIN 1213(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 1214ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); 1215id label 12163382 Test 1217102 Le Pekin (Test) 12181794 Test de resto 12191822 Test 3 12203524 Societe Test 12213525 Fournisseur Test 1222drop table t1,t2; 1223create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM; 1224create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM; 1225create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=MEMORY; 1226create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY; 1227create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM; 1228create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM; 1229insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 1230insert t2 select * from t1; 1231insert t3 select * from t1; 1232insert t4 select * from t1; 1233insert t5 select * from t1; 1234insert t6 select * from t1; 1235checksum table t1, t2, t3, t4, t5, t6, t7 quick; 1236Table Checksum 1237test.t1 2948697075 1238test.t2 NULL 1239test.t3 NULL 1240test.t4 NULL 1241test.t5 2948697075 1242test.t6 NULL 1243test.t7 NULL 1244Warnings: 1245Error 1146 Table 'test.t7' doesn't exist 1246checksum table t1, t2, t3, t4, t5, t6, t7; 1247Table Checksum 1248test.t1 2948697075 1249test.t2 2948697075 1250test.t3 2948697075 1251test.t4 2948697075 1252test.t5 2948697075 1253test.t6 2948697075 1254test.t7 NULL 1255Warnings: 1256Error 1146 Table 'test.t7' doesn't exist 1257checksum table t1, t2, t3, t4, t5, t6, t7 extended; 1258Table Checksum 1259test.t1 2948697075 1260test.t2 2948697075 1261test.t3 2948697075 1262test.t4 2948697075 1263test.t5 2948697075 1264test.t6 2948697075 1265test.t7 NULL 1266Warnings: 1267Error 1146 Table 'test.t7' doesn't exist 1268drop table t1,t2,t3, t4, t5, t6; 1269create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=MyISAM; 1270insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); 1271select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1; 1272trim(name2) 1273fff 1274sss 1275ttt 1276first 1277second 1278third 12791 12802 12813 1282drop table t1; 1283create table t1 (a int) engine=MyISAM; 1284create table t2 like t1; 1285show create table t2; 1286Table Create Table 1287t2 CREATE TABLE `t2` ( 1288 `a` int(11) DEFAULT NULL 1289) ENGINE=MyISAM DEFAULT CHARSET=latin1 1290drop table t1,t2; 1291flush status; 1292show status like "binlog_cache_use"; 1293Variable_name Value 1294Binlog_cache_use 0 1295show status like "binlog_cache_disk_use"; 1296Variable_name Value 1297Binlog_cache_disk_use 0 1298create table t1 (a int) engine=MyISAM; 1299show status like "binlog_cache_use"; 1300Variable_name Value 1301Binlog_cache_use 0 1302show status like "binlog_cache_disk_use"; 1303Variable_name Value 1304Binlog_cache_disk_use 0 1305begin; 1306delete from t1; 1307commit; 1308show status like "binlog_cache_use"; 1309Variable_name Value 1310Binlog_cache_use 0 1311show status like "binlog_cache_disk_use"; 1312Variable_name Value 1313Binlog_cache_disk_use 0 1314drop table t1; 1315create table t1 (c char(10), index (c,c)) engine=MyISAM; 1316ERROR 42S21: Duplicate column name 'c' 1317create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM; 1318ERROR 42S21: Duplicate column name 'c1' 1319create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM; 1320ERROR 42S21: Duplicate column name 'c1' 1321create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM; 1322ERROR 42S21: Duplicate column name 'c1' 1323create table t1 (c1 char(10), c2 char(10)) engine=MyISAM; 1324alter table t1 add key (c1,c1); 1325ERROR 42S21: Duplicate column name 'c1' 1326alter table t1 add key (c2,c1,c1); 1327ERROR 42S21: Duplicate column name 'c1' 1328alter table t1 add key (c1,c2,c1); 1329ERROR 42S21: Duplicate column name 'c1' 1330alter table t1 add key (c1,c1,c2); 1331ERROR 42S21: Duplicate column name 'c1' 1332drop table t1; 1333create table t1(a int(1) , b int(1)) engine=MyISAM; 1334insert into t1 values ('1111', '3333'); 1335select distinct concat(a, b) from t1; 1336concat(a, b) 133711113333 1338drop table t1; 1339create temporary table t1 (a int) engine=MyISAM; 1340insert into t1 values (4711); 1341truncate t1; 1342insert into t1 values (42); 1343select * from t1; 1344a 134542 1346drop table t1; 1347create table t1 (a int) engine=MyISAM; 1348insert into t1 values (4711); 1349truncate t1; 1350insert into t1 values (42); 1351select * from t1; 1352a 135342 1354drop table t1; 1355create 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; 1356insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3); 1357select * from t1 order by a,b,c,d; 1358a b c d e 13591 1 a 1 1 13602 2 b 2 2 13613 3 ab 3 3 1362explain select * from t1 order by a,b,c,d; 1363id select_type table type possible_keys key key_len ref rows Extra 13641 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1365drop table t1; 1366create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM; 1367insert into t1 values ('8', '6'), ('4', '7'); 1368select min(a) from t1; 1369min(a) 13704 1371select min(b) from t1 where a='8'; 1372min(b) 13736 1374drop table t1; 1375create table t1 (x bigint unsigned not null primary key) engine=MyISAM; 1376insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); 1377select * from t1; 1378x 137918446744073709551600 138018446744073709551601 1381select count(*) from t1 where x>0; 1382count(*) 13832 1384select count(*) from t1 where x=0; 1385count(*) 13860 1387select count(*) from t1 where x<0; 1388count(*) 13890 1390select count(*) from t1 where x < -16; 1391count(*) 13920 1393select count(*) from t1 where x = -16; 1394count(*) 13950 1396explain select count(*) from t1 where x > -16; 1397id select_type table type possible_keys key key_len ref rows Extra 13981 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index 1399select count(*) from t1 where x > -16; 1400count(*) 14012 1402select * from t1 where x > -16; 1403x 140418446744073709551600 140518446744073709551601 1406select count(*) from t1 where x = 18446744073709551601; 1407count(*) 14081 1409drop table t1; 1410set default_storage_engine=MyISAM; 1411drop table if exists t1,t2,t3; 1412--- Testing varchar --- 1413--- Testing varchar --- 1414create table t1 (v varchar(10), c char(10), t text); 1415insert into t1 values('+ ', '+ ', '+ '); 1416set @a=repeat(' ',20); 1417insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 1418Warnings: 1419Note 1265 Data truncated for column 'v' at row 1 1420select concat('*',v,'*',c,'*',t,'*') from t1; 1421concat('*',v,'*',c,'*',t,'*') 1422*+ *+*+ * 1423*+ *+*+ * 1424show create table t1; 1425Table Create Table 1426t1 CREATE TABLE `t1` ( 1427 `v` varchar(10) DEFAULT NULL, 1428 `c` char(10) DEFAULT NULL, 1429 `t` text 1430) ENGINE=MyISAM DEFAULT CHARSET=latin1 1431create table t2 like t1; 1432show create table t2; 1433Table Create Table 1434t2 CREATE TABLE `t2` ( 1435 `v` varchar(10) DEFAULT NULL, 1436 `c` char(10) DEFAULT NULL, 1437 `t` text 1438) ENGINE=MyISAM DEFAULT CHARSET=latin1 1439create table t3 select * from t1; 1440show create table t3; 1441Table Create Table 1442t3 CREATE TABLE `t3` ( 1443 `v` varchar(10) DEFAULT NULL, 1444 `c` char(10) DEFAULT NULL, 1445 `t` text 1446) ENGINE=MyISAM DEFAULT CHARSET=latin1 1447alter table t1 modify c varchar(10); 1448show create table t1; 1449Table Create Table 1450t1 CREATE TABLE `t1` ( 1451 `v` varchar(10) DEFAULT NULL, 1452 `c` varchar(10) DEFAULT NULL, 1453 `t` text 1454) ENGINE=MyISAM DEFAULT CHARSET=latin1 1455alter table t1 modify v char(10); 1456show create table t1; 1457Table Create Table 1458t1 CREATE TABLE `t1` ( 1459 `v` char(10) DEFAULT NULL, 1460 `c` varchar(10) DEFAULT NULL, 1461 `t` text 1462) ENGINE=MyISAM DEFAULT CHARSET=latin1 1463alter table t1 modify t varchar(10); 1464Warnings: 1465Note 1265 Data truncated for column 't' at row 2 1466show create table t1; 1467Table Create Table 1468t1 CREATE TABLE `t1` ( 1469 `v` char(10) DEFAULT NULL, 1470 `c` varchar(10) DEFAULT NULL, 1471 `t` varchar(10) DEFAULT NULL 1472) ENGINE=MyISAM DEFAULT CHARSET=latin1 1473select concat('*',v,'*',c,'*',t,'*') from t1; 1474concat('*',v,'*',c,'*',t,'*') 1475*+*+*+ * 1476*+*+*+ * 1477drop table t1,t2,t3; 1478create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))) stats_persistent=0; 1479show create table t1; 1480Table Create Table 1481t1 CREATE TABLE `t1` ( 1482 `v` varchar(10) DEFAULT NULL, 1483 `c` char(10) DEFAULT NULL, 1484 `t` text, 1485 KEY `v` (`v`), 1486 KEY `c` (`c`), 1487 KEY `t` (`t`(10)) 1488) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1489select count(*) from t1; 1490count(*) 1491270 1492insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 1493select count(*) from t1 where v='a'; 1494count(*) 149510 1496select count(*) from t1 where c='a'; 1497count(*) 149810 1499select count(*) from t1 where t='a'; 1500count(*) 150110 1502select count(*) from t1 where v='a '; 1503count(*) 150410 1505select count(*) from t1 where c='a '; 1506count(*) 150710 1508select count(*) from t1 where t='a '; 1509count(*) 151010 1511select count(*) from t1 where v between 'a' and 'a '; 1512count(*) 151310 1514select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1515count(*) 151610 1517select count(*) from t1 where v like 'a%'; 1518count(*) 151911 1520select count(*) from t1 where c like 'a%'; 1521count(*) 152211 1523select count(*) from t1 where t like 'a%'; 1524count(*) 152511 1526select count(*) from t1 where v like 'a %'; 1527count(*) 15289 1529explain select count(*) from t1 where v='a '; 1530id select_type table type possible_keys key key_len ref rows Extra 15311 SIMPLE t1 ref v v 13 const # Using where; Using index 1532explain select count(*) from t1 where c='a '; 1533id select_type table type possible_keys key key_len ref rows Extra 15341 SIMPLE t1 ref c c 11 const # Using where; Using index 1535explain select count(*) from t1 where t='a '; 1536id select_type table type possible_keys key key_len ref rows Extra 15371 SIMPLE t1 ref t t 13 const # Using where 1538explain select count(*) from t1 where v like 'a%'; 1539id select_type table type possible_keys key key_len ref rows Extra 15401 SIMPLE t1 range v v 13 NULL # Using where; Using index 1541explain select count(*) from t1 where v between 'a' and 'a '; 1542id select_type table type possible_keys key key_len ref rows Extra 15431 SIMPLE t1 ref v v 13 const # Using where; Using index 1544explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1545id select_type table type possible_keys key key_len ref rows Extra 15461 SIMPLE t1 ref v v 13 const # Using where; Using index 1547alter table t1 add unique(v); 1548ERROR 23000: Duplicate entry '{ ' for key 'v_2' 1549alter table t1 add key(v); 1550Warnings: 1551Note 1831 Duplicate index 'v_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 1552select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 1553qq 1554*a*a*a* 1555*a *a*a * 1556*a *a*a * 1557*a *a*a * 1558*a *a*a * 1559*a *a*a * 1560*a *a*a * 1561*a *a*a * 1562*a *a*a * 1563*a *a*a * 1564explain select * from t1 where v='a'; 1565id select_type table type possible_keys key key_len ref rows Extra 15661 SIMPLE t1 ref v,v_2 # 13 const # Using index condition 1567select v,count(*) from t1 group by v limit 10; 1568v count(*) 1569a 1 1570a 10 1571b 10 1572c 10 1573d 10 1574e 10 1575f 10 1576g 10 1577h 10 1578i 10 1579select v,count(t) from t1 group by v limit 10; 1580v count(t) 1581a 1 1582a 10 1583b 10 1584c 10 1585d 10 1586e 10 1587f 10 1588g 10 1589h 10 1590i 10 1591select v,count(c) from t1 group by v limit 10; 1592v count(c) 1593a 1 1594a 10 1595b 10 1596c 10 1597d 10 1598e 10 1599f 10 1600g 10 1601h 10 1602i 10 1603select sql_big_result v,count(t) from t1 group by v limit 10; 1604v count(t) 1605a 1 1606a 10 1607b 10 1608c 10 1609d 10 1610e 10 1611f 10 1612g 10 1613h 10 1614i 10 1615select sql_big_result v,count(c) from t1 group by v limit 10; 1616v count(c) 1617a 1 1618a 10 1619b 10 1620c 10 1621d 10 1622e 10 1623f 10 1624g 10 1625h 10 1626i 10 1627select c,count(*) from t1 group by c limit 10; 1628c count(*) 1629a 1 1630a 10 1631b 10 1632c 10 1633d 10 1634e 10 1635f 10 1636g 10 1637h 10 1638i 10 1639select c,count(t) from t1 group by c limit 10; 1640c count(t) 1641a 1 1642a 10 1643b 10 1644c 10 1645d 10 1646e 10 1647f 10 1648g 10 1649h 10 1650i 10 1651select sql_big_result c,count(t) from t1 group by c limit 10; 1652c count(t) 1653a 1 1654a 10 1655b 10 1656c 10 1657d 10 1658e 10 1659f 10 1660g 10 1661h 10 1662i 10 1663select t,count(*) from t1 group by t limit 10; 1664t count(*) 1665a 1 1666a 10 1667b 10 1668c 10 1669d 10 1670e 10 1671f 10 1672g 10 1673h 10 1674i 10 1675select t,count(t) from t1 group by t limit 10; 1676t count(t) 1677a 1 1678a 10 1679b 10 1680c 10 1681d 10 1682e 10 1683f 10 1684g 10 1685h 10 1686i 10 1687select sql_big_result t,count(t) from t1 group by t limit 10; 1688t count(t) 1689a 1 1690a 10 1691b 10 1692c 10 1693d 10 1694e 10 1695f 10 1696g 10 1697h 10 1698i 10 1699alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 1700show create table t1; 1701Table Create Table 1702t1 CREATE TABLE `t1` ( 1703 `v` varchar(300) DEFAULT NULL, 1704 `c` char(10) DEFAULT NULL, 1705 `t` text, 1706 KEY `c` (`c`), 1707 KEY `t` (`t`(10)), 1708 KEY `v` (`v`) 1709) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1710select count(*) from t1 where v='a'; 1711count(*) 171210 1713select count(*) from t1 where v='a '; 1714count(*) 171510 1716select count(*) from t1 where v between 'a' and 'a '; 1717count(*) 171810 1719select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1720count(*) 172110 1722select count(*) from t1 where v like 'a%'; 1723count(*) 172411 1725select count(*) from t1 where v like 'a %'; 1726count(*) 17279 1728explain select count(*) from t1 where v='a '; 1729id select_type table type possible_keys key key_len ref rows Extra 17301 SIMPLE t1 ref v v 303 const # Using where; Using index 1731explain select count(*) from t1 where v like 'a%'; 1732id select_type table type possible_keys key key_len ref rows Extra 17331 SIMPLE t1 range v v 303 NULL # Using where; Using index 1734explain select count(*) from t1 where v between 'a' and 'a '; 1735id select_type table type possible_keys key key_len ref rows Extra 17361 SIMPLE t1 ref v v 303 const # Using where; Using index 1737explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1738id select_type table type possible_keys key key_len ref rows Extra 17391 SIMPLE t1 ref v v 303 const # Using where; Using index 1740explain select * from t1 where v='a'; 1741id select_type table type possible_keys key key_len ref rows Extra 17421 SIMPLE t1 ref v v 303 const # Using index condition 1743select v,count(*) from t1 group by v limit 10; 1744v count(*) 1745a 1 1746a 10 1747b 10 1748c 10 1749d 10 1750e 10 1751f 10 1752g 10 1753h 10 1754i 10 1755select v,count(t) from t1 group by v limit 10; 1756v count(t) 1757a 1 1758a 10 1759b 10 1760c 10 1761d 10 1762e 10 1763f 10 1764g 10 1765h 10 1766i 10 1767select sql_big_result v,count(t) from t1 group by v limit 10; 1768v count(t) 1769a 1 1770a 10 1771b 10 1772c 10 1773d 10 1774e 10 1775f 10 1776g 10 1777h 10 1778i 10 1779alter table t1 drop key v, add key v (v(30)); 1780show create table t1; 1781Table Create Table 1782t1 CREATE TABLE `t1` ( 1783 `v` varchar(300) DEFAULT NULL, 1784 `c` char(10) DEFAULT NULL, 1785 `t` text, 1786 KEY `c` (`c`), 1787 KEY `t` (`t`(10)), 1788 KEY `v` (`v`(30)) 1789) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1790select count(*) from t1 where v='a'; 1791count(*) 179210 1793select count(*) from t1 where v='a '; 1794count(*) 179510 1796select count(*) from t1 where v between 'a' and 'a '; 1797count(*) 179810 1799select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1800count(*) 180110 1802select count(*) from t1 where v like 'a%'; 1803count(*) 180411 1805select count(*) from t1 where v like 'a %'; 1806count(*) 18079 1808explain select count(*) from t1 where v='a '; 1809id select_type table type possible_keys key key_len ref rows Extra 18101 SIMPLE t1 ref v v 33 const # Using where 1811explain select count(*) from t1 where v like 'a%'; 1812id select_type table type possible_keys key key_len ref rows Extra 18131 SIMPLE t1 range v v 33 NULL # Using where 1814explain select count(*) from t1 where v between 'a' and 'a '; 1815id select_type table type possible_keys key key_len ref rows Extra 18161 SIMPLE t1 ref v v 33 const # Using where 1817explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1818id select_type table type possible_keys key key_len ref rows Extra 18191 SIMPLE t1 ref v v 33 const # Using where 1820explain select * from t1 where v='a'; 1821id select_type table type possible_keys key key_len ref rows Extra 18221 SIMPLE t1 ref v v 33 const # Using where 1823select v,count(*) from t1 group by v limit 10; 1824v count(*) 1825a 1 1826a 10 1827b 10 1828c 10 1829d 10 1830e 10 1831f 10 1832g 10 1833h 10 1834i 10 1835select v,count(t) from t1 group by v limit 10; 1836v count(t) 1837a 1 1838a 10 1839b 10 1840c 10 1841d 10 1842e 10 1843f 10 1844g 10 1845h 10 1846i 10 1847select sql_big_result v,count(t) from t1 group by v limit 10; 1848v count(t) 1849a 1 1850a 10 1851b 10 1852c 10 1853d 10 1854e 10 1855f 10 1856g 10 1857h 10 1858i 10 1859alter table t1 modify v varchar(600), drop key v, add key v (v); 1860show create table t1; 1861Table Create Table 1862t1 CREATE TABLE `t1` ( 1863 `v` varchar(600) DEFAULT NULL, 1864 `c` char(10) DEFAULT NULL, 1865 `t` text, 1866 KEY `c` (`c`), 1867 KEY `t` (`t`(10)), 1868 KEY `v` (`v`) 1869) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1870select v,count(*) from t1 group by v limit 10; 1871v count(*) 1872a 1 1873a 10 1874b 10 1875c 10 1876d 10 1877e 10 1878f 10 1879g 10 1880h 10 1881i 10 1882select v,count(t) from t1 group by v limit 10; 1883v count(t) 1884a 1 1885a 10 1886b 10 1887c 10 1888d 10 1889e 10 1890f 10 1891g 10 1892h 10 1893i 10 1894select sql_big_result v,count(t) from t1 group by v limit 10; 1895v count(t) 1896a 1 1897a 10 1898b 10 1899c 10 1900d 10 1901e 10 1902f 10 1903g 10 1904h 10 1905i 10 1906drop table t1; 1907create table t1 (a char(10), unique (a)); 1908insert into t1 values ('a '); 1909insert into t1 values ('a '); 1910ERROR 23000: Duplicate entry 'a' for key 'a' 1911alter table t1 modify a varchar(10); 1912insert into t1 values ('a '),('a '),('a '),('a '); 1913ERROR 23000: Duplicate entry 'a ' for key 'a' 1914insert into t1 values ('a '); 1915ERROR 23000: Duplicate entry 'a ' for key 'a' 1916insert into t1 values ('a '); 1917ERROR 23000: Duplicate entry 'a ' for key 'a' 1918insert into t1 values ('a '); 1919ERROR 23000: Duplicate entry 'a ' for key 'a' 1920update t1 set a='a ' where a like 'a%'; 1921select concat(a,'.') from t1; 1922concat(a,'.') 1923a . 1924update t1 set a='abc ' where a like 'a '; 1925select concat(a,'.') from t1; 1926concat(a,'.') 1927a . 1928update t1 set a='a ' where a like 'a %'; 1929select concat(a,'.') from t1; 1930concat(a,'.') 1931a . 1932update t1 set a='a ' where a like 'a '; 1933select concat(a,'.') from t1; 1934concat(a,'.') 1935a . 1936drop table t1; 1937create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 1938show create table t1; 1939Table Create Table 1940t1 CREATE TABLE `t1` ( 1941 `v` varchar(10) DEFAULT NULL, 1942 `c` char(10) DEFAULT NULL, 1943 `t` text, 1944 KEY `v` (`v`(5)), 1945 KEY `c` (`c`(5)), 1946 KEY `t` (`t`(5)) 1947) ENGINE=MyISAM DEFAULT CHARSET=latin1 1948drop table t1; 1949create table t1 (v char(10) character set utf8); 1950show create table t1; 1951Table Create Table 1952t1 CREATE TABLE `t1` ( 1953 `v` char(10) CHARACTER SET utf8 DEFAULT NULL 1954) ENGINE=MyISAM DEFAULT CHARSET=latin1 1955drop table t1; 1956create table t1 (v varchar(10), c char(10)) row_format=fixed; 1957show create table t1; 1958Table Create Table 1959t1 CREATE TABLE `t1` ( 1960 `v` varchar(10) DEFAULT NULL, 1961 `c` char(10) DEFAULT NULL 1962) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED 1963insert into t1 values('a','a'),('a ','a '); 1964select concat('*',v,'*',c,'*') from t1; 1965concat('*',v,'*',c,'*') 1966*a*a* 1967*a *a* 1968drop table t1; 1969create table t1 (v varchar(65530), key(v(10))); 1970insert into t1 values(repeat('a',65530)); 1971select length(v) from t1 where v=repeat('a',65530); 1972length(v) 197365530 1974drop table t1; 1975create table t1(a int, b varchar(12), key ba(b, a)); 1976insert into t1 values (1, 'A'), (20, NULL); 1977explain select * from t1 where a=20 and b is null; 1978id select_type table type possible_keys key key_len ref rows Extra 19791 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index 1980select * from t1 where a=20 and b is null; 1981a b 198220 NULL 1983drop table t1; 1984create table t1 (v varchar(65530), key(v)); 1985Warnings: 1986Warning 1071 Specified key was too long; max key length is 1000 bytes 1987drop table t1; 1988create table t1 (v varchar(65536)); 1989Warnings: 1990Note 1246 Converting column 'v' from VARCHAR to TEXT 1991show create table t1; 1992Table Create Table 1993t1 CREATE TABLE `t1` ( 1994 `v` mediumtext 1995) ENGINE=MyISAM DEFAULT CHARSET=latin1 1996drop table t1; 1997create table t1 (v varchar(65530) character set utf8); 1998Warnings: 1999Note 1246 Converting column 'v' from VARCHAR to TEXT 2000show create table t1; 2001Table Create Table 2002t1 CREATE TABLE `t1` ( 2003 `v` mediumtext CHARACTER SET utf8 2004) ENGINE=MyISAM DEFAULT CHARSET=latin1 2005drop table t1; 2006set default_storage_engine=MEMORY; 2007create table t1 (v varchar(16384)) engine=MyISAM; 2008drop table t1; 2009create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM; 2010insert into t1 values ('8', '6'), ('4', '7'); 2011select min(a) from t1; 2012min(a) 20134 2014select min(b) from t1 where a='8'; 2015min(b) 20166 2017drop table t1; 2018CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM; 2019insert into t1 (b) values (1); 2020replace into t1 (b) values (2), (1), (3); 2021select * from t1; 2022a b 20233 1 20242 2 20254 3 2026truncate table t1; 2027insert into t1 (b) values (1); 2028replace into t1 (b) values (2); 2029replace into t1 (b) values (1); 2030replace into t1 (b) values (3); 2031select * from t1; 2032a b 20333 1 20342 2 20354 3 2036drop table t1; 2037create table t1 (rowid int not null auto_increment, val int not null,primary 2038key (rowid), unique(val)) engine=MyISAM; 2039replace into t1 (val) values ('1'),('2'); 2040replace into t1 (val) values ('1'),('2'); 2041insert into t1 (val) values ('1'),('2'); 2042ERROR 23000: Duplicate entry '1' for key 'val' 2043select * from t1; 2044rowid val 20453 1 20464 2 2047drop table t1; 2048CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM; 2049INSERT INTO t1 (GRADE) VALUES (151),(252),(343); 2050SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300; 2051GRADE 2052252 2053SELECT GRADE FROM t1 WHERE GRADE= 151; 2054GRADE 2055151 2056DROP TABLE t1; 2057create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM; 2058create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM; 2059insert into t2 values ('aa','cc'); 2060insert into t1 values ('aa','bb'),('aa','cc'); 2061delete t1 from t1,t2 where f1=f3 and f4='cc'; 2062select * from t1; 2063f1 f2 2064drop table t1,t2; 2065create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2066insert into t1(a) values (1),(2),(3); 2067commit; 2068set autocommit = 0; 2069update t1 set b = 5 where a = 2; 2070commit; 2071create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end | 2072set autocommit = 0; 2073insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100), 2074(11),(21),(31),(41),(51),(61),(71),(81),(91),(101), 2075(12),(22),(32),(42),(52),(62),(72),(82),(92),(102), 2076(13),(23),(33),(43),(53),(63),(73),(83),(93),(103), 2077(14),(24),(34),(44),(54),(64),(74),(84),(94),(104); 2078commit; 2079commit; 2080drop trigger t1t; 2081drop table t1; 2082create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2083create table t2(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2084create table t3(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2085create table t4(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2086create table t5(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2087insert into t1(a) values (1),(2),(3); 2088insert into t2(a) values (1),(2),(3); 2089insert into t3(a) values (1),(2),(3); 2090insert into t4(a) values (1),(2),(3); 2091insert into t3(a) values (5),(7),(8); 2092insert into t4(a) values (5),(7),(8); 2093insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12); 2094create trigger t1t before insert on t1 for each row begin 2095INSERT INTO t2 SET a = NEW.a; 2096end | 2097create trigger t2t before insert on t2 for each row begin 2098DELETE FROM t3 WHERE a = NEW.a; 2099end | 2100create trigger t3t before delete on t3 for each row begin 2101UPDATE t4 SET b = b + 1 WHERE a = OLD.a; 2102end | 2103create trigger t4t before update on t4 for each row begin 2104UPDATE t5 SET b = b + 1 where a = NEW.a; 2105end | 2106commit; 2107set autocommit = 0; 2108update t1 set b = b + 5 where a = 1; 2109update t2 set b = b + 5 where a = 1; 2110update t3 set b = b + 5 where a = 1; 2111update t4 set b = b + 5 where a = 1; 2112insert into t5(a) values(20); 2113commit; 2114set autocommit = 0; 2115insert into t1(a) values(7); 2116insert into t2(a) values(8); 2117delete from t2 where a = 3; 2118update t4 set b = b + 1 where a = 3; 2119commit; 2120drop trigger t1t; 2121drop trigger t2t; 2122drop trigger t3t; 2123drop trigger t4t; 2124drop table t1, t2, t3, t4, t5; 2125create table t1(a date) engine=MyISAM; 2126create table t2(a date, key(a)) engine=MyISAM; 2127insert into t1 values('2005-10-01'); 2128insert into t2 values('2005-10-01'); 2129select * from t1, t2 2130where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; 2131a a 21322005-10-01 2005-10-01 2133drop table t1, t2; 2134create table t1 (id int not null, f_id int not null, f int not null, 2135primary key(f_id, id)) engine=MyISAM; 2136create table t2 (id int not null,s_id int not null,s varchar(200), 2137primary key(id)) engine=MyISAM; 2138INSERT INTO t1 VALUES (8, 1, 3); 2139INSERT INTO t1 VALUES (1, 2, 1); 2140INSERT INTO t2 VALUES (1, 0, ''); 2141INSERT INTO t2 VALUES (8, 1, ''); 2142commit; 2143DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) 2144WHERE mm.id IS NULL; 2145select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) 2146where mm.id is null lock in share mode; 2147id f_id f 2148drop table t1,t2; 2149create table t1(a int not null, b int, primary key(a)) engine=MyISAM; 2150insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 2151commit; 2152set autocommit = 0; 2153SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2154update t1 set b = 5 where b = 1; 2155set autocommit = 0; 2156SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2157select * from t1 where a = 7 and b = 3 for update; 2158a b 21597 3 2160commit; 2161commit; 2162drop table t1; 2163CREATE TABLE t1 ( a int ) ENGINE=MyISAM; 2164BEGIN; 2165INSERT INTO t1 VALUES (1); 2166OPTIMIZE TABLE t1; 2167Table Op Msg_type Msg_text 2168test.t1 optimize status OK 2169DROP TABLE t1; 2170