Como desserializar dados usando mysql sem usar php

Estou trabalhando em um sistema que possui dados em uma coluna serializada com php – não é o meu design, devo acrescentar …

Em vez de extrair os dados e desserializar cada registro no php, eu queria fazer isso no mysql. Parece ser um problema comum para desenvolvedores.

Depois de pesquisar todas as funções do mysql, encontrei SUBSTRING_INDEX.

Por exemplo, se você tiver uma mesa como esta (os dados foram modificados para proteger os inocentes!)

dados_antigos
a: 5: {s: 9: “invoiceid”; s: 1: “8”; s: 8: “saldo”; i: 5; s: 14: “trazido para a frente”; i: 3; s: 6: “userid”; s: 5: “13908”; s: 10: “customerid”; s: 1: “3”;}
a: 5: {s: 9: “invoiceid”; s: 1: “8”; s: 8: “saldo”; i: 8; s: 14: “trazido”; i: 5; s: 6: “id do usuário”; s: 5: “13908”; s: 10: “id do cliente”; s: 1: “3”;}
a: 5: {s: 9: “invoiceid”; s: 1: “8”; s: 8: “saldo”; i: 10; s: 14: “trazido”; i: 8; s: 6: “userid”; s: 5: “13908”; s: 10: “customerid”; s: 1: “3”;}
a: 5: {s: 9: “invoiceid”; s: 1: “8”; s: 8: “equilíbrio”; i: 11; s: 14: “trazido para a frente”; i: 10; s: 6: “ID do usuário”; s: 5: “13908”; s: 10: “id do cliente “; s: 1:” 3 “;}
a: 5: {s: 9:” invoiceid “; s: 1:” 8 “; s: 8:” saldo “; i: 13; s: 14:” trazido “; i: 11; s: 6:” id do usuário “; s: 5:” 13908 “; s: 10:” id do cliente “; s: 1:” 3 “;}
a: 5: {s: 9: “invoiceid”; s: 1: “8”; s: 8: “saldo”; i: 14; s: 14: “trazido”; i: 13; s: 6: ” userid “; s: 5:” 13908 “; s: 10:” customerid “; s: 1:” 3 “;}
a: 5: {s: 9:” invoiceid “; s: 3:” 387 “; s : 8: “saldo”; i: 101; s: 14: “avançado”; i: 100; s: 6: “id do usuário”; s: 5: “13908”; s: 10: “id do cliente”; s: 1 : “4”;}
a: 5: {s: 9: “invoiceid”; s: 3: “387”; s: 8: “saldo”; i: 102; s: 14: “trazido”; i: 101 ; s: 6: “userid”; s: 5: “13908”; s: 10: “customerid”; s: 1: “4”;}
a: 5: {s: 9: “invoiceid”; s: 3 : “387”; s: 8: “equilíbrio”; i: 103; s: 14: “trazido para a frente”; i: 102; s: 6: “id do usuário”; s: 5: “13908”; s: 10: “id do cliente “; s: 1:” 4 “;}
a: 5: {s: 9:” invoiceid “; s: 3:” 492 “; s: 8:” saldo “; i: 3; s: 14:” trazido para a frente “; i: 2; s: 6:” userid “; s: 5:” 13908 “; s: 10:” customerid “; s: 3:” 179 “;}

Use esta consulta, o a: 5 acima me diz que existem 5 colunas. Depende do desenvolvedor saber quantas colunas estão nos dados serializados e se a estrutura é a mesma. Mas você entendeu.

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

O que me dá isso

fieldname1 fieldvalue1 fieldname2 fieldvalue2 fieldname3 fieldvalue3 fieldname4 fieldvalue4 fieldname5 fieldvalue5
“invoiceid” “8” “balance” 5 “trouxe para frente” 3 “userid” “13908” “customerid” “3”
“invoiceid” “8” “balance” 8 “trouxe para a frente” 5 “userid” “13908” “customerid” “3”
“invoiceid” “8” “balance” 10 “transferido” 8 “userid” “13908” “customerid” “3”
“invoiceid” “8” “saldo” 11 “transferido “10” ID do usuário “” 13908 “” ID do cliente “”3”
“invoiceid” “8” “balance” 13 “trazido” 11 “userid” “13908” “customerid” “3”
“invoiceid” “8” “balance” 14 “trazido” 13 “userid” “13908” ” ID do cliente “” 3 “
“invoiceid” “387” “balance” 101 “trouxe” 100 “userid” “13908” “customerid” “4”
“invoiceid” “387” “balance” 102 “trouxe” 101 “userid” “13908” “customerid” ” 4 ”
” invoiceid “” 387 “” balance “103” trouxe “102” userid “” 13908 “” customerid “” 4 ”
” invoiceid “” 492 “” balance “3” trouxe “2” userid “” 13908 “” customerid “” 179 “

Então tudo que eu preciso fazer é usar alguns aliases de coluna

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