medoo 支持5大数据库
最后更新于：2022-04-02 02:25:33
[TOC]
>[github](https://github.com/catfan/Medoo)
>[home](https://medoo.in/)
## 概述
| Name | Driver |
| --- | --- |
| MySQL, MariaDB | php\_pdo\_mysql |
| MSSQL (Windows) | php\_pdo\_sqlsrv |
| MSSQL (Linux/UNIX) | php\_pdo\_dblib / php\_pdo\_sqlsrv |
| Oracle | php\_pdo\_oci |
| Oracle version 8 | php\_pdo\_oci8 |
| SQLite | php\_pdo\_sqlite |
| PostgreSQL | php\_pdo\_pgsql |
| Sybase | php\_pdo\_dblib |
php5.4+
## 安装
`composer require catfan/medoo`
## 结合 Slim
[Slim 微型框架](../%E7%BD%91%E7%BB%9C/Slim%E5%BE%AE%E5%9E%8B%E6%A1%86%E6%9E%B6.md)
```
use Medoo\Medoo;
$app = new \Slim\App();
$container = $app->getContainer();
$container['database'] = function () {
return new Medoo([
'database_type' => 'mysql',
'database_name' => 'name',
'server' => 'localhost',
'username' => 'your_username',
'password' => 'your_password'
]);
};
$app->get('/', function($request, $response, $args) {
$data = $this->database->select('account', ['id', 'name']);
return $response->write(json_encode($data));
});
$app->run();
```
## 接口
### 连接
```
use Medoo\Medoo;
$database = new Medoo([
'database_type' => 'mysql',
'database_name' => 'coffee_system',
'server' => 'localhost',
'username' => 'root',
'password' => 'www.upsoft01.com'
]);
```
### where 语句
```
$database->select("account", "user_name", [ // WHERE email = 'foo@bar.com'
"email" => "foo@bar.com"
]);
$database->select("account", "user_name", [ // WHERE user_id = 200
"user_id" => 200
]);
$database->select("account", "user_name", [ // WHERE user_id > 200
"user_id[>]" => 200
]);
$database->select("account", "user_name", [ // WHERE user_id >= 200
"user_id[>=]" => 200
]);
$database->select("account", "user_name", [ // WHERE user_id != 200
"user_id[!]" => 200
]);
$database->select("account", "user_name", [ // WHERE age BETWEEN 200 AND 500
"age[<>]" => [200, 500]
]);
$database->select("account", "user_name", [ // WHERE age NOT BETWEEN 200 AND 500
"age[><]" => [200, 500]
]);
```
#### or
```
$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')
```
#### and
```
$data = $database->select("user_info_eq_info", ["name",],[
'AND'=>[
'machine_id[!]'=>[1,2],
'category_id'=>1,
]
]);
//等于
$data = $database->select("user_info_eq_info", ["name",],[
'machine_id[!]'=>[1,2],
'category_id'=>1,
]);
```
#### 子查询
```
$database->select("account", "user_name", [
"user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
]);
// WHERE user_id IN (2, 51, 321, 3431)
```
#### 混合
```
$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'
```
#### like
```
//SELECT `machine` FROM `user_info_eq_info` WHERE (`name` LIKE '%cpj%')
$database->select("user_info_eq_info",'machine' ,[
'name[~]'=>'cpj', // 数组 ["lon", "foo", "bar"]
]
);
//SELECT `machine` FROM `user_info_eq_info` WHERE (`name` LIKE '%cpj')
$database->select("user_info_eq_info",'machine' ,[
'name[~]'=>'%cpj'
]
)
//SELECT `machine` FROM `user_info_eq_info` WHERE (`name` NOT LIKE '%cpj')
$database->select("user_info_eq_info",'machine' ,[
'name[!~]'=>'cpj'
]
);
// WHERE ("content" LIKE '%lon%' AND "content" LIKE '%on%')
$database->select("person", "id", [
"content[~]" => ["AND" => ["lon", "on"]]
]);
// WHERE ("content" LIKE '%lon%' OR "content" LIKE '%on%')
$database->select("person", "id", [
"content[~]" => ["OR" => ["lon", "on"]]
]);
```
### join查询
```
/**
* [>]account LEFT JOIN `account`
* [<]account RIGHT JOIN `account`
* [><]account INNER JOIN `account`
* [<>]account FULL JOIN `account`
*/
$database->select("post", [
"[><]account" => ["author_id" => "user_id"],
], [
"post.id",
"post.content"
], [
"AND" => [
"post.restrict[<]account.age",
"account.user_name" => "foo",
"account.email" => "foo@bar.com",
]
]
);
print_r($database->last());////SELECT `post`.`id`,`post`.`content` FROM `post` INNER JOIN `account` ON `post`.`author_id` = `account`.`user_id` WHERE (`post`.`restrict` < `account`.`age` AND `account`.`user_name` = 'foo' AND `account`.`email` = 'foo@bar.com')
```
### 排序 order
```
$database->select("account", "user_id", [
// Single condition
"ORDER" => "user_id",
//or
// Multiple condition
"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"
]
]);
```
### limit
```
$database->select("account", "user_id", [
// Get the first 100 of rows
'LIMIT' => 100
// Started from the top 20 rows, and get the next 100
'LIMIT' => [20, 100],
// For Oracle and MSSQL database, you also need to use with GROUP by together
'GROUP' => 'location'
]);
```
### group
```
$database->select("account", "user_id", [
'GROUP' => 'type',
// GROUP by array of values
'GROUP' => [
'type',
'age',
'gender'
],
// Must have to use it with GROUP together
'HAVING' => [
'user_id[>]' => 500
]
]);
```
### 增
```
// 主键自动添加阿萨德
$database->insert("account", [
"user_name" => "foo",
"email" => "foo@bar.com",
"age" => 25
]);
```
#### Last Insert ID
```
$database->insert("account", [
"user_name" => "foo",
"email" => "foo@bar.com",
"age" => 25
]);
$account_id = $database->id();
```
#### 批量插入
```
$database->insert("account", [
[
"user_name" => "foo",
"email" => "foo@bar.com",
"age" => 25,
"city" => "New York",
"lang [JSON]" => ["en", "fr", "jp", "cn"]
],
[
"user_name" => "bar",
"email" => "bar@foo.com",
"age" => 14,
"city" => "Hong Kong",
"lang [JSON]" => ["en", "jp", "cn"]
]
]);
```
#### 序列化
```
$database->insert("account", [
"user_name" => "foo",
"email" => "foo@bar.com",
"age" => 25,
"lang" => ["en", "fr", "jp", "cn"] // => 'a:4:{i:0;s:2:"en";i:1;s:2:"fr";i:2;s:2:"jp";i:3;s:2:"cn";}'
]);
$database->insert("account", [
"user_name" => "foo",
"email" => "foo@bar.com",
"age" => 25,
"lang [JSON]" => ["en", "fr", "jp", "cn"] // => '["en","fr","jp","cn"]'
]);
```
### 查
#### get 单条
```
$data = $database->get("user_info_eq_info", 'mac_addr');
print_r($data); //BC-EE-7B-5B-6B-0FSELECT
$data = $database->get("user_info_eq_info", ['mac_addr']);
print_r($data); //( [mac_addr] => BC-EE-7B-5B-6B-0F )
```
#### 查询所有字段
```
$data = $database->get("user_info_eq_info", '*',[
'category_id'=>1,
]);
```
#### 自定义返回数组
```
$data = $database->select("post", [
"user_id" => [
"nickname",
"location",
"email"
]
]);
// Output data
[
10: {
nickname: "foo",
location: "New York",
email: "foo@example.com"
},
12: {
nickname: "bar",
location: "New York",
email: "bar@medoo.in"
}
]
```
#### join 查询并自定义返回结构
```
$data = $database->select("post", [
"[>]account" => ["user_id"]
], [
"post.post_id",
"post.content",
"userData" => [
"account.user_id",
"account.email",
"meta" => [
"account.location",
"account.gender"
]
]
], [
"LIMIT" => [0, 2]
]);
echo json_encode($data);
// Output data
[
{
post_id: "1",
content: "Hello world!",
userData: {
user_id: "1",
email: "foo@example.com",
meta: {
location: "New York",
gender: "male"
}
}
},
{
post_id: "2",
content: "Hey everyone",
userData: {
user_id: "2",
email: "bar@example.com",
meta: {
location: "London",
gender: "female"
}
}
}
]
```
#### 指定字段类型
```
$data = $database->select("post", [
"[>]account" => ["user_id"]
], [
"post.post_id",
"profile" => [
"account.age [Int]",
"account.is_locked [Bool]",
"account.userData [JSON]"
]
], [
"LIMIT" => [0, 2]
]);
echo json_encode($data);
// Output data
[
{
post_id: "1",
profile: {
age: 20,
is_locked: true,
userData: ["foo", "bar", "tim"]
}
},
{
post_id: "2",
profile: {
age: 25,
is_locked: false,
userData: ["mydata1", "mydata2"]
}
}
]
```
#### 别名
```
$data = $database->select("account", [
"user_id",
"nickname(my_nickname)" // nickname as my_nickname
], [
"LIMIT" => 20
]);
$data = $database->select("post (content)", [
"[>]account (user)" => "user_id", // account as user
], [
"content.user_id (author_id)",
"user.user_id"
], [
"LIMIT" => 20
]);
```
### 更
```
$data =$database->update("account", [
"type" => "user",
// age + 1
"age[+]" => 1,
"level[-]" => 5,
// 乘 2
"score[*]" => 2,
"lang" => ["en", "fr", "jp", "cn"],
"lang [JSON]" => ["en", "fr", "jp", "cn"],
"is_locked" => true,
], [
"user_id[<]" => 1000
]);
echo $data->rowCount();
```
### 删
```
$database->delete("account", [
"AND" => [
"type" => "business",
"age[<]" => 18
]
]);
```
### has 表是否存在
```
if ($database->has("post", ["user_id" => 2312]))
{
return false;
}
```
### 聚合函数
count,max,min,avg
```
$count = $database->count("account", [
"gender" => "female"
]);
```
### 创建表
```
$database->create("account", [
"id" => [
"INT",
"NOT NULL",
"AUTO_INCREMENT"
],
"email" => [
"VARCHAR(70)",
"NOT NULL",
"UNIQUE"
],
"PRIMARY KEY ()"
], [
"ENGINE" => "MyISAM",
"AUTO_INCREMENT" => 200
]);
```
### 删除表
```
$database->drop("account");
```
### 执行sql函数
```
$data = $database->insert('account', [
'user_name' => 'apple',
'user_id' => Medoo::raw('UUID()')
]);
$data = $database->select('account', [
'user_id',
'email'
],
Medoo::raw('WHERE
LENGTH() > 5
ORDER BY RAND()
LIMIT 10
')
);
```
### 事务
```
$database->pdo->beginTransaction();
$database->insert("account", [
"user_name" => "foo",
"email" => "foo@bar.com",
"age" => 25
]);
/* Commit the changes */
$database->pdo->commit();
/* Recognize mistake and roll back changes */
$database->pdo->rollBack();
```
### Debug
#### Debug
```
$database->debug()->select("bccount", [
"user_name",
"email"
], [
"user_id[<]" => 20
]);
// Will output:
// SELECT "user_name","email" FROM "bccount" WHERE "user_id" < 20
```
#### 处理error
```
$database->select("bccount", [
"user_name",
"email"
], [
"user_id[<]" => 20
]);
var_dump( $database->error() );
```
#### log
```
$database->insert("account", [
"user_name" => "foo",
"email" => "foo@bar.com"
]);
var_dump($database->log()); //array(1) { [0] => string(74) "INSERT INTO `account` (`user_name`, `email`) VALUES ('foo', 'foo@bar.com')" }
```
### 查询最后执行的sql
```
$database->last()
```
';