0

I would like to pass the uuid of version varbinary(16) to a user-defined function in MySQL version 5.7.x - It throws an error

1406 - Data too long for column

I have a table with uuid primary key of type varbinary(16) - I would like to convert the binary(16) to human readable version so I written a function to convert the binary version.

CREATE DEFINER=`bala`@`localhost` 
FUNCTION `bin_to_uuid`(`uuid` BINARY(32)) 
    RETURNS varchar(32) CHARSET latin1
NO SQL
return LOWER(CONCAT(
    SUBSTR(HEX(uuid), 1, 8), '-',
    SUBSTR(HEX(uuid), 9, 4), '-',
    SUBSTR(HEX(uuid), 13, 4), '-',
    SUBSTR(HEX(uuid), 17, 4), '-',
    SUBSTR(HEX(uuid), 21)
))

SELECT bin_to_uuid((UNHEX(REPLACE(uuid(), "-",""))))

MySQL version 5.7.x

James Z
  • 12,209
  • 10
  • 24
  • 44
Balamanigandan B
  • 19
  • 5

1 Answers1

0

The return type is varchar(32), but with the hyphens added, the text you're returning is 36 chars long.

Change the return type to varchar(36).

Bohemian
  • 412,405
  • 93
  • 575
  • 722