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; 16Warnings: 17Warning 1062 Duplicate entry '8' for key 'PRIMARY' 18select id, code, name from t1 order by id; 19id code name 202 1 Monty 213 2 David 224 2 Erik 235 3 Sasha 246 3 Jeremy 257 4 Matt 268 1 Sinisa 27update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; 28Warnings: 29Warning 1062 Duplicate entry 'Ralph' for key 'name' 30select id, code, name from t1 order by id; 31id code name 323 2 David 334 2 Erik 345 3 Sasha 356 3 Jeremy 367 4 Matt 378 1 Sinisa 3812 1 Ralph 39drop table t1; 40CREATE TABLE t1 ( 41id int(11) NOT NULL auto_increment, 42parent_id int(11) DEFAULT '0' NOT NULL, 43level tinyint(4) DEFAULT '0' NOT NULL, 44PRIMARY KEY (id), 45KEY parent_id (parent_id), 46KEY level (level) 47) engine=MyISAM; 48INSERT 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); 49update t1 set parent_id=parent_id+100; 50select * from t1 where parent_id=102; 51id parent_id level 528 102 2 539 102 2 5415 102 2 55update t1 set id=id+1000; 56update t1 set id=1024 where id=1009; 57Got one of the listed errors 58select * from t1; 59id parent_id level 601001 100 0 611003 101 1 621004 101 1 631008 102 2 641009 102 2 651017 103 2 661022 104 2 671024 104 2 681028 105 2 691029 105 2 701030 105 2 711031 106 2 721032 106 2 731033 106 2 741203 107 2 751202 107 2 761020 103 2 771157 100 0 781193 105 2 791040 107 2 801002 101 1 811015 102 2 821006 101 1 831034 106 2 841035 106 2 851016 103 2 861007 101 1 871036 107 2 881018 103 2 891026 105 2 901027 105 2 911183 104 2 921038 107 2 931025 105 2 941037 107 2 951021 104 2 961019 103 2 971005 101 1 981179 105 2 99update ignore t1 set id=id+1; 100Warnings: 101Warning 1062 Duplicate entry '1002' for key 'PRIMARY' 102Warning 1062 Duplicate entry '1004' for key 'PRIMARY' 103Warning 1062 Duplicate entry '1005' for key 'PRIMARY' 104Warning 1062 Duplicate entry '1009' for key 'PRIMARY' 105Warning 1062 Duplicate entry '1018' for key 'PRIMARY' 106Warning 1062 Duplicate entry '1025' for key 'PRIMARY' 107Warning 1062 Duplicate entry '1029' for key 'PRIMARY' 108Warning 1062 Duplicate entry '1030' for key 'PRIMARY' 109Warning 1062 Duplicate entry '1031' for key 'PRIMARY' 110Warning 1062 Duplicate entry '1032' for key 'PRIMARY' 111Warning 1062 Duplicate entry '1033' for key 'PRIMARY' 112Warning 1062 Duplicate entry '1034' for key 'PRIMARY' 113Warning 1062 Duplicate entry '1021' for key 'PRIMARY' 114Warning 1062 Duplicate entry '1003' for key 'PRIMARY' 115Warning 1062 Duplicate entry '1016' for key 'PRIMARY' 116Warning 1062 Duplicate entry '1007' for key 'PRIMARY' 117Warning 1062 Duplicate entry '1035' for key 'PRIMARY' 118Warning 1062 Duplicate entry '1036' for key 'PRIMARY' 119Warning 1062 Duplicate entry '1017' for key 'PRIMARY' 120Warning 1062 Duplicate entry '1008' for key 'PRIMARY' 121Warning 1062 Duplicate entry '1037' for key 'PRIMARY' 122Warning 1062 Duplicate entry '1019' for key 'PRIMARY' 123Warning 1062 Duplicate entry '1027' for key 'PRIMARY' 124Warning 1062 Duplicate entry '1028' for key 'PRIMARY' 125Warning 1062 Duplicate entry '1026' for key 'PRIMARY' 126Warning 1062 Duplicate entry '1020' for key 'PRIMARY' 127Warning 1062 Duplicate entry '1006' for key 'PRIMARY' 128select * from t1; 129id parent_id level 1301001 100 0 1311003 101 1 1321004 101 1 1331008 102 2 1341010 102 2 1351017 103 2 1361023 104 2 1371024 104 2 1381028 105 2 1391029 105 2 1401030 105 2 1411031 106 2 1421032 106 2 1431033 106 2 1441204 107 2 1451203 107 2 1461020 103 2 1471158 100 0 1481194 105 2 1491041 107 2 1501002 101 1 1511015 102 2 1521006 101 1 1531034 106 2 1541035 106 2 1551016 103 2 1561007 101 1 1571036 107 2 1581018 103 2 1591026 105 2 1601027 105 2 1611184 104 2 1621039 107 2 1631025 105 2 1641038 107 2 1651022 104 2 1661019 103 2 1671005 101 1 1681180 105 2 169update ignore t1 set id=1023 where id=1010; 170Warnings: 171Warning 1062 Duplicate entry '1023' for key 'PRIMARY' 172select * from t1 where parent_id=102; 173id parent_id level 1741008 102 2 1751010 102 2 1761015 102 2 177explain select level from t1 where level=1; 178id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1791 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index 180Warnings: 181Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level` from `test`.`t1` where (`test`.`t1`.`level` = 1) 182explain select level,id from t1 where level=1; 183id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1841 SIMPLE t1 NULL ref level level 1 const # 100.00 NULL 185Warnings: 186Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`level` = 1) 187explain select level,id,parent_id from t1 where level=1; 188id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1891 SIMPLE t1 NULL ref level level 1 const # 100.00 NULL 190Warnings: 191Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`parent_id` AS `parent_id` from `test`.`t1` where (`test`.`t1`.`level` = 1) 192select level,id from t1 where level=1; 193level id 1941 1003 1951 1004 1961 1002 1971 1006 1981 1007 1991 1005 200select level,id,parent_id from t1 where level=1; 201level id parent_id 2021 1003 101 2031 1004 101 2041 1002 101 2051 1006 101 2061 1007 101 2071 1005 101 208optimize table t1; 209Table Op Msg_type Msg_text 210test.t1 optimize status OK 211show keys from t1; 212Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 213t1 0 PRIMARY 1 id A # NULL NULL BTREE 214t1 1 parent_id 1 parent_id A # NULL NULL BTREE 215t1 1 level 1 level A # NULL NULL BTREE 216drop table t1; 217CREATE TABLE t1 ( 218gesuchnr int(11) DEFAULT '0' NOT NULL, 219benutzer_id int(11) DEFAULT '0' NOT NULL, 220PRIMARY KEY (gesuchnr,benutzer_id) 221) engine=MyISAM; 222replace into t1 (gesuchnr,benutzer_id) values (2,1); 223replace into t1 (gesuchnr,benutzer_id) values (1,1); 224replace into t1 (gesuchnr,benutzer_id) values (1,1); 225select * from t1; 226gesuchnr benutzer_id 2271 1 2282 1 229drop table t1; 230create table t1 (a int) engine=MyISAM; 231insert into t1 values (1), (2); 232optimize table t1; 233Table Op Msg_type Msg_text 234test.t1 optimize status OK 235delete from t1 where a = 1; 236select * from t1; 237a 2382 239check table t1; 240Table Op Msg_type Msg_text 241test.t1 check status OK 242drop table t1; 243create table t1 (a int,b varchar(20)) engine=MyISAM; 244insert into t1 values (1,""), (2,"testing"); 245delete from t1 where a = 1; 246select * from t1; 247a b 2482 testing 249create index skr on t1 (a); 250insert into t1 values (3,""), (4,"testing"); 251analyze table t1; 252Table Op Msg_type Msg_text 253test.t1 analyze status OK 254show keys from t1; 255Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 256t1 1 skr 1 a A # NULL NULL YES BTREE 257drop table t1; 258create table t1 (a int,b varchar(20),key(a)) engine=MyISAM; 259insert into t1 values (1,""), (2,"testing"); 260select * from t1 where a = 1; 261a b 2621 263drop table t1; 264CREATE TABLE t1 ( 265user_id int(10) DEFAULT '0' NOT NULL, 266name varchar(100), 267phone varchar(100), 268ref_email varchar(100) DEFAULT '' NOT NULL, 269detail varchar(200), 270PRIMARY KEY (user_id,ref_email) 271)engine=MyISAM; 272INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); 273select * from t1 where user_id=10292; 274user_id name phone ref_email detail 27510292 sanjeev 29153373 sansh777@hotmail.com xxx 27610292 shirish 2333604 shirish@yahoo.com ddsds 27710292 sonali 323232 sonali@bolly.com filmstar 278INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); 279select * from t1 where user_id=10292; 280user_id name phone ref_email detail 28110292 sanjeev 29153373 sansh777@hotmail.com xxx 28210292 shirish 2333604 shirish@yahoo.com ddsds 28310292 sonali 323232 sonali@bolly.com filmstar 284select * from t1 where user_id>=10292; 285user_id name phone ref_email detail 28610292 sanjeev 29153373 sansh777@hotmail.com xxx 28710292 shirish 2333604 shirish@yahoo.com ddsds 28810292 sonali 323232 sonali@bolly.com filmstar 28910293 shirish 2333604 shirish@yahoo.com ddsds 290select * from t1 where user_id>10292; 291user_id name phone ref_email detail 29210293 shirish 2333604 shirish@yahoo.com ddsds 293select * from t1 where user_id<10292; 294user_id name phone ref_email detail 29510291 sanjeev 29153373 sansh777@hotmail.com xxx 296drop table t1; 297SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 298Warnings: 299Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 300CREATE TABLE t1 (a int not null, b int not null,c int not null, 301key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM; 302Warnings: 303Warning 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 304SET sql_mode = default; 305show index from t1; 306Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 307t1 0 PRIMARY 1 a A # NULL NULL BTREE 308t1 0 PRIMARY 2 b A # NULL NULL BTREE 309t1 0 c 1 c A # NULL NULL BTREE 310t1 0 b 1 b A # NULL NULL BTREE 311t1 1 a 1 a A # NULL NULL BTREE 312t1 1 a_2 1 a A # NULL NULL BTREE 313drop table t1; 314create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY; 315alter table t1 engine=MyISAM; 316insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); 317select * from t1; 318col1 col2 3191 1 3205 2 3212 3 3223 4 3234 4 324update t1 set col2='7' where col1='4'; 325select * from t1; 326col1 col2 3271 1 3285 2 3292 3 3303 4 3314 7 332alter table t1 add co3 int not null; 333select * from t1; 334col1 col2 co3 3351 1 0 3365 2 0 3372 3 0 3383 4 0 3394 7 0 340update t1 set col2='9' where col1='2'; 341select * from t1; 342col1 col2 co3 3431 1 0 3445 2 0 3452 9 0 3463 4 0 3474 7 0 348drop table t1; 349create table t1 (a int not null , b int, primary key (a)) engine = MyISAM; 350create table t2 (a int not null , b int, primary key (a)) engine = MEMORY; 351insert into t1 VALUES (1,3) , (2,3), (3,3); 352select * from t1; 353a b 3541 3 3552 3 3563 3 357insert into t2 select * from t1; 358select * from t2; 359a b 3601 3 3612 3 3623 3 363delete from t1 where b = 3; 364select * from t1; 365a b 366insert into t1 select * from t2; 367select * from t1; 368a b 3693 3 3702 3 3711 3 372select * from t2; 373a b 3741 3 3752 3 3763 3 377drop table t1,t2; 378CREATE TABLE t1 ( 379id int(11) NOT NULL auto_increment, 380ggid varchar(32) binary DEFAULT '' NOT NULL, 381email varchar(64) DEFAULT '' NOT NULL, 382passwd varchar(32) binary DEFAULT '' NOT NULL, 383PRIMARY KEY (id), 384UNIQUE ggid (ggid) 385) ENGINE=MyISAM; 386insert into t1 (ggid,passwd) values ('test1','xxx'); 387insert into t1 (ggid,passwd) values ('test2','yyy'); 388insert into t1 (ggid,passwd) values ('test2','this will fail'); 389ERROR 23000: Duplicate entry 'test2' for key 'ggid' 390insert into t1 (ggid,id) values ('this will fail',1); 391ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 392select * from t1 where ggid='test1'; 393id ggid email passwd 3941 test1 xxx 395select * from t1 where passwd='xxx'; 396id ggid email passwd 3971 test1 xxx 398select * from t1 where id=2; 399id ggid email passwd 4002 test2 yyy 401replace into t1 (ggid,id) values ('this will work',1); 402replace into t1 (ggid,passwd) values ('test2','this will work'); 403update t1 set id=100,ggid='test2' where id=1; 404ERROR 23000: Duplicate entry 'test2' for key 'ggid' 405select * from t1; 406id ggid email passwd 4071 this will work 4083 test2 this will work 409select * from t1 where id=1; 410id ggid email passwd 4111 this will work 412select * from t1 where id=999; 413id ggid email passwd 414drop table t1; 415CREATE TABLE t1 ( 416user_name varchar(12), 417password text, 418subscribed char(1), 419user_id int(11) DEFAULT '0' NOT NULL, 420quota bigint(20), 421weight double, 422access_date date, 423access_time time, 424approved datetime, 425dummy_primary_key int(11) NOT NULL auto_increment, 426PRIMARY KEY (dummy_primary_key) 427) ENGINE=MyISAM; 428INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); 429INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 430INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); 431INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); 432INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); 433select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; 434user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key 435user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1 436user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2 437user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3 438user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4 439user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5 440drop table t1; 441CREATE TABLE t1 ( 442id int(11) NOT NULL auto_increment, 443parent_id int(11) DEFAULT '0' NOT NULL, 444level tinyint(4) DEFAULT '0' NOT NULL, 445KEY (id), 446KEY parent_id (parent_id), 447KEY level (level) 448) engine=MyISAM; 449INSERT 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); 450INSERT INTO t1 values (179,5,2); 451update t1 set parent_id=parent_id+100; 452select * from t1 where parent_id=102; 453id parent_id level 4548 102 2 4559 102 2 45615 102 2 457update t1 set id=id+1000; 458update t1 set id=1024 where id=1009; 459select * from t1; 460id parent_id level 4611001 100 0 4621003 101 1 4631004 101 1 4641008 102 2 4651024 102 2 4661017 103 2 4671022 104 2 4681024 104 2 4691028 105 2 4701029 105 2 4711030 105 2 4721031 106 2 4731032 106 2 4741033 106 2 4751203 107 2 4761202 107 2 4771020 103 2 4781157 100 0 4791193 105 2 4801040 107 2 4811002 101 1 4821015 102 2 4831006 101 1 4841034 106 2 4851035 106 2 4861016 103 2 4871007 101 1 4881036 107 2 4891018 103 2 4901026 105 2 4911027 105 2 4921183 104 2 4931038 107 2 4941025 105 2 4951037 107 2 4961021 104 2 4971019 103 2 4981005 101 1 4991179 105 2 500update ignore t1 set id=id+1; 501select * from t1; 502id parent_id level 5031002 100 0 5041004 101 1 5051005 101 1 5061009 102 2 5071025 102 2 5081018 103 2 5091023 104 2 5101025 104 2 5111029 105 2 5121030 105 2 5131031 105 2 5141032 106 2 5151033 106 2 5161034 106 2 5171204 107 2 5181203 107 2 5191021 103 2 5201158 100 0 5211194 105 2 5221041 107 2 5231003 101 1 5241016 102 2 5251007 101 1 5261035 106 2 5271036 106 2 5281017 103 2 5291008 101 1 5301037 107 2 5311019 103 2 5321027 105 2 5331028 105 2 5341184 104 2 5351039 107 2 5361026 105 2 5371038 107 2 5381022 104 2 5391020 103 2 5401006 101 1 5411180 105 2 542update ignore t1 set id=1023 where id=1010; 543select * from t1 where parent_id=102; 544id parent_id level 5451009 102 2 5461025 102 2 5471016 102 2 548explain select level from t1 where level=1; 549id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5501 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index 551Warnings: 552Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level` from `test`.`t1` where (`test`.`t1`.`level` = 1) 553select level,id from t1 where level=1; 554level id 5551 1004 5561 1005 5571 1003 5581 1007 5591 1008 5601 1006 561select level,id,parent_id from t1 where level=1; 562level id parent_id 5631 1004 101 5641 1005 101 5651 1003 101 5661 1007 101 5671 1008 101 5681 1006 101 569select level,id from t1 where level=1 order by id; 570level id 5711 1003 5721 1004 5731 1005 5741 1006 5751 1007 5761 1008 577delete from t1 where level=1; 578select * from t1; 579id parent_id level 5801002 100 0 5811009 102 2 5821025 102 2 5831018 103 2 5841023 104 2 5851025 104 2 5861029 105 2 5871030 105 2 5881031 105 2 5891032 106 2 5901033 106 2 5911034 106 2 5921204 107 2 5931203 107 2 5941021 103 2 5951158 100 0 5961194 105 2 5971041 107 2 5981016 102 2 5991035 106 2 6001036 106 2 6011017 103 2 6021037 107 2 6031019 103 2 6041027 105 2 6051028 105 2 6061184 104 2 6071039 107 2 6081026 105 2 6091038 107 2 6101022 104 2 6111020 103 2 6121180 105 2 613drop table t1; 614CREATE TABLE t1 ( 615sca_code char(6) NOT NULL, 616cat_code char(6) NOT NULL, 617sca_desc varchar(50), 618lan_code char(2) NOT NULL, 619sca_pic varchar(100), 620sca_sdesc varchar(50), 621sca_sch_desc varchar(16), 622PRIMARY KEY (sca_code, cat_code, lan_code), 623INDEX sca_pic (sca_pic) 624) engine = MyISAM ; 625INSERT 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'); 626select count(*) from t1 where sca_code = 'PD'; 627count(*) 6281 629select count(*) from t1 where sca_code <= 'PD'; 630count(*) 6311 632select count(*) from t1 where sca_pic is null; 633count(*) 6342 635alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); 636select count(*) from t1 where sca_code='PD' and sca_pic is null; 637count(*) 6381 639select count(*) from t1 where cat_code='E'; 640count(*) 6410 642alter table t1 drop index sca_pic, add index (sca_pic, cat_code); 643select count(*) from t1 where sca_code='PD' and sca_pic is null; 644count(*) 6451 646select count(*) from t1 where sca_pic >= 'n'; 647count(*) 6481 649select sca_pic from t1 where sca_pic is null; 650sca_pic 651NULL 652NULL 653update t1 set sca_pic="test" where sca_pic is null; 654delete from t1 where sca_code='pd'; 655drop table t1; 656set @a:=now(); 657CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=MyISAM; 658insert into t1 (a) values(1),(2),(3); 659select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; 660a 6611 6622 6633 664select a from t1 natural join t1 as t2 where b >= @a order by a; 665a 6661 6672 6683 669update t1 set a=5 where a=1; 670select a from t1; 671a 6722 6733 6745 675drop table t1; 676create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM; 677insert into t1 values("hello",1),("world",2); 678select * from t1 order by b desc; 679a b 680world 2 681hello 1 682optimize table t1; 683Table Op Msg_type Msg_text 684test.t1 optimize status OK 685show keys from t1; 686Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 687t1 0 PRIMARY 1 a A # NULL NULL BTREE 688drop table t1; 689create table t1 (i int, j int ) ENGINE=MyISAM; 690insert into t1 values (1,2); 691select * from t1 where i=1 and j=2; 692i j 6931 2 694create index ax1 on t1 (i,j); 695select * from t1 where i=1 and j=2; 696i j 6971 2 698drop table t1; 699CREATE TABLE t1 ( 700a int3 unsigned NOT NULL, 701b int1 unsigned NOT NULL, 702UNIQUE (a, b) 703) ENGINE = MyISAM; 704INSERT INTO t1 VALUES (1, 1); 705SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; 706MIN(B) MAX(b) 7071 1 708drop table t1; 709create 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; 710insert 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); 711explain select * from t1 where a > 0 and a < 50; 712id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7131 SIMPLE t1 NULL system PRIMARY NULL NULL NULL # 100.00 NULL 714Warnings: 715Note 1003 /* select#1 */ select '1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d`,'1' AS `e`,'1' AS `f`,'1' AS `g`,'1' AS `h`,'1' AS `i`,'1' AS `j`,'1' AS `k`,'1' AS `l`,'1' AS `m`,'1' AS `n`,'1' AS `o`,'1' AS `p`,'1' AS `q`,'1' AS `r`,'1' AS `s`,'1' AS `t`,'1' AS `u`,'1' AS `v`,'1' AS `w`,'1' AS `x`,'1' AS `y`,'1' AS `z`,'1' AS `a1`,'1' AS `a2`,'1' AS `a3`,'1' AS `a4`,'1' AS `a5`,'1' AS `a6`,'1' AS `a7`,'1' AS `a8`,'1' AS `a9`,'1' AS `b1`,'1' AS `b2`,'1' AS `b3`,'1' AS `b4`,'1' AS `b5`,'1' AS `b6` from dual where (('1' > 0) and ('1' < 50)) 716drop table t1; 717create 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; 718insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 719LOCK TABLES t1 WRITE; 720insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 721ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 722select id from t1; 723id 7240 7251 7262 72799 728select id from t1; 729id 7300 7311 7322 73399 734UNLOCK TABLES; 735DROP TABLE t1; 736create 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; 737insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 738LOCK TABLES t1 WRITE; 739begin; 740insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 741ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 742select id from t1; 743id 7440 7451 7462 74799 748insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); 749Warnings: 750Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' 751commit; 752select id,id3 from t1; 753id id3 7540 0 7552 2 7561 1 75799 2 758100 2 759UNLOCK TABLES; 760DROP TABLE t1; 761create table t1 (a char(20), unique (a(5))) engine=MyISAM; 762drop table t1; 763create table t1 (a char(20), index (a(5))) engine=MyISAM; 764show create table t1; 765Table Create Table 766t1 CREATE TABLE `t1` ( 767 `a` char(20) DEFAULT NULL, 768 KEY `a` (`a`(5)) 769) ENGINE=MyISAM DEFAULT CHARSET=latin1 770drop table t1; 771create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM; 772insert into t1 values (NULL),(NULL),(NULL); 773delete from t1 where a=3; 774insert into t1 values (NULL); 775select * from t1; 776a 7771 7782 7794 780alter table t1 add b int; 781select * from t1; 782a b 7831 NULL 7842 NULL 7854 NULL 786drop table t1; 787create table t1 788( 789id int auto_increment primary key, 790name varchar(32) not null, 791value text not null, 792uid int not null, 793unique key(name,uid) 794) engine=MyISAM; 795insert into t1 values (1,'one','one value',101), 796(2,'two','two value',102),(3,'three','three value',103); 797set insert_id=5; 798replace into t1 (value,name,uid) values ('other value','two',102); 799delete from t1 where uid=102; 800set insert_id=5; 801replace into t1 (value,name,uid) values ('other value','two',102); 802set insert_id=6; 803replace into t1 (value,name,uid) values ('other value','two',102); 804select * from t1; 805id name value uid 8061 one one value 101 8073 three three value 103 8086 two other value 102 809drop table t1; 810create database mysqltest; 811create table mysqltest.t1 (a int not null) engine= MyISAM; 812insert into mysqltest.t1 values(1); 813create table mysqltest.t2 (a int not null) engine= MEMORY; 814insert into mysqltest.t2 values(1); 815create table mysqltest.t3 (a int not null) engine= MEMORY; 816insert into mysqltest.t3 values(1); 817commit; 818drop database mysqltest; 819show tables from mysqltest; 820ERROR 42000: Unknown database 'mysqltest' 821set autocommit=0; 822create table t1 (a int not null) engine= MyISAM; 823insert into t1 values(1),(2); 824truncate table t1; 825commit; 826truncate table t1; 827truncate table t1; 828select * from t1; 829a 830insert into t1 values(1),(2); 831delete from t1; 832select * from t1; 833a 834commit; 835drop table t1; 836set autocommit=1; 837create table t1 (a int not null) engine= MyISAM; 838insert into t1 values(1),(2); 839truncate table t1; 840insert into t1 values(1),(2); 841select * from t1; 842a 8431 8442 845truncate table t1; 846insert into t1 values(1),(2); 847delete from t1; 848select * from t1; 849a 850drop table t1; 851create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM; 852insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 853explain select * from t1 order by a; 854id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8551 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort 856Warnings: 857Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a` 858explain select * from t1 order by b; 859id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8601 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort 861Warnings: 862Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`b` 863explain select * from t1 order by c; 864id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8651 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort 866Warnings: 867Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`c` 868explain select a from t1 order by a; 869id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8701 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 Using index 871Warnings: 872Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` 873explain select b from t1 order by b; 874id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8751 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index 876Warnings: 877Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` 878explain select a,b from t1 order by b; 879id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8801 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort 881Warnings: 882Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` 883explain select a,b from t1; 884id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8851 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL 886Warnings: 887Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 888explain select a,b,c from t1; 889id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8901 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL 891Warnings: 892Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` 893drop table t1; 894create table t1 (t int not null default 1, key (t)) engine=MyISAM; 895desc t1; 896Field Type Null Key Default Extra 897t int(11) NO MUL 1 898drop table t1; 899SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 900Warnings: 901Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 902CREATE TABLE t1 ( 903number bigint(20) NOT NULL default '0', 904cname char(15) NOT NULL default '', 905carrier_id smallint(6) NOT NULL default '0', 906privacy tinyint(4) NOT NULL default '0', 907last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 908last_mod_id smallint(6) NOT NULL default '0', 909last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 910last_app_id smallint(6) default '-1', 911version smallint(6) NOT NULL default '0', 912assigned_scps int(11) default '0', 913status tinyint(4) default '0' 914) ENGINE=MyISAM; 915INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); 916INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); 917INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); 918INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); 919INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); 920INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); 921CREATE TABLE t2 ( 922number bigint(20) NOT NULL default '0', 923cname char(15) NOT NULL default '', 924carrier_id smallint(6) NOT NULL default '0', 925privacy tinyint(4) NOT NULL default '0', 926last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 927last_mod_id smallint(6) NOT NULL default '0', 928last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 929last_app_id smallint(6) default '-1', 930version smallint(6) NOT NULL default '0', 931assigned_scps int(11) default '0', 932status tinyint(4) default '0' 933) ENGINE=MyISAM; 934INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); 935INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); 936INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); 937INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); 938select * from t1; 939number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 9404077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1 9419197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0 942650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1 943302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0 9446014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 945333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 946select * from t2; 947number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 9484077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1 9499197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0 950650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1 951333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 952delete 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); 953select * from t1; 954number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 9556014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 956333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 957select * from t2; 958number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 959333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 960select * from t2; 961number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 962333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 963drop table t1,t2; 964SET sql_mode = default; 965create 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; 966BEGIN; 967SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 968SELECT @@tx_isolation,@@global.tx_isolation; 969@@tx_isolation @@global.tx_isolation 970SERIALIZABLE REPEATABLE-READ 971Warnings: 972Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 973Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 974insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); 975select id, code, name from t1 order by id; 976id code name 9771 1 Tim 9782 1 Monty 9793 2 David 980COMMIT; 981BEGIN; 982SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 983insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); 984select id, code, name from t1 order by id; 985id code name 9861 1 Tim 9872 1 Monty 9883 2 David 9894 2 Erik 9905 3 Sasha 991COMMIT; 992BEGIN; 993SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 994insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); 995select id, code, name from t1 order by id; 996id code name 9971 1 Tim 9982 1 Monty 9993 2 David 10004 2 Erik 10015 3 Sasha 10026 3 Jeremy 10037 4 Matt 1004COMMIT; 1005DROP TABLE t1; 1006create table t1 (n int(10), d int(10)) engine=MyISAM; 1007create table t2 (n int(10), d int(10)) engine=MyISAM; 1008insert into t1 values(1,1),(1,2); 1009insert into t2 values(1,10),(2,20); 1010UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 1011select * from t1; 1012n d 10131 10 10141 10 1015select * from t2; 1016n d 10171 30 10182 20 1019drop table t1,t2; 1020create table t1 (a int, b int) engine=MyISAM; 1021insert into t1 values(20,null); 1022select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 1023t2.b=t3.a; 1024b ifnull(t2.b,"this is null") 1025NULL this is null 1026select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 1027t2.b=t3.a order by 1; 1028b ifnull(t2.b,"this is null") 1029NULL this is null 1030insert into t1 values(10,null); 1031select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 1032t2.b=t3.a order by 1; 1033b ifnull(t2.b,"this is null") 1034NULL this is null 1035NULL this is null 1036drop table t1; 1037create table t1 (a varchar(10) not null) engine = MEMORY; 1038create table t2 (b varchar(10) not null unique) engine=MyISAM; 1039select t1.a from t1,t2 where t1.a=t2.b; 1040a 1041drop table t1,t2; 1042create table t1 (a int not null, b int, primary key (a)) engine = MyISAM; 1043create table t2 (a int not null, b int, primary key (a)) engine = MyISAM; 1044insert into t1 values (10, 20); 1045insert into t2 values (10, 20); 1046update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; 1047drop table t1,t2; 1048CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM; 1049INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 1050UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; 1051SELECT * from t1; 1052a b 10531 1 1054102 2 1055103 3 10564 4 10575 5 10586 6 10597 7 10608 8 10619 9 1062drop table t1; 1063CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM; 1064CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM; 1065INSERT 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); 1066INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 1067update t1,t2 set t1.a=t1.a+100; 1068select * from t1; 1069a b 1070101 1 1071102 2 1072103 3 1073104 4 1074105 5 1075106 6 1076107 7 1077108 8 1078109 9 1079110 10 1080111 11 1081112 12 1082update t1,t2 set t1.a=t1.a+100 where t1.a=101; 1083select * from t1; 1084a b 1085201 1 1086102 2 1087103 3 1088104 4 1089105 5 1090106 6 1091107 7 1092108 8 1093109 9 1094110 10 1095111 11 1096112 12 1097update t1,t2 set t1.b=t1.b+10 where t1.b=2; 1098select * from t1; 1099a b 1100201 1 1101102 12 1102103 3 1103104 4 1104105 5 1105106 6 1106107 7 1107108 8 1108109 9 1109110 10 1110111 11 1111112 12 1112update 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; 1113select * from t1; 1114a b 1115201 1 1116102 12 1117103 5 1118104 6 1119105 7 1120106 6 1121107 7 1122108 8 1123109 9 1124110 10 1125111 11 1126112 12 1127select * from t2; 1128a b 11291 1 11302 2 11313 13 11324 14 11335 15 11346 6 11357 7 11368 8 11379 9 1138drop table t1,t2; 1139CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY; 1140CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM; 1141SET AUTOCOMMIT=0; 1142INSERT INTO t1 ( B_ID ) VALUES ( 1 ); 1143INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); 1144ROLLBACK; 1145Warnings: 1146Warning 1196 Some non-transactional changed tables couldn't be rolled back 1147SELECT * FROM t1; 1148B_ID 11491 1150drop table t1,t2; 1151create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM; 1152insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); 1153select distinct parent,child from t1 order by parent; 1154parent child 11550 4 11561 2 11571 3 11582 1 1159drop table t1; 1160create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM; 1161create table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY; 1162insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); 1163insert into t2 (a) select b from t1; 1164insert into t1 (b) select b from t2; 1165insert into t2 (a) select b from t1; 1166insert into t1 (a) select b from t2; 1167insert into t2 (a) select b from t1; 1168insert into t1 (a) select b from t2; 1169insert into t2 (a) select b from t1; 1170insert into t1 (a) select b from t2; 1171insert into t2 (a) select b from t1; 1172insert into t1 (a) select b from t2; 1173insert into t2 (a) select b from t1; 1174insert into t1 (a) select b from t2; 1175insert into t2 (a) select b from t1; 1176insert into t1 (a) select b from t2; 1177insert into t2 (a) select b from t1; 1178insert into t1 (a) select b from t2; 1179insert into t2 (a) select b from t1; 1180insert into t1 (a) select b from t2; 1181select count(*) from t1; 1182count(*) 118329267 1184explain select * from t1 where c between 1 and 2500; 1185id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11861 SIMPLE t1 NULL range c c 5 NULL # 100.00 Using index condition 1187Warnings: 1188Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` between 1 and 2500) 1189update t1 set c=a; 1190explain select * from t1 where c between 1 and 2500; 1191id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11921 SIMPLE t1 NULL range c c 5 NULL # 100.00 Using index condition 1193Warnings: 1194Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` between 1 and 2500) 1195drop table t1,t2; 1196create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM; 1197insert into t1 (id) values (null),(null),(null),(null),(null); 1198update t1 set fk=69 where fk is null order by id limit 1; 1199SELECT * from t1; 1200id fk 12011 69 12022 NULL 12033 NULL 12044 NULL 12055 NULL 1206drop table t1; 1207create table t1 (a int not null, b int not null, key (a)) engine=MyISAM; 1208insert 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); 1209SET @tmp=0; 1210update t1 set b=(@tmp:=@tmp+1) order by a; 1211update t1 set b=99 where a=1 order by b asc limit 1; 1212update t1 set b=100 where a=1 order by b desc limit 2; 1213update t1 set a=a+10+b where a=1 order by b; 1214select * from t1 order by a,b; 1215a b 12162 4 12172 5 12182 6 12193 7 12203 8 12213 9 12223 10 12233 11 12243 12 122513 2 1226111 100 1227111 100 1228drop table t1; 1229create table t1 ( c char(8) not null ) engine=MyISAM; 1230insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 1231insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 1232alter table t1 add b char(8) not null; 1233alter table t1 add a char(8) not null; 1234alter table t1 add primary key (a,b,c); 1235update t1 set a=c, b=c; 1236create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM; 1237insert into t2 select * from t1; 1238delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 1239drop table t1,t2; 1240SET AUTOCOMMIT=1; 1241create table t1 (a integer auto_increment primary key) engine=MyISAM; 1242insert into t1 (a) values (NULL),(NULL); 1243truncate table t1; 1244insert into t1 (a) values (NULL),(NULL); 1245SELECT * from t1; 1246a 12471 12482 1249drop table t1; 1250CREATE TABLE t1 (col1 int(1))ENGINE=MyISAM; 1251CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx 1252(stamp))ENGINE=MyISAM; 1253insert into t1 values (1),(2),(3); 1254insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); 1255Warnings: 1256Warning 1265 Data truncated for column 'stamp' at row 3 1257SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < 1258'20020204120000' GROUP BY col1; 1259col1 12601 12612 12623 12634 1264drop table t1,t2; 1265CREATE TABLE t1 ( 1266`id` int(10) unsigned NOT NULL auto_increment, 1267`id_object` int(10) unsigned default '0', 1268`id_version` int(10) unsigned NOT NULL default '1', 1269`label` varchar(100) NOT NULL default '', 1270`description` text, 1271PRIMARY KEY (`id`), 1272KEY `id_object` (`id_object`), 1273KEY `id_version` (`id_version`) 1274) ENGINE=MyISAM; 1275INSERT 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); 1276CREATE TABLE t2 ( 1277`id` int(10) unsigned NOT NULL auto_increment, 1278`id_version` int(10) unsigned NOT NULL default '1', 1279PRIMARY KEY (`id`), 1280KEY `id_version` (`id_version`) 1281) ENGINE=MyISAM; 1282INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); 1283SELECT t2.id, t1.`label` FROM t2 INNER JOIN 1284(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 1285ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); 1286id label 12873382 Test 1288102 Le Pekin (Test) 12891794 Test de resto 12901822 Test 3 12913524 Societe Test 12923525 Fournisseur Test 1293drop table t1,t2; 1294create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM; 1295create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM; 1296create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=MEMORY; 1297create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY; 1298create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM; 1299create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM; 1300insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 1301insert t2 select * from t1; 1302insert t3 select * from t1; 1303insert t4 select * from t1; 1304insert t5 select * from t1; 1305insert t6 select * from t1; 1306checksum table t1, t2, t3, t4, t5, t6, t7 quick; 1307Table Checksum 1308test.t1 2948697075 1309test.t2 NULL 1310test.t3 NULL 1311test.t4 NULL 1312test.t5 2948697075 1313test.t6 NULL 1314test.t7 NULL 1315Warnings: 1316Error 1146 Table 'test.t7' doesn't exist 1317checksum table t1, t2, t3, t4, t5, t6, t7; 1318Table Checksum 1319test.t1 2948697075 1320test.t2 2948697075 1321test.t3 2948697075 1322test.t4 2948697075 1323test.t5 2948697075 1324test.t6 2948697075 1325test.t7 NULL 1326Warnings: 1327Error 1146 Table 'test.t7' doesn't exist 1328checksum table t1, t2, t3, t4, t5, t6, t7 extended; 1329Table Checksum 1330test.t1 2948697075 1331test.t2 2948697075 1332test.t3 2948697075 1333test.t4 2948697075 1334test.t5 2948697075 1335test.t6 2948697075 1336test.t7 NULL 1337Warnings: 1338Error 1146 Table 'test.t7' doesn't exist 1339drop table t1,t2,t3, t4, t5, t6; 1340create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=MyISAM; 1341insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); 1342select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1; 1343trim(name2) 1344fff 1345sss 1346ttt 1347first 1348second 1349third 13501 13512 13523 1353drop table t1; 1354create table t1 (a int) engine=MyISAM; 1355create table t2 like t1; 1356show create table t2; 1357Table Create Table 1358t2 CREATE TABLE `t2` ( 1359 `a` int(11) DEFAULT NULL 1360) ENGINE=MyISAM DEFAULT CHARSET=latin1 1361drop table t1,t2; 1362flush status; 1363show status like "binlog_cache_use"; 1364Variable_name Value 1365Binlog_cache_use 0 1366show status like "binlog_cache_disk_use"; 1367Variable_name Value 1368Binlog_cache_disk_use 0 1369create table t1 (a int) engine=MyISAM; 1370show status like "binlog_cache_use"; 1371Variable_name Value 1372Binlog_cache_use 0 1373show status like "binlog_cache_disk_use"; 1374Variable_name Value 1375Binlog_cache_disk_use 0 1376begin; 1377delete from t1; 1378commit; 1379show status like "binlog_cache_use"; 1380Variable_name Value 1381Binlog_cache_use 0 1382show status like "binlog_cache_disk_use"; 1383Variable_name Value 1384Binlog_cache_disk_use 0 1385drop table t1; 1386create table t1 (c char(10), index (c,c)) engine=MyISAM; 1387ERROR 42S21: Duplicate column name 'c' 1388create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM; 1389ERROR 42S21: Duplicate column name 'c1' 1390create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM; 1391ERROR 42S21: Duplicate column name 'c1' 1392create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM; 1393ERROR 42S21: Duplicate column name 'c1' 1394create table t1 (c1 char(10), c2 char(10)) engine=MyISAM; 1395alter table t1 add key (c1,c1); 1396ERROR 42S21: Duplicate column name 'c1' 1397alter table t1 add key (c2,c1,c1); 1398ERROR 42S21: Duplicate column name 'c1' 1399alter table t1 add key (c1,c2,c1); 1400ERROR 42S21: Duplicate column name 'c1' 1401alter table t1 add key (c1,c1,c2); 1402ERROR 42S21: Duplicate column name 'c1' 1403drop table t1; 1404create table t1(a int(1) , b int(1)) engine=MyISAM; 1405insert into t1 values ('1111', '3333'); 1406select distinct concat(a, b) from t1; 1407concat(a, b) 140811113333 1409drop table t1; 1410create temporary table t1 (a int) engine=MyISAM; 1411insert into t1 values (4711); 1412truncate t1; 1413insert into t1 values (42); 1414select * from t1; 1415a 141642 1417drop table t1; 1418create table t1 (a int) engine=MyISAM; 1419insert into t1 values (4711); 1420truncate t1; 1421insert into t1 values (42); 1422select * from t1; 1423a 142442 1425drop table t1; 1426create 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; 1427insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3); 1428select * from t1 order by a,b,c,d; 1429a b c d e 14301 1 a 1 1 14312 2 b 2 2 14323 3 ab 3 3 1433explain select * from t1 order by a,b,c,d; 1434id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14351 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using filesort 1436Warnings: 1437Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`e` AS `e` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c`,`test`.`t1`.`d` 1438drop table t1; 1439create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM; 1440insert into t1 values ('8', '6'), ('4', '7'); 1441select min(a) from t1; 1442min(a) 14434 1444select min(b) from t1 where a='8'; 1445min(b) 14466 1447drop table t1; 1448create table t1 (x bigint unsigned not null primary key) engine=MyISAM; 1449insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); 1450select * from t1; 1451x 145218446744073709551600 145318446744073709551601 1454select count(*) from t1 where x>0; 1455count(*) 14562 1457select count(*) from t1 where x=0; 1458count(*) 14590 1460select count(*) from t1 where x<0; 1461count(*) 14620 1463select count(*) from t1 where x < -16; 1464count(*) 14650 1466select count(*) from t1 where x = -16; 1467count(*) 14680 1469explain select count(*) from t1 where x > -16; 1470id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14711 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 2 50.00 Using where; Using index 1472Warnings: 1473Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`x` > <cache>(-(16))) 1474select count(*) from t1 where x > -16; 1475count(*) 14762 1477select * from t1 where x > -16; 1478x 147918446744073709551600 148018446744073709551601 1481select count(*) from t1 where x = 18446744073709551601; 1482count(*) 14831 1484drop table t1; 1485set default_storage_engine=MyISAM; 1486drop table if exists t1,t2,t3; 1487--- Testing varchar --- 1488--- Testing varchar --- 1489create table t1 (v varchar(10), c char(10), t text); 1490insert into t1 values('+ ', '+ ', '+ '); 1491set @a=repeat(' ',20); 1492insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 1493Warnings: 1494Note 1265 Data truncated for column 'v' at row 1 1495select concat('*',v,'*',c,'*',t,'*') from t1; 1496concat('*',v,'*',c,'*',t,'*') 1497*+ *+*+ * 1498*+ *+*+ * 1499show create table t1; 1500Table Create Table 1501t1 CREATE TABLE `t1` ( 1502 `v` varchar(10) DEFAULT NULL, 1503 `c` char(10) DEFAULT NULL, 1504 `t` text 1505) ENGINE=MyISAM DEFAULT CHARSET=latin1 1506create table t2 like t1; 1507show create table t2; 1508Table Create Table 1509t2 CREATE TABLE `t2` ( 1510 `v` varchar(10) DEFAULT NULL, 1511 `c` char(10) DEFAULT NULL, 1512 `t` text 1513) ENGINE=MyISAM DEFAULT CHARSET=latin1 1514create table t3 select * from t1; 1515show create table t3; 1516Table Create Table 1517t3 CREATE TABLE `t3` ( 1518 `v` varchar(10) DEFAULT NULL, 1519 `c` char(10) DEFAULT NULL, 1520 `t` text 1521) ENGINE=MyISAM DEFAULT CHARSET=latin1 1522alter table t1 modify c varchar(10); 1523show create table t1; 1524Table Create Table 1525t1 CREATE TABLE `t1` ( 1526 `v` varchar(10) DEFAULT NULL, 1527 `c` varchar(10) DEFAULT NULL, 1528 `t` text 1529) ENGINE=MyISAM DEFAULT CHARSET=latin1 1530alter table t1 modify v char(10); 1531show create table t1; 1532Table Create Table 1533t1 CREATE TABLE `t1` ( 1534 `v` char(10) DEFAULT NULL, 1535 `c` varchar(10) DEFAULT NULL, 1536 `t` text 1537) ENGINE=MyISAM DEFAULT CHARSET=latin1 1538alter table t1 modify t varchar(10); 1539Warnings: 1540Note 1265 Data truncated for column 't' at row 2 1541show create table t1; 1542Table Create Table 1543t1 CREATE TABLE `t1` ( 1544 `v` char(10) DEFAULT NULL, 1545 `c` varchar(10) DEFAULT NULL, 1546 `t` varchar(10) DEFAULT NULL 1547) ENGINE=MyISAM DEFAULT CHARSET=latin1 1548select concat('*',v,'*',c,'*',t,'*') from t1; 1549concat('*',v,'*',c,'*',t,'*') 1550*+*+*+ * 1551*+*+*+ * 1552drop table t1,t2,t3; 1553create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))) stats_persistent=0; 1554show create table t1; 1555Table Create Table 1556t1 CREATE TABLE `t1` ( 1557 `v` varchar(10) DEFAULT NULL, 1558 `c` char(10) DEFAULT NULL, 1559 `t` text, 1560 KEY `v` (`v`), 1561 KEY `c` (`c`), 1562 KEY `t` (`t`(10)) 1563) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1564select count(*) from t1; 1565count(*) 1566270 1567insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 1568select count(*) from t1 where v='a'; 1569count(*) 157010 1571select count(*) from t1 where c='a'; 1572count(*) 157310 1574select count(*) from t1 where t='a'; 1575count(*) 157610 1577select count(*) from t1 where v='a '; 1578count(*) 157910 1580select count(*) from t1 where c='a '; 1581count(*) 158210 1583select count(*) from t1 where t='a '; 1584count(*) 158510 1586select count(*) from t1 where v between 'a' and 'a '; 1587count(*) 158810 1589select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1590count(*) 159110 1592select count(*) from t1 where v like 'a%'; 1593count(*) 159411 1595select count(*) from t1 where c like 'a%'; 1596count(*) 159711 1598select count(*) from t1 where t like 'a%'; 1599count(*) 160011 1601select count(*) from t1 where v like 'a %'; 1602count(*) 16039 1604explain select count(*) from t1 where v='a '; 1605id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16061 SIMPLE t1 NULL ref v v 13 const # 100.00 Using index 1607Warnings: 1608Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ') 1609explain select count(*) from t1 where c='a '; 1610id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16111 SIMPLE t1 NULL ref c c 11 const # 100.00 Using index 1612Warnings: 1613Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`c` = 'a ') 1614explain select count(*) from t1 where t='a '; 1615id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16161 SIMPLE t1 NULL ref t t 13 const # 100.00 Using where 1617Warnings: 1618Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`t` = 'a ') 1619explain select count(*) from t1 where v like 'a%'; 1620id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16211 SIMPLE t1 NULL range v v 13 NULL # 100.00 Using where; Using index 1622Warnings: 1623Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%') 1624explain select count(*) from t1 where v between 'a' and 'a '; 1625id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16261 SIMPLE t1 NULL ref v v 13 const # 100.00 Using where; Using index 1627Warnings: 1628Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ') 1629explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1630id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16311 SIMPLE t1 NULL ref v v 13 const # 100.00 Using where; Using index 1632Warnings: 1633Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n')) 1634alter table t1 add unique(v); 1635ERROR 23000: Duplicate entry '{ ' for key 'v_2' 1636alter table t1 add key(v); 1637Warnings: 1638Warning 1831 Duplicate index 'v_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 1639select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 1640qq 1641*a*a*a* 1642*a *a*a * 1643*a *a*a * 1644*a *a*a * 1645*a *a*a * 1646*a *a*a * 1647*a *a*a * 1648*a *a*a * 1649*a *a*a * 1650*a *a*a * 1651explain select * from t1 where v='a'; 1652id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16531 SIMPLE t1 NULL ref v,v_2 # 13 const # 100.00 NULL 1654Warnings: 1655Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a') 1656select v,count(*) from t1 group by v limit 10; 1657v count(*) 1658a 1 1659a 10 1660b 10 1661c 10 1662d 10 1663e 10 1664f 10 1665g 10 1666h 10 1667i 10 1668select v,count(t) from t1 group by v limit 10; 1669v count(t) 1670a 1 1671a 10 1672b 10 1673c 10 1674d 10 1675e 10 1676f 10 1677g 10 1678h 10 1679i 10 1680select v,count(c) from t1 group by v limit 10; 1681v count(c) 1682a 1 1683a 10 1684b 10 1685c 10 1686d 10 1687e 10 1688f 10 1689g 10 1690h 10 1691i 10 1692select sql_big_result v,count(t) from t1 group by v limit 10; 1693v count(t) 1694a 1 1695a 10 1696b 10 1697c 10 1698d 10 1699e 10 1700f 10 1701g 10 1702h 10 1703i 10 1704select sql_big_result v,count(c) from t1 group by v limit 10; 1705v count(c) 1706a 1 1707a 10 1708b 10 1709c 10 1710d 10 1711e 10 1712f 10 1713g 10 1714h 10 1715i 10 1716select c,count(*) from t1 group by c limit 10; 1717c count(*) 1718a 1 1719a 10 1720b 10 1721c 10 1722d 10 1723e 10 1724f 10 1725g 10 1726h 10 1727i 10 1728select c,count(t) from t1 group by c limit 10; 1729c count(t) 1730a 1 1731a 10 1732b 10 1733c 10 1734d 10 1735e 10 1736f 10 1737g 10 1738h 10 1739i 10 1740select sql_big_result c,count(t) from t1 group by c limit 10; 1741c count(t) 1742a 1 1743a 10 1744b 10 1745c 10 1746d 10 1747e 10 1748f 10 1749g 10 1750h 10 1751i 10 1752select t,count(*) from t1 group by t limit 10; 1753t count(*) 1754a 1 1755a 10 1756b 10 1757c 10 1758d 10 1759e 10 1760f 10 1761g 10 1762h 10 1763i 10 1764select t,count(t) from t1 group by t limit 10; 1765t count(t) 1766a 1 1767a 10 1768b 10 1769c 10 1770d 10 1771e 10 1772f 10 1773g 10 1774h 10 1775i 10 1776select sql_big_result t,count(t) from t1 group by t limit 10; 1777t count(t) 1778a 1 1779a 10 1780b 10 1781c 10 1782d 10 1783e 10 1784f 10 1785g 10 1786h 10 1787i 10 1788alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 1789show create table t1; 1790Table Create Table 1791t1 CREATE TABLE `t1` ( 1792 `v` varchar(300) DEFAULT NULL, 1793 `c` char(10) DEFAULT NULL, 1794 `t` text, 1795 KEY `c` (`c`), 1796 KEY `t` (`t`(10)), 1797 KEY `v` (`v`) 1798) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1799select count(*) from t1 where v='a'; 1800count(*) 180110 1802select count(*) from t1 where v='a '; 1803count(*) 180410 1805select count(*) from t1 where v between 'a' and 'a '; 1806count(*) 180710 1808select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1809count(*) 181010 1811select count(*) from t1 where v like 'a%'; 1812count(*) 181311 1814select count(*) from t1 where v like 'a %'; 1815count(*) 18169 1817explain select count(*) from t1 where v='a '; 1818id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18191 SIMPLE t1 NULL ref v v 303 const # 100.00 Using index 1820Warnings: 1821Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ') 1822explain select count(*) from t1 where v like 'a%'; 1823id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18241 SIMPLE t1 NULL range v v 303 NULL # 100.00 Using where; Using index 1825Warnings: 1826Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%') 1827explain select count(*) from t1 where v between 'a' and 'a '; 1828id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18291 SIMPLE t1 NULL ref v v 303 const # 100.00 Using where; Using index 1830Warnings: 1831Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ') 1832explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1833id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18341 SIMPLE t1 NULL ref v v 303 const # 100.00 Using where; Using index 1835Warnings: 1836Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n')) 1837explain select * from t1 where v='a'; 1838id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18391 SIMPLE t1 NULL ref v v 303 const # 100.00 NULL 1840Warnings: 1841Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a') 1842select v,count(*) from t1 group by v limit 10; 1843v count(*) 1844a 1 1845a 10 1846b 10 1847c 10 1848d 10 1849e 10 1850f 10 1851g 10 1852h 10 1853i 10 1854select 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 1866select sql_big_result v,count(t) from t1 group by v limit 10; 1867v count(t) 1868a 1 1869a 10 1870b 10 1871c 10 1872d 10 1873e 10 1874f 10 1875g 10 1876h 10 1877i 10 1878alter table t1 drop key v, add key v (v(30)); 1879show create table t1; 1880Table Create Table 1881t1 CREATE TABLE `t1` ( 1882 `v` varchar(300) DEFAULT NULL, 1883 `c` char(10) DEFAULT NULL, 1884 `t` text, 1885 KEY `c` (`c`), 1886 KEY `t` (`t`(10)), 1887 KEY `v` (`v`(30)) 1888) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1889select count(*) from t1 where v='a'; 1890count(*) 189110 1892select count(*) from t1 where v='a '; 1893count(*) 189410 1895select count(*) from t1 where v between 'a' and 'a '; 1896count(*) 189710 1898select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1899count(*) 190010 1901select count(*) from t1 where v like 'a%'; 1902count(*) 190311 1904select count(*) from t1 where v like 'a %'; 1905count(*) 19069 1907explain select count(*) from t1 where v='a '; 1908id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19091 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where 1910Warnings: 1911Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ') 1912explain select count(*) from t1 where v like 'a%'; 1913id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19141 SIMPLE t1 NULL range v v 33 NULL # 100.00 Using where 1915Warnings: 1916Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%') 1917explain select count(*) from t1 where v between 'a' and 'a '; 1918id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19191 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where 1920Warnings: 1921Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ') 1922explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1923id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19241 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where 1925Warnings: 1926Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n')) 1927explain select * from t1 where v='a'; 1928id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19291 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where 1930Warnings: 1931Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a') 1932select v,count(*) from t1 group by v limit 10; 1933v count(*) 1934a 1 1935a 10 1936b 10 1937c 10 1938d 10 1939e 10 1940f 10 1941g 10 1942h 10 1943i 10 1944select v,count(t) from t1 group by v limit 10; 1945v count(t) 1946a 1 1947a 10 1948b 10 1949c 10 1950d 10 1951e 10 1952f 10 1953g 10 1954h 10 1955i 10 1956select sql_big_result v,count(t) from t1 group by v limit 10; 1957v count(t) 1958a 1 1959a 10 1960b 10 1961c 10 1962d 10 1963e 10 1964f 10 1965g 10 1966h 10 1967i 10 1968alter table t1 modify v varchar(600), drop key v, add key v (v); 1969show create table t1; 1970Table Create Table 1971t1 CREATE TABLE `t1` ( 1972 `v` varchar(600) DEFAULT NULL, 1973 `c` char(10) DEFAULT NULL, 1974 `t` text, 1975 KEY `c` (`c`), 1976 KEY `t` (`t`(10)), 1977 KEY `v` (`v`) 1978) ENGINE=MyISAM DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 1979select v,count(*) from t1 group by v limit 10; 1980v count(*) 1981a 1 1982a 10 1983b 10 1984c 10 1985d 10 1986e 10 1987f 10 1988g 10 1989h 10 1990i 10 1991select v,count(t) from t1 group by v limit 10; 1992v count(t) 1993a 1 1994a 10 1995b 10 1996c 10 1997d 10 1998e 10 1999f 10 2000g 10 2001h 10 2002i 10 2003select sql_big_result v,count(t) from t1 group by v limit 10; 2004v count(t) 2005a 1 2006a 10 2007b 10 2008c 10 2009d 10 2010e 10 2011f 10 2012g 10 2013h 10 2014i 10 2015drop table t1; 2016create table t1 (a char(10), unique (a)); 2017insert into t1 values ('a '); 2018insert into t1 values ('a '); 2019ERROR 23000: Duplicate entry 'a' for key 'a' 2020alter table t1 modify a varchar(10); 2021insert into t1 values ('a '),('a '),('a '),('a '); 2022ERROR 23000: Duplicate entry 'a ' for key 'a' 2023insert into t1 values ('a '); 2024ERROR 23000: Duplicate entry 'a ' for key 'a' 2025insert into t1 values ('a '); 2026ERROR 23000: Duplicate entry 'a ' for key 'a' 2027insert into t1 values ('a '); 2028ERROR 23000: Duplicate entry 'a ' for key 'a' 2029update t1 set a='a ' where a like 'a%'; 2030select concat(a,'.') from t1; 2031concat(a,'.') 2032a . 2033update t1 set a='abc ' where a like 'a '; 2034select concat(a,'.') from t1; 2035concat(a,'.') 2036a . 2037update t1 set a='a ' where a like 'a %'; 2038select concat(a,'.') from t1; 2039concat(a,'.') 2040a . 2041update t1 set a='a ' where a like 'a '; 2042select concat(a,'.') from t1; 2043concat(a,'.') 2044a . 2045drop table t1; 2046create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 2047show create table t1; 2048Table Create Table 2049t1 CREATE TABLE `t1` ( 2050 `v` varchar(10) DEFAULT NULL, 2051 `c` char(10) DEFAULT NULL, 2052 `t` text, 2053 KEY `v` (`v`(5)), 2054 KEY `c` (`c`(5)), 2055 KEY `t` (`t`(5)) 2056) ENGINE=MyISAM DEFAULT CHARSET=latin1 2057drop table t1; 2058create table t1 (v char(10) character set utf8); 2059show create table t1; 2060Table Create Table 2061t1 CREATE TABLE `t1` ( 2062 `v` char(10) CHARACTER SET utf8 DEFAULT NULL 2063) ENGINE=MyISAM DEFAULT CHARSET=latin1 2064drop table t1; 2065create table t1 (v varchar(10), c char(10)) row_format=fixed; 2066show create table t1; 2067Table Create Table 2068t1 CREATE TABLE `t1` ( 2069 `v` varchar(10) DEFAULT NULL, 2070 `c` char(10) DEFAULT NULL 2071) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED 2072insert into t1 values('a','a'),('a ','a '); 2073select concat('*',v,'*',c,'*') from t1; 2074concat('*',v,'*',c,'*') 2075*a*a* 2076*a *a* 2077drop table t1; 2078create table t1 (v varchar(65530), key(v(10))); 2079insert into t1 values(repeat('a',65530)); 2080select length(v) from t1 where v=repeat('a',65530); 2081length(v) 208265530 2083drop table t1; 2084create table t1(a int, b varchar(12), key ba(b, a)); 2085insert into t1 values (1, 'A'), (20, NULL); 2086explain select * from t1 where a=20 and b is null; 2087id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20881 SIMPLE t1 NULL ref ba ba 20 const,const 1 100.00 Using where; Using index 2089Warnings: 2090Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 20) and isnull(`test`.`t1`.`b`)) 2091select * from t1 where a=20 and b is null; 2092a b 209320 NULL 2094drop table t1; 2095SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 2096Warnings: 2097Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 2098create table t1 (v varchar(65530), key(v)); 2099Warnings: 2100Warning 1071 Specified key was too long; max key length is 1000 bytes 2101drop table t1; 2102create table t1 (v varchar(65536)); 2103Warnings: 2104Note 1246 Converting column 'v' from VARCHAR to TEXT 2105show create table t1; 2106Table Create Table 2107t1 CREATE TABLE `t1` ( 2108 `v` mediumtext 2109) ENGINE=MyISAM DEFAULT CHARSET=latin1 2110drop table t1; 2111create table t1 (v varchar(65530) character set utf8); 2112Warnings: 2113Note 1246 Converting column 'v' from VARCHAR to TEXT 2114show create table t1; 2115Table Create Table 2116t1 CREATE TABLE `t1` ( 2117 `v` mediumtext CHARACTER SET utf8 2118) ENGINE=MyISAM DEFAULT CHARSET=latin1 2119drop table t1; 2120SET sql_mode = default; 2121set default_storage_engine=MEMORY; 2122create table t1 (v varchar(16384)) engine=MyISAM; 2123drop table t1; 2124create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM; 2125insert into t1 values ('8', '6'), ('4', '7'); 2126select min(a) from t1; 2127min(a) 21284 2129select min(b) from t1 where a='8'; 2130min(b) 21316 2132drop table t1; 2133CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM; 2134insert into t1 (b) values (1); 2135replace into t1 (b) values (2), (1), (3); 2136select * from t1; 2137a b 21383 1 21392 2 21404 3 2141truncate table t1; 2142insert into t1 (b) values (1); 2143replace into t1 (b) values (2); 2144replace into t1 (b) values (1); 2145replace into t1 (b) values (3); 2146select * from t1; 2147a b 21483 1 21492 2 21504 3 2151drop table t1; 2152create table t1 (rowid int not null auto_increment, val int not null,primary 2153key (rowid), unique(val)) engine=MyISAM; 2154replace into t1 (val) values ('1'),('2'); 2155replace into t1 (val) values ('1'),('2'); 2156insert into t1 (val) values ('1'),('2'); 2157ERROR 23000: Duplicate entry '1' for key 'val' 2158select * from t1; 2159rowid val 21603 1 21614 2 2162drop table t1; 2163CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM; 2164INSERT INTO t1 (GRADE) VALUES (151),(252),(343); 2165SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300; 2166GRADE 2167252 2168SELECT GRADE FROM t1 WHERE GRADE= 151; 2169GRADE 2170151 2171DROP TABLE t1; 2172create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM; 2173create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM; 2174insert into t2 values ('aa','cc'); 2175insert into t1 values ('aa','bb'),('aa','cc'); 2176delete t1 from t1,t2 where f1=f3 and f4='cc'; 2177select * from t1; 2178f1 f2 2179drop table t1,t2; 2180create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2181insert into t1(a) values (1),(2),(3); 2182commit; 2183set autocommit = 0; 2184update t1 set b = 5 where a = 2; 2185commit; 2186create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end | 2187set autocommit = 0; 2188insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100), 2189(11),(21),(31),(41),(51),(61),(71),(81),(91),(101), 2190(12),(22),(32),(42),(52),(62),(72),(82),(92),(102), 2191(13),(23),(33),(43),(53),(63),(73),(83),(93),(103), 2192(14),(24),(34),(44),(54),(64),(74),(84),(94),(104); 2193commit; 2194commit; 2195drop trigger t1t; 2196drop table t1; 2197create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2198create table t2(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2199create table t3(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2200create table t4(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2201create table t5(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM; 2202insert into t1(a) values (1),(2),(3); 2203insert into t2(a) values (1),(2),(3); 2204insert into t3(a) values (1),(2),(3); 2205insert into t4(a) values (1),(2),(3); 2206insert into t3(a) values (5),(7),(8); 2207insert into t4(a) values (5),(7),(8); 2208insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12); 2209create trigger t1t before insert on t1 for each row begin 2210INSERT INTO t2 SET a = NEW.a; 2211end | 2212create trigger t2t before insert on t2 for each row begin 2213DELETE FROM t3 WHERE a = NEW.a; 2214end | 2215create trigger t3t before delete on t3 for each row begin 2216UPDATE t4 SET b = b + 1 WHERE a = OLD.a; 2217end | 2218create trigger t4t before update on t4 for each row begin 2219UPDATE t5 SET b = b + 1 where a = NEW.a; 2220end | 2221commit; 2222set autocommit = 0; 2223update t1 set b = b + 5 where a = 1; 2224update t2 set b = b + 5 where a = 1; 2225update t3 set b = b + 5 where a = 1; 2226update t4 set b = b + 5 where a = 1; 2227insert into t5(a) values(20); 2228commit; 2229set autocommit = 0; 2230insert into t1(a) values(7); 2231insert into t2(a) values(8); 2232delete from t2 where a = 3; 2233update t4 set b = b + 1 where a = 3; 2234commit; 2235drop trigger t1t; 2236drop trigger t2t; 2237drop trigger t3t; 2238drop trigger t4t; 2239drop table t1, t2, t3, t4, t5; 2240create table t1(a date) engine=MyISAM; 2241create table t2(a date, key(a)) engine=MyISAM; 2242insert into t1 values('2005-10-01'); 2243insert into t2 values('2005-10-01'); 2244select * from t1, t2 2245where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; 2246a a 22472005-10-01 2005-10-01 2248drop table t1, t2; 2249create table t1 (id int not null, f_id int not null, f int not null, 2250primary key(f_id, id)) engine=MyISAM; 2251create table t2 (id int not null,s_id int not null,s varchar(200), 2252primary key(id)) engine=MyISAM; 2253INSERT INTO t1 VALUES (8, 1, 3); 2254INSERT INTO t1 VALUES (1, 2, 1); 2255INSERT INTO t2 VALUES (1, 0, ''); 2256INSERT INTO t2 VALUES (8, 1, ''); 2257commit; 2258DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) 2259WHERE mm.id IS NULL; 2260select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) 2261where mm.id is null lock in share mode; 2262id f_id f 2263drop table t1,t2; 2264create table t1(a int not null, b int, primary key(a)) engine=MyISAM; 2265insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 2266commit; 2267set autocommit = 0; 2268SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2269update t1 set b = 5 where b = 1; 2270set autocommit = 0; 2271SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2272select * from t1 where a = 7 and b = 3 for update; 2273a b 22747 3 2275commit; 2276commit; 2277drop table t1; 2278CREATE TABLE t1 ( a int ) ENGINE=MyISAM; 2279BEGIN; 2280INSERT INTO t1 VALUES (1); 2281OPTIMIZE TABLE t1; 2282Table Op Msg_type Msg_text 2283test.t1 optimize status OK 2284DROP TABLE t1; 2285