Laravel中PostgreSQL处理 Unicode排序报错解决方案

方案1：用chr(0)替代字符串转义表示空字符 #

直接修改orderByRaw中的正则替换逻辑，使用PostgreSQL内置的chr(0)函数表示空字符（对应\u0000），彻底避免转义冲突：

$query = Job::query();

$sortField = $request->input('sortField', 'id');
$sortOrder = $request->input('sortOrder', 'asc');
$direction = $sortOrder == '1' ? 'asc' : 'desc';

if (!in_array($sortField, ['id', 'UUID', 'class', 'created_at', 'available_at', 'reserved_at', 'attempts', 'queue'])) {
    $sortField = 'id';
}

if ($sortField === 'UUID') {
    $query->orderByRaw("regexp_replace(payload::text, chr(0), '', 'g')::json->>'uuid' {$direction}");
} elseif ($sortField === 'class') {
    $query->orderByRaw("regexp_replace(payload::text, chr(0), '', 'g')::json->>'displayName' {$direction}");
} else {
    $query->orderBy($sortField, $direction);
}

$data = $query->paginate((int)$request->get("perPage", 10));
return new DBJsonResponse($data);

方案2：参数绑定传递空字符（更安全） #

用参数绑定的方式传递空字符，既避免转义问题，也符合安全SQL实践：

$query = Job::query();

$sortField = $request->input('sortField', 'id');
$sortOrder = $request->input('sortOrder', 'asc');
$direction = $sortOrder == '1' ? 'asc' : 'desc';
$nullChar = "\x00"; // PHP中直接表示空字符

if (!in_array($sortField, ['id', 'UUID', 'class', 'created_at', 'available_at', 'reserved_at', 'attempts', 'queue'])) {
    $sortField = 'id';
}

if ($sortField === 'UUID') {
    $query->orderByRaw("regexp_replace(payload::text, ?, '', 'g')::json->>'uuid' ?", [$nullChar, $direction]);
} elseif ($sortField === 'class') {
    $query->orderByRaw("regexp_replace(payload::text, ?, '', 'g')::json->>'displayName' ?", [$nullChar, $direction]);
} else {
    $query->orderBy($sortField, $direction);
}

$data = $query->paginate((int)$request->get("perPage", 10));
return new DBJsonResponse($data);

方案3：添加生成列优化性能（推荐） #

如果jobs表数据量较大，每次排序都执行正则替换会损耗性能，建议在PostgreSQL中添加生成列，提前处理空字符并转换为JSON：

先执行SQL创建生成列：

ALTER TABLE jobs 
ADD COLUMN payload_clean json 
GENERATED ALWAYS AS (regexp_replace(payload::text, chr(0), '', 'g')::json) STORED;

之后控制器代码可以简化为：

$query = Job::query();

$sortField = $request->input('sortField', 'id');
$sortOrder = $request->input('sortOrder', 'asc');
$direction = $sortOrder == '1' ? 'asc' : 'desc';

if (!in_array($sortField, ['id', 'UUID', 'class', 'created_at', 'available_at', 'reserved_at', 'attempts', 'queue'])) {
    $sortField = 'id';
}

if ($sortField === 'UUID') {
    $query->orderBy('payload_clean->>uuid', $direction);
} elseif ($sortField === 'class') {
    $query->orderBy('payload_clean->>displayName', $direction);
} else {
    $query->orderBy($sortField, $direction);
}

$data = $query->paginate((int)$request->get("perPage", 10));
return new DBJsonResponse($data);

错误原因说明 #

你之前的代码报错是因为：PHP字符串中的\\u0000会被解析为\u0000传入SQL，但PostgreSQL不支持直接用\u0000表示空字符——它需要用chr(0)函数或者特定的Unicode转义格式，直接写\u0000会触发语法错误。

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