1drop table if exists t1; 2select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; 3'a' = 'a' 'a' = 'a ' 'a ' = 'a' 41 1 1 5select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a'; 6'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a' 70 1 0 8select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0'; 9'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0' 100 0 1 11select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a '; 12'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a ' 130 1 0 14select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0'; 15'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0' 160 0 1 17select 'a a' > 'a', 'a \0' < 'a'; 18'a a' > 'a' 'a \0' < 'a' 191 1 20select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; 21binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' 221 1 1 23create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)); 24insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 25analyze table t1; 26check table t1; 27Table Op Msg_type Msg_text 28test.t1 check status OK 29select * from t1 ignore key (key1) where text1='teststring' or 30text1 like 'teststring_%' ORDER BY text1; 31text1 32teststring 33teststring 34select * from t1 where text1='teststring' or text1 like 'teststring_%'; 35text1 36teststring 37teststring 38select * from t1 where text1='teststring' or text1 > 'teststring\t'; 39text1 40teststring 41select * from t1 order by text1; 42text1 43nothing 44teststring 45teststring 46explain select * from t1 order by text1; 47id select_type table type possible_keys key key_len ref rows Extra 481 SIMPLE t1 index NULL key1 34 NULL 3 Using index 49alter table t1 modify text1 char(32) binary not null; 50check table t1; 51Table Op Msg_type Msg_text 52test.t1 check status OK 53select * from t1 ignore key (key1) where text1='teststring' or 54text1 like 'teststring_%' ORDER BY text1; 55text1 56teststring 57teststring 58select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 59c 60|teststring | 61|teststring| 62select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 63concat('|', text1, '|') 64|teststring| 65select text1, length(text1) from t1 order by text1; 66text1 length(text1) 67nothing 7 68teststring 11 69teststring 10 70select text1, length(text1) from t1 order by binary text1; 71text1 length(text1) 72nothing 7 73teststring 10 74teststring 11 75alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20)); 76insert into t1 values ('teststring '); 77select concat('|', text1, '|') from t1 order by text1; 78concat('|', text1, '|') 79|nothing| 80|teststring| 81|teststring | 82|teststring | 83select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 84concat('|', text1, '|') 85|teststring| 86|teststring | 87select concat('|', text1, '|') from t1 where text1='teststring'; 88concat('|', text1, '|') 89|teststring| 90select concat('|', text1, '|') from t1 where text1='teststring '; 91concat('|', text1, '|') 92|teststring | 93alter table t1 modify text1 text not null, pack_keys=1; 94analyze table t1; 95select concat('|', text1, '|') from t1 where text1='teststring'; 96concat('|', text1, '|') 97|teststring| 98|teststring | 99select concat('|', text1, '|') from t1 where text1='teststring '; 100concat('|', text1, '|') 101|teststring| 102|teststring | 103explain select concat('|', text1, '|') from t1 where text1='teststring '; 104id select_type table type possible_keys key key_len ref rows Extra 1051 SIMPLE t1 ref key1 key1 22 const 2 Using where 106select concat('|', text1, '|') from t1 where text1 like 'teststring_%'; 107concat('|', text1, '|') 108|teststring | 109|teststring | 110select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 111c 112|teststring | 113|teststring | 114|teststring| 115select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 116concat('|', text1, '|') 117|teststring| 118|teststring | 119select concat('|', text1, '|') from t1 order by text1; 120concat('|', text1, '|') 121|nothing| 122|teststring | 123|teststring| 124|teststring | 125drop table t1; 126create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0; 127insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 128select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 129c 130|teststring | 131|teststring| 132select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t'; 133concat('|', text1, '|') 134|teststring | 135|teststring| 136drop table t1; 137create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; 138insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 139select * from t1 ignore key (key1) where text1='teststring' or 140text1 like 'teststring_%' ORDER BY text1; 141text1 142teststring 143teststring 144select * from t1 where text1='teststring' or text1 like 'teststring_%'; 145text1 146teststring 147teststring 148select * from t1 where text1='teststring' or text1 >= 'teststring\t'; 149text1 150teststring 151teststring 152select * from t1 order by text1; 153text1 154nothing 155teststring 156teststring 157explain select * from t1 order by text1; 158id select_type table type possible_keys key key_len ref rows Extra 1591 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 160alter table t1 modify text1 char(32) binary not null; 161select * from t1 order by text1; 162text1 163nothing 164teststring 165teststring 166drop table t1; 167create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb; 168insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 169analyze table t1; 170check table t1; 171Table Op Msg_type Msg_text 172test.t1 check status OK 173select * from t1 where text1='teststring' or text1 like 'teststring_%'; 174text1 175teststring 176teststring 177select * from t1 where text1='teststring' or text1 > 'teststring\t'; 178text1 179teststring 180select * from t1 order by text1; 181text1 182nothing 183teststring 184teststring 185explain select * from t1 order by text1; 186id select_type table type possible_keys key key_len ref rows Extra 1871 SIMPLE t1 index NULL key1 34 NULL 3 Using index 188alter table t1 modify text1 char(32) binary not null; 189select * from t1 order by text1; 190text1 191nothing 192teststring 193teststring 194alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20)); 195insert into t1 values ('teststring '); 196select concat('|', text1, '|') from t1 order by text1; 197concat('|', text1, '|') 198|nothing| 199|teststring| 200|teststring | 201|teststring | 202alter table t1 modify text1 text not null, pack_keys=1; 203select * from t1 where text1 like 'teststring_%'; 204text1 205teststring 206teststring 207select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; 208text1 length(text1) 209teststring 10 210teststring 11 211teststring 11 212select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; 213text1 length(text1) 214teststring 10 215teststring 11 216teststring 11 217select concat('|', text1, '|') from t1 order by text1; 218concat('|', text1, '|') 219|nothing| 220|teststring | 221|teststring| 222|teststring | 223drop table t1; 224