やりたいこと

MySQL 8.0で2つの文字列を格納する列（VARCHAR(2000)）を持つテーブルを作成して、その2つの列の組み合わせにユニーク制約を設定したい。
例えばSQLで書くと以下のとおり。

CREATE TABLE testtable (
    id INT PRIMARY KEY,
    text1 VARCHAR(2000),
    text2 VARCHAR(2000),
    UNIQUE (text1, text2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ただし、これを実行すると以下のようなエラーとなってしまう。

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

このエラーが出る理由であるが、ユニーク制約を設定すると自動的にインデックスが作成され、以下の制限（公式のドキュメント）に引っ掛かっているためである。

The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

※ デフォルトではDYNAMIC row formatである。

utf8mb4では1文字4バイトなので、インデックスのキーの長さが合計768文字まで許されるはずである。合計768文字に制限した以下のSQLは成功して、テーブルが作成される。（この例では2つの列ぞれぞれで先頭384文字をキーとして使用するよう設定している。）

mysql> CREATE TABLE testtable (
    ->     id INT PRIMARY KEY,
    ->     text1 VARCHAR(2000),
    ->     text2 VARCHAR(2000),
    ->     UNIQUE (text1(384), text2(384))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

また、実際に確認してみると、インデックスが作成されている。

mysql> show index from testtable;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| testtable |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| testtable |          0 | text1    |            1 | text1       | A         |           0 |      384 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| testtable |          0 | text1    |            2 | text2       | A         |           0 |      384 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

疑問点

ここまで来て疑問に思ったのは、ユニークであるかどうかの判断は、INSERTしようとしているその列の値全てではなく、ユニークインデックスのキーの長さの部分によって判断されるのではないかということである。机上で調査したが確証は得られなかったので、実機で試してみることにした。

試行

環境

Ubuntu 20.04.1 LTSおよびMySQL 8.0.22である。

stack@stack:~$ lsb_release -d
Description:    Ubuntu 20.04.1 LTS
stack@stack:~$ mysql --version
mysql  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
stack@stack:~$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

試行結果

以下が試行結果である。
ユニークであるかどうかの判断は、ユニークインデックスのキー長によって決まり（今回の場合はtext1が5character、text2が5character）、その長さより後の文字は重複しているかどうかの判断では用いられないようである。

mysql> CREATE TABLE shorttable (
    ->     id INT PRIMARY KEY,
    ->     text1 VARCHAR(2000),
    ->     text2 VARCHAR(2000),
    ->     UNIQUE (text1(5), text2(5))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT shorttable values (1, 'AAAAAAAAAA', 'BBBBBBBBBB');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT shorttable values (2, 'AAAAA=====', 'BBBBB@@@@@');
ERROR 1062 (23000): Duplicate entry 'AAAAA-BBBBB' for key 'shorttable.text1'

ユニーク制約のキーの長さに制限がある場合の重複チェックについての確認（MySQL）

やりたいこと

疑問点

試行

環境

試行結果