MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20.8 CREATE TABLE および生成されるカラム

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / CREATE TABLE および生成されるカラム

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

13.1.20.8 CREATE TABLE および生成されるカラム

CREATE TABLE では、生成されるカラムの指定がサポートされています。生成されたカラムの値は、カラム定義に含まれる式から計算されます。

生成されたカラムは、NDB ストレージエンジンでもサポートされます。

次の単純な例は、sidea カラムおよび sideb カラムに右側の三角形の長さを格納し、sidec のハイフンの長さを計算するテーブルを示しています (もう一方の辺の二乗の合計の平方根):

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

テーブルから選択すると、次の結果になります:

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

triangle テーブルを使用するアプリケーションは、それらを計算する式を指定しなくても、ハイフン値にアクセスできます。

生成されるカラム定義の構文は次のとおりです:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

AS (expr) は、カラムが生成されることを示し、カラム値の計算に使用される式を定義します。カラムの生成された性質をより明確にするために、AS の前に GENERATED ALWAYS を付けることができます。式で許可または禁止されている構造体については、あとで説明します。

VIRTUAL または STORED キーワードは、カラム値の格納方法を示します。これは、カラムの使用に影響します:

VIRTUAL: カラム値は格納されませんが、BEFORE トリガーの直後に行が読み取られたときに評価されます。仮想カラムは記憶域を取りません。

InnoDB は、仮想カラムのセカンダリインデックスをサポートしています。セクション13.1.20.9「セカンダリインデックスと生成されたカラム」を参照してください。
STORED: カラム値は、行の挿入または更新時に評価および格納されます。ストアドカラムには記憶領域が必要であり、インデックス付けできます。

どちらのキーワードも指定されていない場合、デフォルトは VIRTUAL です。

テーブル内で VIRTUAL カラムと STORED カラムを混在させることができます。

カラムがインデックス付けされているか、NULL であるか、またはコメントを提供できるかを示す他の属性を指定できます。

生成されるカラム式は、次のルールに従う必要があります。許可されていない構造が式に含まれている場合は、エラーが発生します。

リテラル、決定的組込み関数および演算子を使用できます。関数は、テーブル内の同じデータが指定された場合、接続ユーザーとは関係なく、複数の起動で同じ結果が生成される場合は決定論的です。非決定的で、この定義に失敗する関数の例: CONNECTION_ID(), CURRENT_USER(), NOW()。
ストアドファンクションおよびユーザー定義関数は使用できません。
ストアドプロシージャおよびストアドファンクションのパラメータは使用できません。
変数 (システム変数、ユーザー定義変数およびストアドプログラムローカル変数) は使用できません。
サブクエリーは許可されません。
生成されたカラム定義は、生成された他のカラムを参照できますが、テーブル定義で以前に発生したカラムのみを参照できます。生成されたカラム定義は、その定義が以前に発生したか後で発生したかに関係なく、テーブル内の任意のベース (生成されていない) カラムを参照できます。
AUTO_INCREMENT 属性は、生成されたカラム定義では使用できません。
生成されたカラム定義で AUTO_INCREMENT カラムをベースカラムとして使用することはできません。
式の評価によって切捨てが発生した場合、または関数への入力が正しくない場合、CREATE TABLE ステートメントはエラーで終了し、DDL 操作は拒否されます。

式が宣言されたカラム型とは異なるデータ型に評価された場合、宣言された型への暗黙的な強制は、通常の MySQL 型変換ルールに従って行われます。セクション12.3「式評価での型変換」を参照してください。

生成されたカラムが TIMESTAMP データ型を使用している場合、explicit_defaults_for_timestamp の設定は無視されます。このような場合、この変数を無効にすると、NULL は CURRENT_TIMESTAMP に変換されません。 MySQL 8.0.22 以降では、カラムが NOT NULL としても宣言されている場合、NULL を挿入しようとすると ER_BAD_NULL_ERROR で明示的に拒否されます。

注記

式の評価では、評価時に有効な SQL モードが使用されます。式のいずれかのコンポーネントが SQL モードに依存している場合、すべての使用中に SQL モードが同じでないかぎり、テーブルの使用方法によって結果が異なることがあります。

CREATE TABLE ... LIKE の場合、宛先テーブルには元のテーブルから生成されたカラム情報が保持されます。

CREATE TABLE ... SELECT の場合、宛先テーブルでは、選択元テーブルのカラムが生成されたカラムであるかどうかに関する情報は保持されません。ステートメントの SELECT 部分では、宛先テーブルの生成されたカラムに値を割り当てることはできません。

生成されたカラムによるパーティション化が許可されます。テーブルのパーティション化を参照してください。

格納された生成カラムに対する外部キー制約では、CASCADE、SET NULL または SET DEFAULT を ON UPDATE 参照アクションとして使用することも、SET NULL または SET DEFAULT を ON DELETE 参照アクションとして使用することもできません。

格納された生成カラムのベースカラムに対する外部キー制約では、CASCADE、SET NULL または SET DEFAULT を ON UPDATE または ON DELETE の参照アクションとして使用できません。

外部キー制約は、仮想生成カラムを参照できません。

トリガーは、NEW.col_name または OLD.col_name を使用して生成されたカラムを参照することはできません。

INSERT、REPLACE および UPDATE では、生成されたカラムが明示的に挿入、置換または更新される場合、許可される値は DEFAULT のみです。

ビュー内の生成されたカラムは、割り当て可能であるため、更新可能とみなされます。ただし、このようなカラムが明示的に更新される場合、許可される値は DEFAULT のみです。

生成されるカラムには、次のようないくつかのユースケースがあります:

仮想生成カラムは、クエリーを簡略化および統合する方法として使用できます。複雑な条件を生成されたカラムとして定義し、テーブルに対する複数のクエリーから参照して、すべてのクエリーが完全に同じ条件を使用するようにできます。
格納された生成カラムは、即時計算にコストがかかる複雑な条件の実体化キャッシュとして使用できます。
生成されたカラムは関数インデックスをシミュレートできます: 生成されたカラムを使用して関数式を定義し、インデックス付けします。これは、JSON カラムなど、直接インデックス付けできない型のカラムを操作する場合に役立ちます。詳細な例は、JSON カラムインデックスを提供するための生成されたカラムのインデックス付けを参照してください。

格納された生成カラムの場合、このアプローチのデメリットは、値が生成されたカラムの値として 2 回格納され、インデックスに 1 回格納されることです。
生成されたカラムがインデックス付けされている場合、オプティマイザはカラム定義に一致するクエリー式を認識し、クエリーがそのカラムを名前で直接参照しない場合でも、クエリーの実行中にカラムのインデックスを適宜使用します。詳細は、セクション8.3.11「生成されたカラムインデックスのオプティマイザによる使用」を参照してください。

例:

テーブル t1 に first_name カラムと last_name カラムが含まれており、アプリケーションが次のような式を使用してフルネームを頻繁に構成するとします:

SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;

式を書き出さないようにするには、t1 でビュー v1 を作成します。これにより、式を使用せずに full_name を直接選択できるため、アプリケーションが簡略化されます:

CREATE VIEW v1 AS
SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;

SELECT full_name FROM v1;

生成されたカラムを使用すると、ビューを定義せずに、アプリケーションで full_name を直接選択することもできます:

CREATE TABLE t1 (
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))
);

SELECT full_name FROM t1;

PREV HOME UP NEXT