Getting rows which include a value with MySQL
I have a MySQL 开发者_运维问答query which gets including some vars like that:
messages TABLE receiver cols
- user1 rows : 1,3,5
- user2 rows : 2,3
- user3 rows : 1,4
I want to get rows which includes '3' value. So I will get 'user1' and 'user2'.
I tried that but naturally it doesn't work.
mysql_query("SELECT * FROM messages WHERE receiver='3'");
How can I do this?
Try this:
mysql_query("SELECT * FROM messages WHERE receiver like '%3%'");
Like others have mentioned, trying to search denormalized data "1,2,3,4,44,5,55" in a relational database is not a good approach. Relational databases are built for handling normalized data.
You could.. note, could split the string into a subset (Split function in MySQL), then do a IN-search operation, but this is neither a recommended approach.
The best way to tackle the problem is from the top. Since trying to build around it, instead of actually fixing it, is never a good way to go.
The solution; Normalize the data.
Message
- [PK] Id
- Body...
Reciever
- [PK] Id
- Name...
MessageReceiver
- [FK] MessageId
- [FK] ReceiverId
Having this table structure, you could easily query:
SELECT M.* FROM MessageReceiver MR
INNER JOIN Message M ON M.Id=MR.MessageId
WHERE MR.ReceiverId=3
Solving it this way will save you a lot of headache in the future.
The easiest way would be to restructure your database.
Have a table messages
with id
, text
, etc., a table receivers
with id
, name
and a table msg2rec
(or however you wanna call it) with id_msg
and id_rec
.
That way you can do:
SELECT m.text, r.name
FROM msg2rec m2r
LEFT JOIN messages m ON m2r.id_msg = m.id
LEFT JOIN receivers r ON m2r.id_rec = r.id
WHERE m2r.id_rec = 3
Alternatively, if you keep your structure you should use:
SELECT text
FROM messages
WHERE rec RLIKE (",3,") OR rec RLIKE("^3,") OR rec RLIKE(",3$")
There's probably one single regexp that does the same as the 3 above, but I can't figure it out right now. Anyway, I would go with the first option: cleaner, easier to mantain and to work with
精彩评论