MySQL insert of binary columns

Question

I communicate with MySQL server version 5.0.45 via MySQL Query Browser 1.2.14

Have column of type binary(6).

CREATE TABLE  `test` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `h` binary(6) NOT NULL,
  PRIMARY KEY  (`tid`),
  KEY `idx1` (`h`)
) ENGINE=MyISAM;

After query

INSERT INTO test(h) VALUES(X'AABBCCDDEEFF');

I see in the h field of added row corrupted value like

D0 84 C2 BB D0 9C

Seems that mysql treated binary value as a string and stored it according to some charset. But then why does it ignore binary column type and X'...' notation of value to insert? Am I doing wrong or MySQL is?

peterm · Accepted Answer · 2015-01-12 01:19:14Z

1

Did you try to use HEX()?

SELECT HEX(h) FROM test;

Will give you:

|       HEX(H) |
|--------------|
| AABBCCDDEEFF |

Now if you want to treat a hexadecimal value as a number you need to use CAST(h AS UNSIGNED).

SELECT CAST(X'AABBCCDDEEFF' AS UNSIGNED);

Output:

| CAST(X'AABBCCDDEEFF' AS UNSIGNED) |
|-----------------------------------|
|                   187723572702975 |

By default a hexadecimal value is treated as a string, which most likely exactly what you wanted it to be, judging by the data type that you've chosen. It's just the output that scared you off.

Collectives™ on Stack Overflow

MySQL insert of binary columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related