What database does laravel support?

laravel supports four databases: 1. MySQL, a relational database management system; 2. PostgreSQL, an "object-relational" database management system; 3. SQLite, a lightweight relational database Management system; 4. SQL Server, a relational database management system.

The operating environment of this tutorial: Windows 7 system, Laravel 6 version, Dell G3 computer.

Laravel supports native SQL queries, fluent query builders and Eloquent ORM. These operations make interacting with the database very simple in various database backends.

Currently Laravel supports the following four databases:

• MySQL 5.7: a relational database management system developed by the Swedish MySQL AB company and belongs to Products of Oracle.

• PostgreSQL 9.6: A free software object-relational database management system with very complete features. It is an object-relational database management system based on POSTGRES, version 4.2, developed by the Department of Computer Science at the University of California. Database management system.

• SQLite 3.8.8: A lightweight database, an ACID-compliant relational database management system, contained in a relatively small C library.

• SQL Server 2017: A relational database management system launched by Microsoft

Configuration

The database configuration file is in the config/database.php file. You can define all database connection configurations in this file and specify the default database connection. This file provides examples of most database configurations supported by Laravel.

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

SQLite configuration

After creating a new SQLite database using a create command such as touch database/database.sqlite, you You can use the absolute path to the database and configure the environment variable to point to this newly created database:

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

To enable foreign key constraints for SQLite connections, the DB_foreign_KEYS environment variable should be set to true:

DB_FOREIGN_KEYS=true

URLs form configuration

Usually, database connections use multiple configuration values, such as host, database, username, password, etc. Each of these configuration values ​​has its corresponding environment variable. This means that there are multiple environment variables that need to be managed when configuring database connection information on the production server.

Some managed database providers (such as Heroku) provide a single database "URL" that contains all connection information for the database in a single string. A sample database URL might look like this:

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

这些 URLs 通常遵循标准模式约定：
driver://username:password@host:port/database?options

For convenience, Laravel supports these URLs as an alternative to configuring the database using multiple configuration options. If the url (or corresponding DATABASE_URL environment variable) configuration option is present, that option will be used to extract database connection and credential information.

Read-write separation

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

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

'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' => '',
],

Note that in the above example, three keys have been added to the configuration array, namely read , write and sticky. Both read and write contain an array with the key host. The other database options for read and write are in the array with the key mysql.

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

sticky option

sticky is an optional value that is used to immediately read records that have been written to the database during the current request cycle. If the sticky option is enabled and a "write" operation is 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 synchronization delay. Whether to enable it, however, depends on the application's needs.

Using multiple database connections

When using multiple database connections, you can access each connection through the connection method of the DB Facade facade. 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 can also use the getPdo method on a connection instance to access the underlying PDO instance:

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

执行原生 SQL 查询

一旦配置好数据库连接后，便可以使用 DB facade 门面运行查询。DB facade 为每种类型的查询提供了相应的方法：select，update，insert，delete 和 statement。

执行 Select 查询

你可以使用 DB Facade 的 select 方法来运行基础的查询语句：

<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
class UserController extends Controller
{
    /**
     * 显示应用程序中所有用户的列表
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::select(&#39;select * from users where active = ?&#39;, [1]);
        return view(&#39;user.index&#39;, [&#39;users&#39; => $users]);
    }
}

传递给 select 方法的第一个参数就是一个原生的 SQL 查询，而第二个参数则是需要绑定到查询中的参数值。通常，这些值用于约束 where 语句。参数绑定可以防止 SQL 注入。

select 方法将始终返回一个 array 数组，数组中的每个结果都是一个 stdClass 对象，可以像下面这样访问结果中的数值：

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

使用命名绑定

除了使用 ? 表示参数绑定外，你还可以使用命名绑定的形式来执行一个查询：

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

执行 Insert 语句

你可以使用 DB Facade 的 insert 方法来执行 insert 语句。与 select 方法一样，该方法将原生 SQL 查询作为其第一个参数，并将绑定的数据作为第二个参数：

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

执行 Update 语句

update 方法用于更新数据库中现有的记录。该方法返回该执行语句影响的行数：

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

执行 Delete 语句

delete 方法用于从数据库中删除记录。与 update 方法一样，返回受该执行语句影响的行数：

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

执行普通语句

有些数据库语句不会有任何返回值。对于这些语句，你可以使用 DB Facade 的 statement 方法来运行：

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

运行未预处理的语句

有时你可能希望在不绑定任何值的情况下运行语句。对于这些类型的操作，可以使用 DB Facade 的 unprepared 方法：

DB::unprepared('update users set votes = 100 where name = "Dries"');

请注意，这些语句不会像上面的语句那样绑定值。它们可以打开你的应用程序进行 SQL 注入，应该非常小心地使用。

隐式提交

在事务中使用 DB 外观的 statement 和 unprepared 方法时，必须小心避免导致 [隐式提交] 的语句 (https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html)。 这些语句将导致数据库引擎间接提交整个事务，从而使 Laravel 不知道数据库的事务级别。这种语句的一个例子是创建数据库表：

DB::unprepared('create table a (col varchar(1) null)');

请参考 MySQL 手册中的触发隐式提交的所有语句列表。

监听查询事件

如果你想监控程序执行的每一个 SQL 查询，你可以使用 listen 方法。这个方法对于记录查询或调试非常有用。你可以在 服务提供器 中注册你的查询监听器：

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

数据库事务

你可以使用 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();

你可以使用 rollBack 方法回滚事务：

DB::rollBack();

最后，你可以使用 commit 方法提交事务：

DB::commit();

技巧：DB facade 的事务方法同样适用于 查询构造器 和 Eloquent ORM。

连接到数据库 CLI

如果要连接到数据库的 CLI，可以使用 db Artisan 命令：

php artisan db

如果需要，可以指定数据库连接名称以连接到不是默认连接的数据库连接：

php artisan db mysql

【相关推荐：laravel视频教程】

The above is the detailed content of What database does laravel support?. For more information, please follow other related articles on the PHP Chinese website!