Hi!
On Jun 30, Linus Upson wrote:
> The following was tested with MySQL 4.1.1 running on RedHat ES 3. This
> message was sent in UTF-8.
>
> The unique key index prefix length is misleading when you use the utf8
> character set. If you specify a prefix of N characters the uniqueness
> constraint appears to be enforced for the first 3*N bytes of column,
> not the first N characters.
Oops.
I agree, it's confusing.
We'll fix it.
> I discovered this problem because mysql silently shortened the unique
> index prefix on one of my utf8 varchar columns. When I execute the
> following statement:
>
> CREATE TABLE `t` (
> `c` varchar(150) NOT NULL default '',
> UNIQUE KEY `c` (`c`(150))
> ) TYPE=MyISAM DEFAULT CHARSET=utf8
>
> there are no errors or warnings, but the result is:
>
> mysql> show create table t \G
> *************************** 1. row ***************************
> Table: t
> Create Table: CREATE TABLE `t` (
> `c` varchar(150) NOT NULL default '',
> UNIQUE KEY `c` (`c`(64))
> ) TYPE=MyISAM DEFAULT CHARSET=utf8
> 1 row in set (0.00 sec)
>
> The 150 character prefix should still fit within the 500 byte index
> limit of 4.1.1 since 3 * 150 = 450, but for some reason the prefix has
> been reduced to 64.
I wasn't able to repeat in in MySQL 4.1.3 - index was created as
expected. MySQL *never* silently truncates a UNIQUE index (if it does,
it's a bug).
Regards,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
