I have MySql Innodb table with a single column "url" containing list of unique non-duplicate urls. Some urls can be longer than 255 characters. I am periodically adding multiple urls that might contain urls already in the table and need those filtered out.
Before encountering those long urls (>255), I had the next setup to add multiple records at once:
Multiple records added with:
INSERT IGNORE INTO table (url) VALUES (url1),(url2),..,(urln)
It adds only unique non-duplicate urls to table.
If I try to change varchar length to a higher than 255 number, I get an error:
Specified key was too long; max key length is 767 bytes
Is there a way to add multiple records while avoiding duplicates, other than relying on primary/unique key?
I am aware of innodb_large_prefix = ON setting to increase the index limit to 3000+ bytes, however confused why the setting is going to get depreciated in later MySql editions and, to some extent, still worry about the going over increased limit as well (although highly unlikely).
I also tried to mess with partial prefixes without much success.
What is the best way to add multiple records without duplicates and key length limitations?
You could additionally store an md5 or sha hash of the link and have the index on that column.