MySQL ELT · LOCATE · INSTR — 위치·값 찾기 완전 정리

 

 

Database · MySQL

MySQL ELT · LOCATE · INSTR —
위치·값 찾기 완전 정리

번호로 값 꺼내기, 값의 위치 찾기, 문자열 안 위치 찾기 — 5개 함수의 차이와 원리

시작 오늘 분석할 코드

MySQL에는 "위치"와 "값"을 서로 바꿔가며 찾아주는 함수가 5개 있다. 겉으로는 비슷해 보이지만 인덱스 → 값인지 값 → 인덱스인지, 리스트 안인지 문자열 안인지에 따라 완전히 다른 함수다. 원본 코드를 그대로 분석해본다.

SELECT ELT(2, '하나', '둘', '셋'), -- 번호번째 값을 가져온다. FIELD('둘', '하나', '둘', '셋'), -- 찾을값이 몇번째 INDEX에 있는지 확인한다. FIND_IN_SET('둘', '하나,둘,셋'), -- 찾을값이 몇번째 INDEX에 있는지 확인한다. 반드시 쉼표로 구분자 처리 할것. INSTR('하나둘셋', '둘'), -- 문자열안에서 찾을 문자가 몇번째에 시작하는지 알려줌 LOCATE('둘', '하나둘셋'); -- INSTR과 반대개념. 찾을 문자가 몇번째에 시작하는지 알려줌

실제 결과 미리보기

ELT(2,…)	FIELD('둘',…)	FIND_IN_SET('둘',…)	INSTR('하나둘셋','둘')	LOCATE('둘','하나둘셋')
'둘'	2	2	3	3

한 줄 결과지만 의미는 전부 다르다 — 앞의 세 개는 "리스트 안 몇 번째", 뒤의 두 개는 "문자열 안 몇 번째 글자"를 묻는다.

용어 용어 정리

ELT(N, …)N번째 위치의 값을 꺼내는 함수 (인덱스 → 값)

FIELD(v, …)찾는 값이 목록에서 몇 번째인지 반환 (값 → 인덱스)

FIND_IN_SET(v, s)쉼표로 구분된 문자열에서 값의 위치 반환

INSTR(s, sub)문자열 s 안에서 부분 문자열 sub가 시작하는 위치

LOCATE(sub, s)INSTR과 동일 기능, 인자 순서가 반대

1-based문자열·리스트 위치는 1부터 센다 (0부터 아님)

0 반환못 찾으면 NULL이 아니라 0을 반환

구분자(delimiter)FIND_IN_SET은 반드시 쉼표(,)로 구분된 문자열만 인식

---

1 ELT(N, v1, v2, …) — 번호로 값 꺼내기

왜 배열이 아니라 인자를 나열하는 방식인가?

MySQL에는 PHP의 배열 같은 자료형이 없다. 그래서 "여러 값 중 N번째"를 다루고 싶을 때는 ELT(N, v1, v2, v3, …)처럼 값을 인자로 쭉 나열하고, 첫 번째 인자 N으로 그중 하나를 골라낸다. ELT(2, '하나', '둘', '셋')은 "2번째 인자를 달라"는 뜻이므로 결과는 '둘'이다.

※ N이 1부터 시작한다는 점에 주의. ELT(1, …)이 첫 번째 값이고, ELT(0, …)은 존재하지 않는 위치라 NULL을 반환한다.

N이 인자 개수보다 크거나(ELT(5, '하나','둘','셋')), 음수이거나, 0일 때도 결과는 전부 NULL이다. 이건 "범위를 벗어난 인덱스 접근"을 에러로 죽이지 않고 조용히 NULL로 흘려보내는 SQL 특유의 관용이다.

---

2 FIELD(찾을값, v1, v2, …) — ELT의 반대

왜 FIELD는 ELT와 항상 세트로 외워야 하는가?

ELT가 "인덱스를 주면 값을 돌려주는" 함수라면, FIELD는 정반대로 "값을 주면 인덱스를 돌려주는" 함수다. FIELD('둘', '하나', '둘', '셋')은 "'둘'이 몇 번째 자리에 있느냐"를 묻고 2를 반환한다.

ELT(N, …)

방향: 인덱스 → 값
입력: 번호 2
출력: '둘'

FIELD(v, …)

방향: 값 → 인덱스
입력: '둘'
출력: 번호 2

※ FIELD는 찾는 값이 목록에 하나도 없으면 0을 반환한다. 대소문자 구분은 컬럼의 콜레이션(collation) 설정을 따른다.

---

3 FIND_IN_SET(찾을값, '쉼표,구분,문자열') — 리스트 안 위치

왜 반드시 쉼표(,)로 구분해야 하는가?

FIND_IN_SET은 FIELD와 하는 일이 비슷해 보이지만 입력 형태가 다르다. FIELD는 값을 인자 여러 개로 나열하는 반면, FIND_IN_SET은 하나의 문자열 안에 쉼표로 항목을 구분해 넣는다. 함수 내부적으로 이 문자열을 쉼표 기준으로 쪼개서(split) 순서를 센 뒤, 찾는 값과 일치하는 조각의 번호를 돌려준다.

FIND_IN_SET('둘', '하나,둘,셋') → 내부적으로 '하나,둘,셋' 을 ['하나','둘','셋'] 으로 쪼갬 → '둘'은 2번째 조각 → 결과: 2

※ 실무에서 자주 걸리는 함정

• 구분자가 쉼표가 아니면(예: '하나|둘|셋') 통째로 한 덩어리로 취급되어 못 찾는다
• MySQL의 SET 자료형과 궁합이 맞게 설계된 함수라 항목이 너무 많은 리스트에는 적합하지 않다
• 정규화된 테이블(1개 값 = 1개 행) 대신 쉼표 구분 문자열을 저장하는 설계 자체가 안티패턴으로 꼽힌다

---

 

반응형

 

4 INSTR(문자열, 찾을문자) — 문자열 안 위치

왜 위치는 0이 아니라 1부터 세는가?

여기서부터는 "리스트 안 순번"이 아니라 "문자열 안 글자 위치"를 찾는 함수로 넘어간다. INSTR('하나둘셋', '둘')은 '하나둘셋'이라는 문자열에서 '둘'이 몇 번째 글자부터 시작하는지를 센다. '하'가 1번째, '나'가 2번째, '둘'이 3번째 글자이므로 결과는 3이다.

※ PHP의 strpos(), JavaScript의 indexOf() 등 대부분의 프로그래밍 언어는 위치를 0부터 센다. 반면 SQL 계열은 사람이 "몇 번째 글자"라고 셀 때 쓰는 방식 그대로 1부터 센다. PHP와 MySQL을 오가며 개발할 때 가장 헷갈리는 포인트다.

찾는 문자가 문자열 안에 없으면 INSTR은 0을 반환한다. NULL이 아니라 0이라는 점이 중요한데, 이는 이어지는 6번 섹션에서 따로 다룬다.

---

5 LOCATE(찾을문자, 문자열) — INSTR과 인자 순서만 반대

기능이 같은데 왜 함수가 두 개나 있는가?

LOCATE('둘', '하나둘셋')은 INSTR('하나둘셋', '둘')과 결과가 완전히 같다(둘 다 3). 차이는 딱 하나, 인자를 쓰는 순서뿐이다. INSTR은 "문자열, 찾을값" 순서고 LOCATE는 "찾을값, 문자열" 순서다.

INSTR(str, substr)

문자열이 먼저
INSTR('하나둘셋','둘')
시작 위치 지정 불가

LOCATE(substr, str [, pos])

찾을 값이 먼저
LOCATE('둘','하나둘셋')
3번째 인자로 검색 시작 위치 지정 가능

두 함수가 공존하는 이유는 호환성이다. INSTR은 Oracle류 SQL 방언에서 넘어온 이름이고, LOCATE는 원래 Sybase/SQL Server 계열 관용을 따른 이름이다. MySQL은 둘 다 지원해서 어느 쪽 문법에 익숙한 사람이 와도 쓸 수 있게 해뒀다. 대신 LOCATE는 LOCATE(substr, str, pos)처럼 세 번째 인자로 검색을 시작할 위치를 지정할 수 있어 "이전 위치 이후부터 다시 찾기" 같은 반복 검색에 더 유리하다.

---

6 없으면 0 — NULL이 아닌 이유

왜 "못 찾음"을 NULL이 아니라 0으로 표현하는가?

FIELD·FIND_IN_SET·INSTR·LOCATE 네 함수 모두 찾는 대상이 없을 때 0을 반환한다. 만약 NULL을 반환했다면, 이 값을 조건문에 쓸 때마다 IS NULL 체크를 추가로 해야 하고 산술 연산(+, 비교 등)에서도 NULL 전파 때문에 예상치 못한 결과가 튀어나온다. 0은 "숫자 위치가 아니다"를 나타내는 안전한 값으로 쓰기 편해서, MySQL은 일관되게 0을 택했다.

IF(LOCATE('사', '하나둘셋') = 0, '없음', '있음') → '사'는 문자열에 없으므로 LOCATE 결과 0 → IF 조건이 참 → '없음'

※ 단, 인자 자체가 NULL이면 결과도 NULL이다(0이 아님). "값이 없어서 못 찾은 것"과 "애초에 비교할 데이터가 없는 것"은 다르게 취급된다는 뜻이다.

---

7 자주 하는 실수

실수	증상	해결
INSTR과 LOCATE의 인자 순서를 혼동	엉뚱한 값이 인자로 들어가 문법 에러 또는 잘못된 결과	INSTR(문자열,찾을값) / LOCATE(찾을값,문자열)로 암기
위치를 0부터 셀 거라 착각	PHP strpos 결과와 1 차이가 나서 로직이 어긋남	MySQL 위치 함수는 전부 1-based임을 기억
FIND_IN_SET에 쉼표 아닌 구분자 사용	있는 값인데도 0(못 찾음)이 반환됨	반드시 쉼표(,)로 구분된 문자열만 전달
ELT(N,…)에서 N이 0 또는 범위 초과	에러 없이 조용히 NULL이 반환돼 원인 파악이 늦음	N의 유효 범위(1~인자개수)를 미리 검증
못 찾은 결과 0을 NULL과 같은 것으로 취급	IS NULL 조건으로 걸러도 안 걸러짐	"없음" 판정은 = 0으로, NULL은 별도로 체크
FIND_IN_SET을 대용량 리스트에 사용	인덱스를 못 타서 풀스캔, 성능 저하	값이 많아지면 별도 매핑 테이블로 정규화

---

핵심 한 줄 요약

ELT(N, …)N번째 값을 반환 (인덱스 → 값), 범위 벗어나면 NULL

FIELD(v, …)값이 몇 번째인지 반환 (값 → 인덱스), 없으면 0

FIND_IN_SET(v, s)쉼표 구분 문자열 안에서 위치 반환, 구분자 필수

INSTR(s, sub)문자열 안 시작 위치, 1부터 세고 없으면 0

LOCATE(sub, s)INSTR과 동일 기능, 인자 순서만 반대

0 vs NULL못 찾으면 0, 인자 자체가 NULL이면 결과도 NULL

Tags

#MySQL #ELT #FIELD #FIND_IN_SET #INSTR #LOCATE #위치찾기 #값찾기 #문자열검색 #문자열함수 #SQL #데이터베이스 #DB #웹개발 #티스토리

▼ 티스토리 태그 입력란 복사용

MySQL, ELT, FIELD, FIND_IN_SET, INSTR, LOCATE, 위치찾기, 값찾기, 문자열검색, 문자열함수, SQL, 데이터베이스, DB, 웹개발, 티스토리