PHP PDO lastInsertId()失效：深度解析与连接复用策略

class Dbh {
    // 假设这个connect方法每次都返回一个新的PDO实例
    protected function connect(): PDO {
        // 这是一个简化的示例，实际中应包含错误处理和配置
        return new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'user', 'password');
    }
}

class Customer extends Dbh {
    protected function setAddCustomer($c_name, $c_address): ?string {
        // 第一次调用connect()，建立连接A
        $pdo_connection_A = $this->connect();
        $stmt = $pdo_connection_A->prepare('INSERT INTO customer (c_name, c_address) VALUES (?, ?);');

        if(!$stmt->execute(array($c_name, $c_address))) {
            // 专业的错误处理应抛出异常或记录日志，而非直接重定向
            error_log("Failed to insert customer: " . implode(", ", $stmt->errorInfo()));
            throw new RuntimeException("Customer insertion failed.");
        }

        // 第二次调用connect()，建立连接B（一个全新的连接！）
        $pdo_connection_B = $this->connect();
        // 在连接B上，并没有执行过INSERT操作
        $last_id = $pdo_connection_B->lastInsertId(); // 因此这里会返回空值或0
        return $last_id;
    }
}

class Dbh {
    private ?PDO $connection = null; // 使用可空类型声明，PHP 7.4+

    protected function connect(): PDO {
        // 只有当连接不存在时才创建
        if ($this->connection === null) {
            try {
                $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
                $user = 'your_username';
                $pass = 'your_password';
                $options = [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,    // 抛出异常，便于错误处理
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,          // 默认以关联数组形式返回结果集
                    PDO::ATTR_EMULATE_PREPARES   => false,                     // 禁用模拟预处理，提高安全性
                ];
                $this->connection = new PDO($dsn, $user, $pass, $options);
            } catch (PDOException $e) {
                error_log("Database connection failed: " . $e->getMessage());
                throw new RuntimeException("无法连接到数据库。");
            }
        }
        return $this->connection; // 返回已存在的或新创建的连接
    }
}

class Customer extends Dbh {
    protected function setAddCustomer($c_name, $c_address): string {
        $pdo = $this->connect(); // 总是获取同一个PDO实例

        $stmt = $pdo->prepare('INSERT INTO customer (c_name, c_address) VALUES (?, ?);');

        if(!$stmt->execute(array($c_name, $c_address))) {
            error_log("Failed to insert customer: " . implode(", ", $stmt->errorInfo()));
            throw new RuntimeException("客户信息插入失败。");
        }

        // 现在，lastInsertId() 在执行INSERT操作的同一个连接上被调用
        return $pdo->lastInsertId();
    }
}

// 首先，在应用启动时创建一次PDO连接实例
try {
    $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
    $user = 'your_username';
    $pass = 'your_password';
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    $pdoConnection = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    error_log("Application level database connection failed: " . $e->getMessage());
    die("系统错误：无法初始化数据库连接。");
}

class CustomerService {
    private PDO $connection;

    // 通过构造函数注入PDO连接
    public function __construct(PDO $connection) {
        $this->connection = $connection;
    }

    public function addCustomer($c_name, $c_address): string {
        $stmt = $this->connection->prepare('INSERT INTO customer (c_name, c_address) VALUES (?, ?);');

        if(!$stmt->execute(array($c_name, $c_address))) {
            error_log("Failed to add customer: " . implode(", ", $stmt->errorInfo()));
            throw new RuntimeException("添加客户失败。");
        }

        return $this->connection->lastInsertId();
    }
}

// 在需要使用CustomerService的地方，传入已创建的PDO连接
$customerService = new CustomerService($pdoConnection);
// 调用方法
// $newCustomerId = $customerService->addCustomer("Alice Smith", "456 Oak Ave");
// echo "新客户ID: " . $newCustomerId;