为什么PostgreSQL会在类似的查询中破坏我的十六进制输入？
为什么PostgreSQL会在类似的查询中破坏我的十六进制输入？
提问于 2023-02-13 17:08:42
在LIKE列上的一些bytea查询中，PostgreSQL正在破坏输入字节序列。为什么会发生这种事？
Expected query plan: b >= '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea
From actual query plan: b >= '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c29'::bytea注意字节序列中的差异(空格显示丢失的字节)：
Expected: \x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29
Actual: \x4cbf996e2c527620d067571c6f650643edfc dc0bd39c29表：
=> \d tbl
table "tbl"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | int | | |
b | bytea | | |查询：
SELECT * FROM tbl WHERE b LIKE '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea||'%'查询规划器：
EXPLAIN ANALYZE SELECT * FROM tbl WHERE b LIKE '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea||'%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Index Scan using idx on tbl (cost=0.56..8.58 rows=670 width=172) (actual time=0.020..0.021 rows=0 loops=1)
Index Cond: ((b >= '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c29'::bytea) AND (b < '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c2a'::bytea))
Filter: (b ~~ '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c2925'::bytea)
Planning Time: 0.090 ms
Execution Time: 0.033 ms
(5 rows)回答 1
Database Administration用户
发布于 2023-02-13 17:38:12
弄明白了！PostgreSQL将\x5c解释为反斜杠，这是LIKE表达式中的转义序列。要修复查询，只需重复5c以正确地转义反斜杠：
EXPLAIN ANALYZE SELECT * FROM tbl WHERE b LIKE '\x4cbf996e2c527620d067571c6f650643edfc5c5cdc0bd39c29'::bytea||'%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Index Scan using idx on tbl (cost=0.56..8.58 rows=670 width=172) (actual time=0.020..0.021 rows=0 loops=1)
Index Cond: ((b >= '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea) AND (b < '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c2a'::bytea))
Filter: (b ~~ '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c2925'::bytea)
Planning Time: 0.090 ms
Execution Time: 0.033 ms
(5 rows)页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/323449
复制相关文章
相似问题
腾讯云开发者
