[MySQL] SUBSTRING_INDEX + REPLACE로 UPDATE 하려고 했다

이번에 서비스에 추가된 내용이 있어 액션이 발생한 날짜를 업데이트해줘야 하는 작업이 필요했다. 이 과정에서 SUBSTRING_INDEX + REPLACE를 사용하여 칼럼 내용을 업데이트하려고 했던 내용을 포스팅하려고 한다. (했던...이라고 쓴 이유는 저 방법으로 못했다는 뜻이다.)

 

처음에 생각했던 것은 아래와 같다.

1. 칼럼의 기본 값을 '0,0,0,0,0'으로 세팅한다.
2. 액션이 발생하면 알맞은 인덱스 값을 찾아 날짜로 업데이트한다. (예를 들어 B 액션이 발생했으면 두 번째 위치에 있는 '0'을  XXXX-XX-XX 형태로 업데이트)
3. 업데이트된 데이터를 서버에서 받아 처리한 뒤 프론트에 전달한다. 끝!

 

SUBSTRING_INDEX() 함수는 문자열과 구분자 및 위치를 넘겨주면 구분자를 기준으로 문자열을 자른 뒤 해당 위치의 문자열까지 SELECT 하는 함수이다.

# 첫번째 '0' 반환
SELECT SUBSTRING_INDEX('0,0,0,0,0', ',', 1);
# 두번째 '0' 반환
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('0,0,0,0,0', ',', 1),',',-1);

원하는 위치에 있는 문자열을 선택할 수 있으니 이를 활용해 첫 번째 반환된 '0'을 오늘 날짜로 업데이트하려고 했다.

UPDATE ... REPLACE('칼럼명', SUBSTRING_INDEX('0,0,0,0,0', 1, ','), CURDATE()) ... ; # ?

'2021-10-24,0,0,0,0' 형태가 될 것이라 예상했으나 틀렸다. '2021-10-24,2021-10-24,2021-10-24,2021-10-24,2021-10-24', 첫 번째 위치한 '0'이 아닌 모든 '0'이 오늘 날짜로 변경된다. 결론부터 말하면 SUBSTRING_INDEX를 통해 특정 위치의 문자열을 구하는 것은 성공했으나, REPLACE가 해당 문자열과 일치하는 모든 것을 변경해버렸기 때문이었다. 즉 문제는 1) 잘못된 초기값 설정 2) 날짜라는 특성으로 인해 예상치 못한 부분이 REPALCE 대상이 됨이었다.

 

초기값을 '0,0,0,0,0'과 같이 모두 동일한 문자로 세팅할 경우 REPLACE를 하면 결국 모든 문자가 업데이트될 것이고 '1,2,3,4,5'처럼 각각 다른 문자로 세팅한다 하더라도 날짜 특성상 원하지 않는 부분까지 업데이트가 될 것이기에, 초기값은 Null로 설정한 뒤 CONCAT() 함수를 이용해 업데이트된 날짜를 추가하는 방향으로 수정했다. (Null인 경우 빈 스트링이 적용될 수 있게 INFULL 사용)

UPDATE ... CONCAT(IFNULL('칼럼명', ''), CURDATE()) ...;

 

처음에 '0,0,0,0,0'으로 초기값을 정한 이유는 해당 칼럼을 봤을 때 값이 몇 개 들어갈지 예측 가능하게끔 하기위함이었는데, 앞으로의 서비스 개선 및 기획 내용 수정 등 여러 변수에 의해 해당 값이 변경될 수 있다는 측면에서 굳이 저렇게 설정할 필요가 없었다는 생각이 든다. (그리고 칼럼 설명은 comment로 설명하면 됨!)

 

결론

부합하는 모든 문자열을 일괄 업데이트할 목적이 아니라면, SUBSTRING_INDEX + REPLACE 조합을 통한 업데이트는 문자열이 유니크할 경우에 사용해야 기대한 결과를 얻을 수 있다.