type_bit.result

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

select 0 + b'1';
0 + b'1'
1
select 0 + b'0';
0 + b'0'
0
select 0 + b'000001';
0 + b'000001'
1
select 0 + b'000011';
0 + b'000011'
3
select 0 + b'000101';
0 + b'000101'
5
select 0 + b'000000';
0 + b'000000'
0
select 0 + b'10000000';
0 + b'10000000'
128
select 0 + b'11111111';
0 + b'11111111'
255
select 0 + b'10000001';
0 + b'10000001'
129
select 0 + b'1000000000000000';
0 + b'1000000000000000'
32768
select 0 + b'1111111111111111';
0 + b'1111111111111111'
65535
select 0 + b'1000000000000001';
0 + b'1000000000000001'
32769
drop table if exists t1,t2;
create table t1 (a bit(65));
ERROR 42000: Display width out of range for column 'a' (max = 64)
create table t1 (a bit(0));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a bit(64));
insert into t1 values 
(b'1111111111111111111111111111111111111111111111111111111111111111'),
(b'1000000000000000000000000000000000000000000000000000000000000000'),
(b'0000000000000000000000000000000000000000000000000000000000000001'),
(b'1010101010101010101010101010101010101010101010101010101010101010'),
(b'0101010101010101010101010101010101010101010101010101010101010101');
select hex(a) from t1;
hex(a)
FFFFFFFFFFFFFFFF
8000000000000000
1
AAAAAAAAAAAAAAAA
5555555555555555
drop table t1;
create table t1 (a bit);
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 4
select hex(a) from t1;
hex(a)
0
1
0
1
1
alter table t1 add unique (a);
ERROR 23000: Duplicate entry '' for key 1
drop table t1;
create table t1 (a bit(2));
insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 4
select a+0 from t1;
a+0
0
1
2
3
alter table t1 add key (a);
explain select a+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	2	NULL	4	Using index
select a+0 from t1;
a+0
0
1
2
3
drop table t1;
create table t1 (a bit(7), b bit(9), key(a, b));
insert into t1 values 
(94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),    
(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380),   
(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),    
(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),
(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),   
(44, 307), (68, 454), (57, 135);
explain select a+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	38	Using index
select a+0 from t1;
a+0
0
4
5
9
23
24
28
29
30
31
34
44
49
56
57
59
60
61
68
68
75
77
78
79
87
88
94
94
104
106
108
111
116
118
119
122
123
127
explain select b+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	38	Using index
select b+0 from t1;
b+0
177
245
178
363
36
398
499
399
83
438
202
307
345
379
135
188
343
152
206
454
42
133
123
349
351
411
46
468
280
446
67
368
390
380
368
118
411
403
explain select a+0, b+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	38	Using index
select a+0, b+0 from t1;
a+0	b+0
0	177
4	245
5	178
9	363
23	36
24	398
28	499
29	399
30	83
31	438
34	202
44	307
49	345
56	379
57	135
59	188
60	343
61	152
68	206
68	454
75	42
77	133
78	123
79	349
87	351
88	411
94	46
94	468
104	280
106	446
108	67
111	368
116	390
118	380
119	368
122	118
123	411
127	403
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	2	NULL	27	Using where; Using index; Using filesort
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
a+0	b+0
44	307
49	345
56	379
60	343
68	206
68	454
79	349
87	351
88	411
94	468
104	280
106	446
111	368
116	390
118	380
119	368
123	411
127	403
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	2	NULL	8	Using where; Using index; Using filesort
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
a+0	b+0
57	135
61	152
59	188
68	206
44	307
60	343
49	345
56	379
68	454
set @@max_length_for_sort_data=0;
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
a+0	b+0
57	135
61	152
59	188
68	206
44	307
60	343
49	345
56	379
68	454
select hex(min(a)) from t1;
hex(min(a))
0
select hex(min(b)) from t1;
hex(min(b))
24
select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
hex(min(a))	hex(max(a))	hex(min(b))	hex(max(b))
0	7F	24	1F3
drop table t1;
create table t1 (a int not null, b bit, c bit(9), key(a, b, c));
insert into t1 values
(4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54),
(56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34);
select a+0, b+0, c+0 from t1;
a+0	b+0	c+0
1	1	100
2	1	4
4	NULL	1
4	0	3
4	0	23
4	0	34
4	0	54
4	1	100
23	0	1
56	0	22
select hex(min(b)) from t1 where a = 4;
hex(min(b))
0
select hex(min(c)) from t1 where a = 4 and b = 0;
hex(min(c))
3
select hex(max(b)) from t1;
hex(max(b))
1
select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
a+0	b+0	c+0
4	0	3
4	0	23
select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
a+0	b+0	c+0
4	1	100
select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
a+0	b+0	c+0
4	1	100
select a+0, b+0, c+0 from t1 order by b desc;
a+0	b+0	c+0
2	1	4
1	1	100
4	1	100
4	0	3
4	0	23
4	0	54
56	0	22
23	0	1
4	0	34
4	NULL	1
select a+0, b+0, c+0 from t1 order by c;
a+0	b+0	c+0
4	NULL	1
23	0	1
4	0	3
2	1	4
56	0	22
4	0	23
4	0	34
4	0	54
1	1	100
4	1	100
drop table t1;
create table t1(a bit(2), b bit(2));
insert into t1 (a) values (0x01), (0x03), (0x02);
update t1 set b= concat(a);
select a+0, b+0 from t1;
a+0	b+0
1	1
3	3
2	2
drop table t1;
create table t1 (a bit(7), key(a));
insert into t1 values (44), (57);
select a+0 from t1;
a+0
44
57
drop table t1;
create table t1 (a bit(3), b bit(12));
insert into t1 values (7,(1<<12)-2), (0x01,0x01ff);
select hex(a),hex(b) from t1;
hex(a)	hex(b)
7	FFE
1	1FF
select hex(concat(a)),hex(concat(b)) from t1;
hex(concat(a))	hex(concat(b))
07	0FFE
01	01FF
drop table t1;
create table t1(a int, b bit not null);
alter table t1 add primary key (a);
drop table t1;
create table t1 (a bit(19), b bit(5));
insert into t1 values (1000, 10), (3, 8), (200, 6), (2303, 2), (12345, 4), (1, 0);
select a+0, b+0 from t1;
a+0	b+0
1000	10
3	8
200	6
2303	2
12345	4
1	0
alter table t1 engine=heap;
select a+0, b+0 from t1;
a+0	b+0
1000	10
3	8
200	6
2303	2
12345	4
1	0
alter table t1 add key(a, b);
select a+0, b+0 from t1;
a+0	b+0
1000	10
3	8
200	6
2303	2
12345	4
1	0
alter table t1 engine=myisam;
select a+0, b+0 from t1;
a+0	b+0
1	0
3	8
200	6
1000	10
2303	2
12345	4
create table t2 engine=heap select * from t1;
select a+0, b+0 from t2;
a+0	b+0
1	0
3	8
200	6
1000	10
2303	2
12345	4
drop table t1;
create table t1 select * from t2;
select a+0, b+0 from t1;
a+0	b+0
1	0
3	8
200	6
1000	10
2303	2
12345	4
drop table t1, t2;
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 
g bit(1) NOT NULL default 1, h char(1) default 'a');
insert into t1 set a=1;
select hex(g), h from t1;
hex(g)	h
1	a
drop table t1;
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
g bit(1) NOT NULL default 1);
insert into t1 set a=1;
select hex(g) from t1;
hex(g)
1
drop table t1;
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 
h char(1) default 'a') engine=myisam;
insert into t1 set a=1;
select h from t1;
h
a
drop table t1;
create table t1 (a bit(8)) engine=heap;
insert into t1 values ('1111100000');
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
select a+0 from t1;
a+0
255
drop table t1;
create table t1 (a bit(7));
insert into t1 values (120), (0), (111);
select a+0 from t1 union select a+0 from t1;
a+0
120
0
111
select a+0 from t1 union select NULL;
a+0
120
0
111
NULL
select NULL union select a+0 from t1;
NULL
NULL
120
0
111
create table t2 select a from t1 union select a from t1;
select a+0 from t2;
a+0
120
0
111
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` bit(7) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (id1 int(11), b1 bit(1));
create table t2 (id2 int(11), b2 bit(1));
insert into t1 values (1, 1), (2, 0), (3, 1);
insert into t2 values (2, 1), (3, 0), (4, 0);
create algorithm=undefined view v1 as 
select b1+0, b2+0 from t1, t2 where id1 = id2 and b1 = 0
union
select b1+0, b2+0 from t1, t2 where id1 = id2 and b2 = 1;
select * from v1;
b1+0	b2+0
0	1
drop table t1, t2;
drop view v1;
create table t1(a bit(4));
insert into t1(a) values (1), (2), (5), (4), (3);
insert into t1 select * from t1;
select a+0 from t1;
a+0
1
2
5
4
3
1
2
5
4
3
drop table t1;
create table t1 (a1 int(11), b1 bit(2));
create table t2 (a2 int(11), b2 bit(2));
insert into t1 values (1, 1), (2, 0), (3, 1), (4, 2);
insert into t2 values (2, 1), (3, 0), (4, 1), (5, 2);
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2;
a1	a2	b1+0	b2+0
2	2	0	1
3	3	1	0
4	4	2	1
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1;
a1	a2	b1+0	b2+0
2	2	0	1
3	3	1	0
4	4	2	1
select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2;
a1	a2	b1+0	b2+0
1	2	1	1
3	2	1	1
2	3	0	0
1	4	1	1
3	4	1	1
4	5	2	2
select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1;
sum(a1)	b1+0	b2+0
2	0	0
4	2	2
8	1	1
select 1 from t1 join t2 on b1 = b2 group by b1 order by 1;
1
1
1
1
select b1+0,sum(b1), sum(b2) from t1 join t2 on b1 = b2 group by b1 order by 1;
b1+0	sum(b1)	sum(b2)
0	0	0
1	4	4
2	2	2
drop table t1, t2;