merge.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

drop table if exists t1,t2,t3,t4,t5,t6;
drop database if exists mysqltest;
create table t1 (a int not null primary key auto_increment, message char(20));
create table t2 (a int not null primary key auto_increment, message char(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2);
select * from t3;
a	b
1	Testing
2	table
3	t1
1	Testing
2	table
3	t2
select * from t3 order by a desc;
a	b
3	t1
3	t2
2	table
2	table
1	Testing
1	Testing
drop table t3;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2);
explain select * from t3 where a < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	a	a	4	NULL	18	Using where
explain select * from t3 where a > 10 and a < 20;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	a	a	4	NULL	17	Using where
select * from t3 where a = 10;
a	b
10	Testing
10	Testing
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
3	t1
3	t2
4	Testing
4	Testing
5	table
5	table
6	t2
6	t1
7	Testing
7	Testing
8	table
8	table
9	t2
9	t2
select * from t3 where a > 10 and a < 20;
a	b
11	table
11	table
12	t1
12	t1
13	Testing
13	Testing
14	table
14	table
15	t2
15	t2
16	Testing
16	Testing
17	table
17	table
18	t2
18	t2
19	Testing
19	Testing
explain select a from t3 order by a desc limit 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	index	NULL	a	4	NULL	1131	Using index
select a from t3 order by a desc limit 10;
a
699
698
697
696
695
694
693
692
691
690
select a from t3 order by a desc limit 300,10;
a
416
415
415
414
414
413
413
412
412
411
delete from t3 where a=3;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
4	Testing
4	Testing
5	table
5	table
6	t2
6	t1
7	Testing
7	Testing
8	table
8	table
9	t2
9	t2
delete from t3 where a >= 6 and a <= 8;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
4	Testing
4	Testing
5	table
5	table
9	t2
9	t2
update t3 set a=3 where a=9;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
3	t2
3	t2
4	Testing
4	Testing
5	table
5	table
update t3 set a=6 where a=7;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
3	t2
3	t2
4	Testing
4	Testing
5	table
5	table
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` char(20) default NULL,
  KEY `a` (`a`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2);
select * from t4;
ERROR HY000: All tables in the MERGE table are not identically defined
alter table t4 add column c int;
ERROR HY000: All tables in the MERGE table are not identically defined
create database mysqltest;
create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6);
show create table t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `a` int(11) NOT NULL,
  `b` char(20) default NULL,
  KEY `a` (`a`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`)
alter table t5 engine=myisam;
drop table t5, mysqltest.t6;
drop database mysqltest;
drop table t4,t3,t1,t2;
create table t1 (c char(10)) engine=myisam;
create table t2 (c char(10)) engine=myisam;
create table t3 (c char(10)) union=(t1,t2) engine=merge;
insert into t1 (c) values ('test1');
insert into t1 (c) values ('test1');
insert into t1 (c) values ('test1');
insert into t2 (c) values ('test2');
insert into t2 (c) values ('test2');
insert into t2 (c) values ('test2');
select * from t3;
c
test1
test1
test1
test2
test2
test2
select * from t3;
c
test1
test1
test1
test2
test2
test2
delete from t3 where 1=1;
select * from t3;
c
select * from t1;
c
drop table t3,t2,t1;
CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr));
CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr));
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
ENGINE=MERGE UNION=(t1,t2);
SELECT * from t3;
incr	othr
INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17);
INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32);
INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37);
INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30);
SELECT * from t3 where incr in (1,2,3,4) order by othr;
incr	othr
1	10
2	24
4	33
3	53
alter table t3 UNION=(t1);
select count(*) from t3;
count(*)
10
alter table t3 UNION=(t1,t2);
select count(*) from t3;
count(*)
20
alter table t3 ENGINE=MYISAM;
select count(*) from t3;
count(*)
20
drop table t3;
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
ENGINE=MERGE UNION=(t1,t2);
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `incr` int(11) NOT NULL,
  `othr` int(11) NOT NULL,
  PRIMARY KEY  (`incr`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
alter table t3 drop primary key;
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `incr` int(11) NOT NULL,
  `othr` int(11) NOT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
drop table t3,t2,t1;
create table t1 (a int not null, key(a)) engine=merge;
select * from t1;
a
drop table t1;
create table t1 (a int not null, b int not null, key(a,b));
create table t2 (a int not null, b int not null, key(a,b));
create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2);
insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6);
insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6);
flush tables;
select * from t3 where a=1 order by b limit 2;
a	b
1	1
1	2
drop table t3,t1,t2;
create table t1 (a int not null, b int not null auto_increment, primary key(a,b));
create table t2 (a int not null, b int not null auto_increment, primary key(a,b));
create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
show create table t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`)
show create table t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
select * from t3 order by b,a limit 3;
a	b
select * from t4 order by b,a limit 3;
a	b
1	1
2	1
1	2
select * from t5 order by b,a limit 3,3;
a	b
2	2
1	3
2	3
select * from t6 order by b,a limit 6,3;
a	b
1	4
2	4
insert into t5 values (5,1),(5,2);
insert into t6 values (6,1),(6,2);
select * from t1 order by a,b;
a	b
1	1
1	2
1	3
1	4
5	1
5	2
select * from t2 order by a,b;
a	b
2	1
2	2
2	3
2	4
6	1
6	2
select * from t4 order by a,b;
a	b
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
5	1
5	2
6	1
6	2
insert into t3 values (3,1),(3,2),(3,3),(3,4);
select * from t3 order by a,b;
a	b
3	1
3	2
3	3
3	4
alter table t4 UNION=(t1,t2,t3);
show create table t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,