Same hashing function MySQL and PostgreSQL

Question

I'm looking for an hashing function which is implemented in both PostgreSQL and MySQL, which can hash a string and return an integer.

For example I tried to use hashtext function in PostgreSQL, but I haven't found the same function implementation in MySQL.

I've seen that MySQL supports md5, but it returns a string instead of an integer.

md5 does return a string, but in my usecase I would need an integer returned. For example, hashtext() function in postgresql does return an integer — Samuele Ceroni
– Samuele Ceroni, Commented Mar 30, 2022 at 22:24
This would go a lot better if you provided some example data and a rationale for what you are doing. Add answers as update to question. — Adrian Klaver
– Adrian Klaver, Commented Mar 30, 2022 at 22:33
i doubt that an integer will not have many collisions, you can ways program doxygen.postgresql.org/… as function or procedure to generatw — nbk
– nbk, Commented Mar 30, 2022 at 22:38
Can you please edit your question to post a link to documentation / explanation for the hashtext() function you mention? — O. Jones
– O. Jones, Commented Mar 30, 2022 at 22:43

O. Jones · Accepted Answer · 2022-03-30 22:46:22Z

3

The Postgres query

SELECT encode(sha224('foo'::bytea), 'hex')

and the MySQL query

SELECT SHA2('foo', 224)

both return the same number encoded as a hex string, 0808f64e60d58979fcb676c96ec938270dea42445aeefcd3a4e6f8db.

MySQL has the CRC32() function to return an unsigned 32-bit cyclic redundancy check number on a a string. You can create the same capability with a stored function in postgres. For example: CRC32 function with PL/pgSQL

They get the same value. Postgres fiddle MySql fiddle.

answered Mar 30, 2022 at 22:46

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Same hashing function MySQL and PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related