sql like用法

测试表.
CREATE TABLE test_like (
  like_value  VARCHAR(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个字符
SELECT
  *
FROM
  test_like
WHERE
  like_value LIKE '1234_';
like_value
--------------------
12345
(1 行受影响)

% 表示仅仅匹配0个或多个字符
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 定义转义符
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