SQL 튜터 세션 정리
오늘 푼 문제
- 문제: LeetCode 1517. Find Users With Valid E-Mails
- 핵심 개념: MySQL
REGEXP, 정규표현식 문자 집합(character class), 시작/끝 앵커, 실제 점(.) 표현 - 사용 표현:
REGEXP,[],^,$,*,[.]
문제 조건 쪼개기
유효한 이메일을 찾기 위해 조건을 다음처럼 나누어 생각했다.
- 이메일은 알파벳으로 시작해야 한다.
@앞부분에는 허용된 문자만 올 수 있다.- 도메인은 정확히
leetcode.com이어야 한다. - 도메인 뒤에 다른 문자가 더 붙으면 안 된다.
처음에는 다음과 같은 수도코드 형태로 접근했다.
WHERE REGEX(left(mail, 1), ['A-Za-z'])
AND AFTER(mail, '@') = 'leetcode.com'이 접근의 방향은 좋았다. 실제 SQL에서는 문자열을 잘라 검사하는 방식도 가능하지만, 이 문제는 이메일 형식 전체를 검사하는 문제이므로 MySQL의 정규표현식 연산자인 REGEXP를 사용하는 쪽이 더 자연스럽다.
MySQL의 REGEXP
MySQL에서는 특정 컬럼이 정규표현식 패턴을 만족하는지 확인할 때 다음 형태를 쓴다.
컬럼명 REGEXP '정규표현식'예를 들어 mail 컬럼이 어떤 패턴을 만족하는지 확인하려면 다음처럼 쓴다.
mail REGEXP '패턴'여기서 중요한 점은 REGEXP가 함수처럼 REGEXP(...) 형태로 쓰이는 것이 아니라, LIKE처럼 컬럼과 패턴 사이에 놓이는 연산자처럼 쓰인다는 점이다.
mail LIKE '%@leetcode.com'
mail REGEXP '정규표현식'LIKE는 단순한 와일드카드 매칭에 가깝고, REGEXP는 더 복잡한 형식 검증에 적합하다.
문자 집합 []
정규표현식에서 대괄호 []는 그룹이라기보다 문자 집합(character class) 이다.
[abc]이 표현은 a, b, c 중 한 글자를 의미한다.
즉 다음은 매칭된다.
a
b
c하지만 다음은 [abc] 하나만으로는 매칭되지 않는다.
ab
abc
d핵심은 []가 여러 글자 문자열을 의미하는 것이 아니라, 그 안의 후보 중 한 글자를 의미한다는 점이다.
[A-Za-z]의 의미
[A-Za-z]이 표현은 다음 두 범위를 합친 문자 집합이다.
A-Z 대문자 A부터 Z까지
a-z 소문자 a부터 z까지따라서 [A-Za-z]는 알파벳 한 글자를 의미한다.
매칭되는 예:
A
z
G
m매칭되지 않는 예:
1
_
가
abab가 매칭되지 않는 이유는 [A-Za-z]가 알파벳 여러 글자가 아니라 알파벳 한 글자이기 때문이다.
반복 기호 *
여러 글자를 허용하려면 문자 집합 뒤에 반복 기호를 붙인다.
[A-Za-z]**는 바로 앞의 패턴이 0번 이상 반복된다는 뜻이다.
따라서 위 표현은 다음을 모두 허용한다.
빈 문자열
A
abc
Hello이메일 문제에서 @ 앞부분의 나머지 문자들을 표현하려면, 허용된 문자 집합을 만들고 그 뒤에 *를 붙이는 방식으로 생각할 수 있다.
예를 들어 알파벳, 숫자, 언더스코어, 점, 하이픈을 허용하려면 재료는 다음과 같다.
[A-Za-z0-9_.-]이 문자들이 여러 번 반복될 수 있다면 여기에 *를 붙일 수 있다.
[A-Za-z0-9_.-]*시작과 끝: ^, $
정규표현식에서 ^는 문자열의 시작을 의미한다.
^A이 표현은 A로 시작하는 문자열을 찾는다.
반대로 $는 문자열의 끝을 의미한다.
com$이 표현은 com으로 끝나는 문자열을 찾는다.
이메일 전체 형식을 검사할 때는 ^와 $를 함께 쓰는 것이 중요하다. 그래야 문자열 중간 일부만 조건을 만족하는 것이 아니라, 전체 문자열이 처음부터 끝까지 패턴을 만족하는지 확인할 수 있다.
실제 점 .을 표현하는 방법: [.]
정규표현식에서 점 .은 일반적인 점 문자가 아니다. 특별한 의미를 가진다.
.정규표현식에서 .은 아무 문자 한 글자를 의미한다.
따라서 다음처럼 쓰면:
leetcode.com중간의 점이 실제 점이 아니라 아무 문자 하나로 해석될 수 있다.
예를 들어 의도치 않게 다음과 같은 문자열도 통과할 수 있다.
leetcodeXcom
leetcode-com
leetcodeacom실제 점 문자 .을 의미하게 하려면 이스케이프가 필요하다. 한 가지 쉬운 방법은 문자 집합을 사용하는 것이다.
[.]대괄호 안의 점은 특별한 의미의 .이 아니라, 문자 집합 안에 있는 실제 점 문자로 해석된다.
그래서 도메인 leetcode.com을 정확히 표현할 때는 다음처럼 쓸 수 있다.
leetcode[.]com이 표현은 leetcode 다음에 실제 점이 오고, 그 뒤에 com이 오는 경우만 의미한다.
이 문제의 정규표현식 조립 순서
최종 패턴을 바로 외우기보다, 다음 순서로 조립하면 된다.
- 문자열 시작
- 첫 글자는 알파벳
- 그 뒤에는 허용 문자들이 0번 이상 반복
@leetcode.com도메인- 문자열 끝
이를 재료로 표현하면 다음과 같다.
^ 문자열 시작
[A-Za-z] 첫 글자는 알파벳 한 글자
[A-Za-z0-9_.-]* 이후 허용 문자 0개 이상
@leetcode[.]com 정확한 도메인
$ 문자열 끝이 문제에서 중요한 학습 포인트는 정답 패턴을 외우는 것이 아니라, 각 기호가 어떤 역할을 하는지 이해한 뒤 조건을 순서대로 조립하는 것이다.
REGEXP와 REGEXP_LIKE()
처음에는 다음처럼 REGEXP 연산자를 사용할 수 있다고 생각했다.
mail REGEXP '패턴'하지만 LeetCode의 테스트 케이스에서 다음 값이 문제가 되었다.
winston@leetcode.COM도메인은 정확히 leetcode.com이어야 하므로 COM은 제외되어야 한다. 그런데 MySQL의 기본 collation이 대소문자를 구분하지 않는 경우, REGEXP가 com과 COM을 같은 것으로 보고 통과시킬 수 있다.
이때 사용할 수 있는 함수형 표현이 REGEXP_LIKE()이다.
REGEXP_LIKE(문자열, 패턴, 옵션)세 번째 인자에는 매칭 옵션을 줄 수 있다.
'c' case-sensitive, 대소문자 구분
'i' case-insensitive, 대소문자 무시따라서 이 문제처럼 leetcode.com은 허용하지만 leetcode.COM은 제외해야 할 때는 'c' 옵션을 사용한다.
REGEXP_LIKE(mail, '패턴', 'c')처음에는 BINARY mail REGEXP '패턴'처럼 대소문자 구분을 강제하려고 했지만, LeetCode 환경에서는 다음과 같은 collation 충돌이 발생할 수 있었다.
Character set 'binary' cannot be used in conjunction with 'utf8mb4_general_ci' in call to regexp_like이 경우에는 BINARY보다 REGEXP_LIKE()의 세 번째 옵션 'c'를 사용하는 편이 더 안전하다.
최종적으로 조립한 구조는 다음과 같다.
SELECT user_id, name, mail
FROM Users
WHERE REGEXP_LIKE(mail, '^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$', 'c');여기서 'c' 덕분에 @leetcode.com은 통과하지만 @leetcode.COM은 탈락한다.
오늘의 메타 정리
이번 문제에서 좋았던 점은 SQL 함수를 정확히 알지 못해도 조건을 먼저 자연어로 분해했다는 점이다.
letters로 시작해야 한다
도메인이 leetcode.com이어야 한다이렇게 문제 조건을 먼저 쪼개면, 나중에 REGEXP, LEFT(), SUBSTRING_INDEX() 같은 구체적인 도구를 붙이기 쉬워진다.
또 하나의 핵심 발견은 []의 의미였다. 처음에는 그룹으로 묶는 역할처럼 느껴질 수 있지만, 정규표현식에서 []는 정확히는 문자 후보군 중 한 글자를 고르는 문자 집합이다.
그리고 .은 실제 점이 아니라 “아무 문자 한 글자”라는 특별한 의미가 있으므로, 도메인의 실제 점을 표현할 때는 [.]처럼 처리할 수 있다는 점을 새로 배웠다.