MySql Binary query
up vote
1
down vote
favorite
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.
mysql sql binary
add a comment |
up vote
1
down vote
favorite
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.
mysql sql binary
Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy.
– Madhur Bhaiya
Nov 11 at 6:00
@Madhur Bhaiya Updated with a screenshot
– joepour
Nov 11 at 6:15
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
Nov 11 at 6:24
@MadhurBhaiya db-fiddle.com/f/3eqCCdEQPsiuxhjef2xHtb/0
– joepour
Nov 11 at 6:41
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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.
mysql sql binary
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.
mysql sql binary
mysql sql binary
edited Nov 11 at 6:14
asked Nov 11 at 5:17
joepour
2,45682227
2,45682227
Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy.
– Madhur Bhaiya
Nov 11 at 6:00
@Madhur Bhaiya Updated with a screenshot
– joepour
Nov 11 at 6:15
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
Nov 11 at 6:24
@MadhurBhaiya db-fiddle.com/f/3eqCCdEQPsiuxhjef2xHtb/0
– joepour
Nov 11 at 6:41
add a comment |
Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy.
– Madhur Bhaiya
Nov 11 at 6:00
@Madhur Bhaiya Updated with a screenshot
– joepour
Nov 11 at 6:15
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
Nov 11 at 6:24
@MadhurBhaiya db-fiddle.com/f/3eqCCdEQPsiuxhjef2xHtb/0
– joepour
Nov 11 at 6:41
Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy.
– Madhur Bhaiya
Nov 11 at 6:00
Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy.
– Madhur Bhaiya
Nov 11 at 6:00
@Madhur Bhaiya Updated with a screenshot
– joepour
Nov 11 at 6:15
@Madhur Bhaiya Updated with a screenshot
– joepour
Nov 11 at 6:15
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
Nov 11 at 6:24
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
Nov 11 at 6:24
@MadhurBhaiya db-fiddle.com/f/3eqCCdEQPsiuxhjef2xHtb/0
– joepour
Nov 11 at 6:41
@MadhurBhaiya db-fiddle.com/f/3eqCCdEQPsiuxhjef2xHtb/0
– joepour
Nov 11 at 6:41
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
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
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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
add a comment |
up vote
2
down vote
accepted
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
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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
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
edited Nov 11 at 9:25
answered Nov 11 at 9:17
Madhur Bhaiya
18.7k62236
18.7k62236
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53246046%2fmysql-binary-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you provide some sample data showcasing this. A DB / SQL fiddle would be handy.
– Madhur Bhaiya
Nov 11 at 6:00
@Madhur Bhaiya Updated with a screenshot
– joepour
Nov 11 at 6:15
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
Nov 11 at 6:24
@MadhurBhaiya db-fiddle.com/f/3eqCCdEQPsiuxhjef2xHtb/0
– joepour
Nov 11 at 6:41