MySQL Регулярні вирази (Regexp)
Маркус Аллен
⚡ Розумний підсумок
MySQL Регулярні вирази (REGEXP) зіставляють значення стовпців із гнучкими шаблонами, які не можуть бути виражені символами підстановки. Тут пояснюється синтаксис REGEXP, синонім RLIKE, кожен підтримуваний метасимвол, виправлені приклади запитів до бази даних myflixdb та функції регулярних виразів, додані в MySQL 8.0.
Які MySQL регулярні вирази?
MySQL регулярні вирази допомогти вам знайти дані, що відповідають складним критеріям. Регулярний вираз – це шаблон, який описує форму значення, яке ви шукаєте, а не саме значення.
Якщо ви вже працювали з MySQL макетів, ви можете запитати, чому варто вивчати регулярні вирази, якщо LIKE дає схожі результати. Відповідь — виразна сила: шаблони підстановки пропонують лише два символи, тоді як регулярні вирази описують діапазони символів, альтернативи, повторення та позиції слів в одному шаблоні.
Після визначення мети, у наступному розділі представлено синтаксис, який ви використовуватимете в кожному запиті REGEXP.
Базовий синтаксис REGEXP
Основний синтаксис регулярного виразу виглядає наступним чином.
SELECT * FROM table_name WHERE fieldname REGEXP 'pattern';
ТУТ:
- «Оператор SELECT» є стандартом Оператор SELECT.
- "WHERE ім'я поля" — це назва стовпця, для якого виконується регулярний вираз.
- «Шаблон REGEXP» — REGEXP — це оператор регулярного виразу, а «pattern» представляє шаблон, який потрібно знайти. RLIKE це синонім REGEXP і повертає ті самі результати. Щоб уникнути плутанини з оператором LIKE, краще використовувати REGEXP.
Давайте тепер розглянемо практичний приклад.
SELECT * FROM `movies` WHERE `title` REGEXP 'code';
Наведений вище запит шукає всі назви фільмів, що містять слово «code». Неважливо, де слово «code» знаходиться на початку, посередині чи в кінці назви. Якщо назва містить шаблон, повертається рядок.
Зіставлення початку значення зі списком символів
Припустимо, нам потрібні фільми, назви яких починаються з a, b, c або d, а потім будь-яка кількість інших символів. Ми поєднуємо список символів з метасимволом каретки, щоб досягти цього результату.
SELECT * FROM `movies` WHERE `title` REGEXP '^[abcd]';
Виконання наведеного вище сценарію в MySQL Верстак з базою даних myflixdb дає нам такі результати.
| movie_id | назву | директор | year_released | category_id |
|---|---|---|---|---|
| 4 | Code Ім'я Блек | Едгар Джимз | 2010 | NULL |
| 5 | Татові маленькі дівчатка | NULL | 2007 | 8 |
| 6 | Ангели та демони | NULL | 2007 | 6 |
| 7 | Давінці Code | NULL | 2007 | 6 |
У шаблоні '^[abcd]' каретка (^) вимагає, щоб збіг починався з початку значення, а список символів [abcd] приймає лише назви, перша літера яких a, b, c або d. Порівняння не враховує регістр за стандартним сортуванням, тому «Code Повертається «Ім’я Блек».
Виключення символів зі списку заперечених символів
Давайте тепер змінимо скрипт і заперечимо список символів, щоб побачити, які рядки повертаються.
SELECT * FROM `movies` WHERE `title` REGEXP '^[^abcd]';
Виконання наведеного вище сценарію в MySQL Аналіз Workbench на базі даних myflixdb дає нам такі результати.
| movie_id | назву | директор | year_released | category_id |
|---|---|---|---|---|
| 1 | Пірати Карибського моря 4 | Роб Маршалл | 2011 | 1 |
| 2 | Забути Сару Маршал | Ніколас Столлер | 2008 | 2 |
| 3 | X-Men | 2008 | ||
| 9 | Honey moonERS | Джон Шульц | 2005 | 8 |
| 16 | 67% винний | 2012 | ||
| 17 | Великий диктатор | Чалі Чаплі | 1920 | 7 |
| 18 | зразок фільму | анонімний | 8 | |
| 19 | фільм 3 року | Джон Браун | 1920 | 8 |
Усередині списку символів каретка змінює значення: '^[^abcd]' все ще закріплює збіг на початку, тоді як [^abcd] тепер виключає кожен заголовок, який починається з одного з доданих символів.
У цих двох прикладах використовуються лише анкори та списки символів. У наступному розділі розглядається повний набір метасимволів.
Метасимволи регулярного виразу
Наведені вище приклади показують найпростішу форму регулярного виразу. Метасимволи дозволяють точно налаштувати пошук за шаблоном: вони виражають повторення, альтернативи, діапазони та позиції. У таблиці нижче перелічено всі метасимволи, що підтримуються MySQL Оператор REGEXP з виправленим прикладом для кожного з них.
| колісниця | Опис | Приклад | |
|---|---|---|---|
| * | Команда зірочка (*) збігається з нулем (0) або більше екземплярами один символ що передує цьому. | SELECT * FROM movies WHERE title REGEXP 'da*'; збігається з літерою «d», за якою йде нуль або більше символів «a», тобто Да Вінчі Code і «Daddy's Little Girls» відповідають вимогам. Використовуйте «da+», коли літера «a» дійсно має бути присутня. | |
| + | Команда плюс (+) збігається з одним або кількома екземплярами символу, що стоїть перед ним. | SELECT * FROM `files` WHERE `title` REGEXP 'mon+'; повертає всі фільми, що містять слово «mon», а потім один або кілька символів «n». Наприклад, «Ангели та демони». | |
| ? | Команда знак питання (?) збігається з нулем (0) або одним екземпляром символу, що стоїть перед ним. | SELECT * FROM `categories` WHERE `category_name` REGEXP 'com?'; зіставляє «co» з необов’язковою літерою «m». Наприклад, комедія та романтична комедія. | |
| . | Команда крапка (.) збігається з будь-яким окремим символом, окрім нового рядка. | SELECT * FROM movies WHERE `year_released` REGEXP '200.'; відображає всі фільми, випущені протягом року, починаючи з «200», а потім будь-який окремий символ. Наприклад, 2005, 2007, 2008. | |
| [abc] | Команда список символів [abc] збігається з будь-яким із символів, що містяться в ньому. | SELECT * FROM `files` WHERE `title` REGEXP '[vwxyz]'; видає всі фільми, що містять будь-якого персонажа з «vwxyz». Наприклад, Люди Ікс та Да Вінчі Code. | |
| [^abc] | Команда заперечений список [^abc] збігається з будь-яким символом, окрім тих, що містяться в ньому. | SELECT * FROM `files` WHERE `title` REGEXP '^[^vwxyz]'; видає всі фільми, назва яких не починається з літери «vwxyz». | |
| [AZ] | Команда діапазон [AZ] збігається з будь-якою літерою верхнього регістру. | SELECT * FROM `members` WHERE `postal_address` REGEXP '[AZ]'; відображає всіх учасників, чия поштова адреса містить літеру від A до Z. Наприклад, Джанет Джонс з номером членства 1. | |
| [az] | Команда діапазон [az] збігається з будь-якою літерою нижнього регістру. | SELECT * FROM `members` WHERE `postal_address` REGEXP '[az]'; повертає всіх учасників, чия поштова адреса містить літеру від a до z. Зверніть увагу, що сортування за замовчуванням не враховує регістр, тому цей діапазон також відповідає великим літерам. | |
| [0-9] | Команда діапазон [0-9] відповідає будь-якій цифрі від 0 до 9. | SELECT * FROM `учасники` WHERE `номер_контакту` REGEXP '[0-9]'; відображає всіх учасників, чий контактний номер містить хоча б одну цифру. Наприклад, Роберт Філ. | |
| ^ | Команда вставка (^) прив'язує збіг до початку значення. | SELECT * FROM `files` WHERE `title` REGEXP '^[cd]'; видає всі фільми, назва яких починається на літеру «c» або «d». Наприклад Code Назвіть Блек, Татових маленьких дівчаток та Да Вінчі Code. | |
| $ | Команда знаки долара ($) прив'язує збіг до кінця значення. | SELECT * FROM `movies` WHERE `title` REGEXP 'code$'; видає всі фільми, назва яких закінчується на «код». Наприклад, Да Вінчі Code. | |
| | | Команда вертикальна смуга (|) ізолює альтернативи. | SELECT * FROM `files` WHERE `title` REGEXP '^[cd]|^[u]'; видає всі фільми, назва яких починається на «c», «d» або «u». Наприклад Code Ім'я Блек, Да Вінчі Code, та Підземний світ – AwakenIng. | |
| \b | Команда межа слова (\b) збігається з початком або кінцем слова. Він замінює старіші маркери [[:<:]] та [[:>:]], які MySQL 8.0 видалено. | SELECT * FROM `movies` WHERE `title` REGEXP '\\bfor'; подано всі фільми, слово яких починається на «for». Наприклад, «For Forgetting Sarah Marshal». On MySQL 5.7 еквівалентним шаблоном є '[[:<:]]for'. | |
| [[:клас:]] | Команда клас персонажа збігається з іменованою групою символів: [[:alpha:]] для літер, [[:space:]] для пробілів, [[:punct:]] для розділових знаків та [[:upper:]] для великих літер. Зверніть увагу на подвійний квадратні дужки. | SELECT * FROM `movies` WHERE `title` REGEXP '^[[:alpha:][:space:]]+$'; видає всі фільми, назва яких містить лише літери та пробіли. Наприклад, «Забувши Сару Маршал», тоді як «Пірати Карибського моря 4» пропущено через цифру. | |
Зворотна коса риска (\) – це керуючий символ. Тому що MySQL спочатку аналізує рядок, а потім шаблон, зворотна скісну риску (\\) має бути записана як подвійна зворотна скісну риску (\\) всередині шаблону REGEXP.
⚠️ Попередження щодо версії: MySQL У версії 8.0.4 старий механізм регулярних виразів замінено бібліотекою ICU. У цьому випуску маркери слів [[:<:]] та [[:>:]] були видалені, тому шаблони, скопійовані зі старого матеріалу, не працюють із «синтаксичною помилкою». MySQL 8.0. Використовуйте \b замість цього.
Тепер, коли кожен метасимвол визначено, виникає справедливе питання: коли REGEXP має замінити простіший оператор LIKE?
REGEXP проти LIKE: який з них слід використовувати?
Обидва оператори фільтрують рядки за шаблоном, але вирішують різні проблеми. LIKE розуміє лише два символи, тоді як REGEXP розуміє повний набір метасимволів, показаний вище. Ця можливість має свою ціну, тому вибір є компромісом, а не перевагою.
| критерій | LIKE | REGEXP |
|---|---|---|
| Символи візерунків | Тільки % та _ | Anchors, діапазони, чергування, квантифікатори, класи символів |
| Типове використання | Пошук за префіксом, суфіксом та запитом «містить» | Валідація, кілька альтернатив, зіставлення з урахуванням позиції |
| Використання індексу | Можливо, коли шаблон не починається з % | Ніколи не використовує індекс |
| Повернене значення | ПРАВДА чи ХИБНІСТЬ | 1 або 0, та NULL, коли будь-який з операндів має значення NULL |
Оберіть LIKE для простого зіставлення, оскільки він добре читається і все ще може використовувати індекс. Оберіть REGEXP, коли один шаблон має виражати кілька правил одночасно, наприклад, «починається з c або d і закінчується цифрою». У великих таблицях спочатку звузьте рядки за допомогою індексованої умови, а потім застосуйте REGEXP до меншого набору.
MySQL 8.0 функції регулярних виразів
Оператор REGEXP відповідає лише на одне питання: чи відповідає значення шаблону? MySQL У версії 8.0 додано чотири функції, які йдуть далі та дозволяють вам знаходити, наприкладtract та перепишіть відповідний текст. Кожен з них приймає необов'язковий аргумент match_type, де 'c' примусово використовує порівняння з урахуванням регістру, а 'i' — без урахування регістру.
- REGEXP_LIKE(вираз, шаблон) повертає 1, коли значення відповідає шаблону. Це функціональна форма оператора REGEXP, а аргумент match_type явно вказує чутливість до регістру.
- REGEXP_INSTR(вираз, шаблон) повертає позицію першого символу збігу або 0, якщо шаблон не знайдено.
- REGEXP_SUBSTR(вираз, шаблон) повертає сам відповідний підрядок, що корисно для вилучення року, коду або числа з довшого текстового значення.
- REGEXP_REPLACE(вираз, шаблон, заміна) повертає значення з кожним заміненим збігом, тому може очищати дані всередині SQL-запит на оновлення.
SELECT title, REGEXP_SUBSTR(title, '[0-9]+') AS number_in_title FROM `movies` WHERE REGEXP_LIKE(title, '[0-9]');
Наведений вище запит повертає кожну назву фільму, яка містить цифру, разом із самими цифрами. MySQL 5.7 ці функції недоступні, тому оператор REGEXP залишається єдиним варіантом.
