1. 소개
MySQL 정규 표현식 (REGEXP)이란?
MySQL 정규 표현식은 데이터베이스 내 문자열을 유연하게 검색하고 조작할 수 있는 강력한 도구입니다. 표준 문자열 검색으로는 구현하기 어려운 고급 패턴 매칭을 가능하게 하여, 특정 형식이나 조건에 맞는 데이터를 추출할 수 있습니다.
예를 들어, “특정 문자로 시작하는 이름”이나 “숫자만 포함하는 코드”와 같은 데이터를 손쉽게 추출할 수 있습니다. 이러한 기능은 데이터 정제 및 복잡한 검색 조건을 처리할 때 특히 유용합니다.
MySQL에서 정규 표현식을 사용할 때의 장점
- 복잡한 검색 조건 지원
- 표준 LIKE 연산자로는 처리할 수 없는 복잡한 문자열 패턴을 지정할 수 있습니다.
- 데이터의 일괄 추출 및 교체
- 예를 들어, 특정 형식에 맞는 데이터만 추출하거나 문자열의 일부를 교체할 수 있습니다.
- MySQL 8.0 이후 강화된 기능
- REGEXP_LIKE, REGEXP_SUBSTR 등 새로운 함수가 추가되어 보다 유연한 작업이 가능해졌습니다.
본 문서의 목적
이 문서는 MySQL 정규 표현식 (REGEXP)의 기본 사용법부터 고급 예제 및 중요한 고려 사항까지 모두 설명합니다. 초보자이든 반전문가이든 실제 적용에 도움이 되는 실용적인 인사이트를 제공할 것입니다.
다음 섹션에서는 MySQL에서 정규 표현식의 기본 개념을 설명합니다.
2. MySQL 정규 표현식 기본
REGEXP 연산자는 무엇인가?
MySQL에서 REGEXP 연산자는 정규 표현식을 사용하기 위해 제공됩니다. 이 연산자는 값이 지정된 패턴과 일치하는지를 판단합니다. 또한 RLIKE는 REGEXP의 별칭으로 동작합니다.
다음 예제는 문자열이 패턴 “abc”와 일치하는지를 확인합니다.
SELECT * FROM users WHERE name REGEXP 'abc';
REGEXP 연산자의 기본 구문
정규 표현식을 이용한 검색의 기본 구문은 다음과 같습니다.
SELECT * FROM table_name WHERE column_name REGEXP 'pattern';
일반적인 REGEXP 패턴 목록
| Symbol | Description | Example |
|---|---|---|
^ | Matches the beginning of a line | ^abc → Strings that start with “abc” |
$ | Matches the end of a line | abc$ → Strings that end with “abc” |
. | Matches any single character | a.c → Matches “abc”, “adc”, etc. |
| | OR (matches either pattern) | abc|xyz → Matches “abc” or “xyz” |
[] | Matches any one of the specified characters | [abc] → Matches “a”, “b”, or “c” |
* | Matches zero or more repetitions | ab*c → Matches “ac”, “abc”, “abbc”, etc. |
REGEXP와 LIKE의 차이점
| Feature | LIKE | REGEXP |
|---|---|---|
| Flexibility | Supports only wildcards (% and _) | Supports advanced pattern matching |
| Performance | Fast | May be slightly slower for complex patterns |
실용 예제: REGEXP를 이용한 검색
예제 1: 유효한 이메일 형식 검색
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
예제 2: 숫자만 포함하는 필드 검색
SELECT * FROM orders WHERE order_id REGEXP '^[0-9]+$';
요약
이 섹션에서는 MySQL에서 REGEXP 연산자의 기본 사용법과 패턴들을 다루었습니다. 이를 통해 간단한 검색부터 고급 패턴 매칭까지 유연한 데이터 작업이 가능해집니다.
3. MySQL 8.0에 추가된 정규 표현식 함수
REGEXP_LIKE() – 정규 표현식으로 매치 여부 확인
REGEXP_LIKE(string, pattern [, flags])
예시:
SELECT REGEXP_LIKE('abcdef', 'abc');
결과: 1 (매치됨)
REGEXP_INSTR() – 매치 위치 찾기
REGEXP_INSTR(string, pattern [, start_position, occurrence, flags, return_type])
예시:
SELECT REGEXP_INSTR('abcdef', 'cd');
결과: 3
REGEXP_SUBSTR() – 매치된 부분 문자열 추출
REGEXP_SUBSTR(string, pattern [, start_position, occurrence, flags])
예시:
SELECT REGEXP_SUBSTR('abc123def', '[0-9]+');
결과: 123
REGEXP_REPLACE() – 정규 표현식으로 교체
REGEXP_REPLACE(string, pattern, replacement [, start_position, occurrence, flags])
예시:
SELECT REGEXP_REPLACE('Item123Price456', '[0-9]', '#');
결과: Item###Price###
요약
MySQL 8.0에 추가된 정규 표현식 함수들은 상세하고 유연한 문자열 작업을 가능하게 합니다. 이를 효과적으로 활용하면 데이터를 보다 효율적으로 추출하고 변환할 수 있습니다. 
4. 정규 표현식 실무 활용 사례
특정 패턴과 일치하는 데이터 검색
예시 1: 이메일 주소 형식 감지
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
예시 2: 전화번호 형식 감지
SELECT * FROM contacts WHERE phone REGEXP '^[0-9]{3}-[0-9]{4}-[0-9]{4}$';
문자열 추출
예시 1: 숫자 부분 추출
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+');
결과: 123
데이터 교체
예시 1: 숫자를 “#” 로 교체
SELECT REGEXP_REPLACE('Item123Price456', '[0-9]', '#');
결과: Item###Price###
데이터 검증 및 정리
예시 1: 잘못된 이메일 주소 감지
SELECT * FROM users WHERE email NOT REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
요약
이 예시들을 통해 검색, 추출, 교체 및 검증과 같은 작업을 효율적으로 처리할 수 있습니다.
5. 중요 고려 사항 및 모범 사례
멀티바이트 문자 처리 (전각 문자)
MySQL 정규식은 기본적으로 바이트 단위로 평가되므로, 일본어와 같은 멀티바이트 문자를 처리할 때 특별한 주의가 필요합니다.
해결책:
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
성능에 미치는 영향
문제: 정규식은 복잡한 처리를 포함하므로 대규모 데이터셋을 검색할 때 성능이 저하될 수 있습니다.
해결책:
SELECT * FROM users WHERE email LIKE '%@example.com' AND email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
ReDoS(정규식 서비스 거부) 방지
문제: 악의적인 패턴이 시스템 부하를 과도하게 유발할 수 있습니다.
해결책:
- 가능한 경우 단순한 패턴을 사용하십시오.
- 입력 검증을 강화하십시오.
- 쿼리 실행 시간을 모니터링하십시오.
버전 호환성 확인
새로운 정규식 함수는 MySQL 8.0 이전 버전에서는 사용할 수 없습니다. 구현하기 전에 항상 환경 버전을 확인하십시오.
스테이징 환경에서 테스트
쿼리 동작 및 성능을 사전에 테스트하고, 엣지 케이스와 잘못된 값이 어떻게 처리되는지 확인하십시오.
요약
성능과 보안을 모두 고려하면서 정규식을 안전하고 효율적으로 사용하기 위해 이러한 모범 사례를 기억하십시오.
6. 결론
핵심 요점
- 기본 연산 및 정규식 패턴을 학습하면 간단한 검색부터 복잡한 데이터 추출까지 모든 작업을 처리할 수 있습니다.
- MySQL 8.0에서 도입된 정규식 함수는 더욱 유연한 작업을 가능하게 합니다.
- 실용적인 예시를 활용하면 실제 데이터 조작의 효율성이 향상됩니다.
- 모범 사례를 적용하면 안전하고 고성능의 쿼리를 보장할 수 있습니다.
MySQL 정규식 사용의 장점
- 고급 검색 기능: 단순 문자열 검색으로는 정의하기 어려운 조건을 손쉽게 지정할 수 있습니다.
- 데이터 처리 효율성 향상: 추출, 교체 및 검증을 SQL 내에서 직접 수행할 수 있습니다.
- 다양한 적용 분야: 데이터 정제부터 로그 분석까지 다양한 작업에 활용할 수 있습니다.
학습 및 적용 지속 방법
- 실제 데이터셋을 사용해 쿼리를 연습하여 이해도를 높이십시오.
- 최신 버전 기능을 적극 활용해 성능을 최적화하십시오.
- 정기적으로 쿼리를 검토하여 보안과 속도를 유지하십시오.
최종 생각
MySQL 정규식을 마스터하면 운영 효율성을 높이고 데이터 분석 역량을 강화할 수 있습니다.
7. 자주 묻는 질문 (FAQ)
Q1. MySQL에서 REGEXP와 LIKE의 차이점은 무엇인가요?
A. REGEXP는 고급 패턴 매칭을 지원하고, LIKE는 주로 부분 문자열 매칭에 사용됩니다.
SELECT * FROM users WHERE email LIKE '%example.com';
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
Q2. 성능을 어떻게 향상시킬 수 있나요?
A.
- 필터 조건을 미리 적용합니다.
- 인덱스를 효율적으로 사용합니다.
- 쿼리를 간단하고 최적화된 상태로 유지합니다.
Q3. 멀티바이트 문자를 어떻게 처리하나요?
A. UTF-8 지원을 구성합니다.
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Q4. 정규식을 사용한 교체 예시를 보여줄 수 있나요?
A. 숫자를 “#” 로 교체합니다.
SELECT REGEXP_REPLACE('Item123Price456', '[0-9]', '#');
Q5. 날짜 형식을 변환하는 쿼리는 무엇인가요?
A. “YYYY/MM/DD” 를 “YYYY-MM-DD” 로 변경합니다.
SELECT REGEXP_REPLACE('2023/12/20', '/', '-');
Q6. REGEXP를 사용해 여러 조건을 지정하려면 어떻게 해야 하나요?
A. 파이프 기호 (|) 를 사용합니다.
SELECT * FROM products WHERE name REGEXP 'phone|tablet';
요약
FAQ 섹션에서는 일반적인 질문들을 다루고 실용적인 쿼리 예시를 제공했습니다.
