Barredo: Ackpacket: I don’t think that it does anything meaningful
Dufer: Hi, what comlumn type would be best to store some settings serialized array
Goda: Hi guys. How can I read a specific information out of a serialized column?
Strathman: Hey look, the same terrible idea two times in a row
Jerone: It was not my idea to store serialized data in mysql. But I’m confronted with having to read it now
Strathman: Either consider a one-to-many table that stores the contents of an array or store it as VARBINARY and then p**** it in your application
Strathman: MySQL won’t know what the contents of the array should look like apriori, so you have to fetch it all and let your application decide
Abrey: So I can’t search in that serialized array with MySQL?
Strathman: You can do something naive like a LIKE search against it but who knows if that will match what you actually want. That depends on the serialization format.
Casso: I want to find a specific “key” = “value” pair. How can I do that?
Caroselli: Searching for key is no problem, but how do I find it’s value?
Strathman: You may be able to do a regex with REGEXP
Clyatt: Soee: It would be best to not store array in single column
Pendexter: How about this?: http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/
Clyatt: JosefDe: if key and value are two different columns everything is fine and extremely easy. Not only with MySQL, but with any other RDBMS
Clyatt: JosefDe: If keys and values are stored together in same column your worst nightmares are yet to come
Sow: Salle: but it is for some settings that are rarely updated and there is plenty of them
Clyatt: Soee: Storing array as a single column makes sense only in case: IF you store the whole array AND *always* INSERT the entire array at once AND you *always* SELECT the entire array at once AND most important you are 100% sure that nobody never, ever in any point in the future will need to search something within that array.
Clyatt: Soee: If there is slightest chance someday someone will need something like “Find if the 3rd element of the array is equal to foo” then you don’t use single column. More on that here:
Rindels: Soee: http://gtowey.blogspot.com/2009/12/how-to-fix-comma-separated-list-of-doom.html
Siddons: Hello. I have a query question. I am doing a sumdate condition with a group by on a column that has an enum of statuses. This works well to get me counts of how many items are in what status on the date condition in the sum. But my problem is that it will only show me 0 sum results for “statuses” that are already in the table. But let’s say there’s a FAILED status, that no row in the table has. Then it does not show up in the resulting q
Wildey: Is there any way I can still have a query result row like FAILED – 0?
Stave: TheRealGent: Use an outer join.
Norman: Xgc, outer join against what? There are no joins in this query.
Rhodus: And status isn’t an lk table.
Schnader: TheRealGent: One way is to have a table of the distinct list of possible status values.
Vinsel: TheRealGent: Well, you need that.
Clyatt: TheRealGent: Why? What’s so difficult about creating such table?
Savitz: Is there no information schema query I can use to get a list of the enum values?
Kogel: TheRealGent: It only makes sense. Why wouldn’t you want a list of all possible status values?
Howieson: Salle, maintainability.
Beards: TheRealGent: That’s the problem with enum.
Clyatt: TheRealGent: You get much better maintanability with separate table especially compared to ENUM
Bierle: So a dev adds a new status – 1 Add to Java enum. 2 Add to column enum. 3 Add to lk_table. Not to mention how do you do***ent this. Because the likely scenario is the dev will only do #1 and #2
Cluff: Ah, you’re saying ditch the enum for the LK
Clyatt: TheRealGent: Exactly!