PHP轻量级数据库(MySQL)框架
- PHP版本5.4及以上
- 支持PDO, 已安装扩展
pdo_mysql
- 支持PHP版本5.4+,依赖PDO扩展
- 常见数据库操作功能全部实现
- WHERE组合条件上千种变化
- 支持事务回调操作
- 支持分片回调处理数据
- 支持缓存查询数据
- 支持多种联表查询
- 支持多种数据绑定
- 数据安全过滤保障
- 引入、包装更简单
// Using Juggler namespace
use Upfor\Juggler\Juggler;
// Config and connect server
$config = array(
'host' => '127.0.0.1',
'port' => 3306,
'dbname' => 'db_name',
'username' => 'your_username',
'password' => 'your_password',
'charset' => 'utf8',
'prefix' => 'db_pre_',
);
$db = new Juggler($config);
// Insert data
$db->table('account')->insert(array(
"username" => "name",
"email" => "[email protected]",
));
用于执行查询类SQL。
- 成功,返回查询结果;失败,返回
FALSE
。 - 支持unbuffered查询
/**
* Execute an SQL statement and return the result
*
* @param string $sql
* @param array $bindData
* @param boolean $fetch
* @param boolean $unbuffered
* @return mixed
*/
public function query($sql, array $bindData = array(), $fetch = false, $unbuffered = false) {...}
用于执行非查询类SQL。
- 成功,返回影响行数;失败,返回
FALSE
。
/**
* Execute an SQL statement and return the number of affected rows
*
* @param string $sql
* @param array $bindData
* @param boolean $fetch
* @return mixed
*/
public function exec($sql, array $bindData = array(), $fetch = false) {...}
可与`table()`、`where()`、`limit()`、`order()`、`group`等方法联合使用。
查询并获取多行数据
// SELECT * FROM `account`
$db->table('account')->getList()
查询并获取单行数据
// SELECT * FROM `account` LIMIT 1
$db->table('account')->getRow();
是否存在符合条件的数据
/**
* Determine whether the target data existed
*
* @param array $where
* @return boolean
*/
public function has($where = array()) {...}
// SELECT EXISTS(SELECT 1 FROM `account` WHERE `id` = 12) AS `tmp`
$db->table('account')->where(array(
'id' => 12,
))->has();
统计满足条件的数据行数
/**
* Counts the number of rows
*
* @param string $field
* @return integer
*/
public function count($field = '*') {...}
// SELECT COUNT(*) AS `count_total` FROM `account` WHERE `id` >= 12 LIMIT 1
$db->table('account')->where(array(
'id|>=' => 12,
))->count();
计算符合条件数据的字段总和
/**
* Get the total value for the column
*
* @param string $field
* @return integer
*/
public function sum($field) {...}
// SELECT SUM(`age`) AS `sum_total` FROM `account` WHERE `id` >= 12 LIMIT 1
$db->table('account')->where(array(
'id|>=' => 12,
))->sum('age');
查询符合条件数据的字段最大值
/**
* Get the maximum value for the column
*
* @param string $field
* @return integer
*/
public function max($field) {...}
// SELECT MAX(`age`) AS `max_tmp` FROM `account` WHERE `id` >= 12 LIMIT 1
$db->table('account')->where(array(
'id|>=' => 12,
))->sum('age');
查询符合条件数据的字段最小值
/**
* Get the minimum value for the column
*
* @param string $field
* @return integer
*/
public function min($field) {...}
// SELECT MIN(`age`) AS `min_tmp` FROM `account` WHERE `id` >= 12 LIMIT 1
$db->table('account')->where(array(
'id|>=' => 12,
))->min('age');
查询符合条件数据的字段平均值
/**
* Get the average value for the column
*
* @param string $field
* @return integer
*/
public function avg($field) {...}
// SELECT AVG(`age`) AS avg_tmp FROM `account` WHERE `id` >= 12 LIMIT 1
$db->table('account')->where(array(
'id|>=' => 12,
))->avg('age');
查询符合条件数据的第一行指定字段值
/**
* Gets the value of a field
*
* @param string $field
* @return mixed
*/
public function value($field) {...}
// SELECT `age` FROM `account` WHERE `id` >= 12 LIMIT 1
$db->table('account')->where(array(
'id|>=' => 12,
))->value('age');
查询符合条件数据的指定字段(列)
/**
* Gets the value of the specified column
*
* @param string $field
* @return array
*/
public function column($field) {...}
// SELECT `age` FROM `account` WHERE `id` >= 12
$db->table('account')->where(array(
'id|>=' => 12,
))->column('age');
可与`table()`等方法联合使用。
+ 支持单条或批量插入。
+ 插入数据必须是关联数组,即元素的键名就是字段名。
+ 一维数组会被视为单条插入;二维数组会被视为批量插入;超过二维,则会对值进行自动转换成JSON字符串。
+ 支持`ON DUPLICATE KEY UPDATE`进行数据更新。
/**
* Insert new data
*
* Support for bulk inserts, or updates while a duplicate value in a UNIQUE index or PRIMARY KEY
*
* @param array $data The data to insert
* @param mixed $replace Use sub statement `ON DUPLICATE KEY UPDATE` to replace existed data
* @param boolean $fetch Is fetch the SQL statement?
* @return integer
*/
public function insert(array $data, $replace = null, $fetch = false) {...}
- 单条插入
// INSERT INTO `account` (`age`, `name`) VALUES (18, 'john')
$db->table('account')->insert(array(
'name' => 'john',
'age' => 18,
));
- 批量插入
// INSERT INTO `account` (`age`, `name`) VALUES (18, 'john'), (20, 'mary')
$db->table('account')->insert(array(
array(
'name' => 'john',
'age' => 18,
),
array(
'name' => 'mary',
'age' => 20,
),
));
- $replace: true, 全部字段更新
// INSERT INTO `account` (`age`, `name`) VALUES (18, 'john'), (20, 'mary') ON DUPLICATE KEY UPDATE `age`=VALUES(`age`), `name`=VALUES(`name`)
$db->table('account')->insert(array(
array(
'name' => 'john',
'age' => 18,
),
array(
'name' => 'mary',
'age' => 20,
),
), true);
- $replace: array(var1, var2, ...), 更新指定字段
// INSERT INTO `account` (`age`, `name`) VALUES (18, 'john'), (20, 'mary') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`)
$db->table('account')->insert(array(
array(
'name' => 'john',
'age' => 18,
),
array(
'name' => 'mary',
'age' => 20,
),
), array('name'));
- $replace: string, 原生SQL子句
// INSERT INTO `account` (`age`, `name`) VALUES (18, 'john'), (20, 'mary') ON DUPLICATE KEY UPDATE `age`=VALUES(`age`) + 1
$db->table('account')->insert(array(
array(
'name' => 'john',
'age' => 18,
),
array(
'name' => 'mary',
'age' => 20,
),
), '`age`=VALUES(`age`) + 1');
可与`table()`、`where()`、`limit()`、`order()`等方法联合使用。
/**
* Update data
*
* @param array $data
* @param array $where
* @param mixed $table
* @param boolean $fetch
* @return integer
*/
public function update(array $data, array $where = array(), $table = null, $fetch = false) {...}
// UPDATE `account` SET `name` = 'hello', `age` = 12 WHERE `id` = 108
$db->table('account')->where(array(
'id' => 108,
))->update(array(
'name' => 'hello',
'age' => 12,
));
// UPDATE `account` SET `name` = 'hello', `age` = 12 WHERE `id` = 108
$db->update(array(
'name' => 'hello',
'age' => 12,
), array(
'id' => 108,
), 'account');
可与table()
、where()
、limit()
、order()
等方法联合使用。
/**
* Conditionally delete data
*
* @param array $where
* @param mixed $table
* @param boolean $fetch
* @return integer
*/
public function delete(array $where = array(), $table = null, $fetch = false) {...}
// DELETE FROM `account` WHERE `gid` IN (1, 2, 3)
$db->table('account')->where(array(
'gid' => array(1, 2, 3),
))->delete();
// DELETE FROM `account` WHERE `gid` IN (1, 2, 3)
$db->delete(array(
'gid' => array(1, 2, 3),
), 'account');
Juggler 最核心、最强大的功能。支持多层级、多类型、多组合的WHERE查询条件表达式。
/**
* Sets the `WHERE` statement
*
* @see Function `buildWhere`
* @access protected
* @param mixed $condition Supports a variety of formats
* @param mixed $value The value of condition
* @return Juggler
*/
public function where($condition, $value = null) {
if (is_string($condition)) {
if (!is_null($value)) {
$this->options['where'][$condition] = $value;
} else {
$this->options['where']['SQL'] = $condition;
}
} elseif (is_array($condition)) {
if (isset($this->options['where']) && is_array($this->options['where'])) {
$this->options['where'] = array_merge($this->options['where'], $condition);
} else {
$this->options['where'] = $condition;
}
}
return $this;
}
$condition
: 支持单一字段, 复杂SQL, 多维数组, 复杂的条件表达式等
$value
: 当$condition
作为数组类型的参数时, 无效
// SELECT * FROM `account` WHERE `id` = 12
$db->table('account')->where('id', 12)->getList();
// SELECT * FROM `account` WHERE `id` != 12
$db->table('account')->where('id|!', 12)->getList();
// SELECT * FROM `account` WHERE MOD(`id`, 15) > 12
$db->table('account')->where('MOD(`id`, 15)|>', 12)->getList();
// SELECT * FROM `account` WHERE `id` > 12 AND `status` = 1
$db->table('account')->where('`id` > 12 AND `status` = 1')->getList();
// SELECT * FROM `account` WHERE (`id` BETWEEN 100 AND 200) AND `avatar` IS NOT NULL AND `gid` IN (1, 3, 5) AND `username` LIKE 'john%' AND `age` = 16
$db->table('account')->where(array(
'id|<>' => array(100, 200),
'avatar|!' => null,
'gid' => array(1, 3, 5),
'username|~' => 'john%',
'SQL' => '`age` = 16',
))->getList();
支持用中竖线|
进行分隔操作符, 以实现复杂的条件语句.
支持的条件操作符: !, >, >=, <, <=, <>, ><, , !
- 无操作符 (
=
,IN
,IS NULL
)
// SELECT * FROM `account` WHERE `id` = 12
$db->table('account')->where('id', 12)->getList();
// SELECT * FROM `account` WHERE `status` = 0
$db->table('account')->where('status', false)->getList();
// SELECT * FROM `account` WHERE `id` IN (1, 3, 5)
$db->table('account')->where('id', array(1, 3, 5))->getList();
// SELECT * FROM `account` WHERE `avatar` IS NULL
$db->table('account')->where(array(
'avatar' => null,
))->getList();
!
(!=
,NOT IN
,IS NOT NULL
)
// SELECT * FROM `account` WHERE `id` != 12
$db->table('account')->where('id|!', 12)->getList();
// SELECT * FROM `account` WHERE `id` NOT IN (1, 3, 5)
$db->table('account')->where('id|!', array(1, 3, 5))->getList();
// SELECT * FROM `account` WHERE `avatar` IS NOT NULL
$db->table('account')->where(array(
'avatar|!' => null,
))->getList();
<>
、><
(BETWEEN
,NOT BETWEEN
)
// SELECT * FROM `account` WHERE `id` BETWEEN 100 AND 300
$db->table('account')->where(array(
'id|<>' => array(100, 300),
))->getList();
// SELECT * FROM `account` WHERE `id` NOT BETWEEN 100 AND 300
$db->table('account')->where(array(
'id|><' => array(100, 300),
))->getList();
~
、!~
(LIKE
,NOT LIKE
)
// SELECT * FROM `account` WHERE `url` LIKE '%qq.com%'
$db->table('account')->where(array(
'url|~' => 'qq.com',
))->getList();
// SELECT * FROM `account` WHERE `url` LIKE '%qq.com'
$db->table('account')->where(array(
'url|~' => '%qq.com',
))->getList();
// SELECT * FROM `account` WHERE `url` LIKE 'qq.com%'
$db->table('account')->where(array(
'url|~' => 'qq.com%',
))->getList();
// SELECT * FROM `account` WHERE `url` NOT LIKE '%qq.com%'
$db->table('account')->where(array(
'url|!~' => 'qq.com',
))->getList();
// SELECT * FROM `account` WHERE `url` NOT LIKE '%qq.com%' OR `url` NOT LIKE '%taobao.com%'
$db->table('account')->where(array(
'url|!~' => array('qq.com', 'taobao.com'),
))->getList();
目前,支持AND
、OR
、SQL
3种组合语句。3种组合可以任意嵌套、组合使用。
AND
对于数组,默认组合方式为AND
,故可省略。 一级条件,均为AND
组合。
// SELECT * FROM `account` WHERE `username` = 'hello' AND `age` > 10
$db->table('account')->where(array(
'username' => 'hello',
'age|>' => 10,
))->getList();
// SELECT * FROM `account` WHERE `username` = 'hello' AND `age` > 10
$db->table('account')->where(array(
array(
'username' => 'hello',
'age|>' => 10,
),
))->getList();
// SELECT * FROM `account` WHERE (`id` >= 12 OR `avatar` IS NOT NULL) AND `gid` IN (1, 2, 3) AND `sql` = 123 AND (`username` = 'hello' AND `age` > 10)
$db->table('account')->where(array(
'OR' => array(
'id|>=' => 12,
'avatar|!' => null,
),
'gid' => array(1, 2, 3),
'SQL' => '`sql` = 123',
array(
'username' => 'hello',
'age|>' => 10,
),
))->getList();
OR
使用OR
组合,可实现WHERE
条件语句中的OR
。
// SELECT * FROM `account` WHERE (`id` >= 12 OR `avatar` IS NOT NULL) AND `gid` IN (1, 2, 3) AND (`username` = 'hello' AND `age` > 10)
$db->table('account')->where(array(
'OR' => array(
'id|>=' => 12,
'avatar|!' => null,
),
'gid' => array(1, 2, 3),
array(
'username' => 'hello',
'age|>' => 10,
),
))->getList();
SQL
使用SQL
组合,可实现原生SQL语句。SQL
组合可直接使用字符串类型的参数传入where()
方法。
// SELECT * FROM `account` WHERE `id` >= 100 AND `username` LIKE '%john%'
$db->table('account')->where("`id` >= 100 AND `username` LIKE '%john%'")->getList();
// SELECT * FROM `account` WHERE (`id` >= 12 OR `money` > 41) AND `gid` IN (1, 2, 3) AND `sql` = 123 AND (`username` = 'hello' AND `age` > 10)
$db->table('account')->where(array(
'OR' => array(
'id|>=' => 12,
'SQL' => '`money` > 41',
),
'gid' => array(1, 2, 3),
'SQL' => '`sql` = 123',
array(
'username' => 'hello',
'age|>' => 10,
),
))->getList();
设置数据库名称
设置`DISTINCT`子句
指定查询的字段
- 支持字符串、数组、数值等
- 支持字段别名
- 对于普通字段,建议使用数据
- 支持字段运算表达式
// SELECT RAND() AS `rand_str` FROM `account`
$db->table('account')->field(array(
'rand_str' => 'RAND()',
))->getList();
// SELECT RAND() AS `rand_str`, `username`, `age` * `score` AS `weight` FROM `account`
$db->table('account')->field(array(
'rand_str' => 'RAND()',
'username',
'weight' => '`age` * `score`',
))->getList();
// 直接传入原生SQL子句
// SELECT `username`, `age`, `age` * `score` AS `weight` FROM `account`
$db->table('account')->field('`username`, `age`, `age` * `score` AS `weight`')->getList();
指定查询的表名
- 支持别名、前缀
- 支持数组形式传参
- 数据库配置有前缀,则可用统一的前缀
/**
* Sets the table name for current statement
*
* @param mixed $table Table name
* @param string $alias Table alias
* @param boolean $prefix The prefix of table name
* @return Juggler
*/
public function table($table, $alias = null, $prefix = false) {...}
// SELECT * FROM `account`
$db->table('account')->getList();
// 别名
// SELECT * FROM `account` AS `a`
$db->table('account', 'a')->getList();
// 数组形式传参
// SELECT * FROM `account` AS `a`
$db->table(array('account', 'a'))->getList();
// 指定表前缀
// SELECT * FROM `pre_account` AS `a`
$db->table('account', 'a', 'pre_')->getList();
// 使用数据库配置统一表前缀
// SELECT * FROM `db_pre_account` AS `a`
$db->table('account', 'a', true)->getList();
指定排序方式
- 支持多字段排序
- 支持数组传参
/**
* Sets the `ORDER BY` statement
*
* @param string|array $field Field name, supports multiple definitions
* @param string $order ASC or DESC, not case-sensitive
* @return Juggler
*/
public function order($field, $order = null) {...}
// SELECT * FROM `account` ORDER BY `id`
$db->table('account')->order('id')->getList();
// SELECT * FROM `account` ORDER BY `id` DESC
$db->table('account')->order('id', 'DESC')->getList();
// SELECT * FROM `account` ORDER BY `id` DESC, `age` ASC
$db->table('account')->order(array(
'id' => 'DESC',
'age' => 'ASC',
))->getList();
指定`GROUP BY`聚合子句
- 支持多字段聚合
- 支持聚合排序
/**
* Sets the `GROUP BY` statement
*
* @param string|array $group
* @param string $order ASC or DESC, not case-sensitive
* @return $this
*/
public function group($group, $order = null) {...}
// SELECT * FROM `account` GROUP BY `id`, `age`
$db->table('account')->group(array(
'id',
'age',
))->getList();
// SELECT * FROM `account` GROUP BY `id` ASC
$db->table('account')->group('id', 'asc')->getList();
var_dump($data);
// SELECT * FROM `account` GROUP BY `id` DESC, `age` ASC
$db->table('account')->group(array(
'id' => 'DESC',
'age' => 'ASC',
))->getList();
// 原生SQL子句
// SELECT * FROM `account` GROUP BY year ASC, country ASC, product ASC WITH ROLLUP
$db->table('account')->group('year ASC, country ASC, product ASC WITH ROLLUP')->getList();
设置`LIMIT`子句
/**
* Sets the `LIMIT` statement
*
* @param integer|string $offset
* @param integer $length
* @return Juggler
*/
public function limit($offset, $length = null) {...}
// SELECT * FROM `account` LIMIT 5
$db->table('account')->limit(5)->getList();
// SELECT * FROM `account` LIMIT 11, 10
$db->table('account')->limit(11, 10)->getList();
设置分页参数,转换为`LIMIT`子句
/**
* Sets the page of the data
* A more vivid way of get range values
*
* @param integer|string $page Page number
* @param integer $listRows Page size
* @return Juggler
*/
public function page($page, $listRows = null) {...}
// SELECT * FROM `account` LIMIT 100, 10
$db->table('account')->page(11, 10)->getList();
联表查询`JOIN`子句
/**
* @param string|array $table Join table(s)
* @param string|array $condition Join conditions
* @param string $type Join type
* @param boolean $prefix The prefix of table name
* @return Juggler
*/
public function join($table, $condition, $type = 'LEFT', $prefix = false) {...}
// SELECT * FROM `account` AS `A` LEFT JOIN `money` AS `M` ON `M`.`account_id` = `A`.`id`
$db->table('account', 'A')->join(['money', 'M'], ['M.account_id' => 'A.id'])->getList();
设置是否对字段、表名、数据库名等进行包裹。系统默认会进行智能包裹。
设置是否返回解析完成后的SQL语句。一般用于调试。
获取SQL执行日志
获取最后一条SQL执行错误信息
获取最后一条SQL产生的插入ID
分块/分批处理数据
缓冲式查询SQL
获取表的字段信息
获取数据库所有数据表
获取数据库服务器&连接信息
开始事务
提交事务
回滚事务
是否处于事务状态
回调形式执行事务