MD5/SHA-1 hash as MySql unique key

DrJohnZoidberg

DrJohnZoidberg

Honorary Master
Joined
Jul 24, 2006
Messages
28,169
Reaction score
7,679
Location
Table View
Hey all,

Just want to pick your collective brains quick.

I am currently receiving some daily reports, the records don't have unique id's. I am using mysqlimport in the linux cli to pull them into my database, but to prevent the same data being imported more than once I am creating a unique field based off a 3 columns before importing it, e.g) I create a string with the date, username and transaction type like "2014/11/01User999TransactionType1".

This is working fine but these strings can get quite long depending on the source data so I was thinking of creating a hash out of the string and then storing the hash as the unique value.

Is this a good idea? I've Googled a bit and it is apparently highly unlikely I will have any collisions. Will it matter if I use md5 or sha1 when it comes to possible collisions?

TIA
 
DrJohnZoidberg said:
Hey all,

Just want to pick your collective brains quick.

I am currently receiving some daily reports, the records don't have unique id's. I am using mysqlimport in the linux cli to pull them into my database, but to prevent the same data being imported more than once I am creating a unique field based off a 3 columns before importing it, e.g) I create a string with the date, username and transaction type like "2014/11/01User999TransactionType1".

This is working fine but these strings can get quite long depending on the source data so I was thinking of creating a hash out of the string and then storing the hash as the unique value.

Is this a good idea? I've Googled a bit and it is apparently highly unlikely I will have any collisions. Will it matter if I use md5 or sha1 when it comes to possible collisions?

TIA
Click to expand...
Nope, not a good idea. Hashes can have multiple inputs resulting in the same hashed values. This means you might end up picking a value as duplicated even though it isn't.
 
sp4ceman said:
are those 3 columns enough to ensure the record is unique?
Click to expand...

Yes.

Messugga said:
Nope, not a good idea. Hashes can have multiple inputs resulting in the same hashed values. This means you might end up picking a value as duplicated even though it isn't.
Click to expand...

This is what I am concerned about. I guess it's just safest to leave it as is then?
 
Messugga said:
Nope, not a good idea. Hashes can have multiple inputs resulting in the same hashed values. This means you might end up picking a value as duplicated even though it isn't.
Click to expand...

The key aspect of a strong hash is its only one way, as in you can't get the original data from the hash (maybe with lookup tables) and that its unique for all data sets.

I know MD5 was broken (has collisions) but its only for specific bit patterns as far as I know. So ensuring that the string you are hashing is unique will most likely give you a unique hash. You are not using this for cryptography so there is no security related problems to look out for.

I use also use the same method (MD5 hash) to identify unique sets of data with in a SQL database and for easy searching

EDIT: from wikipedia
The ideal cryptographic hash function has four main properties:
  • it is easy to compute the hash value for any given message
  • it is infeasible to generate a message that has a given hash
  • it is infeasible to modify a message without changing the hash
  • it is infeasible to find two different messages with the same hash.
 
Last edited:
It is possible, but highly, highly unlikely you may get a clash if you use a hashing method. (Even for MD5, which would have the most common rate of clashing)

A GUID is the proper way of doing this, but it typically wouldn't require you hashing the strings together, it just generates a great big unique ID based on randomness seeded on (I think) physical attributes of your machine (amongst other things)

GUIDs have the advantage of being a built in method within your DB & are usually catered for by a lot of the more popular libraries around.
 
Yes, I still don't really understand these GUIDs.

How would it prevent duplicate rows being imported for example if the report I get has bad data (e.g. if I already have the data for 2014-01-01, but then the this data gets sent to me again in error which happens quite often. All of these processes are automated so I don't see the data before it gets imported)?
 
Chances of collisions are smaller with sha1 if memory serves. Either way the chance is *tiny* enough that it doesn't matter unless you're crunching millions of records.

Anyway...if you're really freaked about collisions then just do

MD5(string) + sha1(string)

Still gets you a predictable key and dramatically decreases the risk even further.

As I said though - its a non-issue imo unless you're doing crypto or your name is Google.
 
guids will not work as you cannot generate the same guid twice, and as such cannot do a compare.

hashing with MD5 or SHA is completely ok.
 
Or create a compound key of those 3 columns, the way the database should probably originally have been designed. If the data exists in the database why go and add redundant duplication in the form of a generated hash of any kind. That data serves no purpose other than to take up space when the data you need is already there.
 
gkm said:
Why don't you simply create a unique index (http://www.tutorialspoint.com/mysql/mysql-indexes.htm) on the original three columns? Then you do not have to bother with any string concatenation or MD5 gymnastics.

Otherwise, an MD5 or SHA should be fine for what you are trying to do, but in my opinion it is not needed at all.
Click to expand...

patrict said:
Or create a compound key of those 3 columns, the way the database should probably originally have been designed. If the data exists in the database why go and add redundant duplication in the form of a generated hash of any kind. That data serves no purpose other than to take up space when the data you need is already there.
Click to expand...

This. Use the RDBMS the way it is meant to be used.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X