range.result

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

(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5),
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a,b	a	5	NULL	2	Using where
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
a	b
DROP TABLE t1;
CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b));
INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0);
INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0);
SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1);
COUNT(*)
6
SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
COUNT(*)
6
DROP TABLE t1;
CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) );
INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4);
SELECT * FROM t1
WHERE
(
( b =1 AND a BETWEEN 14 AND 21 ) OR
( b =2 AND a BETWEEN 16 AND 18 ) OR
( b =3 AND a BETWEEN 15 AND 19 ) OR
(a BETWEEN 19 AND 47)
);
a	b
15	1
47	1
DROP TABLE t1;
CREATE TABLE t1 (
id int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,
line int( 5 ) unsigned NOT NULL default '0',
columnid int( 3 ) unsigned NOT NULL default '0',
owner int( 3 ) unsigned NOT NULL default '0',
ordinal int( 3 ) unsigned NOT NULL default '0',
showid smallint( 6 ) unsigned NOT NULL default '1',
tableid int( 1 ) unsigned NOT NULL default '1',
content int( 5 ) unsigned NOT NULL default '188',
PRIMARY KEY ( owner, id ) ,
KEY menu( owner, showid, columnid ) ,
KEY `COLUMN` ( owner, columnid, line ) ,
KEY `LINES` ( owner, tableid, content, id ) ,
KEY recount( owner, line ) 
) ENGINE = MYISAM;
INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5);
SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30;
id	columnid	tableid	content	showid	line	ordinal
13	13	1	188	1	5	0
15	15	1	188	1	1	0
drop table t1;
create  table t1 (id int(10) primary key);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
select id from t1 where id in (2,5,9) ;
id
2
5
9
select id from t1 where id=2 or id=5 or id=9 ;
id
2
5
9
drop table t1;
create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2));
insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"),
(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"),
(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"),
(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"),
(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"),
(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"),
(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa");
select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1;
id1	idnull
drop table t1;
create table t1 (
id int not null auto_increment,
name char(1) not null,
uid int not null,
primary key (id),
index uid_index (uid));
create table t2 (
id int not null auto_increment,
name char(1) not null,
uid int not null,
primary key (id),
index uid_index (uid));
insert into t1(id, uid, name) values(1, 0, ' ');
insert into t1(uid, name) values(0, ' ');
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t2(uid, name) select uid, name from t1;
insert into t2(uid, name) select uid, name from t1;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
delete from t2;
insert into t2(uid, name) values 
(1, CHAR(64+1)),
(2, CHAR(64+2)),
(3, CHAR(64+3)),
(4, CHAR(64+4)),
(5, CHAR(64+5)),
(6, CHAR(64+6)),
(7, CHAR(64+7)),
(8, CHAR(64+8)),
(9, CHAR(64+9)),
(10, CHAR(64+10)),
(11, CHAR(64+11)),
(12, CHAR(64+12)),
(13, CHAR(64+13)),
(14, CHAR(64+14)),
(15, CHAR(64+15)),
(16, CHAR(64+16)),
(17, CHAR(64+17)),
(18, CHAR(64+18)),
(19, CHAR(64+19)),
(20, CHAR(64+20)),
(21, CHAR(64+21)),
(22, CHAR(64+22)),
(23, CHAR(64+23)),
(24, CHAR(64+24)),
(25, CHAR(64+25)),
(26, CHAR(64+26));
insert into t1(uid, name) select uid, name from t2;
delete from t2;
insert into t2(id, uid, name) select id, uid, name from t1;
select count(*) from t1;
count(*)
1026
select count(*) from t2;
count(*)
1026
analyze table t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	Table is already up to date
explain select * from t1, t2  where t1.uid=t2.uid AND t1.uid > 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uid_index	uid_index	4	NULL	112	Using where
1	SIMPLE	t2	ref	uid_index	uid_index	4	test.t1.uid	38	
explain select * from t1, t2  where t1.uid=t2.uid AND t2.uid > 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uid_index	uid_index	4	NULL	112	Using where
1	SIMPLE	t2	ref	uid_index	uid_index	4	test.t1.uid	38	
explain select * from t1, t2  where t1.uid=t2.uid AND t1.uid != 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uid_index	uid_index	4	NULL	113	Using where
1	SIMPLE	t2	ref	uid_index	uid_index	4	test.t1.uid	38	
explain select * from t1, t2  where t1.uid=t2.uid AND t2.uid != 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uid_index	uid_index	4	NULL	113	Using where
1	SIMPLE	t2	ref	uid_index	uid_index	4	test.t1.uid	38	
select * from t1, t2  where t1.uid=t2.uid AND t1.uid > 0;
id	name	uid	id	name	uid
1001	A	1	1001	A	1
1002	B	2	1002	B	2
1003	C	3	1003	C	3
1004	D	4	1004	D	4
1005	E	5	1005	E	5
1006	F	6	1006	F	6
1007	G	7	1007	G	7
1008	H	8	1008	H	8
1009	I	9	1009	I	9
1010	J	10	1010	J	10
1011	K	11	1011	K	11
1012	L	12	1012	L	12
1013	M	13	1013	M	13
1014	N	14	1014	N	14
1015	O	15	1015	O	15
1016	P	16	1016	P	16
1017	Q	17	1017	Q	17
1018	R	18	1018	R	18
1019	S	19	1019	S	19
1020	T	20	1020	T	20
1021	U	21	1021	U	21
1022	V	22	1022	V	22
1023	W	23	1023	W	23
1024	X	24	1024	X	24
1025	Y	25	1025	Y	25
1026	Z	26	1026	Z	26
select * from t1, t2  where t1.uid=t2.uid AND t1.uid != 0;
id	name	uid	id	name	uid
1001	A	1	1001	A	1
1002	B	2	1002	B	2
1003	C	3	1003	C	3
1004	D	4	1004	D	4
1005	E	5	1005	E	5
1006	F	6	1006	F	6
1007	G	7	1007	G	7
1008	H	8	1008	H	8
1009	I	9	1009	I	9
1010	J	10	1010	J	10
1011	K	11	1011	K	11
1012	L	12	1012	L	12
1013	M	13	1013	M	13
1014	N	14	1014	N	14
1015	O	15	1015	O	15
1016	P	16	1016	P	16
1017	Q	17	1017	Q	17
1018	R	18	1018	R	18
1019	S	19	1019	S	19
1020	T	20	1020	T	20
1021	U	21	1021	U	21
1022	V	22	1022	V	22
1023	W	23	1023	W	23
1024	X	24	1024	X	24
1025	Y	25	1025	Y	25
1026	Z	26	1026	Z	26
drop table t1,t2;
create table t1 (x bigint unsigned not null);
insert into t1(x) values (0xfffffffffffffff0);
insert into t1(x) values (0xfffffffffffffff1);
select * from t1;
x
18446744073709551600
18446744073709551601
select count(*) from t1 where x>0;
count(*)
2
select count(*) from t1 where x=0;
count(*)
0
select count(*) from t1 where x<0;
count(*)
0
select count(*) from t1 where x < -16;
count(*)
0
select count(*) from t1 where x = -16;
count(*)
0
select count(*) from t1 where x > -16;
count(*)
2
select count(*) from t1 where x = 18446744073709551601;
count(*)
1
create table t2 (x bigint not null);
insert into t2(x) values (cast(0xfffffffffffffff0+0 as signed));
insert into t2(x) values (cast(0xfffffffffffffff1+0 as signed));
select * from t2;
x
-16
-15
select count(*) from t2 where x>0;
count(*)
0
select count(*) from t2 where x=0;
count(*)
0
select count(*) from t2 where x<0;
count(*)
2
select count(*) from t2 where x < -16;
count(*)
0
select count(*) from t2 where x = -16;
count(*)
1
select count(*) from t2 where x > -16;
count(*)
1
select count(*) from t2 where x = 18446744073709551601;