How to unserialize data using mysql without using php

I'm working on a system that has data in a column serialized with php - not my design I should add...

Rather than pulling the data and unserializing each record in php, I wanted to do this in mysql. It seems to be a common problem for developers.

After hunting through all the mysql functions I found SUBSTRING_INDEX.

For example if you have a table like this (data has been modified to protect the innocent!)

old_data

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:5;s:14:"broughtforward";i:3;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:8;s:14:"broughtforward";i:5;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:10;s:14:"broughtforward";i:8;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:11;s:14:"broughtforward";i:10;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:13;s:14:"broughtforward";i:11;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:14;s:14:"broughtforward";i:13;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:101;s:14:"broughtforward";i:100;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";}

a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:102;s:14:"broughtforward";i:101;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";}

a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:103;s:14:"broughtforward";i:102;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";}

a:5:{s:9:"invoiceid";s:3:"492";s:8:"balance";i:3;s:14:"broughtforward";i:2;s:6:"userid";s:5:"13908";s:10:"customerid";s:3:"179";}

Use this query, the a:5 above tells me that there are 5 columns. It does depend on the developer knowing how many columns are in the serialized data and the structure being the same. But you get the idea.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM audit
WHERE object_type=42


Which gives me this

fieldname1	fieldvalue1	fieldname2	fieldvalue2	fieldname3	fieldvalue3	fieldname4	fieldvalue4	fieldname5	fieldvalue5
"invoiceid"	"8"	"balance"	5	"broughtforward"	3	"userid"	"13908"	"customerid"	"3"
"invoiceid"	"8"	"balance"	8	"broughtforward"	5	"userid"	"13908"	"customerid"	"3"
"invoiceid"	"8"	"balance"	10	"broughtforward"	8	"userid"	"13908"	"customerid"	"3"
"invoiceid"	"8"	"balance"	11	"broughtforward"	10	"userid"	"13908"	"customerid"	"3"
"invoiceid"	"8"	"balance"	13	"broughtforward"	11	"userid"	"13908"	"customerid"	"3"
"invoiceid"	"8"	"balance"	14	"broughtforward"	13	"userid"	"13908"	"customerid"	"3"
"invoiceid"	"387"	"balance"	101	"broughtforward"	100	"userid"	"13908"	"customerid"	"4"
"invoiceid"	"387"	"balance"	102	"broughtforward"	101	"userid"	"13908"	"customerid"	"4"
"invoiceid"	"387"	"balance"	103	"broughtforward"	102	"userid"	"13908"	"customerid"	"4"
"invoiceid"	"492"	"balance"	3	"broughtforward"	2	"userid"	"13908"	"customerid"	"179"

Then all I need to do is use some column aliases


SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS invoiceid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS balance,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS broughtforward,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS userid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS customerid
FROM audit
WHERE object_type=42


Simples!