MySQL で「NOT NULL」したカラムに、さらに「0を禁止」することはできますか？

前提

Wordpressで独自テーブルを実装しています。

実現したいこと

INSERTにあたって未指定のカラムに勝手に0が入ってしまう原因を突き止め、これを防ぎたいです。

※ただし、外部キー制約以外の方法を模索しています。

発生している問題

該当のソースコードにあるように lang_idカラムを指定せずINSERTすると、勝手に0が入ってしまいます。

該当のソースコード

■CREATE

まずこちらphpMyAdminから実行し CREATE 致しました。

SQL
1CREATE TABLE `wp_names` (
2 `ID`      INT(11)     NOT NULL AUTO_INCREMENT,
3 `lang_id` INT(3)      UNSIGNED NOT NULL,
4 `name`    VARCHAR(10) NOT NULL,
5 PRIMARY KEY (`ID`)
6);

■INSERT

続いて次のようにphpでlang_id を未指定で実行しますと1が返り、INSERTが成功します。

php
1global $wpdb;
2$table   = 'names';
3$newdata = ['name'=>'カブトムシ']; // lang_id を未指定
4$format  = ['%s'];
5$result = $wpdb->insert( $wpdb->prefix.$table, $newdata, $format );	
6var_export($result); // 1 が返る

■問題の確認

成功したことについて「lang_idには何が入ったんだ？」と疑問を感じ、phpMyAdminでSELECT * FROM wp_names;を見てみると次の結果で、このように未指定のlang_idに対し、勝手に0が入ってしまうという問題が確認されました。

ID	lang_id	name
1	0	カブトムシ

試したこと

■SQLFiddleでのINSERT

まず、未指定のカラムに勝手に0が入るのは、MySQLの本来の仕様なのか？を確認すべく、SQLFiddleをお借りして実行してみました。こちらです。
http://sqlfiddle.com/#!9/381c0f

上記SQLFiddleにさらに次を記載すると'みかん'のINSERTでエラーとなり、どうやら本来はエラーになるもののように思われます。

SQL
1INSERT INTO wp_names (`name`)
2VALUES ('みかん');

■phpMyAdminでのINSERT

次に、未指定のカラムに勝手に0が入るのは、$wpdb->insert()の仕様なのか？を確認すべく、phpで$wpdb->insert()を実行するのではなく、phpMyAdminから次のINSERTを実行してみました。

SQL
1INSERT INTO wp_names (`name`)
2VALUES ('クワガタ');

するとここではエラーにならずwp_namesは次の内容となり、やはりlang_idに0が入ってしまいます。

ID	lang_id	name
1	0	カブトムシ
2	0	クワガタ

■考察

以上のように、未指定は本来エラーだが、私の環境では勝手に0が入ってしまうものかと思われます。

しかしその原因が発見できません。「MySQL のあるバージョンでは勝手に0が入る」などがリファレンスマニュアルにあるかと思ったのですが見当たらず…。

ツールのバージョンなど

最後に私の環境を以下に記載させて頂きます。

PHP 8.0
MySQL 5.7.31
phpMyAdmin 4.4.15.10

この問題の原因と、そして防ぐ方法がお分かりになる方がいらっしゃいましたら宜しくお願い致します。

maisumakun

2022/08/27 04:33

意図的に0を入れるのも除外したい感じでしょうか？

nikuatsu

2022/08/27 05:10 編集

コメントありがとうございます。確定ではありませんがそう考えております。なぜなら「意図せずに入ってしまったのか、意図的に入れたのか」の区別が難しそうだからです。尚もし非対応言語が POST されたときは、lang_id には 0 でなく 255 を意図的に入れようかと思っています。そしてこの言語情報は php で次のように define しており、SQLのマスタテーブルのように使っています。（この程度をSQLで CREATE して都度 SELECT するよりも、php で define した方が早く呼べそうだという所感からです。） define( 'LANGS', [ 1 => 'en', // 対応言語 2 => 'ja', // 対応言語 3 => 'zh', // 対応言語 255 => 'unsupported' // 非対応言語 ] ); 以上の内容に従って、0は除外したい。という質問をさせて頂きました。（もし以上の内容がおかしければご指摘頂けましたら幸いです。）

m.ts10806

2022/08/27 06:05

それならNULL許可にした方が良いのでは。「必ず入るわけではない」なら尚更。

nikuatsu

2022/08/27 06:18

えっと、必ず入るのですが…。

m.ts10806

2022/08/27 06:29

じゃあ実装側の匙加減で如何様にもなるので実行前にバリデーションするなりなんなりで弾けば良い話では。試した方法が「入らない」を想定したやり方なのでこのような指摘をしています。

m.ts10806

2022/08/27 06:35

それかDEFAULTを0以外の使う値にする。

nikuatsu

2022/08/27 06:47

> 実行前にバリデーションするなりなんなりで弾けば良い話ではまったく仰る通りかと思います。しかし現状は開発中で、うっかり入らぬよう除外できた方が便利と考えての質問です。実際に「SQLFiddleでのINSERT」ではエラーによってうっかりは起こりえませんのであれば便利と考えます。 > 試した方法が「入らない」を想定したやり方なのでこのような指摘をしています。いいえ、記載されている内容と文脈を正しく読解してください。試しているのは仕様の確認です。そのために lang_id を未指定にして試しているのであって、未指定を想定しているわけではございません。 > それかDEFAULTを0以外の使う値にする。これは一案ですよね。ありがとうございます。ところで、あなたほどのレベルの方がこうご提案されるということは、もしかして、「NOT NULL」したカラムに、さらに「0を禁止」するというのは不可能なのでしょうか？

行動規範の内容に同意します

回答1件

ベストアンサー

MySQL 8.0以上であれば、CHECK制約というものがあって、テーブル定義にCHECK (lang_id <> 0)と書くだけで0を入れない実装が可能です（MySQLリファレンス）。

MySQL 5.7ではCHECKを書いても無視されるだけで効果がありません。ストアドプロシージャを立てればやれなくはないかな…という気もしますが、やりたいことに対して大げさすぎる印象です。

投稿2022/08/27 12:46

maisumakun

総合スコア145183

maisumakun

2022/08/27 12:51

> そしてこの言語情報は php で次のように define しており、SQLのマスタテーブルのように使っています。マスターデータがある状況でしたら、それをDBに定義して外部キーをかけるのも合理的かと思います。PHPサイドでは1度引いてキャッシュしておけばいいでしょう。

maisumakun

2022/08/27 13:00

MySQLでは「SQLモード」の設定があり、設定によってはINSERTで不足した値を勝手に補ってしまいます。 https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html#sql-mode-strict なお、すでに動いているアプリについてSQLモードを切り替えたい場合、既存のSQLモードに依存して動いている箇所がないか、入念な検証が必要です。