merge.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 880 行 · 第 1/2 页

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	35	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	35	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	35	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	35	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
create table t3 engine=merge union=(t1, t2) select (select max(a) 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;
create table t1(a int);
create table t2(a int);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
select * from t3;
a
1
2
insert t2 select * from t2;
select * from t2;
a
2
2
insert t3 select * from t1;
select * from t3;
a
1
1
2
2
insert t1 select * from t3;
select * from t1;
a
1
1
1
1
2
2
select * from t2;
a
2
2
select * from t3;
a
1
1
1
1
2
2
2
2
check table t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
drop table t1, t2, t3;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(2),(1);
CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT * FROM t2 WHERE a=2;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, t2;
CREATE TABLE t1(a INT) ENGINE=MEMORY;
CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM t2;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, t2;
CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
SELECT * FROM t2;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t2;
CREATE TABLE t1(a INT, b TEXT);
CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(a SMALLINT, b SMALLINT);
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b));
CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b));
CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(c1 VARCHAR(1));
CREATE TABLE m1 LIKE t1;
ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
SELECT * FROM m1;
c1
DROP TABLE t1, m1;
CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT,
c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT);
CREATE TABLE m1 LIKE t1;
ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
SELECT * FROM m1;
c1	c2	c3	c4	c5	c6	c7	c8	c9
DROP TABLE t1, m1;
CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci,
b INT, INDEX(a,b));
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2);
INSERT INTO t1 VALUES ('ss',1);
INSERT INTO t2 VALUES ('ss',2),(0xDF,2);
SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2;
COUNT(*)
2
DROP TABLE t1,t2,t3;
create table t1 (b bit(1));
create table t2 (b bit(1));
create table tm (b bit(1)) engine = merge union = (t1,t2);
select * from tm;
b
drop table tm, t1, t2;
create table t1 (a int) insert_method = last engine = merge;
insert into t1 values (1);
ERROR HY000: Table 't1' is read only
create table t2 (a int) engine = myisam;
alter table t1 union (t2);
insert into t1 values (1);
alter table t1 insert_method = no;
insert into t1 values (1);
ERROR HY000: Table 't1' is read only
drop table t2;
drop table t1;
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
test.tm1	check	Error	Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	Error	Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	error	Corrupt
CREATE TABLE t1(a INT);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
test.tm1	check	Error	Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	error	Corrupt
CREATE TABLE t2(a BLOB);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
test.tm1	check	Error	Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	error	Corrupt
ALTER TABLE t2 MODIFY a INT;
SELECT * FROM tm1;
a
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
test.tm1	check	status	OK
DROP TABLE tm1, t1, t2;
End of 5.0 tests