[MySQL] 設定されたデータ長以上のデータを挿入した場合にエラーを発生させない

IT
2022.09.03

はじめに

本記事では設定されたデータ長以上のデータを挿入した場合にエラーを発生させずに、設定したデータ長を超えた部分は落としてDBに登録する方法についてまとめます。

環境

DB は MySQL を使用します。

  mysql> select version();
  +-----------+
  | version() |
  +-----------+
  | 8.0.30    |
  +-----------+

事象

以下のテーブルを例に説明します。

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

上記のテーブルでは first_name カラムのデータ型は VARCHAR(14) を設定しています。
そのため、このテーブルに first_name が14文字 以上のデータを挿入すると、エラーが発生します。

INSERT INTO `employees` VALUES (10001,'1953-09-02','GeorgiGeorgiGeorgi','Facello','M','1986-06-26')
-- Error Code: 1406. Data too long for column 'first_name' at row 1

本エラーは設定したデータ長以上データを挿入・更新した場合に発生するエラーですが、場合によっては設定したデータ長を超えた部分は落としてDBに登録したい(エラーを発生させたくない)こともあると思います。
(例えば商品説明のような極端に長いものが存在し、後半部分の情報は最悪失われても問題ないと判断できるもの)

解決方法

SQLモードの設定を変更することで解決できます。
デフォルトの設定を確認します。

SELECT @@GLOBAL.sql_mode;
-- 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

上記の STRICT_TRANS_TABLES はテーブルの設定に従わない値を挿入、更新した場合に、SQLの実行を中止します。
そのため sql_mode の設定を変更し、再度挿入を行うと、今度はエラーが発生せずに挿入することができました。
設定したデータ長を超えた部分は落とされていることが確認できます。

SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
INSERT INTO `employees` VALUES (10001,'1953-09-02','GeorgiGeorgiGeorgi','Facello','M','1986-06-26');
select * from employees;
-- '10001', '1953-09-02', 'GeorgiGeorgiGe', 'Facello', 'M', '1986-06-26'

おわりに

本記事では設定されたデータ長以上のデータを挿入した場合にエラーを発生させずに、設定したデータ長を超えた部分は落としてDBに登録する方法についてまとめます。
意図した値と異なる値が登録される可能性があるため、使い所には注意が必要ですね。
この記事がどなたかの参考になれば幸いです。

参考

コメント