User Defined Variable used within IN of WHERE clause?

[msutfin1]

Prepared statements work fine in general. Having a bit 'o difficulty with the syntax for using a variable in the IN portion of a WHERE clause. Works great with a single value, but not more than one.. i.e.

SET @list = "2";

PREPARE my_stmt FROM 'SELECT * FROM <table> WHERE my_id in (?)'
EXECUTE my_stmt Using @list;

query ok, 1 row affected
rows matched: 1
--cool...

If I change the SET statement to:

SET @list = "2, 3";
EXECUTE my_stmt Using @list;

query OK, 0 rows affected
rows matched: 1

so, mysql "sees" just the first value in the list, but for some reason is unable to affect any rows..

Just point me to the reading material is there's someone who has already covered this succinctly..

Much thanks!

 

[towerbase]

Forget about PREPARE for the moment and try your SQL statements in the basic form. You will find that

SELECT * FROM <table> WHERE my_id in ("2")

gives the records for where my_id = 2. But

SELECT * FROM <table> WHERE my_id in ("2, 3")

also only gives the records for where my_id = 2. This is because my_id is expecting a numeric value. MySQL tries to make a numeric value out of your string "2" and indeed converts it to 2. However, when converting "2, 3" it stops when it reaches the comma and also converts that string to a 2.

Each parameter requires its own ? place holder.

Andrew
Hampshire, UK

 

[grtammi]

Depending on your version of MySQL, you can use FIND_IN_SET in your WHERE clause after version 5.0:

SET @list = "2" ;
PREPARE my_stmt FROM "SELECT * FROM `<table>` WHERE FIND_IN_SET(`my_id`, ?) != 0" ;
EXECUTE my_stmt USING @list;

returns 1 result, whereas :

SET @list = "2,3,4" ;
PREPARE my_stmt FROM "SELECT * FROM `<table>` WHERE FIND_IN_SET(`my_id`, ?) != 0" ;
EXECUTE my_stmt USING @list;

returned 3 results as expected (the id's were in the table I tested with. )

I'm not sure if there's a performance hit or not using FIND_IN_SET, but on small sets, doesn't seem like much at all. The table I used had 20000 records and took no longer than 3ms to retrieve the records.

Also, I think there's a hard limit on the number of id's in the set you can use.

Greg