MariaDBで255文字を超えるユニークインデックスを作ってみたら、こんな落とし穴があった！
2024-07-27
MariaDBで255文字のユニークインデックスを作成可能か？
MariaDBのバージョン確認
MariaDB 10.2.7以前では、VARCHAR、CHAR、TEXT型カラムに対して、最大255文字までのユニークインデックスを作成できました。しかし、MariaDB 10.3以降では、デフォルトのインデックスサイズが65,535バイトに拡張されました。
確認方法
SELECT @@version;
出力結果に10.3以上が含まれていれば、デフォルトのインデックスサイズは65,535バイトです。
カラム長の確認
ユニークインデックスを作成するカラムの長さは、255文字以下である必要があります。
DESCRIBE table_name;
出力結果で、Column_nameが対象カラム、TypeがVARCHAR、CHAR、TEXT、Lengthが255以下であることを確認します。
インデックスサイズの設定
MariaDB 10.3以降で、255文字を超えるユニークインデックスを作成したい場合は、INDEXまたはUNIQUE制約にUSING句を使用し、インデックスサイズを明示的に指定する必要があります。
例
CREATE TABLE table_name (
column_name VARCHAR(256) UNIQUE USING btree (255)
);
上記例では、column_nameカラムに対して255文字までのユニークインデックスを作成します。
注意点
- インデックスサイズは、カラム長よりも大きく設定する必要があります。
- MariaDB 10.3以降で、インデックスサイズを明示的に指定しない場合、デフォルトのインデックスサイズ(65,535バイト)が適用されます。
- MariaDB 10.2.7以前では、255文字を超えるユニークインデックスを作成しようとすると、エラーが発生します。
-- テーブル作成
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) UNIQUE,
email VARCHAR(255) UNIQUE,
password VARCHAR(255)
);
-- 255文字を超えるユニークインデックス作成
CREATE TABLE posts (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(500) UNIQUE USING btree (255),
content TEXT,
author_id INT
);
-- インデックスサイズ確認
SHOW INDEX FROM users;
SHOW INDEX FROM posts;
-- 255文字を超えるユニークインデックスを使用した検索
SELECT * FROM users WHERE username = 'sample_username';
SELECT * FROM posts WHERE title = 'サンプルタイトル';
255文字を超えるユニークインデックスを作成する他の方法
PREFIXを使用する
PREFIXを使用して、インデックスに使用するカラムの先頭部分のみを指定できます。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) UNIQUE USING btree (250),
email VARCHAR(255) UNIQUE USING btree (250),
password VARCHAR(255)
);
上記例では、usernameとemailカラムに対して、先頭250文字のみを使用したユニークインデックスを作成します。
サロゲートキーを使用する
VARCHAR、CHAR、TEXT型カラムではなく、INT型カラムをユニークインデックスとして使用できます。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255),
email VARCHAR(255),
password VARCHAR(255),
unique_id INT UNIQUE
);
-- サロゲートキー生成
INSERT INTO users (username, email, password) VALUES ('sample_username', '[email protected]', 'password123');
SELECT LAST_INSERT_ID();
-- サロゲートキーを使用した検索
SELECT * FROM users WHERE unique_id = 1;
上記例では、unique_idカラムをINT型ユニークインデックスとして使用しています。
別のデータベースを使用する
MariaDB以外のデータベースでは、255文字を超えるユニークインデックスを作成できる場合があります。
mariadb
