Mysql said that it only supports 700 char unique field in mysql table. So if I set the field to be unique then it will say error (like max length is 700 error). The reason is that unicode takes 3 bytes, so 700 unicode chars will be 2100 chars & Mysql can't set a field with 2100 to be unique.
Is that any way to fix this issue? I am using Mysql 5.0.
Having such a large unique constraint will have a performance penalty in any case since it needs to be indexed (internally)...
What I would do is use an extra field, and insert a (e.g. SHA1) hash of the field in question. Make that extra field unique.
If you need to make sure that all (internal / external) updates / inserts always keep this field in sync you may need triggers to assure this. If you can control where updates / inserts are coming from you might get away with calculating the hash as a part of the insert / update query.
The default length limit on an index in InnoDB is 765 bytes. This means if you use a multi-byte character set, the limit is smaller, based on the greatest number of bytes per character. For example, 255 characters for utf8, or 191 characters if you use utf8mb4.
You can set the configuration value innodb_large_prefix, and this allows InnoDB to support index length up to 3072 bytes, but you must use the Barracuda row formats (DYNAMIC or COMPRESSED). These row format are not supported in MySQL 5.0.
Example:
mysql> SET GLOBAL innodb_large_prefix=ON;
mysql> create table b (v varchar(3073), key(v)) row_format=dynamic;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table b (v varchar(3072), key(v)) row_format=dynamic;
Query OK, 0 rows affected (0.02 sec)
mysql> create table butf8 (v varchar(1025), key (v)) row_format=dynamic, character set=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table butf8 (v varchar(1024), key (v)) row_format=dynamic, character set=utf8;
Query OK, 0 rows affected (0.01 sec)
See http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
Other storage engines may have greater limits, but it is implementation-specific.
For example, in MyISAM, the default index length limit is 1000 bytes, so you can create an index on a varchar(333) in utf8, and varchar(250) in utf8mb4.
In TokuDB, the maximum index length is 3072 bytes. But I couldn't find any mention of TokuDB support for MySQL 5.0. Tokutek distributes a binary build of MySQL 5.5 with TokuDB storage engine.
But all the above, while answering your question literally, avoids the greater issue: do you really want a unique index on such long strings? Long index sizes are not as efficient as short index sizes, (both in terms of size on disk or in memory, and in terms of runtime performance).
You would do better to limit your string size, or else store a hash or a soundex of the string, and apply uniqueness to that column.
Re your comment:
I can use trigger to prevent the duplicated row
I assume you mean you will do a SELECT inside the trigger to search for a row where the long string matches, and abort the operation if one is found. I want to add another piece of information that may help this trigger function more efficiently:
You can still create an index on a very long string, but define the index as a "prefix index" so that it indexes the first N characters. This is not suitable for a UNIQUE constraint, because two different strings may have the same characters in the leading part of the string. But it will help your SELECT in the trigger search only among strings that are same at least in that leading portion.
The other comment is that MySQL 5.0 does not have any syntax in a trigger to abort the operation. The SIGNAL functionality was introduced in MySQL 5.5.
One hack I have seen used to abort a trigger is to declare a local integer variable in the trigger, and then inside an IF statement try to assign a string value to the integer variable, which will cause an error.
DECLARE foo INT;
IF ( ... ) THEN
SET foo = 'Duplicate string found';
END IF;
When you try to insert a duplicate, you get this:
ERROR 1366 (HY000): Incorrect integer value: 'Duplicate string found' for column 'foo' at row 2
The clever thing is that you can make the string you try to assign contain the error message you want displayed to the user, and you can make the local variable have the same name as the column that you want to preserve uniqueness. The only strange part is that the error says "Incorrect integer value".