newbie: testing if table contains some blob data

[r_ahi...@poczta.onet.pl]

Hello,
I am learning MySQL. I have a table 'pictures' (InnoDB) containing pictures
and movies:

CREATE TABLE `pictures` (
`picture_id` int(10) unsigned NOT NULL auto_increment COMMENT 'Picture
identifier',
`picture` blob NOT NULL COMMENT 'Picture contents',
`picture_name` varchar(25) collate utf8_polish_ci NOT NULL,
PRIMARY KEY (`picture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci
COMMENT='Pictures/movies'

In the table I store GIFs, JPEGs and MPEGs.
When I INSERT new picture/movie into my table I want to know if table
already contains this picture/movie (but recorded picture can be added with
a new name).
Could you help me please and tell how to add new picture/movie only if it
doesn't exist in 'pictures' table. If it exists, I want to know
corresponding picture_id.
Thanks for help.
/RAM/

[Chuck]

r_ahi...@poczta.onet.pl wrote:
> Hello,
> I am learning MySQL.

Aren't we all? ;-)

You might try running an md5 or sha1 hash on the blob and use that as a
unique key. I'm not sure if the mysql built-in md5 or sha function can
take a blob as an argument though so you might need to compute the hash
in your application.

[r_ahi...@poczta.onet.pl]

Chuck wrote:
> You might try running an md5 or sha1 hash on the blob and use that as a
> unique key. I'm not sure if the mysql built-in md5 or sha function can
> take a blob as an argument though so you might need to compute the hash
> in your application.

I found in documentation that SHA1 has string as argument. Do you think it
is good idea to use SH1(ENCODE(str))?
/RAM/

[Willem Bogaerts]

As Chuck already said, I would check for a hash or filename (if the
filename is unique with the picture), just because uniqueness checking
is so much lighter.

After that, read the fine manual about the INSERT .. ON DUPLICATE KEY
syntax. Especially in combination with LAST_INSERT_ID(), it is possible
to construct a query that always gives the correct ID, no matter if it
were existent or newly created. You'd use a query of the form:

INSERT INTO pictures(picture,picture_hash) VALUES(0x...., '...') ON
DUPLICATE KEY UPDATE picture_id=LAST_INSERT_ID(picture_id),
picture=VALUES(picture), picture_hash=VALUES(picture_hash);

After that, getting the last inserted ID will yield the ID of that picture.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

[r_ahi...@poczta.onet.pl]

r_ahi...@poczta.onet.pl wrote:
> Do you think it is good idea to use SH1(ENCODE(str))?

Sorry, I meant SH1(ENCODE(blob)).
/RAM/

[Chuck]

Actually I've never tried it so please let me know if it works.