版本: 1.1.3
WHERE 语法
一些 Medoo 函数需要使用 $where 参数去过滤数据，SQL WHERE 子句是强大的但具有大量复杂的语法，逻辑关系并有潜在的安全问题比如SQL注入。Medoo 则提供了一个强大且易用的方法来建立 WHERE 查询子句并防止注入。
基本条件查询
基本条件查询非常简单，容易理解。您可使用附加符号来获取高级的数字范围。
$database->select("account", "user_name", [
"email" => "[email protected]"
]);
// WHERE email = '[email protected]'
$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
// [><] and [<>] 同样适用于 datetime
$database->select("account", "user_name", [
"birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
//WHERE "create_date" BETWEEN '2015-01-01' AND '2015-05-01' (now)
// 你不仅可以用于单个的字符串或数值，也可用于数组
$database->select("account", "user_name", [
"OR" => [
"user_id" => [2, 123, 234, 54],
"email" => ["[email protected]", "[email protected]", "[email protected]"]
]
]);
// WHERE
// user_id IN (2,123,234,54) OR
// email IN ('[email protected]','[email protected]','[email protected]')
// [否定的条件查询]
$database->select("account", "user_name", [
"AND" => [
"user_name[!]" => "foo",
"user_id[!]" => 1024,
"email[!]" => ["[email protected]", "[email protected]", "[email protected]"],
"city[!]" => null,
"promoted[!]" => true
]
]);
// WHERE
// `user_name` != 'foo' AND
// `user_id` != 1024 AND
// `email` NOT IN ('[email protected]','[email protected]','[email protected]') AND
// `city` IS NOT NULL
// `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"
]
]);
// 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" => "[email protected]"
],
"password" => "12345"
]
]);
// WHERE (user_name = 'foo' OR email = '[email protected]') AND password = '12345'
// [重要提示]
// 因为 Medoo 使用数组数据结构来描述关系条件查询，数组中重复的键将被覆盖。
//
// 以下是错误的:
$database->select("account", '*', [
"AND" => [
"OR" => [
"user_name" => "foo",
"email" => "[email protected]"
],
"OR" => [
"user_name" => "bar",
"email" => "[email protected]"
]
]
]);
// [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = '[email protected]')
// 要纠正这种情况，只需为每个 AND 和 OR 键名指定一个注释。内容可以任意。
$database->select("account", '*', [
"AND #Actually, this comment feature can be used on every AND and OR relativity condition" => [
"OR #the first condition" => [
"user_name" => "foo",
"email" => "[email protected]"
],
"OR #the second condition" => [
"user_name" => "bar",
"email" => "[email protected]"
]
]
]);
// SELECT * FROM "account"
// WHERE (
// (
// "user_name" = 'foo' OR "email" = '[email protected]'
// )
// AND
// (
// "user_name" = 'bar' OR "email" = '[email protected]'
// )
// )
LIKE 条件查询
LIKE 可用于基本条件查询和关系条件查询中，只需添加 [~] 语法。
// 默认情况下，关键字将被％前后包围起来。
$database->select("person", "id", [
"city[~]" => "lon"
]);
WHERE "city" LIKE '%lon%'
// 数组支持
$database->select("person", "id", [
"city[~]" => ["lon", "foo", "bar"]
]);
WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'
// 否定条件查询 [!~]
$database->select("person", "id", [
"city[!~]" => "lon"
]);
WHERE "city" NOT LIKE '%lon%'
// SQL LIKE 通配符
// 你可以使用 SQL 通配符来匹配更多复杂的情形
$database->select("person", "id", [
"city[~]" => "%stan" // Kazakhstan, Uzbekistan, Türkmenistan
]);
$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...
]);
排序 (更新自 v1.1)
$database->select("account", "user_id", [
// 单个条件
"ORDER" => "user_id",
// 多个条件
"ORDER" => [
// Order by column with sorting by customized order.
"user_id" => [43, 12, 57, 98, 144, 1],
// Order by column
"register_date",
// 降序 Order by column with descending sorting
"profile_id" => "DESC",
// 升序 Order by column with ascending sorting
"date" => "ASC"
]
]);
全文搜索
// [MATCH]
$database->select("post_table", "post_id", [
"MATCH" => [
"columns" => ["content", "title"],
"keyword" => "foo"
]
]);
// WHERE MATCH (content, title) AGAINST ('foo')
使用 SQL 方法
在某些特殊情况下，您可能需要使用 SQL 函数来处理数据。只需在字段前面分配 #，那么该值就不会被引用。
$data = $database->select('account', [
'user_id',
'user_name'
], [
'#datetime' => 'NOW()'
]);
// SELECT "user_id","user_name"
// FROM "account"
// WHERE "datetime" = NOW()
// [重要提示] 请记住，值应匹配为大写的 XXX()，否则不会被引用。
// 以下示例将失败。
$database->select('account', [
'user_id',
'user_name'
], [
'#datetime2' => 'now()',
'datetime3' => 'NOW()',
'#datetime4' => 'NOW'
]);
附加条件查询
$database->select("account", "user_id", [
"GROUP" => "type",
// 必须与 GROUP 一起使用
"HAVING" => [
"user_id[>]" => 500
],
// LIMIT => 20
"LIMIT" => [20, 100]
]);
// SELECT user_id FROM account
// GROUP BY type
// HAVING user_id > 500
// LIMIT 20,100