MySQL 문법 및 예제 정리 - LIKE, REGEXP, REGEXP_LIKE
MySQL에서 문자열 패턴 매칭은 LIKE(단순 와일드카드)와 REGEXP(정규 표현식) 두 축으로 나뉜다. 로그 파싱, 이메일·전화번호 검증, 특정 패턴 추출 같은 작업의 대부분은 이 두 연산자의 차이를 정확히 이해하는 것에서 출발한다.
| 구분 | LIKE |
REGEXP (정규식) |
|---|---|---|
| 문법 | %, _ 와일드카드 |
ICU 정규 표현식(MySQL 8.0.4부터). 그 이전은 Spencer 구현 |
| 인덱스 | 와일드카드로 시작하지 않는 상수 패턴이면 B-Tree 인덱스 범위 조건이 된다 | 인덱스 범위 조건 목록에 포함되지 않는다 |
| 활용 | 접두/접미사 매칭, 단순 포함 | 이메일 검증, 복잡한 문자 클래스, 그룹 캡처 |
| 함수 변형 | LIKE |
REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE |
성능은 “어느 쪽이 몇 배 빠르다”로 일반화할 수 있는 값이 아니라 조건에 달려 있다. LIKE는 패턴이 상수이고 와일드카드로 시작하지 않을 때만 B-Tree 인덱스의 범위 조건으로 처리되고1, '%abc'나 '%abc%'처럼 앞이 열려 있으면 인덱스를 타지 못해 결국 전 행을 확인한다. REGEXP는 범위 조건 목록에 아예 없으므로 컬럼에 인덱스가 걸려 있어도 기본적으로 행마다 정규식 매칭을 수행한다1. 그래서 두 연산자의 비교 기준은 “인덱스를 탈 수 있는 형태인가”와 “패턴이 얼마나 복잡한가” 두 가지이며, 아래 예제처럼 소량 데이터를 다루는 상황에서는 체감 차이가 크지 않다.
1. LIKE 연산자
LIKE연산자는 문자열이 특정 패턴과 일치하는지 여부를 확인할 때 사용한다.- 패턴 매칭은 주로 와일드카드(
%,_)를 사용해 처리한다. - 기본 문법:
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';
- 와일드카드:
%: 0개 이상의 문자와 일치한다._: 정확히 1개의 문자와 일치한다.- ESCAPE:
%·_자체를 리터럴로 찾으려면 ESCAPE 문자를 앞에 붙인다. 별도로 지정하지 않으면\가 기본 ESCAPE 문자다2.
- 예시:
# 설명: `customer_name`이 `'A'`로 시작하는 모든 행을 반환한다. `%`는 0개 이상의 문자를 대체한다.
SELECT * FROM Customers
WHERE customer_name LIKE 'A%';
# 설명: 두 번째 문자가 `'b'`인 모든 고객을 검색한다. `_`는 1개의 문자를 대체한다.
SELECT * FROM Customers
WHERE customer_name LIKE '_b%';
패턴 예시 요약
| 패턴 | 설명 |
|---|---|
'A%' |
'A'로 시작하는 모든 문자열 |
'%B' |
'B'로 끝나는 모든 문자열 |
'%abc%' |
'abc'를 포함하는 모든 문자열 |
'A_C%' |
`‘A’로 시작하고 3번째 문자가 ‘C’인 문자열 |
2. REGEXP 연산자
REGEXP(RLIKE와 동의어) 연산자는 정규 표현식(Regular Expressions)으로 복잡한 패턴 매칭을 수행한다.LIKE보다 복잡한 패턴을 지원하며, 앵커를 쓰지 않으면 패턴이 문자열 어디에 있든 일치로 취급한다(^·$로 위치를 고정한다)3.- 기본 문법:
SELECT column_name
FROM table_name
WHERE column_name REGEXP 'pattern';
- 정규 표현식 주요 패턴:
| 패턴 | 설명 |
|---|---|
. |
임의의 한 문자 |
^ |
문자열의 시작 |
$ |
문자열의 끝 |
[abc] |
a, b, c 중 하나와 일치 |
[^abc] |
a, b, c가 아닌 문자와 일치 |
[a-z] |
소문자 알파벳 범위 내의 문자와 일치 |
[0-9] |
숫자와 일치 |
* |
0개 이상의 문자와 일치 |
+ |
1개 이상의 문자와 일치 |
{n} |
정확히 n개의 문자와 일치 |
{n,} |
n개 이상의 문자와 일치 |
{n,m} |
n개 이상 m개 이하 문자와 일치 |
(abc\|def) |
abc 또는 def 중 하나와 일치 |
() |
패턴을 그룹화하여 하나의 단위로 처리 |
\d |
숫자와 일치 |
\D |
숫자가 아닌 문자와 일치 |
\w |
단어 문자와 일치 |
\W |
단어 문자가 아닌 문자와 일치 |
\s |
공백 문자와 일치(스페이스, 탭, 줄바꿈) |
\S |
공백이 아닌 문자와 일치 |
\\. |
. 문자 자체와 일치 |
동작 조건과 제약
- 정규식 엔진은 MySQL 8.0.4에서 ICU로 교체되었다. ICU는 Unicode를 온전히 지원하고 multibyte safe하지만, 8.0.4 이전의 Spencer 구현은 바이트 단위로 비교했고
\d·\w같은 ICU 문법을 해석하지 못했다3. 8.0.4 미만 버전을 함께 운영한다면 패턴을 그대로 옮겨 쓸 수 없다. - 단어 경계는 ICU에서
\b로 쓴다. Spencer의[[:<:]]·[[:>:]]표기는 ICU에서 지원되지 않는다3. - 대소문자 구분은 문자열의 collation을 따르며,
REGEXP_LIKE()의match_type에c/i를 넘겨 덮어쓸 수 있다. 단 두 인자 중 하나라도 이진 문자열이면i를 줘도 대소문자를 구분한다3. -
MySQL 문자열 리터럴은 C 이스케이프 문법을 쓰므로, 정규식에 리터럴 백슬래시를 넣으려면 SQL에서는
'\\.'처럼 두 번 적어야 한다(NO_BACKSLASH_ESCAPES모드에서는 한 번)3. 아래 예제의\\.가 이 이유 때문이다. - 예시:
# 설명: `customer_name`이 `'A'`로 시작하는 모든 행을 반환한다. `^`는 문자열의 시작을 의미한다.
SELECT * FROM Customers
WHERE customer_name REGEXP '^A';
# 설명: `product_name`이 `A`, `B`, 또는 `C`로 시작하는 모든 제품을 검색한다.
SELECT * FROM Products
WHERE product_name REGEXP '^[A-C]';
# 설명: 이메일 주소 형식(알파벳 문자로 시작하고, `@` 뒤에 `com`, `org`, `net` 도메인이 붙는 이메일)과 일치하는 행을 검색한다.
SELECT * FROM Employees
WHERE email REGEXP '[a-z]+@[a-z]+\\.(com|org|net)';
3. REGEXP_LIKE
REGEXP_LIKE()는 MySQL 8.0부터 추가된 함수로, 정규 표현식을 기반으로 문자열이 주어진 패턴과 일치하는지 확인한다.LIKE와 유사하지만 정규 표현식을 받으므로 더 복잡한 패턴을 처리할 수 있고, 선택 인자match_type으로 대소문자 처리 방식을 지정할 수 있다3.- 기본 문법:
SELECT column_name
FROM table_name
WHERE REGEXP_LIKE(column_name, 'pattern'[, match_type]);
- 예시:
- 사용자 이름이 알파벳과 숫자만 포함된 경우
# 설명: `username`이 알파벳과 숫자로만 이루어진 경우를 찾는다.
SELECT * FROM Users WHERE REGEXP_LIKE(username, '^[a-zA-Z0-9]+$');
- 이메일이 특정 도메인으로 끝나는 경우
# 설명: 이메일이 `gmail.com` 또는 `yahoo.com`으로 끝나는 행을 반환한다. `\\.`는 점을 의미한다.
SELECT * FROM Employees WHERE REGEXP_LIKE(email, '@(gmail|yahoo)\\.com$');
4. REGEXP_REPLACE
REGEXP_REPLACE()(MySQL 8.0부터)는 문자열에서 정규 표현식과 일치하는 부분을 다른 문자열로 치환한다.- 기본 문법:
string: 검색할 문자열pattern: 정규 표현식 패턴replacement: 치환할 문자열
REGEXP_REPLACE(string, pattern, replacement)
- 예시:
- 특정 패턴을 제거하기
# 설명: 숫자가 아닌 문자를 모두 빈 문자열로 치환(제거)해 `1234567890`을 반환한다.
SELECT REGEXP_REPLACE('123-456-7890', '[^0-9]', '') AS phone_number;
- 이메일의 도메인 변경하기
# 설명: 이메일 주소에서 도메인 부분을 `@newdomain.com`으로 치환한다.
SELECT REGEXP_REPLACE(email, '@.*$', '@newdomain.com') AS new_email
FROM Employees;
5. REGEXP_INSTR
REGEXP_INSTR()(MySQL 8.0부터)는 문자열에서 정규 표현식 패턴이 처음 등장하는 위치를 반환한다.- 이 함수는
INSTR()와 비슷하지만 정규 표현식을 지원한다. - 기본 문법:
string: 검색할 문자열pattern: 찾고자 하는 정규 표현식 패턴
REGEXP_INSTR(string, pattern)
- 예시:
- 문자열에서 숫자가 처음 등장하는 위치 찾기
# 설명: 첫 번째 숫자가 등장하는 위치를 반환한다. `Contact`가 1~7번째이므로 결과는 8이다.
SELECT REGEXP_INSTR('Contact123 for details', '[0-9]') AS first_digit_position;
- 이메일 주소에서 도메인 부분의 시작 위치 찾기
# 설명: 이메일 주소에서 도메인이 시작되는 위치를 반환한다.
SELECT REGEXP_INSTR(email, '@[a-zA-Z]+\\.com') AS domain_position
FROM Employees;
6. REGEXP_SUBSTR
REGEXP_SUBSTR()(MySQL 8.0부터) 함수는 문자열에서 정규 표현식 패턴과 일치하는 부분 문자열을 반환한다.- 기본 문법:
string: 검색할 문자열pattern: 찾고자 하는 정규 표현식 패턴
REGEXP_SUBSTR(string, pattern)
- 예시:
- 문자열에서 첫 번째로 등장하는 이메일 추출
# 설명: 이메일 주소 형식과 일치하는 첫 번째 부분 문자열을 추출한다.
SELECT REGEXP_SUBSTR('Contact us at support@example.com', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}') AS email;
- 문자열에서 첫 번째로 등장하는 숫자 추출
# 설명: 숫자 부분을 추출해 `12345`를 반환한다.
SELECT REGEXP_SUBSTR('The order number is 12345', '[0-9]+') AS order_number;
댓글남기기