MySQL: LIKE Query Help?

开发者_StackOverflow社区

I have a column in my table called student_id, and I am storing the student IDs associated with a particular record in that column, delimited with a | character. Here are a couple sample entries of the data in that column:

 243|244|245 
 245|1013|289|1012
 549|1097|1098|245|1099

I need to write a SQL query that will return records that have a student_id of `245. Any help will be greatly appreciated.

---

Don't store multiple values in the student_id field, as having exactly one value for each row and column intersection is a requirement of First Normal Form. This is a Good Thing for many reasons, but an obvious one is that it resolves having to deal with cases like having a student_id of "1245".

Instead, it would be much better to have a separate table for storing the student IDs associated with the records in this table. For example (you'd want to add proper constraints to this table definition as well),

CREATE TABLE mytable_student_id (
   mytable_id INTEGER,
   student_id INTEGER
);

And then you could query using a join:

SELECT * FROM mytable JOIN mytable_student_id 
   ON (mytable.id=mytable_student_id.mytable_id) WHERE mytable_student_id.student_id = 245

Note that since you didn't post any schema details regarding your original table other than that it contains a student_id field, I'm calling it mytable for the purpose of this example (and assuming it has a primary key field called id -- having a primary key is another requirement of 1NF).

---

@Donut is totally right about First Normal Form: if you have a one-to-many relation you should use a separate table, other solutions lead to ad-hoccery and unmaintainable code.

But if you're faced with data that are in fact stored like that, one common way of doing it is this:

WHERE CONCAT('|',student_id,'|') LIKE '%|245|%'

---

Again, I agree with Donut, but this is the proper query to use if you can't do anything about the data for now.

WHERE student_id like '%|245|%' or student_id like '%|245' or student_id like '245|%'

This takes care of 245 being at the start, middle or end of the string. But if you aren't stuck with this design, please, please do what Donut recommends.