如何为使用实时数据库数据的PHP方法编写可靠单元测试？

Great question—testing database-dependent PHP code without getting burned by real data changes is such a common (and frustrating) pain point, especially when you’ve got a whole fleet of similar methods to cover. Let’s break down the industry-leading approaches that’ll make your tests rock-solid, repeatable, and immune to shifts in real-world data or pricing models.

The first critical step is to stop hardcoding database connections inside your methods. Instead, pass the database client (like PDO or a custom repository class) as a parameter. This lets you swap out the real database with a test-friendly replacement during unit tests.

Before (tight coupling):

function calculateProductTax($productId) {
    $db = new PDO('mysql:host=prod-db;dbname=store', 'user', 'pass');
    $stmt = $db->prepare("SELECT base_price FROM products WHERE id = ?");
    $stmt->execute([$productId]);
    $price = $stmt->fetchColumn();
    return $price * 0.07; // 7% tax
}

After (decoupled with DI):

function calculateProductTax($productId, PDO $db) {
    $stmt = $db->prepare("SELECT base_price FROM products WHERE id = ?");
    $stmt->execute([$productId]);
    $price = $stmt->fetchColumn();
    return $price * 0.07;
}

Now your method doesn’t care where the database comes from—just that it adheres to the PDO interface.

Once you’ve decoupled with DI, you can use test doubles to mimic database behavior without touching real data. There are two main types to consider:

Mock Objects (For Strict Behavior Verification) #

Mocks let you define exactly how the database should respond to specific calls. PHPUnit’s built-in MockBuilder is perfect for this. For example:

public function testCalculateProductTaxAppliesCorrectRate() {
    // Mock the PDOStatement to return a fixed price
    $mockStmt = $this->createMock(PDOStatement::class);
    $mockStmt->method('execute')->willReturn(true);
    $mockStmt->method('fetchColumn')->willReturn(100); // Simulate $100 base price

    // Mock the PDO instance to return our mock statement
    $mockDb = $this->createMock(PDO::class);
    $mockDb->method('prepare')->willReturn($mockStmt);

    // Test the method with our mock database
    $tax = calculateProductTax(123, $mockDb);

    // Assert the tax calculation is correct
    $this->assertEquals(7, $tax);
}

Mocks are ideal when you need to verify that specific database calls are made (e.g., ensuring prepare() is called with the right query).

Fake Databases (For Real Query Execution) #

If you want to test actual SQL queries without relying on production data, use an in-memory database like SQLite. This lets you create a temporary, isolated database for each test:

protected PDO $db;

public function setUp(): void {
    // Create an in-memory SQLite database
    $this->db = new PDO('sqlite::memory:');
    // Replicate your production table structure
    $this->db->exec("CREATE TABLE products (id INT, base_price DECIMAL(10,2))");
    // Insert fixed test data
    $this->db->exec("INSERT INTO products VALUES (123, 100), (456, 50)");
}

public function testCalculateProductTaxForExistingProduct() {
    $tax = calculateProductTax(123, $this->db);
    $this->assertEquals(7, $tax);
}

public function tearDown(): void {
    // No cleanup needed—SQLite in-memory db is destroyed automatically
}

Fakes are great for testing complex query logic (like joins or filters) without worrying about real data changes.

Even with fakes or mocks, you need to ensure tests don’t share state. For real test databases (not in-memory), use transactions to roll back changes after each test:

public function setUp(): void {
    $this->db = new PDO('mysql:host=test-db;dbname=store_test', 'user', 'pass');
    $this->db->beginTransaction(); // Start a transaction
    $this->db->exec("INSERT INTO products VALUES (123, 100)");
}

public function tearDown(): void {
    $this->db->rollBack(); // Undo all changes from the test
}

This guarantees every test starts with a clean, predictable dataset—no more flaky tests because one test modified data another relies on.

Since you’ve got lots of similar methods, parameterized tests will save you tons of duplicate code. PHPUnit’s @dataProvider lets you run the same test logic against multiple input/output pairs:

/**
 * @dataProvider productTaxCalculationProvider
 */
public function testProductTaxCalculations($productId, $basePrice, $expectedTax) {
    $mockStmt = $this->createMock(PDOStatement::class);
    $mockStmt->method('execute')->willReturn(true);
    $mockStmt->method('fetchColumn')->willReturn($basePrice);

    $mockDb = $this->createMock(PDO::class);
    $mockDb->method('prepare')->willReturn($mockStmt);

    $tax = calculateProductTax($productId, $mockDb);
    $this->assertEquals($expectedTax, $tax);
}

public function productTaxCalculationProvider() {
    return [
        [123, 100, 7],    // 100 * 0.07
        [456, 50, 3.5],   // 50 * 0.07
        [789, 200, 14],   // 200 * 0.07
        [999, 0, 0],      // Zero price case
    ];
}

You can extend this to cover different pricing types (promo prices, bulk discounts) by adding more entries to the data provider—no need to write a separate test for each scenario.

Don’t forget edge cases that could break your tests if real data changes:

• Products with no price (or NULL values)
• Negative prices (if your business allows them)
• Special pricing tiers (e.g., wholesale vs. retail)

Also, define "contracts" for your database interactions. For example, if your method expects a base_price column, a test that verifies the query uses that column will fail if someone renames it to price—alerting you to update both the method and test before it breaks production.

In your CI pipeline, always run tests against a dedicated test database (never production!). Automate the setup: create the database, run migrations, insert test data, run tests, then tear it down. This ensures tests are run in a consistent environment every time, regardless of local machine setups.

内容的提问来源于stack exchange，提问作者Dennis