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