CODEWITHSUNDEEP
mysqlbinaryleading-zero
IndigoFenix
IndigoFenix

Reputation: 321

Why does MySQL sometimes add a leading zero for BINARY column, and how do I make it stop?

I have a BINARY(255) column in a MySQL database, and I need it to be able to store binary data exactly as I put it in.

If I run the query (UPDATE table SET bindata = 0xffff WHERE id = 1), the data appears with a leading zero, (0ffff0000). However, if I add enough characters after the 0x (102, apparently?) the leading zero disappears and the inserted value shows up correctly.

Why does this happen, and how do I make it stop?

Upvotes: 0

Views: 729

Answers (1)

Shadow
Shadow

Reputation: 34232

As mysql documentation on hexadecimal literals says:

Values written using 0xval notation that contain an odd number of digits are treated as having an extra leading 0. For example, 0xaaa is interpreted as 0x0aaa.

Mysql also supports the X'val' notation, but that one raises a syntax error if the literal has odd number of digits.

The reason for this restriction is simple:

By default, a hexadecimal literal is a binary string, where each pair of hexadecimal digits represents a character.

I'm not aware any method to overcome this restriction. You may want to right pad the digits with a 0 if you have odd number of digits.

Upvotes: 1

Related Questions