MySQL에서 레코드 목록 내 특정 값을 검색하고 교체하는 방법

MySQL에서 하나의 필드에 여러 값이 콤마(,)로 구분되어 저장된 경우, 그중 특정 값을 찾아 다른 값으로 교체해야 할 때가 있습니다. 이 글에서는 SUBSTRING_INDEX()와 CONCAT() 함수를 활용해 레코드 목록 안에서 원하는 값을 검색하고 새로운 값으로 대체하는 방법을 단계별로 알아보겠습니다.

1. 테이블 생성하기

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable
-> (
-> ListOfName text
-> );
Query OK, 0 rows affected (0.66 sec)

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 콤마로 구분된 이름 목록을 테이블에 삽입합니다.

mysql> insert into DemoTable values('Carol,Sam,John,David,Bob,Mike,Robert,John,Chris,James,Jace');
Query OK, 1 row affected (0.13 sec)

3. 저장된 레코드 확인하기

SELECT 문을 사용해 테이블의 모든 레코드를 조회합니다.

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+------------------------------------------------------------+
| ListOfName                                               |
+------------------------------------------------------------+
| Carol,Sam,John,David,Bob,Mike,Robert,John,Chris,James,Jace |
+------------------------------------------------------------+
1 row in set (0.00 sec)

4. 검색 후 교체(UPDATE) 쿼리 실행하기

이제 목록에서 'John'이라는 이름을 검색하여 'Adam'으로 교체하는 UPDATE 쿼리를 실행합니다.

mysql> update DemoTable
-> set ListOfName=
-> concat(substring_index(ListOfName,'John',2) ,'Adam', SUBSTRING_INDEX(ListOfName, 'John', -1));
Query OK, 1 row affected (0.37 sec)
Rows matched: 1 Changed: 1 Warnings: 0

쿼리 동작 원리:

• SUBSTRING_INDEX(ListOfName,'John',2) — 'John'이 두 번째로 나타나는 위치까지의 문자열을 반환합니다.
• 'Adam' — 기존 'John' 자리에 들어갈 새로운 값입니다.
• SUBSTRING_INDEX(ListOfName, 'John', -1) — 마지막 'John' 이후의 문자열을 반환합니다.

세 부분을 CONCAT()으로 연결하면 두 번째 'John'이 'Adam'으로 교체된 결과가 만들어집니다.

5. 결과 확인하기

테이블을 다시 조회하여 변경 사항을 확인합니다.

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+------------------------------------------------------------+
| ListOfName                                               |
+------------------------------------------------------------+
| Carol,Sam,John,David,Bob,Mike,Robert,Adam,Chris,James,Jace |
+------------------------------------------------------------+
1 row in set (0.00 sec)

위 출력 결과를 보면 두 번째 'John'이 성공적으로 'Adam'으로 교체된 것을 확인할 수 있습니다.

참고: REPLACE() 함수를 활용한 간단한 방법

목록 내 모든 일치 항목을 한 번에 교체하려면 MySQL에서 제공하는 REPLACE() 함수를 사용하는 것이 더 간단합니다.

mysql> update DemoTable
-> set ListOfName = REPLACE(ListOfName, 'John', 'Adam');

다만 REPLACE() 함수는 해당 문자열이 포함된 모든 위치를 교체한다는 점에 유의해야 합니다. 특정 번째 항목만 선택적으로 교체하려면 위에서 소개한 SUBSTRING_INDEX 방식을 사용하는 것이 적합합니다.