[MySQL] 문자열 패턴 매칭 연산자 (REGEXP, LIKE)

MySQL에서 문자열 패턴 비교 연산자인 REGEXP, LIKE에 대해서 알아보고 자주 쓰이는 패턴에 대해서 정리해보고자 한다.

---

문자열 패턴 매칭 연산자

REGEXP 연산자

문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자로, `RLIKE(Regular Expression)`와 동일한 역할을 수행한다.

SELECT 'abc' REGEXP '^[x-z]';

• 연산자의 좌측: 비교 대상(문자열 값 또는 문자열 칼럼)
• 연산자의 우측: 검증하고자 하는 정규 표현식

`REGEXP` 연산자는 정규 표현식을 토대로 하는 패턴 매칭 연산을 제공하는데, POSIX 표준으로 구현되어 있어 POSIX 정규 표현식에서 사용하는 패턴 키워드를 그대로 사용할 수 있다.

POSIX?
- Portable Operating System Interface(이식 가능 운영 체제 인터페이스)의 약자
- 서로 다른 UNIX OS의 공통 API를 정리하여 이식성이 높은 유닉스 응용 프로그램을 개발하기 위한 목적으로 IEEE가 책정한 애플리케이션 인터페이스 규격

POSIX 정규 표현식

1️⃣ 기본 메타 문자

• `.`: 1개의 문자를 의미함 (캐리지 리턴 및 줄 바꿈은 제외)
  • `...`라고 표현했다면 3개의 문자로 구성된 문자열을 찾는 것
• `[]`: 문자 그룹을 표시하며, 괄호 안에 있는 어떤 문자든 하나와 일치함
  • `[xyz]` 또는 `[x-z]`라고 표현하면 `x`, `y`, `z` 중 하나인지 확인하는 것
• `()`: 문자열 그룹을 표시하며, 괄호 안에 있는 모든 문자와 일치함
  • `(xyz)`라고 표현하면 반드시 `"xyz"` 모두 있는지 확인하는 것
• `|`: OR을 의미하며, 여러 값 중 하나와 일치함
  • `abc|xyz`라고 표현하면 `"abc"` 이거나 `"xyz"` 인지 확인하는 것

2️⃣ 반복 수량자

• `{m,n}`: 해당 패턴이 최소 `m`회에서 최대 `n`회까지 반복됨
• `?`: 해당 패턴이 0회 또는 1회 반복됨 ➡️ `{0,1}`와 동일
• `+`: 해당 패턴이 1회 이상 반복됨 ➡️ `{1,}`와 동일
• `*`: 해당 패턴이 0회 또는 1회 이상 반복됨 (즉, 0회 이상 반복됨) ➡️ `{0,}`와 동일

3️⃣ 앵커 문자 (정규 표현식의 일치 위치를 고정하는 메타문자)

• `^`: 문자열이 해당 패턴으로 시작함 ➡️ 문자열의 시작 검사
• `$`: 문자열이 해당 패턴으로 끝남 ➡️ 문자열의 끝 검사

 

LIKE 연산자

• `REGEXP` 연산자보다는 훨씬 단순한 문자열 패턴 비교 연산자이지만 DBMS에서는 `LIKE` 연산자를 훨씬 더 많이 사용한다.
• `LIKE` 연산자는 정규 표현식을 검사하는 것이 아니라, 와일드카드를 사용하여 간단한 패턴 일치를 수행한다.

와일드카드(wildcard)

문자열 내에서 임의의 문자나 문자열을 대체하기 위해 사용되는 기호

• `%`: 0개 이상의 문자를 대체함
• `_`: 정확히 1개의 문자를 대체함

 

REGEXP vs LIKE

• 문자열 패턴 표현 방식
  • LIKE: 와일드카드('%', '_' 사용)
  • REGEXP: 정규 표현식 사용
• 패턴 일치 기준
  • LIKE: 비교 대상 문자열의 처음부터 끝까지 정확하게 일치해야 함 
  • REGEXP: 비교 대상 문자열의 일부만 일치해서 TRUE를 반환함
• 인덱스 레인지 스캔
  • LIKE: 와일드카드가 검색어 앞쪽에 있을 때는 인덱스 레인지 스캔 사용 가능 ➡️ 인덱스의 Left-most 특성 때문!
  • REGEXP: 인덱스 레인지 스캔 사용 불가 ➡️ WHERE 조건에 REGEXP 연산자를 사용한 조건을 단독으로 사용하면 성능상 좋지 않으므로, 가능하다면 데이터 조회 범위를 줄일 수 있는 조건과 함께 사용하자!

간단한 패턴 매칭에는 성능 상 이점이 있는 `LIKE` 연산자를 추천!
복잡하고 다양한 패턴 검색 시에는 깔끔하게 코드를 작성할 수 있는 `REGEXP` 연산자 추천!

 

자주 쓰이는 패턴

이메일 주소 유효성 검증

'^[A-Za-z0-9+-_.]+@[A-Za-z0-9-]+\.[A-Za-z0-9]+$'

💡 이메일 주소의 구조

 

👣 단계별 설명

select '[email protected]' REGEXP '^[a-zA-Z0-9+-_.]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'; -- 1
select '[email protected]' REGEXP '^[a-zA-Z0-9+-_.]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'; -- 1
select '[email protected]' REGEXP '^[a-zA-Z0-9+-_.]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'; -- 1

 

전화번호 유효성 검증

'^(01([0|1]?){3})-([0-9]{3,4})-([0-9]{4})'

• 위의 네 가지 형식 중 하나로 입력해야 한다.

SELECT '010-1234-5678' REGEXP '^(01([0|1]?){3})-([0-9]{3,4})-([0-9]{4})'; -- 1
SELECT '01012345678' REGEXP '^(01([0|1]?){3})-([0-9]{3,4})-([0-9]{4})';   -- 0
SELECT '019-1234-5678' REGEXP '^(01([0|1]?){3})-([0-9]{3,4})-([0-9]{4})'; -- 0
SELECT '010-134-5678' REGEXP '^(01([0|1]?){3})-([0-9]{3,4})-([0-9]{4})';  -- 1
SELECT '010-134-578' REGEXP '^(01([0|1]?){3})-([0-9]{3,4})-([0-9]{4})';   -- 0

 

아이디 검증

아이디가 영문 대소문자, 숫자, '_'(언더바)로만 구성된 5~10자가 맞는지 확인하고 싶다면?

'^[a-zA-Z0-9_]{5,10}$';

select 'coji123' regexp '^[a-zA-Z0-9_]{5,10}$';  -- 1
select 123456 regexp '^[a-zA-Z0-9_]{5,10}$';	-- 0
select 'coji_123' regexp '^[a-zA-Z0-9_]{5,10}$'; -- 1
select 'coji-123' regexp '^[a-zA-Z0-9_]{5,10}$'; -- 0
select 'coji' regexp '^[a-zA-Z0-9_]{5,10}$';	-- 0

 

문자열 검색

특정 문자(열)로 시작하는 문자열 검색 (전방 일치)

-- REGEXP
SELECT 'Hello World' REGEXP '^Hello'; -- 1

-- LIKE
SELECT 'Hello World' LIKE 'Hello%'; -- 1

특정 문자(열)로 끝나는 문자열 검색 (후방 일치)

-- REGEXP
SELECT 'Hello World' REGEXP 'World$'; -- 1

-- LIKE
SELECT 'Hello World' LIKE '%World'; -- 1

특정 문자(열)가 포함된 문자열 검색

-- REGEXP
SELECT 'Hello World' REGEXP 'lo'; -- 1

-- LIKE
SELECT 'Hello World' LIKE '%lo%'; -- 1

 

연습해 보자

정규표현식 연습해 보기

MySQL에서 지원하는 연산자뿐만 아니라 다양한 연산자를 제공하는 것에 주의!

https://regexone.com/lesson/introduction_abcs

RegexOne - Learn Regular Expressions - Lesson 1: An Introduction, and the ABCs

 

 

Ref

📘 Real MySQL 8.0 - 11. 쿼리 작성 및 최적화

https://tcpschool.com/mysql/mysql_operator_patternMatching

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/pattern-matching-conditions-posix.html

https://www.ibm.com/docs/ko/watson-explorer/12.0.x?topic=queries-posix-regular-expression-syntax-examples

https://codingspooning.tistory.com/entry/MySQL-%EC%A0%95%EA%B7%9C%ED%91%9C%ED%98%84%EC%8B%9D-%EA%B2%80%EC%83%89%ED%95%98%EA%B8%B0-REGEXP-LIKE

https://dojang.io/mod/page/view.php?id=2439