I am having a table with a column that has few ids that were put into database with multi select. Column for example contains: 1,4,5,7,9. Is it possible to check if this column contains for example number 5 or not in it through MySQL query ?. I need to select all the people that have number 5 or some other listed in that field and print them through php.
-
2It's definitely possible. You can do it with a LIKE statement; though it would be easier for you if your database design was normalised.andrewsi– andrewsi2013-07-19 16:14:53 +00:00Commented Jul 19, 2013 at 16:14
Add a comment
|
4 Answers
http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set
SELECT ...
WHERE FIND_IN_SET(5, list_column)
But understand that this search is bound to be very slow. It cannot use an index, and it will cause a full table-scan (reading every row in the table). As the table grows, the query will become unusably slow.
Please read my answer to Is storing a delimited list in a database column really that bad?
You can use @MikeChristensen's answer to be more standard. Another trick with standard SQL is this:
select * from TableName
where ',' || ids || ',' LIKE '%,5,%'
(in standard SQL, || is the string concatenation operator, but in MySQL, you have to SET SQL_MODE=PIPES_AS_CONCAT or SET SQL_MODE=ANSI to get that behavior.)
Another MySQL-specific solution is to use a special word-boundary regular expression, which will match either the comma punctuation or beginning/end of string:
select * from TableName
where ids RLIKE '[[:<:]]5[[:>:]]'
None of these solutions scale well; they all cause table-scans. Sorry I understand you cannot change the database design, but if your project next requires to make the query faster, you can tell them it's not possible without redesigning the table.
3 Comments
Vuk Vasić
i know about normal forms :) and i know that this is bad. But the system is already created by someone else and i can't change it. Belive me i would make database normal if i could :D. Thanks this function is good :). I would accept this answer in 10 minutes :)
Mike Christensen
+1 for the built-in mySql function.. I'll leave my answer up since it's more standard..
Sumith Harshan
Super answer and worked perfectly. Voted. Thank you !
Perhaps:
select * from TableName
where ids = '5' -- only 5
or ids like '5,%' -- begins with 5
or ids like '%,5' -- ends with 5
or ids like '%,5,%' -- 5 in the middle somewhere
It probably won't be very fast on large amounts of data. I'd suggest normalizing these multi-selection values into a new table, where each selection is a single row with a link to TableName.
answered Jul 19, 2013 at 16:15
Mike Christensen
91.8k52 gold badges223 silver badges349 bronze badges
Comments
select * from your_table where concat(',',target_column,',') like '%,5,%'
you can write the sql query like this, for example you are looking for the number 5 select * from your_table_name where ids='5' if you want to check the result with php just tell me i will write it for you :)
3 Comments
andrewsi
That will work if it's the only entry in the field; if the field is '1,5,9', it won't find it.
Walid Naceri
yeah, :( sorry my english is abd now i understood the question well if the filed for example contain 1,5,9,10,3 and you want to check if the field contain 10 just write select * from your_table where ids like '%5%';
andrewsi
Ah, not quite - that would wrongly return people with the value 15.