数据库操作 —— 使用进阶：通过查询构建器实现高级功能

<h3 id="toc_0">简介

数据库查询构建器提供了一个方便的流接口用于创建和执行数据库查询。查询构建器可以用于执行应用中绝大部分数据库操作，并且能够在 Laravel 支持的所有数据库系统上工作。

注：流接口是一种设计模式，更多关于流接口模式的设计和使用方式，可查看这篇教程：。

Laravel 查询构建器使用 PDO 参数绑定来避免 SQL 注入攻击，不再需要过滤以绑定方式传递的字符串。

从一张表中取出所有行

我们可以从?DB?门面的?table?方法开始，table?方法为给定表返回一个流式查询构建器实例，该实例允许你在查询上链接多个约束条件并返回最终查询结果。在本例中，我们使用?get?方法获取表中所有记录：

UserController = DB::table('users')-> view('user.index',['users' => }

get?方法返回包含结果集的?IlluminateSupportCollection，其中每一个结果都是 PHP 的?StdClass?对象实例：

你可以像访问对象的属性一样访问字段的值：

( -> }

从一张表中获取一行/一列

如果你只是想要从数据表中获取一行数据，可以使用?first?方法，该方法将会返回单个?StdClass?对象：

= DB::table('users')->where('name','John')-> ->name;

如果你不需要完整的一行，可以使用?value?方法从结果中获取单个值，该方法会直接返回指定列的值：

= DB::table('users')->where('name','John')->value('email');

获取数据列值列表

如果想要获取包含单个列值的数组，可以使用?pluck?方法，在本例中，我们获取角色标题数组：

= DB::table('roles')->pluck('title'<span style="color: #0000ff">foreach (<span style="color: #800080">$titles <span style="color: #0000ff">as <span style="color: #800080">$title<span style="color: #000000">) {
<span style="color: #0000ff">echo <span style="color: #800080">$title<span style="color: #000000">;
}

还可以在返回数组中为列值指定自定义键（该自定义键必须是该表的其它字段列名，否则会报错）：

= DB::table('roles')->pluck('title','name'<span style="color: #0000ff">foreach (<span style="color: #800080">$roles <span style="color: #0000ff">as <span style="color: #800080">$name => <span style="color: #800080">$title<span style="color: #000000">) {
<span style="color: #0000ff">echo <span style="color: #800080">$title<span style="color: #000000">;
}

如果你需要处理成千上百条数据库记录，可以考虑使用?chunk?方法，该方法一次获取结果集的一小块，然后传递每一小块数据到闭包函数进行处理，该方法在编写处理大量数据库记录的 Artisan 命令的时候非常有用。例如，我们可以将处理全部?users?表数据分割成一次处理?100?条记录的小组块：

DB::table('users')->orderBy('id')->chunk(100,( (

你可以通过从闭包函数中返回?false?来终止组块的运行：

DB::table('users')->orderBy('id')->chunk(100,(

查询构建器还提供了多个聚合方法，如count,?max,?min,?avg?和?sum，你可以在构造查询之后调用这些方法：

= DB::table('users')-><span style="color: #800080">$price = DB::table('orders')-><span style="color: #008080">max('price');

当然，你可以联合其它查询子句和聚合函数来构建查询：

= DB::table('orders'->where('finalized',1->avg('price');

指定查询子句

当然，我们并不总是想要获取数据表的所有列，使用?select?方法，你可以为查询指定自定义的?select?子句：

= DB::table('users')->select('name','email as user_email')->get();

distinct?方法允许你强制查询返回不重复的结果集：

= DB::table('users')->distinct()->get();

如果你已经有了一个查询构建器实例并且希望添加一个查询列到已存在的 select 子句，可以使用?addSelect?方法：

= DB::table('users')->select('name' = ->addSelect('age')->get();

有时候你希望在查询中使用原生表达式，这些表达式将会以字符串的形式注入到查询中，所以要格外小心避免 SQL 注入。想要创建一个原生表达式，可以使用?DB::raw?方法：

= DB::table('users'->select(DB::raw('count(*) as user_count,status'->where('status','<>',1->groupBy('status'->get();

除了使用?DB::raw?外，你还可以使用以下方法来插入原生表达式到查询的不同部分。

selectRaw

selectRaw?方法可用于替代?select(DB::raw(...))，该方法接收一个可选的绑定数组作为第二个参数：

= DB::table('orders'->selectRaw('price * ? as price_with_tax',[1.0825->get();

whereRaw / orWhereRaw

whereRaw?和?orWhereRaw?方法可用于注入原生?where?子句到查询，这两个方法接收一个可选的绑定数组作为第二个参数：

= DB::table('orders'->whereRaw('price > IF(state = "TX",?,100)',[200->get();

havingRaw?和?orHavingRaw?方法可用于设置原生字符串作为?having?子句的值：

= DB::table('orders'->select('department',DB::raw('SUM(price) as total_sales'->groupBy('department'->havingRaw('SUM(price) > 2500'->get();

orderByRaw

orderByRaw?方法可用于设置原生字符串作为?order by?子句的值：

= DB::table('orders'->orderByRaw('updated_at - created_at DESC'->get();

查询构建器还可以用于编写连接语句，关于 SQL 的几种连接类型，通过下图可以一目了然：

内连接（等值连接）

要实现一个简单的"内连接"，你可以使用查询构建器实例上的?join?方法，传递给?join?方法的第一个参数是你需要连接到的表名，剩余的其它参数则是为连接指定的列约束，当然，正如你所看到的，你可以在单个查询中连接多张表：

= DB::table('users'->('contacts','users.id','=','contacts.user_id'->('orders','orders.user_id'->select('users.*','contacts.phone','orders.price'->get();

左连接

如果你是想要执行“左连接”而不是“内连接”，可以使用?leftJoin?方法。该方法和?join?方法的用法一样：

= DB::table('users'->leftJoin('posts','posts.user_id'->get();

交叉连接

要执行“交叉连接”可以使用?crossJoin?方法，传递你想要交叉连接的表名到该方法即可。交叉连接在第一张表和被连接表之间生成一个笛卡尔积：

= DB::table('sizes'->crossJoin('colours'->get();

高级连接语句

你还可以指定更多的高级连接子句，传递一个闭包到?join?方法作为第二个参数，该闭包将会接收一个?JoinClause对象用于指定?join?子句约束：

DB::table('users'->('contacts', (->on('users.id','contacts.user_id')->orOn(...->get();

如果你想要在连接中使用“where”风格的子句，可以在查询中使用?where?和orWhere?方法。这些方法会将列和值进行比较而不是列和列进行比较：

DB::table('users'->('contacts','contacts.user_id'->where('contacts.user_id','>',5->get();

查询构建器还提供了“联合”两个查询的快捷方式，比如，你可以先创建一个查询，然后使用?union?方法将其和第二个查询进行联合：

= DB::table('users'->whereNull('first_name'<span style="color: #800080">$users = DB::table('users'<span style="color: #000000">)
->whereNull('last_name'<span style="color: #000000">)
->union(<span style="color: #800080">$first<span style="color: #000000">)
->get();

注：unionAll?方法也是有效的，并且和?union?使用方式相同。

使用查询构建器上的?where?方法可以添加?where?子句到查询中，调用where?最基本的方式需要传递三个参数，第一个参数是列名，第二个参数是任意一个数据库系统支持的操作符，第三个参数是该列要比较的值。

例如，下面是一个验证“votes”列的值是否等于 100 的查询：

= DB::table('users')->where('votes',100)->get();

为了方便，如果你只是简单比较列值和给定数值是否相等，可以将数值直接作为where?方法的第二个参数：

= DB::table('users')->where('votes',100)->get();

当然，你还可以使用其它操作符来编写?where?子句：

= DB::table('users'->where('votes','>=',100-><span style="color: #800080">$users = DB::table('users'<span style="color: #000000">)
->where('votes',100<span style="color: #000000">)
-><span style="color: #000000">get();

<span style="color: #800080">$users = DB::table('users'<span style="color: #000000">)
->where('name','like','T%'<span style="color: #000000">)
->get();

还可以传递条件数组到?where?函数：

= DB::table('users')->'status','1'],'subscribed',->get();

你可以通过方法链将多个?where?约束链接到一起，也可以添加?or?子句到查询，orWhere?方法和?where?方法接收参数一样：

= DB::table('users'->where('votes',100->orWhere('name','John'->get();

whereBetween

whereBetween?方法验证列值是否在给定值之间：

= DB::table('users'->whereBetween('votes',[1,100])->get();

whereNotBetween

whereNotBetween?方法验证列值不在给定值之间：

= DB::table('users'->whereNotBetween('votes',100->get();

whereIn/whereNotIn

whereIn?方法验证给定列的值是否在给定数组中：

= DB::table('users'->whereIn('id',2,3->get();

whereNotIn?方法验证给定列的值不在给定数组中：

= DB::table('users'->whereNotIn('id',3->get();

whereNull/whereNotNull

whereNull?方法验证给定列的值为?NULL：

= DB::table('users'->whereNull('updated_at'->get();

whereNotNull?方法验证给定列的值不是?NULL：

= DB::table('users'->whereNotNull('updated_at'->get();

whereDate / whereMonth / whereDay / whereYear

whereDate?方法用于比较字段值和日期：

= DB::table('users'->whereDate('created_at','2016-10-10'->get();

whereMonth?方法用于比较字段值和一年中的指定月份：

= DB::table('users'->whereMonth('created_at','10'->get();

whereDay?方法用于比较字段值和一月中的指定日期：

= DB::table('users'->whereDay('created_at','10'->get();

whereYear?方法用于比较字段值和指定年：

= DB::table('users'->whereYear('created_at','2017'->get();

whereColumn

whereColumn?方法用于验证两个字段是否相等：

= DB::table('users'->whereColumn('first_name','last_name'->get();

还可以传递一个比较运算符到该方法：

= DB::table('users'->whereColumn('updated_at','created_at'->get();

还可以传递多条件数组到?whereColumn?方法，这些条件通过?and?操作符进行连接：

= DB::table('users'->'first_name','last_name'],'updated_at','created_at'->get();

有时候你需要创建更加高级的 where 子句，比如“where exists”或者嵌套的参数分组。Laravel 查询构建器也可以处理这些。作为开始，让我们看一个在括号中进行分组约束的例子：

DB::table('users'->where('name','John'->orWhere( (->where('votes',100->where('title','Admin'->get();

正如你所看到的，传递闭包到?orWhere?方法构造查询构建器来开始一个约束分组，该闭包将会获取一个用于设置括号中包含的约束的查询构建器实例。上述语句等价于下面的 SQL：

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

whereExists?方法允许你编写?where exists?SQL 子句，whereExists?方法接收一个闭包参数，该闭包获取一个查询构建器实例从而允许你定义放置在“exists”子句中的查询：

DB::table('users'->whereExists( (->select(DB::raw(1->from('orders'->whereRaw('orders.user_id = users.id'->get();

上述查询等价于下面的 SQL 语句：

select *1 from orders where orders.user_id = users.

Laravel 还支持在提供 JSON 字段类型的数据库（目前是 MySQL 5.7 和 PostgresSQL）上使用操作符?->?获取指定 JSON 字段值：

= DB::table('users'->where('options->language','en'-><span style="color: #800080">$users = DB::table('users'<span style="color: #000000">)
->where('preferences->dining->meal','salad'<span style="color: #000000">)
->get();

orderBy

orderBy?方法允许你通过给定字段对结果集进行排序，orderBy?的第一个参数应该是你希望排序的字段，第二个参数控制着排序的方向 ——?asc?或?desc：

= DB::table('users'->orderBy('name','desc'->get();

latest / oldest

latest?和?oldest?方法允许你通过日期对结果进行排序，默认情况下，结果集根据?created_at?字段进行排序，或者，你可以按照你想要排序的字段作为字段名传入：

= DB::table('users'->->first();

inRandomOrder

inRandomOrder?方法可用于对查询结果集进行随机排序，比如，你可以用该方法获取一个随机用户：

= DB::table('users'->->first();

groupBy / having

groupBy?和?having?方法用于对结果集进行分组，having?方法和?where?方法的用法类似：

= DB::table('users'->groupBy('account_id'->having('account_id',100->get();

关于?having?的更多高级用法，可查看?havingRaw?方法。

skip / take

想要限定查询返回的结果集的数目，或者在查询中跳过给定数目的结果，可以使用skip?和?take?方法：

= DB::table('users')->skip(10)->take(5)->get();

作为替代方法，还可以使用?limit?和?offset?方法：

= DB::table('users'->offset(10->limit(5->get();

有时候你可能想要某些条件为?true?的时候才将条件子句应用到查询。例如，你可能只想给定值在请求中存在的情况下才应用?where?语句，这可以通过?when?方法实现：

= ->input('role'<span style="color: #800080">$users = DB::table('users'<span style="color: #000000">)
->when(<span style="color: #800080">$role,<span style="color: #0000ff">function (<span style="color: #800080">$query) <span style="color: #0000ff">use (<span style="color: #800080">$role<span style="color: #000000">) {
<span style="color: #0000ff">return <span style="color: #800080">$query->where('role_id',<span style="color: #800080">$role<span style="color: #000000">);
})
->get();

when?方法只有在第一个参数为?true?的时候才执行给定闭包，如果第一个参数为?false，则闭包不执行。

你可以传递另一个闭包作为?when?方法的第三个参数，该闭包会在第一个参数为?false?的情况下执行。为了演示这个特性如何使用，我们来配置一个查询的默认排序：

= <span style="color: #800080">$users = DB::table('users'<span style="color: #000000">)
->when(<span style="color: #800080">$sortBy,<span style="color: #0000ff">function (<span style="color: #800080">$query) <span style="color: #0000ff">use (<span style="color: #800080">$sortBy<span style="color: #000000">) {
<span style="color: #0000ff">return <span style="color: #800080">$query->orderBy(<span style="color: #800080">$sortBy<span style="color: #000000">);
},<span style="color: #0000ff">function (<span style="color: #800080">$query<span style="color: #000000">) {
<span style="color: #0000ff">return <span style="color: #800080">$query->orderBy('name'<span style="color: #000000">);
})
->get();

查询构建器还提供了?insert?方法用于插入记录到数据表。insert?方法接收数组形式的字段名和字段值进行插入操作：

DB::table('users')->'email' => 'john@example.com','votes' => 0

你甚至可以一次性通过传入多个数组来插入多条记录，每个数组代表要插入数据表的记录：

DB::table('users')->'email' => 'taylor@example.com','votes' => 0],'email' => 'dayle@example.com','votes' => 0

自增ID

如果数据表有自增ID，使用?insertGetId?方法来插入记录并返回ID值：

= DB::table('users')->'email' => 'john@example.com','votes' => 0

注：当使用 PostgresSQL 时?insertGetId?方法默认自增列被命名为 id，如果你想要从其他“序列”获取ID，可以将序列名作为第二个参数传递到insertGetId?方法。

当然，除了插入记录到数据库，查询构建器还可以通过使用?update?方法更新已有记录。update?方法和?insert?方法一样，接收字段名和字段值的键值对数组，对应字段名就是要更新的列，你可以通过?where?子句来对?update?查询进行约束：

DB::table('users'->where('id',1->update(['votes' => 1]);

更新 JSON 字段的时候，需要使用?->?语法访问 JSON 对象上相应的值，该操作只能用于支持 JSON 字段类型的数据库：

DB::table('users'->where('id',1->update(['options->enabled' => ]);

查询构建器还为增减给定字段名对应数值提供方便。相较于编写?update?语句，这是一条捷径，提供了更好的体验和测试接口。

这两个方法都至少接收一个参数：需要修改的列。第二个参数是可选的，用于控制列值增加/减少的数目。

DB::table('users')->increment('votes'DB::table('users')->increment('votes',5<span style="color: #000000">);

DB::table('users')->decrement('votes'<span style="color: #000000">);

DB::table('users')->decrement('votes',5);

在操作过程中你还可以指定额外的列进行更新：

DB::table('users')->increment('votes',1,['name' => 'John']);

当然，查询构建器还可以通过?delete?方法从表中删除记录，你可以在调用?delete?方法前通过添加?where?子句来添加约束条件：

DB::table('users')->DB::table('users')->where('votes',100)->delete();

如果你希望清除整张表，也就是删除所有列并将自增 ID 置为 0，可以使用?truncate?方法：

DB::table('users')->truncate();

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在做操作之前先上锁。

乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于?write_condition?机制的其实都是提供的乐观锁。

下面我们看下悲观锁和乐观锁在 Laravel 中的使用：

Laravel 查询构建器提供了一些方法帮助你在?select?语句中实现“悲观锁”。可以在查询中使用?sharedLock?方法从而在运行语句时带一把”共享锁“。共享锁可以避免被选择的行被修改直到事务提交：

DB::table('users')->where('votes',100)->sharedLock()->get();

上面这个查询等价于下面这条 SQL 语句：

select * from `users` where `votes` > '100' lock in share mode

此外你还可以使用?lockForUpdate?方法。“for update”锁避免选择行被其它共享锁修改或删除：

DB::table('users')->where('votes',100)->lockForUpdate()->get();

上面这个查询等价于下面这条 SQL 语句：

select * from `users` where `votes` > '100' update

for update?与?lock in share mode?都是用于确保被选中的记录值不能被其它事务更新（上锁），两者的区别在于?lock in share mode?不会阻塞其它事务读取被锁定行记录的值，而?for update?会阻塞其他锁定性读对锁定行的读取（非锁定性读仍然可以读取这些记录，lock in share mode?和?for update?都是锁定性读）。

这么说比较抽象，我们举个计数器的例子：在一条语句中读取一个值，然后在另一条语句中更新这个值。使用?lock in share mode?的话可以允许两个事务读取相同的初始化值，所以执行两个事务之后最终计数器的值+1；而如果使用?for update?的话，会锁定第二个事务对记录值的读取直到第一个事务执行完成，这样计数器的最终结果就是+2了。

乐观锁，大多是基于数据版本 （ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。

读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行重试，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!