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;

결과

fieldname1fieldvalue1fieldname2fieldvalue2fieldname3fieldvalue3
"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;

개선된 결과

fieldname1fieldvalue1fieldname2fieldvalue2fieldname3fieldvalue3
"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_namelast_namecity
"Taekyun""Kim""Seoul"
"Gildong""Hong""Seoul"
"Adam""Levine"

참고

기본적으로 serialize 되었다는 것은 항목이 많을 것을 가정하여 50개의 항목을 찾을 수 있는 질의문을 샘플로 작성해 두었습니다. 확인은 Github Gist: How to unserialize data using mysql without using php.sql에서 확인하실 수 있습니다.


함께보기




Powered by Blogger.