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