drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; create 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=bdb; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; id code name 1 1 Tim 2 1 Monty 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt update ignore t1 set id = 8, name = 'Sinisa' where id < 3; select id, code, name from t1 order by id; id code name 2 1 Monty 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt 8 1 Sinisa update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; select id, code, name from t1 order by id; id code name 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt 8 1 Sinisa 12 1 Ralph drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4) DEFAULT '0' NOT NULL, PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) ) engine=bdb; INSERT 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); update t1 set parent_id=parent_id+100; select * from t1 where parent_id=102; id parent_id level 8 102 2 9 102 2 15 102 2 update t1 set id=id+1000; update t1 set id=1024 where id=1009; ERROR 23000: Duplicate entry '1024' for key 'PRIMARY' select * from t1; id parent_id level 1001 100 0 1002 101 1 1003 101 1 1004 101 1 1005 101 1 1006 101 1 1007 101 1 1008 102 2 1009 102 2 1015 102 2 1016 103 2 1017 103 2 1018 103 2 1019 103 2 1020 103 2 1021 104 2 1022 104 2 1024 104 2 1025 105 2 1026 105 2 1027 105 2 1028 105 2 1029 105 2 1030 105 2 1031 106 2 1032 106 2 1033 106 2 1034 106 2 1035 106 2 1036 107 2 1037 107 2 1038 107 2 1040 107 2 1157 100 0 1179 105 2 1183 104 2 1193 105 2 1202 107 2 1203 107 2 update ignore t1 set id=id+1; select * from t1; id parent_id level 1001 100 0 1002 101 1 1003 101 1 1004 101 1 1005 101 1 1006 101 1 1007 101 1 1008 102 2 1010 102 2 1015 102 2 1016 103 2 1017 103 2 1018 103 2 1019 103 2 1020 103 2 1021 104 2 1023 104 2 1024 104 2 1025 105 2 1026 105 2 1027 105 2 1028 105 2 1029 105 2 1030 105 2 1031 106 2 1032 106 2 1033 106 2 1034 106 2 1035 106 2 1036 107 2 1037 107 2 1039 107 2 1041 107 2 1158 100 0 1180 105 2 1184 104 2 1194 105 2 1202 107 2 1204 107 2 update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102 order by parent_id,id; id parent_id level 1008 102 2 1010 102 2 1015 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref level level 1 const X Using index explain select level,id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref level level 1 const X Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref level level 1 const X select level,id from t1 where level=1; level id 1 1002 1 1003 1 1004 1 1005 1 1006 1 1007 select level,id,parent_id from t1 where level=1; level id parent_id 1 1002 101 1 1003 101 1 1004 101 1 1005 101 1 1006 101 1 1007 101 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 id A 39 NULL NULL BTREE t1 1 parent_id 1 parent_id A 9 NULL NULL BTREE t1 1 level 1 level A 3 NULL NULL BTREE drop table t1; CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, benutzer_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (gesuchnr,benutzer_id) ) engine=BDB; replace into t1 (gesuchnr,benutzer_id) values (2,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); select * from t1; gesuchnr benutzer_id 1 1 2 1 drop table t1; create table t1 (id int not null primary key, x int not null, key (x)) engine=bdb; insert into t1 (id, x) values (1, 1); replace into t1 (id, x) values (1, 2); select * from t1; id x 1 2 drop table t1; create table t1 (a int) engine=bdb; insert into t1 values (1), (2); optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK delete from t1 where a = 1; select * from t1; a 2 check table t1; Table Op Msg_type Msg_text test.t1 check note The storage engine for the table doesn't support check drop table t1; create table t1 (a int,b varchar(20)) engine=bdb; insert into t1 values (1,""), (2,"testing"); delete from t1 where a = 1; select * from t1; a b 2 testing create index skr on t1 (a); insert into t1 values (3,""), (4,"testing"); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 skr 1 a A 3 NULL NULL YES BTREE drop table t1; create table t1 (a int,b varchar(20),key(a)) engine=bdb; insert into t1 values (1,""), (2,"testing"); select * from t1 where a = 1; a b 1 drop table t1; create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) engine=BDB; insert into t1 values ("a",1),("b",2),("a",2),("c",1); insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); insert into t1 (a) values ("a"),("b"),("c"),("d"); insert into t1 (a) values ('k'),('d'); insert into t1 (a) values ("a"); insert into t1 values ("d",last_insert_id()); select * from t1; a b a 1 a 2 a 3 a 4 a 5 b 2 b 3 b 4 c 1 c 2 c 3 d 1 d 2 d 5 e 1 k 1 flush tables; select count(*) from t1; count(*) 16 drop table t1; create table t1 (n int not null primary key) engine=bdb; set autocommit=0; insert into t1 values (4); rollback; select n, "after rollback" from t1; n after rollback insert into t1 values (4); commit; select n, "after commit" from t1; n after commit 4 after commit commit; insert into t1 values (5); insert into t1 values (4); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' commit; select n, "after commit" from t1; n after commit 4 after commit 5 after commit set autocommit=1; insert into t1 values (6); insert into t1 values (4); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' select n from t1; n 4 5 6 set autocommit=0; begin; savepoint `my_savepoint`; insert into t1 values (7); savepoint `savept2`; insert into t1 values (3); select n from t1; n 3 4 5 6 7 savepoint savept3; rollback to savepoint savept2; rollback to savepoint savept3; ERROR 42000: SAVEPOINT savept3 does not exist rollback to savepoint savept2; release savepoint `my_savepoint`; select n from t1; n 4 5 6 7 rollback to savepoint `my_savepoint`; ERROR 42000: SAVEPOINT my_savepoint does not exist rollback to savepoint savept2; ERROR 42000: SAVEPOINT savept2 does not exist insert into t1 values (8); savepoint sv; commit; savepoint sv; set autocommit=1; rollback; drop table t1; create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=BDB; begin; insert into t1 values(1,'hamdouni'); select id as afterbegin_id,nom as afterbegin_nom from t1; afterbegin_id afterbegin_nom 1 hamdouni rollback; select id as afterrollback_id,nom as afterrollback_nom from t1; afterrollback_id afterrollback_nom set autocommit=0; insert into t1 values(2,'mysql'); select id as afterautocommit0_id,nom as afterautocommit0_nom from t1; afterautocommit0_id afterautocommit0_nom 2 mysql rollback; select id as afterrollback_id,nom as afterrollback_nom from t1; afterrollback_id afterrollback_nom set autocommit=1; drop table t1; CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=bdb; insert into t1 values ('pippo', 12); insert into t1 values ('pippo', 12); ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY' delete from t1; delete from t1 where id = 'pippo'; select * from t1; id val insert into t1 values ('pippo', 12); set autocommit=0; delete from t1; rollback; select * from t1; id val pippo 12 delete from t1; commit; select * from t1; id val drop table t1; set autocommit=1; CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=BDB; INSERT INTO t1 VALUES (1, 'Jochen'); select * from t1; ID NAME 1 Jochen drop table t1; CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=BDB; set autocommit=0; INSERT INTO t1 SET _userid='marc@anyware.co.uk'; COMMIT; SELECT * FROM t1; _userid marc@anyware.co.uk SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk'; _userid marc@anyware.co.uk drop table t1; set autocommit=1; CREATE TABLE t1 ( user_id int(10) DEFAULT '0' NOT NULL, name varchar(100), phone varchar(100), ref_email varchar(100) DEFAULT '' NOT NULL, detail varchar(200), PRIMARY KEY (user_id,ref_email) )engine=bdb; INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); select * from t1 where user_id=10292; user_id name phone ref_email detail 10292 sanjeev 29153373 sansh777@hotmail.com xxx 10292 shirish 2333604 shirish@yahoo.com ddsds 10292 sonali 323232 sonali@bolly.com filmstar INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); select * from t1 where user_id=10292; user_id name phone ref_email detail 10292 sanjeev 29153373 sansh777@hotmail.com xxx 10292 shirish 2333604 shirish@yahoo.com ddsds 10292 sonali 323232 sonali@bolly.com filmstar select * from t1 where user_id>=10292; user_id name phone ref_email detail 10292 sanjeev 29153373 sansh777@hotmail.com xxx 10292 shirish 2333604 shirish@yahoo.com ddsds 10292 sonali 323232 sonali@bolly.com filmstar 10293 shirish 2333604 shirish@yahoo.com ddsds select * from t1 where user_id>10292; user_id name phone ref_email detail 10293 shirish 2333604 shirish@yahoo.com ddsds select * from t1 where user_id<10292; user_id name phone ref_email detail 10291 sanjeev 29153373 sansh777@hotmail.com xxx drop table t1; CREATE TABLE t1 (a int not null, b int not null,c int not null, key(a),primary key(a,b), unique(c),key(a),unique(b)); show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A NULL NULL NULL BTREE t1 0 PRIMARY 2 b A 0 NULL NULL BTREE t1 0 c 1 c A 0 NULL NULL BTREE t1 0 b 1 b A 0 NULL NULL BTREE t1 1 a 1 a A NULL NULL NULL BTREE t1 1 a_2 1 a A NULL NULL NULL BTREE drop table t1; create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); alter table t1 engine=BDB; insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); select * from t1; col1 col2 1 1 2 3 3 4 4 4 5 2 update t1 set col2='7' where col1='4'; select * from t1; col1 col2 1 1 2 3 3 4 4 7 5 2 alter table t1 add co3 int not null; select * from t1; col1 col2 co3 1 1 0 2 3 0 3 4 0 4 7 0 5 2 0 update t1 set col2='9' where col1='2'; select * from t1; col1 col2 co3 1 1 0 2 9 0 3 4 0 4 7 0 5 2 0 drop table t1; create table t1 (a int not null , b int, primary key (a)) engine = BDB; create table t2 (a int not null , b int, primary key (a)) engine = myisam; insert into t1 VALUES (1,3) , (2,3), (3,3); select * from t1; a b 1 3 2 3 3 3 insert into t2 select * from t1; select * from t2; a b 1 3 2 3 3 3 delete from t1 where b = 3; select * from t1; a b insert into t1 select * from t2; select * from t1; a b 1 3 2 3 3 3 select * from t2; a b 1 3 2 3 3 3 drop table t1,t2; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, ggid varchar(32) binary DEFAULT '' NOT NULL, email varchar(64) DEFAULT '' NOT NULL, passwd varchar(32) binary DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE ggid (ggid) ) ENGINE=BDB; insert into t1 (ggid,passwd) values ('test1','xxx'); insert into t1 (ggid,passwd) values ('test2','yyy'); insert into t1 (ggid,passwd) values ('test2','this will fail'); ERROR 23000: Duplicate entry 'test2' for key 'ggid' insert into t1 (ggid,id) values ('this will fail',1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' select * from t1 where ggid='test1'; id ggid email passwd 1 test1 xxx select * from t1 where passwd='xxx'; id ggid email passwd 1 test1 xxx select * from t1 where id=2; id ggid email passwd 2 test2 yyy replace into t1 (ggid,id) values ('this will work',1); replace into t1 (ggid,passwd) values ('test2','this will work'); update t1 set id=100,ggid='test2' where id=1; ERROR 23000: Duplicate entry 'test2' for key 'ggid' select * from t1; id ggid email passwd 1 this will work 3 test2 this will work select * from t1 where id=1; id ggid email passwd 1 this will work select * from t1 where id=999; id ggid email passwd drop table t1; CREATE TABLE t1 ( user_name varchar(12), password text, subscribed char(1), user_id int(11) DEFAULT '0' NOT NULL, quota bigint(20), weight double, access_date date, access_time time, approved datetime, dummy_primary_key int(11) NOT NULL auto_increment, PRIMARY KEY (dummy_primary_key) ) ENGINE=BDB; INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1 user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2 user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3 user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4 user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5 drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4) DEFAULT '0' NOT NULL, KEY (id), KEY parent_id (parent_id), KEY level (level) ) engine=bdb; INSERT 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); INSERT INTO t1 values (179,5,2); update t1 set parent_id=parent_id+100; select * from t1 where parent_id=102; id parent_id level 8 102 2 9 102 2 15 102 2 update t1 set id=id+1000; update t1 set id=1024 where id=1009; select * from t1; id parent_id level 1001 100 0 1003 101 1 1004 101 1 1008 102 2 1024 102 2 1017 103 2 1022 104 2 1024 104 2 1028 105 2 1029 105 2 1030 105 2 1031 106 2 1032 106 2 1033 106 2 1203 107 2 1202 107 2 1020 103 2 1157 100 0 1193 105 2 1040 107 2 1002 101 1 1015 102 2 1006 101 1 1034 106 2 1035 106 2 1016 103 2 1007 101 1 1036 107 2 1018 103 2 1026 105 2 1027 105 2 1183 104 2 1038 107 2 1025 105 2 1037 107 2 1021 104 2 1019 103 2 1005 101 1 1179 105 2 update ignore t1 set id=id+1; select * from t1; id parent_id level 1002 100 0 1004 101 1 1005 101 1 1009 102 2 1025 102 2 1018 103 2 1023 104 2 1025 104 2 1029 105 2 1030 105 2 1031 105 2 1032 106 2 1033 106 2 1034 106 2 1204 107 2 1203 107 2 1021 103 2 1158 100 0 1194 105 2 1041 107 2 1003 101 1 1016 102 2 1007 101 1 1035 106 2 1036 106 2 1017 103 2 1008 101 1 1037 107 2 1019 103 2 1027 105 2 1028 105 2 1184 104 2 1039 107 2 1026 105 2 1038 107 2 1022 104 2 1020 103 2 1006 101 1 1180 105 2 update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; id parent_id level 1009 102 2 1025 102 2 1016 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref level level 1 const X Using index select level,id from t1 where level=1; level id 1 1004 1 1005 1 1003 1 1007 1 1008 1 1006 select level,id,parent_id from t1 where level=1; level id parent_id 1 1004 101 1 1005 101 1 1003 101 1 1007 101 1 1008 101 1 1006 101 select level,id from t1 where level=1 order by id; level id 1 1003 1 1004 1 1005 1 1006 1 1007 1 1008 delete from t1 where level=1; select * from t1; id parent_id level 1002 100 0 1009 102 2 1025 102 2 1018 103 2 1023 104 2 1025 104 2 1029 105 2 1030 105 2 1031 105 2 1032 106 2 1033 106 2 1034 106 2 1204 107 2 1203 107 2 1021 103 2 1158 100 0 1194 105 2 1041 107 2 1016 102 2 1035 106 2 1036 106 2 1017 103 2 1037 107 2 1019 103 2 1027 105 2 1028 105 2 1184 104 2 1039 107 2 1026 105 2 1038 107 2 1022 104 2 1020 103 2 1180 105 2 drop table t1; CREATE TABLE t1 ( sca_code char(6) NOT NULL, cat_code char(6) NOT NULL, sca_desc varchar(50), lan_code char(2) NOT NULL, sca_pic varchar(100), sca_sdesc varchar(50), sca_sch_desc varchar(16), PRIMARY KEY (sca_code, cat_code, lan_code), INDEX sca_pic (sca_pic) ) engine = bdb ; INSERT 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'); select count(*) from t1 where sca_code = 'PD'; count(*) 1 select count(*) from t1 where sca_code <= 'PD'; count(*) 1 select count(*) from t1 where sca_pic is null; count(*) 2 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 select count(*) from t1 where cat_code='E'; count(*) 0 alter table t1 drop index sca_pic, add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 select count(*) from t1 where sca_pic >= 'n'; count(*) 1 select sca_pic from t1 where sca_pic is null; sca_pic NULL NULL update t1 set sca_pic="test" where sca_pic is null; delete from t1 where sca_code='pd'; drop table t1; set @a:=now(); CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=bdb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; a 1 2 3 select a from t1 natural join t1 as t2 where b >= @a order by a; a 1 2 3 update t1 set a=5 where a=1; select a from t1; a 2 3 5 drop table t1; flush logs; create table t1 (b blob, i int, key (b(100)), key (i), key (i, b(20))) engine=bdb; insert into t1 values ('this is a blob', 1), (null, -1), (null, null),("",1),("",2),("",3); select b from t1 where b = 'this is a blob'; b this is a blob select * from t1 where b like 't%'; b i this is a blob 1 select b, i from t1 where b is not null; b i this is a blob 1 1 2 3 select * from t1 where b is null and i > 0; b i select * from t1 where i is NULL; b i NULL NULL update t1 set b='updated' where i=1; select * from t1; b i updated 1 NULL -1 NULL NULL updated 1 2 3 drop table t1; create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=bdb; insert into t1 values("hello",1),("world",2); select * from t1 order by b desc; a b world 2 hello 1 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 2 NULL NULL BTREE drop table t1; create table t1 (i int, j int )ENGINE=BDB; insert into t1 values (1,2); select * from t1 where i=1 and j=2; i j 1 2 create index ax1 on t1 (i,j); select * from t1 where i=1 and j=2; i j 1 2 drop table t1; create table t1 ( branch_id int auto_increment primary key, branch_name varchar(255) not null, branch_active int not null default 1, unique branch_name(branch_name), index branch_active(branch_active) ) engine=bdb; create table t2 ( target_id int auto_increment primary key, target_name varchar(255) not null, target_active int not null default 1, unique target_name(target_name), index target_active(target_active) ) engine=bdb; create table t3 ( platform_id int auto_increment primary key, platform_name varchar(255) not null, platform_active int not null default 1, unique platform_name(platform_name), index platform_active(platform_active) ) engine=bdb; create table t4 ( product_id int auto_increment primary key, product_name varchar(255) not null, version_file varchar(255) not null, product_active int not null default 1, unique product_name(product_name), index product_active(product_active) ) engine=bdb; create table t5 ( product_file_id int auto_increment primary key, product_id int not null, file_name varchar(255) not null, /* cvs module used to find the file version */ module_name varchar(255) not null, /* flag whether the file is still included in the product */ file_included int not null default 1, unique product_file(product_id,file_name), index file_included(file_included) ) engine=bdb; create table t6 ( file_platform_id int auto_increment primary key, product_file_id int not null, platform_id int not null, branch_id int not null, /* filename in the build system */ build_filename varchar(255) not null, /* default filename in the build archive */ archive_filename varchar(255) not null, unique file_platform(product_file_id,platform_id,branch_id) ) engine=bdb; create table t8 ( archive_id int auto_increment primary key, branch_id int not null, target_id int not null, platform_id int not null, product_id int not null, status_id int not null default 1, unique archive(branch_id,target_id,platform_id,product_id), index status_id(status_id) ) engine=bdb; create table t7 ( build_id int auto_increment primary key, branch_id int not null, target_id int not null, build_number int not null, build_date date not null, /* build system tag, e.g. 'rmanight-022301-1779' */ build_tag varchar(255) not null, /* path relative to the build archive root, e.g. 'current' */ build_path text not null, unique build(branch_id,target_id,build_number) ) engine=bdb; insert into t1 (branch_name) values ('RealMedia'); insert into t1 (branch_name) values ('RP8REV'); insert into t1 (branch_name) values ('SERVER_8_0_GOLD'); insert into t2 (target_name) values ('rmanight'); insert into t2 (target_name) values ('playerall'); insert into t2 (target_name) values ('servproxyall'); insert into t3 (platform_name) values ('linux-2.0-libc6-i386'); insert into t3 (platform_name) values ('win32-i386'); insert into t4 (product_name, version_file) values ('realserver', 'servinst'); insert into t4 (product_name, version_file) values ('realproxy', 'prxyinst'); insert into t4 (product_name, version_file) values ('realplayer', 'playinst'); insert into t4 (product_name, version_file) values ('plusplayer', 'plusinst'); create temporary table tmp1 select branch_id, target_id, platform_id, product_id from t1, t2, t3, t4 ; create temporary table tmp2 select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id from tmp1 left join t8 using (branch_id,target_id,platform_id,product_id) where t8.archive_id is null ; insert into t8 (branch_id, target_id, platform_id, product_id, status_id) select branch_id, target_id, platform_id, product_id, 1 from tmp2 ; drop table tmp1 ; drop table tmp2 ; insert into t5 (product_id, file_name, module_name) values (1, 'servinst', 'server'); insert into t5 (product_id, file_name, module_name) values (2, 'prxyinst', 'server'); insert into t5 (product_id, file_name, module_name) values (3, 'playinst', 'rpapp'); insert into t5 (product_id, file_name, module_name) values (4, 'plusinst', 'rpapp'); insert into t6 (product_file_id,platform_id,branch_id,build_filename,archive_filename) values (1, 2, 3, 'servinst.exe', 'win32-servinst.exe'); insert into t6 (product_file_id,platform_id,branch_id,build_filename,archive_filename) values (1, 1, 3, 'v80_linux-2.0-libc6-i386_servinst.bin', 'linux2-servinst.exe'); insert into t6 (product_file_id,platform_id,branch_id,build_filename,archive_filename) values (3, 2, 2, 'playinst.exe', 'win32-playinst.exe'); insert into t6 (product_file_id,platform_id,branch_id,build_filename,archive_filename) values (4, 2, 2, 'playinst.exe', 'win32-playinst.exe'); insert into t7 (branch_id,target_id,build_number,build_tag,build_date,build_path) values (2, 2, 1071, 'playerall-022101-1071', '2001-02-21', 'current'); insert into t7 (branch_id,target_id,build_number,build_tag,build_date,build_path) values (2, 2, 1072, 'playerall-022201-1072', '2001-02-22', 'current'); insert into t7 (branch_id,target_id,build_number,build_tag,build_date,build_path) values (3, 3, 388, 'servproxyall-022201-388', '2001-02-22', 'current'); insert into t7 (branch_id,target_id,build_number,build_tag,build_date,build_path) values (3, 3, 389, 'servproxyall-022301-389', '2001-02-23', 'current'); insert into t7 (branch_id,target_id,build_number,build_tag,build_date,build_path) values (4, 4, 100, 'foo target-010101-100', '2001-01-01', 'current'); update t8 set status_id=2 where branch_id=2 and target_id=2 and platform_id=2 and product_id=1; select t7.build_path from t1, t7, t2, t3, t4, t5, t6 where t7.branch_id = t1.branch_id and t7.target_id = t2.target_id and t5.product_id = t4.product_id and t6.product_file_id = t5.product_file_id and t6.platform_id = t3.platform_id and t6.branch_id = t6.branch_id and t7.build_id = 1 and t4.product_id = 3 and t5.file_name = 'playinst' and t3.platform_id = 2; build_path current drop table t1, t2, t3, t4, t5, t6, t7, t8; CREATE TABLE t1 ( a tinytext NOT NULL, b tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (a(32),b) ) ENGINE=BDB; INSERT INTO t1 VALUES ('a',1),('a',2); SELECT * FROM t1 WHERE a='a' AND b=2; a b a 2 SELECT * FROM t1 WHERE a='a' AND b in (2); a b a 2 SELECT * FROM t1 WHERE a='a' AND b in (1,2); a b a 1 a 2 drop table t1; CREATE TABLE t1 ( a int3 unsigned NOT NULL, b int1 unsigned NOT NULL, UNIQUE (a, b) ) ENGINE = BDB; INSERT INTO t1 VALUES (1, 1); SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; MIN(B) MAX(b) 1 1 drop table t1; create 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=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; insert into t1 values (99,1,2,'D'),(1,1,2,'D'); ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' select id from t1; id 0 1 2 select id from t1; id 0 1 2 UNLOCK TABLES; DROP TABLE t1; create 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=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; begin; insert into t1 values (99,1,2,'D'),(1,1,2,'D'); ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' select id from t1; id 0 1 2 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); commit; select id,id3 from t1; id id3 0 0 1 1 2 2 100 2 UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY (SYAIN_NO,KINMU_DATE)) ENGINE=BerkeleyDB; CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY (SYAIN_NO,STR_DATE) ) ENGINE=BerkeleyDB; select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; KINMU_DATE select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; KINMU_DATE DROP TABLE t1,t2; create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) engine=bdb; insert into t1 values (1,1), (1,2); select * from t1 where a = 1; a b 1 1 1 2 select t1.*, t2.* from t1, t1 t2 where t1.a = t2.a and t2.a = 1; a b a b 1 1 1 1 1 1 1 2 1 2 1 1 1 2 1 2 select * from t1 where a = 1; a b 1 1 1 2 drop table t1; create 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=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'); create table t2 (id int NOT NULL,primary key (id)) engine=bdb; LOCK TABLES t1 WRITE, t2 WRITE; insert into t2 values(1); SELECT t1.* FROM t1 WHERE id IN (1); id id2 id3 dummy1 SELECT t1.* FROM t2 left outer join t1 on (t1.id=t2.id); id id2 id3 dummy1 NULL NULL NULL NULL delete from t1 where id3 >= 0 and id3 <= 0; drop table t1,t2; CREATE TABLE t1 (i varchar(48) NOT NULL default '', p varchar(255) default NULL,s varchar(48) NOT NULL default '', PRIMARY KEY (i), UNIQUE(p,s)) ENGINE=BDB; INSERT INTO t1 VALUES ('00000000-e6c4ddeaa6-003b8-83458387','programs/xxxxxxxx.wmv','00000000-e6c4ddeb32-003bc-83458387'); SELECT * FROM t1 WHERE p='programs/xxxxxxxx.wmv'; i p s 00000000-e6c4ddeaa6-003b8-83458387 programs/xxxxxxxx.wmv 00000000-e6c4ddeb32-003bc-83458387 drop table t1; CREATE TABLE t1 ( STR_DATE varchar(8) NOT NULL default '',INFO_NOTE varchar(200) default NULL,PRIMARY KEY (STR_DATE) ) ENGINE=BerkeleyDB; select INFO_NOTE from t1 where STR_DATE = '20010610'; INFO_NOTE select INFO_NOTE from t1 where STR_DATE < '20010610'; INFO_NOTE select INFO_NOTE from t1 where STR_DATE > '20010610'; INFO_NOTE drop table t1; create table t1 (a int not null, b int, primary key (a)) engine =bdb; create table t2 (a int not null, b int, primary key (a)) engine =bdb; insert into t1 values (2, 3),(1, 7),(10, 7); insert into t2 values (2, 3),(1, 7),(10, 7); select * from t1; a b 1 7 2 3 10 7 select * from t2; a b 1 7 2 3 10 7 delete t1, t2 from t1, t2 where t1.a = t2.a; select * from t1; a b select * from t2; a b select * from t2; a b drop table t1,t2; create table t1 (x int not null, index(x)) engine=bdb; insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); select * from t1 where x <= 10 and x >= 7; x 7 8 9 10 select * from t1 where x <= 10 and x >= 7 order by x; x 7 8 9 10 select * from t1 where x <= 10 and x >= 7 order by x desc; x 10 9 8 7 select * from t1 where x <= 8 and x >= 5 order by x desc; x 8 7 6 5 select * from t1 where x < 8 and x > 5 order by x desc; x 7 6 drop table t1; create table t1 ( c char(8) not null ) engine=bdb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); alter table t1 add b char(8) not null; alter table t1 add a char(8) not null; alter table t1 add primary key (a,b,c); update t1 set a=c, b=c; create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=bdb; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; create table t1 (a char(10), key(a), b int not null, key(b)) engine=bdb; insert into t1 values ('a',1),('A',2); explain select a from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 select a from t1; a a A explain select b from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL b 4 NULL 2 Using index select b from t1; b 1 2 alter table t1 modify a char(10) binary; explain select a from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 11 NULL 2 Using index select a from t1; a A a drop table t1; create table t1( pk1 text not null, pk2 text not null, pk3 char(4), key1 int, key2 int, primary key(pk1(4), pk2(4), pk3), key(key1), key(key2) ) engine=bdb; insert into t1 values (concat('aaa-', repeat('A', 4000)), concat('eee-', repeat('e', 4000)), 'a++a', 1, 1); insert into t1 values (concat('bbb-', repeat('B', 4000)), concat('ggg-', repeat('G', 4000)), 'b++b', 1, 1); select substring(pk1, 1, 4), substring(pk1, 4001), substring(pk2, 1, 4), substring(pk2, 4001), pk3, key1, key2 from t1 force index(key1, key2) where key1 < 3 or key2 < 3; substring(pk1, 1, 4) substring(pk1, 4001) substring(pk2, 1, 4) substring(pk2, 4001) pk3 key1 key2 aaa- AAAA eee- eeee a++a 1 1 bbb- BBBB ggg- GGGG b++b 1 1 drop table t1; create table t1 ( pk1 varchar(8) not null default '', pk2 varchar(4) not null default '', key1 int(11) default null, key2 int(11) default null, primary key (pk1,pk2), key key1 (key1), key key2 (key2)) engine=bdb; insert into t1 values ('','empt',2,2), ('a','a--a',2,2), ('bb','b--b',2,2), ('ccc','c--c',2,2), ('dddd','d--d',2,2); select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; pk1 pk2 key1 key2 empt 2 2 a a--a 2 2 bb b--b 2 2 ccc c--c 2 2 dddd d--d 2 2 drop table t1; set autocommit=0; create table t1(b varchar(30)) engine=bdb; insert into t1 values ('one'); commit; select b FROM t1 outer_table where exists (select 'two' from t1 where 'two' = outer_table.b); b drop table t1; set autocommit=1; create table t1(a int primary key, b varchar(30)) engine=bdb; insert into t1 values (1,'one'), (2,'two'), (3,'three'), (4,'four'); create table t2 like t1; insert t2 select * from t1; select a from t1 where a in (select a from t2); a 1 2 3 4 delete from t2; insert into t2 (a, b) select a, b from t1 where (a, b) in (select a, b from t1); select * from t2; a b 1 one 2 two 3 three 4 four drop table t1, t2; create table t1 (a int, b varchar(30), primary key(a)) engine = bdb; insert into t1 values (1,'one'); commit; truncate t1; select * from t1; a b drop table t1; SET NAMES utf8; create table t1 (a varchar(255) character set utf8) engine=bdb; set @a:= convert(repeat(_latin1 0xFF, 255) using utf8); insert into t1 values (@a); select a, length(a), char_length(a) from t1; a length(a) char_length(a) ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 510 255 drop table t1; SET NAMES latin1; CREATE TABLE t1 ( id int unsigned NOT NULL auto_increment, list_id smallint unsigned NOT NULL, term TEXT NOT NULL, PRIMARY KEY(id), INDEX(list_id, term(4)) ) ENGINE=BDB CHARSET=utf8; INSERT INTO t1 SET list_id = 1, term = "letterc"; INSERT INTO t1 SET list_id = 1, term = "letterb"; INSERT INTO t1 SET list_id = 1, term = "lettera"; INSERT INTO t1 SET list_id = 1, term = "letterd"; SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); id 1 SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); id 2 SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); id 3 SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); id 4 DROP TABLE t1; create table t1 (a int, key(a)) engine=bdb; create table t2 (b int, key(b)) engine=bdb; insert into t1 values (1),(1),(2),(3),(4); insert into t2 values (1),(5),(6),(7); delete from t1 where (a in (select b from t2)); select count(*) from t1; count(*) 3 insert into t1 set a=(select b from t2); ERROR 21000: Subquery returns more than 1 row select count(*) from t1; count(*) 3 update t1 set a = a + 1 where (a in (select b from t2)); select count(*) from t1; count(*) 3 drop table t1, t2; End of 4.1 tests create temporary table t1 (a int, primary key(a)) engine=bdb; select * from t1; a alter table t1 add b int; select * from t1; a b drop table t1; set storage_engine=bdb; drop table if exists t1,t2,t3; --- Testing varchar --- --- Testing varchar --- create table t1 (v varchar(10), c char(10), t text); insert into t1 values('+ ', '+ ', '+ '); set @a=repeat(' ',20); insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); Warnings: Note 1265 Data truncated for column 'v' at row 1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+ *+*+ * *+ *+*+ * show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 create table t2 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 create table t3 select * from t1; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 alter table t1 modify c varchar(10); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `t` text ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 alter table t1 modify v char(10); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` char(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `t` text ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 alter table t1 modify t varchar(10); Warnings: Note 1265 Data truncated for column 't' at row 2 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` char(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `t` varchar(10) DEFAULT NULL ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+*+*+ * *+*+*+ * drop table t1,t2,t3; create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `v` (`v`), KEY `c` (`c`), KEY `t` (`t`(10)) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 select count(*) from t1; count(*) 270 insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); select count(*) from t1 where v='a'; count(*) 10 select count(*) from t1 where c='a'; count(*) 10 select count(*) from t1 where t='a'; count(*) 10 select count(*) from t1 where v='a '; count(*) 10 select count(*) from t1 where c='a '; count(*) 10 select count(*) from t1 where t='a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; count(*) 10 select count(*) from t1 where v like 'a%'; count(*) 11 select count(*) from t1 where c like 'a%'; count(*) 11 select count(*) from t1 where t like 'a%'; count(*) 11 select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where explain select count(*) from t1 where c='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range t t 13 NULL # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where explain select count(*) from t1 where v between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where alter table t1 add unique(v); ERROR 23000: Duplicate entry '{ ' for key 'v_2' alter table t1 add key(v); select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; qq *a*a*a* *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v,v_2 # 13 const # Using where select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(c) from t1 group by v limit 10; v count(c) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(c) from t1 group by v limit 10; v count(c) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select c,count(*) from t1 group by c limit 10; c count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select c,count(t) from t1 group by c limit 10; c count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result c,count(t) from t1 group by c limit 10; c count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select t,count(*) from t1 group by t limit 10; t count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select t,count(t) from t1 group by t limit 10; t count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result t,count(t) from t1 group by t limit 10; t count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(300) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `c` (`c`), KEY `t` (`t`(10)), KEY `v` (`v`) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 select count(*) from t1 where v='a'; count(*) 10 select count(*) from t1 where v='a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; count(*) 10 select count(*) from t1 where v like 'a%'; count(*) 11 select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 303 NULL # Using where explain select count(*) from t1 where v between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 alter table t1 drop key v, add key v (v(30)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(300) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `c` (`c`), KEY `t` (`t`(10)), KEY `v` (`v`(30)) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 select count(*) from t1 where v='a'; count(*) 10 select count(*) from t1 where v='a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; count(*) 10 select count(*) from t1 where v like 'a%'; count(*) 11 select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 33 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 33 NULL # Using where explain select count(*) from t1 where v between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 33 const # Using where explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 33 const # Using where explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 33 const # Using where select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 alter table t1 modify v varchar(600), drop key v, add key v (v); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(600) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `c` (`c`), KEY `t` (`t`(10)), KEY `v` (`v`) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 drop table t1; create table t1 (a char(10), unique (a)); insert into t1 values ('a '); insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a' for key 'a' alter table t1 modify a varchar(10); insert into t1 values ('a '),('a '),('a '),('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' update t1 set a='a ' where a like 'a%'; select concat(a,'.') from t1; concat(a,'.') a . update t1 set a='abc ' where a like 'a '; select concat(a,'.') from t1; concat(a,'.') a . update t1 set a='a ' where a like 'a %'; select concat(a,'.') from t1; concat(a,'.') a . update t1 set a='a ' where a like 'a '; select concat(a,'.') from t1; concat(a,'.') a . drop table t1; create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `v` (`v`(5)), KEY `c` (`c`(5)), KEY `t` (`t`(5)) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (v char(10) character set utf8); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` char(10) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(10), c char(10)) row_format=fixed; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED insert into t1 values('a','a'),('a ','a '); select concat('*',v,'*',c,'*') from t1; concat('*',v,'*',c,'*') *a*a* *a *a* drop table t1; create table t1 (v varchar(65530), key(v(10))); insert into t1 values(repeat('a',65530)); select length(v) from t1 where v=repeat('a',65530); length(v) 65530 drop table t1; create table t1(a int, b varchar(12), key ba(b, a)); insert into t1 values (1, 'A'), (20, NULL); explain select * from t1 where a=20 and b is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref ba ba 20 const,const 1 Using where select * from t1 where a=20 and b is null; a b 20 NULL drop table t1; create table t1 (v varchar(65530), key(v)); Warnings: Warning 1071 Specified key was too long; max key length is MAX_KEY_LENGTH bytes drop table if exists t1; create table t1 (v varchar(65536)); Warnings: Note 1246 Converting column 'v' from VARCHAR to TEXT show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` mediumtext ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(65530) character set utf8); Warnings: Note 1246 Converting column 'v' from VARCHAR to TEXT show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` mediumtext CHARACTER SET utf8 ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 drop table t1; set storage_engine=MyISAM; create table t1 (a varchar(255) character set utf8, b varchar(255) character set utf8, c varchar(255) character set utf8, d varchar(255) character set utf8, key (a,b,c,d)) engine=bdb; drop table t1; create table t1 (a varchar(255) character set utf8, b varchar(255) character set utf8, c varchar(255) character set utf8, d varchar(255) character set utf8, e varchar(255) character set utf8, key (a,b,c,d,e)) engine=bdb; ERROR 42000: Specified key was too long; max key length is 3072 bytes set autocommit=0; create table t1 (a int) engine=bdb; commit; alter table t1 add primary key(a); drop table t1; End of 5.0 tests create table t1 (a int) engine=bdb; set session transaction isolation level repeatable read; set transaction isolation level serializable; begin; select @@tx_isolation; @@tx_isolation SERIALIZABLE insert into t1 values (1); set transaction isolation level read committed; ERROR 25001: Transaction isolation level can't be changed while a transaction is in progress rollback; begin; select @@tx_isolation; @@tx_isolation REPEATABLE-READ insert into t1 values (1); rollback; drop table t1; End of 5.1 tests