MySQL: PHP를 사용하지 않고 Query만으로 unserialize하는 방법
PHP로 serialize()된 데이터를 데이터베이스에 저장하는 경우가 있습니다. 일반적이라면 PHP에서 unserialize() 후 데이터를 확인합니다.
만약 데이터베이스에서 질의문으로 데이터를 확인하고 싶다면 읽어보시고 테스트해보세요.
운영 중인 서비스에 사용하시는 것은 추천하지 않으며 MySQL의 SUBSTRING_INDEX() 응용한 예시로 봐주세요.
샘플 데이터
| table_name.column_name |
|---|
| a:3:{s:10:"first_name";s:7:"Taekyun";s:9:"last_name";s:3:"Kim";s:4:"city";s:5:"Seoul";} |
| a:3:{s:10:"first_name";s:7:"Gildong";s:9:"last_name";s:4:"Hong";s:4:"city";s:5:"Seoul";} |
| a:2:{s:10:"first_name";s:4:"Adam";s:9:"last_name";s:6:"Levine";} |
질의문
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 1), ':', -1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 2), ':', -1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 3), ':', -1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 4), ':', -1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 5), ':', -1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 6), ':', -1) AS fieldvalue3
FROM table_name;
결과
| fieldname1 | fieldvalue1 | fieldname2 | fieldvalue2 | fieldname3 | fieldvalue3 |
|---|---|---|---|---|---|
| "first_name" | "Taekyun" | "last_name" | "Kim" | "city" | "Seoul" |
| "first_name" | "Gildong" | "last_name" | "Hong" | "city" | "Seoul" |
| "first_name" | "Adam" | "last_name" | "Levine" | "Levine";} | "Levine";} |
항목이 적은 마지막 행은 빈 항목에 "Levine";} 값이 추가됩니다. 이것을 if()를 이용하여 ;} 포함되어 있으면 공백으로 표시되도록 개선해보겠습니다.
개선된 질의문
SELECT
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 1), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 1), ':', -1)) AS fieldname1,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 2), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 2), ':', -1)) AS fieldvalue1,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 3), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 3), ':', -1)) AS fieldname2,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 4), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 4), ':', -1)) AS fieldvalue2,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 5), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 5), ':', -1)) AS fieldname3,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 6), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 6), ':', -1)) AS fieldvalue3
FROM table_name;
개선된 결과
| fieldname1 | fieldvalue1 | fieldname2 | fieldvalue2 | fieldname3 | fieldvalue3 |
|---|---|---|---|---|---|
| "first_name" | "Taekyun" | "last_name" | "Kim" | "city" | "Seoul" |
| "first_name" | "Gildong" | "last_name" | "Hong" | "city" | "Seoul" |
| "first_name" | "Adam" | "last_name" | "Levine" |
참고로 세미콜론(;)을 기준으로 문자열을 찾기 때문에 &와 같이 세미콜론이 포함된 데이터가 있다면 치환해줘야 합니다. (예시: REPLACE(column_name, '&', '&')) 그리고 "AS fieldname1" 이해를 돕기 위한 임시 이름입니다. 다중 배열이면 위 예시처럼 깔끔하게 나오지 않습니다. 위치만 확인 후 최종 질의문을 만드세요.
최종 질의문
SELECT
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 2), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 2), ':', -1)) AS first_name,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 4), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 4), ':', -1)) AS last_name,
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 6), ':', -1) REGEXP ';}', '', SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ';', 6), ':', -1)) AS city
FROM table_name;
최종 결과
| first_name | last_name | city |
|---|---|---|
| "Taekyun" | "Kim" | "Seoul" |
| "Gildong" | "Hong" | "Seoul" |
| "Adam" | "Levine" |