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