MySQL 8.0.23 では、INVISIBLE COLUMN の仕組みが解放されました。
ドキュメントによると「カラムを追加しても、SELECT * の動作に影響を与えないところがいいんだよねー」と書いてあるのですが、うーん、それって何処の人のニーズなの？という感じで、もにょもにょします。（そんなニーズないだろう、という思いと、もしあるとしたら私の思いもつかない使い方なので、話を聞いて感動してみたい思いとが共存している感じです）
MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.10 Invisible Columns
ということで、試すのも簡単そうなので、実際に動かしてみました。
だらだらと、やります。
テーブルの作成と確認
himitu カラムを INVISIBLE として、テーブルt1を作ってみます。
CREATE TABLE t1 (
id integer,
name varchar(10),
himitu varchar(10) INVISIBLE,
created_at datetime);
作れたので、descと show create table それぞれで確認してみます。
desc：
mysql> desc t1;
+------------+-------------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-----------+
| id | int | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| himitu | varchar(10) | YES | | NULL | INVISIBLE |
| created_at | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+-----------+
4 rows in set (0.01 sec)
出力を見ると、普通に4カラムのテーブルが作成されて、付加情報として「INVISIBLE」という属性がついている、というイメージですね。
show create table:
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`himitu` varchar(10) DEFAULT NULL ,
`created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
8.0.23 以降の場合のみに「INVISIBLE」がつくようになっています。つまり 8.0.23 で作成／ダンプしたテーブルを、それ以前の（たとえばお隣のサーバの）MySQL に入れると、INVISIBLE なカラムはVISIBLE になるということでもあり、この機能のウリである「SELECT * の結果が変わらない」という観点からは、異なる動作が発生すると言えます。まぁそもそも、個人的には SELECT * の使用はお勧めしていないので、このことで問題になってしまうようなシステムは私の周りにはないと思うのですが、、
データを入れる
INSERT文でカラム明示しない場合は、ないものとして扱われる（以下の例では3カラムの値のみを与えている）。INVISIBLE カラムに値を入れたい場合は INSERT文でカラムを明示する必要がある。
INSERT INTO t1 VALUES (1, "watashi", now());
INSERT INTO t1 (id, name, himitu, created_at) VALUES (2, "oreore", "naisyo", now());
内容確認。SELECT * には出てこない。
mysql> SELECT * FROM t1;
+------+---------+---------------------+
| id | name | created_at |
+------+---------+---------------------+
| 1 | watashi | 2021-01-21 14:47:02 |
| 2 | oreore | 2021-01-21 14:47:41 |
+------+---------+---------------------+
2 rows in set (0.00 sec)
カラム明示をすれば、もちろん確認可能。
mysql> SELECT id, name, himitu, created_at FROM t1;
+------+---------+--------+---------------------+
| id | name | himitu | created_at |
+------+---------+--------+---------------------+
| 1 | watashi | NULL | 2021-01-21 14:47:02 |
| 2 | oreore | naisyo | 2021-01-21 14:47:41 |
+------+---------+--------+---------------------+
2 rows in set (0.00 sec)
INVISIBLEをVISIBLEに
秘密カラムを隠す必要がなくなったので(というシナリオで) VISIBLE 化してみる。
mysql> ALTER TABLE t1 MODIFY COLUMN himitu VARCHAR(10) VISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
desc で見ても、Extraのところにあった「INVISIBLE」の表示はなくなっているし、Oracleみたいに、VISIBLE化した時に一番最後のカラムに移動したりはしない。
mysql> desc t1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| himitu | varchar(10) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
SELECT * でももちろん表示されるようになった。
mysql> SELECT * FROM t1;
+------+---------+--------+---------------------+
| id | name | himitu | created_at |
+------+---------+--------+---------------------+
| 1 | watashi | NULL | 2021-01-21 14:47:02 |
| 2 | oreore | naisyo | 2021-01-21 14:47:41 |
+------+---------+--------+---------------------+
2 rows in set (0.00 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`himitu` varchar(10) DEFAULT NULL,
`created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
ぜんぶ隠しちゃうことはできない
すべてのカラムを秘密にしてみようと思ったのだけど、許してもらえなかった。
mysql> CREATE TABLE t2 (
-> id integer INVISIBLE,
-> name varchar(10) INVISIBLE,
-> himitu varchar(10) INVISIBLE,
-> created_at datetime INVISIBLE);
ERROR 4028 (HY000): A table must have at least one visible column.
ということで、とりあえず１つだけVISIBLEなカラムを作った状態で、
CREATE TABLE t2 (
id integer INVISIBLE,
name varchar(10) ,
himitu varchar(10) INVISIBLE,
created_at datetime INVISIBLE);
そのカラムを INVISIBLE にしてみようとしたが、、、
mysql> ALTER TABLE t2 MODIFY COLUMN name VARCHAR(10) INVISIBLE;
ERROR 4028 (HY000): A table must have at least one visible column.
やはり同じエラー。最終的に１つもVISIBLEなカラムが存在しな状態が許されないことが確認できました。
これ、今回の機能のために新たに付け加えられたエラーメッセージなんですよね。お会いできて、なんだか嬉しい。
INVISIBLEなカラムに値必須オプションを付けると面倒
id 列を PRIMARY KEY にして INVISIBLE にしてみます。なんと、これ、テーブル作成できます。
（ここまでのテーブルを元にしたので himituという名前の列がありますが、もはや秘密ではありません）
mysql> CREATE TABLE t3 (
-> id integer PRIMARY KEY INVISIBLE,
-> name varchar(10) ,
-> himitu varchar(10) ,
-> created_at datetime );
Query OK, 0 rows affected (0.01 sec)
でも、列の明示なしでのINSERTを試みると、エラー。id列の値を指定していないのだから当然ですね。
mysql> INSERT INTO t3 VALUES ("watashi", "naisyo", now());
ERROR 1364 (HY000): Field 'id' doesn't have a default value
INVISIBLE カラムの使い方を見つけた！！！
先ほど INVISIBLE にした PRIMARY KEY の id列に、AUTO_INCREMENTを付けてみます。
CREATE TABLE t4 (
id integer AUTO_INCREMENT PRIMARY KEY INVISIBLE,
name varchar(10) ,
himitu varchar(10) ,
created_at datetime );
ここに3件のデータを入れて、内容確認してみます。
INSERT INTO t4 VALUES ("watashi", "naisyo", now());
INSERT INTO t4 VALUES ("oreore", "nainai", now());
INSERT INTO t4 VALUES ("kare", "syosyo", now());
mysql> SELECT id, name, himitu, created_at FROM t4;
+----+---------+--------+---------------------+
| id | name | himitu | created_at |
+----+---------+--------+---------------------+
| 1 | watashi | naisyo | 2021-01-21 14:58:35 |
| 2 | oreore | nainai | 2021-01-21 14:59:04 |
| 3 | kare | syosyo | 2021-01-21 14:59:04 |
+----+---------+--------+---------------------+
3 rows in set (0.01 sec)
うん！！これだ！！！
INSERT時に、AUTO_INCREMENT やデフォルト値に任せて、普段は値を明示しないような場合、ちょっとだけ便利だぞ、これ！
今までは、AUTO_INCREMENT を持つテーブルに手作業で値を入れる場合に、id列を除いた列名を羅列するか、あるいは 値として*1NULLを明示していたのが、これで不要になるのです。きっと便利だ！
ということで本日時点の結論。
- INVISIBLE カラムは、AUTO_INCREMENT の列に指定すると INSERTがちょっとラク！
(笑)
追記：秘密カラムの追加
いちばん本筋であった「カラムを追加しても SELECT * の結果が変わらないのがいいんだよー」というシナリオを書いていませんでした。
以下のようにして、追加できます。
mysql> ALTER TABLE t4 ADD COLUMN himi2 varchar(5) INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
descで確認すると、ちゃんと追加されています。
mysql> desc t4;
+------------+-------------+------+-----+---------+--------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+--------------------------+
| id | int | NO | PRI | NULL | auto_increment INVISIBLE |
| name | varchar(10) | YES | | NULL | |
| himitu | varchar(10) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| himi2 | varchar(5) | YES | | NULL | INVISIBLE |
+------------+-------------+------+-----+---------+--------------------------+
5 rows in set (0.00 sec)
