tp5源码分析之数据库查询

1 查询对象

查询对象(Query)实现基本的查询操作

与模型不同的是，查询对象是在数据库的整体上进行操作，所以需要指定数据库表
模型针对的数据库中的某个数据表的操作。

2 查询操作

2-1 增删改查

$query->insert()

public function insert(array $data, $replace = false, $getLastInsID = false, $sequence = null)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        // 生成SQL语句
        $sql = $this->builder()->insert($data, $options, $replace);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        }

        // 执行操作
        $result = $this->execute($sql, $bind);
        if ($getLastInsID) {
            $sequence = $sequence ?: (isset($options['sequence']) ? $options['sequence'] : null);
            return $this->getLastInsID($sequence);
        }
        return $result;
    }

$query->insertAll()

public function insertAll(array $dataSet)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        if (!is_array(reset($dataSet))) {
            return false;
        }
        // 生成SQL语句
        $sql = $this->builder()->insertAll($dataSet, $options);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        } else {
            // 执行操作
            return $this->execute($sql, $bind);
        }
    }

$query->selectInsert()

public function selectInsert($fields, $table)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        // 生成SQL语句
        $table = $this->parseSqlTable($table);
        $sql   = $this->builder()->selectInsert($fields, $table, $options);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        } else {
            // 执行操作
            return $this->execute($sql, $bind);
        }
    }

$query->select()

public function select($data = null)
    {
        if ($data instanceof Query) {
            return $data->select();
        } elseif ($data instanceof \Closure) {
            call_user_func_array($data, [ & $this]);
            $data = null;
        }
        // 分析查询表达式
        $options = $this->parseExpress();

        if (false === $data) {
            // 用于子查询 不查询只返回SQL
            $options['fetch_sql'] = true;
        } elseif (!is_null($data)) {
            // 主键条件分析
            $this->parsePkWhere($data, $options);
        }

        $resultSet = false;
        if (empty($options['fetch_sql']) && !empty($options['cache'])) {
            // 判断查询缓存
            $cache = $options['cache'];
            unset($options['cache']);
            $key       = is_string($cache['key']) ? $cache['key'] : md5(serialize($options));
            $resultSet = Cache::get($key);
        }
        if (!$resultSet) {
            // 生成查询SQL
            $sql = $this->builder()->select($options);
            // 获取参数绑定
            $bind = $this->getBind();
            if ($options['fetch_sql']) {
                // 获取实际执行的SQL语句
                return $this->connection->getRealSql($sql, $bind);
            }
            // 执行查询操作
            $resultSet = $this->query($sql, $bind, $options['master'], $options['fetch_class']);

            if ($resultSet instanceof \PDOStatement) {
                // 返回PDOStatement对象
                return $resultSet;
            }

            if (isset($cache)) {
                // 缓存数据集
                if (isset($cache['tag'])) {
                    Cache::tag($cache['tag'])->set($key, $resultSet, $cache['expire']);
                } else {
                    Cache::set($key, $resultSet, $cache['expire']);
                }
            }
        }

        // 返回结果处理
        if (count($resultSet) > 0) {
            // 数据列表读取后的处理
            if (!empty($this->model)) {
                // 生成模型对象
                $model = $this->model;
                foreach ($resultSet as $key => $result) {
                    /** @var Model $result */
                    $result = new $model($result);
                    $result->isUpdate(true);
                    // 关联查询
                    if (!empty($options['relation'])) {
                        $result->relationQuery($options['relation']);
                    }
                    $resultSet[$key] = $result;
                }
                if (!empty($options['with']) && $result instanceof Model) {
                    // 预载入
                    $resultSet = $result->eagerlyResultSet($resultSet, $options['with'], is_object($resultSet) ? get_class($resultSet) : '');
                }
            }
        } elseif (!empty($options['fail'])) {
            $this->throwNotFound($options);
        }
        return $resultSet;
    }

$query->find()

public function find($data = null)
    {
        if ($data instanceof Query) {
            return $data->find();
        } elseif ($data instanceof \Closure) {
            call_user_func_array($data, [ & $this]);
            $data = null;
        }
        // 分析查询表达式
        $options = $this->parseExpress();

        if (!is_null($data)) {
            // AR模式分析主键条件
            $this->parsePkWhere($data, $options);
        }

        $options['limit'] = 1;
        $result           = false;
        if (empty($options['fetch_sql']) && !empty($options['cache'])) {
            // 判断查询缓存
            $cache = $options['cache'];
            if (true === $cache['key'] && !is_null($data) && !is_array($data)) {
                $key = 'think:' . $options['table'] . '|' . $data;
            } else {
                $key = is_string($cache['key']) ? $cache['key'] : md5(serialize($options));
            }
            $result = Cache::get($key);
        }
        if (!$result) {
            // 生成查询SQL
            $sql = $this->builder()->select($options);
            // 获取参数绑定
            $bind = $this->getBind();
            if ($options['fetch_sql']) {
                // 获取实际执行的SQL语句
                return $this->connection->getRealSql($sql, $bind);
            }
            // 执行查询
            $result = $this->query($sql, $bind, $options['master'], $options['fetch_class']);

            if ($result instanceof \PDOStatement) {
                // 返回PDOStatement对象
                return $result;
            }

            if (isset($cache)) {
                // 缓存数据
                if (isset($cache['tag'])) {
                    Cache::tag($cache['tag'])->set($key, $result, $cache['expire']);
                } else {
                    Cache::set($key, $result, $cache['expire']);
                }
            }
        }

        // 数据处理
        if (!empty($result[0])) {
            $data = $result[0];
            if (!empty($this->model)) {
                // 返回模型对象
                $model = $this->model;
                $data  = new $model($data);
                $data->isUpdate(true, isset($options['where']['AND']) ? $options['where']['AND'] : null);
                if ($this->allowField) {
                    $data->allowField($this->allowField);
                }
                // 关联查询
                if (!empty($options['relation'])) {
                    $data->relationQuery($options['relation']);
                }
                if (!empty($options['with'])) {
                    // 预载入
                    $data->eagerlyResult($data, $options['with'], is_object($result) ? get_class($result) : '');
                }
            }
        } elseif (!empty($options['fail'])) {
            $this->throwNotFound($options);
        } else {
            $data = null;
        }
        return $data;
    }

$query->update()

public function update(array $data)
    {
        $options = $this->parseExpress();
        $pk      = $this->getPk($options);
        if (isset($options['cache']) && is_string($options['cache'])) {
            $key = $options['cache'];
        }

        if (empty($options['where'])) {
            // 如果存在主键数据 则自动作为更新条件
            if (is_string($pk) && isset($data[$pk])) {
                $where[$pk] = $data[$pk];
                if (!isset($key)) {
                    $key = 'think:' . $options['table'] . '|' . $data[$pk];
                }
                unset($data[$pk]);
            } elseif (is_array($pk)) {
                // 增加复合主键支持
                foreach ($pk as $field) {
                    if (isset($data[$field])) {
                        $where[$field] = $data[$field];
                    } else {
                        // 如果缺少复合主键数据则不执行
                        throw new Exception('miss complex primary data');
                    }
                    unset($data[$field]);
                }
            }
            if (!isset($where)) {
                // 如果没有任何更新条件则不执行
                throw new Exception('miss update condition');
            } else {
                $options['where']['AND'] = $where;
            }
        } elseif (is_string($pk) && isset($options['where']['AND'][$pk]) && is_scalar($options['where']['AND'][$pk])) {
            $key = 'think:' . $options['table'] . '|' . $options['where']['AND'][$pk];
        }
        // 生成UPDATE SQL语句
        $sql = $this->builder()->update($data, $options);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        } else {
            // 检测缓存
            if (isset($key) && Cache::get($key)) {
                // 删除缓存
                Cache::rm($key);
            }
            // 执行操作
            return '' == $sql ? 0 : $this->execute($sql, $bind);
        }
    }

$query->delete()

public function delete($data = null)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        if (isset($options['cache']) && is_string($options['cache'])) {
            $key = $options['cache'];
        }

        if (!is_null($data) && true !== $data) {
            if (!isset($key) && !is_array($data)) {
                // 缓存标识
                $key = 'think:' . $options['table'] . '|' . $data;
            }
            // AR模式分析主键条件
            $this->parsePkWhere($data, $options);
        }

        if (true !== $data && empty($options['where'])) {
            // 如果条件为空 不进行删除操作 除非设置 1=1
            throw new Exception('delete without condition');
        }
        // 生成删除SQL语句
        $sql = $this->builder()->delete($options);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        }

        // 检测缓存
        if (isset($key) && Cache::get($key)) {
            // 删除缓存
            Cache::rm($key);
        }
        // 执行操作
        return $this->execute($sql, $bind);
    }

2-2 事务操作

$query->startTrans()

启动事务

public function startTrans()
    {
        $this->connection->startTrans();
    }

$query->commit()

提交事务

public function commit()
    {
        $this->connection->commit();
    }

$query->transaction()

执行事务

public function transaction($callback)
   {
       return $this->connection->transaction($callback);
   }

$query->rollback()

回滚事务

public function rollback()
    {
        $this->connection->rollback();
    }

2-3 sql语句操作

$query->query()

调用连接器的静态query方法

public function query($sql, $bind = [], $master = false, $class = false)
    {
        return $this->connection->query($sql, $bind, $master, $class);
    }

$query->execute()

调用连接器的静态query方法

public function execute($sql, $bind = [])
    {
        return $this->connection->execute($sql, $bind);
    }

2-4 聚合操作

count()

public function count($field = '*')
    {
        return $this->value('COUNT(' . $field . ') AS tp_count', 0);
    }

max()

public function max($field = '*')
    {
        return $this->value('MAX(' . $field . ') AS tp_max', 0);
    }

min()

public function min($field = '*')
    {
        return $this->value('MIN(' . $field . ') AS tp_min', 0);
    }

avg()

public function avg($field = '*')
    {
        return $this->value('AVG(' . $field . ') AS tp_avg', 0);
    }

sum()

public function sum($field = '*')
    {
        return $this->value('SUM(' . $field . ') AS tp_sum', 0);
    }

$query->value()

public function value($field, $default = null)
    {
        $result = null;
        if (!empty($this->options['cache'])) {
            // 判断查询缓存
            $cache = $this->options['cache'];
            if (empty($this->options['table'])) {
                $this->options['table'] = $this->getTable();
            }
            $key    = is_string($cache['key']) ? $cache['key'] : md5($field . serialize($this->options));
            $result = Cache::get($key);
        }
        if (!$result) {
            if (isset($this->options['field'])) {
                unset($this->options['field']);
            }
            $pdo = $this->field($field)->fetchPdo(true)->find();
            if (is_string($pdo)) {
                // 返回SQL语句
                return $pdo;
            }
            $result = $pdo->fetchColumn();
            if (isset($cache)) {
                // 缓存数据
                if (isset($cache['tag'])) {
                    Cache::tag($cache['tag'])->set($key, $result, $cache['expire']);
                } else {
                    Cache::set($key, $result, $cache['expire']);
                }
            }
        } else {
            // 清空查询条件
            $this->options = [];
        }
        return !is_null($result) ? $result : $default;
    }

$query->column()

public function column($field, $key = '')
    {
        $result = false;
        if (!empty($this->options['cache'])) {
            // 判断查询缓存
            $cache = $this->options['cache'];
            if (empty($this->options['table'])) {
                $this->options['table'] = $this->getTable();
            }
            $guid   = is_string($cache['key']) ? $cache['key'] : md5($field . serialize($this->options));
            $result = Cache::get($guid);
        }
        if (!$result) {
            if (isset($this->options['field'])) {
                unset($this->options['field']);
            }
            if ($key && '*' != $field) {
                $field = $key . ',' . $field;
            }
            $pdo = $this->field($field)->fetchPdo(true)->select();
            if (is_string($pdo)) {
                // 返回SQL语句
                return $pdo;
            }
            if (1 == $pdo->columnCount()) {
                $result = $pdo->fetchAll(PDO::FETCH_COLUMN);
            } else {
                $resultSet = $pdo->fetchAll(PDO::FETCH_ASSOC);
                if ($resultSet) {
                    $fields = array_keys($resultSet[0]);
                    $count  = count($fields);
                    $key1   = array_shift($fields);
                    $key2   = $fields ? array_shift($fields) : '';
                    $key    = $key ?: $key1;
                    foreach ($resultSet as $val) {
                        if ($count > 2) {
                            $result[$val[$key]] = $val;
                        } elseif (2 == $count) {
                            $result[$val[$key]] = $val[$key2];
                        } elseif (1 == $count) {
                            $result[$val[$key]] = $val[$key1];
                        }
                    }
                } else {
                    $result = [];
                }
            }
            if (isset($cache) && isset($guid)) {
                // 缓存数据
                if (isset($cache['tag'])) {
                    Cache::tag($cache['tag'])->set($guid, $result, $cache['expire']);
                } else {
                    Cache::set($guid, $result, $cache['expire']);
                }
            }
        } else {
            // 清空查询条件
            $this->options = [];
        }
        return $result;
    }

2-5 链式操作

table

public function table($table)
    {
        $this->options['table'] = $table;
        return $this;
    }

where

public function where($field, $op = null, $condition = null)
    {
        $param = func_get_args();
        array_shift($param);
        $this->parseWhereExp('AND', $field, $op, $condition, $param);
        return $this;
    }

whereOr

public function whereOr($field, $op = null, $condition = null)
    {
        $param = func_get_args();
        array_shift($param);
        $this->parseWhereExp('OR', $field, $op, $condition, $param);
        return $this;
    }

whereXor

public function whereXor($field, $op = null, $condition = null)
    {
        $param = func_get_args();
        array_shift($param);
        $this->parseWhereExp('XOR', $field, $op, $condition, $param);
        return $this;
    }

whereTime()

public function whereTime($field, $op, $range = null)
    {
        if (is_null($range)) {
            // 使用日期表达式
            $date = getdate();
            switch (strtolower($op)) {
                case 'today':
                case 'd':
                    $range = 'today';
                    break;
                case 'week':
                case 'w':
                    $range = 'this week 00:00:00';
                    break;
                case 'month':
                case 'm':
                    $range = mktime(0, 0, 0, $date['mon'], 1, $date['year']);
                    break;
                case 'year':
                case 'y':
                    $range = mktime(0, 0, 0, 1, 1, $date['year']);
                    break;
                case 'yesterday':
                    $range = ['yesterday', 'today'];
                    break;
                case 'last week':
                    $range = ['last week 00:00:00', 'this week 00:00:00'];
                    break;
                case 'last month':
                    $range = [date('y-m-01', strtotime('-1 month')), mktime(0, 0, 0, $date['mon'], 1, $date['year'])];
                    break;
                case 'last year':
                    $range = [mktime(0, 0, 0, 1, 1, $date['year'] - 1), mktime(0, 0, 0, 1, 1, $date['year'])];
                    break;
            }
            $op = is_array($range) ? 'between' : '>';
        }
        $this->where($field, strtolower($op) . ' time', $range);
        return $this;
    }

field()

public function field($field, $except = false, $tableName = '', $prefix = '', $alias = '')
    {
        if (empty($field)) {
            return $this;
        }
        if (is_string($field)) {
            $field = array_map('trim', explode(',', $field));
        }
        if (true === $field) {
            // 获取全部字段
            $fields = !empty($this->allowField) && ('' == $tableName || $this->getTable() == $tableName) ? $this->allowField : $this->getTableInfo($tableName ?: (isset($this->options['table']) ? $this->options['table'] : ''), 'fields');
            $field  = $fields ?: ['*'];
        } elseif ($except) {
            // 字段排除
            $fields = !empty($this->allowField) && ('' == $tableName || $this->getTable() == $tableName) ? $this->allowField : $this->getTableInfo($tableName ?: (isset($this->options['table']) ? $this->options['table'] : ''), 'fields');
            $field  = $fields ? array_diff($fields, $field) : $field;
        }
        if ($tableName) {
            // 添加统一的前缀
            $prefix = $prefix ?: $tableName;
            foreach ($field as $key => $val) {
                if (is_numeric($key)) {
                    $val = $prefix . '.' . $val . ($alias ? ' AS ' . $alias . $val : '');
                }
                $field[$key] = $val;
            }
        }

        if (isset($this->options['field'])) {
            $field = array_merge($this->options['field'], $field);
        }
        $this->options['field'] = array_unique($field);
        return $this;
    }

limit()

public function limit($offset, $length = null)
    {
        if (is_null($length) && strpos($offset, ',')) {
            list($offset, $length) = explode(',', $offset);
        }
        $this->options['limit'] = intval($offset) . ($length ? ',' . intval($length) : '');
        return $this;
    }

order()

public function order($field, $order = null)
    {
        if (!empty($field)) {
            if (is_string($field)) {
                if (!empty($this->options['via'])) {
                    $field = $this->options['via'] . '.' . $field;
                }
                $field = empty($order) ? $field : [$field => $order];
            } elseif (!empty($this->options['via'])) {
                foreach ($field as $key => $val) {
                    if (is_numeric($key)) {
                        $field[$key] = $this->options['via'] . '.' . $val;
                    } else {
                        $field[$this->options['via'] . '.' . $key] = $val;
                        unset($field[$key]);
                    }
                }
            }
            $this->options['order'] = $field;
        }
        return $this;
    }

group()

public function group($group)
    {
        $this->options['group'] = $group;
        return $this;
    }

having()

public function having($having)
    {
        $this->options['having'] = $having;
        return $this;
    }

join()

public function join($join, $condition = null, $type = 'INNER')
    {
        if (empty($condition)) {
            // 如果为组数，则循环调用join
            foreach ($join as $key => $value) {
                if (is_array($value) && 2 <= count($value)) {
                    $this->join($value[0], $value[1], isset($value[2]) ? $value[2] : $type);
                }
            }
        } else {
            $prefix = $this->prefix;
            // 传入的表名为数组
            if (is_array($join)) {
                if (0 !== $key = key($join)) {
                    // 设置了键名则键名为表名，键值作为表的别名
                    $table = $key . ' ' . array_shift($join);
                } else {
                    $table = array_shift($join);
                }
                if (count($join)) {
                    // 有设置第二个元素则把第二元素作为表前缀
                    $table = (string) current($join) . $table;
                } elseif (false === strpos($table, '.')) {
                    // 加上默认的表前缀
                    $table = $prefix . $table;
                }
            } else {
                $join = trim($join);
                if (0 === strpos($join, '__')) {
                    $table = $this->parseSqlTable($join);
                } elseif (false === strpos($join, '(') && false === strpos($join, '.') && !empty($prefix) && 0 !== strpos($join, $prefix)) {
                    // 传入的表名中不带有'('并且不以默认的表前缀开头时加上默认的表前缀
                    $table = $prefix . $join;
                } else {
                    $table = $join;
                }
            }
            if (is_array($condition)) {
                $condition = implode(' AND ', $condition);
            }
            $this->options['join'][] = strtoupper($type) . ' JOIN ' . $table . ' ON ' . $condition;
        }
        return $this;
    }

union()

public function union($union, $all = false)
    {
        $this->options['union']['type'] = $all ? 'UNION ALL' : 'UNION';

        if (is_array($union)) {
            $this->options['union'] = array_merge($this->options['union'], $union);
        } else {
            $this->options['union'][] = $union;
        }
        return $this;
    }

distinct()

public function distinct($distinct)
    {
        $this->options['distinct'] = $distinct;
        return $this;
    }

lock()

public function lock($lock = false)
    {
        $this->options['lock']   = $lock;
        $this->options['master'] = true;
        return $this;
    }

cache()

public function cache($key = true, $expire = null, $tag = null)
    {
        // 增加快捷调用方式 cache(10) 等同于 cache(true, 10)
        if (is_numeric($key) && is_null($expire)) {
            $expire = $key;
            $key    = true;
        }
        if (false !== $key) {
            $this->options['cache'] = ['key' => $key, 'expire' => $expire, 'tag' => $tag];
        }
        return $this;
    }

force()

public function force($force)
    {
        $this->options['force'] = $force;
        return $this;
    }

comment()

public function comment($comment)
    {
        $this->options['comment'] = $comment;
        return $this;
    }

alais()

public function alias($alias)
    {
        $this->options['alias'] = $alias;
        return $this;
    }

fetchSql()

public function fetchSql($fetch = true)
    {
        $this->options['fetch_sql'] = $fetch;
        return $this;
    }

fetchPdo()

public function fetchPdo($pdo = true)
    {
        $this->options['fetch_class'] = $pdo;
        return $this;
    }

fetchClass()

public function fetchClass($class)
    {
        $this->options['fetch_class'] = $class;
        return $this;
    }

with()

public function with($with)
    {
        if (empty($with)) {
            return $this;
        }

        if (is_string($with)) {
            $with = explode(',', $with);
        }

        $i            = 0;
        $currentModel = $this->model;

        /** @var Model $class */
        $class = new $currentModel;
        foreach ($with as $key => $relation) {
            $closure = false;
            if ($relation instanceof \Closure) {
                // 支持闭包查询过滤关联条件
                $closure    = $relation;
                $relation   = $key;
                $with[$key] = $key;
            } elseif (is_string($relation) && strpos($relation, '.')) {
                $with[$key]                   = $relation;
                list($relation, $subRelation) = explode('.', $relation, 2);
            }

            /** @var Relation $model */
            $model = $class->$relation();
            $info  = $model->getRelationInfo();
            if (in_array($info['type'], [Relation::HAS_ONE, Relation::BELONGS_TO])) {
                if (0 == $i) {
                    $name  = Loader::parseName(basename(str_replace('\\', '/', $currentModel)));
                    $table = $this->getTable();
                    $alias = isset($info['alias'][$name]) ? $info['alias'][$name] : $name;
                    $this->table($table)->alias($alias);
                    if (isset($this->options['field'])) {
                        $field = $this->options['field'];
                        unset($this->options['field']);
                    } else {
                        $field = true;
                    }
                    $this->field($field, false, $table, $alias);
                }
                // 预载入封装
                $joinTable = $model->getTable();
                $joinName  = Loader::parseName(basename(str_replace('\\', '/', $info['model'])));
                $joinAlias = isset($info['alias'][$joinName]) ? $info['alias'][$joinName] : $joinName;
                $this->via($joinAlias);

                if (Relation::HAS_ONE == $info['type']) {
                    $this->join($joinTable . ' ' . $joinAlias, $alias . '.' . $info['localKey'] . '=' . $joinAlias . '.' . $info['foreignKey'], $info['joinType']);
                } else {
                    $this->join($joinTable . ' ' . $joinAlias, $alias . '.' . $info['foreignKey'] . '=' . $joinAlias . '.' . $info['localKey'], $info['joinType']);
                }

                if ($closure) {
                    // 执行闭包查询
                    call_user_func_array($closure, [ & $this]);
                    //指定获取关联的字段
                    //需要在 回调中 调方法 withField 方法，如
                    // $query->where(['id'=>1])->withField('id,name');
                    if (!empty($this->options['with_field'])) {
                        $field = $this->options['with_field'];
                        unset($this->options['with_field']);
                    }
                }
                $this->field($field, false, $joinTable, $joinAlias, $relation . '__');
                $i++;
            } elseif ($closure) {
                $with[$key] = $closure;
            }
        }
        $this->via();
        $this->options['with'] = $with;
        return $this;
    }

withField()

public function withField($field)
    {
        $this->options['with_field'] = $field;
        return $this;
    }

via()

 public function via($via = '')
    {
        $this->options['via'] = $via;
        return $this;
    }

relation()

public function relation($relation)
    {
        $this->options['relation'] = $relation;
        return $this;
    }

3 数据表操作

1 数据表信息

$query->name()

public function name($name)
    {
        $this->name = $name;
        return $this;
    }

$query->setTable()

public function setTable($table)
    {
        $this->table = $table;
        return $this;
    }

$query->getTable()

public function getTable($name = '')
    {
        if ($name || empty($this->table)) {
            $name      = $name ?: $this->name;
            $tableName = $this->prefix;
            if ($name) {
                $tableName .= Loader::parseName($name);
            }
        } else {
            $tableName = $this->table;
        }
        return $tableName;
    }

$query->getTableInfo()

public function getTableInfo($tableName = '', $fetch = '')
    {
        if (!$tableName) {
            $tableName = $this->getTable();
        }
        if (is_array($tableName)) {
            $tableName = key($tableName) ?: current($tableName);
        }

        if (strpos($tableName, ',')) {
            // 多表不获取字段信息
            return false;
        } else {
            $tableName = $this->parseSqlTable($tableName);
        }

        list($guid) = explode(' ', $tableName);
        if (!isset(self::$info[$guid])) {
            $info   = $this->connection->getFields($tableName);
            $fields = array_keys($info);
            $bind   = $type   = [];
            foreach ($info as $key => $val) {
                // 记录字段类型
                $type[$key] = $val['type'];
                $bind[$key] = $this->getFieldBindType($val['type']);
                if (!empty($val['primary'])) {
                    $pk[] = $key;
                }
            }
            if (isset($pk)) {
                // 设置主键
                $pk = count($pk) > 1 ? $pk : $pk[0];
            } else {
                $pk = null;
            }
            self::$info[$guid] = ['fields' => $fields, 'type' => $type, 'bind' => $bind, 'pk' => $pk];
        }
        return $fetch ? self::$info[$guid][$fetch] : self::$info[$guid];
    }

$query->getpk()

public function getPk($options = '')
    {
        if (!empty($this->pk)) {
            $pk = $this->pk;
        } else {
            $pk = $this->getTableInfo(is_array($options) ? $options['table'] : $options, 'pk');
        }
        return $pk;
    }

2 数据表字段信息

$query->getTableFields()

 public function getTableFields($options)
    {
        return !empty($this->allowField) ? $this->allowField : $this->getTableInfo($options['table'], 'fields');
    }

$query->getFieldsType()

public function getFieldsType($options)
    {
        return !empty($this->fieldType) ? $this->fieldType : $this->getTableInfo($options['table'], 'type');
    }

3 数据表绑定信息

$query->getFieldBind()

public function getFieldsBind($options)
    {
        $types = $this->getFieldsType($options);
        $bind  = [];
        if ($types) {
            foreach ($types as $key => $type) {
                $bind[$key] = $this->getFieldBindType($type);
            }
        }
        return $bind;
    }

$query->getFieldBindType()

protected function getFieldBindType($type)
    {
        if (preg_match('/(int|double|float|decimal|real|numeric|serial)/is', $type)) {
            $bind = PDO::PARAM_INT;
        } elseif (preg_match('/bool/is', $type)) {
            $bind = PDO::PARAM_BOOL;
        } else {
            $bind = PDO::PARAM_STR;
        }
        return $bind;
    }

4 sql解析合成语句操作

1 数据表解析

$query->parseSqlTable()

public function parseSqlTable($sql)
    {
        if (false !== strpos($sql, '__')) {
            $prefix = $this->prefix;
            $sql    = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function ($match) use ($prefix) {
                return $prefix . strtolower($match[1]);
            }, $sql);
        }
        return $sql;
    }

2 where语句解析

$query->parseWhereExp()

protected function parseWhereExp($logic, $field, $op, $condition, $param = [])
    {
        if ($field instanceof \Closure) {
            $this->options['where'][$logic][] = is_string($op) ? [$op, $field] : $field;
            return;
        }

        if (is_string($field) && !empty($this->options['via']) && !strpos($field, '.')) {
            $field = $this->options['via'] . '.' . $field;
        }
        if (is_string($field) && preg_match('/[,=\>\<\'\"\(\s]/', $field)) {
            $where[] = ['exp', $field];
            if (is_array($op)) {
                // 参数绑定
                $this->bind($op);
            }
        } elseif (is_null($op) && is_null($condition)) {
            if (is_array($field)) {
                // 数组批量查询
                $where = $field;
            } elseif ($field) {
                // 字符串查询
                if (is_numeric($field)) {
                    $where[] = ['exp', $field];
                } else {
                    $where[$field] = ['null', ''];
                }
            }
        } elseif (is_array($op)) {
            $where[$field] = $param;
        } elseif (in_array(strtolower($op), ['null', 'notnull', 'not null'])) {
            // null查询
            $where[$field] = [$op, ''];
        } elseif (is_null($condition)) {
            // 字段相等查询
            $where[$field] = ['eq', $op];
        } else {
            $where[$field] = [$op, $condition];
        }
        if (!empty($where)) {
            if (!isset($this->options['where'][$logic])) {
                $this->options['where'][$logic] = [];
            }
            $this->options['where'][$logic] = array_merge($this->options['where'][$logic], $where);
        }
    }

$query->parsePkWhere()

protected function parsePkWhere($data, &$options)
    {
        $pk = $this->getPk($options);
        // 获取当前数据表
        if (!empty($options['alias'])) {
            $alias = $options['alias'];
        }
        if (is_string($pk)) {
            $key = isset($alias) ? $alias . '.' . $pk : $pk;
            // 根据主键查询
            if (is_array($data)) {
                $where[$key] = isset($data[$pk]) ? $data[$pk] : ['in', $data];
            } else {
                $where[$key] = strpos($data, ',') ? ['IN', $data] : $data;
            }
        } elseif (is_array($pk) && is_array($data) && !empty($data)) {
            // 根据复合主键查询
            foreach ($pk as $key) {
                if (isset($data[$key])) {
                    $attr         = isset($alias) ? $alias . '.' . $key : $key;
                    $where[$attr] = $data[$key];
                } else {
                    throw new Exception('miss complex primary data');
                }
            }
        }

        if (!empty($where)) {
            if (isset($options['where']['AND'])) {
                $options['where']['AND'] = array_merge($options['where']['AND'], $where);
            } else {
                $options['where']['AND'] = $where;
            }
        }
        return;
    }

3 表达式解析

$query->parseExpress()

protected function parseExpress()
    {
        $options = $this->options;

        // 获取数据表
        if (empty($options['table'])) {
            $options['table'] = $this->getTable();
        }

        if (!isset($options['where'])) {
            $options['where'] = [];
        } elseif (isset($options['view'])) {
            // 视图查询条件处理
            foreach (['AND', 'OR'] as $logic) {
                if (isset($options['where'][$logic])) {
                    foreach ($options['where'][$logic] as $key => $val) {
                        if (array_key_exists($key, $options['map'])) {
                            $options['where'][$logic][$options['map'][$key]] = $val;
                            unset($options['where'][$logic][$key]);
                        }
                    }
                }
            }

            if (isset($options['order'])) {
                // 视图查询排序处理
                if (is_string($options['order'])) {
                    $options['order'] = explode(',', $options['order']);
                }
                foreach ($options['order'] as $key => $val) {
                    if (is_numeric($key)) {
                        if (strpos($val, ' ')) {
                            list($field, $sort) = explode(' ', $val);
                            if (array_key_exists($field, $options['map'])) {
                                $options['order'][$options['map'][$field]] = $sort;
                                unset($options['order'][$key]);
                            }
                        } elseif (array_key_exists($val, $options['map'])) {
                            $options['order'][$options['map'][$val]] = 'asc';
                            unset($options['order'][$key]);
                        }
                    } elseif (array_key_exists($key, $options['map'])) {
                        $options['order'][$options['map'][$key]] = $val;
                        unset($options['order'][$key]);
                    }
                }
            }
        }

        // 表别名
        if (!empty($options['alias'])) {
            $options['table'] .= ' ' . $options['alias'];
        }

        if (!isset($options['field'])) {
            $options['field'] = '*';
        }

        if (!isset($options['strict'])) {
            $options['strict'] = $this->getConfig('fields_strict');
        }

        foreach (['master', 'lock', 'fetch_class', 'fetch_sql', 'distinct'] as $name) {
            if (!isset($options[$name])) {
                $options[$name] = false;
            }
        }

        foreach (['join', 'union', 'group', 'having', 'limit', 'order', 'force', 'comment'] as $name) {
            if (!isset($options[$name])) {
                $options[$name] = '';
            }
        }

        if (isset($options['page'])) {
            // 根据页数计算limit
            list($page, $listRows) = $options['page'];
            $page                  = $page > 0 ? $page : 1;
            $listRows              = $listRows > 0 ? $listRows : (is_numeric($options['limit']) ? $options['limit'] : 20);
            $offset                = $listRows * ($page - 1);
            $options['limit']      = $offset . ',' . $listRows;
        }

        $this->options = [];
        return $options;
    }

5 查询构造器

查询对象可以调用查询构造合成SQL语句
查询构造器的实验见 查询构造

查询构造器创建

查询构造器，针对不同数据库驱动拼接SQL语句时需要创建不同的查询构造器

$query->builder()

创建针对不同数据库驱动的查询构造器。

protected function builder()
    {
        static $builder = [];
        $driver         = $this->builder;
        if (!isset($builder[$driver])) {
            $class            = false !== strpos($driver, '\\') ? $driver : '\\think\\db\\builder\\' . ucfirst($driver);
            $builder[$driver] = new $class($this->connection);
        }
        // 设置当前查询对象
        $builder[$driver]->setQuery($this);
        return $builder[$driver];
    }

查询构造器使用

在数据库操作中调用查询构造器拼接SQL语句

;insert中调用$query->builder()->insert()合成insert语句

public function insert(array $data, $replace = false, $getLastInsID = false, $sequence = null)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        // 生成SQL语句
        $sql = $this->builder()->insert($data, $options, $replace);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        }

        // 执行操作
        $result = $this->execute($sql, $bind);
        if ($getLastInsID) {
            $sequence = $sequence ?: (isset($options['sequence']) ? $options['sequence'] : null);
            return $this->getLastInsID($sequence);
        }
        return $result;
    }

； update片段中调用$query->builder()->update()合成update语句

public function update(array $data)
    {
        $options = $this->parseExpress();
        $pk      = $this->getPk($options);
        if (isset($options['cache']) && is_string($options['cache'])) {
            $key = $options['cache'];
        }

        if (empty($options['where'])) {
            // 如果存在主键数据 则自动作为更新条件
            if (is_string($pk) && isset($data[$pk])) {
                $where[$pk] = $data[$pk];
                if (!isset($key)) {
                    $key = 'think:' . $options['table'] . '|' . $data[$pk];
                }
                unset($data[$pk]);
            } elseif (is_array($pk)) {
                // 增加复合主键支持
                foreach ($pk as $field) {
                    if (isset($data[$field])) {
                        $where[$field] = $data[$field];
                    } else {
                        // 如果缺少复合主键数据则不执行
                        throw new Exception('miss complex primary data');
                    }
                    unset($data[$field]);
                }
            }
            if (!isset($where)) {
                // 如果没有任何更新条件则不执行
                throw new Exception('miss update condition');
            } else {
                $options['where']['AND'] = $where;
            }
        } elseif (is_string($pk) && isset($options['where']['AND'][$pk]) && is_scalar($options['where']['AND'][$pk])) {
            $key = 'think:' . $options['table'] . '|' . $options['where']['AND'][$pk];
        }
        // 生成UPDATE SQL语句
        $sql = $this->builder()->update($data, $options);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        } else {
            // 检测缓存
            if (isset($key) && Cache::get($key)) {
                // 删除缓存
                Cache::rm($key);
            }
            // 执行操作
            return '' == $sql ? 0 : $this->execute($sql, $bind);
        }
    }