MySql Binary query

Question

I have a table with a Binary PK:

CREATE TABLE `codes` (
   `Code` binary(45) NOT NULL,

   PRIMARY KEY (`Code`),
   UNIQUE KEY `Code_UNIQUE` (`Code`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I insert I use BINARY('value').

I attempted to retrieve a row by Code using the following:

SELECT * from codes WHERE `Code` = BINARY('value')

This doesn't work because the length of the Code field is 45, so MySql pads the data with empty bytes.

This can be visualised running the following:

SELECT HEX(Code), HEX(BINARY('value')) FROM codes

I can get it to work with this (ugly) query:

SELECT * FROM codes WHERE TRIM('0' from HEX(Code)) = HEX(BINARY('value'))

So I was just wondering if anyone can provide a nice and performant way to achieve this. This is straightforward if you know the size of your data, but the Code field can be any length.

Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy. — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 11, 2018 at 6:00
Images never help! Because we cannot reproduce the case. You can either provide us with the DDL statements for your table(s); or it would be even better if you can directly provide a db-fiddle.com — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 11, 2018 at 6:24

Madhur Bhaiya · Accepted Answer · 2018-11-11 09:25:06Z

Instead of Binary datatype, you should use Varbinary. This will not pad the data with trailing 0s, in case the length of data is smaller than the maximum size defined.

When BINARY values are stored, they are right-padded with the pad value to the specified length. The pad value is 0x00 (the zero byte). Values are right-padded with 0x00 on insert, and no trailing bytes are removed on select.

For VARBINARY, there is no padding on insert and no bytes are stripped on select.

Also, you do not need to define Unique constraint on an already defined Primary Key. Primary Key basically satisfied the Unique constraint, with additional condition of NOT NULL values. So, defining Unique constraint further does not add anything upon it.

This is how the CREATE TABLE statement can be:

CREATE TABLE `codes` (
   `Code` Varbinary(45) NOT NULL, -- Changed to Varbinary

   PRIMARY KEY (`Code`) -- Removed Unique constraint

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into codes (Code) VALUES (BINARY('value'));

Query #1

SELECT HEX(Code), HEX(BINARY('value')) FROM codes ;

| HEX(Code)  | HEX(BINARY('value')) |
| ---------- | -------------------- |
| 76616C7565 | 76616C7565           |

Query #2

SELECT * from codes WHERE `Code` = BINARY('value');

| Code            |
| --------------- |
| [object Object] |

View on DB Fiddle

Collectives™ on Stack Overflow

MySql Binary query

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