Laravel中PDO插入二进制UUID至闭包表报错的解决咨询

这个错误的核心原因是PDO默认将所有绑定参数视为字符串类型，当你传入二进制UUID时，它会自动转义这些字节序列，导致插入到BINARY(16)列时格式不匹配。下面给你两种可靠的修复方案：

方案一：使用原生PDO手动绑定参数并指定二进制类型 #

直接获取PDO连接实例，手动绑定每个参数并指定PDO::PARAM_LOB类型（该类型用于处理二进制数据，能避免字符串转义）：

public function insertNode($tenantUuid, $ancestorUuid, $descendantUuid) { 
    $table = $this->table; 
    $query = " INSERT INTO {$table} (tenant_uuid, ancestor, descendant, depth) 
               SELECT :tenantUuid1, tbl.ancestor, :descendantUuid1, tbl.depth+1 
               FROM {$table} AS tbl WHERE tbl.descendant = :ancestorUuid1 
               UNION ALL 
               SELECT :tenantUuid2, :descendantUuid2, :descendantUuid3, 0 "; 

    // 获取原生PDO连接
    $pdo = DB::connection($this->getConnectionName())->getPdo();
    $stmt = $pdo->prepare($query);

    // 绑定每个二进制参数，指定类型为PDO::PARAM_LOB
    $stmt->bindParam(':tenantUuid1', $tenantUuid, PDO::PARAM_LOB);
    $stmt->bindParam(':descendantUuid1', $descendantUuid, PDO::PARAM_LOB);
    $stmt->bindParam(':ancestorUuid1', $ancestorUuid, PDO::PARAM_LOB);
    $stmt->bindParam(':tenantUuid2', $tenantUuid, PDO::PARAM_LOB);
    $stmt->bindParam(':descendantUuid2', $descendantUuid, PDO::PARAM_LOB);
    $stmt->bindParam(':descendantUuid3', $descendantUuid, PDO::PARAM_LOB);

    $stmt->execute();
}

方案二：使用Laravel查询构建器重构查询 #

如果你更倾向于使用Laravel的查询构建器而不是原生SQL，可以用insertUsing和unionAll来构建查询，Laravel会自动处理参数的正确绑定类型：

public function insertNode($tenantUuid, $ancestorUuid, $descendantUuid) { 
    $connection = DB::connection($this->getConnectionName());
    $table = $this->table;

    // 构建第一个SELECT部分：获取父节点的所有祖先并关联新节点
    $ancestorQuery = $connection->table($table)
        ->select(
            $connection->raw('? as tenant_uuid', [$tenantUuid]),
            'ancestor',
            $connection->raw('? as descendant', [$descendantUuid]),
            $connection->raw('depth + 1 as depth')
        )
        ->where('descendant', $ancestorUuid);

    // 构建第二个SELECT部分：插入节点自身的闭包记录（depth=0）
    $selfQuery = $connection->table($table)
        ->select(
            $connection->raw('? as tenant_uuid', [$tenantUuid]),
            $connection->raw('? as ancestor', [$descendantUuid]),
            $connection->raw('? as descendant', [$descendantUuid]),
            $connection->raw('0 as depth')
        );

    // 合并两个查询并执行插入
    $connection->table($table)
        ->insertUsing(
            ['tenant_uuid', 'ancestor', 'descendant', 'depth'],
            $ancestorQuery->unionAll($selfQuery)
        );
}

关键说明 #

• PDO::PARAM_LOB告诉PDO将参数视为二进制数据，不会进行字符串转义，确保原始字节序列被正确插入到BINARY(16)列中。
• 因为你提到插入数据均非用户生成，所以无需担心SQL注入问题，但正确的参数类型绑定仍然是保证二进制数据正确性的必要步骤。

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