# 数据库使用

为了简化调用，KaliPHP 框架数据库主要考虑使用 MySQL(ClickHouse)，并没有考虑其他数据库的情况，因此并没有很复杂的数据库驱动之类的数据类结构，并且只支持`mysqli`函数，使用时记得安装`mysqli`扩展。

db类的成员方法都是静态方法，因此使用时不需要初始化。

### 连接配置

数据库库信息都配置在`app/config/database.php`中，也可根据环境配置在`database_dev.php`/`database_pre.php`/`database_pub.php`里面

基本参数如下：

```
// app/config/database_dev.php
return array(
    'config' => array(
        // 库名
        'name'       => 'kali',
        // 用户名
        'user'       => 'root',
        // 密码
        'pass'       => 'pwd',
        // 端口号
        'port'       => 3306,
        // 编码格式
        'charset'    => 'utf8',
        // 是否长链接（默认关闭, mysqli的长链问题很多）
        'keep-alive' => true,
        // 表前缀
        'prefix'     => 'kali',
        // 是否对SQL语句进行安全检查并处理，在插入十万条以上数据的时候会出现瓶颈
        'safe_test'  => true,
        // 慢查询阀值，秒
        'slow_query' => 0.5,
        // 库IP
        'host' => array(
            // 主库IP
            'master' => '192.168.0.2:3306',
            // 从库IP, 支持多个从库，随机链接
            'slave'  => array('192.168.0.3:3306', '192.168.0.4:3306', '192.168.0.5:3306')
        ),
        // 字段加密key
        'crypt_key' => 'key',
        // 需要加解密字段, 字段类型记得采用BLOB
        'crypt_fields' => array(
            'kali_member' => array( 'name', 'email', 'address' ),
        ),
        // JSON类型字段
        'json_fields' => array(
            'kali_product' => array( 'params' ),
        ),
    )
);
```

#### 如果你有多个项目需要用到同一份配置，只需要把上面的配置放在公共处，在你需要使用处引用

<pre><code><strong>// common/config/database.php
</strong><strong>$configs = require APPPATH . '/../../common/config/'.basename(__FILE__);
</strong>return array_merge($configs, [
    // 需要增加或覆盖配置，写到下面即可
]);
</code></pre>

### 主从操作

框架支持配置不同组别的数据库(MySQL、ClickHouse) ，但是都需要同时配置主从库，从库支持多个，操作数据库时会自动判断，`增删改`操作主库，`查询`操作从库

如果需要强制使用主库： \
&#x20;   1、程序上下文有对数据修改并且需要立刻获得修改结果的时候，常见于订单生成接口。\
&#x20;   2、操作事务的时候，这个时候我们可以在操作的时候指定使用主库：

```
$query->execute(true);
```

还可以设置从库状态为 false

```
db::enable_slave(false);
```

### 切换数据库

框架支持随时切换数据库，比如从 MySQL 切换到 ClickHouse 进行快速查询，记得 ClickHouse 配置也需要同时设置主从配置，但是信息可以一样

```
// app/config/clickhouse.php
// 切换成 ClickHouse
db::init_db('clickhouse', 'clickhouse');
db::switch_db('clickhouse');
```

### 基础查询

```
db::query($sql)->execute($is_master = false);
```

$is\_master 是读写分离参数，默认false为智能操作，查询操作从库，增删改操作主库，true为强制操作主库

### 返回值

返回值是根据查询类型而变化的

**SELECT:**\
&#x20;   1、无数据返回 array()\
&#x20;   2、默认返回多维数组\
&#x20;   3、使用as\_sql()后返回将要执行的SQL\
&#x20;   4、使用as\_row()后返回一维数组\
&#x20;   5、使用as\_field()后返回第一个元素\
&#x20;   6、使用as\_result()后返回结果集&#x20;

**INSERT:**\
&#x20;   array( insert id, affected rows )&#x20;

**UPDATE & DELETE:**\
&#x20;   affected rows

### 查询数据

查询数据支持两种字段选择方式：字符串（字段以逗号隔开）、数组，如果需要使用db::expr()方法，请使用数组方式

```
// SELECT `id`,`name` FROM `users`

// 字符串
$result = db::select('id, name')
    ->from('users')
    ->execute();

// 数组
$resutl = db::select(array('id', 'name'))
    ->from('users')
    ->execute();

// SELECT DISTINCT `name` FROM `users`
$users = db::select('name')
    ->from('users')
    ->distinct(true)
    ->execute();

// SELECT `company_id`, COUNT(DISTINCT `order_id`) AS `count`
$users = db::select([
        'name', 
        db::expr('COUNT(DISTINCT `order_id`)')
    ])
    ->from('users')
    ->distinct(true)
    ->execute();

// Use as_row() to get a piece of data
$user = db::select('name')
    ->from('users')
    ->as_row()
    ->execute();

// SELECT COUNT(*) AS `count` FROM `users`
$result = db::select('COUNT(*) AS `count`')
    ->from('users')
    ->as_row()
    ->execute();
```

### 增加数据

单条数据增加可采用`set`方法, 成功返回（`自增ID` 和 `受影响行数`）

```
// INSERT INTO `users`(`name`,`email`,`password`)
// VALUES ("John Random", "john@example.com", "s0_s3cr3t")
list($insert_id, $rows_affected) = db::insert('users')->set(array(
    'name'     => 'John Random',
    'email'    => 'john@example.com',
    'password' => 's0_s3cr3t',
))->execute();
```

批量增加数据可采用`columns`和`values`组合方法

```
// INSERT INTO `users`(`name`,`email`,`password`)
// VALUES ("John Random", "john@example.com", "s0_s3cr3t"),
// ("Sam Chen", "sam@example.com", "s0_s4cr4t")
list($insert_id, $rows_affected) = db::insert('users')->columns(array(
    'name', 'email', 'password'
))->values(array(
    array('John Random', 'john@example.com', 's0_s3cr3t'),
    array('Sam Chen', 'sam@example.com', 's0_s3cr4t')
))->execute();
```

### 修改数据

单字段修改可采用`value`方法, 成功返回（`受影响行数`）

```
// UPDATE `users` SET `name` = "John Random" WHERE `id` = "2";
$rows_affected = db::update('users')
    ->value("name", "John Random")
    ->where('id', '=', '2')
    ->execute();
```

多字段修改可采用`set`方法

```
// UPDATE `users`
// SET `group` = "Peter Griffon", `email` = "peter@thehindenpeter.com"
// WHERE `id` = "16";
$rows_affected = db::update('users')
    ->set(array(
        'name'  => "Peter Griffon",
        'email' => "peter@thehindenpeter.com"
    ))
    ->where('id', '=', '16')
    ->execute();
```

### 删除数据

删除操作成功返回（`受影响行数`）

```
// DELETE FROM `users`
$rows_affected = db::delete('users')->execute(); // (int) 20

// DELETE FROM `users` WHERE `email` LIKE "%@example.com"
$rows_affected = db::delete('users')
    ->where('email', 'like', '%@example.com')
    ->execute(); // (int) 7
```

### 多联表

框架支持多连表模型: `join`（全联接），`left join`（左联接），`right join`（右联接）方法

```
// SELECT * FROM `users` LEFT JOIN `roles` ON `roles`.`id` = `users`.`role_id`
db::select()
    ->from('users')
    ->join('roles','LEFT')
    ->on('roles.id', '=', 'users.role_id');

// SELECT * FROM `users` RIGHT OUTER JOIN `roles` ON `roles`.`id` = `users`.`role_id`
db::select()
    ->from('users')
    ->join('roles','right outer')
    ->on('roles.id', '=', 'users.role_id');
```

多次使用`join`方法可以继续联接，理论上可以建立任意数量的关联表。

### 选择器

选择器用于筛选表内数据，参数可以为两个或者三个，两个相当于`=`号省略不写。

```
// SELECT * FROM `users` WHERE `id` = 1
$result = db::select()->from('users')->where('id', 1)->execute();
$result = db::select()->from('users')->where('id', '=', 1)->execute();


// SELECT * FROM `users` WHERE `id` != 1
$result = db::select()->from('users')->where('id', '!=', 1)->execute();

// SELECT * FROM `users` WHERE `delete_user` IS NOT NULL
$result = db::select()->from('users')->where('delete_user', '!=', NULL)->execute();

// SELECT * FROM `users` WHERE `name` LIKE "john%"
$result = db::select()->from('users')->where('name', 'like', 'john%')->execute();
```

`IN`、`BETWEEN`等多值方式需要传递一维数组参数

```
// SELECT * FROM `users` WHERE `id` IN (1,2,3)
$id_array = array(1,2,3);
$result = db::select()
    ->from('users')
    ->where('id', 'in', $id_array)
    ->execute();

// SELECT * FROM `users` WHERE `id` BETWEEN 1 AND 2
$result = db::select()
    ->from('users')
    ->where('id', 'between', array(1,2))
    ->execute();
```

多条件查询可采用数组或者迭代调用

```
// SELECT * FROM `users` WHERE `name` LIKE "john%" AND `sex`='1'
// 数组方式
$where = array(
    array('name', 'like', 'john%'),
    array('sex', '=', 1)
);
$result = db::select()
    ->from('users')
    ->where($where)
    ->execute();

// 迭代方式
$where = array();
$query = db::select()
    ->from('users');
if ( !empty($keyword))
{
    $query->where('name', 'like', "%{$keyword}%");
    $query->where('enname', 'like', "%{$keyword}%");
}
$query->execute();
```

### 复杂选择

除了正常的匹配选择以外，框架还提供了其他复杂选择器。

```
// SELECT * FROM `users` WHERE (`name` = 'John' AND `email` = 'john@example.com')
// OR (`name` = 'mike' OR `name` = 'dirk')
$result = db::select()
    ->from('users')
    ->where_open()
        ->where('name', 'John')
        ->and_where('email', 'john@example.com')
    ->where_close()
    ->or_where_open()
        ->where('name', 'mike')
        ->or_where('name', 'dirk')
    ->or_where_close()
    ->execute();
```

### 其他条件

`order by`语句支持迭代调用方式

```
// SELECT * FROM `users` ORDER BY `name` ASC
db::select()
    ->from('users')
    ->order_by('name', 'asc');

// 迭代调用
// SELECT * FROM `users` PRDER BY `name` ASC, `surname` DESC
db::select()
    ->from('users')
    ->order_by('name', 'asc')
    ->order_by('surname', 'desc');
```

`group by`语句单字段情况下传递字符串即可，多字段需要传递数组

```
// SELECT * FROM `users` GROUP BY `age`
db::select()
    ->from('users')
    ->group_by('age');

// SELECT * FROM `users` GROUP BY `age`,`sex`
$group = array('age', 'sex');
db::select()
    ->from('users')
    ->group_by($group);
```

`Limit && Offset`

```
// SELECT * FROM `users` LIMIT 1
db::select()
    ->from('users')
    ->limit(1);

// SELECT * FROM `users` LIMIT 10 OFFSET 5
db::select()
    ->from('users')
    ->limit(10)
    ->offset(5);
```

### 防止构建

正常情况下，传递参数进去后会被构建器重新构建成表达式，`expr`方法会返回一个db\_expression类对象，可以防止表达式被重新构建，保留它原来的样子。

```
$expr = db::expr('columnname + 1');
```

```
// SELECT `company_id`, COUNT(DISTINCT `order_id`) AS `count` FROM `order`
db::select( array('company_id', db::expr('COUNT(DISTINCT `order_id`) AS `count`')) )
   ->from('order')
   ->group_by('company_id')
   ->execute();
```

### JSON字段

JSON字段需要在Mysql中设置好字段类型为json，然后在 `config/database.php` 文件中配置好参数：

```
'json_fields' => [
    'lrs_test' => ['json_field'],
]
```

配置好后，对当前字段操作只要传入`array`，便会自动转为json字符串，不需要`json_encode`

#### 添加字段

```
db::insert('lrs_test')
    ->set(['json_field' => ['a' => 1, 'b' => 2, 'c' => 3] ])
    ->execute();
```

#### 更新字段

{% hint style="danger" %}
更新一定要注意的是，如果原先是a、b、c三个参数，只传入a、b参数，c并不会被删除，仅仅是更新了a、b的值而已，如果要整个字段修改，需要使用`json_encode($data)`
{% endhint %}

```
db::update('lrs_test')
    ->set(['json_field' => ['a' => 10, 'b' => 20] ])
    ->where('id', 3)
    ->execute();

// 上面的操作，并不会删除c参数，仅仅是更新了a、b的值而已，需要配合php函数处理
$json_str = json_encode([
    'a' => 1,
    'b' => 2
]);
db::update('lrs_test')
    ->set(['json_field' => $json_str ])
    ->where('id', 3)
    ->execute();
```

#### 查询字段

```
$data = db::select('*')
    ->from('lrs_test')
    ->where('json_field->b', 2)
    ->execute();
```

### SQL模版

框架中提供了上述`选择器`，`条件语句`，`联表`等，基本覆盖了所有sql语法，但可能还有部分生僻的用法无法被实现， 于是这里提供了一种SQL模版的使用方式，支持用户自定义SQL语句，但`并不推荐用户使用`，如果一定要使用的话，请务必自己做好`防SQL注入`

你可以使用框架提供的标准占位符，他是一个字符串，以冒号（:varname）为前缀。

```
$name = 'John'; // 设置要绑定的变量
$query = "SELECT * FROM users WHERE username = :name"; // 要执行的表达式

// 绑定变量执行查询, 相当于执行 SELECT * FROM users WHERE username = 'John'
$result = db::query($query)
    ->bind('name', $name)
    ->execute();

// 也可以用param方法，param是bind方法的别名
$result = db::query($query)
    ->param('name', $name)
    ->execute();
```

多个参数绑定采用数组穿参

```
$params = array('name' => 'John', 'state' => 'new'); // 设置要绑定的变量
$query = "SELECT * FROM users WHERE username = :name AND state = :state"; // 要执行的表达式

// 绑定变量执行查询，相当于执行 SELECT * FROM users WHERE username = 'John' AND state = 'new'
$result = db::query($query)
    ->parameters($params)
    ->execute();
```

### 游标数据

如果DB中取出的数据非常大，而PHP中却无法承受这么大量的内存可以用来处理，这时候就需要用到游标了

游标可以将复合条件的数据逐一取出，在程序中进行分批处理，从而降低大数据所带来的内存瓶颈

```
// 选择器，条件类模式完全一样，在获取数据时使用as_result方法
$rsid = db::query( $sql )->as_result()->execute();
while( $row = db::fetch($rsid) )
{
    //do something...
}
```

### 事务处理

框架提供了一套简单的事务处理机制，默认是关闭的，可以通过`db::start()`方法开启

`注意：`请确保连接的数据表是`innodb`的存储引擎，否者事务并不会生效。

在`db::start()`之后可以通过`db::commit()`来进行完整事务的提交保存，但并不会影响`start`之前的操作

同理，可以通过`db::rollback()`进行整个事务的回滚，回滚所有当前未提交的事务

当程序调用`db::end()`方法后事务会全部终止，未提交的事务也会自动回滚，另外，程序析构时，也会自动回滚未提交的事务

```
// 在事务开始前的操作都会默认提交，num:0
db::update('#PB#_test')->set(array('num'=>0))->where('id', 1)->execute();
// 开始事务
db::start();
// set num = num+2
db::update('#PB#_test')->set(array('num'=>db::expr('`num`+1')))->where('id', 1)->execute();
db::update('#PB#_test')->set(array('num'=>db::expr('`num`+1')))->where('id', 1)->execute();
// 回滚事务
db::rollback();
// 当前num还是0，事务操作请务必调用主库查询
$num = db::select('num')->from('#PB#_test')->where('id', 1)->as_field()->execute(true);
// set num = num+2
db::update('#PB#_test')->set(array('num'=>db::expr('`num`+1')))->where('id', 1)->execute();
db::update('#PB#_test')->set(array('num'=>db::expr('`num`+1')))->where('id', 1)->execute();
// 提交事务
db::commit();
// num = 2，事务操作请务必调用主库查询
$num = db::select('num')->from('#PB#_test')->where('id', 1)->as_field()->execute(true);
// 关闭事务
db::end();
```

另外，事务的开启并不会影响`select`操作，只对增加，删除，修改操作有影响

还有就是，框架默认是主从操作以缓解数据库查询压力，但是数据库主从同步需要时间而且远比PHP执行效率要低，开始事务、回滚事务、提交事务、关闭事务会自动调用主库，增加，删除，修改操作也会自动调用主库，所以用户只需要在查询操作时强制使用主库( `execute(true)` )即可

### 数据缓存

尚未实现。

### 语句调试

SQL调试方法已经集成在框架事件中，只需要在需要调试语句的方法前调用`event::on(onSql)`就可以在`页面控制台`中输出sql语句了

```
// one方法绑定一次事件，输出一次后自动释放
event::one(onSql);
$data = db::query()->execute();

// on方法绑定事件，直到off释放前都会有效
event::on(onSql);
$data = db::query()->execute();
$data = db::query()->execute();
$data = db::query()->execute();
event::off(onSql);
```

该SQL事件功能还可自行绑定方法，具体用法会在后面`事件`介绍中详细展开


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://doc.kaliphp.com/database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.