MySQL Selecting Something with Array Information

开发者_Python百科

Is there a way to do something like this?

SELECT * FROM tablename WHERE x CONTAINS "1"

Basically, I want to select data from the database where x contains a specific number. The thing is, the x column in any row could contain "1, 2, 3" and I want to select all those that contain 1, specifically 1, not 11 or anything that contains 1, but specifically a 1.

Here's an example:

id   title   x
-------------------
1    row1    1,22,3
2    row2    1,5
3    row3    5,91
4    row4    70

And I want my query to return rows 1 and 2. I don't want row 3, as the 1 is inside the number 91. I don't want row 4 because there's no 1 there either.

---

You can use the FIND_IN_SET function like so:

SELECT * FROM tablename WHERE FIND_IN_SET('1', x)

This will also get optimised to use bit arithmetic if you are calling it on a SET type.

---

You can try this:

SELECT * FROM tablename WHERE x REGEXP "(^|,)1(,|$)"

---

Ideally you'd normalize your 'x' column out to a separate table.

But... you could also hack it like this:

SELECT * FROM tablename WHERE x LIKE '%,1' OR x LIKE '1,%' OR x LIKE '%,1,%'

This basically just handles the three different cases where the "1" is the first, last or a middle element in your list. (note if you've got a space after your commas you'd change the last part to '%, 1,%'

EDIT: Actually Dmitriy's REGEXP is nicer, and a'r's FIND_IN_SET looks ideal.