とある日

MySQL8.0.23が2021-01-18に一般公開されました。

リリースノートを読んでいる中で気になったSQL構文に関する注意事項について調べていこうと思います。

内容としては、非表示の列を追加できるというものです。

Oracleだとすでに実装されている機能みたいですね。

自分は知らなかったです。

列を非表示にしたいとか考えたことすらなかった・・・。

環境

項目	詳細
DataBase	MySQL 8.0.23
OS	CentOS8

更に詳しい詳細は下記のテスト環境の記事を参照してください。

updraft.hatenadiary.com

非表示列とは

非表示の列

MySQLは、MySQL8.0.23以降の非表示列をサポートしています。非表示の列は通常、クエリに対して非表示になっていますが、明示的に参照されている場合はアクセスできます。MySQL 8.0.23より前では、すべての列が表示されます。

OracleDataBaseは12cの頃からあるみたいですね。

使用用途

列追加時の不具合回避

非表示の列のところに、実際にあった使用例。

アプリケーションがSELECT * クエリを使用してテーブルにアクセスし、アプリケーションが予期しない新しい列を追加するためにテーブルが変更された場合でも、変更せずに動作し続ける必要があるとします。では SELECT *、クエリ、* すべてのテーブル列に評価さは、目に見えないものを除いて、その解決策は見えない列として新しい列を追加することです。列はクエリから「非表示」のままSELECT *であり、アプリケーションは以前と同じように機能し続けます。新しいバージョンのアプリケーションは、必要に応じて、明示的に参照することにより、非表示の列を参照できます。

既存のシステムでSELCET * FROM TABLEとしてSQLを実行し、すべてのカラムに対して特定の処理を明示的に指定した場合、あとから追加したカラムに対して処理をしなかったときに不具合が生じるのを防ぐ目的として使用されるみたいですね。

ただ、アスタリスクをしていするSQLをアプリケーション側で記述しないですが。

意図しないデータ流失を阻止

SQLインジェクションで、ユーザのテーブルデータをSELCET * FROM TABLEで全件取得するような人がいた場合、この機能を使用すればパスワードや住所などの重要な列のデータの流失を阻止することができる。

DESCを使用してのカラム一覧でも表示されないのでそういった用途にも使用できるかも。

いざ実践

まず、MySQL8.0.23をインストールします。

用意した環境のお話がこちら↓

updraft.hatenadiary.com

テーブル作成

単純明快なテーブル構成にします。

CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);

INVISIBLE：非表示状態
VISIBLE：表示状態

col1を表示状態(通常) col2 非表示状態としています。

テーブル構造確認

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> desc t1;
+-------+------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra     |
+-------+------+------+-----+---------+-----------+
| col1  | int  | YES  |     | NULL    |           |
| col2  | int  | YES  |     | NULL    | INVISIBLE |
+-------+------+------+-----+---------+-----------+
2 rows in set (0.00 sec)

mysql> desc t1\G;
*************************** 1. row ***************************
  Field: col1
   Type: int
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: col2
   Type: int
   Null: YES
    Key:
Default: NULL
  Extra: INVISIBLE
2 rows in set (0.00 sec)

どうやら、DESCや SHOW CREATE TABLE だと、非表示の列は表示されるようですね。

されないものだと思ってました。

Oracleは表示されない仕様らしい。

いずれは、そうなりそう。

データ挿入

適当なデータ挿入。

mysql> INSERT INTO t1 (col1, col2) VALUES(1, 2), (3, 4);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

再度テーブル構造やデータ確認します。

データ挿入後SELECT文

アスタリスクでのSELECT文とカラムを明示的に指定したときの比較。

mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT col1,col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

col2列が非表示になっている。

データ挿入後テーブル構造

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> desc t1\G;
*************************** 1. row ***************************
  Field: col1
   Type: int
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: col2
   Type: int
   Null: YES
    Key:
Default: NULL
  Extra: INVISIBLE
2 rows in set (0.00 sec)

構造には特に変化なし。

実験

Oracleやリリースノートをみてて気になったことを試した見る。

暗黙のINSERT

明示的なINSERTではない暗黙のINSERTを検証してみます。

明示的

INSERT INTO t1 VALUES(5,6);

mysql> INSERT INTO t1 VALUES(5,6);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

エラーになる。

暗黙的

INSERT INTO t1(col1,col2) VALUES(7,8);

mysql> INSERT INTO t1(col1,col2) VALUES(7,8);
Query OK, 1 row affected (0.12 sec)

mysql> select * from t1;
+------+
| col1 |
+------+
|    1 |
|    3 |
|    7 |
+------+
3 rows in set (0.00 sec)

mysql> select col1,col2 from t1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    7 |    8 |
+------+------+
3 rows in set (0.00 sec)

明示的な指定では、問題なくデータが挿入される。

INVISIBLE列にINDEX作成

非表示の列に対して、INDEXを作成したらINDEXは使用されるのか検証。

mysql> create index invisivle_index_col2 on t1(col2);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

問題なく作成完了。

そして、SELECT文実行。

mysql> explain select * from t1 where col2 = 2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: invisivle_index_col2
          key: invisivle_index_col2
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

問題なく作成され、使用された。

全ての列をINVISIBLE

公式リファレンスだと、全ての列に対しては指定できないと書いてある。

テーブルには、少なくとも1つの表示列が必要です。すべての列を非表示にしようとすると、エラーが発生します。

エラーを発生させてみます。

mysql> ALTER TABLE t1 ALTER COLUMN col1 SET INVISIBLE;
ERROR 4028 (HY000): A table must have at least one visible column.

翻訳↓

ERROR 4028 (HY000)。テーブルには少なくとも 1 つの可視列が必要です。

記述通りエラー発生。

〆

気づかず同様の列を追加したり何かしら問題を起こしてトリガーとかみたいに使用を制限されそうな機能だと思いました。

OracleとMySQLで少し仕様が違うのは今後仕様追加でOracleのようになりそうですね。

面白い機能でしたが、普段使いはしなさそう。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQL8.0.23で追加された非表示の列の日。

目次