mysql，查询到 where 条件，如果一个值 = 任何东西 [英] mysql, query with to where condation, if the one value = any thing
问题描述
表格
id name value
1 abc 12
2 def 13
4 mkg 14
5 pcr 10
select * from table where name = 'abc' and value = 12;--------------------- 这会给我
select * from table where name = 'abc' and value = 12;--------------------- this will give me result of
1 abc 12
SELECT * FROM table WHERE name = 'abc' AND value = 12 OR 1=1
这将列出我所有的项目，因为它应该只列出 name='abc' 然后搜索 12 的值或任何东西，如果不是 12 意味着空白它应该没问题.
this will list me all the items, as it should only list the name='abc' and then search for the value of 12 or any thing if not 12 means blank it should fine anything.
1 abc 12
2 def 13
4 mkg 14
5 pcr 10
这里我只想得到 name='abc'，但每次查询时我都必须运行拖曳条件.like (select * from table where name=x and value=x or 1=1)
Here i want to get only the name='abc', but i have to run the tow conditions every time i do query. like (select * from table where name=x and value=x or 1=1)
应该显示:
1 abc 12
idia 是用户将通过输入为其中一列赋值，结果应根据用户输入进行搜索.
the idia is user will give value for one of the column by input and the resut shoudl search acording to the user input.
query: (SELECT * FROM table WHERE name = x or 1=1 AND (value = x or 1=1)
所以如果用户不只查询 name='abc' 和 value any
so if the user doest query only for name ='abc' and value any
select * from table where name = 'abc' OR 1=1 and (value = '' OR 1=1):
select * from table where name = '' OR 1=1 and (value = 12 OR 1=1):
因为用户会将其中之一放在 name 或 value 中，所以我必须在两者中都放入 OR 1=1
because the user will put one of them either the name or value so i have to put the OR 1=1 in both of them.
<form>
<input type='text' name='name'>
<input type='text' name='value'>
<submit>
</form>
那么现在
1- 如果用户为两者都设置了值，则可以使用简单的 Q= 其中 x=x 和 y=y
1- if the user put value for both, it's ok with a simple Q= where x=x and y=y
2= 如果用户为 one(name) 输入值 Q= WHERE name=x and value = '' OR 1=1;
2= if the user put value for one(name) Q= WHERE name=x and value = '' OR 1=1;
3- 如果用户为 one(value)Q=select * from table WHERE name = 'abc' and (value = '' OR 1=1) 输入值:
3- if the user put value for one(value)Q=select * from table WHERE name = 'abc' and (value = '' OR 1=1):
4-如果用户为 both(name,value) 输入值 Q=select * from table WHERE name='abc' AND value=12;
4-if the user put value for both(name,value) Q=select * from table WHERE name='abc' AND value=12;
现在我想要可以管理所有的查询，所以我做到了:
now i want the query that can manage all so i did:
query = SELECT * FROM table where name = Get-INPUT OR 1=1 AND (value= Get-INPUT or 1=1)
但这对我不起作用，因为条件 (1=1) 为真，它向我显示了所有记录.问题:如何管理我的表单，如果用户输入一个条件其他条件只是用户 1=1-true
but this is not working for me, as the condition (1=1) is true, it shows me all records. question: how to manage my form, if user input for one condition other condition just user 1=1-true
问候
推荐答案
在查询中使用方括号，以逻辑方式对 WHERE 子句项进行分组.
Use brackets in your query, to group your WHERE clause items logically.
WHERE name = 'abc' AND (value = 12 OR 1=1)
如果您尝试提供一个接受两个变量的查询，并根据这些变量进行搜索，那么您的逻辑只会稍微偏离.
If you are trying to provide a query that accepts two variables, and searches based on those variables you're only slightly off in your logic.
提供两个变量
假设我们有两个变量 @incoming_name 和 @incoming_value:
Presuming we have two variables @incoming_name and @incoming_value:
SET @incoming_name = 'abc';
SET @incoming_value = 12;
SELECT * FROM table1
WHERE (@incoming_name = name OR @incoming_name IS NULL)
AND (@incoming_value = value OR @incoming_value IS NULL)
这会给你
1 abc 12
提供一个变量
然后，如果用户为这些值中的任何一个提供 NULL，它将在您的搜索中跳过它们.
Then if the user supplies NULL for either of those values, it will skip over them in your search.
SET @incoming_name = 'abc';
SET @incoming_value = NULL;
SELECT * FROM table1
WHERE (@incoming_name = name OR @incoming_name IS NULL)
AND (@incoming_value = value OR @incoming_value IS NULL)
这仍然给你预期的结果
1 abc 12
没有提供变量
如果用户没有提供任何变量来缩小搜索范围
If the user supplies neither of the variables to narrow down the search
SET @incoming_name = NULL;
SET @incoming_value = NULL;
SELECT * FROM table1
WHERE (@incoming_name = name OR @incoming_name IS NULL)
AND (@incoming_value = value OR @incoming_value IS NULL)
您将获得表格中的所有结果
You will get all the results from the table
1 abc 12
2 def 13
4 mkg 14
5 pcr 10
在脚本中使用变量
使用此方法您不必在 MySQL 中SET 变量名，这仅用于演示目的.您也可以将代码/脚本中的值放入其他语言.例如使用 PHP 和 PDO Prepared Statments
Using this method you do not have to SET variable names in MySQL, that was just for demonstration purposes. You can put the value from your code/script in other languages as well. For example using PHP and PDO Prepared Statments
SELECT * FROM table1
WHERE (:incoming_name = name OR :incoming_name IS NULL)
AND (:incoming_value = value OR :incoming_value IS NULL)
然后您可以将您的用户变量绑定到 :incoming_name 和 :incoming_value，它会按照您的预期进行搜索.如果其中一个为空，它将跳过它们，否则在搜索中将需要它们.
Then you can bind your user variables to :incoming_name and :incoming_value and it will search as you expect. If either are null it will skip over them, otherwise they'll be required in the search.
警告:我没有测试过这段代码，只是为了让您大致了解它是如何工作的.它使用 PHP PDO 和此处记录的准备好的语句.
Warning: I have not tested this code, it's just to give you a rough idea on how it would work. It uses PHPs PDO and prepared statements as documented here.
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$stmt = $dbh->prepare("SELECT * FROM table1
WHERE (:incoming_name1 = name OR :incoming_name2 IS NULL)
AND (:incoming_value1 = value OR :incoming_value2 IS NULL)");
$stmt->bindParam(':incoming_name1', $name);
$stmt->bindParam(':incoming_name2', $name);
$stmt->bindParam(':incoming_value1', $value);
$stmt->bindParam(':incoming_value2', $value);
if ($stmt->execute()) {
while ($row = $stmt->fetch()) {
var_dump($row);
}
}
您需要将查询中的每个参数绑定一个单独的名称，这就是为什么当我们想要两次相同的参数时，我们需要给它一个单独的名称(incoming_value1 和 incoming_value2)
You need to bind each param in the query with a separate name, which is why when we want the same param twice we need to give it a separate name (incoming_value1 and incoming_value2)
这篇关于mysql，查询到 where 条件，如果一个值 = 任何东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
