haoa/mix-database

There is no license information available for the latest version (v1.0.3) of this package.

Simple database for use in multiple execution environments, with support for FPM, Swoole, Workerman, and optional pools

v1.0.3 2024-07-30 06:29 UTC

Requires

• php: >=7.2.0
• ext-pdo: *
• mix/object-pool: ~3.0.0

Requires (Dev)

• phpunit/phpunit: ^7.0.0

Unknown License

README

composer require haoa/mix-database

Quick start

注意：协程环境中，不可在并发请求中使用单例

$db = new Mix\Database\Database('mysql:host=127.0.0.1;port=3306;charset=utf8;dbname=test', 'username', 'password');

创建

$db->insert('users', [
    'name' => 'foo',
    'balance' => 0,
]);

查询

$db->table('users')->where('id = ?', 1)->first();

更新

$db->table('users')->where('id = ?', 1)->update('name', 'foo1');

删除

$db->table('users')->where('id = ?', 1)->delete();

启动连接池 Pool

在 Swoole 协程环境中，启动连接池

$maxOpen = 50;        // 最大开启连接数
$maxIdle = 20;        // 最大闲置连接数
$maxLifetime = 3600;  // 连接的最长生命周期
$waitTimeout = 0.0;   // 从池获取连接等待的时间, 0为一直等待
$db->startPool($maxOpen, $maxIdle, $maxLifetime, $waitTimeout);
Swoole\Runtime::enableCoroutine(); // 必须放到最后，防止触发协程调度导致异常

连接池统计

$db->poolStats(); // array, fields: total, idle, active

创建 Insert

创建

$data = [
    'name' => 'foo',
    'balance' => 0,
];
$db->insert('users', $data);

获取 InsertId

$data = [
    'name' => 'foo',
    'balance' => 0,
];
$insertId = $db->insert('users', $data)->lastInsertId();

替换创建

$data = [
    'name' => 'foo',
    'balance' => 0,
];
$db->insert('users', $data, 'REPLACE INTO');

批量创建

$data = [
    [
        'name' => 'foo',
        'balance' => 0,
    ],
    [
        'name' => 'foo1',
        'balance' => 0,
    ]
];
$db->batchInsert('users', $data);

使用函数创建

$data = [
    'name' => 'foo',
    'balance' => 0,
    'add_time' => new Mix\Database\Expr('CURRENT_TIMESTAMP()'),
];
$db->insert('users', $data);

查询 Select

获取结果

Where

AND

$db->table('users')->where('id = ? AND name = ?', 1, 'foo')->get();

$db->table('users')->where('id = ?', 1)->where('name = ?', 'foo')->get();

OR

$db->table('users')->where('id = ? OR id = ?', 1, 2)->get();

$db->table('users')->where('id = ?', 1)->or('id = ?', 2)->get();

IN

$db->table('users')->where('id IN (?)', [1, 2])->get();

$db->table('users')->where('id NOT IN (?)', [1, 2])->get();

Select

$db->table('users')->select('id, name')->get();

$db->table('users')->select('id', 'name')->get();

$db->table('users')->select('name AS n')->get();

Order

$db->table('users')->order('id', 'desc')->get();

$db->table('users')->order('id', 'desc')->order('name', 'asc')->get();

Limit

$db->table('users')->limit(5)->get();

$db->table('users')->offset(10)->limit(5)->get();

Group & Having

$db->table('news')->select('uid, COUNT(*) AS total')->group('uid')->having('COUNT(*) > ?', 0)->get();

$db->table('news')->select('uid, COUNT(*) AS total')->group('uid')->having('COUNT(*) > ? AND COUNT(*) < ?', 0, 10)->get();

Join

$db->table('news AS n')->select('n.*, u.name')->join('users AS u', 'n.uid = u.id')->get();

$db->table('news AS n')->select('n.*, u.name')->leftJoin('users AS u', 'n.uid = u.id AND u.balance > ?', 10)->get();

更新 Update

更新单个字段

$db->table('users')->where('id = ?', 1)->update('name', 'foo1');

获取影响行数

$rowsAffected = $db->table('users')->where('id = ?', 1)->update('name', 'foo1')->rowCount();

更新多个字段

$data = [
    'name' => 'foo1',
    'balance' => 100,
];
$db->table('users')->where('id = ?', 1)->updates($data);

使用表达式更新

$db->table('users')->where('id = ?', 1)->update('balance', new Mix\Database\Expr('balance + ?', 1));

$data = [
    'balance' => new Mix\Database\Expr('balance + ?', 1),
];
$db->table('users')->where('id = ?', 1)->updates($data);

使用函数更新

$db->table('users')->where('id = ?', 1)->update('add_time', new Mix\Database\Expr('CURRENT_TIMESTAMP()'));

$data = [
    'add_time' => new Mix\Database\Expr('CURRENT_TIMESTAMP()'),
];
$db->table('users')->where('id = ?', 1)->updates($data);

删除 Delete

删除

$db->table('users')->where('id = ?', 1)->delete();

获取影响行数

$rowsAffected = $db->table('users')->where('id = ?', 1)->delete()->rowCount();

原生 Raw

$db->raw('SELECT * FROM users WHERE id = ?', 1)->first();

$db->exec('DELETE FROM users WHERE id = ?', 1)->rowCount();

事务 Transaction

手动事务

$tx = $db->beginTransaction();
try {
    $data = [
        'name' => 'foo',
        'balance' => 0,
    ];
    $tx->insert('users', $data);
    $tx->commit();
} catch (\Throwable $ex) {
    $tx->rollback();
    throw $ex;
}

自动事务，执行异常自动回滚并抛出异常

$db->transaction(function (Mix\Database\Transaction $tx) {
    $data = [
        'name' => 'foo',
        'balance' => 0,
    ];
    $tx->insert('users', $data);
});

调试 Debug

$db->debug(function (Mix\Database\ConnectionInterface $conn) {
        var_dump($conn->queryLog()); // array, fields: time, sql, bindings
    })
    ->table('users')
    ->where('id = ?', 1)
    ->get();

日志 Logger

日志记录器，配置后可打印全部SQL信息

$db->setLogger($logger);

$logger 需实现 Mix\Database\LoggerInterface

interface LoggerInterface
{
    public function trace(float $time, string $sql, array $bindings, int $rowCount, ?\Throwable $exception): void;
}

License

Apache License Version 2.0, http://www.apache.org/licenses/