MySQL错误输出与IN子句和参数 [英] MySQL wrong output with IN clause and parameter

问题描述

我很难在MySQL中进行查询.

I'm having a hard time with a query in MySQL.

我正在使用Delphi XE，并且正在向MySQL发送带有某些参数的查询.这是查询的示例:

I'm working with Delphi XE and I'm sending a query with some parameter to MySQL. Here's an example for the query:

SELECT * FROM users u WHERE u.id IN (:idUsers);

':idUsers'是将接收我通过Delphi发送的参数的变量，该字符串是一个包含这样格式的字符串，例如:1,2,3

The ':idUsers' is the variable that will receive the parameter I send with Delp which is a string containing that is formatted like this, ex.: 1,2,3

问题在于，使用此字符串，我仅收到第一个用户(id = 1).从我看到的情况来看，就像MySQL在我发送的字符串的开头和结尾处添加了引号('')，就像它是'1,2,3'而不是1,2,3一样.我已经尝试过这种选择:

The problem is that with this string, I receive only the first user (id = 1). From what I can see, its just like MySQL is adding some quote ('') at the beginning and at the end of the string I send, like if it was '1,2,3' instead of 1,2,3. I've tried this select :

SELECT * FROM users u WHERE u.id IN ('1,2,3'); 

并且确实确实只返回第一个用户.

and it does indeed return only the first user..

我在MSSQL中有一个函数，该函数可以拆分发送的字符串并返回一个临时表，但是最近我们切换到了MySQL，从我读到的内容来看，MySQL不允许返回表.

I had a function in MSSQL that was splitting the string I sended and returning a temporary table, but we recently switched to MySQL, and from what I read, MySQL doesn't allow returning a table.

有人知道如何解决此问题吗?我一直在网上寻找答案，但没有找到针对此特定问题的答案.

Anyone has a clue on how to solve this problem? I've been scrapping the Web for an answer and haven't found one for this specific problem.

感谢您的帮助.

推荐答案

参数不能那样工作；他们不知道您要提供的是逗号分隔的值列表.如果设置ParamByName('WhatEver').AsString或Value，则认为您的意思是带引号的字符串，其中包含该参数中的所有内容.因此，它的作用完全相同-正在传递IN ('1, 2, 3')，而不是预期的IN (1, 2, 3).

Parameters don't work like that; they have no idea that what you're trying to provide is a comma-separated list of values. If you set ParamByName('WhatEver').AsString or Value, it thinks you mean a quoted string that contains everything that belongs in that parameter. So it's doing exactly what it appears to - it's passing IN ('1, 2, 3'), instead of the intended IN (1, 2, 3).

您将需要返回到自己自己解析并使用临时表的情况，或者动态执行WHERE子句并将其连接起来，然后再执行查询. (实际上，您可以动态构建查询，为IN子句中的每个项目创建一个新参数，然后遍历并为每个动态创建的参数分配值，但这很快就会变得非常难看.)

You'll need to either go back to parsing it out yourself and using a temp table, or build the WHERE clause dynamically and concatenating it before executing the query. (Actually, you could build the query dynamically, creating a new parameter for each of the items in the IN clause, and then loop through and assign values to each of those dynamically created parameters, but that gets very ugly very quickly.)

这篇关于MySQL错误输出与IN子句和参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！