MySQL where子句等于任何东西(SELECT * WHERE col = ANY_VALUE) [英] MySQL where clause equals anything (SELECT * WHERE col = ANY_VALUE)

问题描述

我想在MySQL中创建一个具有可选值的查询.指定该值后，将根据该值过滤查询，而当该值不是时，将返回所有行.这是主意:

I'd like to create a query in MySQL that has an optional value. When the value is specified the query is filtered by that value, when the value is not all rows are returned. Here's the idea:

public function doQuery($item = 'ANY_VALUE') {
  $query = "SELECT * FROM table WHERE item = ?";
  db->fetchAll($query,array($item))
  ...
}

doQuery(); // Returns everything
doQuery($item='item1'); // Returns only rows where item = 'item1'

是否有一种简单的方法，而不必根据$ item的值创建两个查询字符串?

Is there an easy way to do this without creating two query strings depending on the value of $item?

推荐答案

据我所知，不存在这样的任何"占位符.

As far as I know, no such "any" placeholder exists.

如果可以使用LIKE，则可以

If you can use LIKE, you could do

SELECT * FROM table WHERE item LIKE '%'

如果可以附加条件，则可以像这样使item子句无效:

if you can append a condition, you could nullify the item clause like this:

SELECT * FROM table WHERE item = ? OR 1=1

(尽管在您的示例中不起作用，因为您将"item"作为参数传递)

(won't work in your example though, because you are passing "item" as a parameter)

这就是我能看到的所有选项-使用两个查询可能是最简单的，在第二个查询中完全删除WHERE子句.

That's all the options I can see - it's probably easiest to work with two queries, removing the WHERE clause altogether in the second one.

这可能行得通，但我不确定从数据库的角度来看这是否是个好主意.

This would probably work, but I*m not sure whether it's a good idea from a database point of view.

public function doQuery($item = 'ANY_VALUE') {
  $query = "SELECT * FROM table WHERE item = ? OR 1 = ?";
  db->fetchAll($query,array($item, ($item == 'ANY_VALUE' ? 1 : 0))
  ...
}

这篇关于MySQL where子句等于任何东西(SELECT * WHERE col = ANY_VALUE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！