Search code examples
mysqlbinaryuuid

Mysql UNHEX return weird characters

I am trying to use function to convert UUID to binary for database storage efficiency using this code I got from this thread :

DELIMITER $$

CREATE FUNCTION BIN_TO_UUID(b BINARY(16), f BOOLEAN)
RETURNS CHAR(36)
DETERMINISTIC
BEGIN
   DECLARE hexStr CHAR(32);
   SET hexStr = HEX(b);
   RETURN LOWER(CONCAT(
        IF(f,SUBSTR(hexStr, 9, 8),SUBSTR(hexStr, 1, 8)), '-',
        IF(f,SUBSTR(hexStr, 5, 4),SUBSTR(hexStr, 9, 4)), '-',
        IF(f,SUBSTR(hexStr, 1, 4),SUBSTR(hexStr, 13, 4)), '-',
        SUBSTR(hexStr, 17, 4), '-',
        SUBSTR(hexStr, 21)
    ));
END$$


CREATE FUNCTION UUID_TO_BIN(uuid CHAR(36), f BOOLEAN)
RETURNS BINARY(16)
DETERMINISTIC
BEGIN
  RETURN UNHEX(CONCAT(
  IF(f,SUBSTRING(uuid, 15, 4),SUBSTRING(uuid, 1, 8)),
  SUBSTRING(uuid, 10, 4),
  IF(f,SUBSTRING(uuid, 1, 8),SUBSTRING(uuid, 15, 4)),
  SUBSTRING(uuid, 20, 4),
  SUBSTRING(uuid, 25))
  );
END$$

DELIMITER ;

On my local server(I am using Windows XAMPP with 10.4.22-MariaDB), it works as intended. When I call UUID_TO_BIN(UUID(),0) it returns something like 0x11edbe57536d750480c2c025a57a3115. When i tried it on my production server (I am using linux with 10.5.16-MariaDB), it returns weird characters like this. When I try to return the UUID back using BIN_TO_UUID(UUID_TO_BIN(UUID(),1),1) it does return it back to the original UUID in both local and production server.

But what I want is to store the UUID_TO_BIN result in binary and hexadecimal base representation(not in some weird characters) just like in my local server.

I have hunch that it might be because of the database's/table's charset or collation difference. My local server database uses utf8mb4_general_ci while my production server uses latin1_swedish_ci.

I am too afraid to change the database's/table's charset or collation while not fully sure that it is indeed the cause.

Please help me what makes the function return this weird characters?And what should I do to make it return in binary and hexadecimal base representation in my production server? Many thanks in advance.

Solution

  • It is apparently simply a 'display' problem, the binary data returned from the UUID_TO_BIN function itself is stored correctly but displayed in a non printable characters hence it looks weird. This problem occurs because the hex-as-binary option in mysql is set to disabled, so to enable it just use mysql --hex-as-binary command when opening mysql just as referenced in this thread. Though i am not sure how to change it permanently since its missing in the my.cnf files.