在Postgres中，MySQL的HEX()和UNHEX()是否等效? [英] MySQL's HEX() and UNHEX() equivalent in Postgres?

问题描述

我正在将一些使用MySQL的工具转换为PostgreSQL.这样，我遇到了许多问题，但能够找到大多数东西.我遇到问题的是HEX()和UNHEX().我尝试了encode(%s, 'hex')和decode(%s, 'hex')，但实际上并没有停止使我出错，但是似乎仍然无法解决问题.有谁知道在Postgres中相当于这些功能的东西吗?

I'm in the process of converting some tools over that are using MySQL to PostgreSQL. With that, I've run into a number of issues but was able to find most everything. The one I'm having an issue with is HEX() and UNHEX(). I've tried encode(%s, 'hex') and decode(%s, 'hex') which did actually stop causing me to have errors, but it still did not seem to do the trick. Does anyone have an idea to what the equivalent of those functions would be in Postgres?

这是旧的MySQL查询:

Here is the old MySQL query:

SELECT HEX(test_table.hash),
       title,
       user,
       reason,
       description,
       url,
       performed,
       comment,
       authenticated,
       status
FROM alerts
JOIN user_responses ON test_table.hash = user_responses.hash
JOIN test_status ON test_table.hash = test_status.hash
WHERE status = %s

这是我以PostgreSQL格式更新的查询:

And here is my updated query in PostgreSQL format:

SELECT encode(test_table.hash, 'hex') as hash,
       title,
       user,
       reason,
       description,
       url,
       performed,
       comment,
       authenticated,
       status
FROM test_table
JOIN user_responses ON test_table.hash = user_responses.hash
JOIN test_status ON test_table.hash = test_status.hash
WHERE status = %s

谢谢！

推荐答案

create function hex(text) returns text language sql immutable strict as $$
  select encode($1::bytea, 'hex')
$$;

create function hex(bigint) returns text language sql immutable strict as $$
  select to_hex($1)
$$;

create function unhex(text) returns text language sql immutable strict as $$
  select encode(decode($1, 'hex'), 'escape')
$$;


select hex('abc'), hex(123), unhex(hex('PostgreSQL'));

结果:

╔════════╤═════╤════════════╗
║  hex   │ hex │   unhex    ║
╠════════╪═════╪════════════╣
║ 616263 │ 7b  │ PostgreSQL ║
╚════════╧═════╧════════════╝

这篇关于在Postgres中，MySQL的HEX()和UNHEX()是否等效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！