測(cè)試表.
CREATE TABLE test_like (
like_valueVARCHAR(20)
);
INSERT INTO test_like
SELECT '1234567890' UNION ALL
SELECT '12345' UNION ALL
SELECT 'abca' UNION ALL
SELECT 'adcb' UNION ALL
SELECT 'abcc' UNION ALL
SELECT 'abcd' UNION ALL
SELECT 'abcde' UNION ALL
SELECT 'xyz 10% OFF' UNION ALL
SELECT 'xyz 20% OFF' UNION ALL
SELECT 'xyz_123_abc'
_ 表示僅僅匹配1個(gè)字符
SELECT
*
FROM
test_like
WHERE
like_value LIKE '1234_';
like_value
--------------------
12345
(1 行受影響)
% 表示僅僅匹配0個(gè)或多個(gè)字符
SELECT
*
FROM
test_like
WHERE
like_value LIKE '12345%';
like_value
--------------------
1234567890
12345
(2 行受影響)
使用模式匹配
SELECT
*
FROM
test_like
WHERE
like_value LIKE 'abc[ad]';
like_value
--------------------
abca
abcd
(2 行受影響)
SELECT
*
FROM
test_like
WHERE
like_value LIKE '[1a]%[5e]';
like_value
--------------------
12345
abcde
(2 行受影響)
使用 ESCAPE 定義轉(zhuǎn)義符
SELECT
*
FROM
test_like
WHERE
like_value LIKE '%!%%' ESCAPE '!';
like_value
--------------------
xyz 10% OFF
xyz 20% OFF
(2 行受影響)
SELECT
*
FROM
test_like
WHERE
like_value LIKE '%!_%' ESCAPE '!';
like_value
--------------------
xyz_123_abc
(1 行受影響)