"is it possible to make a field in mysql table to to have length max=700, to be unicode (3 bytes) and to be unique?" Code Answer

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".