MySQL :: MySQL 8.0 リファレンスマニュアル

9.1 English
9.0 English
8.4 English
8.0 English

MySQL 8.0 リファレンスマニュアル / ... / 非表示カラム

このページは機械翻訳したものです。

13.1.20.10 非表示カラム

MySQL では、MySQL 8.0.23 の時点で非表示カラムがサポートされています。非表示のカラムは通常、クエリーでは非表示ですが、明示的に参照されている場合はアクセスできます。 MySQL 8.0.23 より前は、すべてのカラムが表示されます。

不可視のカラムが役立つ場合の図として、アプリケーションが SELECT * クエリーを使用してテーブルにアクセスし、アプリケーションが予期しない新しいカラムを追加するようにテーブルを変更した場合でも、変更せずに作業を続行する必要があるとします。 SELECT * クエリーでは、* は非表示のカラムを除くすべてのテーブルのカラムに評価されるため、解決策は新しいカラムを非表示のカラムとして追加することです。カラムは SELECT * クエリーから「「非表示」」のままであり、アプリケーションは引き続き以前と同様に動作します。アプリケーションの新しいバージョンは、明示的に参照することで、必要に応じて不可視のカラムを参照できます。

次の各セクションでは、MySQL で非表示カラムを処理する方法について説明します。

DDL ステートメントと非表示カラム

カラムはデフォルトで可視化されます。新しいカラムの可視性を明示的に指定するには、CREATE TABLE または ALTER TABLE のカラム定義の一部として VISIBLE または INVISIBLE キーワードを使用します:

CREATE TABLE t1 (
  i INT,
  j DATE INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE t1 ADD COLUMN k INT INVISIBLE;

既存のカラムの可視性を変更するには、VISIBLE または INVISIBLE キーワードをいずれかの ALTER TABLE カラム変更句とともに使用します:

ALTER TABLE t1 CHANGE COLUMN j j DATE VISIBLE;
ALTER TABLE t1 MODIFY COLUMN j DATE INVISIBLE;
ALTER TABLE t1 ALTER COLUMN j SET VISIBLE;

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

非表示カラムでは、通常のカラム属性がサポートされます: NULL, NOT NULL, AUTO_INCREMENT など。

生成されたカラムは非表示にできます。

インデックス定義では、PRIMARY KEY インデックスや UNIQUE インデックスの定義など、不可視のカラムに名前を付けることができます。テーブルには 1 つ以上の可視カラムが必要ですが、インデックス定義には可視カラムは必要ありません。

テーブルから削除された不可視のカラムは、通常の方法で、カラムに名前を付けるインデックス定義から削除されます。

外部キー制約は非表示カラムに定義でき、外部キー制約は非表示カラムを参照できます。

CHECK の制約は、不可視のカラムに対して定義できます。新規または変更された行の場合、非表示カラムに対する CHECK 制約に違反するとエラーが発生します。

CREATE TABLE ... LIKE には不可視のカラムが含まれており、新しいテーブルでは不可視です。

CREATE TABLE ... SELECT には、SELECT 部分で明示的に参照されないかぎり、不可視のカラムは含まれません。ただし、明示的に参照されている場合でも、既存のテーブルに表示されないカラムは新しいテーブルに表示されます:

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

非表示を保持する必要がある場合は、CREATE TABLE ... SELECT ステートメントの CREATE TABLE 部分で非表示カラムの定義を指定します:

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 (col2 INT INVISIBLE) AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ビューは、ビューを定義する SELECT ステートメントで非表示カラムを明示的に参照することで、非表示カラムを参照できます。カラムを参照するビューを定義した後にカラムの可視性を変更しても、ビューの動作は変わりません。

DML ステートメントと非表示カラム

SELECT ステートメントでは、選択リストで明示的に参照されないかぎり、非表示カラムは結果セットの一部になりません。選択リストでは、* および tbl_name.* の短縮形に非表示カラムは含まれません。自然結合には、非表示のカラムは含まれません。

次のステートメントの順序を考えてみます:

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> INSERT INTO t1 (col1, col2) VALUES(1, 2), (3, 4);

mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
|    1 |
|    3 |
+------+

mysql> SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+

最初の SELECT は、選択リストの非表示カラム col2 を参照しません (* には非表示カラムが含まれていないため)。したがって、col2 はステートメントの結果に表示されません。もう一方の SELECT は col2 を参照するため、結果に表示されます。

新しい行を作成するステートメントの場合、明示的に参照されて値が割り当てられないかぎり、非表示カラムには暗黙的なデフォルト値が割り当てられます。暗黙的なデフォルトの詳細は、暗黙的なデフォルト処理を参照してください。

INSERT (および REPLACE の場合、非置換行の場合) では、カラムリストが欠落しているか、空のカラムリスト、または非表示のカラムを含まない空でないカラムリストを使用して、暗黙的なデフォルトの割当てが行われます:

CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
INSERT INTO t1 VALUES(...);
INSERT INTO t1 () VALUES(...);
INSERT INTO t1 (col1) VALUES(...);

最初の 2 つの INSERT ステートメントでは、VALUES() リストは表示カラムごとに値を指定する必要があり、非表示カラムは指定できません。 3 つ目の INSERT ステートメントでは、VALUES() リストに名前付きカラムの数と同じ数の値を指定する必要があります。

LOAD DATA および LOAD XML の場合、暗黙的なデフォルト割当ては、欠落しているカラムリストまたは非表示のカラムを含まない空でないカラムリストで発生します。入力行に非表示カラムの値を含めないでください。

前述のステートメントに暗黙的なデフォルト以外の値を割り当てるには、カラムリストで非表示カラムに明示的に名前を付け、その値を指定します。

* には非表示カラムが含まれていないため、INSERT INTO ... SELECT * および REPLACE INTO ... SELECT * には非表示カラムは含まれていません。暗黙的なデフォルトの割当ては、前述のとおりに行われます。

新しい行を挿入または無視するステートメント、あるいは PRIMARY KEY インデックスまたは UNIQUE インデックスの値に基づいて既存の行を置換または変更するステートメントの場合、MySQL は非表示カラムを表示カラムと同じように処理: 非表示カラムはキー値の比較に関与します。具体的には、新しい行が一意キー値の既存の行と同じ値を持つ場合、インデックスカラムが可視か不可視かにかかわらず、次の動作が発生します:

IGNORE 修飾子を使用すると、INSERT、LOAD DATA および LOAD XML は新しい行を無視します。
REPLACE は、既存の行を新しい行に置き換えます。 REPLACE 修飾子を使用すると、LOAD DATA と LOAD XML は同じことを行います。
INSERT ... ON DUPLICATE KEY UPDATE によって既存の行が更新されます。

UPDATE ステートメントの非表示カラムを更新するには、表示カラムの場合と同様に、非表示カラムに名前を付けて値を割り当てます。

非表示カラムのメタデータ

カラムが表示可能か非表示かに関する情報は、INFORMATION_SCHEMA.COLUMNS テーブルまたは SHOW COLUMNS 出力の EXTRA カラムから入手できます。例:

mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA     |
+------------+-------------+-----------+
| t1         | i           |           |
| t1         | j           |           |
| t1         | k           | INVISIBLE |
+------------+-------------+-----------+

カラムはデフォルトで表示されるため、EXTRA では表示情報は表示されません。非表示カラムの場合、EXTRA には INVISIBLE が表示されます。

SHOW CREATE TABLE では、テーブル定義に非表示のカラムが表示され、バージョン固有のコメントには INVISIBLE キーワードが含まれます:

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int DEFAULT NULL,
  `j` int DEFAULT NULL,
  `k` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysqldump および mysqlpump では SHOW CREATE TABLE が使用されるため、ダンプされたテーブル定義に非表示のカラムが含まれます。また、ダンプされたデータには非表示のカラム値も含まれます。

不可視のカラムをサポートしていない古いバージョンの MySQL にダンプファイルをリロードすると、バージョン固有のコメントが無視され、不可視のカラムが可視として作成されます。

バイナリログと不可視のカラム

MySQL では、非表示カラムはバイナリログ内のイベントに関して次のように処理されます:

テーブル作成イベントには、非表示カラムの INVISIBLE 属性が含まれます。
不可視のカラムは、行イベントで可視のカラムと同様に扱われます。これらは、必要に応じて binlog_row_image システム変数の設定に従って含まれます。
行イベントが適用されると、不可視のカラムは行イベントの可視カラムと同様に扱われます。特に、使用するアルゴリズムおよびインデックスは、slave_rows_search_algorithms システム変数の設定に従って選択されます。
非表示カラムは、書込みセットの計算時に可視カラムと同様に扱われます。特に、書込みセットには不可視のカラムに定義されたインデックスが含まれます。
mysqlbinlog コマンドには、カラムメタデータの可視性が含まれます。

PREV HOME UP NEXT