SQL에서 패턴을 찾아주는 LIKE 활용하기

CodeSignal SQL 예제를 바탕으로 LIKE 활용법에 대해 알아본다.

users 테이블

와일드카드 _ & %

_는 한 글자만을 의미하며, %는 0글자부터 그 이상을 의미한다.

%와 % 사이에 텍스트를 넣으면 해당 텍스트가 포함된 모든 raw를 불러올 수 있다.

-- TEST가 중간에 들어가는 모든 raw를 불러온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';

SELECT * FROM users WHERE attribute LIKE '_TEST%';
=> TEST 앞에 1만 존재하는 3번 raw만 가져온다.

와일드카드 없이 텍스트로 시작하거나 끝나면 해당 텍스트로 시작되거나 끝나야 한다.

SELECT * FROM users WHERE attribute LIKE 'TEST%';
=> TEST로 시작하는 2번 raw만 가져온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%Axc';
=> Axc로 끝나는 3,4번 raw를 가져온다.

case sensitivity

case sensitivity는 대소문자를 구별하느냐의 차이를 의미한다.

MySQL - case-insensitive <-> BINARY

MySQL에서 LIKE는 기본적으로 case-insensitive하다. 즉, 대소문자를 구별하지 않는다.

-- 모두 같은 결과를 가져온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';
SELECT * FROM users WHERE attribute LIKE '%test%';
SELECT * FROM users WHERE attribute LIKE '%TeSt%';

case-sensitive 하려면 즉, 대소문자를 구별하여 패턴을 찾기 위해서는 BINARY를 사용해야 한다.

SELECT * FROM users WHERE attribute LIKE BINARY '%TEST%';

PostgreSQL - case-sensitive <-> ILIKE

PostgreSQL에서는 MySQL과는 반대로 case-sensitive하다. 즉, 대소문자를 구별한다.

case-insensitive 하려면 ILIKE를 사용해야 한다. ILIKE는 PostrgreSQL에서만 지원하는 방식이다.

SELECT * FROM users WHERE attribute LIKE '%TEST%'; # case-sensitive
SELECT * FROM users WHERE attribute ILIKE '%tEsT%'; # case-insensitive

LIKE PERFORMANCE 관련

정규식 표현 REGEXP (MySQL), ~ (PostgreSQL)와 비교

-- 두 쿼리가 같은 의미 (MySQL)
SELECT * FROM users WHERE attribute LIKE '%TEST%'; -- LIKE 가 더 빠름
SELECT * FROM users WHERE attribute REGEXP 'TEST'; -- REGEXP

정규식 표현을 LIKE와 같은 용도로 활용하면 성능이 훨씬 느리기 때문에 주의가 필요하다.

정규식 표현은 필요한 상황에 맞춰서 활용해야 한다.

INDEX 활용

와일드카드가 뒤에 오는 prefix search( e.g. LIKE ‘TEST%’ )의 경우, B-TREE INDEX를 활용하여 성능을 높일 수 있다.

하지만, ‘%TEST’처럼 와일드카드가 먼저 나오는 postfix search경우는 FULL TABLE SCAN을 실행할 수밖에 없다.

( INDEX는 값을 LEFT-TO-RIGHT로 처리하는 특성 때문 )

‘%TEST%’같은 infix search도 마찬가지다. 이럴 경우, FULL TEXT SEARCH를 고려할 수 있다.

문제 설명

이번 문제는 attribute에서 %{first_name}_{second_name}%의 구조를 가진 raw를 찾는 것이다. 단, 이름 철자 그대로 적용되어야 한다.

예를 들면, 1번 raw에서 Mike Cober은 %Mikel_Cober%형태를 띄고 있다. 하지만, 4번 raw의 경우, %MACk_ALice%형태로 대소문자에 문제가 있음을 볼 수 있다.

CONCAT은 column value와 텍스트를 이어주는 역할을 한다. 와일드카드를 문자로 활용하기 위해서 \를 앞에 붙여야 한다.

-- MySQL 기준
-- 와일드카드 %와 % 사이에 %{fisrt_name}_{second_name}% 구조를 넣은 형식이다.
SELECT * FROM users WHERE attribute 
    LIKE BINARY CONCAT('%\%', first_name , '\_' , second_name ,'\%%')

3번은 모두 소문자이고, 4번은 대소문자가 다르므로 정확하게 입력된 건 1,2번 raw뿐이다.

References :

• MySQL REGEXP 퍼포먼스 간단 테스트