Having a number in

Can someone give me a query that will return as a result rows ID 1 & 3?

ID Name Hidden 
1 Mika 1,4,2 
2 Loca 0 
3 Nosta 4 
4 Like 2 

Something like this SELECT * FROM table WHERE Hidden HAVING(4开发者_JS百科)

---

SELECT * FROM table WHERE FIND_IN_SET('4',Hidden);

docs for FIND_IN_SET

---

SELECT * FROM table WHERE CONCAT(',',Hidden,',') LIKE '%,4,%'

or you can avoid using LIKE like this

SELECT * FROM table WHERE INSTR(CONCAT(',',Hidden,','), ',4,') > 0

this will not get things like 40, 14, etc, but you need to make sure there are no spaces in the Hidden field (eg, 1, 4, 5 or update the concat and LIKE function accordingly.

---

SELECT * FROM table WHERE Hidden LIKE '%4%'

the % are wildcards.

---

Full Text Search might be a reasonable solution for this as long as you use the correct word breaks.

---

Either go with Full Text Search, as suggested, or

Spin the Hidden values off into a separate table, with the ID of current row.

Eg, Mika would have three entries in this table

ID = 1, Hidden =1
ID = 1, Hidden =4
ID = 1, Hidden =2

Then you could return results against this spin off table.

---

You may also want to consider normalizing the table and storing these "hidden" values in a separate table with an index on the apropriate column. Depending on the number of rows you have that would be much faster:

ID Hidden
1  1
1  4
1  2 
3  4 
4  2 

and:

SELECT DISTINCT table.* FROM table, hidden_table WHERE table.ID = hidden_table.ID AND hidden_table.hidden = 4