如何通过mysql中序列化格式的字段中的特定记录进行排序

Question

I'm in big trouble in mysql/php.

I've stored address/city/country etc, in database using serialize method. But now, I don't know how to find particular city from serialize data and how to sort this records in mysql.

My serialize data is :

a:6:{s:12:"country_code";s:2:"IT";s:10:"country_id";s:3:"105";s:7:"Address";s:16:"Via Emilia 234/B";s:3:"Zip";s:5:"40068";s:4:"City";s:21:"San Lazzaro Di Savena";s:8:"Province";s:2:"BO";}

I want to find province = "BO" from above data and also sort this data by province in asc or desc order.

I don't want to use like.

Please help me!

Answer 1

When you choosed to store php serialized value in a db column (schemaless paradigm?) which is not understod by mysql engine (that sees it as a text value), you locked out yourself to use sql based on that values (except string functions).

What you can do is for those fields you want order by where etc. create columns.

With postgresql you may choose JSONB to stay schemaless but use sql on value , i am not familiar with mysql's json support.