データベース設計の落とし穴 - MariaDBにおけるVARCHARカラムのバイト数制限

2024-04-02

MariaDBでVARCHARカラムの長さを文字数ではなくバイト数で制限する方法

CHARACTER SETとCOLLATIONの指定

VARCHARカラムを作成する際に、CHARACTER SETとCOLLATIONを指定することで、カラムに格納できる文字の種類と、その文字をどのように比較するかを定義することができます。

例えば、以下の例では、カラム name は最大255バイトのUTF-8文字列を格納できるようになり、文字比較はケースインセンシティブになります。

CREATE TABLE users (
  name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);

CHECK制約を使用して、カラムに格納できる文字列の長さをバイト数で制限することができます。

例えば、以下の例では、カラム name は最大255バイトの文字列しか格納できないようになっています。

CREATE TABLE users (
  name VARCHAR(255) CHECK (LENGTH(name) <= 255)
);

例えば、以下のストアドプロシージャは、カラム name に格納する文字列の長さが255バイトを超えていないことを確認します。

DELIMITER //

CREATE PROCEDURE check_name_length(
  IN name VARCHAR(255)
)
BEGIN
  IF LENGTH(name) > 255 THEN
    SIGNAL SQLSTATE '42000'
      SET MESSAGE_TEXT = 'The name must be no longer than 255 bytes.';
  END IF;
END //

DELIMITER ;

このストアドプロシージャは、INSERTやUPDATEステートメントで使用することができます。

INSERT INTO users (name) VALUES ('John Doe');

CALL check_name_length('John Doe');

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

CALL check_name_length('Jane Doe');

MariaDBでVARCHARカラムの長さを文字数ではなくバイト数で制限するには、CHARACTER SETとCOLLATIONの指定、CHECK制約、ストアドプロシージャなどの方法があります。

それぞれの方法にはメリットとデメリットがあり、使用する方法は状況によって異なります。

CHARACTER SETとCOLLATIONの指定は、シンプルで使いやすい方法ですが、すべての文字コードに対応しているわけではありません。
CHECK制約は、すべての文字コードに対応していますが、パフォーマンスが低下する可能性があります。
ストアドプロシージャは、最も柔軟な方法ですが、開発コストがかかります。

CHARACTER SETとCOLLATIONの指定

CREATE TABLE users (
  name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);

CHECK制約

CREATE TABLE users (
  name VARCHAR(255) CHECK (LENGTH(name) <= 255)
);

ストアドプロシージャ

DELIMITER //

CREATE PROCEDURE check_name_length(
  IN name VARCHAR(255)
)
BEGIN
  IF LENGTH(name) > 255 THEN
    SIGNAL SQLSTATE '42000'
      SET MESSAGE_TEXT = 'The name must be no longer than 255 bytes.';
  END IF;
END //

DELIMITER ;

使用例

INSERT INTO users (name) VALUES ('John Doe');

CALL check_name_length('John Doe');

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

CALL check_name_length('Jane Doe');

<?php

$name = 'John Doe';

if (strlen($name) > 255) {
  echo 'The name must be no longer than 255 bytes.';
  exit;
}

// ...

?>

この方法は、データベースに負荷をかけずに、簡単に実装することができます。

例えば、以下のトリガーは、カラム name に255バイトを超える文字列が格納されるのを防ぎます。

DELIMITER //

CREATE TRIGGER check_name_length
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF LENGTH(NEW.name) > 255 THEN
    SIGNAL SQLSTATE '42000'
      SET MESSAGE_TEXT = 'The name must be no longer than 255 bytes.';
  END IF;
END //

DELIMITER ;