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

はじめに

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