mysql可以用equals吗_MySQL where子句等於任何東西（SELECT * WHERE col = ANY_VALUE）

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:

我想在MySQL中創建一個具有可選值的查詢。指定值時，查詢將按該值進行過濾，此時值不是返回所有行。這是個主意：

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'

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

有沒有根據$ item的值創建兩個查詢字符串的簡單方法？

3 个解决方案

#1

18

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

據我所知，沒有這樣的“任何”占位符存在。

If you can use LIKE, you could do

如果你可以使用LIKE，你可以這樣做

SELECT * FROM table WHERE item LIKE '%'

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

如果你可以附加一個條件，你可以使item子句無效，如下所示：

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

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

(但在你的例子中不起作用，因為你傳遞“item”作為參數)

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.

這是我能看到的所有選項 - 使用兩個查詢可能最容易，在第二個查詢中完全刪除WHERE子句。

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))

...

}

#2

3

Better way to do this is first generate sql query from the parameter you need to bother on, and then execute.

更好的方法是首先從你需要打擾的參數生成sql查詢，然后執行。

function doQuery($params) {

$query = 'SELECT * FROM mytable ';

if (is_array($params) // or whatever your condition ) {

$query .= 'WHERE item = ' . $params[0];

}

$query .= ' ;';

// execute generated query

execute($query);

}

#3

-1

There's really no reason to do this with one query. Running the query with no WHERE clause makes it explicit that you want to return all rows. Running it with a WHERE clause makes it explicit that you want to filter the rows.

用一個查詢真的沒有理由這樣做。運行沒有WHERE子句的查詢會明確表示您要返回所有行。使用WHERE子句運行它會明確表示您要過濾行。