数据库连接

Testing Is Documentation

tests/Database/DatabaseTest.php数据库连接 - 图1

Uses

  1. <?php
  3. use Exception;
  4. use Generator;
  5. use Leevel\Database\Database;
  6. use Leevel\Database\IDatabase;
  7. use Leevel\Database\Mysql;
  8. use Leevel\Database\Select;
  9. use Leevel\Filesystem\Helper;
  10. use PDO;
  11. use PDOException;
  12. use Tests\Database\DatabaseTestCase as TestCase;
  13. use Tests\MysqlNeedReconnectMock;
  14. use Throwable;

基本使用

  1. public function testBaseUse(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $database = $connect;
  7.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  9.     $this->assertSame(
  10.         1,
  11.         $database
  12.             ->table('guest_book')
  13.             ->insert($data),
  14.     );
  16.     $result = $database
  17.         ->table('guest_book', 'name,content')
  18.         ->where('id', 1)
  19.         ->findOne();
  21.     $this->assertSame('tom', $result->name);
  22.     $this->assertSame('I love movie.', $result->content);
  23. }

query 查询数据记录

  1. public function testQuery(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  4.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  6.     $this->assertSame(
  7.         1,
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data),
  11.     );
  13.     $insertData = $connect->query('select * from guest_book where id=?', [1]);
  14.     $insertData = (array) $insertData[0];
  16.     $this->assertSame(1, $insertData['id']);
  17.     $this->assertSame('tom', $insertData['name']);
  18.     $this->assertSame('I love movie.', $insertData['content']);
  19.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  20. }

query 查询数据记录支持缓存

query 是一个底层查询方法支持直接设置缓存，实际上其它的查询都会走这个 query 查询方法。

query 原型

  1. # Leevel\Database\Database::query
  2. /**
  3.  * 查询数据记录.
  4.  *
  5.  * @param bool|int $master
  6.  *
  7.  * @return mixed
  8.  */
  9. public function query(string $sql, array $bindParams = [], $master = false, ?string $cacheName = null, ?int $cacheExpire = null, ?string $cacheConnect = null);
  1. public function testQueryCache(): void
  2. {
  3.     $manager = $this->createDatabaseManager();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 5; $n++) {
  8.         $manager
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $cacheDir = dirname(__DIR__).'/databaseCacheManager';
  14.     $cacheFile = $cacheDir.'/testcachekey.php';
  16.     $result = $manager
  17.         ->table('guest_book')
  18.         ->query('SELECT * FROM guest_book');
  19.     $this->assertFileNotExists($cacheFile);
  20.     $this->assertCount(6, $result);
  21.     $this->assertSame(1, $result[0]->id);
  22.     $this->assertSame('tom', $result[0]->name);
  23.     $this->assertSame('I love movie.', $result[0]->content);
  25.     $resultWithoutCache = $manager
  26.         ->query('SELECT * FROM guest_book', [], false, 'testcachekey');
  27.     // cached data
  28.     $resultWithCache = $manager
  29.         ->query('SELECT * FROM guest_book', [], false, 'testcachekey');
  31.     $this->assertFileExists($cacheFile);
  32.     $this->assertCount(6, $resultWithCache);
  33.     $this->assertSame(1, $resultWithCache[0]->id);
  34.     $this->assertSame('tom', $resultWithCache[0]->name);
  35.     $this->assertSame('I love movie.', $resultWithCache[0]->content);
  36.     $this->assertEquals($result, $resultWithCache);
  37.     $this->assertFalse($result === $resultWithCache);
  38.     $this->assertEquals($resultWithCache, $resultWithoutCache);
  39. }

execute 执行 SQL 语句

  1. public function testExecute(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertSame(1, $connect->execute('insert into guest_book (name, content) values (?, ?)', ['小鸭子', '喜欢游泳']));
  6.     $insertData = $connect->query('select * from guest_book where id=?', [1]);
  7.     $insertData = (array) $insertData[0];
  9.     $this->assertSame(1, $insertData['id']);
  10.     $this->assertSame('小鸭子', $insertData['name']);
  11.     $this->assertSame('喜欢游泳', $insertData['content']);
  12.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  13. }

cursor 游标查询

cursor 游标查询可以节省内存。

cursor 原型

  1. # Leevel\Database\Database::cursor
  2. /**
  3.  * 游标查询.
  4.  *
  5.  * @param bool|int $master
  6.  */
  7. public function cursor(string $sql, array $bindParams = [], $master = false): Generator;
  1. public function testCursor(): void
  2. {
  3.     $manager = $this->createDatabaseManager();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 5; $n++) {
  8.         $manager
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $result = $manager->cursor('SELECT * FROM guest_book');
  14.     $this->assertInstanceof(Generator::class, $result);
  15.     $n = 1;
  16.     foreach ($result as $v) {
  17.         $this->assertSame($n, $v->id);
  18.         $this->assertSame('tom', $v->name);
  19.         $this->assertSame('I love movie.', $v->content);
  20.         $n++;
  21.     }
  22. }

select 原生 SQL 查询数据

  1. public function testSelect(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     $this->assertSame(
  8.         1,
  9.         $connect
  10.             ->table('guest_book')
  11.             ->insert($data),
  12.     );
  14.     $insertData = $connect->select('select * from guest_book where id = ?', [1]);
  15.     $insertData = (array) $insertData[0];
  17.     $this->assertSame(1, $insertData['id']);
  18.     $this->assertSame('tom', $insertData['name']);
  19.     $this->assertSame('I love movie.', $insertData['content']);
  20.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  21. }

select 原生 SQL 查询数据支持参数绑定

  1. public function testSelectWithBind(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     $this->assertSame(
  8.         1,
  9.         $connect
  10.             ->table('guest_book')
  11.             ->insert($data),
  12.     );
  14.     $insertData = $connect->select('select * from guest_book where id = :id', ['id' => 1]);
  15.     $insertData = (array) $insertData[0];
  17.     $this->assertSame(1, $insertData['id']);
  18.     $this->assertSame('tom', $insertData['name']);
  19.     $this->assertSame('I love movie.', $insertData['content']);
  20.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  21. }

insert 插入数据 insert (支持原生 SQL)

  1. public function testInsert(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertSame(1, $connect->insert('insert into guest_book (name, content) values (?, ?)', ['tom', 'I love movie.']));
  7.     $insertData = $connect->select('select * from guest_book where id = :id', ['id' => 1]);
  8.     $insertData = (array) $insertData[0];
  10.     $this->assertSame(1, $insertData['id']);
  11.     $this->assertSame('tom', $insertData['name']);
  12.     $this->assertSame('I love movie.', $insertData['content']);
  13.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  14. }

update 更新数据 update (支持原生 SQL)

  1. public function testUpdate(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertSame(1, $connect->insert('insert into guest_book (name, content) values (?, ?)', ['tom', 'I love movie.']));
  7.     $insertData = $connect->select('select * from guest_book where id = :id', ['id' => 1]);
  8.     $insertData = (array) $insertData[0];
  10.     $this->assertSame(1, $insertData['id']);
  11.     $this->assertSame('tom', $insertData['name']);
  12.     $this->assertSame('I love movie.', $insertData['content']);
  13.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  15.     $this->assertSame(1, $connect->update('update guest_book set name = "小牛" where id = ?', [1]));
  17.     $insertData = $connect->select('select * from guest_book where id = :id', ['id' => 1]);
  18.     $insertData = (array) $insertData[0];
  20.     $this->assertSame(1, $insertData['id']);
  21.     $this->assertSame('小牛', $insertData['name']);
  22.     $this->assertSame('I love movie.', $insertData['content']);
  23.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  24. }

delete 删除数据 delete (支持原生 SQL)

  1. public function testDelete(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertSame(1, $connect->insert('insert into guest_book (name, content) values (?, ?)', ['tom', 'I love movie.']));
  7.     $insertData = $connect->select('select * from guest_book where id = :id', ['id' => 1]);
  8.     $insertData = (array) $insertData[0];
  10.     $this->assertSame(1, $insertData['id']);
  11.     $this->assertSame('tom', $insertData['name']);
  12.     $this->assertSame('I love movie.', $insertData['content']);
  13.     $this->assertStringContainsString(date('Y-m'), $insertData['create_at']);
  15.     $this->assertSame(1, $connect->delete('delete from guest_book where id = ?', [1]));
  16.     $this->assertSame(0, $connect->table('guest_book')->findCount());
  17. }

transaction 执行数据库事务

  1. public function testTransaction(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  15.     $connect->transaction(function ($connect) {
  16.         $connect
  17.             ->table('guest_book')
  18.             ->where('id', 1)
  19.             ->delete();
  21.         $this->assertSame(1, $connect->table('guest_book')->findCount());
  23.         $connect
  24.             ->table('guest_book')
  25.             ->where('id', 2)
  26.             ->delete();
  28.         $this->assertSame(0, $connect->table('guest_book')->findCount());
  29.     });
  31.     $this->assertSame(0, $connect->table('guest_book')->findCount());
  32. }

transaction 执行数据库事务回滚例子

  1. public function testTransactionRollback(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  15.     $this->assertFalse($connect->inTransaction());
  17.     try {
  18.         $connect->transaction(function ($connect) {
  19.             $connect->table('guest_book')->where('id', 1)->delete();
  21.             $this->assertSame(1, $connect->table('guest_book')->findCount());
  23.             $this->assertTrue($connect->inTransaction());
  25.             throw new Exception('Will rollback');
  26.             $connect->table('guest_book')->where('id', 2)->delete();
  27.         });
  28.     } catch (Throwable $e) {
  29.         $this->assertSame('Will rollback', $e->getMessage());
  30.     }
  32.     $this->assertFalse($connect->inTransaction());
  34.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  35. }

beginTransaction.commit 启动事务和用于非自动提交状态下面的查询提交

  1. public function testTransactionByCustom(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  15.     $connect->beginTransaction();
  17.     $connect->table('guest_book')->where('id', 1)->delete();
  19.     $this->assertSame(1, $connect->table('guest_book')->findCount());
  21.     $connect->table('guest_book')->where('id', 2)->delete();
  23.     $this->assertSame(0, $connect->table('guest_book')->findCount());
  25.     $connect->commit();
  27.     $this->assertSame(0, $connect->table('guest_book')->findCount());
  28. }

beginTransaction.rollBack 启动事务和事务回滚

  1. public function testTransactionRollbackByCustom(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  15.     $this->assertFalse($connect->inTransaction());
  17.     try {
  18.         $connect->beginTransaction();
  20.         $connect
  21.             ->table('guest_book')
  22.             ->where('id', 1)
  23.             ->delete();
  25.         $this->assertSame(1, $connect->table('guest_book')->findCount());
  27.         $this->assertTrue($connect->inTransaction());
  29.         throw new Exception('Will rollback');
  30.         $connect->table('guest_book')->where('id', 2)->delete();
  32.         $connect->commit();
  33.     } catch (Throwable $e) {
  34.         $this->assertSame('Will rollback', $e->getMessage());
  36.         $connect->rollBack();
  37.     }
  39.     $this->assertFalse($connect->inTransaction());
  41.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  42. }

procedure 查询存储过程数据记录

  1. public function testCallProcedure(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $result = $connect->procedure('CALL test_procedure(0)');
  15.     $data = <<<'eot'
  16.         [
  17.             [
  18.                 {
  19.                     "name": "tom"
  20.                 },
  21.                 {
  22.                     "name": "tom"
  23.                 }
  24.             ],
  25.             [
  26.                 {
  27.                     "content": "I love movie."
  28.                 }
  29.             ]
  30.         ]
  31.         eot;
  33.     $this->assertSame(
  34.         $data,
  35.         $this->varJson(
  36.             $result
  37.         )
  38.     );
  39. }

procedure 查询存储过程数据记录支持参数绑定

  1. public function testCallProcedure2(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $result = $connect->procedure('CALL test_procedure2(0,:name)', [
  14.         'name' => [null, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 200],
  15.     ]);
  17.     $data = <<<'eot'
  18.         [
  19.             [
  20.                 {
  21.                     "content": "I love movie."
  22.                 }
  23.             ],
  24.             [
  25.                 {
  26.                     "_name": "tom"
  27.                 }
  28.             ]
  29.         ]
  30.         eot;
  32.     $this->assertSame(
  33.         $data,
  34.         $this->varJson(
  35.             $result
  36.         )
  37.     );
  38. }

查询存储过程数据支持原生方法

  1. public function testCallProcedure3(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $connect
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $pdoStatement = $connect->pdo(true)->prepare('CALL test_procedure2(0,:name)');
  14.     $outName = null;
  15.     $pdoStatement->bindParam(':name', $outName, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 200);
  16.     $pdoStatement->execute();
  18.     $result = [];
  19.     do {
  20.         try {
  21.             $result[] = $pdoStatement->fetchAll(PDO::FETCH_OBJ);
  22.         } catch (PDOException $e) {
  23.         }
  24.     } while ($pdoStatement->nextRowset());
  26.     $data = <<<'eot'
  27.         [
  28.             [
  29.                 {
  30.                     "content": "I love movie."
  31.                 }
  32.             ],
  33.             [
  34.                 {
  35.                     "_name": "tom"
  36.                 }
  37.             ]
  38.         ]
  39.         eot;
  41.     $this->assertSame(
  42.         $data,
  43.         $this->varJson(
  44.             $result
  45.         )
  46.     );
  47. }

查询存储过程数据支持缓存

procedure 是一个底层查询方法支持直接设置缓存。

procedure 原型

  1. # Leevel\Database\Database::procedure
  2. /**
  3.  * 查询存储过程数据记录.
  4.  *
  5.  * @param bool|int $master
  6.  */
  7. public function procedure(string $sql, array $bindParams = [], $master = false, ?string $cacheName = null, ?int $cacheExpire = null, ?string $cacheConnect = null): array;
  1. public function testCacheProcedure(): void
  2. {
  3.     $manager = $this->createDatabaseManager();
  5.     $data = ['name' => 'tom', 'content' => 'I love movie.'];
  7.     for ($n = 0; $n <= 1; $n++) {
  8.         $manager
  9.             ->table('guest_book')
  10.             ->insert($data);
  11.     }
  13.     $cacheDir = dirname(__DIR__).'/databaseCacheManager';
  14.     $cacheFile = $cacheDir.'/testcachekey.php';
  16.     $result = $manager
  17.         ->procedure('CALL test_procedure(0)');
  18.     $this->assertFileNotExists($cacheFile);
  19.     $data = <<<'eot'
  20.         [
  21.             [
  22.                 {
  23.                     "name": "tom"
  24.                 },
  25.                 {
  26.                     "name": "tom"
  27.                 }
  28.             ],
  29.             [
  30.                 {
  31.                     "content": "I love movie."
  32.                 }
  33.             ]
  34.         ]
  35.         eot;
  36.     $this->assertSame(
  37.         $data,
  38.         $this->varJson(
  39.             $result
  40.         )
  41.     );
  43.     $resultWithoutCache = $manager
  44.         ->procedure('CALL test_procedure(0)', [], false, 'testcachekey');
  45.     $this->assertFileExists($cacheFile);
  46.     // cached data
  47.     $resultWithCache = $manager
  48.         ->procedure('CALL test_procedure(0)', [], false, 'testcachekey');
  49.     $this->assertFileExists($cacheFile);
  50.     $this->assertSame(
  51.         $data,
  52.         $this->varJson(
  53.             $resultWithCache
  54.         )
  55.     );
  56.     $this->assertEquals($result, $resultWithCache);
  57.     $this->assertFalse($result === $resultWithCache);
  58.     $this->assertEquals($resultWithCache, $resultWithoutCache);
  59. }

pdo 返回 PDO 查询连接

  1. public function testPdo(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertNull($connect->pdo(IDatabase::MASTER));
  6.     $this->assertInstanceof(PDO::class, $connect->pdo(true));
  7.     $this->assertInstanceof(PDO::class, $connect->pdo(IDatabase::MASTER));
  8.     $this->assertNull($connect->pdo(5));
  10.     $connect->close();
  11. }

setSavepoints 设置是否启用部分事务回滚保存点

  1. public function testBeginTransactionWithCreateSavepoint(): void
  2. {
  3.     if (isset($_SERVER['TRAVIS_COMMIT'])) {
  4.         $this->markTestSkipped('Mysql of travis-ci not support savepoint.');
  6.         return;
  7.     }
  9.     $connect = $this->createDatabaseConnect();
  11.     $connect->setSavepoints(true);
  12.     $connect->beginTransaction();
  13.     $connect
  14.         ->table('guest_book')
  15.         ->insert(['name' => 'tom']); // `tom` will not rollBack
  17.     $connect->beginTransaction();
  18.     $this->assertSame('SAVEPOINT trans2', $connect->getLastSql());
  20.     $connect
  21.         ->table('guest_book')
  22.         ->insert(['name' => 'jerry']);
  24.     $connect->rollBack();
  25.     $this->assertSame('ROLLBACK TO SAVEPOINT trans2', $connect->getLastSql());
  26.     $connect->commit();
  28.     $book = $connect
  29.         ->table('guest_book')
  30.         ->where('id', 1)
  31.         ->findOne();
  33.     $this->assertSame(1, $connect->table('guest_book')->findCount());
  34.     $this->assertSame('tom', $book->name);
  35. }

setSavepoints 设置是否启用部分事务提交保存点

  1. public function testCommitWithReleaseSavepoint(): void
  2. {
  3.     if (isset($_SERVER['TRAVIS_COMMIT'])) {
  4.         $this->markTestSkipped('Mysql of travis-ci not support savepoint.');
  6.         return;
  7.     }
  9.     $connect = $this->createDatabaseConnect();
  10.     $connect->setSavepoints(true);
  11.     $connect->beginTransaction();
  13.     $connect
  14.         ->table('guest_book')
  15.         ->insert(['name' => 'tom']);
  17.     $connect->beginTransaction();
  18.     $this->assertSame('SAVEPOINT trans2', $connect->getLastSql());
  20.     $connect
  21.         ->table('guest_book')
  22.         ->insert(['name' => 'jerry']);
  24.     $connect->commit();
  25.     $this->assertSame('RELEASE SAVEPOINT trans2', $connect->getLastSql());
  26.     $connect->commit();
  28.     $book = $connect
  29.         ->table('guest_book')
  30.         ->where('id', 1)
  31.         ->findOne();
  32.     $book2 = $connect
  33.         ->table('guest_book')
  34.         ->where('id', 2)
  35.         ->findOne();
  37.     $this->assertSame(2, $connect->table('guest_book')->findCount());
  38.     $this->assertSame('tom', $book->name);
  39.     $this->assertSame('jerry', $book2->name);
  40. }

numRows 返回影响记录

  1. public function testNumRows(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertSame(0, $connect->numRows());
  7.     $connect
  8.         ->table('guest_book')
  9.         ->insert(['name' => 'jerry', 'content' => '']);
  11.     $this->assertSame(1, $connect->numRows());
  13.     $connect
  14.         ->table('guest_book')
  15.         ->where('id', 1)
  16.         ->update(['name' => 'jerry']);
  18.     $this->assertSame(0, $connect->numRows());
  20.     $connect
  21.         ->table('guest_book')
  22.         ->where('id', 1)
  23.         ->update(['name' => 'tom']);
  25.     $this->assertSame(1, $connect->numRows());
  26. }

数据库主从

数据库配置项 distributed 表示主从，如果从数据库均连接失败，则还是会走主库。

  1. public function testReadConnectDistributed(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock([
  4.         'driver'             => 'mysql',
  5.         'separate'           => false,
  6.         'distributed'        => true,
  7.         'master'             => [
  8.             'host'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['HOST'],
  9.             'port'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PORT'],
  10.             'name'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['NAME'],
  11.             'user'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['USER'],
  12.             'password' => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PASSWORD'],
  13.             'charset'  => 'utf8',
  14.             'options'  => [
  15.                 PDO::ATTR_PERSISTENT        => false,
  16.                 PDO::ATTR_CASE              => PDO::CASE_NATURAL,
  17.                 PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
  18.                 PDO::ATTR_STRINGIFY_FETCHES => false,
  19.                 PDO::ATTR_EMULATE_PREPARES  => false,
  20.                 PDO::ATTR_TIMEOUT           => 30,
  21.             ],
  22.         ],
  23.         'slave' => [
  24.             [
  25.                 'host'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['HOST'],
  26.                 'port'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PORT'],
  27.                 'name'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['NAME'],
  28.                 'user'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['USER'],
  29.                 'password' => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PASSWORD'],
  30.                 'charset'  => 'utf8',
  31.                 'options'  => [
  32.                     PDO::ATTR_PERSISTENT        => false,
  33.                     PDO::ATTR_CASE              => PDO::CASE_NATURAL,
  34.                     PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
  35.                     PDO::ATTR_STRINGIFY_FETCHES => false,
  36.                     PDO::ATTR_EMULATE_PREPARES  => false,
  37.                     PDO::ATTR_TIMEOUT           => 30,
  38.                 ],
  39.             ],
  40.             [
  41.                 'host'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['HOST'],
  42.                 'port'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PORT'],
  43.                 'name'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['NAME'],
  44.                 'user'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['USER'],
  45.                 'password' => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PASSWORD'],
  46.                 'charset'  => 'utf8',
  47.                 'options'  => [
  48.                     PDO::ATTR_PERSISTENT        => false,
  49.                     PDO::ATTR_CASE              => PDO::CASE_NATURAL,
  50.                     PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
  51.                     PDO::ATTR_STRINGIFY_FETCHES => false,
  52.                     PDO::ATTR_EMULATE_PREPARES  => false,
  53.                     PDO::ATTR_TIMEOUT           => 30,
  54.                 ],
  55.             ],
  56.         ],
  57.     ]);
  59.     $this->assertInstanceof(PDO::class, $connect->pdo());
  61.     $connect->close();
  62. }

数据库读写分离

数据库配置项 separate 表示读写分离，如果从数据库均连接失败，则读数据还是会走主库。

  1. public function testReadConnectDistributedButAllInvalidAndAlsoIsSeparate(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock([
  4.         'driver'             => 'mysql',
  5.         'separate'           => true,
  6.         'distributed'        => true,
  7.         'master'             => [
  8.             'host'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['HOST'],
  9.             'port'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PORT'],
  10.             'name'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['NAME'],
  11.             'user'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['USER'],
  12.             'password' => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PASSWORD'],
  13.             'charset'  => 'utf8',
  14.             'options'  => [
  15.                 PDO::ATTR_PERSISTENT        => false,
  16.                 PDO::ATTR_CASE              => PDO::CASE_NATURAL,
  17.                 PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
  18.                 PDO::ATTR_STRINGIFY_FETCHES => false,
  19.                 PDO::ATTR_EMULATE_PREPARES  => false,
  20.                 PDO::ATTR_TIMEOUT           => 30,
  21.             ],
  22.         ],
  23.         'slave' => [
  24.             [
  25.                 'host'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['HOST'],
  26.                 'port'     => '5555', // not invalid
  27.                 'name'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['NAME'],
  28.                 'user'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['USER'],
  29.                 'password' => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PASSWORD'],
  30.                 'charset'  => 'utf8',
  31.                 'options'  => [
  32.                     PDO::ATTR_PERSISTENT        => false,
  33.                     PDO::ATTR_CASE              => PDO::CASE_NATURAL,
  34.                     PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
  35.                     PDO::ATTR_STRINGIFY_FETCHES => false,
  36.                     PDO::ATTR_EMULATE_PREPARES  => false,
  37.                     PDO::ATTR_TIMEOUT           => 30,
  38.                 ],
  39.             ],
  40.             [
  41.                 'host'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['HOST'],
  42.                 'port'     => '6666', // not invalid
  43.                 'name'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['NAME'],
  44.                 'user'     => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['USER'],
  45.                 'password' => $GLOBALS['LEEVEL_ENV']['DATABASE']['MYSQL']['PASSWORD'],
  46.                 'charset'  => 'utf8',
  47.                 'options'  => [
  48.                     PDO::ATTR_PERSISTENT        => false,
  49.                     PDO::ATTR_CASE              => PDO::CASE_NATURAL,
  50.                     PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
  51.                     PDO::ATTR_STRINGIFY_FETCHES => false,
  52.                     PDO::ATTR_EMULATE_PREPARES  => false,
  53.                     PDO::ATTR_TIMEOUT           => 30,
  54.                 ],
  55.             ],
  56.         ],
  57.     ]);
  59.     $this->assertInstanceof(PDO::class, $connect->pdo());
  60.     $this->assertInstanceof(PDO::class, $connect->pdo());
  62.     $connect->close();
  63. }

databaseSelect 返回查询对象

  1. public function testDatabaseSelectIsNotInit(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  4.     $this->assertInstanceof(Select::class, $connect->databaseSelect());
  5. }

getTableNames 取得数据库表名列表

  1. public function testGetTableNames(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  4.     $result = $connect->getTableNames('test');
  5.     $this->assertTrue(in_array('guest_book', $result, true));
  6. }

getTableColumns 取得数据库表字段信息

  1. public function testGetTableColumns(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  4.     $result = $connect->getTableColumns('guest_book');
  6.     $sql = <<<'eot'
  7.         {
  8.             "list": {
  9.                 "id": {
  10.                     "field": "id",
  11.                     "type": "bigint(20)",
  12.                     "collation": null,
  13.                     "null": false,
  14.                     "key": "PRI",
  15.                     "default": null,
  16.                     "extra": "auto_increment",
  17.                     "comment": "ID",
  18.                     "primary_key": true,
  19.                     "type_name": "bigint",
  20.                     "type_length": "20",
  21.                     "auto_increment": true
  22.                 },
  23.                 "name": {
  24.                     "field": "name",
  25.                     "type": "varchar(64)",
  26.                     "collation": "utf8_general_ci",
  27.                     "null": false,
  28.                     "key": "",
  29.                     "default": "",
  30.                     "extra": "",
  31.                     "comment": "名字",
  32.                     "primary_key": false,
  33.                     "type_name": "varchar",
  34.                     "type_length": "64",
  35.                     "auto_increment": false
  36.                 },
  37.                 "content": {
  38.                     "field": "content",
  39.                     "type": "longtext",
  40.                     "collation": "utf8_general_ci",
  41.                     "null": false,
  42.                     "key": "",
  43.                     "default": null,
  44.                     "extra": "",
  45.                     "comment": "评论内容",
  46.                     "primary_key": false,
  47.                     "type_name": "longtext",
  48.                     "type_length": null,
  49.                     "auto_increment": false
  50.                 },
  51.                 "create_at": {
  52.                     "field": "create_at",
  53.                     "type": "datetime",
  54.                     "collation": null,
  55.                     "null": false,
  56.                     "key": "",
  57.                     "default": "CURRENT_TIMESTAMP",
  58.                     "extra": "",
  59.                     "comment": "创建时间",
  60.                     "primary_key": false,
  61.                     "type_name": "datetime",
  62.                     "type_length": null,
  63.                     "auto_increment": false
  64.                 }
  65.             },
  66.             "primary_key": [
  67.                 "id"
  68.             ],
  69.             "auto_increment": "id",
  70.             "table_collation": "utf8_general_ci",
  71.             "table_comment": "留言板"
  72.         }
  73.         eot;
  75.     $this->assertSame(
  76.         $sql,
  77.         $this->varJson(
  78.             $result
  79.         )
  80.     );
  81. }

getRawSql 游标查询

getRawSql 返回原生查询真实 SQL，以便于更加直观。

getRawSql 原型

  1. # Leevel\Database\Database::getRawSql
  2. /**
  3.  * 从 PDO 预处理语句中获取原始 SQL 查询字符串.
  4.  *
  5.  * - This method borrows heavily from the pdo-debug package and is part of the pdo-debug package.
  6.  *
  7.  * @see https://github.com/panique/pdo-debug/blob/master/pdo-debug.php
  8.  * @see https://stackoverflow.com/questions/210564/getting-raw-sql-query-string-from-pdo-prepared-statements
  9.  * @see http://php.net/manual/en/pdo.constants.php
  10.  */
  11. public static function getRawSql(string $sql, array $bindParams): string;
  1. public function testGetRawSql(): void
  2. {
  3.     $sql = Database::getRawSql('SELECT * FROM guest_book WHERE id = :id', [
  4.         ':id' => [1],
  5.     ]);
  6.     $this->assertSame($sql, 'SELECT * FROM guest_book WHERE id = 1');
  7. }