MySql insert statement to binary datatype?

Question

I am using MySQL database.

I have one table having column with datatype binary(16).

I need help with the insert statement for this table.

Example:
CREATE TABLE `assignedresource` (
`distid` binary(16) NOT NULL
)

insert into assignedresource values ('9fad5e9e-efdf-b449');

Error : Lookup Error - MySQL Database Error: Data too long for column 'distid' at row 1

How to resolve this issue?

Answer 1

You should remove the hyphens to make the value match the length of the field...

Example:
CREATE TABLE `assignedresource` (
`distid` binary(16) NOT NULL
)

insert into assignedresource values ('9fad5e9eefdfb449');

Also, MySQL standard is to use this notation to denote the string as binary... X'9fad5e9eefdfb449', i.e.

insert into assignedresource values (X'9fad5e9eefdfb449');

Answer 2

Well, assuming that you want to strictly insert a hexadecimal string, first you need to remove the dashes and then "unhex" your string before inserting it into a binary(16) data type column, the code would go like this:

INSERT INTO `assignedresource` VALUES(UNHEX(REPLACE('9fad5e9e-efdf-b449','-','')));

Also... the "usable" data you are inserting is actually 8 bytes after undashing it, so binary(8) would do fine if you plan on not storing the dashes.