mysql就像不是通配符

I have this table:

+---------+----------+
+  Items  +  Person  +
+---------+----------+
+ 2,99,75 +  Jack    +
+ 4,9,63  +  Rose    +
+---------+----------+

Now I do a simple

LIKE :items

and binding it using

$stmt->bindParam(':items',$item,PDO::PARAM_STR);

where

$item = "%9%".

The result contains both Jack and Rose, which is wrong because I expected to have Rose only as my result. It seems that LIKE sees both 99 and 9. How can I restrict my LIKE to have only 9 because that was the value of $items?

Its because % represent one or more character (anything). So 99 will match the "%9%"

If you want only 9, you can try with

"%,9,%"

I believe the main issue on this is what @interrobang said, the way you are representing the data.

If this table X that you show is the list itens for each person, you should have a column with the person id and another column with the item id, and multiple lines to represent multiple itens for each person. Doing like this your search would be much faster and easier to use and mantain in the future.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

CREATE TABLE person (
     id int auto_increment primary key, 
     name varchar(20)
);

CREATE TABLE item (
     id int auto_increment primary key, 
     name varchar(20)
);

CREATE TABLE person_item (
     id int auto_increment primary key, 
     person_id int,
     item_id int
);

ALTER TABLE person_item ADD UNIQUE (person_id,item_id);

INSERT INTO person(id,name) VALUES
(1, 'John'),
(2, 'Mary'),
(3, 'Oliver');


INSERT INTO item (id,name) VALUES
(1,'Pen'),
(2,'Pencil'),
(3,'Book');

INSERT INTO person_item (person_id,item_id) VALUES
(1,1),
(1,3),
(2,2),
(3,1);

Query 1:

select p.name from person_item pi, person p, item i
where pi.person_id = p.id
and pi.item_id = i.id
and i.name LIKE 'Book%'

Results:

| NAME |
--------
| John |

The other answers are good to do. However, I pose this alternative based on the fact that Items appears to be ID's.

If you need to query off comma separated values I would recommend a separate table. Using LIKE to query in a single field will never truly be fool-proof and could be a security concern. Try this.

Table 1: Person

+---------+----------+
+  ID     +  Person  +
+---------+----------+
+ <int>   + <string> +
+---------+----------+

Table 2: Item

+---------+----------+
+ PersonID+  ItemID  +
+---------+----------+
+ <int>   + <int>    +
+---------+----------+

Then use joins to query both tables as needed.

SELECT * FROM Person INNER JOIN Items ON Items.PersonID = Person.ID 
   WHERE Items.ItemID = '9';

This should provide you with every record in Person that has ItemID "9" associated with them.

Perhaps this might help: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html