WHERE 语法
Medoo 的一些函数需要 $where 参数来过滤记录，类似于 SQL WHERE 子句，这虽然强大，但包含大量复杂语法、逻辑关系以及关于 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')
您不仅可以使用单个字符串或数字值，还可以使用数组。
$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')
否定条件
$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
// "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')
要纠正这个问题，只需为每个 AND 和 OR 键名分配一个注释（# 加一个空格）。注释内容可以是任何内容。
$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%'
分组
$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%'
复合条件
$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, 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...
]);
排序条件
$database->select("account", "user_id", [
// 单一条件。
"ORDER" => "user_id",
// 多重条件。
"ORDER" => [
// 按列排序，按自定义顺序排序。
"user_id" => [43, 12, 57, 98, 144, 1],
// 按列排序。
"register_date",
// 按列降序排序。
"profile_id" => "DESC",
// 按列升序排序。
"date" => "ASC"
]
]);
全文搜索
MySQL 数据库支持全文搜索功能，以获得高级搜索结果。
搜索模式列表
- natural - IN NATURAL LANGUAGE MODE
- natural+query - IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
- boolean - IN BOOLEAN MODE
- query - WITH QUERY EXPANSION
// [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)
使用正则表达式
$data = $database->select('account', [
'user_id',
'user_name'
], [
'user_name[REGEXP]' => '[a-z0-9]*'
]);
// WHERE "user_name" REGEXP '[a-z0-9]*'
使用 SQL 函数
您现在可以使用带有 raw 对象的 SQL 函数进行复杂使用。更多信息请访问 https://medoo.lvtao.net/2.0/doc.raw.php。
$data = $database->select('account', [
'user_id',
'user_name'
], [
'datetime' => Medoo::raw('NOW()')
]);
// WHERE "datetime" = NOW()
LIMIT 和 OFFSET
$database->select("account", "user_id", [
// 获取前 100 行。
'LIMIT' => 100,
// 从前 20 行开始，获取接下来的 100 行。
'LIMIT' => [20, 100],
// 对于 Oracle 和 MSSQL 数据库，您还需要一起使用 ORDER BY。
'ORDER' => 'location'
]);
GROUP 和 HAVING
$database->select("account", "user_id", [
'GROUP' => 'type',
// 按值数组分组。
'GROUP' => [
'type',
'age',
'gender'
],
'HAVING' => [
'user_id[>]' => 500
]
]);