Introduction

Laravel enables very simple interaction with databases in various database backends using native SQL, fluent query builder and Eloquent ORM. Laravel currently supports four databases:

Configuration

The database configuration file is placed in the config/database.php file, you All database connections can be defined here and the connection used by default can be specified. This file provides examples of most database configurations supported by Laravel.

By default, Laravel's sample environment configuration uses Laravel Homestead (this is a small virtual machine that allows you to easily develop Laravel locally). You can modify this configuration according to the needs of your local database.

SQLite configuration

After creating a new SQLite database using a command like touch database/database.sqlite, you can use the absolute value of the database Path configuration environment variable to point to this newly created database:

DB_CONNECTION=sqliteDB_DATABASE=/absolute/path/to/database.sqlite

If you want to enable foreign key constraints for SQLite connections, you should add foreign_key_constraints to config/database.php In the configuration file:

'sqlite' => [   
 // ...    
 'foreign_key_constraints' => true,
 ],

Read and write separation

Sometimes you want the SELECT statement to use a database connection , while INSERT, UPDATE, and DELETE statements use another database connection. In Laravel, whether you use native queries, query builders, or Eloquent ORM, you can easily implement

In order to understand how read-write separation is configured, let's first look at an example:

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

Note that in the above example, three keys are added to the configuration array, namely read, write and sticky. The keys for read and write both contain an array with the key host. The other databases of read and write are in the array with the key mysql.

If you want to override the configuration in the main array, just modify the read and write arrays. So, in this example: 192.168.1.1 will connect to the host as a "read", and 192.168.1.2 will connect to the host as a "write". These two connections will share various configurations of the mysql array, such as database credentials (username/password), prefix, character encoding, etc.

sticky The option

sticky is an optional value that can be used to immediately read what has been written during the current request cycle Database records. If the sticky option is enabled and a "write" operation has been performed during the current request cycle, any "read" operations will use the "write" connection. This ensures that data written in the same request cycle can be read immediately, thereby avoiding the problem of data inconsistency caused by master-slave delay. Whether to enable it, however, depends on the application's needs.

Using multiple database connections

When using multiple database connections, you can use DB Facade's connection method accesses each connection. The parameter name passed to the connection method should be a value in the connections array in the config/database.php configuration file:

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

you You can also access the underlying PDO instance using the getPdo method on a connection instance:

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

Running native SQL Queries

Once you have configured your database connection, you can run queries using the DB facade. The DB facade provides methods for each type of query: select, update, insert, delete and statement.

Run Select Query

You can use the select method of the DB Facade to run the basic query statement:

<?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::select('select * from users where active = ?', [1]);        
         return view('user.index', ['users' => $users]);    
        }
    }

The first parameter passed to the select method is a native SQL query, and the second parameter is the parameter value that needs to be bound to the query. Typically, these values ​​are used in constraint where statements. Parameter binding is used to prevent SQL injection. The

select method will always return an array, each result in the array is a = StdClass object, and the result value can be accessed like this:

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

Using named binding

In addition to using ? to indicate parameter binding, you can also use named binding to execute a query:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Run the insert statement

You can use the insert method of the DB Facade to execute the insert statement. Like select, this method takes the native SQL query as its first parameter and the bound data as the second parameter:

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

Run the update statement

update method is used to update existing records in the database. This method returns the number of rows affected by the statement:

$affected = DB::update('update users set votes = 100 where name = ?', ['John']);

Run delete statement

delete method is used to delete records from the database. Like update, returns the number of rows affected by the statement:

$deleted = DB::delete('delete from users');

Run ordinary statements

Some database statements will not have any return value . For these statements, you can use the statement method of the DB Facade to run:

DB::statement('drop table users');

Listen to query events

If you want to monitor every SQL query executed by the program, you can use the listen method. This method is useful for logging queries or debugging. You can register your query listener in the service provider:

<?php
    namespace App\Providers;
    use Illuminate\Support\Facades\DB;
    use Illuminate\Support\ServiceProvider;
    class AppServiceProvider extends ServiceProvider{  
      /**
     * 启动应用服务
     *
     * @return void
     */    
    public function boot()  
      {     
         DB::listen(function ($query) {          
           // $query->sql            
           // $query->bindings            
           // $query->time       
          });   
        }  
    /**
     * 注册服务提供器
     *
     * @return void
     */   
    public function register()  
      {      
        //   
       }
  }

Database Transaction

You can use The DB facade's transaction method runs a set of operations within a database transaction. If an exception occurs in the transaction's closure Closure, the transaction will be rolled back. If the transaction closure Closure executes successfully, the transaction will automatically commit. Once you use transaction, you no longer need to worry about manual rollback or commit:

DB::transaction(function () {  
  DB::table('users')->update(['votes' => 1]);    
  DB::table('posts')->delete();
 });

Handling deadlocks

The transaction method accepts an optional second parameter, which is used to indicate the number of times the transaction will be repeated when a deadlock occurs. Once the defined number of attempts have been completed, an exception will be thrown:

DB::transaction(function () { 
   DB::table('users')->update(['votes' => 1]);    
   DB::table('posts')->delete();
  }, 5);

Using transactions manually

If you want to start a transaction manually, and be sure to rollback and Submission can be fully controlled, then you can use beginTransaction method of DB Facade:

DB::beginTransaction();

You can use rollBack method to roll back the transaction:

DB::rollBack();

Finally, you can use the commit method to commit the transaction:

DB::commit();

{tip} DB The transaction method of the facade also applies to queries Constructor and Eloquent ORM.