MySQL BINARY type representation

Question

There is a MySQL query parameter of type BINARY. Binary values in MySQL can be represented like x'1f7a8e'. However, when adding a parameter of type MySqlDbType.Binary to a MySqlCommand the query is formed with the value represented as _binary '1f7a8e' which causes the query to not find the row. Below you can see a code example of how the MySQL API is being used.

var baz = "1f7a8e";
var cmd = new MySqlCommand("SELECT foo FROM bar WHERE baz=@baz");
cmd.Parameters.Add("@baz", MySqlDbType.Binary).Value = baz;

// Execute query and read result

What would be the correct way of using a binary value as a parameter?

This seems like a strange design and/or scenario. Does it not work if you simply convert the string to bytes as per [here](https://stackoverflow.com/questions/16072709/converting-string-to-byte-array-in-c-sharp)? — stephen.vakil, Oct 05 '17 at 20:03

score 0 · Accepted Answer · answered Oct 05 '17 at 20:04

I was trying to store a Guid as a BINARY(16).

First I tried guid.ToByteArray() which did not work. Then I tried guid.ToString().Replace("-", "") which also failed.

Thanks to @daniel-a-white I then tried guid.ToString().Replace("-", "") and then converted the resulting hexadecimal number into a byte array (as described here) and that worked.

score 0 · Answer 2 · answered Oct 05 '17 at 21:58

0

You can store GUID as Binary (16)

UNHEX(REPLACE(@baz, "-",""))

This will return binary(16).

And to SELECT

SELECT * FROM mytable WHERE HEX(baz) = @baz

answered Oct 05 '17 at 21:58

Allen King

2,372
4
34
52

MySQL BINARY type representation

2 Answers2