WHERE 語法

版本: v2.3.0

WHERE 條件語法

某些 Medoo 方法支援傳入 $where 參數來篩選記錄，其作用類似 SQL 的 WHERE 子句。手寫 SQL 條件往往既複雜又容易引入 SQL 注入風險，而 Medoo 提供了結構化、可讀性更高的寫法來安全建立 WHERE 條件。

基本條件

基本條件易於使用。您也可以將運算子附加到欄位名稱後方，以建立更進階的比較條件，特別適合數值欄位。

$database->select("account", "user_name", [
	"email" => "foo@bar.com"
]);
// WHERE email = 'foo@bar.com'

$database->select("account", "user_name", [
	"user_id" => 200
]);
// WHERE user_id = 200

$database->select("account", "user_name", [
	"user_id[>]" => 200
]);
// WHERE user_id > 200

$database->select("account", "user_name", [
	"user_id[>=]" => 200
]);
// WHERE user_id >= 200

$database->select("account", "user_name", [
	"user_id[!]" => 200
]);
// WHERE user_id != 200

$database->select("account", "user_name", [
	"age[<>]" => [200, 500]
]);
// WHERE age BETWEEN 200 AND 500

$database->select("account", "user_name", [
	"age[><]" => [200, 500]
]);
// WHERE age NOT BETWEEN 200 AND 500

[<>] 和 [><] 運算子也可以與日期時間值一起使用。

$database->select("account", "user_name", [
	"birthday[<>]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);

$database->select("account", "user_name", [
	"birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);

WHERE ("birthday" BETWEEN '2015-01-01' AND '2017-01-01')
WHERE ("birthday" NOT BETWEEN '2015-01-01' AND '2017-01-01')

您不僅可以傳遞單一字串或數值，還可以傳遞一個陣列。在這種情況下，Medoo 會產生 IN 條件。

$database->select("account", "user_name", [
	"OR" => [
		"user_id" => [2, 123, 234, 54],
		"email" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"]
	]
]);

WHERE
user_id IN (2,123,234,54) OR
email IN ('foo@bar.com','cat@dog.com','admin@medoo.in')

否定條件

使用 [!] 運算子建立否定比較，例如 !=、NOT IN 和 IS NOT NULL。

$database->select("account", "user_name", [
	"AND" => [
		"user_name[!]" => "foo",
		"user_id[!]" => 1024,
		"email[!]" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"],
		"city[!]" => null,
		"promoted[!]" => true
	]
]);

WHERE
"user_name" != 'foo' AND
"user_id" != 1024 AND
"email" NOT IN ('foo@bar.com','cat@dog.com','admin@medoo.in') AND
"city" IS NOT NULL AND
"promoted" != 1

您也可以使用 select() 或 get() 的結果作為條件的值。

$database->select("account", "user_name", [
	"user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
]);

WHERE user_id IN (2, 51, 321, 3431)

邏輯條件

邏輯條件描述多個表達式之間的關係。使用 AND 和 OR 建立更複雜的查詢。

基本用法

$database->select("account", "user_name", [
	"AND" => [
		"user_id[>]" => 200,
		"age[<>]" => [18, 25],
		"gender" => "female"
	]
]);

// Medoo 預設使用 AND 組合條件。下方查詢與其等價。
$database->select("account", "user_name", [
	"user_id[>]" => 200,
	"age[<>]" => [18, 25],
	"gender" => "female"
]);

WHERE user_id > 200 AND age BETWEEN 18 AND 25 AND gender = 'female'

$database->select("account", "user_name", [
	"OR" => [
		"user_id[>]" => 200,
		"age[<>]" => [18, 25],
		"gender" => "female"
	]
]);

WHERE user_id > 200 OR age BETWEEN 18 AND 25 OR gender = 'female'

嵌套條件

$database->has("account", [
	"AND" => [
		"OR" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"password" => "12345"
	]
]);

WHERE (user_name = 'foo' OR email = 'foo@bar.com') AND password = '12345'

因為 Medoo 使用陣列來描述邏輯條件，所以同一個陣列中的重複鍵會互相覆寫。

// 這不會如預期運作。
$database->select("account", '*', [
	"AND" => [
		"OR" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"OR" => [
			"user_name" => "bar",
			"email" => "bar@foo.com"
		]
	]
]);
// [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = 'bar@foo.com')

為了避免鍵衝突，請在邏輯鍵名稱後面附加註解。註釋以 # 開頭，可以包含任何文字。

$database->select("account", '*', [
	"AND #註解可用於任意 AND 或 OR 邏輯條件" => [
		"OR #第一組條件" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"OR #第二組條件" => [
			"user_name" => "bar",
			"email" => "bar@foo.com"
		]
	]
]);

WHERE (
	("user_name" = 'foo' OR "email" = 'foo@bar.com')
	AND
	("user_name" = 'bar' OR "email" = 'bar@foo.com')
)

欄位間比較

$database->select("post", [
	"[>]account" => "user_id",
], [
	"post.content"
], [
	// 在完整限定欄位名稱上使用運算子比較欄位，例如 [=]、[>]、[<] 與 [!=]。
	"post.restrict[<]account.age"
]);

WHERE "post"."restrict" < "account"."age"

LIKE 條件

使用 [~] 運算子建立 LIKE 條件。它可以在基本條件和邏輯條件下使用。

// 預設會在關鍵字兩側自動加上 %，從而執行包含比對。
$database->select("person", "id", [
	"city[~]" => "lon"
]);

WHERE "city" LIKE '%lon%'

多組 LIKE 比對

$database->select("person", "id", [
	"city[~]" => ["lon", "foo", "bar"]
]);

WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'

否定 LIKE 條件

$database->select("person", "id", [
	"city[!~]" => "lon"
]);

WHERE "city" NOT LIKE '%lon%'

組合 LIKE 條件

$database->select("person", "id", [
	"content[~]" => ["AND" => ["lon", "on"]]
]);

WHERE ("content" LIKE '%lon%' AND "content" LIKE '%on%')

$database->select("person", "id", [
	"content[~]" => ["OR" => ["lon", "on"]]
]);

WHERE ("content" LIKE '%lon%' OR "content" LIKE '%on%')

SQL 萬用字元

您可以搭配 SQL 萬用字元進行更進階的比對。

$database->select("person", "id", [
	"city[~]" => "%stan" // Kazakhstan, Uzbekistan, Turkmenistan
]);

$database->select("person", "id", [
	"city[~]" => "Londo_" // London, Londox, Londos, ...
]);

$database->select("person", "id", [
	"name[~]" => "[BCR]at" // Bat, Cat, Rat
]);

$database->select("person", "id", [
	"name[~]" => "[!BCR]at" // Eat, Fat, Hat, ...
]);

排序條件

使用 ORDER 對結果集進行排序。

// 依單一欄位排序。
$database->select("account", "user_id", [
	"ORDER" => "user_id"
]);

// 依多個欄位排序。
$database->select("account", "user_id", [
	"ORDER" => [
		// 使用自訂順序對某欄位排序。
		"user_id" => [43, 12, 57, 98, 144, 1],

		// 使用預設方向依某欄位排序。
		"register_date",

		// 依遞減順序排序。
		"profile_id" => "DESC",

		// 依遞增順序排序。
		"date" => "ASC"
	]
]);

全文搜尋

MySQL 支援全文搜尋，可用來建立更進階的搜尋查詢。

搜尋模式

natural	自然語言模式
natural+query	具查詢擴充的自然語言模式
boolean	布林模式
query	查詢擴充模式

// MATCH 條件。
$database->select("post_table", "post_id", [
	"MATCH" => [
		"columns" => ["content", "title"],
		"keyword" => "foo",

		// 可選：搜尋模式。
		"mode" => "natural"
	]
]);

WHERE MATCH (content, title) AGAINST ('foo' IN NATURAL LANGUAGE MODE)

正規表示式

使用 [REGEXP] 運算子將值與正規表示式進行比對。

$data = $database->select('account', [
	'user_id',
	'user_name'
], [
	'user_name[REGEXP]' => '[a-z0-9]*'
]);

WHERE "user_name" REGEXP '[a-z0-9]*'

SQL 函數

在更進階的用法中，您可以透過 raw() 物件使用 SQL 函式。更多說明請參閱 raw 頁面。

$data = $database->select('account', [
	'user_id',
	'user_name'
], [
	'datetime' => Medoo::raw('NOW()')
]);

WHERE "datetime" = NOW()

LIMIT 與 OFFSET

使用 LIMIT 限制傳回的行數。您也可以指定偏移量。

// 回傳前 100 列。
$database->select("account", "user_id", [
	"LIMIT" => 100
]);

// 略過前 20 列，然後回傳接下來的 100 列。
$database->select("account", "user_id", [
	"LIMIT" => [20, 100]
]);

// 對於 Oracle 與 MSSQL，還需要 ORDER BY。
$database->select("account", "user_id", [
	"LIMIT" => [20, 100],
	"ORDER" => "location"
]);

GROUP BY 與 HAVING

使用 GROUP 將資料列分組，再透過 HAVING 過濾分組後的結果。

// 依單一欄位分組。
$database->select("account", "user_id", [
	"GROUP" => "type"
]);

// 依多個欄位分組。
$database->select("account", "user_id", [
	"GROUP" => [
		"type",
		"age",
		"gender"
	]
]);

// 對資料列進行分組，並過濾分組後的結果。
$database->select("account", "user_id", [
	"GROUP" => "type",
	"HAVING" => [
		"user_id[>]" => 500
	]
]);