此代码身经百战, 使用简便。操作类启用了强类型检查, 代码在下方, 注意需要配合另外4个类文件来执行:
1 Mysql连接操作类 http://www.wkwkk.com/article/a361f53d66945efef0bc33141dbdb165.html
2 用于配套缓存和错误日志的Redis连接操作类(阻塞) http://www.wkwkk.com/article/7f9260987a3d040b4068dcc386500e61.html
3 Sql语句状态缓存类 http://www.wkwkk.com/article/7f9260987a3d040b4068dcc386500e61.html 下方可见
4 基本连接配置和模型实例分配类案例 http://www.wkwkk.com/article/98a742396c78db8ed15c32649c70e9c4.html
代码如下
<?php
declare(strict_types=1);
namespace Lightpdo;
use Lightredis\Worker as RedisWorker;
/**
* fpm版连贯操作类
* @package orm
* @subpackage mysql
* @author qinchao <castor@happycoworker.com>
* @since 20160811
* @version 20230602
*/
if (WORKMODE != 'fpm') {
return;
}
class Worker
{
private $_mysql_handler;
private $_prefix;
// redis 连接配置
private $_redis_config = [];
// log开关 支持参数: 0=关闭, 1=redis
private $_log = 0;
private $_state; // 状态容器
private static $_instance = [];
/**
* 单例
* @param array $config
* @return mixed
*/
public static function instance(array $config)
{
$hash = substr(md5($config['host'] . $config['port'] . $config['dbname'] . $config['user']), 8, 16);
if (empty(static::$_instance[$hash]) || !array_key_exists($hash, static::$_instance)) {
static::$_instance[$hash] = new static($config);
}
static::$_instance[$hash]->_state = new State();
return static::$_instance[$hash];
}
public function __construct(array $config)
{
if (isset($config['transaction']) && $config['transaction']) {
$this->_mysql_handler = new ConnectionHandler($config);
} else {
$this->_mysql_handler = ConnectionHandler::instance($config);
}
$this->_state = new State();
$this->_prefix = isset($config['prefix']) ? $config['prefix'] : '';
$this->_log = isset($config['log']) ? $config['log'] : 0;
$this->_initRedisConfig($config);
}
private function _initRedisConfig(array $config)
{
$this->_redis_config = [
'ip' => isset($config['redis_ip']) ? $config['redis_ip'] : '127.0.0.1',
'port' => isset($config['redis_port']) ? (int)$config['redis_port'] : 6379,
'auth' => isset($config['redis_auth']) ? $config['redis_auth'] : '',
'db' => isset($config['redis_db']) ? (int)$config['redis_db'] : 8,
'log' => isset($config['redis_log']) ? (int)$config['redis_log'] : 4,
];
}
/**
* @desc 初始化sql状态存储对象
* @access private
* @return void
*/
private function _init_sqlstatement()
{
if ($this->_state === null) {
$this->_state = new State();
}
}
/**
* @desc 断线重连
* @access private
* @return void
*/
private function _reconnect()
{
$this->_mysql_handler->pdo_instance = $this->_mysql_handler->reconnect();
}
/**
* @desc 启动事务
* @access public
* @return self
*/
public function transaction(): self
{
$this->_mysql_handler->pdo_instance->beginTransaction();
return $this;
}
/**
* 提交事务
* @access public
* @return void
*/
public function commit()
{
$this->_mysql_handler->pdo_instance->commit();
$this->close();
}
/**
* 撤销事务,回滚
* @access public
* @return void
*/
public function rollback()
{
$this->_mysql_handler->pdo_instance->rollback();
$this->close();
}
/**
* @desc 选择字段
* @access public
* @return self
*/
public function select(): self
{
$this->_init_sqlstatement();
$this->_state->query_type = 1;
$numOfArgs = func_num_args();
if ($numOfArgs == 0 || trim((string)func_get_arg(0)) == '') {
$this->_state->field = '*';
} else {
$selectField = &$this->_state->field;
if (is_array(func_get_arg(0))) {
foreach (func_get_arg(0) as $v) {
$selectField .= $v . ',';
}
} else {
for ($i = 0; $i < $numOfArgs; $i++) {
$selectField .= ',' . func_get_arg($i);
}
}
$selectField = ltrim($selectField, ',');
}
return $this;
}
/**
* @desc 选择数据表
* @param string|array $table 表名/表名数组[表1名=>别名1,表2名=>别名2,...]
* @param string $alias 表别名
*/
public function from($table, string $alias = ''): self
{
$this->_init_sqlstatement();
if (is_array($table)) {
$clause = '';
foreach ($table as $k => $v) {
$clause .= ', ' . trim($this->_prefix . trim($k) . ' ' . $v);
}
$this->_state->table = ltrim($clause, ', ');
} else {
$this->_state->table = trim($this->_prefix . trim((string)$table) . ' ' . $alias);
}
return $this;
}
/**
* @desc 左联表
* @access public
* @param string $table 表名
* @param string $alias 表别名
* @param string $case 关联条件
* @return self
*/
public function leftJoin(string $table, string $alias, string $case): self
{
$this->_state->join .= ' LEFT JOIN `' . $this->_prefix . trim($table) . '` AS ' . $alias . ' ON ' . $case;
return $this;
}
/**
* @desc 内联表
* @access public
* @param string $table 表名
* @param string $alias 表别名
* @param string $case 关联条件
* @return self
*/
public function join(string $table, string $alias, string $case): self
{
$this->_state->join .= ' INNER JOIN `' . $this->_prefix . trim($table) . '` AS ' . $alias . ' ON ' . $case;
return $this;
}
/**
* @desc 右联表
* @access public
* @return self
*/
public function rightJoin(string $table, string $alias, string $case): self
{
$this->_state->join .= ' RIGHT JOIN `' . $this->_prefix . trim($table) . '` AS ' . $alias . ' ON ' . $case;
return $this;
}
/**
* @desc 内联表
* @access public
* @param string $table 表名
* @param string $alias 表别名
* @param string $case 关联条件
* @return self
*/
public function innerJoin(string $table, string $alias, string $case): self
{
$this->_state->join .= ' INNER JOIN `' . $this->_prefix . trim($table) . '` AS ' . $alias . ' ON ' . $case;
return $this;
}
/**
* @desc insert语句中的存在即更新, 注意! 数据表中必须存在主键或唯一索引
* @access public
* @return self
*/
public function dupkeyUpdate($condition): self
{
if ($this->_state->query_type == 2) {
if (is_array($condition)) {
$clause = &$this->_state->on_duplicate_key_update;
$values = &$this->_state->bind_params;
foreach ($condition as $k => $v) {
$clause .= ',' . $k . '=?';
$values[] = $v;
}
$clause = ltrim($clause, ', ');
} else {
$this->_state->on_duplicate_key_update = $condition;
}
return $this;
} else {
$this->_trigger_exception('DUPLICATE KEY UPDATE: requires to start with INSERT at ' . __LINE__);
}
}
/**
* @desc where语句
* @access public
* @return self
*/
public function where(): self
{
$this->_init_sqlstatement();
$numOfArgs = func_num_args();
if ($numOfArgs && is_array(func_get_arg(0))) {
$clause = '';
foreach (func_get_arg(0) as $k => $v) {
$clause .= ' AND ' . $k . '=?';
$this->_state->bind_params[] = $v;
}
if (empty($this->_state->where)) {
$this->_state->where = ltrim($clause, ' AND ');
} else {
$this->_state->where .= ' AND ' . ltrim($clause, ' AND ');
}
} else {
switch ($numOfArgs) {
// 字符串条件
case 1:
$clause = trim(func_get_arg(0));
if (empty($this->_state->where)) {
$this->_state->where = $clause;
} else {
$this->_state->where .= ' AND ' . $clause;
}
break;
// 单等号条件
case 2:
$clause = func_get_arg(0) . ' = ?';
if (empty($this->_state->where)) {
$this->_state->where = $clause;
} else {
$this->_state->where .= ' AND ' . $clause;
}
$this->_state->bind_params[] = func_get_arg(1);
break;
// 其他符号条件
case 3:
$arg1 = strtolower(func_get_arg(1));
if (array_key_exists($arg1, [
'in' => 0,
'not in' => 0,
])) {
if (trim(func_get_arg(2)) == '') {
$this->_trigger_exception('IN(): missing argument');
} else {
$clause = func_get_arg(0) . ' ' . $arg1 . ' (' . func_get_arg(2) . ')';
$this->_build_where_clause($clause);
}
} elseif (array_key_exists($arg1, [
'like' => 0,
'not like' => 0
])) {
if (trim(func_get_arg(2)) == '') {
$this->_trigger_exception('LIKE(): missing argument at ' . __LINE__);
} else {
$clause = func_get_arg(0) . ' ' . $arg1 . " ('" . func_get_arg(2) . "')";
$this->_build_where_clause($clause);
}
} elseif (array_key_exists($arg1, [
'!=' => 0,
'>' => 0,
'>=' => 0,
'<=' => 0,
'<' => 0,
'<>' => 0,
'=' => 0,
])) {
$clause = func_get_arg(0) . ' ' . $arg1 . ' ' . '?';
if (empty($this->_state->where)) {
$this->_state->where = $clause;
} else {
$this->_state->where .= ' AND ' . $clause;
}
$this->_state->bind_params[] = func_get_arg(2);
} else {
$this->_trigger_exception('where(): bad operator at ' . __LINE__);
}
break;
// 过多的参数
default:
$this->_trigger_exception('where(): too many arguments');
}
}
return $this;
}
/**
* @desc where 语句中的IN
* @param string $field
* @param array|string|int $values
* @param string $param_type int或者string
* @return self
*/
public function whereIn($field, $values, $param_type = '', $method = 'IN')
{
if (is_string($values) || is_int($values)) {
// 字符串
$this->_build_where_clause(sprintf('%s %s (%s)', $field, $method, $values));
}
if (is_array($values)) {
// 数组
if (!count($values)) {
$this->_trigger_exception($method . ': missing arguments');
}
$str = ltrim(array_reduce($values, function ($r, $vv) use ($param_type) {
switch (gettype($vv)) {
case 'integer':
return sprintf('%s,%d', $r, $vv);
case 'string':
if ($param_type == 'int') {
return sprintf('%s,%d', $r, (int)$vv);
} else {
return sprintf("%s,'%s'", $r, addslashes($vv));
}
default:
$this->_trigger_exception('IN/NOT IN only supports int & string');
}
}), ',');
$this->_build_where_clause(sprintf('%s %s (%s)', $field, $method, $str));
}
return $this;
}
/**
* @desc where 语句中的NOT IN处理
* @param string $field
* @param array|string $values
* @return self
*/
public function whereNotIn($field, $values, $parameter_type = '')
{
return $this->whereIn($field, $values, $parameter_type, 'NOT IN');
}
/**
* @desc 在where语句的基础上增加或计算符的条件语句
* @access public
* @return self
*/
public function orWhere()
{
switch (func_num_args()) {
case 0:
self::_trigger_runtime_exception('orWhere(): missing argument at ' . __LINE__);
case 1:
if (is_string(func_get_arg(0))) {
if (empty($this->_state->where)) {
self::_trigger_runtime_exception('orWhere() must be called after other where() statements at ' . __LINE__);
}
// 字符串条件
$this->_state->where .= ' OR ' . trim(func_get_arg(0));
} else {
self::_trigger_runtime_exception('orWhere(): type error at ' . __LINE__);
}
break;
default:
self::_trigger_runtime_exception('orWhere(): wrong behavior at ' . __LINE__);
}
return $this;
}
/**
* @desc 组装where子句当中存在like或in方法时所用的代码复用方法
* @access private
* @param string $clause
*/
private function _build_where_clause(string $clause)
{
if (empty($this->_state->where)) {
$this->_state->where = $clause;
} else {
$this->_state->where .= ' AND ' . $clause;
}
}
/**
* @desc 连贯操作之排序
* @access public
* @param string $order
* @return self
*/
public function orderBy(string $order): self
{
$this->_state->order = ' ORDER BY ' . $order;
return $this;
}
/**
* @desc 连贯操作之分组
* @access public
* @param string $group
* @return self
*/
public function groupBy(string $group): self
{
$this->_state->group = ' GROUP BY ' . $group;
return $this;
}
/**
* @desc 连贯操作之having
* @access public
* @param string $having
* @return self
*/
public function having(string $having): self
{
$this->_state->having = ' HAVING ' . $having;
return $this;
}
/**
* @desc 批量更新方法
* @access public
* @param array $params 以主键为首个键值的数组
* @return self
*/
public function batchUpdate($params): self
{
$this->_state->query_type = 31;
if (is_array($params) && isset($params[0])) {
$primaryKey = $primaryValue = '';
foreach ($params as $v) {
foreach ($v as $k2 => $v2) {
$primaryKey = $k2;
$primaryValue = $v2;
break;
}
break;
}
$arr2 = [];
foreach ($params as $v) {
$arr2[$v[$primaryKey]] = $v;
array_splice($arr2[$v[$primaryKey]], $primaryKey, 1);
}
$sql = '';
$sets = &$this->_state->bind_params;
foreach ($arr2[$primaryValue] as $k => $v) {
$sql .= sprintf('%s = CASE %s ', $k, $primaryKey);
foreach ($arr2 as $k2 => $v2) {
$sql .= "WHEN ? THEN ? ";
$sets[] = $k2;
$sets[] = $v2[$k];
}
$sql .= 'END, ';
}
$sql = substr($sql, 0, strrpos($sql, ','));
$this->_state->update_field = $sql;
$this->_state->where = sprintf('%s IN (%s)', $primaryKey, implode(',', array_keys($arr2)));
} else {
$this->_trigger_exception('batchUpdate(): bad parameter');
}
return $this;
}
/**
* @desc 批量插入方法
* @access public
* @param array $params 二维数组 首个子数组内含键必须包含字段名称信息
* @return self
*/
public function batchInsert($params): self
{
$this->_state->query_type = 21;
if (is_array($params) && count($params) >= 1) {
$insertField = $insertValues = '';
foreach ($params[0] as $k => $v) {
$insertField .= sprintf(',`%s`', $k);
}
$this->_state->insert_field = ltrim($this->_state->insert_field, ',');
foreach ($params as $v) {
$insertValues .= '( ';
foreach ($v as $vv) {
$insertValues .= ',?';
$this->_state->bind_params[] = $vv;
}
$insertValues = sprintf('%s),', ltrim($insertValues, ','));
}
$this->_state->insert_values = ltrim($insertValues, ',');
} else {
$this->_trigger_exception('batchInsert(): bad parameter');
}
return $this;
}
/**
* @desc 连贯操作之限制条数
* @access public
* @return self
*/
public function limit(): self
{
switch (func_num_args()) {
case 1:
$this->_state->limit = sprintf('LIMIT %u', func_get_arg(0));
break;
case 2:
$this->_state->limit = sprintf('LIMIT %u,%u', func_get_arg(0), func_get_arg(1));
break;
default:
$this->_trigger_exception('limit(): bad parameter');
}
return $this;
}
/**
* @desc 连贯操作之插入指定数据表
* @access public
* @param string $table
* @return self
*/
public function insert(string $table): self
{
$this->_init_sqlstatement();
$this->_state->query_type = 2;
$this->_state->table = $this->_prefix . trim($table);
return $this;
}
/**
* @desc 连贯操作之插入字段和对应值
* @access public
* @return self
*/
public function values(): self
{
$numOfArgs = func_num_args();
if ($numOfArgs) {
$valuesArr = &$this->_state->bind_params;
if (is_array(func_get_arg(0))) {
$numOfValues = count(func_get_arg(0));
$insertField = &$this->_state->insert_field;
foreach (func_get_arg(0) as $k => $v) {
$insertField .= ',`' . $k . '`';
$valuesArr[] = $v;
}
$this->_state->insert_field = ltrim($this->_state->insert_field, ',');
} else {
$numOfValues = $numOfArgs;
$this->_state->insert_field = '';
for ($i = 0; $i < $numOfArgs; $i++) {
$valuesArr[] = func_get_arg($i);
}
}
$insertValues = &$this->_state->insert_values;
$insertValues .= str_repeat(',?', $numOfValues);
$insertValues = '( ' . ltrim($insertValues, ',') . ' )';
} else {
$this->_trigger_exception('values() expects at least 1 parameter');
}
return $this;
}
/**
* @desc 连贯操作之获取插入的Id
* @access public
* @return self
*/
public function getId(): self
{
$this->_state->get_last_inserted_id = 1;
return $this;
}
// 获取指定字段的记录数量
public function count($field = '*')
{
return $this->_callFuncOnFieldSelector('count', $field);
}
// 获取指定字段的最大值
public function max($field = '')
{
return $this->_callFuncOnFieldSelector('max', $field);
}
// 获取指定字段的最小值
public function min($field = '')
{
return $this->_callFuncOnFieldSelector('min', $field);
}
// 获取指定字段的平均值
public function avg($field = '')
{
return $this->_callFuncOnFieldSelector('avg', $field);
}
// 获取指定字段的和
public function sum($field = '')
{
return $this->_callFuncOnFieldSelector('sum', $field);
}
private function _callFuncOnFieldSelector($funcName, $field)
{
$this->_state->query_type = 1;
if (empty($field)) {
$this->_trigger_exception('sql needs a field as parameter');
} else {
$this->_state->field = $funcName . '(' . $field . ')';
}
return $this->sql($this->_build_sql4query())->bind($this->_state->bind_params)->_grab();
}
/**
* @desc 自增操作
* @access public
* @return self
*/
public function increase(): self
{
switch (func_num_args()) {
case 1:
$this->_state->update_field = '`' . func_get_arg(0) . '` = `' . func_get_arg(0) . '`+1';
break;
case 2:
$this->_state->update_field = '`' . func_get_arg(0) . '` = `' . func_get_arg(0) . '`+?';
$this->_state->bind_params[] = func_get_arg(1);
break;
default:
$this->_trigger_exception('invalid parameter while using increment()');
}
return $this;
}
/**
* @desc 自减操作
* @access public
* @return self
*/
public function decrease(): self
{
switch (func_num_args()) {
case 1:
$this->_state->update_field = '`' . func_get_arg(0) . '` = `' . func_get_arg(0) . '`-1';
break;
case 2:
$this->_state->update_field = '`' . func_get_arg(0) . '` = `' . func_get_arg(0) . '`-?';
$this->_state->bind_params[] = func_get_arg(1);
break;
default:
$this->_trigger_exception('invalid paramter while using decrement()');
}
return $this;
}
/**
* @desc 连贯操作之开始插入数据
* @access public
* @return array|false|mixed|void
*/
public function go()
{
if ($this->_state->is_sql_native) {
// 原生语句执行
if ($this->_state->query_type == 1) {
$this->_trigger_exception('sql query type error at ' . __LINE__);
}
}
switch ($this->_state->query_type) {
case 2: // 插入
case 21:
return $this->_run($this->_build_sql4insert(), $this->_state->bind_params, 4);
case 3: // 更新
case 31:
return $this->_run($this->_build_sql4update(), $this->_state->bind_params, 5);
case 4: // 删除
return $this->_run($this->_build_sql4delete(), $this->_state->bind_params, 6);
default:
$this->_trigger_exception('can not determine sql type.');
}
}
/**
* @desc 连贯操作之更新的目标数据表
* @access public
* @param string $table
* @return self
*/
public function update(string $table): self
{
$this->_init_sqlstatement();
$this->_state->query_type = 3;
$this->_state->table = $this->_prefix . trim($table);
return $this;
}
/**
* @desc 连贯操作之指定更新的字段和值
* @access public
* @param array $params
* @return self
*/
public function set(array $params): self
{
$updateField = &$this->_state->update_field;
$setsArr = &$this->_state->bind_params;
foreach ($params as $k => $v) {
$updateField .= ',`' . $k . '`=?';
$setsArr[] = $v;
}
$updateField = ltrim($updateField, ',');
return $this;
}
/**
* @desc 连贯操作之删除的目标数据库
* @access public
* @param string $table
* @return self
*/
public function delete(string $table): self
{
$this->_init_sqlstatement();
$this->_state->query_type = 4;
$this->_state->table = $this->_prefix . trim($table);
return $this;
}
/**
* 查询多行
* @access public
*/
public function all()
{
if ($this->_state->is_sql_native) {
// 原生语句
return $this->_all();
} else {
// 连贯操作
if (func_num_args() && func_get_arg(0) > 0) {
$this->_state->limit = ' LIMIT ' . abs((int)func_get_arg(0));
}
return $this->sql($this->_build_sql4query())->bind($this->_state->bind_params)->_all();
}
}
/**
* 方法别名: 查询多行
* @access public
*/
public function rows()
{
return $this->all();
}
/**
* 查询单行
* @access public
* @return array
*/
public function fetch()
{
if ($this->_state->is_sql_native) {
// 原生语句
return $this->_fetch();
} else {
// 连贯操作
$this->_state->limit = ' LIMIT 1';
return $this->sql($this->_build_sql4query())->bind($this->_state->bind_params)->_fetch();
}
}
public function row()
{
return $this->fetch();
}
/**
* 函数别名: 查询一行, 兼容TP框架orm
* @access public
*/
public function find()
{
return $this->row();
}
/**
* 查询一个
*/
public function grab()
{
if ($this->_state->is_sql_native) {
// 原生语句
return $this->_grab();
} else {
// 连贯操作
$this->_state->limit = ' LIMIT 1';
if (func_num_args() && !empty(func_get_arg(0))) {
$this->_state->field = trim(func_get_arg(0));
}
return $this->sql($this->_build_sql4query())->bind($this->_state->bind_params)->_grab();
}
}
/**
* 打印出Sql语句
* @access public
* @return bool|string|void
*/
public function getSql()
{
switch ($this->_state->query_type) {
case 1:
return $this->_build_sql4query();
case 2:
case 21:
return $this->_build_sql4insert();
case 3:
case 31:
return $this->_build_sql4update();
case 4:
return $this->_build_sql4delete();
default:
$this->_trigger_exception('unknown sql type');
}
}
/**
* 方法别名: 打印sql语句
* @access public
*/
public function print()
{
return $this->getSql();
}
/**
* 方法别名: 打印Sql语句
* @access public
*/
public function dump()
{
return $this->getSql();
}
/**
* 构造删除用的Sql语句
* @access private
*/
private function _build_sql4delete()
{
// 如果where条件不存在,则终止操作
if (empty($this->_state->table) || empty($this->_state->where)) {
return false;
}
$this->_state->sql_statement = 'DELETE FROM `' . $this->_state->table . '` WHERE ' . $this->_state->where . $this->_state->limit;
return $this->_state->sql_statement;
}
/**
* 构造更新用的Sql语句
* @access private
* @return string
*/
private function _build_sql4update()
{
// 如果where条件不存在,则终止操作
if (empty($this->_state->table) || empty($this->_state->where)) {
self::_trigger_runtime_exception('Attempted unconditionally update. Stop.');
}
$this->_state->sql_statement = 'UPDATE `' . $this->_state->table . '` SET ' . $this->_state->update_field . ' WHERE ' . $this->_state->where . $this->_state->limit;
return $this->_state->sql_statement;
}
/**
* 构造插入用的Sql语句
* @access private
*/
private function _build_sql4insert()
{
if (empty($this->_state->table)) {
return false;
}
if (empty($this->_state->insert_field)) {
$this->_state->sql_statement = empty($this->_state->on_duplicate_key_update) ? 'INSERT INTO `' . $this->_state->table . '` VALUES ' . $this->_state->insert_values : 'INSERT INTO `' . $this->_state->table . '` VALUES ' . $this->_state->insert_values . ' ON DUPLICATE KEY UPDATE ' . $this->_state->on_duplicate_key_update;
} else {
$this->_state->sql_statement = empty($this->_state->on_duplicate_key_update) ? 'INSERT INTO `' . $this->_state->table . '` ( ' . $this->_state->insert_field . ' ) VALUES ' . $this->_state->insert_values : 'INSERT INTO `' . $this->_state->table . '` ( ' . $this->_state->insert_field . ' ) VALUES ' . $this->_state->insert_values . ' ON DUPLICATE KEY UPDATE ' . $this->_state->on_duplicate_key_update;
}
return $this->_state->sql_statement;
}
/**
* 构造查询用的Sql语句
* @access private
* @return string
*/
private function _build_sql4query()
{
if (empty($this->_state->table)) {
self::_trigger_runtime_exception('No table was specified while querying');
}
$this->_state->sql_statement = sprintf('SELECT %s FROM %s %s %s %s %s %s %s', $this->_state->field, $this->_state->table, $this->_state->join, empty($this->_state->where) ? '' : 'WHERE ' . $this->_state->where, $this->_state->group, $this->_state->having, $this->_state->order, $this->_state->limit);
return $this->_state->sql_statement;
}
/**
* 触发错误并销毁sql状态存储对象
* @access public
* @param string $msg
*/
private function _trigger_exception(string $msg)
{
$this->_state = null;
throw new \PDOException($msg);
}
/**
* 准备Sql语句
* @access public
* @param string $sql 原始sql语句
* @return self
*/
public function sql(string $sql): self
{
$this->_init_sqlstatement();
$this->_state->is_sql_native = 1;
if (trim($sql) == '') {
$this->_trigger_exception('no sql found');
} else {
switch (strtolower($sql[0])) {
case 's':
// select 查询语句
$this->_state->query_type = 1;
break;
case 'i':
// insert 插入语句
$this->_state->query_type = 2;
break;
case 'u':
// update 更新语句
$this->_state->query_type = 3;
break;
case 'd':
if (strtolower($sql[1]) == 'e') {
// delete 删除语句
$this->_state->query_type = 4;
break;
} else {
// 不支持风险语句drop
$this->_trigger_exception('operation type error');
}
default:
$this->_trigger_exception('operation type error');
}
$this->_state->sql_statement = str_ireplace('*_', $this->_prefix, trim($sql));
}
return $this;
}
/**
* 准备绑定的参数
* @access public
*/
public function bind($params): self
{
$this->_state->bind_params = $params;
return $this;
}
/**
* 使用cache存储
* @param int $ttl 生存秒
* @param string $key
*/
public function cache(int $ttl = 30, string $key = '')
{
$this->_state->cache = 1;
$this->_state->cache_ttl = abs((int)$ttl);
$this->_state->cache_key = $key;
return $this;
}
/**
* @desc 参数绑定处理
* @access private
* @param object $pdo_statement
* @param array $params
* @return object
*/
private static function preparation_loop($pdo_statement, $params)
{
foreach ($params as $k => $v) {
switch (gettype($v)) {
case 'integer':
case 'double':
$pdo_statement->bindValue($k + 1, $v, \PDO::PARAM_INT);
break;
case 'string':
$pdo_statement->bindValue($k + 1, $v, \PDO::PARAM_STR);
break;
case 'boolean':
$pdo_statement->bindValue($k + 1, ($v ? '1' : '0'), \PDO::PARAM_BOOL);
break;
case 'NULL':
$pdo_statement->bindValue($k + 1, null, \PDO::PARAM_NULL);
break;
case 'resource':
$pdo_statement->bindValue($k + 1, $v, \PDO::PARAM_LOB);
break;
case 'array':
self::_trigger_runtime_exception('An array with > 2 dimensions was passed into the where clause');
}
}
return $pdo_statement;
}
/**
* 预编译执行SQL
* @access private
* @param string $sql 带有占位符的sql语句
* @param array $params 绑定的参数
* @param int $sql_type 执行类型 1 多行 2 单行 3 单个 4 插入 5 更新
* @return mixed 查询结果,或执行后受影响的行数
* @author qinchao <castor@happycoworker.com>
*/
private function _run($sql, $params = [], $sql_type = 1)
{
if ($params === []) {
$pdo_statement = $this->_mysql_handler->pdo_instance->query($sql);
} else if (empty($sql)) {
$this->_trigger_exception('No sql.');
} else {
$pdo_statement = $this->_mysql_handler->pdo_instance->prepare($sql);
}
if ($this->_mysql_handler->pdo_instance->errorCode() != '00000') {
$errInfo = $this->_mysql_handler->pdo_instance->errorInfo();
if ($errInfo[1] === 2013 || $errInfo[1] === 2016 || is_string($errInfo[2]) && strpos($errInfo[2], 'STMT_PREPARE') !== false) {
// 此时已断线
$this->_reconnect();
return $this->_run($sql, $params, $sql_type);
} else {
// 其他错误
if ($this->_log == 1) {
error_log(@json_encode(array_merge($this->_mysql_handler->pdo_instance->errorInfo(), [$sql]), JSON_PRETTY_PRINT), 3, SWOOLE_LOG_FILE);
}
$this->_trigger_exception(json_encode($this->_mysql_handler->pdo_instance->errorInfo(), JSON_UNESCAPED_UNICODE));
}
}
if ($pdo_statement === false) {
$this->_state = null;
return false;
}
if ($params !== []) {
if (is_array($params)) {
$pdo_statement = self::preparation_loop($pdo_statement, $params);
$pdo_statement->execute($params);
} else {
$pdo_statement = self::preparation_loop($pdo_statement, [$params]);
$pdo_statement->execute();
}
}
switch ($sql_type) {
case 1:
$r = $pdo_statement->fetchAll(\PDO::FETCH_ASSOC);
if ($r === false) {
$r = [];
}
break;
case 2:
$r = $pdo_statement->fetch(\PDO::FETCH_ASSOC);
if ($r === false) {
$r = [];
}
break;
case 3:
$r = $pdo_statement->fetchColumn();
break;
case 4: // 插入
$r = $pdo_statement->rowCount();
if ($r && $this->_state->get_last_inserted_id == 1) {
$r = $this->_mysql_handler->pdo_instance->lastInsertId();
}
$pdo_statement = null;
$this->_state = null;
return $r;
case 5: // 更新
case 6: // 删除
$r = $pdo_statement->rowCount();
$pdo_statement = null;
$this->_state = null;
return $r;
default:
self::_trigger_runtime_exception("Illegal query type.");
}
if ($this->_state->cache == 1) {
$this->_writeInRedis($r);
}
$pdo_statement = null;
$this->_state = null;
return $r;
}
/**
* 往缓存里写数据
* @access private
*/
private function _writeInRedis($data)
{
$r = RedisWorker::connect($this->_redis_config['db']);
if (trim((string)$this->_state->cache_key) == '') {
$this->_state->cache_key = substr(md5($this->_state->sql_statement . json_encode($this->_state->bind_params)), 4, 24);
}
if (!$r->exists($this->_state->cache_key)) {
$r->set($this->_state->cache_key, (string)json_encode($data));
$r->expire($this->_state->cache_key, $this->_state->cache_ttl);
}
$r->close();
}
/**
* 从缓存取数据
* @access private
*/
private function _read_from_redis($sql_type)
{
$r = RedisWorker::connect($this->_redis_config['db']);
if (trim((string)$this->_state->cache_key) == '') {
$result = $r->get(substr(md5($this->_state->sql_statement . (count($this->_state->bind_params) > 0 ? json_encode($this->_state->bind_params) : '')), 4, 24));
if (!$result) {
$r->close();
return $this->_run($this->_state->sql_statement, $this->_state->bind_params, $sql_type);
} else {
$this->_state = null;
$r->close();
return json_decode($result, true);
}
} else {
$result = $r->get($this->_state->cache_key);
if (!$result) {
// 结果已过期
$r->close();
return $this->_run($this->_state->sql_statement, $this->_state->bind_params, $sql_type);
} else {
$this->_state = null;
$r->close();
return json_decode($result, true);
}
}
}
// 预编译方式读取多行
private function _all()
{
if (trim($this->_state->sql_statement) == '') {
$this->_trigger_exception('No sql');
} else {
if ($this->_state->cache == 1) {
$result = $this->_read_from_redis(1);
} else {
$result = $this->_run($this->_state->sql_statement, $this->_state->bind_params, 1);
}
return $result;
}
}
/**
* 预编译方式获取1行
* @return array|false
*/
private function _fetch()
{
if (trim($this->_state->sql_statement) == '') {
$this->_trigger_exception('No sql');
} else {
if ($this->_state->cache == 1) {
$result = $this->_read_from_redis(2);
} else {
$result = $this->_run($this->_state->sql_statement, $this->_state->bind_params, 2);
}
return $result;
}
}
/**
* 预编译方式读取1个
* @access private
*/
private function _grab()
{
if (trim($this->_state->sql_statement) == '') {
$this->_trigger_exception('No sql');
} else {
if ($this->_state->cache == 1) {
$result = $this->_read_from_redis(3);
} else {
$result = $this->_run($this->_state->sql_statement, $this->_state->bind_params, 3);
}
return $result;
}
}
/**
* @desc 呼叫匿名方法
* @access public
* @param $fn
* @param $args
* @return Worker|void
*/
public function __call($fn, $args)
{
switch (count($args)) {
case 1: // where+字段名
$this->_init_sqlstatement();
if (strtolower(substr($fn, 0, 5)) == 'where') {
$field = strtolower(substr($fn, 5));
if (empty($this->_state->where)) {
$this->_state->where = $field . ' = ?';
} else {
$this->_state->where .= ' AND ' . $field . ' = ?';
}
$this->_state->bind_params[] = $args[0];
}
return $this;
}
}
/**
* @desc 触发错误并销毁sql状态存储对象
* @access private
* @param string $msg
*/
private static function _trigger_runtime_exception(string $msg)
{
throw new \Exception($msg);
}
/**
* 关闭连接
* @access public
* @return void
*/
public function close()
{
$this->_mysql_handler->close();
}
/**
* 析构
* @access public
*/
public function __destruct()
{
$this->_mysql_handler->close();
}
}注意: 这些代码是阻塞的, 不适用于协程版的swoole。
