1
drop table if exists t1;
2
create table t1 (a varchar(10), key(a));
3
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
4
explain extended select * from t1 where a like 'abc%';
5
id select_type table type possible_keys key key_len ref rows filtered Extra
6
1 SIMPLE t1 index a a 13 NULL 5 20.00 Using where; Using index
8
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" where ("test"."t1"."a" like 'abc%')
9
explain extended select * from t1 where a like concat('abc','%');
10
id select_type table type possible_keys key key_len ref rows filtered Extra
11
1 SIMPLE t1 index a a 13 NULL 5 20.00 Using where; Using index
13
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" where ("test"."t1"."a" like concat('abc','%'))
14
select * from t1 where a like "abc%";
18
select * from t1 where a like concat("abc","%");
22
select * from t1 where a like "ABC%";
26
select * from t1 where a like "test%";
29
select * from t1 where a like "te_t";
32
select * from t1 where a like "%a%";
37
select * from t1 where a like "%abcd%";
40
select * from t1 where a like "%abc\d%";
44
create table t1 (a varchar(10), key(a));
45
insert into t1 values ('a'), ('a\\b');
46
select * from t1 where a like 'a\\%' escape '#';
49
select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b';
53
create table t1 (a datetime);
54
insert into t1 values ('2004-03-11 12:00:21');
55
select * from t1 where a like '2004-03-11 12:00:21';
60
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r);
61
INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����');
62
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
63
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
64
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
65
SELECT * FROM t1 WHERE a LIKE '%����%';
85
SELECT * FROM t1 WHERE a LIKE '%���%';
105
SELECT * FROM t1 WHERE a LIKE '����%';
127
CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250;
128
INSERT INTO t1 VALUES
129
('Techni Tapes Sp. z o.o.'),
130
('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
131
('AKAPESTER 1 P.P.H.U.'),
132
('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
133
('PPUH PESKA-I Maria Struniarska');
134
select * from t1 where a like '%PESA%';
136
Pojazdy Szynowe PESA Bydgoszcz SA Holding
137
Pojazdy Szynowe PESA Bydgoszcz S A Holding
138
select * from t1 where a like '%PESA %';
140
Pojazdy Szynowe PESA Bydgoszcz SA Holding
141
Pojazdy Szynowe PESA Bydgoszcz S A Holding
142
select * from t1 where a like '%PES%';
144
Techni Tapes Sp. z o.o.
145
Pojazdy Szynowe PESA Bydgoszcz SA Holding
147
Pojazdy Szynowe PESA Bydgoszcz S A Holding
148
PPUH PESKA-I Maria Struniarska
149
select * from t1 where a like '%PESKA%';
151
PPUH PESKA-I Maria Struniarska
152
select * from t1 where a like '%ESKA%';
154
PPUH PESKA-I Maria Struniarska
156
select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin;
157
_cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin
160
select 'andre%' like 'andre�%' escape '�';
161
'andre%' like 'andre�%' escape '�'
163
select _cp1251'andre%' like convert('andre�%' using cp1251) escape '�';
164
_cp1251'andre%' like convert('andre�%' using cp1251) escape '�'