CODEWITHSUNDEEP
mysqlbinary
Don
Don

Reputation: 1570

MySQL SELECT against varbinary column

This is a follow up to a previous issue I had posted here.

I created a test table:

CREATE TABLE `my_test_table` (
    `record_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `col1` BINARY(20) NULL DEFAULT NULL,
    `col2` CHAR(40) NULL DEFAULT NULL,
    PRIMARY KEY (`record_id`)
)

Then ran the statement:

INSERT INTO my_test_table (col1, col2) VALUES(sha1('test'), sha1('test') );

The data looks like...

1 0x6139346138666535636362313962613631633463 a94a8fe5ccb19ba61c4c0873d391e987982fbbd3

I'm not sure how I can select against the VARBINARY column. I can select against the CHAR like:

SELECT * FROM my_test_table WHERE col2 = sha1('test');

I've tried

SELECT * FROM my_test_table WHERE col1 = hex(sha1('test'));

And other variations but can't seem to find a solution (if there is one). I need to be able to check to see if a value already exists in the database before I allow a new insert. I was looking at VARBINARY and BINARY based on previous suggestions. Thanks.

Upvotes: 1

Views: 9209

Answers (3)

MoMo
MoMo

Reputation: 111

BTW the sha1('test') returns a STRING of hex characters...

You should use unhex(sha1('test')) when inputing data as a hex string, or else it wont be entered as acsii values which wont work with matching at all

SELECT * FROM my_test_table WHERE col1 = unhex(sha1('test')); should be the matching query also.

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65547

I have not read your previous question, but based on the source code in this question, you are only storing the first 20 characters of the sha1 hash in col1, so if you want to select it you should just look for the first 20 characters of the sha1 hash.

For example:

SELECT * 
FROM my_test_table 
WHERE col1 = left(sha1('test'),20);

Upvotes: 2

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Data truncation: Data too long for column 'col1' at row 1: INSERT INTO my_test_table (col1, col2) VALUES(sha1('test'), sha1('test') )

Can this be the reason why you cannot select the data properly?

Upvotes: 0

Related Questions