说明：本文主要学习下Query Builder编译Fluent Api为SQL的细节和执行SQL的过程。实际上，上一篇聊到了\Illuminate\Database\Query\Builder这个非常重要的类，这个类含有三个主要的武器：MySqlConnection, MySqlGrammar, MySqlProcessor。MySqlConnection主要就是在执行SQL时做连接MySql数据库操作，MySqlProcessor主要就是用来对执行SQL后的数据集做后置处理操作，这两点已经在之前上篇聊过，那MySqlGrammar就是SQL语法编译器，用来编译Fluent Api为SQL。最后使用MySqlConnection::select($sql, $bindings)执行SQL。

开发环境：Laravel5.3 + PHP7

Builder::toSql()

看下toSql()的源码：

public function toSql()    {        // $this->grammar = new MySqlGrammar        return $this->grammar->compileSelect($this);    }        public function compileSelect(Builder $query)    {        $sql = parent::compileSelect($query);        // 从上一篇文章知道，$unions属性没有存储值，$wheres属性是有值的        if ($query->unions) {            $sql = '('.$sql.') '.$this->compileUnions($query);        }        return $sql;    }

这里首先会调用Illuminate\Database\Query\GrammarsGrammar::compileSelect(Builder $query)，看下compileSelect(Builder $query)的源码：

public function compileSelect(Builder $query)    {        // $original = ['*']        $original = $query->columns;        if (is_null($query->columns)) {            $query->columns = ['*'];        }        $sql = trim($this->concatenate($this->compileComponents($query)));        $query->columns = $original;        // $sql = 'select * from users where id = ?'        return $sql;    }        protected $selectComponents = [        'aggregate',        'columns',        'from',        'joins',        'wheres',        'groups',        'havings',        'orders',        'limit',        'offset',        'lock',    ];        protected function compileComponents(Builder $query)    {        $sql = [];        foreach ($this->selectComponents as $component) {            //             if (! is_null($query->$component)) {                $method = 'compile'.ucfirst($component);                // 1. compileColumns($builder, ['*']) -> 'select ' . $this->columnize(['*'])                // 2. compileFrom($builder, 'users'); -> 'from '.$this->wrapTable('users')                // 3. compileWheres($builder, [ 0 => ['type' => 'basic', 'column' => 'id', 'operator' => '=', 'value' => 1, 'boolean' => 'and'], ])                // $sql = ['columns' => 'select *', 'from' => 'from users', 'wheres' => 'where id = ?']                $sql[$component] = $this->$method($query, $query->$component);            }        }        return $sql;    }

从上文源码中可知道，首先依次遍历片段集合:aggregate,columns,from,joins,wheres,groups,havings,orders,limit,offset,lock，查看属性有无存储值。在上文中知道，在片段$columns,from,wheres存有值为['*'], 'users', [['type' => 'basic', 'column' => 'id', 'operator' => '=', 'value' => 1, 'boolean' => 'and']]，然后通过拼接字符串调用方法compileColumns($builder, ['*']), compileFrom($builder, 'users'), compileWheres($builder, array)，依次看下这些方法的源码：

protected function compileColumns(Builder $query, $columns)    {           if (! is_null($query->aggregate)) {            return;        }        // $select = 'select '        $select = $query->distinct ? 'select distinct ' : 'select ';        return $select.$this->columnize($columns);    }        // Illuminate/Database/Grammar    public function columnize(array $columns)    {        // 依次经过wrap()函数封装下        return implode(', ', array_map([$this, 'wrap'], $columns));    }        public function wrap($value, $prefixAlias = false)    {        if ($this->isExpression($value)) {            return $this->getValue($value);        }        if (strpos(strtolower($value), ' as ') !== false) {            $segments = explode(' ', $value);            if ($prefixAlias) {                $segments[2] = $this->tablePrefix.$segments[2];            }            return $this->wrap($segments[0]).' as '.$this->wrapValue($segments[2]);        }        $wrapped = [];        $segments = explode('.', $value);        // $segments = ['*']        foreach ($segments as $key => $segment) {            if ($key == 0 && count($segments) > 1) {                $wrapped[] = $this->wrapTable($segment);            } else {                // $wrapped = ['*']                $wrapped[] = $this->wrapValue($segment);            }        }        return implode('.', $wrapped);    }        protected function wrapValue($value)    {        if ($value === '*') {            return $value;        }        return '"'.str_replace('"', '""', $value).'"';    }

通过源码很容易知道compileColumns($builder, ['*'])返回值select "*"，然后将该值以key-value形式存储在$sql变量中，这时$sql = ['columns' => 'select "*"']。

protected function compileFrom(Builder $query, $table)    {        return 'from '.$this->wrapTable($table);    }        // Illuminate/Database/Grammar    public function wrapTable($table)    {        if ($this->isExpression($table)) {            return $this->getValue($table);        }                // 返回"users"        return $this->wrap($this->tablePrefix.$table, true);    }

很容易知道返回值是from "users"，然后将该值存储在$sql变量中，这时$sql = ['columns' => 'select "*"', 'from' => 'from "users"']。OK,看下compileWheres($builder, array)的源码：

protected function compileWheres(Builder $query)    {        $sql = [];        if (is_null($query->wheres)) {            return '';        }        foreach ($query->wheres as $where) {            $method = "where{$where['type']}"; // 'whereBasic'            // 'and ' . $this->whereBasic($builder, ['type' => 'basic', 'column' => 'id', 'operator' => '=', 'value' => 1, 'boolean' => 'and']            // -> $sql = ['and id = ?', ];            $sql[] = $where['boolean'].' '.$this->$method($query, $where);        }                if (count($sql) > 0) {            $sql = implode(' ', $sql);            // $conjunction = 'where'            $conjunction = $query instanceof JoinClause ? 'on' : 'where';            // 去除掉'and'字符后为'where id = ?'            return $conjunction.' '.$this->removeLeadingBoolean($sql);        }        return '';    }        protected function whereBasic(Builder $query, $where)    {        // $value = '?'        $value = $this->parameter($where['value']);        // 返回'id = ?'        return $this->wrap($where['column']).' '.$where['operator'].' '.$value;    }

从源码中可知道返回值为where id = ?，这时$sql = ['columns' => 'select "*"', 'from' => 'from "users"', 'wheres' => 'where id = ?']。

OK, 最后通过concatenate()函数把$sql值拼接成字符串select "*" from "users" where id = ?：

protected function concatenate($segments)    {        return implode(' ', array_filter($segments, function ($value) {            return (string) $value !== '';        }));    }

也就是说，通过SQL语法编译器MySqlGrammar把table('users')->where('id', '=', 1)编译成了SQL语句select * from users where id = ?。

MySqlConnection::select()

上文聊到Builder::runSelect()调用了三个方法：MySqlConnection::select(), Builder::toSql(), Builder::getBindings()，其中Builder::toSql()通过SQL语法编译器已经编译得到了SQL语句，Builder::getBindings()获取存储在$bindings[ ]的值。最后看下MySqlConnection::select()是如何执行SQL语句的：

public function select($query, $bindings = [], $useReadPdo = true)    {        // Closure就是用来执行SQL,并把$query = 'select * from users where id =?', $bindings = 1作为参数传递进去        return $this->run($query, $bindings, function (Connection $me, $query, $bindings) use ($useReadPdo) {            if ($me->pretending()) {                return [];            }            // $statement = PDO::prepare('select * from users where id =?')            /** @var \PDOStatement $statement */            $statement = $this->getPdoForSelect($useReadPdo)->prepare($query);            $me->bindValues($statement, $me->prepareBindings($bindings));            //PDO三步走: SQL编译prepare() => 值绑定bindValue() => SQL执行execute()            // PDO通过这种方式防止SQL注入            $statement->execute();            $fetchMode = $me->getFetchMode();            $fetchArgument = $me->getFetchArgument();            $fetchConstructorArgument = $me->getFetchConstructorArgument();            if ($fetchMode === PDO::FETCH_CLASS && ! isset($fetchArgument)) {                $fetchArgument = 'StdClass';                $fetchConstructorArgument = null;            }            // PDOStatement::fetchAll(PDO::FETCH_OBJ);            return isset($fetchArgument)                ? $statement->fetchAll($fetchMode, $fetchArgument, $fetchConstructorArgument)                : $statement->fetchAll($fetchMode);        });    }        protected function run($query, $bindings, Closure $callback)    {        $this->reconnectIfMissingConnection();        $start = microtime(true);        try {            // 执行闭包函数            $result = $this->runQueryCallback($query, $bindings, $callback);        } catch (QueryException $e) {            if ($this->transactions >= 1) {                throw $e;            }            $result = $this->tryAgainIfCausedByLostConnection(                $e, $query, $bindings, $callback            );        }        $time = $this->getElapsedTime($start);        $this->logQuery($query, $bindings, $time);        return $result;    }        protected function runQueryCallback($query, $bindings, Closure $callback)    {        try {            // 执行闭包函数            $result = $callback($this, $query, $bindings);        }catch (Exception $e) {            throw new QueryException(                $query, $this->prepareBindings($bindings), $e            );        }        return $result;    }

通过源码知道主要是执行闭包来实现连接数据库和执行SQL操作，其中$statement = $this->getPdoForSelect($useReadPdo)->prepare($query)这句代码实现了数据库的连接操作和SQL语句送入MySQL服务器进行语句编译。上文中提前聊了通过数据库连接器MySqlConnector::connect()连接数据库，这里知道实际上连接数据库是在这个时刻才触发的，Laravel5.0版本好像还没有这么写：

protected function getPdoForSelect($useReadPdo = true)    {        return $useReadPdo ? $this->getReadPdo() : $this->getPdo();    }        public function getPdo()    {        if ($this->pdo instanceof Closure) {            // 连接数据库，获得PDO实例            return $this->pdo = call_user_func($this->pdo);        }        return $this->pdo;    }

通过源码知道执行SQL操作很简单，就是常见的PDO操作：PDO三步走: SQL编译PDO::prepare() => 值绑定PDOStatement::bindValue() => SQL执行PDOStatement::execute()。所以这里可看出Query Builder是在PHP PDO的基础上实现的一层封装，使得用更加面向对象的Fluent API来操作数据库，而不需要写一行SQL语句。

OK, 总的来说，通过了解Query Builder的实现原理后，知道其并不复杂或神秘，只是一个对PDO更友好封装的包裹，Query Builder有几个重要的类或概念：连接类MySqlConnection及其为其服务的连接器MySqlConnector;Builder 类;SQL语法解析器MySqlGrammar;后置处理器MySqlProcessor。

OK, illuminate/database package不仅提供了Query Builder，还提供了Eloquent ORM。那Eloquent ORM又是什么，与Query Builder是什么关系呢？既然有了Query Builder，为何还提供了Eloquent ORM呢？

总结：本文主要学习了Query Builder编译SQL细节和执行SQL逻辑。后续在分享下Eloquent ORM的实现原理，到时见。

招聘