MySQL 정규 표현식 (REGEXP) 가이드: 구문, MySQL 8 함수, 예제 및 모범 사례

1. 소개

MySQL에서 정규 표현식 사용하기

MySQL은 데이터를 검색하고 조작하기 위한 강력한 데이터베이스 관리 시스템이며, 정규 표현식 (REGEXP) 을 지원하여 보다 유연한 검색을 가능하게 합니다. 정규 표현식을 사용하면 특정 패턴과 일치하는 데이터를 효율적으로 추출하고, 데이터를 포맷하거나 검증할 수 있습니다.

MySQL에서 정규 표현식을 사용할 때의 장점

MySQL 정규 표현식을 사용하면 다음과 같은 장점이 있습니다:

• 고급 검색 : 특정 문자열이나 패턴을 포함하는 데이터를 손쉽게 추출합니다.
• 데이터 무결성 검사 : 입력 검증에 유용합니다(예: 이메일 주소 형식 검증).
• LIKE 연산자보다 강력 : 와일드카드(%와 _)를 사용하는 LIKE와 달리, REGEXP는 보다 복잡한 검색 조건을 유연하게 정의할 수 있습니다.

이 문서의 목적 및 구성

이 문서는 MySQL에서 정규 표현식의 기본부터 고급 활용, 성능 최적화, MySQL 8.0 이후에 도입된 새로운 정규 표현식 함수까지 모두 설명합니다. 실용적인 예제를 통해 초보자도 이해하기 쉽도록 구성되었습니다.

문서 구성:

1. MySQL 정규 표현식 기본 및 문법
2. MySQL 8.0 이후에 추가된 정규 표현식 함수
3. 실용 예제(검증, 데이터 추출, 포맷 변환)
4. 정규 표현식 사용 시 주요 고려 사항(성능, 보안)
5. FAQ(자주 묻는 질문)
6. 요약

2. MySQL에서 정규 표현식 기본 및 문법

MySQL에서 정규 표현식 사용하는 방법

MySQL에서는 REGEXP 연산자(또는 RLIKE)를 사용해 정규 표현식을 다룹니다. LIKE 연산자와 유사하게, 열 값이 지정된 정규식 패턴과 일치하는지를 판단하는 데 사용됩니다.

기본 문법

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

또는

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE는 REGEXP의 별칭이며, 두 연산자는 동일하게 동작합니다.

예시
예를 들어, 이름에 문자 “山”이 포함된 레코드를 검색하려면:

SELECT * FROM users WHERE name REGEXP '山';

기본 정규 표현식 패턴

MySQL 정규 표현식에서는 다음과 같은 패턴을 사용할 수 있습니다.

LIKE 연산자와의 차이점

MySQL은 간단한 패턴 매칭에 주로 사용되는 LIKE 연산자도 제공합니다. 그러나 REGEXP는 LIKE보다 더 강력하며, 보다 복잡한 조건으로 검색할 수 있습니다.

예시 1: LIKE 연산자 사용

SELECT * FROM users WHERE name LIKE '%山%';

• LIKE를 사용하면 山이 포함된 데이터를 검색할 수 있지만, 단순 와일드카드(%와 _)만 사용할 수 있습니다.

예시 2: REGEXP 연산자 사용

SELECT * FROM users WHERE name REGEXP '^山';

• REGEXP를 사용하면 이름이 山으로 시작하는 데이터만 추출할 수 있습니다.

여러 패턴 지정 방법

|를 사용하면 OR 조건으로 여러 패턴을 지정할 수 있습니다.

예시: 성이 “佐藤” 또는 “田中”인 사용자 조회

SELECT * FROM users WHERE name REGEXP '佐藤|田中';

부정 매칭

대괄호 안에 ^를 사용하면 지정된 문자들을 포함하지 않는 데이터를 검색할 수 있습니다.

예시: “山”이 아닌 문자로 시작하는 데이터

SELECT * FROM users WHERE name REGEXP '^[^山]';

이 경우, 이름이 山으로 시작하지 않는 레코드를 반환합니다.

요약

• MySQL에서 REGEXP는 LIKE보다 더 강력한 패턴 검색을 가능하게 합니다.
• 기본 구문 및 패턴( . , ^ , $ , [] , * , + 등)을 이해하면 유연한 검색이 가능합니다.
• LIKE와의 차이를 이해하고 각각을 적절히 사용하는 것이 중요합니다.
• |를 사용하면 여러 패턴을 동시에 검색할 수 있습니다.
• 부정 문자 클래스 [^ ]를 사용하면 특정 문자로 시작하지 않는 데이터를 검색할 수 있습니다.

3. MySQL 8.0 및 이후 버전에서 추가된 정규 표현식 함수

MySQL 8.0에서는 기존 REGEXP 연산자 외에 네 개의 새로운 함수가 추가되어 보다 유연한 정규 표현식 검색이 가능해졌습니다. 이 함수들을 사용하면 매치 위치를 가져오고, 부분 문자열을 추출하며, 교체를 수행할 수 있어 MySQL에서 정규식으로 할 수 있는 작업 범위가 크게 확대됩니다.

이 섹션에서는 각 새로운 정규 표현식 함수를 자세히 설명하고 구체적인 예시를 보여드립니다.

3.1 REGEXP_LIKE()

개요

REGEXP_LIKE()는 REGEXP 연산자와 마찬가지로 컬럼 값이 지정된 정규식 패턴과 일치하는지 확인합니다.

구문

REGEXP_LIKE(column_name, 'regex_pattern' [, flags])

• column_name : 검색할 컬럼
• 'regex_pattern' : 조건으로 사용되는 정규식 패턴
• flags (optional) : 대소문자 구분 여부 (i를 사용하면 대소문자를 구분하지 않음)

예시

“이메일이 gmail.com 또는 yahoo.co.jp로 끝나는 사용자 가져오기”

SELECT * FROM users WHERE REGEXP_LIKE(email, '(@gmail\.com|@yahoo\.co\.jp)$');

“’admin’을 포함하는 사용자 이름 검색 (대소문자 구분 없음)”

SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

✅ REGEXP_LIKE()는 REGEXP 연산자와 동일한 목적을 가지지만, 보다 표준 SQL에 친화적인 방식으로 사용할 수 있습니다.

3.2 REGEXP_INSTR()

개요

REGEXP_INSTR()는 문자열 내에서 정규식 매치가 발생하는 위치(시작 인덱스)를 반환합니다.

구문

REGEXP_INSTR(column_name, 'regex_pattern' [, start_position, occurrence, return_option, flags])

• start_position (optional): 검색을 시작할 위치 (기본값: 1)
• occurrence (optional): 반환할 매치 순서 (기본값: 1)
• return_option (optional): 0 (시작 위치 반환) 또는 1 (매치 종료 위치 반환)
• flags (optional): 대소문자 구분 없이 매치하려면 i 사용

예시

“전화번호가 090 또는 080으로 시작하면 시작 위치를 가져오기”

SELECT phone, REGEXP_INSTR(phone, '^(090|080)') AS match_pos FROM users;

“이메일 주소에서 도메인 부분의 시작 위치 가져오기”

SELECT email, REGEXP_INSTR(email, '@') AS domain_start FROM users;

• @가 발견된 위치를 반환합니다 (예: user@example.com은 5를 반환).

✅ REGEXP_INSTR()를 사용하면 위치 정보를 얻을 수 있어 문자열 처리 옵션이 확대됩니다.

3.3 REGEXP_SUBSTR()

개요

REGEXP_SUBSTR()는 문자열 내에서 정규식 패턴과 일치하는 부분 문자열을 추출합니다.

구문

REGEXP_SUBSTR(column_name, 'regex_pattern' [, start_position, occurrence, flags])

• occurrence (optional): n번째 매치를 가져옴 (기본값: 1)
• flags (optional): 대소문자 구분 (i를 사용하면 무시)

예시

“이메일 주소에서 도메인 부분만 추출하기”

SELECT email, REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain FROM users;

“메시지에서 첫 번째 숫자 시퀀스 가져오기”

SELECT message, REGEXP_SUBSTR(message, '[0-9]+') AS first_number FROM logs;

✅ REGEXP_SUBSTR()를 사용하면 특정 패턴을 추출하여 데이터 정리 및 변환에 활용할 수 있습니다.

3.4 REGEXP_REPLACE()

개요

REGEXP_REPLACE()는 정규식 패턴과 일치하는 부분 문자열을 다른 문자열로 교체합니다.

구문

REGEXP_REPLACE(column_name, 'regex_pattern', 'replacement' [, occurrence, flags])

• replacement : 일치하는 부분을 대체할 문자열
• occurrence (선택): n번째 일치만 대체 (생략 시 모든 일치 대체)
• flags (선택): 대소문자 구분 없음 매칭을 위해 i 사용

예시

“전화번호에서 하이픈 (-) 제거”

SELECT phone, REGEXP_REPLACE(phone, '-', '') AS clean_phone FROM users;

“HTML 태그 제거”

SELECT comment, REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_text FROM reviews;

✅ REGEXP_REPLACE()를 사용하면 형식 변환과 데이터 정리가 쉬워집니다.

3.5 요약

4. MySQL 정규 표현식의 실전 예시

MySQL 정규 표현식을 활용하면 데이터 유효성 검사, 특정 데이터 추출, 형식 변환과 같은 실세계 데이터 처리 작업을 간소화할 수 있습니다. 이 섹션에서는 SQL 코드와 설명과 함께 실전 사용 사례를 소개합니다.

4.1 이메일 주소 유효성 검사

개요

데이터베이스에 저장된 이메일 주소가 유효한 형식을 따르는지 확인하기 위해 정규 표현식을 사용합니다.

사용할 SQL

SELECT email FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

설명

• ^[a-zA-Z0-9._%+-]+ → @ 앞에 하나 이상의 알파벳, 숫자, 점, 밑줄 또는 플러스 기호
• @[a-zA-Z0-9.-]+ → @ 뒤의 도메인 이름
• \.[a-zA-Z]{2,}$ → 최소 2자 이상의 TLD (Top-Level Domain)으로 끝남 (예: .com , .jp , .net )

이 쿼리를 사용하면 유효하지 않은 이메일 주소를 제외할 수 있습니다 (예: user@@example.com, user@.com).

4.2 일본 전화번호 형식 확인

개요

일반적인 일본 전화번호 (예: 090-1234-5678, 03-1234-5678)가 올바른 형식을 따르는지 확인합니다.

사용할 SQL

SELECT phone FROM users 
WHERE phone REGEXP '^(0[789]0-[0-9]{4}-[0-9]{4}|0[1-9]-[0-9]{4}-[0-9]{4})$';

설명

• 0[789]0-[0-9]{4}-[0-9]{4} → 이동통신 번호 ( 090-xxxx-xxxx , 080-xxxx-xxxx 등)
• 0[1-9]-[0-9]{4}-[0-9]{4} → 유선 전화번호 ( 03-xxxx-xxxx , 06-xxxx-xxxx 등)

이와 같이 정규 표현식은 형식 일관성이 요구될 때 유용합니다.

4.3 신용카드 번호 형식 유효성 검사

개요

신용카드 번호 (Visa, Mastercard, American Express 등)의 형식을 유효성 검사합니다.

사용할 SQL

SELECT card_number FROM payments 
WHERE card_number REGEXP '^(4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13})$';

설명

• ^4[0-9]{12}(?:[0-9]{3})?$ → Visa (16자리 또는 13자리)
• ^5[1-5][0-9]{14}$ → Mastercard (16자리)
• ^3[47][0-9]{13}$ → American Express (15자리)

이 쿼리를 사용하면 데이터베이스에서 유효하지 않은 카드 번호를 필터링할 수 있습니다 (예: 잘못된 길이 또는 유효하지 않은 시작 숫자).

4.4 HTML 태그 제거

개요

사용자 제출 데이터에 HTML 태그가 포함되어 있으면 이를 제거하여 콘텐츠를 일반 텍스트로 변환할 수 있습니다.

사용할 SQL

SELECT REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_comment FROM reviews;

설명

• '<[^>]+>' → <와 > 사이의 모든 HTML 태그 제거

예시

이것은 사용자 댓글과 블로그 게시물에서 HTML 태그를 제거하는 데 유용합니다.

4.5 우편번호 형식 유효성 검사 (일본)

개요

일본 우편번호 (예: 123-4567)가 올바른 형식을 따르는지 유효성 검사합니다.

사용할 SQL

SELECT postal_code FROM addresses 
WHERE postal_code REGEXP '^[0-9]{3}-[0-9]{4}$';

설명

• ^[0-9]{3}-[0-9]{4}$ → “3자리-4자리” 형식의 우편번호

이 정규 표현식을 사용하면 데이터 일관성을 유지하고 잘못된 형식을 방지할 수 있습니다.

4.6 사용자 이름 필터링 (허용되지 않는 단어 감지)

개요

Check whether usernames contain disallowed words reserved for system use (e.g., admin, root, system) during registration.

사용할 SQL

SELECT username FROM users 
WHERE username REGEXP 'admin|root|system';

설명

• admin|root|system → 이러한 용어가 포함된 사용자 이름을 감지합니다

이 쿼리는 일반 사용자가 시스템 관리자용으로 의도된 이름을 사용하는 것을 방지하는 데 도움이 됩니다.

요약

• 정규 표현식은 이메일 주소, 전화번호 및 신용카드 번호 검증에 사용할 수 있습니다.
• 또한 HTML 태그 제거 및 데이터 형식 변환에 적용할 수 있습니다.
• 금지 단어 필터링 및 우편번호 검증과 같은 작업을 통해 데이터베이스 안전성과 일관성을 향상시키는 데 효과적입니다.

5. 정규 표현식 사용 시 중요한 고려 사항 (성능 및 보안)

MySQL에서 정규 표현식을 사용하면 강력한 검색 및 데이터 정제가 가능합니다. 그러나 잘못된 사용은 성능 저하를 일으키고 보안 위험을 초래할 수 있습니다. 이 섹션에서는 MySQL 정규 표현식을 안전하고 효율적으로 사용하기 위한 주요 고려 사항을 설명합니다.

5.1 정규 표현식 성능 최적화

MySQL 정규식 검색은 편리하지만 단점이 있습니다: 일반적으로 인덱스가 적용되지 않아 쿼리가 느려질 수 있습니다.

성능 향상을 위한 조치

1. LIKE 또는 FULLTEXT 인덱스 결합

   SELECT * FROM users WHERE email LIKE '%gmail.com';
   

2. 보다 간단한 정규 표현식 사용

   SELECT * FROM users WHERE name REGEXP '^admin|admin$';
   

3. 대상 데이터를 먼저 필터링

   SELECT * FROM users WHERE email LIKE 'a%' AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

5.2 보안 위험 및 대책

1. 정규 표현식 DoS (ReDoS)

• .*와 같은 무제한 매치를 피하십시오.
• ^와 $를 사용하여 검색 범위를 제한하십시오.
• 사용자 입력을 REGEXP에 직접 전달하지 마십시오.

2. SQL 인젝션 및 정규 표현식

❌ Unsafe query

SELECT * FROM users WHERE username REGEXP '$input';

✅ 안전한 쿼리 (플레이스홀더 사용)

$stmt = $pdo->prepare("SELECT * FROM users WHERE username REGEXP ?");
$stmt->execute([$sanitized_input]);

5.3 버전 호환성

요약

• REGEXP가 인덱스를 사용하지 않기 때문에 성능 조치가 중요합니다.
• ReDoS(정규 표현식 DoS)를 방지하려면 와일드카드 사용을 과도하게 하지 마십시오.
• SQL 인젝션을 방지하려면 사용자 입력을 REGEXP에 직접 적용하지 마십시오.

6. FAQ (자주 묻는 질문)

다음은 MySQL 정규 표현식(REGEXP) 사용에 관한 일반적인 질문들입니다. 이러한 문제를 해결하면 정규식을 보다 효과적으로 사용할 수 있습니다.

6.1 MySQL에서 사용할 수 있는 정규식 패턴은 무엇인가요?

6.2 LIKE와 REGEXP의 차이점은 무엇인가요?

✅ LIKE가 더 빠르므로 간단한 검색에는 LIKE를, 복잡한 매칭이 필요할 때는 REGEXP를 사용하십시오.

6.3 MySQL 5.x와 MySQL 8.x의 정규식 기능 차이는 무엇인가요?

6.4 정규식이 예상대로 작동하지 않을 때는 어떻게 해야 하나요?

✅ 체크리스트

1. 이스케이프가 올바른지 확인

   SELECT * FROM users WHERE email REGEXP '\.com$';
   

2. REGEXP_LIKE()와 함께 i 플래그를 시도

   SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');
   

3. 데이터 인코딩 확인

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 REGEXP 쿼리 성능을 어떻게 향상시킬 수 있나요?

1. LIKE로 사전 필터링

   SELECT * FROM users 
   WHERE email LIKE '%gmail.com' 
   AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

2. 적절한 경우 인덱스 사용

   ALTER TABLE users ADD FULLTEXT(email);
   

요약

• REGEXP는 LIKE보다 더 강력하지만, 성능에 주의해야 합니다.
• MySQL 8.0에서 REGEXP_LIKE() 등의 새로운 정규 표현식 함수가 추가되어 더 유연한 처리가 가능합니다.
• 성능 향상을 위해: LIKE와 결합, 인덱스 사용, 간단한 정규 표현식 패턴 설계.

7. 요약

MySQL 정규 표현식(REGEXP)은 데이터 검색, 검증, 형식 변환에 매우 유용합니다. 이 기사에서는 MySQL 정규 표현식 기본, MySQL 8.0 이후 추가된 새로운 기능, 실전 예제, 중요한 고려사항, FAQ를 다루었습니다.

7.1 주요 요점

가장 중요한 점의 빠른 요약입니다.

1. MySQL 정규 표현식 기본

• REGEXP 연산자를 사용하면 LIKE보다 더 유연한 패턴 매칭이 가능합니다.
• 일반적인 정규 표현식 패턴
• ^ (시작), $ (끝), . (임의의 단일 문자), [] (문자 클래스), + (하나 이상의 반복), 등.

2. MySQL 8.0+의 새로운 정규 표현식 함수

MySQL 8.0에서 이 네 가지 함수가 추가되어 더 유연한 처리가 가능합니다:

• REGEXP_LIKE() : REGEXP 연산자의 대안
• REGEXP_INSTR() : 매칭되는 부분 문자열의 시작 위치 가져오기
• REGEXP_SUBSTR() : 매칭되는 부분 문자열 추출
• REGEXP_REPLACE() : 정규 표현식을 사용한 부분 문자열 교체

3. 실전 예제

• 이메일 주소 검증
• 전화번호 및 신용카드 번호 확인
• HTML 태그 제거
• 우편번호 형식 검증
• 허용되지 않는 단어 감지
• 데이터 정리 (공백 자르기, 쉼표 제거 등.)

4. 정규 표현식을 사용할 때의 중요한 고려사항

• 성능 최적화
• REGEXP는 인덱스를 사용하지 않기 때문에 전체 테이블 스캔을 초래할 수 있습니다.
• LIKE 또는 FULLTEXT 인덱스를 결합하여 성능을 향상시킬 수 있습니다.
• 보안 조치
• ReDoS (Regular Expression DoS) 를 방지하기 위해 .* 의 과도한 사용을 피하세요.
• SQL injection 을 방지하기 위해 placeholders 를 사용하세요.

5. FAQ (자주 묻는 질문)

• MySQL에서 사용 가능한 정규 표현식 패턴
• LIKE와 REGEXP의 차이점
• MySQL 5.x와 8.x의 기능 차이
• 정규 표현식이 예상대로 작동하지 않을 때 문제 해결
• 성능 향상 방법

7.2 정규 표현식 사용 베스트 프랙티스

1. 정규 표현식 패턴을 간단하게 유지

• 예: .* 의 과도한 사용을 피하고 ^ (시작)과 $ (끝)을 활용하세요.

1. 적절한 곳에서 LIKE 또는 FULLTEXT와 결합

• LIKE로 사전 필터링한 후 REGEXP를 적용하여 쿼리 비용을 줄이세요.

1. 데이터 정리용으로 REGEXP_REPLACE() 사용

• 예: HTML 태그 제거 및 불필요한 공백 정규화.

1. MySQL 8.0 이후를 활용

• REGEXP_LIKE() 등의 새로운 함수를 사용하면 SQL이 더 읽기 쉽게 됩니다.

1. 보안 조치를 철저히 적용

• 사용자 입력을 REGEXP에 직접 적용하지 마세요 (SQL injection 방지).
• 동적 쿼리에 placeholders를 사용하세요.

7.3 추가 학습 자료

MySQL 정규 표현식을 더 깊이 배우는 데 도움이 되는 참조 자료입니다.

공식 문서

• MySQL 8.0 Regular Expression Reference
• MySQL 5.x Regular Expression Basics

7.4 마무르기

MySQL 정규 표현식은 데이터 검색, 정리, 검증을 포함한 많은 상황에서 사용할 수 있습니다. 그러나 성능과 보안 고려사항을 이해하면서 적절히 사용하는 것이 중요합니다.

이 기사가 MySQL 정규 표현식 작업에 도움이 되기를 바랍니다. 프로젝트에 이러한 기술을 적용해 보세요!