数据库使用

为了简化调用，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' ),
        ),
    )
);

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

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

主从操作

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

如果需要强制使用主库： 1、程序上下文有对数据修改并且需要立刻获得修改结果的时候，常见于订单生成接口。 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: 1、无数据返回 array() 2、默认返回多维数组 3、使用as_sql()后返回将要执行的SQL 4、使用as_row()后返回一维数组 5、使用as_field()后返回第一个元素 6、使用as_result()后返回结果集

INSERT: array( insert id, affected rows )

UPDATE & DELETE: 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();

更新字段

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

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事件功能还可自行绑定方法，具体用法会在后面事件介绍中详细展开

Previous配置 Next请求类

Last updated 1 year ago