安装数据库组件

composer require hyperf/db-connection

数据库配置

<?php

declare(strict_types=1);

return [
    'default' => [
        'driver' => env('DB_DRIVER', 'mysql'),
        'host' => env('DB_HOST', 'mysql'),
        'database' => env('DB_DATABASE', 'hyperf'),
        'port' => env('DB_PORT', 3306),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', '123456'),
        'charset' => env('DB_CHARSET', 'utf8'),
        'collation' => env('DB_COLLATION', 'utf8_unicode_ci'),
        'prefix' => env('DB_PREFIX', ''),
        'pool' => [
            'min_connections' => 1,
            'max_connections' => 10,
            'connect_timeout' => 10.0,
            'wait_timeout' => 3.0,
            'heartbeat' => -1,
            'max_idle_time' => (float) env('DB_MAX_IDLE_TIME', 60),
        ],
        'commands' => [
            'gen:model' => [
                'path' => 'app/Model',
                'force_casts' => true,
                'inheritance' => 'Model',
            ],
        ],
    ],
];

本地环境配置

APP_NAME=skeleton
APP_ENV=dev

DB_DRIVER=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=hyperf
DB_USERNAME=root
DB_PASSWORD=123456
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci
DB_PREFIX=

REDIS_HOST=redis
REDIS_AUTH=(null)
REDIS_PORT=6379
REDIS_DB=0

生成User Model

php bin/hyperf.php gen:Model User

user model

<?php

declare (strict_types=1);
namespace App\Model;

use Hyperf\DbConnection\Model\Model;
/**
 */
class User extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'User';
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [];
    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [];
}

生成Db Controller

php bin/hyperf.php gen:controller DbController

Db controller 添加路由 添加Db测试代码

<?php

declare(strict_types=1);

namespace App\Controller;

use Hyperf\HttpServer\Contract\RequestInterface;
use Hyperf\HttpServer\Contract\ResponseInterface;
use Hyperf\HttpServer\Annotation\AutoController;
use Hyperf\DbConnection\Db;

/**
 * @AutoController
 */
class DbController
{
        public function index(RequestInterface $request, ResponseInterface $response)
        {
                $age = $request->input('age',20);
                $users = Db::select('SELECT * FROM `user` WHERE age = ?',[$age]);  //  返回array

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

mysql user表数据

mysql> select * from user;
+----+-------------+------+--------+
| id | name        | age  | status |
+----+-------------+------+--------+
|  1 | HuYongjian6 |   20 |      1 |
|  2 | huyongjian2 |   23 |      0 |
|  3 | Huyongjian3 |   23 |      1 |
+----+-------------+------+--------+
3 rows in set (0.00 sec)

测试

curl 118.195.173.53:9501/db/index?age=23

返回结果

[{
    "id": 2,
    "name": "huyongjian2",
    "age": 23,
    "status": 0
}, {
    "id": 3,
    "name": "Huyongjian3",
    "age": 23,
    "status": 1
}]

Execute 执行类

use Hyperf\DbConnection\Db;

$inserted = Db::insert('INSERT INTO user (id, name, age) VALUES (?, ?, ?)', [4, '小明',25]); // 返回是否成功 bool
$affected = Db::update('UPDATE user set name = ? WHERE id = ?', ['Huyongjian4', 4]);
$affected = Db::delete('DELETE FROM user WHERE id = ?', [4]);

自动管理数据库事务

Db::transaction(function () {
    Db::table('user')->where('id',1)->update(['name' => 'hyj1']);
    Db::table('user')->where('id',3)->update(['name'=>'hyj3']);
});

手动管理数据库事务

Db::beginTransaction();
try{
    Db::table('user')->where('id',1)->update(['name' => 'HYJ1']);
    Db::table('user')->where('id',3)->update(['name'=>'HYJ3']);
    Db::commit();
} catch(\Throwable $ex){
    Db::rollBack();
}

SQL 数据记录

// 启用 SQL 数据记录功能
Db::enableQueryLog();
Db::table('user')->where('id',1)->get();
Db::table('user')->where('id',2)->get();
// 打印最后一条 SQL 相关数据
var_dump(Db::getQueryLog());

记录结果

array(2) {
  [0]=>
  array(3) {
    ["query"]=>
    string(35) "select * from `user` where `id` = ?"
    ["bindings"]=>
    array(1) {
      [0]=>
      int(1)
    }
    ["time"]=>
    float(1.5)
  }
  [1]=>
  array(3) {
    ["query"]=>
    string(35) "select * from `user` where `id` = ?"
    ["bindings"]=>
    array(1) {
      [0]=>
      int(2)
    }
    ["time"]=>
    float(0.42)
  }
}