List:General Discussion« Previous MessageNext Message »
From:Li Li Date:November 5 2013 6:41am
Subject:how to create unique key for long varchar?
View as plain text  
hi all
    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

     also I need to set the transaction isolation to SERIALIZABLE

     is this solution correct in multithread/process environment?

     another method is using trigger to check whether the url exist. I
don't know whether this method will be faster than previous one. how
to ensure it's correctness in multithread environment?
Thread
how to create unique key for long varchar?Li Li5 Nov
  • Re: how to create unique key for long varchar?Dan Nelson5 Nov
    • RE: how to create unique key for long varchar?Rick James5 Nov
    • Re: how to create unique key for long varchar?Li Li6 Nov
      • Re: how to create unique key for long varchar?Michael Dykman6 Nov
      • Re: how to create unique key for long varchar?Li Li6 Nov