users 테이블
idfirst_namesecond_nameattribute
| 1 | Mikel | Cober | 3%Mikel_Cober%dsfew |
| 2 | Tosha | Chace | TESTdDfwerwYz%Tosha_Chace%werhfsWhixc |
| 3 | Carrol | Bean | 1TESTzhncdDfwerwYz%carrol_bean%weornsWIJAxc |
| 4 | Mack | Alice | asyvwTESTncdDfUWYz%MACk_ALice%wehrJUWBaXAxc |
| 5 | Vicenta | Kravitz | Tn35g5h51u7lt1231W946J |
| 6 | Kevin | Heart | kUJ3N67vLB07mQL9Ai7p18cXGzjdT32r8283ZQi |
와일드카드 _ & %
_는 한 글자만을 의미하며, %는 0글자부터 그 이상을 의미한다.
%와 % 사이에 텍스트를 넣으면 해당 텍스트가 포함된 모든 raw를 불러올 수 있다.
-- TEST가 중간에 들어가는 모든 raw를 불러온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';
idfirst_namesecond_nameattribute
| 2 | Tosha | Chace | TESTdDfwerwYz%Tosha_Chace%werhfsWhixc |
| 3 | Carrol | Bean | 1TESTzhncdDfwerwYz%carrol_bean%weornsWIJAxc |
| 4 | Mack | Alice | asyvwTESTncdDfUWYz%MACk_ALice%wehrJUWBaXAxc |
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뿐이다.
idfirst_namesecond_nameattribute
| 1 | Mikel | Cober | 3%Mikel_Cober%dsfew |
| 2 | Tosha | Chace | TESTdDfwerwYz%Tosha_Chace%werhfsWhixc |
'dev > mysql' 카테고리의 다른 글
| MySQL 테이블 이름 변경 (RENAME) (0) | 2021.07.08 |
|---|---|
| MySQL 데이터 추가, 수정, 삭제 (INSERT, UPDATE, DELETE) (0) | 2021.07.08 |
| MySQL AUTO_INCREMENT 옵션 총 정리 (0) | 2021.07.08 |
| MySQL 설치와 기본 설정방법 (0) | 2021.07.07 |
