binary.result 3.72 KB
Newer Older
1
drop table if exists t1,t2;
unknown's avatar
unknown committed
2 3 4 5 6 7 8
create table t1 (name char(20) not null, primary key (name));
create table t2 (name char(20) binary not null, primary key (name));
insert into t1 values ("");
insert into t1 values ("");
insert into t1 values ("");
insert into t2 select * from t1;
select * from t1 order by name;
9 10 11 12
name



unknown's avatar
unknown committed
13
select concat("*",name,"*") from t1 order by 1;
14 15 16 17
concat("*",name,"*")
**
**
**
unknown's avatar
unknown committed
18
select min(name),min(concat("*",name,"*")),max(name),max(concat("*",name,"*")) from t1;
19 20
min(name)	min(concat("*",name,"*"))	max(name)	max(concat("*",name,"*"))
	**		**
unknown's avatar
unknown committed
21
select * from t2 order by name;
22 23 24 25
name



unknown's avatar
unknown committed
26
select concat("*",name,"*") from t2 order by 1;
27 28 29 30
concat("*",name,"*")
**
**
**
unknown's avatar
unknown committed
31
select min(name),min(concat("*",name,"*")),max(name),max(concat("*",name,"*")) from t2;
32 33
min(name)	min(concat("*",name,"*"))	max(name)	max(concat("*",name,"*"))
	**		**
unknown's avatar
unknown committed
34
select name from t1 where name between '' and '';
35 36 37
name


unknown's avatar
unknown committed
38
select name from t2 where name between '' and '';
39 40 41 42
name



unknown's avatar
unknown committed
43
select name from t2 where name between '' and '';
44
name
unknown's avatar
unknown committed
45
drop table t1,t2;
unknown's avatar
unknown committed
46
create table t1 (a char(10) not null, b char(10) binary not null,key (a), key(b));
unknown's avatar
unknown committed
47
insert into t1 values ("hello ","hello "),("hello2 ","hello2 ");
unknown's avatar
unknown committed
48 49 50 51 52 53 54 55 56 57 58 59 60 61
select concat("-",a,"-",b,"-") from t1 where a="hello";
concat("-",a,"-",b,"-")
-hello-hello-
select concat("-",a,"-",b,"-") from t1 where a="hello ";
concat("-",a,"-",b,"-")
-hello-hello-
select concat("-",a,"-",b,"-") from t1 ignore index (a) where a="hello ";
concat("-",a,"-",b,"-")
-hello-hello-
select concat("-",a,"-",b,"-") from t1 where b="hello";
concat("-",a,"-",b,"-")
-hello-hello-
select concat("-",a,"-",b,"-") from t1 where b="hello ";
concat("-",a,"-",b,"-")
62
-hello-hello-
unknown's avatar
unknown committed
63 64
select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello ";
concat("-",a,"-",b,"-")
65
-hello-hello-
unknown's avatar
unknown committed
66
alter table t1 modify b tinytext not null, drop key b, add key (b(100));
67 68 69 70
select concat("-",a,"-",b,"-") from t1;
concat("-",a,"-",b,"-")
-hello-hello-
-hello2-hello2-
unknown's avatar
unknown committed
71 72
select concat("-",a,"-",b,"-") from t1 where b="hello ";
concat("-",a,"-",b,"-")
unknown's avatar
unknown committed
73
-hello-hello-
unknown's avatar
unknown committed
74 75 76
select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello ";
concat("-",a,"-",b,"-")
-hello-hello-
unknown's avatar
unknown committed
77
drop table t1;
78 79 80 81 82 83 84 85 86 87
create table t1 (b char(8));
insert into t1 values(NULL);
select b from t1 where binary b like '';
b
select b from t1 group by binary b like '';
b
NULL
select b from t1 having binary b like '';
b
drop table t1;
unknown's avatar
unknown committed
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
create table t1 (a char(15) binary, b binary(15));
insert into t1 values ('aaa','bbb'),('AAA','BBB');
select upper(a),upper(b) from t1;
upper(a)	upper(b)
AAA	bbb
AAA	BBB
select lower(a),lower(b) from t1;
lower(a)	lower(b)
aaa	bbb
aaa	BBB
select * from t1 where upper(a)='AAA';
a	b
aaa	bbb
AAA	BBB
select * from t1 where lower(a)='aaa';
a	b
aaa	bbb
AAA	BBB
select * from t1 where upper(b)='BBB';
a	b
AAA	BBB
select * from t1 where lower(b)='bbb';
a	b
aaa	bbb
select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
charset(a)	charset(b)	charset(binary 'ccc')
latin1	binary	latin1
select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
collation(a)	collation(b)	collation(binary 'ccc')
latin1_bin	binary	latin1_bin
drop table t1;
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
create table t1( firstname char(20), lastname char(20));
insert into t1 values ("john","doe"),("John","Doe");
select * from t1 where firstname='john' and firstname like binary 'john';
firstname	lastname
john	doe
select * from t1 where firstname='john' and binary 'john' = firstname;
firstname	lastname
john	doe
select * from t1 where firstname='john' and firstname = binary 'john';
firstname	lastname
john	doe
select * from t1 where firstname='John' and firstname like binary 'john';
firstname	lastname
john	doe
select * from t1 where firstname='john' and firstname like binary 'John';
firstname	lastname
John	Doe
drop table t1;