LIKE, REGEXP. Вывод данных по шаблону (маске) в MySQL

Тестовая БД

Для всех примеров далее будем использовать базу данных книжного магазина - Bookstore и таблицу books.

mysql> USE Bookstore;

Database changed
mysql> SELECT id, title, author, price, shelf_position FROM books;
+----+---------------------------+--------------------+--------+----------------+
| id | title                     | author             | price  | shelf_position |
+----+---------------------------+--------------------+--------+----------------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 | 023A12         |
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 | 003C05         |
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 | 004D11         |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 | 007A15         |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 | 004E08         |
|  6 | Война и мир               | Лев Толстой        | 341.00 | 020F01         |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 | 004D05         |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 | 001H10         |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 | 006A19         |
| 10 | Бесы                      | Фёдор Достоевский  | 212.00 | 028A03         |
+----+---------------------------+--------------------+--------+----------------+
10 rows in set (0.00 sec)

Оператор LIKE

LIKE используется для поиска записей, данные которых совпадают с заданным шаблоном.

Для создания шаблона используются два специальных символа.

  • % - представляет собой неопределенное количество любых символов.
  • _ - представляет только один любой символ.

Синтаксис LIKE

mysql> SELECT column1, column2, ... , columnN
    -> FROM table_name
    -> WHERE column LIKE 'pattern';

В таблице ниже представлены несколько возможных вариантов шаблона оператора LIKE.

Шаблон LIKE Описание
LIKE 'a%' Значение начинается с символа "a"
LIKE '%a' Значение заканчивается на символ "a"
LIKE '%a%' Значение имеет символ "a" в любом месте
LIKE '_a%' Значение имеет символ "a" на втором месте
LIKE 'a__' Значение состоит из 3 символов, первый "a"
LIKE 'a%d' Значение начинается с символа "a" и заканчивается на символ "d"

Рассмотрим несколько примеров.

Найдем все записи с книгами, в названии которых есть слово "Акция".

mysql> SELECT id, title, author, price, shelf_position
    -> FROM books
    -> WHERE title LIKE '%Акция%';
+----+---------------------------+--------------------+--------+----------------+
| id | title                     | author             | price  | shelf_position |
+----+---------------------------+--------------------+--------+----------------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 | 023A12         |
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 | 003C05         |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 | 007A15         |
+----+---------------------------+--------------------+--------+----------------+
3 rows in set (0.00 sec)

В колонке shelf_position представлен код местоположения книги, например код - 004E08 означает: 4 ряд, шкаф Е, полка 8. Найдем все записи с книгами, которые хранятся на полках шкафа D на 4 ряду. 

mysql> SELECT id, title, author, price, shelf_position
    -> FROM books
    -> WHERE shelf_position LIKE '004D__';
+----+---------------------------+--------------------+--------+----------------+
| id | title                     | author             | price  | shelf_position |
+----+---------------------------+--------------------+--------+----------------+
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 | 004D11         |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 | 004D05         |
+----+---------------------------+--------------------+--------+----------------+
2 rows in set (0.00 sec)

LIKE можно использовать с логическим оператором NOT, и находить записи, которые НЕ соответствуют заданному шаблону.

mysql> SELECT id, title, author, price, shelf_position
    -> FROM books
    -> WHERE author NOT LIKE 'Николай%';
+----+--------------------------------+-----------------------------------+--------+----------------+
| id | title                          | author                            | price  | shelf_position |
+----+--------------------------------+-----------------------------------+--------+----------------+
|  1 | Дубровский (Акция)             | Александр Пушкин                  | 230.00 | 023A12         |
|  3 | Мастер и Маргарита             | Михаил Булгаков                   | 263.00 | 004D11         |
|  5 | Преступление и наказание       | Фёдор Достоевский                 | 245.00 | 004E08         |
|  6 | Война и мир                    | Лев Толстой                       | 341.00 | 020F01         |
|  7 | Анна Каренина                  | Лев Толстой                       | 346.00 | 004D05         |
|  8 | Отцы и дети                    | Иван Тургенев                     | 371.00 | 001H10         |
|  9 | Собачье сердце                 | Михаил Булгаков                   | 232.00 | 006A19         |
| 10 | Бесы                           | Фёдор Достоевский                 | 212.00 | 028A03         |
+----+--------------------------------+-----------------------------------+--------+----------------+
10 rows in set (0.00 sec)

Оператор REGEXP

Поиск значений по шаблонам также возможен с помощью регулярных выражений, используя оператор REGEXP.

RLIKE является синонимом для оператора REGEXP.

Синтаксис REGEXP

mysql> SELECT column1, column2, ... , columnN
    -> FROM table_name
    -> WHERE column REGEXP 'pattern';

Ниже представлены несколько примеров использования регулярных выражений в MySQL.

Найдем все записи книг, имя автора которых начинается с букв "А" или "Л".

mysql> SELECT id, title, author, price, shelf_position
    -> FROM books
    -> WHERE author REGEXP '^(А|Л)';
+----+---------------------------+--------------------+--------+----------------+
| id | title                     | author             | price  | shelf_position |
+----+---------------------------+--------------------+--------+----------------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 | 023A12         |
|  6 | Война и мир               | Лев Толстой        | 341.00 | 020F01         |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 | 004D05         |
+----+---------------------------+--------------------+--------+----------------+
3 rows in set (0.00 sec)

Найдем все книги, в названии которых есть слово "война".

mysql> SELECT id, title, author, price, shelf_position
    -> FROM books
    -> WHERE title REGEXP '(В|в)ойна';
+----+----------------------+-----------------------+--------+----------------+
| id | title                | author                | price  | shelf_position |
+----+----------------------+-----------------------+--------+----------------+
|  6 | Война и мир          | Лев Толстой           | 341.00 | 020F01         |
+----+----------------------+-----------------------+--------+----------------+
1 row in set (0.00 sec)

Посмотрим какие книги находятся в шкафах на 004 ряду.

mysql> SELECT id, title, author, price, shelf_position
    -> FROM books
    -> WHERE shelf_position REGEXP '^004.*';
+----+---------------------------+--------------------+--------+----------------+
| id | title                     | author             | price  | shelf_position |
+----+---------------------------+--------------------+--------+----------------+
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 | 004D11         |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 | 004E08         |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 | 004D05         |
+----+---------------------------+--------------------+--------+----------------+
3 rows in set (0.00 sec)