How to deal with binary format in MySQL?

In MySQL, some bad people use a binary format for ID, which must be transformed to other bases where there is a UUID

Hello @Unnamed and welcome to the community!

You can try to use the following expressions to cast binary columns to the number type or the character type:

CAST(CONV(`binary_column`, 2, 10) AS UNSIGNED)
CAST(`binary_column` AS CHAR(36))

Then you will be able to use that expression in conditions and aggregations similar to other columns. The only difference from an actual column in that you can’t sort by expressions in the visual mode of the Query builder.

Updated topic title.
Moved to #uncategorized.

Not UUID (

@Unnamed, CAST as CHAR produces a string with the default character set. It’s possible that you have to explicitly specify some other character set. Like this:

CAST(`binary_column` AS CHAR(36) CHARACTER SET charset_name)

ASCII and UCS2 charsets are worth a try.

Also, you can import a binary representation of a binary column with the following expression:

CONV(`binary_column`, 2, 2)

I find this:
concat(HEX(LEFT(Id,4)),'-', HEX(MID(Id,5,2)),'-', HEX(MID(Id,7,2)),'-',HEX(MID(Id,9,2)),'-',HEX(RIGHT(Id,6)))

Screenshot_2

1 Like