Laravel 数据库

作者: 王玉伟的伟 | 来源:发表于2019-12-15 20:37 被阅读0次

配置：

数据库的配置文件放置在 config/database.php 文件中，你可以在此定义所有的数据库连接，并指定默认使用的连接。此文件内提供了大部分 Laravel 能支持的数据库配置示例。

MySQL配置：

首先，在环境变量配置文件 .env 配置MySQL的链接信息

DB_CONNECTION=mysql 
DB_HOST=127.0.0.1 
DB_PORT=3306 
DB_DATABASE=laravel 
DB_USERNAME=root 
DB_PASSWORD=root

然后，在数据库配置文件 config/database.php 进行配置

// 选择链接的数据库 
'default' => env('DB_CONNECTION', 'mysql'), 
// MySQL配置 
'mysql' => [
    'driver' => 'mysql', 
    'url' => env('DATABASE_URL'), 
    'host' => env('DB_HOST', '127.0.0.1'), 
    'port' => env('DB_PORT', '3306'), 
    'database' => env('DB_DATABASE', 'laravel'), 
    'username' => env('DB_USERNAME', 'root'), 
    'password' => env('DB_PASSWORD', 'root'), 
    'unix_socket' => env('DB_SOCKET', ''), 
    'charset' => 'utf8mb4', 
    'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 
];

URLs式的配置：

通常，数据库连接使用多个配置值，例如 host , database , username , password 等。这些配置值中的每一个都有其相应的环境变量。这意味着在生产服务器上配置数据库连接信息时，需要管理多个环境变量。

一些托管数据库提供程序（如 heroku ）提供单个数据库「URL」，该 url 在单个字符串中包含数据库的所有连接信息。示例数据库 URL 可能如下所示：

mysql://root:password@127.0.0.1/forge?charset=UTF‐8

这些 url 通常遵循标准模式约定：

driver://username:password@host:port/database?options

读写分离:

有时候你希望 SELECT 语句使用一个数据库连接，而 INSERT，UPDATE，和 DELETE 语句使用另一个数据库连接。在 Laravel 中，无论你是使用原生查询，查询构造器，或者是 Eloquent ORM，都能轻松的实现

为了弄明白读写分离是如何配置的，我们先来看个例子：

'mysql' => [ 
    'read' => [
         'host' => [ 
              '192.168.1.1', 
              '196.168.1.2', 
        ], 
    ],
    'write' => [ 
        'host' => [
           '196.168.1.3',
         ],
     ],
    'sticky' => true, 
    'driver' => 'mysql', 
    'database' => 'database', 
    'username' => 'root', 
    'password' => '', 
    'charset' => 'utf8mb4', 
    'collation' => 'utf8mb4_unicode_ci', 
    'prefix' => '', 
]

注意在以上的例子中，配置数组中增加了三个键，分别是 read ， write 和 sticky 。 read 和 write 的键都包含一个键为 host 的数组。而 read 和 write 的其他数据库都在键为 mysql 的数组中。

如果你想重写主数组中的配置，只需要修改 read 和 write 数组即可。所以，这个例子中： 192.168.1.1 和 192.168.1.2 将作为「读」连接主机，而 192.168.1.3 将作为「写」连接主机。这两个连接会共享 mysql 数组的各项配置，如数据库的凭据（用户名 / 密码），前缀，字符编码等。

stick 选项：

sticky 是一个可选值，它可用于立即读取在当前请求周期内已写入数据库的记录。若 sticky 选项被启用，并且当前请求周期内执行过「写」操作，那么任何「读」操作都将使用「写」连接。这样可确保同一个请求周期内写入的数据可以被立即读取到，从而避免主从延迟导致数据不一致的问题。不过是否启用它，取决于应用程序的需求

使用多个数据库连接：

当使用多个数据库连接时，你可以通过 DB Facade 的 connection 方法访问每一个连接。传递给 connection 方法的参数 name 应该是 config/database.php 配置文件中 connections 数组中的一个值：

$users = DB::connection('foo')‐>select(...);

你也可以使用一个连接实例上的 getPdo 方法访问底层的 PDO 实例：

$pdo = DB::connection()‐>getPdo();

数据库事务

你可以使用 DB facade 的 transaction 方法在数据库事务中运行一组操作。如果事务的闭包 Closure 中出现一个异常，事务将会回滚。如果事务闭包 Closure 执行成功，事务将自动提交。一旦你使用了 transaction ，就不再需要担心手动回滚或提交的问题：

DB::transaction(function () { 
          DB::table('users')‐>update(['votes' => 1]); 

          DB::table('posts')‐>delete(); 
});

处理死锁

transaction 方法接受一个可选的第二个参数，该参数用来表示事务发生死锁时重复执行的次数。一旦定义的次数尝试完毕，就会抛出一个异常：

DB::transaction(function () { 
          DB::table('users')‐>update(['votes' => 1]); 

          DB::table('posts')‐>delete(); 
}, 5);

手动使用事务

如果你想要手动开始一个事务，并且对回滚和提交能够完全控制，那么你可以使用 DB Facade 的 beginTransaction 方法：

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

// 事务回滚 
DB::rollBack(); 

// 提交事务 
DB::commit();

查询构造器：

Laravel 的数据库查询构造器为创建和运行数据库查询提供了一个方便的接口。它可用于执行应用程序中大部分数据库操作，且可在所有支持的数据库系统上运行。

Laravel 的查询构造器使用 PDO 参数绑定来保护您的应用程序免受 SQL 注入攻击。因此没有必要清理作为绑定传递的字符串。

对于sql的执行进行监听
使用场景：调试的时候，方便与跟踪问题

DB::listen(function ($query) { 
          echo $query‐>sql; 
          // $query‐>bindings 
          // $query‐>time 
});

使用在AppServiceProvider中的boot方法中注入

获取结果：

从一个数据表中获取所有行你可以使用 DB facade 里的 table 方法来开始查询。 table 方法为给定的表返回一个查询构造器实例，允许你在查询上链式调用更多的约束，最后使用 get 方法获取结果：

<?php namespace App\Http\Controllers; 
use Illuminate\Support\Facades\DB; 
use App\Http\Controllers\Controller; 
class UserController extends Controller { 
/*** 显示所有应用程序的用户列表。 ** @return Response */ 
  public function index() { 
      $users = DB::table('users')‐>get(); 
      return view('user.index', ['users' => $users]); 
  } 
}

该 get 方法返回一个包含 Illuminate\Support\Collection 的结果，其中每个结果都是 PHP StdClass 对象的一个实例。你可以访问字段作为对象的属性来访问每列的值：

foreach ($users as $user) {
     echo $user‐>name; 
}

从数据表中获取单行或者单列如果你只需要从数据表中获取一行数据，你可以使用 first 方法。该方法返回一个 StdClass 对象：

$user = DB::table('users')‐>where('name', 'John')‐>first();
echo $user‐>name;

如果你甚至不需要整行数据，则可以使用 value 方法从记录中获取单个值。该方法将直接返回该字段的值：

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

获取一列的值如果你想获取包含单列值的集合，则可以使用 pluck 方法。在下面的例子中，我们将获取角色表中标题的集合：

$titles = DB::table('roles')‐>pluck('title');
foreach ($titles as $title) { 
        echo $title; 
}

你还可以在返回的集合中指定字段的自定义键名：(会将 roles 表中的 name 字段当做键名， title 字段当做键值返回)

$roles = DB::table('roles')‐>pluck('title', 'name');
foreach ($roles as $name => $title) { 
      echo $title; 
}

聚合查询构造器还提供了各种聚合方法，比如 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');

分块操作如果你需要处理上千条数据库记录，你可以考虑使用 chunk 方法。该方法一次获取结果集的一小块，并将其传递给闭包函数进行处理。该方法在 Artisan 命令编写数千条处理数据的时候非常有用。

DB::table('city')‐>orderBy('city_id')‐>chunk(100, function ($citys) { 
          foreach ($citys as $city) { 
              echo $city‐>city_id.' name : '.$city‐>city_name.'<br>'; 
          } 
});

连接查询

inner join 语句

查询构造器也可以编写 join 方法。若要执行基本的「内链接」，你可以在查询构造器实例上使用 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();

Left Join / Right Join 语句

如果你想使用「左连接」或者「右连接」代替「内连接」，可以使用 leftJoin 或者 rightJoin 方法。这两个方法与 join 方法用法相同：

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

$users = DB::table('users') 
          ‐>rightJoin('posts', 'users.id', '=', 'posts.user_id') 
          ‐>get();

高级 Join 语句

你可以指定更高级的 join 语句。比如传递一个闭包作为 join 方法的第二个参数。此闭包接收一个 JoinClause 对象，从而指定 join 语句中指定的约束：

DB::table('users') 
          ‐>join('contacts', function ($join) { 
                $join‐>on('users.id', '=', 'contacts.user_id')‐>orOn(...); 
          })‐>get();

如果你想要在连接上使用「where」风格的语句，你可以在连接上使用 where 和 orWhere 方法。这些方法会将列和值进行比较，而不是列和列进行比较：

DB::table('users') 
          ‐>join('contacts', function ($join) { 
                $join‐>on('users.id', '=', 'contacts.user_id') 
                        ‐>where('contacts.user_id', '>', 5); 
          })
          ‐>get();

Where 语句：

简单的 where 语句
在构造 where 查询实例的中，你可以使用 where 方法。调用 where 最基本的方式是需要传递三个参数：第一个参数是列名，第二个参数是任意一个数据库系统支持的运算符，第三个是该列要比较的值。

例如，下面是一个要验证「votes」字段的值等于 100 的查询：

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

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

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

当然，你也可以使用其他的运算符来编写 where 子句：

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

$users = DB::table('users') ‐>where('votes', '<>', 100) ‐>get();

$users = DB::table('users') ‐>where('name', 'like', 'T%') ‐>get();

你还可以传递条件数组到 where 函数中

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

Or 语句

你可以一起链式调用 where 约束，也可以在查询中添加 or 字句。 orWhere 方法和 where 方法接收的参数一样：

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

其他 Where 语句
whereBetween / orWhereBetween
whereBetween 方法验证字段值是否在给定的两个值之间：

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

whereNotBetween / orWhereNotBetween

whereNotBetween 方法验证字段值是否在给定的两个值之外：

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

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereIn 方法验证字段的值必须存在指定的数组里：

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

whereNull / whereNotNull / orWhereNull / orWhereNotNull
whereNull 方法验证指定的字段必须是 NULL：

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

whereDate / whereMonth / whereDay / whereYear / whereTime
whereDate 方法用于比较字段值与给定的日期：

$users = DB::table('users') ‐>whereDate('created_at', '2016‐12‐31') ‐>get();

whereColumn / orWhereColumn
whereColumn 方法用于比较两个字段的值是否相等：

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

参数分组

有时候你需要创建更高级的 where 子句，例如「where exists」或者嵌套的参数分组。 Laravel 的查询构造器也能够处理这些。下面，让我们看一个在括号中进行分组约束的例子：

$users = DB::table('users') 
                    ‐>where('name', '=', 'John') 
                    ‐>where(function ($query) {
                         $query‐>where('votes', '>', 100) 
                                    ‐>orWhere('title', '=', 'Admin'); 
                    })
                    ‐>get();

你可以看到，通过一个 Closure 写入 where 方法构建一个查询构造器来约束一个分组。这个 Closure 接收一个查询实例，你可以使用这个实例来设置应该包含的约束。上面的例子将生成以下 SQL：

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

Where Exists 语句

whereExists 方法允许你使用 where exists SQL 语句。 whereExists 方法接收一个 Closure 参数，该 whereExists 方法接受一个 Closure 参数，该闭包获取一个查询构建器实例从而允许你定义放置在 exists 字句中查询：

$users = 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 )

数据插入

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

DB::table('users')‐>insert( ['email' => 'john@example.com', 'votes' => 0] );

你甚至可以将数组传递给 insert 方法，将多个记录插入到表中

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

insertOrIgnore 方法用于忽略重复插入记录到数据库的错误：

DB::table('users')‐>insertOrIgnore([ 
            ['id' => 1, 'email' => 'taylor@example.com'], 
            ['id' => 2, 'email' => 'dayle@example.com'] ]
);

自增 ID

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

$id = DB::table('users')‐>insertGetId( ['email' => 'john@example.com', 'votes' => 0] )

数据更新

当然，除了插入记录到数据库中，查询构造器也可以通过 update 方法更新已有的记录。 update 方法和 insert 方法一样，接受包含要更新的字段及值的数组。你可以通过 where 子句对 update 查询进行约束：

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

有时您可能希望更新数据库中的现有记录，或者如果不存在匹配记录则创建它。在这种情况下，可以使用 updateOrInsert 方法。 updateOrInsert 方法接受两个参数：一个用于查找记录的条件数组，以及一个包含要更该记录的键值对数组。

updateOrInsert 方法将首先尝试使用第一个参数的键和值对来查找匹配的数据库记录。如果记录存在，则使用第二个参数中的值去更新记录。如果找不到记录，将插入一个新记录，更新的数据是两个数组的集合：

DB::table('users') ‐>updateOrInsert( 
          ['email' => 'john@example.com', 'name' => 'John'], 
          ['votes' => '2'] 
);

更新 JSON 字段时，你可以使用 > 语法访问 JSON 对象中相应的值，此操作只能支持 MySQL 5.7+ 和 PostgreSQL 9.5+:

$affected = DB::table('users') ‐>where('id', 1) ‐>update(['options‐>enabled' => true]);

自增 & 自减

查询构造器还为给定字段的递增或递减提供了方便的方法。此方法提供了一个比手动编写 update 语句更具表达力且更精练的接口。

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

DB::table('users')‐>increment('votes');

DB::table('users')‐>increment('votes', 5);

DB::table('users')‐>decrement('votes');

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

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

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

删除

查询构造器也可以使用 delete 方法从表中删除记录。在使用 delete 前，可以添加 where 子句来约束 delete 语法：

DB::table('users')‐>delete();

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

如果你需要清空表，你可以使用 truncate 方法，它将删除所有行，并重置自增 ID 为零：

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