The odds against the eventuality you are worried about are astronomically high. Much serious work on the internet would fall apart were that not true. Collision is simply not going to happen within the next several hundred thousand years. On Nov 5, 2013 9:59 PM, "Li Li" <fancye...@gmail.com> wrote:
> I prefer your solution in that it's something like Optimistic Locking. > but the problem is that if I define md5 as unique key and there exists > 2 different urls with the same md5. I can't insert the second url > anymore > > On Tue, Nov 5, 2013 at 11:55 PM, Dan Nelson <dnel...@allantgroup.com> > wrote: > > In the last episode (Nov 05), Li Li said: > >> I want to create a table with a long varchar column, maybe it's the > url. > >> according to dns spec, the url's max length is fixed. but I have > >> to deal with url having long params such as > >> a.html?q=aaaaaaaaaaaaaaaaaaaaaaaaaaaa&fl=bbbbbbbbbbbbbbbbbbbbbbbb.... > >> I want the url is unique when inserting it. > >> I googled and found > >> http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql > >> this post suggests use md5 of url. But in theory, there will be > >> conflict that two different urls will have the same md5(even it's > >> probablitiy is very small). I want to a absolute correct solution. > >> one method i can come up with is using select ... for update > >> 1. begin transaction > >> 2. select url from tb where md5='xxxx' for update > >> 3. if the url is not exist, insert into this url; else do nothing > > > > It might be more efficient to optimize for the common case here. The > > assumption is that an md5 (or sha1 or sha2) hash collision is extremely > > unlikely, so you could just insert your new row, and if you get a > "duplicate > > entry for primary key" error, then you can "select url from tb where > > md5='xxxx' ", and compare the retreived url with the one you want to > insert. > > > > -- > > Dan Nelson > > dnel...@allantgroup.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
