At AtYourService.com.cy we send a lot of emails. Especially when you compare it to the Cyprus market we send even more. Advocates of the Lean method we measure and report EVERYTHING. We even monitor some data real time. Reporting of a live table is a no go when you are running a real time application. So a lot of reports contain a lot of GROUP_CONCAT fields. When it comes to combinig these reprots with a semi hot table its kind of a mess to join over a field containing comma separated keys from another table.
SELECT * FROM table1,report_table2 WHERE date BETWEEN 2015-01-01 AND NOW() AND table1.id in (x1,x2,x3)
But x1,x2,x3 is actually a text field in report_table2.
WHERE id in (table.field)
throws an error (well maybe not if the values are integers and MySql CASTs them to the first scalar value and if you are lucky the expression will evaluate to true if you hit the first one)
The solution?
SELECT * FROM table1,report_table2 WHERE date BETWEEN 2015-01-01 AND NOW() AND FIND_IN_SET(table1.id,report_table2.field)
Maybe I should have known this but I didnt. It blew my mind and I thought I had to share.
Share your favourite MySQL function in the comments below