range.result

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

"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4", 
"abcdefg5", "123456785", "qwertyui5", "asddfg5", 
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4", 
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4", 
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4", 
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
"abcdefg1", "123456781", "qwertyui1", "asddfg1", 
"abcdefg2", "123456782", "qwertyui2", "asddfg2", 
"abcdefg3", "123456783", "qwertyui3", "asddfg3", 
"abcdefg4", "123456784", "qwertyui4", "asddfg4", 
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC");
c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14	c15	c16
drop table t1;
End of 4.1 tests
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
status varchar(20),
PRIMARY KEY  (id),
KEY (status)
);
INSERT INTO t1 VALUES
(1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'),
(7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'),
(13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'),
(19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'), 
(25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'),
(31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'),
(37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'),
(43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'),
(49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'),
(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C');
EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	status	status	23	NULL	11	Using where
EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	status	status	23	NULL	11	Using where
SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
id	status
53	C
54	C
55	C
56	C
57	C
58	C
59	C
60	C
SELECT * FROM t1 WHERE status NOT IN ('A','B');
id	status
53	C
54	C
55	C
56	C
57	C
58	C
59	C
60	C
EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	status	status	23	NULL	11	Using where; Using index
EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	status	status	23	NULL	11	Using where; Using index
EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	status	status	23	NULL	10	Using where
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	status	status	23	NULL	10	Using where
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id	status
53	C
54	C
55	C
56	C
57	C
58	C
59	C
60	C
SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id	status
53	C
54	C
55	C
56	C
57	C
58	C
59	C
60	C
DROP TABLE t1;
CREATE TABLE  t1 (a int, b int, primary key(a,b));
INSERT INTO  t1 VALUES
(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3);
CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3;
EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
EXPLAIN SELECT a,b FROM t1 WHERE a < 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
EXPLAIN SELECT a,b FROM v1 WHERE a < 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
SELECT a,b FROM t1 WHERE a < 2 and b=3;
a	b
1	3
SELECT a,b FROM v1 WHERE a < 2 and b=3;
a	b
1	3
DROP VIEW v1;
DROP TABLE t1;
CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name));
INSERT INTO t1 VALUES ('Betty'), ('Anna');
SELECT * FROM t1;
name
Anna
Betty
DELETE FROM t1 WHERE name NOT LIKE 'A%a';
SELECT * FROM t1;
name
Anna
DROP TABLE t1;
CREATE TABLE t1 (a int, KEY idx(a));
INSERT INTO t1 VALUES (NULL), (1), (2), (3);
SELECT * FROM t1;
a
NULL
1
2
3
DELETE FROM t1 WHERE NOT(a <=> 2);
SELECT * FROM t1;
a
2
DROP TABLE t1;
create table t1 (a int, b int, primary key(a,b));
create view v1 as select a, b from t1;
INSERT INTO `t1` VALUES
(0,0),(1,0),(2,0),(3,0),(4,0),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(11,2),(12,2)
,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3);
explain select * from t1 where a in (3,4)  and b in (1,2,3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
explain select * from v1 where a in (3,4)  and b in (1,2,3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
explain select * from t1 where a between 3 and 4 and b between 1 and 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
explain select * from v1 where a between 3 and 4 and b between 1 and 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
drop view v1;
drop table t1;
create table t3 (a int);
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a varchar(10), filler char(200), key(a)) charset=binary;
insert into t1 values ('a','');
insert into t1 values ('a ','');
insert into t1 values ('a  ', '');
insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), ''
  from t3 A, t3 B, t3 C;
create table t2 (a varchar(10), filler char(200), key(a));
insert into t2 select * from t1;
explain select * from t1 where a between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	13	NULL	#	Using where
explain select * from t1 where a = 'a' or a='a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	13	NULL	#	Using where
explain select * from t2 where a between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	a	a	13	const	#	Using where
explain select * from t2 where a = 'a' or a='a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	a	a	13	const	#	Using where
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	13	NULL	#	Using where
select a, hex(filler) from t1 where a not between 'b' and 'b';
a	hex(filler)
a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
drop table t1,t2,t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, key(a));
insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
set @a="select * from t2 force index (a) where a NOT IN(0";
select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
count(*)
1000
set @a=concat(@a, ')');
insert into t2 values (11),(13),(15);
set @b= concat("explain ", @a);
prepare stmt1 from @b;
execute stmt1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	a	a	5	NULL	1003	Using where; Using index
prepare stmt1 from @a;
execute stmt1;
a
11
13
15
drop table t1, t2;
CREATE TABLE t1 (
id int NOT NULL DEFAULT '0',
b int NOT NULL DEFAULT '0',
c int NOT NULL DEFAULT '0', 
INDEX idx1(b,c), INDEX idx2(c));
INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
INSERT INTO t1(b,c) VALUES (3,4), (3,4);
SELECT * FROM t1 WHERE b<=3 AND 3<=c;
id	b	c
0	3	4
0	3	4
SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
id	b	c
0	3	4
0	3	4
EXPLAIN  SELECT * FROM t1 WHERE b<=3 AND 3<=c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	3	Using where
EXPLAIN  SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	3	Using where
SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id	b	c
0	3	4
0	3	4
SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id	b	c
0	3	4
0	3	4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	idx1,idx2	idx1,idx2	4,4	NULL	4	Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	idx1,idx2	idx1,idx2	4,4	NULL	4	Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (                                      
item char(20) NOT NULL default '',                          
started datetime NOT NULL default '0000-00-00 00:00:00', 
price decimal(16,3) NOT NULL default '0.000',                 
PRIMARY KEY (item,started)                     
) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('A1','2005-11-01 08:00:00',1000),
('A1','2005-11-15 00:00:00',2000),
('A1','2005-12-12 08:00:00',3000),
('A2','2005-12-01 08:00:00',1000);
EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	PRIMARY	PRIMARY	20	const	2	Using where
Warnings:
Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
item	started	price
A1	2005-11-01 08:00:00	1000.000
A1	2005-11-15 00:00:00	2000.000
Warnings:
Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
item	started	price
A1	2005-11-01 08:00:00	1000.000
A1	2005-11-15 00:00:00	2000.000
DROP INDEX `PRIMARY` ON t1;
EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
Warnings:
Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
item	started	price
A1	2005-11-01 08:00:00	1000.000
A1	2005-11-15 00:00:00	2000.000
Warnings:
Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
item	started	price
A1	2005-11-01 08:00:00	1000.000
A1	2005-11-15 00:00:00	2000.000
DROP TABLE t1;