Hello,
I have a field in a table with states abbr. that have been serialized example,
s:155:"AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VI,VT,WA,WI,WV,WY";
The above example is one with all states, but some just have one state or a few it depends on how many the client selected.
How would I write a select statement to pull all the records that have "CA" in the list for example.
I tried this: select * from locations where 1 and states REGEXP '["]CA["]' but this only returns records with just CA
s:2:"CA";
Im looking for a way to grab those and also the records with multiple states if CA is in it.
Any help would be greatly appreciated.
Thanks in advance
Select query serialized field
Moderator: General Moderators
- Jonah Bron
- DevNet Master
- Posts: 2764
- Joined: Thu Mar 15, 2007 6:28 pm
- Location: Redding, California
Re: Select query serialized field
You can select them like this:
But if you need select based on that, it would be better not to serialize that field, and instead create a many-to-many relationship between locations and states (this would require two extra tables).
Code: Select all
SELECT * FROM locations WHERE states LIKE "%CA%"Re: Select query serialized field
Don't serialize, use SQL tables. Normalize!
There are 10 types of people in this world, those who understand binary and those who don't
Re: Select query serialized field
I agree with Vladsun. Serializing (if that's the correct term for this) breaks First Normal Form.
[Edit:] I have just looked up serializing and my interpretation is that it is inappropriate in this use. This article discusses where it is appropriate to use and what the consequences are, primarily the very problem you are having: searching. It violates the rules for relational databases.
http://www.mysqlperformanceblog.com/201 ... -database/
[Edit:] I have just looked up serializing and my interpretation is that it is inappropriate in this use. This article discusses where it is appropriate to use and what the consequences are, primarily the very problem you are having: searching. It violates the rules for relational databases.
http://www.mysqlperformanceblog.com/201 ... -database/
Last edited by califdon on Wed Dec 15, 2010 5:11 pm, edited 2 times in total.
Reason: Added comments.
Reason: Added comments.
Re: Select query serialized field
Yes, denormalizing is used in some cases - but one still has to have a normalized DB. Then one can easily build denormalized tables/view from it. That's what data warehouse is.
There are 10 types of people in this world, those who understand binary and those who don't