MySQL prepared statement using IN

Question

I want to execute a prepared statement like

prepare stmt1 from 'select * from tags where name in (?)'

set @a='tag1, tag2' ;

execute stmt1 using @a ;

But alas, it does not work as expected (it seems to treat the argument to in as a single string).

Is it possible to form a list like this using in in a prepared statement?

Donnie · Accepted Answer · 2010-12-22 01:16:50Z

3

In short, no.

THe basic query that you're telling to to try to run is select * from foo where bar in ('tag1, tag2'), which means it's matching the string 'tag1, tag2' and not treating it as a list.

Unfortunately, with in, you typically need to do dynamic queries. Other options are a UDF which parse out the string, or a temp table you join against.

answered Dec 22, 2010 at 1:16

Donnie

47k10 gold badges67 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bobobobo Over a year ago

Ok, hold on. How do you parse the string in a user defined function? You can't just run an exec type statement on the list and have it work can you?

Mark Byers · Accepted Answer · 2010-12-22 01:16:36Z

1

No. You can build the SQL dynamically (using for example CONCAT) and then execute it.

Or you can use multiple parameters:

prepare stmt1 from 'select * from tags where name in (?, ?)'

set @a='tag1';
set @b='tag2';

execute stmt1 using @a, @b;

answered Dec 22, 2010 at 1:16

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

MySQL prepared statement using IN

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related