MySQL에서 text 컬럼의 특정 값의 type을 수정하는 방법(이라 쓰고 MySQL에서 JSON 다루기 붙이기)

notifications라는 테이블에 payload라는 컬럼이 있다.
payload에는 parentCommentId가 있는 row도 있고 없는 row도 있다.
어떠한 문제(분명히 parentCommentId의 type을 number로 고정해뒀는데 string으로 들어간 이유는 아직 모르겠음... 찾으면 수정하기 위해 일단 메모)로 인해 parentCommentId가 string으로 저장이 되어있었고, 클라분들도 이 부모 댓글 id만 string으로 내려오나보다~ 하고 변환하는 코드를 보유하고 있었다.

parentCommentId의 type은 number가 맞다.

payload의 parentCommentId:"number" -> parentCommentId:number로 마이그레이션하기

딱 떠오른 건 replace 였는데 문제는 저 number가 다 다른데 어떻게 한담?
따옴표만 빼면 되니까 수작업을 할까 생각도 했었는데 prod DB에는 3만5천개의 데이터가 있다 wow 이건 절대 못한다.

결국 gpt의 도움을 받아 쿼리문을 얻어냈는데, 해당 쿼리에 사용된 문법들을 모두 처음 처음봤다.
쿼리에 사용된 문법들을 정리하고 이해해보자

UPDATE notifications
SET payload = JSON_SET(payload, '$.parentCommentId', CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.parentCommentId')) AS UNSIGNED))
WHERE JSON_VALID(payload) AND JSON_TYPE(JSON_EXTRACT(payload, '$.parentCommentId')) = 'STRING';

1. UPDATE 테이블이름 SET 컬럼이름 WHERE 조건절
2. JSON_EXTRACT(payload, '$.parentCommentId') : payload컬럼에서 parentCommentId의 값을 추출한다는 뜻
3. JSON_UNQUOTE(JSON_EXTRACT(payload, '$.parentCommentId')) : 2번에서 추출한 parentCommentId은 “123” 이렇게 되어 있다. 이걸 unquote → 따옴표를 제거한다는 뜻. 즉 "123"을 123으로 반환한다.
4. CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.parentCommentId')) AS UNSIGNED) : 3번에서 추출한 데이터는 따옴표가 제거된 123이다. CAST(값) AS UNSIGNED는 값을 부호가 없는 정수로 변환한다.
5. JSON_SET(payload, '$.parentCommentId', CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.parentCommentId')) AS UNSIGNED)) : payload컬럼의 parentCommentId 값을 4번에서 바꾼 정수 123으로 업데이트하는 문장이다.
6. JSON_TYPE(JSON_EXTRACT(payload, '$.parentCommentId')) = 'STRING' : payload의 parentCommentId의 값을 추출했을 때, 타입이 string인 데이터와
7. JSON_VALID(payload) : payload컬럼이 Json인 데이터를 수정하겠다는 뜻

payload컬럼의 parentCommentId에 string data가 들어간 값을,
따옴표를 제거하고 부호가 없는 정수로 바꾼 후 업데이트치는 쿼리문이다.