searching content of mysql fields
I have a table setup the following way
`id` int(11) NOT NULL AUTO_INCREMENT,
`eventid` int(11) NOT NULL,
`invites` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
In that table i have rows which look like this
1, 127, '32215, 55648, 89887, 55555, 66666, 33333'
2, 1开发者_开发问答68, '44444, 33333, 121212, 0540541'
How would i search the contnts of the invites
field for a match for a particular number such as if i wanted to search for 33333
, it would return both rows, but if i searched for 44444
it would return only the second?
I believe you can use find_in_set(), so something like:
select * from tablename where find_in_set('44444', invites);
This isn't very good database design though.
As a side note, allow me to note that your database schema - storing a set of values in a plain-text field - is improper. If you want to query on a one-to-many relationship like this, you ought to have another table that defines the relationship between a single event and a single invitation, like this:
eventid invitationid
1 15
1 16
1 17
2 18
3 19
3 20
Then you'll be able to do standard SQL queries that will actually be performant and will make sense.
精彩评论