.net mysql 参数,在MySQL .NET Provider中使用命名参数

In the MySQL .NET provider, you can use named parameters in the syntax:

?parametername

Now, I'm trying to create a named parameter for a parameter that will be used in an 'IN' list, e.g. :

select * from mytable where id in (?ids)

How can I use a named parameter with this, if I use varchar, it will add quotes before and after the list, ie.:

If I pass the value of the parameter using varchar:

cmd.Parameters.Add("?ids", MySqlDbType.Varchar).Value = ids; // ids is a string which contains the ids separated by commas, e.g. 1, 2, 3 .. etc

the query will be executed like this:

select * from mytable where id in ('1, 2 ,3')

Of course this will throw an error, how can I pass a named parameter without getting the quotes, this is the way it should be executed:

select * from mytable where id in (1, 2 , 3)

Is there any workaround for this? I'm currently using String.Format() but would like to use a named parameter, is this possible some how?

P.S. I'm only using plain text statements, no sproc, so none of this is going to be passed to an sproc (just in case you think it's not possible because sprocs don't accept arrays)

解决方案

This has been asked here so many times that I've stopped to count.

It's always the same answer, regardless of technology. You simply must add as may parameters to your query as you plan to have IN "arguments".

If you want to query WHERE id IN (1, 2 ,3), your prepared statement must look like this:

SELECT * FROM mytable WHERE id IN (?, ?, ?)

Use whatever string building facility you see fit to make such an SQL string. After that, add the three parameter values to it.

It's the whole point of prepared statements to separate SQL code from data. The commas are SQL code, you will never get them into the statement with a single parameter, they must be in before.

Okay, there is one alternative. Make a separate/temporary table, store your IDs in it and query something like this:

SELECT

*

FROM

mytable m

INNER JOIN searchtable s ON m.id = s.id