Laravel—数据库操作

发表于 更新于 阅读次数： 本文字数： 13k 阅读时长 ≈ 12 分钟

数据库操作

Laravel支持的数据库系统：

  • MySQL
  • Postgres
  • SQLite
  • SQL Server

配置

应用的数据库配置位于config/database.php（但数据库用户及密码等敏感信息位于.env文件）

<!-- 位于.env文件中关于数据库的配置 -->

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

config/database.php文件可以根据需要配置要使用的数据库，env函数读取.env配置,第二个参数表示读取不到则采用默认的方式

<!-- 位于database.php中关于MySQL数据库的配置 -->
    'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

运行原生的SQL查询

DB门面为每种操作提供了相应的方法：select,update,insert,delete和statement
使用之前要先引用DB类

//要先引入！！！
use Illuminate\Support\Facades\DB;
<!-- 查询语句 -->
public function dbSelect()
{
    //该方法以数组的形式返回结果集
    //?表示通配符，这样做可以防止SQL注入攻击
    $res = DB::select('select * from goods where id = ?', [20]);
    //还可以使用命名绑定的方式执行查询
    $res = DB::select('select * from goods where id = :id',['id'=>1]);
}

<!-- 插入语句 -->
public function dbInsert()
{
    $res = DB::insert('insert into goods (id,names) values (?,?)',[1,'xiaoming']);
}

<!-- 更新语句 -->
public function dbUpdate()
{
    //该方法返回更新语句影响的行数
    $res = DB::update('update goods set name = ? where id = ?',['xiaoming',2]);
}

<!-- 删除语句 -->
public function dbDelete()
{
    //该方法返回删除的行数
    $res = DB::delete('delete from goods where id < ?',[3]);
}

运行一个通用语句
DB门面的statement方法可以用于新增表、修改表、删除表等操作

DB::statement(‘drop table goods’);

事务操作

事务操作指在数据库中运行一系列的操作，只有当全部操作都成功时才生效，否则就自动还原为原来的状态

手动使用事务

使用DB门面的beginTransaction开始一个事务,rollBack表示出错时回滚事务,commit用于提交事务

//开始事务  
DB::beginTransaction();

//数据库执行语句
$res = DB::update("update user set account = account - 2000 where id = 1000");
$res2 = DB::update('update user set account = account + 2000 where id = 2');

if($res && $res2) {
    //事务提交
    DB::commit();
}
else {
    //回滚事务
     DB::rollback();
}

自动使用事务

DB门面的transaction方法能够自动在一个数据库事务中运行一连串操作，不需要手动回滚或提交

DB::transaction(function(){
    //数据库操作的各种函数
    ...
});

处理死锁

数据库死锁是指有两个或者两个以上的数据库操作相互依赖，一方需要等待另一方退出才能获取资源，但是没有一方提前退出，就会造成死锁
transaction方法接受一个可选参数作为第二个参数，用于定义死锁发生时事务的最大重试次数，如果茶尝试次数超过指定值，就抛出异常：

DB::transaction(function(){
    //数据库操作的各种函数
},5);//指定次数在函数之后

操作多个数据库（读写分离）

配置方法

当需要操作多个数据库时，可以在config/database.php配置中多添加三个数组read、write、sticky（可选）,read和write两个键对应值都有一个包含单个键host的数组，分别表示读链接和写链接，读/写链接的其余配置共用mysql主数组配置

  • 读链接可以以数组形式配置多个
  • 目前Laravel只支持的单个写链接
  • sticky项用于在当前请求生命周期内允许立即读取写入数据库的记录，使用写链接读取
'mysql' => [
            'read' => [
                'host' => ['193.168.1.1','193.168.1.2']
            ],
            'write' => [
                'host' => ['193.168.1.3']
            ],
            'sticky' => true,
            'driver' => 'mysql',
            // 'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

使用不同数据库连接

DB门面上的connection方法可以访问不同的链接，参数为配置文件中对应的某个链接的名字,有几种不同的使用方法：

$users = DB::connection(‘read’)->select(…);
$users = DB::connection(‘mysql::read’)->select(…);

查询构建器

Laravel提供的用于创建和执行数据库查询的方便流接口，使用DB门面的table方法，参数为表名

查询

查询所有行

get方法获取表中所有的记录

$users = DB::table(‘users’)->get();

查询单条

first方法从数据表中获取单行数据

$users = DB::table(‘users’)->where(‘name’,’=’,’xiaoming’)->first()

value方法从结果中获取某一字段的单个值

$users = DB::table(‘users’)->where(‘id’,’=’,’1’)->value(‘name’)

查询一列

pluck方法获取指定某个列的所有值

$users = DB::table(‘users’)->pluck(‘name’)

可以为pluck指定第二个可选参数作为列值的自定义键，键名必须是该表其它字段名，否则会报错 

//无参数
$title = DB::table('roles')->pluck('title');

foreach ($titles as $title) {
    echo $title;
}

//有参数
$roles = DB::table('roles')->pluck('title','name');

foreach ($roles as $name => $title)
{
    echo $title;
}

组块结果集

chunk方法用于处理当数据库记录很多时，一次获取结果集的一小块 

//把全部uses表数据分割成一次处理100条记录的小组块
DB::table('users')->orderBy('id')->chunk(100,function($users){
    foreach ($users as $user)
});

指定查询子句

select方法可以为查询自定义select子句

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

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

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

addSelect方法能够在已有的查询子句的基础上添加一个查询字段

$query = DB::table(‘users’)->select(‘name’);
$users = $query->addSelect(‘age’)->get();

原生表达式

有时候在查询中需要使用到原生表达式，DB::raw方法或者selectRaw方法可以创建原生表达式

//第一种方法
$users = DB::table('users')
                ->select(DB::raw('count(*) as user_count,stasus'))
                ->where('status','<>',1)
                ->groupBy('status')
                ->get();

//第二种方法
$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();
  • whereRaw和orWhereRaw方法可用于注入原生where子句到查询，这两个方法接收一个可选的绑定数组作为第二个参数:
$orders = DB::table('orders')
            ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
            ->get();
  • havingRaw和orHavingRaw方法可用于设置原生字符串作为having子句的值：
$orders = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();
  • orderByRaw方法可用于设置原生字符串作为order by子句的值：
$orders = DB::table('orders')
                ->orderByRaw('updated_at - created_at DESC')
                ->get();

插入

insert方法用于插入记录到数据表,接受数组形式的字段名和字段值作为参数进行插入操作

DB::table('users')->insert([
    ['name' => 'xiaoming', 'years' => 8],
    ['name' =>'xzm', 'years' => 6]
]);

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

$id = DB::table('users')->insetGetId([
    ['name' => 'xiaoming', 'years' => 8]
]);

更新

update方法用于更新已有记录

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

删除

delete 方法用于删除记录 

DB::table('users')
    ->where(['name'=>'xiaoming'])
    ->delete();

truncate方法可以用于清除整张表

DB::table(‘users’)->truncate();

自增与自减

increment和decrement方法用于为给定字段递增或递减，第二个可选参数用于控制递增或递减的量 

DB::table('users')->increment('point');

DB::teble('users')->increment('point',5);

DB::table('users')->decrement('point');

DB::teble('users')->decrement('point',5);

也可以在操作过程中指定更新的字段：

DB::table(‘users’)->increment(‘point’,1,[‘name’ => ‘xiaoming’]);

连贯操作

条件

where

where方法需要传递三个参数，第一个参数是列名，第二个参数是任意一个数据库系统支持的操作符（默认是=），第三个参数是该列要比较的值。
还可以传递条件数组到where函数

$users = DB::table('users')->where
([
    ['status','=','1']，
    ['subscribed','<>','1'],
])->get();

orWhere相当于或，即只要有一个条件满足即可 

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

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

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


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

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

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


$users = DB::table('users')
            ->whereNotIn('id', [1, 2, 3])
            ->get();

其它类型：

  • whereNull,whereNotNull方法验证给定列的值是否为 NULL

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

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

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

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

  • whereTime方法用于比较字段值和指定时间

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

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

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

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

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

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

whereExists方法允许编写where existsSQL语句，接受一个函数作为参数，该函数获取一个查询构建器实例 

//其中$query=DB::table('orders')
DB::table('users')
         ->whereExists(function($query){
             $query ->select(DB::raw(1))
                    ->from('orders')
                    ->whereRaw('orders.user_id = users.id');
         })
         ->get();
//等价于以下SQL语句：
select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

when

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

可以传递另一个闭包作为when方法的第三个参数，该闭包会在第一个参数为 false 的情况下执行

$sortBy = null;

//闭包的基本语法，use表示引入外部变量
$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                        return $query->orderBy($sortBy);
                    }, function ($query) {
                        return $query->orderBy('name');
                    })
                ->get();

排序

orderBy方法对给定结果集进行排序，第一个参数为排序字段，第二个是排序法方向，asc或desc 

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

groupBy/having方法对查询结果进行分组

//可以将多个参数作为数组传递给groupBy方法
$users = DB::table('users')
                 ->groupBy('account_id','first_name')
                 ->having('account_id','>','100')
                 ->get();

latest/oldest方法通过日期对结果进行排序，默认情况下根据created_at进行排序，也可以把字段名作为参数传入

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

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

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

分页

skip和take方法用于限定查询跳过的数目和返回的数目，一般用于分页，还可以用limit和offset方法：

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

连接表

关于SQL的几种连接类型：

join

join方法为内连接，第一个参数为连接的表名，其他的参数为连接指定的列约束

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

leftJoin为左连接方法

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

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

联合（Union）

union方法将两个查询联合 

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

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

计算（聚合函数）

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

$users = DB::table('users')->count();

$price = DB::table('orders')->max('price');

还可以联合其它查询子句和聚合函数来构建查询：

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

除了通过 count 方法来判断匹配查询条件的结果是否存在外，还可以使用 exists 或 doesntExist 方法：

return DB::table('orders')->where('finalized', 1)->exists();

return DB::table('orders')->where('finalized', 1)->doesntExist();

sql语句记录（监听查询事件）

获取应用中每次 SQL 语句的执行，可以使用listen方法，该方法对查询日志和调试非常有用，可以在服务提供者中注册查询监听器：

<?php

namespace App\Providers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function ($query) {
             $query->sql
             $query->bindings
             $query->time
        });
    }

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

悲观锁 & 乐观锁

悲观锁（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’ for 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” 字段来实现。

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