1drop table if exists t1,t2; 2create table t1 (name char(20) not null, primary key (name)); 3create table t2 (name char(20) binary not null, primary key (name)); 4insert into t1 values ("�"); 5insert into t1 values ("�"); 6insert into t1 values ("�"); 7insert into t2 select * from t1; 8select * from t1 order by name; 9name 10� 11� 12� 13select concat("*",name,"*") from t1 order by 1; 14concat("*",name,"*") 15*�* 16*�* 17*�* 18select min(name),min(concat("*",name,"*")),max(name),max(concat("*",name,"*")) from t1; 19min(name) min(concat("*",name,"*")) max(name) max(concat("*",name,"*")) 20� *�* � *�* 21select * from t2 order by name; 22name 23� 24� 25� 26select concat("*",name,"*") from t2 order by 1; 27concat("*",name,"*") 28*�* 29*�* 30*�* 31select min(name),min(concat("*",name,"*")),max(name),max(concat("*",name,"*")) from t2; 32min(name) min(concat("*",name,"*")) max(name) max(concat("*",name,"*")) 33� *�* � *�* 34select name from t1 where name between '�' and '�'; 35name 36� 37� 38select name from t2 where name between '�' and '�'; 39name 40� 41� 42� 43select name from t2 where name between '�' and '�'; 44name 45drop table t1,t2; 46create table t1 (a char(10) not null, b char(10) binary not null,key (a), key(b)); 47insert into t1 values ("hello ","hello "),("hello2 ","hello2 "); 48select concat("-",a,"-",b,"-") from t1 where a="hello"; 49concat("-",a,"-",b,"-") 50-hello-hello- 51select concat("-",a,"-",b,"-") from t1 where a="hello "; 52concat("-",a,"-",b,"-") 53-hello-hello- 54select concat("-",a,"-",b,"-") from t1 ignore index (a) where a="hello "; 55concat("-",a,"-",b,"-") 56-hello-hello- 57select concat("-",a,"-",b,"-") from t1 where b="hello"; 58concat("-",a,"-",b,"-") 59-hello-hello- 60select concat("-",a,"-",b,"-") from t1 where b="hello "; 61concat("-",a,"-",b,"-") 62-hello-hello- 63select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello "; 64concat("-",a,"-",b,"-") 65-hello-hello- 66alter table t1 modify b tinytext not null, drop key b, add key (b(100)); 67select concat("-",a,"-",b,"-") from t1; 68concat("-",a,"-",b,"-") 69-hello-hello- 70-hello2-hello2- 71select concat("-",a,"-",b,"-") from t1 where b="hello "; 72concat("-",a,"-",b,"-") 73-hello-hello- 74select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello "; 75concat("-",a,"-",b,"-") 76-hello-hello- 77drop table t1; 78create table t1 (b char(8)); 79insert into t1 values(NULL); 80select b from t1 where binary b like ''; 81b 82select b from t1 group by binary b like ''; 83b 84NULL 85select b from t1 having binary b like ''; 86b 87drop table t1; 88create table t1 (a char(3) binary, b binary(3)); 89insert into t1 values ('aaa','bbb'),('AAA','BBB'); 90select upper(a),upper(b) from t1; 91upper(a) upper(b) 92AAA bbb 93AAA BBB 94select lower(a),lower(b) from t1; 95lower(a) lower(b) 96aaa bbb 97aaa BBB 98select * from t1 where upper(a)='AAA'; 99a b 100aaa bbb 101AAA BBB 102select * from t1 where lower(a)='aaa'; 103a b 104aaa bbb 105AAA BBB 106select * from t1 where upper(b)='BBB'; 107a b 108AAA BBB 109select * from t1 where lower(b)='bbb'; 110a b 111aaa bbb 112select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; 113charset(a) charset(b) charset(binary 'ccc') 114latin1 binary binary 115select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; 116collation(a) collation(b) collation(binary 'ccc') 117latin1_bin binary binary 118drop table t1; 119create table t1( firstname char(20), lastname char(20)); 120insert into t1 values ("john","doe"),("John","Doe"); 121select * from t1 where firstname='john' and firstname like binary 'john'; 122firstname lastname 123john doe 124select * from t1 where firstname='john' and binary 'john' = firstname; 125firstname lastname 126john doe 127select * from t1 where firstname='john' and firstname = binary 'john'; 128firstname lastname 129john doe 130select * from t1 where firstname='John' and firstname like binary 'john'; 131firstname lastname 132john doe 133select * from t1 where firstname='john' and firstname like binary 'John'; 134firstname lastname 135John Doe 136drop table t1; 137create table t1 (a binary); 138show create table t1; 139Table Create Table 140t1 CREATE TABLE `t1` ( 141 `a` binary(1) DEFAULT NULL 142) ENGINE=ENGINE DEFAULT CHARSET=latin1 143drop table t1; 144create table t1 (col1 binary(4)); 145insert into t1 values ('a'),('a '); 146select hex(col1) from t1; 147hex(col1) 14861000000 14961200000 150alter table t1 modify col1 binary(10); 151select hex(col1) from t1; 152hex(col1) 15361000000000000000000 15461200000000000000000 155insert into t1 values ('b'),('b '); 156select hex(col1) from t1; 157hex(col1) 15861000000000000000000 15961200000000000000000 16062000000000000000000 16162200000000000000000 162drop table t1; 163CREATE TABLE t1 ( 164a binary(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', 165index idx(a) 166); 167INSERT INTO t1 SET a=unhex('1F9480179366F2BF567E1C4B964C1EF029087575'); 168INSERT INTO t1 SET a=unhex('1F9480179366F2BF567E1C4B964C1EF029082020'); 169INSERT INTO t1 SET a=unhex('1F9480179366F2BF567E1C4B964C1EF029080707'); 170ANALYZE table t1; 171Table Op Msg_type Msg_text 172test.t1 analyze status OK 173SELECT hex(a) FROM t1 order by a; 174hex(a) 1751F9480179366F2BF567E1C4B964C1EF029080707 1761F9480179366F2BF567E1C4B964C1EF029082020 1771F9480179366F2BF567E1C4B964C1EF029087575 178EXPLAIN SELECT hex(a) FROM t1 order by a; 179id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1801 SIMPLE t1 NULL index NULL idx 20 NULL 3 100.00 Using index 181Warnings: 182Note 1003 /* select#1 */ select hex(`test`.`t1`.`a`) AS `hex(a)` from `test`.`t1` order by `test`.`t1`.`a` 183SELECT hex(a) from t1 WHERE a=unhex('1F9480179366F2BF567E1C4B964C1EF029082020'); 184hex(a) 1851F9480179366F2BF567E1C4B964C1EF029082020 186EXPLAIN 187SELECT hex(a) from t1 WHERE a=unhex('1F9480179366F2BF567E1C4B964C1EF029082020'); 188id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1891 SIMPLE t1 NULL ref idx idx 20 const 1 100.00 Using where; Using index 190Warnings: 191Note 1003 /* select#1 */ select hex(`test`.`t1`.`a`) AS `hex(a)` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(unhex('1F9480179366F2BF567E1C4B964C1EF029082020'))) 192SELECT hex(a) from t1 WHERE a=unhex('1F9480179366F2BF567E1C4B964C1EF02908'); 193hex(a) 194DROP TABLE t1; 195CREATE TABLE t1 ( 196id numeric(20) NOT NULL, 197lang varchar(8) NOT NULL, 198msg varchar(32) NOT NULL, 199PRIMARY KEY (id,lang) 200); 201INSERT INTO t1 VALUES (33, 'en', 'zzzzzzz'); 202INSERT INTO t1 VALUES (31, 'en', 'xxxxxxx'); 203INSERT INTO t1 VALUES (32, 'en', 'yyyyyyy'); 204SELECT * FROM t1 WHERE id=32; 205id lang msg 20632 en yyyyyyy 207DROP TABLE t1; 208