merge.result
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:16k
- 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 16 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 t1
- 6 t2
- 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 default '0',
- `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: Can't open file: 't4.MRG' (errno: 143)
- alter table t4 add column c int;
- ERROR HY000: Can't open file: 't4.MRG' (errno: 143)
- 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 default '0',
- `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 default '0',
- `othr` int(11) NOT NULL default '0',
- 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 default '0',
- `othr` int(11) NOT NULL default '0'
- ) 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 default '0',
- `b` int(11) NOT NULL default '0',
- 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 default '0',
- `b` int(11) NOT NULL default '0',
- 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 default '0',
- `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 default '0',
- `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 default '0',
- `b` int(11) NOT NULL default '0',
- KEY `a` (`a`,`b`)
- ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`,`t3`)
- 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
- 3 1
- 3 2
- 3 3
- 3 4
- 5 1
- 5 2
- 6 1
- 6 2
- alter table t4 INSERT_METHOD=FIRST;
- show create table t4;
- Table Create Table
- t4 CREATE TABLE `t4` (
- `a` int(11) NOT NULL default '0',
- `b` int(11) NOT NULL default '0',
- KEY `a` (`a`,`b`)
- ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`)
- insert into t4 values (4,1),(4,2);
- select * from t1 order by a,b;
- a b
- 1 1
- 1 2
- 1 3
- 1 4
- 4 1
- 4 2
- 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 t3 order by a,b;
- a b
- 3 1
- 3 2
- 3 3
- 3 4
- 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
- 3 1
- 3 2
- 3 3
- 3 4
- 4 1
- 4 2
- 5 1
- 5 2
- 6 1
- 6 2
- select * from t5 order by a,b;
- a b
- 1 1
- 1 2
- 1 3
- 1 4
- 2 1
- 2 2
- 2 3
- 2 4
- 4 1
- 4 2
- 5 1
- 5 2
- 6 1
- 6 2
- select 1;
- 1
- 1
- insert into t5 values (1,NULL),(5,NULL);
- insert into t6 values (2,NULL),(6,NULL);
- select * from t1 order by a,b;
- a b
- 1 1
- 1 2
- 1 3
- 1 4
- 1 5
- 4 1
- 4 2
- 5 1
- 5 2
- 5 3
- select * from t2 order by a,b;
- a b
- 2 1
- 2 2
- 2 3
- 2 4
- 2 5
- 6 1
- 6 2
- 6 3
- select * from t5 order by a,b;
- a b
- 1 1
- 1 2
- 1 3
- 1 4
- 1 5
- 2 1
- 2 2
- 2 3
- 2 4
- 2 5
- 4 1
- 4 2
- 5 1
- 5 2
- 5 3
- 6 1
- 6 2
- 6 3
- select * from t6 order by a,b;
- a b
- 1 1
- 1 2
- 1 3
- 1 4
- 1 5
- 2 1
- 2 2
- 2 3
- 2 4
- 2 5
- 4 1
- 4 2
- 5 1
- 5 2
- 5 3
- 6 1
- 6 2
- 6 3
- insert into t1 values (99,NULL);
- select * from t4 where a+0 > 90;
- a b
- 99 1
- insert t5 values (1,1);
- ERROR 23000: Duplicate entry '1-1' for key 1
- insert t6 values (2,1);
- ERROR 23000: Duplicate entry '2-1' for key 1
- insert t5 values (1,1) on duplicate key update b=b+10;
- insert t6 values (2,1) on duplicate key update b=b+20;
- select * from t5 where a < 3;
- a b
- 1 2
- 1 3
- 1 4
- 1 5
- 1 11
- 2 2
- 2 3
- 2 4
- 2 5
- 2 21
- drop table t6, t5, t4, t3, t2, t1;
- CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM;
- INSERT INTO t1 VALUES (1,1), (2,1);
- CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM;
- INSERT INTO t2 VALUES (1,2), (2,2);
- CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) ENGINE=MRG_MyISAM UNION=(t1,t2);
- select max(b) from t3 where a = 2;
- max(b)
- 2
- select max(b) from t1 where a = 2;
- max(b)
- 1
- drop table t3,t1,t2;
- create table t1 (a int not null);
- create table t2 (a int not null);
- insert into t1 values (1);
- insert into t2 values (2);
- create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
- select * from t3;
- a
- 1
- 2
- create temporary table t4 (a int not null);
- create temporary table t5 (a int not null);
- insert into t4 values (1);
- insert into t5 values (2);
- create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
- select * from t6;
- a
- 1
- 2
- drop table t6, t3, t1, t2, t4, t5;
- CREATE TABLE t1 (
- fileset_id tinyint(3) unsigned NOT NULL default '0',
- file_code varchar(32) NOT NULL default '',
- fileset_root_id tinyint(3) unsigned NOT NULL default '0',
- PRIMARY KEY (fileset_id,file_code),
- KEY files (fileset_id,fileset_root_id)
- ) ENGINE=MyISAM;
- INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1),
- (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1),
- (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1);
- CREATE TABLE t2 (
- fileset_id tinyint(3) unsigned NOT NULL default '0',
- file_code varchar(32) NOT NULL default '',
- fileset_root_id tinyint(3) unsigned NOT NULL default '0',
- PRIMARY KEY (fileset_id,file_code),
- KEY files (fileset_id,fileset_root_id)
- ) ENGINE=MRG_MyISAM UNION=(t1);
- EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2
- AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t2 range PRIMARY PRIMARY 33 NULL 5 Using where
- EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
- AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t2 range PRIMARY,files PRIMARY 33 NULL 5 Using where
- EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
- AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t1 range PRIMARY,files PRIMARY 33 NULL 5 Using where
- EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
- AND file_code = '0000000115' LIMIT 1;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t2 const PRIMARY,files PRIMARY 33 const,const 1
- DROP TABLE t2, t1;
- create table t1 (x int, y int, index xy(x, y));
- create table t2 (x int, y int, index xy(x, y));
- create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2);
- insert into t1 values(1, 2);
- insert into t2 values(1, 3);
- select * from t3 where x = 1 and y < 5 order by y;
- x y
- 1 2
- 1 3
- select * from t3 where x = 1 and y < 5 order by y desc;
- x y
- 1 3
- 1 2
- drop table t1,t2,t3;
- create table t1 (a int);
- create table t2 (a int);
- insert into t1 values (0);
- insert into t2 values (1);
- create table t3 engine=merge union=(t1, t2) select * from t1;
- ERROR HY000: You can't specify target table 't1' for update in FROM clause
- create table t3 engine=merge union=(t1, t2) select * from t2;
- ERROR HY000: You can't specify target table 't2' for update in FROM clause
- drop table t1, t2;
- create table t1 (
- a double(14,4),
- b varchar(10),
- index (a,b)
- ) engine=merge union=(t2,t3);
- create table t2 (
- a double(14,4),
- b varchar(10),
- index (a,b)
- ) engine=myisam;
- create table t3 (
- a double(14,4),
- b varchar(10),
- index (a,b)
- ) engine=myisam;
- insert into t2 values ( null, '');
- insert into t2 values ( 9999999999.999, '');
- insert into t3 select * from t2;
- select min(a), max(a) from t1;
- min(a) max(a)
- 9999999999.9990 9999999999.9990
- flush tables;
- select min(a), max(a) from t1;
- min(a) max(a)
- 9999999999.9990 9999999999.9990
- drop table t1, t2, t3;
- create table t1 (a int,b int,c int, index (a,b,c));
- create table t2 (a int,b int,c int, index (a,b,c));
- create table t3 (a int,b int,c int, index (a,b,c))
- engine=merge union=(t1 ,t2);
- insert into t1 (a,b,c) values (1,1,0),(1,2,0);
- insert into t2 (a,b,c) values (1,1,1),(1,2,1);
- explain select a,b,c from t3 force index (a) where a=1 order by a,b,c;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t3 ref a a 5 const 2 Using where; Using index
- select a,b,c from t3 force index (a) where a=1 order by a,b,c;
- a b c
- 1 1 0
- 1 1 1
- 1 2 0
- 1 2 1
- explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t3 ref a a 5 const 2 Using where; Using index
- select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
- a b c
- 1 2 1
- 1 2 0
- 1 1 1
- 1 1 0
- show index from t3;
- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
- t3 1 a 1 a A NULL NULL NULL YES BTREE
- t3 1 a 2 b A NULL NULL NULL YES BTREE
- t3 1 a 3 c A NULL NULL NULL YES BTREE
- drop table t1, t2, t3;
- CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) )
- ENGINE=MyISAM;
- CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) )
- ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST;
- INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2;
- INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3;
- SELECT b FROM t2;
- b
- 3
- DROP TABLE t1, t2;