Zend插入用户并将值设置为max()+1

use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Expression;

// 初始化数据库连接
$adapter = new Adapter([
    'driver' => 'pdo_mysql',
    'database' => 'your_database',
    'username' => 'your_username',
    'password' => 'your_password',
    'hostname' => 'localhost'
]);

// 创建SQL对象
$sql = new Sql($adapter);

// 开始事务
$adapter->getDriver()->getConnection()->beginTransaction();

try {
    // 获取当前最大user_id并加一
    $select = $sql->select('users')
                 ->columns(['max_id' => new Expression('MAX(user_id) + 1')]);

    $statement = $sql->prepareStatementForSqlObject($select);
    $result = $statement->execute();
    $maxIdRow = $result->current();

    if ($maxIdRow && isset($maxIdRow['max_id'])) {
        $newUserId = $maxIdRow['max_id'];

        // 插入新用户记录
        $insert = $sql->insert('users')
                      ->values([
                          'user_id' => $newUserId,
                          'username' => 'exampleUser',
                          // 其他字段...
                      ]);

        $statement = $sql->prepareStatementForSqlObject($insert);
        $statement->execute();

        // 提交事务
        $adapter->getDriver()->getConnection()->commit();
    } else {
        throw new \Exception("无法获取最大ID");
    }
} catch (\Exception $e) {
    // 回滚事务
    $adapter->getDriver()->getConnection()->rollback();
    echo "发生错误：" . $e->getMessage();
}