10bet网址
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr)- 26.8Mb
PDF (A4)- 26.8Mb


13.1.17 CREATE TABLE 構文

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name(create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name[(create_definition,...)] [table_options] [partition_options]select_statementCREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name{ LIKEold_tbl_name| (LIKEold_tbl_name) }create_definition:col_namecolumn_definition| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)reference_definition| CHECK (expr)column_definition:data_type[NOT NULL | NULL] [DEFAULTdefault_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition]data_type: BIT[(length)] | TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] | DATE | TIME[(fsp)] | TIMESTAMP[(fsp)] | DATETIME[(fsp)] | YEAR | CHAR[(length)] [CHARACTER SETcharset_name] [COLLATEcollation_name] | VARCHAR(length) [CHARACTER SETcharset_name] [COLLATEcollation_name] | BINARY[(length)] | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SETcharset_name] [COLLATEcollation_name] | TEXT [BINARY] [CHARACTER SETcharset_name] [COLLATEcollation_name] | MEDIUMTEXT [BINARY] [CHARACTER SETcharset_name] [COLLATEcollation_name] | LONGTEXT [BINARY] [CHARACTER SETcharset_name] [COLLATEcollation_name] | ENUM(value1,value2,value3,...) [CHARACTER SETcharset_name] [COLLATEcollation_name] | SET(value1,value2,value3,...) [CHARACTER SETcharset_name] [COLLATEcollation_name] |spatial_typeindex_col_name:col_name[(length)] [ASC | DESC]index_type: USING {BTREE | HASH}index_option: KEY_BLOCK_SIZE [=]value|index_type| WITH PARSERparser_name| COMMENT 'string'reference_definition: REFERENCEStbl_name(index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETEreference_option] [ON UPDATEreference_option]reference_option: RESTRICT | CASCADE | SET NULL | NO ACTIONtable_options:table_option[[,]table_option] ...table_option: ENGINE [=]engine_name| AUTO_INCREMENT [=]value| AVG_ROW_LENGTH [=]value|(默认)字符集(=)charset_name| CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=]collation_name| COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | DATA DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | INDEX DIRECTORY [=] 'absolute path to directory' | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=]value| MAX_ROWS [=]value| MIN_ROWS [=]value| PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=]value| TABLESPACEtablespace_name[STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...)partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONSnum] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) } [SUBPARTITIONSnum] ] [(partition_definition[,partition_definition] ...)]partition_definition: PARTITIONpartition_name[VALUES {LESS THAN {(expr|value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=]engine_name] [COMMENT [=]'comment_text'] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=]max_number_of_rows] [MIN_ROWS [=]min_number_of_rows] [TABLESPACE [=]tablespace_name] [NODEGROUP [=]node_group_id] [(subpartition_definition[,subpartition_definition] ...)]subpartition_definition: SUBPARTITIONlogical_name[[STORAGE] ENGINE [=]engine_name] [COMMENT [=]'comment_text'] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=]max_number_of_rows] [MIN_ROWS [=]min_number_of_rows] [TABLESPACE [=]tablespace_name] [NODEGROUP [=]node_group_id]select_statement:[IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement)

CREATE TABLEは、指定された名前を持つテーブルを作成します。このテーブルに対するCREATE権限が必要です。

許可されるテーブル名のルールは、セクション9.2「スキーマオブジェクト名」に示されています。デフォルトでは、テーブルは我nnoDBストレージエンジンを使用して、デフォルトデータベース内に作成されます。テーブルがすでに存在する場合、デフォルトデータベースが存在しない場合、またはデータベースが存在しない場合はエラーが発生します。

特定のデータベース内にテーブルを作成するには、テーブル名をdb_name.tbl_nameとして指定できます。そのデータベースが存在すると仮定すると、これは、デフォルトデータベースが存在するかどうかには関係なく機能します。引用符で囲まれた識別子を使用する場合は、データベース名とテーブル名を個別に引用符で囲みます。たとえば、`mydb.mytbl`ではなく、`mydb`.`mytbl`と記述します。

一時テーブル

テーブルの作成時にTEMPORARYキーワードを使用できます。TEMPORARYテーブルは現在のセッションにのみ表示され、そのセッションが閉じられると自動的に削除されます。つまり、2 つの異なるセッションが同じ一時テーブル名を使用することができ、互いに、または同じ名前の既存のTEMPORARY以外のテーブルと競合することはありません。(既存のテーブルは、一時テーブルが削除されるまで非表示になります。)一時テーブルを作成するには、CREATE TEMPORARY TABLES権限が必要です。

注記

TEMPORARYキーワードを使用した場合、CREATE TABLEは、現在のアクティブなトランザクションを自動的にはコミットしません。

注記

TEMPORARYテーブルは、データベース (スキーマ) と非常に疎な関係を持っています。データベースを削除しても、そのデータベース内で作成されたどのTEMPORARYテーブルも自動的には削除されません。また、CREATE TABLEステートメントでテーブル名をデータベース名で修飾した場合は、存在しないデータベース内にTEMPORARYテーブルを作成することもできます。この場合は、そのテーブルへの以降のすべての参照をデータベース名で修飾する必要があります。

同じ名前を持つ既存のテーブル

キーワード我F NOT EXISTSは、テーブルがすでに存在する場合にエラーが発生しないようにします。ただし、既存のテーブルの構造がCREATE TABLEステートメントによって示されている構造と同一であることの検証は行われません。

物理表現

MySQL は、各テーブルを、データベースディレクトリ内にある.frmテーブル形式 (定義) ファイルで表します。そのテーブルのストレージエンジンによって、ほかのファイルが作成されることもあります。

我nnoDBテーブルの場合、ファイルストレージは、innodb_file_per_table構成オプションによって制御されます。このオプションがオフになっている場合、我nnoDBテーブルおよびインデックスはすべて、1 つ以上の.ibd ファイルによって表されるシステムテーブルスペースに格納されます。このオプションがオンになっているときに作成された各我nnoDBテーブルでは、テーブルデータとそれに関連付けられたすべてのインデックスは、データベースディレクトリ内にある.ibd ファイルに格納されます。

MyISAMテーブルの場合は、ストレージエンジンがデータおよびインデックスファイルを作成します。そのため、MyISAMテーブルtbl_nameごとに 3 つのディスクファイルが存在します。

ファイル 目的
tbl_name.frm テーブル形式 (定義) ファイル
tbl_name.MYD データファイル
tbl_name.MYI インデックスファイル

第15章「代替ストレージエンジンでは、テーブルを表すために各ストレージエンジンがどのようなファイルを作成するかについて説明しています。テーブル名に特殊文字が含まれている場合は、セクション9.2.3「識別子とファイル名のマッピング」で説明されているように、その文字のエンコードされたバージョンがテーブルファイルの名前に含まれます。

カラムのデータ型および属性

data_typeは、カラム定義内のデータ型を表します。spatial_typeは,空間データ型を表します。示されているデータ型の構文は代表的な例にすぎません。カラムデータ型を指定するために使用できる構文の完全な説明や、各型のプロパティーに関する情報については、第11章「データ型およびセクション11.5「空間データの拡張」を参照してください。

属性の中には、すべてのデータ型には適用されないものがあります。AUTO_INCREMENTは、整数型と浮動小数点型にのみ適用されます。DEFAULTは、BLOBまたはTEXT型には適用されません。

  • NULLNOT NULLのどちらも指定されていない場合、そのカラムはNULLが指定されたかのように処理されます。

  • 整数または浮動小数点のカラムには、追加の属性AUTO_INCREMENTを指定できます。インデックスが設定されたAUTO_INCREMENTカラムにNULL(推奨) または0の値を挿入すると、カラムは次のシーケンス値に設定されます。通常、これはvalue+1です。ここでvalueは現在テーブルにあるカラムの最大値です。AUTO_INCREMENTシーケンスは1で始まります。

    行を挿入したあとにAUTO_INCREMENT値を取得するには、LAST_INSERT_ID()SQL 関数またはmysql_insert_id()C API 関数を使用します。セクション12.14「情報関数」およびセクション23.7.7.37「mysql_insert_id()」を参照してください。

    NO_AUTO_VALUE_ON_ZEROSQL モードが有効になっている場合は、新しいシーケンス値を生成することなく、0AUTO_INCREMENTカラム内に0として格納できます。セクション5.1.7「サーバー SQL モード」を参照してください。

    注記

    テーブルごとに存在できるAUTO_INCREMENTカラムは 1 つだけです。このカラムはインデックス付きである必要があり、DEFAULT値を割り当てることはできません。AUTO_INCREMENTカラムは、正の値だけが含まれている場合にのみ正しく機能します。負の数を挿入すると、非常に大きな正の数を挿入したと見なされます。これは、数字が正から負にラップするときの精度の問題を回避すると同時に、0を含むAUTO_INCREMENTカラムを誤って取得してしまわないようにするために行われます。

    MyISAMテーブルの場合は、マルチカラムキー内のAUTO_INCREMENTセカンダリカラムを指定できます。セクション3.6.9「AUTO_INCREMENT の使用」を参照してください。

    MySQL を一部の ODBC アプリケーションと互換性があるようにするために、次のクエリーを使用して、最後に挿入された行のAUTO_INCREMENT値を見つけることができます。

    SELECT * FROMtbl_nameWHEREauto_col我S NULL

    我nnoDBAUTO_INCREMENTについては、セクション14.6.5「InnoDB での AUTO_INCREMENT 処理」を参照してください。AUTO_INCREMENTと MySQL レプリケーションについては、セクション17.4.1.1「レプリケーションと AUTO_INCREMENT」を参照してください。

  • 文字データ型 (CHARVARCHARTEXT) には、そのカラムの文字セットと照合順序を指定するためのCHARACTER SETおよびCOLLATE属性を含めることができます。詳細は、セクション10.1「文字セットのサポート」を参照してください。CHARSETCHARACTER SETのシノニムです。例:

    CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

    MySQL 5.6 は、文字カラム定義内の長さの指定を文字数で解釈します。(MySQL 4.1 より前のバージョンでは、バイト単位で解釈されました。)BINARYVARBINARYの長さはバイト単位です。

  • DEFAULT句は、カラムのデフォルト値を指定します。例外が 1 つあります。デフォルト値は定数である必要があるので、関数または式にはできません。これは、たとえば日付カラムのデフォルト値にNOW()CURRENT_DATEなどの関数の値を設定できないことを意味します。例外として、TIMESTAMPまたは (MySQL 5.6.5 の時点では)DATETIMEカラムのデフォルトとしてCURRENT_TIMESTAMPを指定できることがあります。セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」を参照してください。

    カラム定義に明示的なDEFAULT値が含まれていない場合、MySQL は、セクション11.6「データ型デフォルト値」で説明されているようにデフォルト値を決定します。

    BLOBおよびTEXTカラムにはデフォルト値を割り当てられません。

    NO_ZERO_DATEまたはNO_ZERO_IN_DATESQL モードが有効になっているときに、日付の値のデフォルトがそのモードに従って正しくない場合、CREATE TABLEでは厳密な SQL モードが有効になっていない場合は警告を、厳密モードが有効になっている場合はエラーを生成します。たとえば、NO_ZERO_IN_DATEが有効になっている場合は、c1 DATE DEFAULT '2010-00-00'によって警告が生成されます。(MySQL 5.6.6 より前は、厳密モードが有効になっていない場合でも、このステートメントはエラーを生成します。)

  • COMMENTオプションを使用して、カラムのコメントを最大 1024 文字の長さで指定できます。このコメントは、SHOW CREATE TABLEおよびSHOW FULL COLUMNSステートメントによって表示されます。

  • MySQL Cluster では、COLUMN_FORMATを使用して、NDBテーブルの個々のカラムのデータストレージフォーマットを指定することもできます。許可されるカラムフォーマットは、FIXEDDYNAMIC、およびDEFAULTです。FIXEDは固定幅のストレージを指定するために使用され、DYNAMICはカラムが可変幅になることを許可し、DEFAULTはカラムで、そのカラムのデータ型によって決定される固定幅または可変幅のストレージが使用されるようにします (ROW_FORMAT指定子によってオーバーライドされる可能性があります)。

    NDBテーブルの場合、COLUMN_FORMATのデフォルト値はDEFAULTです。

    COLUMN_FORMATは現在、NDB以外のストレージエンジンを使用しているテーブルのカラムには影響を与えません。MySQL 5.6 以降では、COLUMN_FORMATは暗黙のうちに無視されます。

  • NDBテーブルの場合は、STORAGE句を使用して、カラムがディスク上またはメモリー内のどちらに格納されるかを指定することもできます。STORAGE DISKを指定するとカラムはディスク上に格納され、STORAGE MEMORYを指定するとインメモリーストレージが使用されます。使用されるCREATE TABLEステートメントには、引き続きTABLESPACE句が含まれている必要があります。

    mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) ENGINE NDB; ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140) mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) TABLESPACE ts_1 ENGINE NDB; Query OK, 0 rows affected (1.06 sec)

    NDBテーブルの場合、STORAGE DEFAULTSTORAGE MEMORYと同等です。

    STORAGE句は、NDB以外のストレージエンジンを使用しているテーブルには影響を与えません。STORAGEキーワードは、MySQL Cluster に付属のmysqldの構築でのみサポートされます。ほかのどのバージョンの MySQL でも認識されません。その場合は、STORAGEキーワードを使用しようとすると、必ず構文エラーが発生します。

  • KEYは通常、我NDEXのシノニムです。キー属性PRIMARY KEYもまた、カラム定義内で指定する場合は、単にKEYとして指定できます。これは、ほかのデータベースシステムとの互換性のために実装されました。

  • UNIQUEインデックスは、そのインデックス内のすべての値が異なっている必要があるという制約を作成します。既存の行に一致するキー値を持つ新しい行を追加しようとすると、エラーが発生します。すべてのエンジンについて、UNIQUEインデックスは、NULLを含むことができるカラムでの複数のNULL値を許可します。

  • PRIMARY KEYは、すべてのキーカラムをNOT NULLとして定義する必要のある一意のインデックスです。それらがNOT NULLとして明示的に宣言されていない場合、MySQL は、それらを暗黙的に (かつ警告なしで) そのように宣言します。テーブルに存在できるPRIMARY KEYは 1 つだけです。PRIMARY KEYの名前は、常にPRIMARYです。そのため、これをその他のどの種類のインデックスの名前としても使用できません。

    PRIMARY KEYが存在しないときに、アプリケーションがテーブル内のPRIMARY KEYを要求した場合、MySQL は、NULLカラムのない最初のUNIQUEインデックスをPRIMARY KEYとして返します。

    我nnoDBテーブルでは、セカンダリインデックスのためのストレージのオーバーヘッドを最小限に抑えるために、PRIMARY KEYを短い値に維持してください。各セカンダリインデックスエントリには、対応する行の主キーカラムのコピーが含まれています。(セクション14.2.13「InnoDB テーブルおよびインデックスの構造」を参照してください。)

  • 作成されたテーブルでは、PRIMARY KEYが最初に配置され、そのあとにすべてのUNIQUEインデックス、さらに一意でないインデックスが続きます。これは、MySQL オプティマイザが、使用するインデックスに優先順位を付けたり、重複したUNIQUEキーをよりすばやく検出したりするのに役立ちます。

  • PRIMARY KEYをマルチカラムインデックスにすることができます。ただし、カラム指定でPRIMARY KEYキー属性を使用してマルチカラムインデックスを作成することはできません。それを行なっても、その単一カラムがプライマリとしてマークされるだけです。個別のPRIMARY KEY(index_col_name, ...)句を使用する必要があります。

  • PRIMARY KEYまたはUNIQUEインデックスが、整数型を含む 1 つのカラムのみで構成されている場合は、SELECTステートメントでそのカラムを_rowidとして参照することもできます。

  • MySQL では、PRIMARY KEYの名前はPRIMARYです。その他のインデックスでは、名前を割り当てなかった場合、そのインデックスには最初のインデックス付きカラムと同じ名前が割り当てられ、それを一意にするためにオプションのサフィクス (_2_3...) が付けられます。テーブルのインデックス名は、SHOW INDEX FROMtbl_nameを使用して確認できます。セクション13.7.5.23「SHOW INDEX 構文」を参照してください。

  • 一部のストレージエンジンでは、インデックスの作成時にインデックスタイプを指定できます。index_type指定子の構文は、USINGtype_nameです。

    例:

    CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;

    USINGの推奨される位置は、インデックスカラムリストのあとです。カラムリストの前にも指定できますが、このオプションをその位置で使用するためのサポートは非推奨であり、将来の MySQL リリースで削除される予定です。

    index_option値は、インデックスの追加オプションを指定します。USINGはそのようなオプションの 1 つです。許可されるindex_option値の詳細は、セクション13.1.13「CREATE INDEX 構文」を参照してください。

    インデックスの詳細は、セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。

  • MySQL 5.6 では、NULL値を持つことができるカラム上のインデックスをサポートするのは我nnoDBMyISAM、およびMEMORYだけです。それ以外の場合は、インデックス付きカラムをNOT NULLとして宣言する必要があります。そうしないと、エラー結果が発生します。

  • CHARVARCHARBINARY、およびVARBINARYカラムの場合は、col_name(length)構文を使用してインデックスプリフィクス長を指定することにより、カラム値の先頭の部分のみを使用するインデックスを作成できます。BLOBおよびTEXTカラムにもインデックスを設定できますが、プリフィクス長を指定する必要があります。プリフィクス長は、バイナリ以外の文字列型の場合は文字数で、バイナリ文字列型の場合はバイト単位で指定されます。つまり、インデックスエントリは、CHARVARCHAR、およびTEXTカラムの場合は各カラム値の最初のlength文字、BINARYVARBINARY、およびBLOBカラムの場合は各カラム値の最初のlengthバイトで構成されます。このようにカラム値のプリフィクスのみにインデックスを設定すると、インデックスファイルをはるかに小さくできます。セクション8.3.4「カラムインデックス」を参照してください。

    BLOBおよびTEXTカラム上のインデックス設定をサポートするのは、我nnoDBおよびMyISAMストレージエンジンだけです。例:

    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

    我nnoDBテーブルではプリフィクスの長さを最大 767 バイトに、またinnodb_large_prefixオプションが有効になっている場合は 3072 バイトにすることができます。プリフィクスの制限がバイト単位で測定されるのに対して、CREATE TABLEステートメントでのプリフィクス長は、バイナリ以外のデータ型 (CHARVARCHARTEXT) では文字数として解釈されます。複数バイトの文字セットを使用するカラムのプリフィクス長を指定する場合は、この点を考慮に入れてください。

  • index_col_nameの指定をASCまたはDESCで終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

  • SELECT内でカラムに対してORDER BYまたはGROUP BYを使用すると、サーバーは、max_sort_lengthシステム変数によって示されている初期のバイト数のみを使用して値をソートします。

  • 全文検索に使用される特殊なFULLTEXTインデックスを作成できます。FULLTEXTインデックスをサポートするのは、我nnoDBおよびMyISAMだけです。これらは、CHARVARCHAR、およびTEXTカラムからのみ作成できます。インデックス設定は常に、カラム全体に対して実行されます。カラムプリフィクスのインデックス設定はサポートされていないため、プリフィクス長が指定されてもすべて無視されます。操作の詳細は、セクション12.9「全文検索関数」を参照してください。WITH PARSER句は、全文インデックス設定および検索操作に特殊な処理が必要な場合にパーサープラグインをインデックスに関連付けるために、index_option値として指定できます。この句は、FULLTEXTインデックスに対してのみ有効です。プラグインの作成の詳細は、セクション24.2「MySQL プラグイン API」を参照してください。

  • 空間データ型にSPATIALインデックスを作成できます。空間型はMyISAMテーブルでのみサポートされ、インデックス付きカラムをNOT NULLとして宣言する必要があります。セクション11.5「空間データの拡張」を参照してください。

  • MySQL 5.6 では、インデックス定義に最大 1024 文字のオプションのコメントを含めることができます。

  • 我nnoDBおよびNDBテーブルは、外部キー制約のチェックをサポートしています。参照されるテーブルのカラムには、常に明示的に名前を付ける必要があります。外部キーに対してはON DELETEON UPDATEの両方のアクションがサポートされています。詳細および例については、セクション13.1.17.2「外部キー制約の使用」を参照してください。我nnoDBでの外部キーに固有の情報については、セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。

    その他のストレージエンジンの場合、MySQL Server は、CREATE TABLEステートメント内のFOREIGN KEYおよびREFERENCES構文を解析して無視します。CHECK句は、すべてのストレージエンジンによって解析されますが、無視されます。セクション1.7.2.4「外部キーの違い」を参照してください。

    重要

    ANSI/ISO SQL 標準に精通しているユーザーの場合は、参照整合性の制約定義で使用されるMATCH句を認識または適用するストレージエンジンは (我nnoDBを含め) 存在しません。明示的なMATCH句を使用しても、指定された効果が得られないだけでなく、ON DELETEおよびON UPDATE句が無視される原因にもなります。これらの理由により、MATCHの指定は避けるようにしてください。

    SQL 標準でのMATCH句は、複合 (マルチカラム) 外部キー内のNULL値が、主キーとの比較時にどのように処理されるかを制御します。我nnoDBは基本的に、外部キーをすべてまたは部分的にNULLにすることが許可される、MATCH SIMPLEで定義されるセマンティクスを実装しています。その場合は、このような外部キーを含む (子テーブルの) 行の挿入が許可され、その行は参照される (親) テーブル内のどの行にも一致しません。トリガーを使用して、ほかのセマンティクスを実装できます。

    さらに、MySQL ではパフォーマンスのために、参照されるカラムにインデックスを設定する必要があります。ただし、参照されるカラムをUNIQUEまたはNOT NULLとして宣言するという要件は適用されません。一意でないキーまたはNULL値を含むキーへの外部キー参照の処理は、UPDATEDELETE CASCADEなどの操作に対して適切に定義されていません。UNIQUE(またはPRIMARY) とNOT NULLの両方であるキーのみを参照する外部キーを使用することをお勧めします。

    MySQL は、参照がカラム指定の一部として定義されている (SQL 標準で定義された)インラインのREFERENCES指定を認識せず、またサポートもしていません。MySQL は、個別のFOREIGN KEY指定の一部として指定されている場合にのみREFERENCES句を受け入れます。

    注記

    我nnoDBストレージエンジンを使用するパーティション化されたテーブルは、外部キーをサポートしていません。KEYまたはLINEAR KEYによってパーティション化されたNDBテーブルは、この制限によって影響を受けません。詳細については、セクション19.6「パーティショニングの制約と制限」を参照してください。

  • テーブルあたり 4096 カラムという強い制限値がありますが、特定のテーブルでは、実際の最大数がこれより少なくなる可能性があります。実際の最大数は、セクションD.10.4“テーブルカラム数と行サイズの制限”で説明されている要因によって異なります。

TABLESPACEおよびSTORAGEテーブルオプションは、NDBテーブルでのみ使用されます。tablespace_nameという名前のテーブルスペースが、すでにCREATE TABLESPACEを使用して作成されている必要があります。STORAGEは、使用されるストレージのタイプ (ディスクまたはメモリー) を決定するものであり、DISKMEMORYDEFAULTのいずれかです。

TABLESPACE ... STORAGE DISKは、MySQL Cluster ディスクデータテーブルスペースにテーブルを割り当てます。詳細は、セクション18.5.12「MySQL Cluster ディスクデータテーブル」を参照してください。

重要

STORAGE句を、TABLESPACE句のないCREATE TABLEステートメントで使用することはできません。

Storage Engines (ストレージエンジン)

ENGINEテーブルオプションは、次の表に示されている名前のいずれかを使用して、テーブルのストレージエンジンを指定します。エンジン名は、引用符で囲んでも囲まなくてもかまいません。引用符で囲まれた名前'DEFAULT'は認識されますが、無視されます。

ストレージエンジン 説明
我nnoDB 行ロックと外部キーを備えたトランザクションセーフテーブル。新しいテーブルのためのデフォルトのストレージエンジン。MySQL は経験しているが、我nnoDBがはじめてである場合は、第14章「我nnoDB ストレージエンジン、そのなかでも特にセクション14.1.1「デフォルトの MySQL ストレージエンジンとしての InnoDB」を参照してください。
MyISAM 主に読み取り専用または読み取りが大半のワークロードに使用される、バイナリの移植可能なストレージエンジン。セクション15.2「MyISAM ストレージエンジン」を参照してください。
MEMORY このストレージエンジンのデータは、メモリー内にのみ格納されます。セクション15.3「MEMORY ストレージエンジン」を参照してください。
CSV カンマ区切り値形式で行を格納するテーブル。セクション15.4「CSV ストレージエンジン」を参照してください。
ARCHIVE アーカイブストレージエンジン。セクション15.5「ARCHIVE ストレージエンジン」を参照してください。
EXAMPLE サンプルのエンジン。セクション15.9「EXAMPLE ストレージエンジン」を参照してください。
FEDERATED リモートテーブルにアクセスするストレージエンジン。セクション15.8「FEDERATED ストレージエンジン」を参照してください。
HEAP これはMEMORYのシノニムです。
MERGE 1 つのテーブルとして使用されるMyISAMテーブルのコレクション。MRG_MyISAMとも呼ばれます。セクション15.7「MERGE ストレージエンジン」を参照してください。
NDB トランザクションと外部キーをサポートする、クラスタ化された、耐障害の、メモリーベースのテーブル。NDBCLUSTERとも呼ばれます。第18章「MySQL Cluster NDB 7.3 および MySQL Cluster NDB 7.4を参照してください。

使用できないストレージエンジンが指定されている場合、MySQL は、代わりにデフォルトのエンジンを使用します。通常、これはMyISAMです。たとえば、テーブル定義にENGINE=INNODBオプションが含まれているが、MySQL サーバーが我NNODBテーブルをサポートしていない場合、テーブルはMyISAMテーブルとして作成されます。これにより、マスター上にはトランザクションテーブルが存在するが、スレーブ上に作成されるテーブルは (高速化のために) 非トランザクションであるようなレプリケーションセットアップを行うことが可能になります。MySQL 5.6 では、ストレージエンジンの指定が受け付けられない場合は警告が発生します。

セクション5.1.7「サーバー SQL モード」で説明されているように、NO_ENGINE_SUBSTITUTIONSQL モードの設定によってエンジンの置換を制御できます。

注記

ENGINEのシノニムであった古いTYPEオプションは、MySQL 5.5 で削除されました。MySQL 5.5 以降にアップグレードする場合は、TYPEに依存する既存のアプリケーションを、代わりにENGINEを使用するように変換する必要があります

パフォーマンスの最適化

その他のテーブルオプションは、テーブルの動作を最適化するために使用されます。ほとんどの場合は、それらのうちのどれも指定する必要はありません。特に示されていないかぎり、これらのオプションはすべてのストレージエンジンに適用されます。特定のストレージエンジンに適用されないオプションは、テーブル定義の一部として受け入れられ、記憶される可能性があります。それにより、あとでALTER TABLEを使用して、別のストレージエンジンを使用するようにテーブルを変換した場合に、このようなオプションが適用されます。

  • AUTO_INCREMENT

    テーブルの初期のAUTO_INCREMENT値。MySQL 5.6 では、これはMyISAMMEMORY我nnoDB、およびARCHIVEテーブルに対して機能します。AUTO_INCREMENTテーブルオプションをサポートしていないエンジンの最初の自動インクリメント値を設定するには、テーブルを作成したあとに目的の値より 1 小さい値を持つダミーの行を挿入してから、そのダミーの行を削除します。

    CREATE TABLEステートメント内のAUTO_INCREMENTテーブルオプションをサポートするエンジンの場合は、ALTER TABLEtbl_nameAUTO_INCREMENT =Nを使用してAUTO_INCREMENT値をリセットすることもできます。この値を、現在カラム内にある最大値より小さく設定することはできません。

  • AVG_ROW_LENGTH

    テーブルの平均の行の長さの近似値。これを設定する必要があるのは、可変サイズの行を持つ大きなテーブルの場合だけです。

    MyISAMテーブルを作成すると、MySQL はMAX_ROWSおよびAVG_ROW_LENGTHオプションの積を使用して、結果として得られるテーブルがどれくらいの大きさになるかを判定します。どちらのオプションも指定しない場合、MyISAMデータおよびインデックスファイルの最大サイズは、デフォルトで 256T バイトになります。(オペレーティングシステムでその大きさのファイルがサポートされていない場合、テーブルサイズはファイルサイズ制限によって制約されます。)インデックスをより小さく、かつ高速にするためにポインタサイズを小さく維持したいと考えており、実際に大きなファイルが必要でない場合は、myisam_data_pointer_sizeシステム変数を設定することによってデフォルトのポインタサイズを小さくすることができます。(セクション5.1.4「サーバーシステム変数」を参照してください。)すべてのテーブルをデフォルトの制限を超えて拡張できるようにしたいと考えており、テーブルが必要以上に少し遅く、かつ大きくなってもかまわない場合は、この変数を設定することによってデフォルトのポインタサイズを大きくすることができます。この値を 7 に設定すると、最大 65,536T バイトのテーブルサイズが許可されます。

  • [DEFAULT] CHARACTER SET

    テーブルのデフォルトの文字セットを指定します。CHARSETCHARACTER SETのシノニムです。文字セット名がDEFAULTである場合は、データベース文字セットが使用されます。

  • CHECKSUM

    MySQL ですべての行のライブチェックサム (つまり、テーブルが変更されると MySQL が自動的に更新するチェックサム) が保持されるようにする場合は、これを 1 に設定します。これにより、テーブルの更新が少し遅くなりますが、破損したテーブルを見つけることが容易になります。CHECKSUM TABLEステートメントは、このチェックサムをレポートします。(MyISAMのみ。)

  • [DEFAULT] COLLATE

    テーブルのデフォルトの照合順序を指定します。

  • COMMENT

    テーブルのコメントであり、長さは最大 2048 文字です。

  • CONNECTION

    FEDERATEDテーブルの接続文字列。

    注記

    古いバージョンの MySQL は、接続文字列にCOMMENTオプションを使用していました。

  • DATA DIRECTORY我NDEX DIRECTORY

    我nnoDBでは、DATA DIRECTORY='directory'オプションを使用すると、MySQL データディレクトリ以外の場所に新しい我nnoDBfile-per-table テーブルスペースを作成できます。MySQL は、指定されたディレクトリ内にデータベース名に対応するサブディレクトリを作成し、さらにその中に新しいテーブルの.ibdファイルを作成します。我nnoDBテーブルでDATA DIRECTORYオプションを使用するには、innodb_file_per_table構成オプションを有効にする必要があります。このディレクトリは,ディレクトリへの(相対パスではなく)フルパス名である必要があります。詳細は、セクション14.5.4「テーブルスペースの位置の指定」を参照してください。

    MyISAMテーブルを作成する場合は、DATA DIRECTORY='directory'句、我NDEX DIRECTORY='directory'句、またはその両方を使用できます。これらは、それぞれMyISAMテーブルのデータファイルとインデックスファイルを配置する場所を指定します。我nnoDBテーブルとは異なり、DATA DIRECTORYまたは我NDEX DIRECTORYオプションでMyISAMテーブルを作成する場合、MySQL はデータベース名に対応するサブディレクトリを作成しません。各ファイルは、指定されたディレクトリ内に作成されます。

    重要

    テーブルレベルのDATA DIRECTORYおよび我NDEX DIRECTORYオプションは、パーティション化されたテーブルでは無視されます。(Bug #32091)

    これらのオプションは、--skip-symbolic-linksオプションを使用していない場合にのみ機能します。また、オペレーティングシステムにも、機能するスレッドに対して安全なrealpath()呼び出しが存在する必要があります。詳細は、セクション8.11.3.1.2「Unix 上の MyISAM へのシンボリックリンクの使用」を参照してください。

    MyISAMテーブルがDATA DIRECTORYオプションなしで作成される場合、.MYDファイルがデータベースディレクトリ内に作成されます。デフォルトでは、MyISAMが既存の.MYDファイルを検出した場合、そのファイルを上書きします。我NDEX DIRECTORYオプションを指定せずに作成されたテーブルについて、.MYIファイルに同じことが当てはまります。この動作を抑制するには、--keep_files_on_createオプションを使用してサーバーを起動します。その場合、MyISAMは既存のファイルを上書きせず、代わりにエラーを返します。

    MyISAMテーブルがDATA DIRECTORYまたは我NDEX DIRECTORYオプションを使用して作成され、既存の.MYDまたは.MYIファイルが見つかった場合、MyISAM は常にエラーを返します。指定されたディレクトリ内のファイルは上書きされません。

    重要

    DATA DIRECTORYまたは我NDEX DIRECTORYでは、MySQL データディレクトリを含むパス名を使用できません。これには、パーティション化されたテーブルや個々のテーブルパーティションが含まれます。(Bug #32167 を参照してください。)

  • DELAY_KEY_WRITE

    テーブルのキー更新をテーブルが閉じられるまで遅らせる場合は、これを 1 に設定します。セクション5.1.4「サーバーシステム変数」にあるdelay_key_writeシステム変数の説明を参照してください。(MyISAMのみ。)

  • 我NSERT_METHOD

    MERGEテーブルにデータを挿入する場合は、我NSERT_METHODを使用して,行を挿入するテーブルを指定する必要があります。我NSERT_METHODは、MERGEテーブルにのみ役立つオプションです。最初または最後のテーブルに挿入するにはFIRSTまたはLASTの値を、挿入されないようにするにはNOの値を使用します。セクション15.7「MERGE ストレージエンジン」を参照してください。

  • KEY_BLOCK_SIZE

    圧縮された我nnoDBテーブルでは、オプションで、ページに使用するサイズをバイト単位で指定します。この値はヒントとして扱われます。我nnoDBでは、必要に応じて異なるサイズが使用される可能性があります。値 0 は、innodb_page_size値の半分であるデフォルトの圧縮済みページサイズを表します。KEY_BLOCK_SIZE値は、innodb_page_size値以下にしかできません。innodb_page_size値を超える値を指定した場合は、その値が無視され、警告が発行されます。また、KEY_BLOCK_SIZEinnodb_page_size値の半分に設定されます。innodb_strict_mode=ONの場合、無効なKEY_BLOCK_SIZE値を指定するとエラーが返されます。使用法の詳細は、セクション14.7「InnoDB 圧縮テーブル」を参照してください。

    個々のインデックス定義では、テーブルの値をオーバーライドする独自のKEY_BLOCK_SIZE値を指定できます。

    注記

    我nnoDBテーブルに対してKEY_BLOCK_SIZE句を使用している場合は、innodb_strict_modeを有効にすることをお勧めします。

  • MAX_ROWS

    テーブル内に格納することを予定している行の最大数。これは強い制限値ではなく、どちらかと言うと、テーブルが少なくともこの行数を格納できる必要があるという、ストレージエンジンへのヒントです。

    NDBストレージエンジンは、この値を最大値として扱います。非常に大きな (数百万行を含む) MySQL Cluster テーブルを作成する予定がある場合は、このオプションを使用してMAX_ROWS = 2 *rowsを設定することにより、テーブルの主キーのハッシュを格納するために使用されるハッシュテーブル内にNDBによって十分な数のインデックススロットが割り当てられることを保証するようにしてください。ここで、rowsはテーブルに挿入することが予測される行数です。

    MAX_ROWSの最大値は 4294967295 です。これを超える値は、この制限に切り捨てられます。

  • MIN_ROWS

    テーブル内に格納することを予定している行の最小数。MEMORYストレージエンジンは、このオプションをメモリー使用に関するヒントとして使用します。

  • PACK_KEYS

    PACK_KEYSは、MyISAMテーブルでのみ有効になります。インデックスを小さくする場合は、このオプションを 1 に設定します。通常は、これによって更新は遅く、読み取りは高速になります。このオプションを 0 に設定すると、キーのすべてのパッキングが無効になります。これをDEFAULTに設定すると、長いCHARVARCHARBINARY、またはVARBINARYカラムのみをパックするようストレージエンジンに指示します。

    PACK_KEYSを使用しない場合,デフォルトでは文字列をパックしますが、数値はパックしません。PACK_KEYS=1を使用した場合は、数値もパックされます。

    2 進数のキーをパックする場合、MySQL は次のプリフィクス圧縮を使用します。

    • 前のキーの何バイトが次のキーと同じであるかを示すために、すべてのキーに 1 バイトが余分に必要になります。

    • 行へのポインタは、圧縮率を向上させるために、キーの直後に高位バイトが先に来る順序で格納されます。

    つまり、2 つの連続した行に等しいキーが多数存在する場合は、次の同じキーはすべて、通常 (行へのポインタを含め) 2 バイトしか占有しません。これを、次のキーがstorage_size_for_key + pointer_size(ここで、ポインタサイズは通常 4) を占有する通常のケースと比較してください。逆に言うと、プリフィクス圧縮から大きな利点が得られるのは、同じ数値が多数存在する場合だけです。すべてのキーが完全に異なっている場合は、そのキーがNULL値を持つことができるキーでないかぎり、キーあたり 1 バイト多く使用されます。(この場合、パックされたキーの長さは、キーがNULLであるかどうかをマークするために使用されるのと同じバイトに格納されます。)

  • PASSWORD

    このオプションは使用されません。.frmファイルを暗号化し、ほかのどの MySQL サーバーからも使用できないようにする必要がある場合は、当社の販売部門に問い合わせてください。

  • ROW_FORMAT

    行が格納される物理フォーマットを定義します。これらの選択は、テーブルに使用されているストレージエンジンによって異なります。

    我nnoDBテーブルの場合:

    • デフォルトでは、行は圧縮形式 (ROW_FORMAT=COMPACT) で格納されます。

    • 古いバージョンの MySQL で使用されていた非圧縮形式は、ROW_FORMAT=REDUNDANTを指定することによって引き続き要求できます。

    • 我nnoDBテーブルの圧縮を有効にするには、ROW_FORMAT=COMPRESSEDを指定し、セクション14.7「InnoDB 圧縮テーブル」の手順に従います。

    • データ型 (特にBLOB型) の我nnoDBストレージの効率を向上させるには、ROW_FORMAT=DYNAMICを指定し、セクション14.9.3「DYNAMIC および COMPRESSED 行フォーマット」の手順に従います。COMPRESSEDおよびDYNAMIC行フォーマットはどちらも、構成設定innodb_file_per_table=1およびinnodb_file_format=barracudaを使用してテーブルを作成する必要があります。

    • デフォルト以外のROW_FORMAT句を指定する場合は、innodb_strict_mode構成オプションも有効にすることを考慮してください。

    • 我nnoDB行フォーマットの詳細は、セクション14.9「InnoDB の行ストレージと行フォーマット」を参照してください。

    MyISAMテーブルの場合は、このオプション値を、静的行フォーマットまたは可変長行フォーマットを示すFIXEDまたはDYNAMICに設定できます。myisampackは、この型をCOMPRESSEDに設定します。セクション15.2.3「MyISAM テーブルのストレージフォーマット」を参照してください。

    注記

    CREATE TABLEステートメントを実行するとき、テーブルに使用しているストレージエンジンでサポートされていない行フォーマットを指定した場合、テーブルはそのストレージエンジンのデフォルトの行フォーマットを使用して作成されます。SHOW TABLE STATUSに応答してこのカラムでレポートされる情報は、使用されている実際の行フォーマットです。作成中は元のCREATE TABLE定義が保持されているため、これはCreate_optionsカラム内の値とは異なる可能性があります。

  • STATS_AUTO_RECALC

    我nnoDBテーブルの永続的統計を自動的に再計算するかどうかを指定します。値DEFAULTを指定すると、テーブルの永続的統計設定はinnodb_stats_auto_recalc構成オプションによって決定されます。値1を指定すると、統計は、テーブル内のデータの 10% が変更されたときに再計算されます。値0は、このテーブルの自動再計算が行われないようにします。この設定の場合、テーブルへの大幅な変更を行なったあとに統計を再計算するには、ANALYZE TABLEステートメントを発行します。永続的統計機能の詳細は、セクション14.13.16.1「永続的オプティマイザ統計のパラメータの構成」を参照してください。

  • STATS_PERSISTENT

    我nnoDBテーブルの永続的統計を有効にするかどうかを指定します。値DEFAULTを指定すると、テーブルの永続的統計設定はinnodb_stats_persistent構成オプションによって決定されます。値1がテーブルの永続的統計を有効にするのに対して、値0はこの機能を無効にします。CREATE TABLEまたはALTER TABLEステートメントを使用して永続的統計を有効にしたあと、代表的なデータのテーブルへのロード後に統計を計算するには、ANALYZE TABLEステートメントを発行します。永続的統計機能の詳細は、セクション14.13.16.1「永続的オプティマイザ統計のパラメータの構成」を参照してください。

  • STATS_SAMPLE_PAGES

    インデックス付きカラムのカーディナリティーやその他の統計 (ANALYZE TABLEによって計算される統計など) を推定するときにサンプリングするインデックスページの数。詳細は、セクション14.13.16.1「永続的オプティマイザ統計のパラメータの構成」を参照してください。

  • UNION

    UNIONは、同一のMyISAMテーブルのコレクションを 1 つのものとしてアクセスする場合に使用されます。これは、MERGEテーブルでのみ機能します。セクション15.7「MERGE ストレージエンジン」を参照してください。

    MERGEテーブルにマップするテーブルに対するSELECTUPDATE、およびDELETE権限が必要です。

    注記

    以前は、使用されるすべてのテーブルがMERGEテーブル自体と同じデータベース内に存在する必要がありました。この制限は適用されなくなりました。

パーティション化

partition_optionsを使用すると、CREATE TABLEで作成されたテーブルのパーティション化を制御できます。

重要

このセクションの最初にあるpartition_optionsの構文に示されているすべてのオプションが、すべてのパーティショニングタイプに使用できるわけではありません。各タイプに固有の情報については、次の個々のタイプのリストを参照してください。また、MySQL でのパーティション化の動作や使用に関するより詳細な情報、および MySQL のパーティション化に関連したテーブル作成やその他のステートメントの追加の例については、第19章「パーティション化を参照してください。

partition_options句が使用される場合、この句はPARTITION BYで始まります。この句には、パーティションを決定するために使用される関数が含まれています。この関数は、1 からnumまでの範囲の整数値を返します。ここで、numはパーティションの数です。(テーブルに含めることのできるユーザー定義パーティションの最大数は 1024 です。この最大数には、このセクションのあとの方で説明されているサブパーティションの数が含まれています。)MySQL 5.6 で、この関数に使用可能な選択肢を次のリストに示します。

  • HASH(expr): 行の配置および検索のためのキーを作成するために 1 つ以上のカラムをハッシュします。exprは、1 つ以上のテーブルカラムを使用した式です。これは、1 つの整数値が得られる任意の有効な MySQL 式 (MySQL 関数を含む) にすることができます。たとえば、次はどちらも、PARTITION BY HASHを使用した有効なCREATE TABLEステートメントです。

    CREATE TABLE t1 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1); CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH ( YEAR(col3) );

    PARTITION BY HASHでは、VALUES LESS THANまたはVALUES INのどちらの句も使用できません。

    PARTITION BY HASHは、exprをパーティションの数で割った余り (つまり、法) を使用します。例および追加情報については、セクション19.2.4「HASH パーティショニング」を参照してください。

    LINEARキーワードには、いくぶん異なるアルゴリズムが必要になります。この場合、行が格納されるパーティションの数は、1 つ以上の論理的なAND演算の結果として計算されます。線形ハッシュの説明および例については、セクション19.2.4.1「LINEAR HASH パーティショニング」を参照してください。

  • KEY(column_list): これはHASHに似ていますが、均一なデータ分散を保証するために MySQL がハッシュ関数を提供する点が異なります。column_list引数は、単純に 1 つ以上のテーブルカラム (最大 16 個) のリストです。この例は、4 つのパーティションを持つ、キーによってパーティション化された単純なテーブルを示しています。

    CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;

    キーによってパーティション化されたテーブルの場合は、LINEARキーワードを使用して線形パーティション化を採用できます。これには、HASHによってパーティション化されたテーブルの場合と同じ効果があります。つまり、パーティション番号は法ではなく、&演算子を使用して見つけられます (詳細は、セクション19.2.4.1「LINEAR HASH パーティショニング」およびセクション19.2.5「KEY パーティショニング」を参照してください)。この例では、キーによる線形パーティション化を使用して 5 つのパーティション間でデータを分散させます。

    CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5;

    ALGORITHM={1|2}オプションは、MySQL 5.6.11 から[SUB]PARTITION BY [LINEAR] KEYでサポートされています。ALGORITHM=1を指定すると、サーバーは MySQL 5.1 と同じキーハッシュ関数を使用します。ALGORITHM=2は、サーバーが、MySQL 5.5 以降で実装され、KEYによってパーティション化された新しいテーブルに対してデフォルトで使用されるキーハッシュ関数を採用することを示します。(MySQL 5.5 以降で採用されたキーハッシュ関数によって作成されたパーティション化されたテーブルを MySQL 5.1 サーバーで使用することはできません。)このオプションを指定しない場合は、ALGORITHM=2を使用するのと同じ効果があります。このオプションは、主に[LINEAR] KEYによってパーティション化されたテーブルを MySQL 5.1 以降の MySQL バージョン間でアップグレードまたはダウングレードするときに使用するか、または MySQL 5.5 以降のサーバー上で、MySQL 5.1 サーバー上で使用できるKEYまたはLINEAR KEYによってパーティション化されたテーブルを作成することを目的にしています。詳細は、セクション13.1.7.1「ALTER TABLE パーティション操作」を参照してください。

    MySQL 5.6.11 以降のmysqldumpは、このオプションをバージョン管理されたコメント内に次のように書き込みます。

    创建表t1 (INT) / * !50100年的分区键*//*!50611 ALGORITHM = 1 *//*!50100 () PARTITIONS 3 */

    これにより、MySQL 5.6.10 以前のサーバーはこのオプションを無視するようになります。これらのバージョンでは、通常であれば構文エラーが発生します。KEYによってパーティション化またはサブパーティション化されたテーブルを使用している MySQL 5.5.31 またはそれ以降の MySQL 5.5 サーバー上で作成されたダンプを、バージョン 5.6.11 より前の MySQL 5.6 サーバーにロードする予定がある場合は、続行する前に、必ずセクション2.11.1.3「MySQL 5.5 から 5.6 へのアップグレード」を参照するようにしてください。(そこで見つかった情報は、MySQL 5.6.11 以降のサーバーから作成されたKEYによってパーティション化またはサブパーティション化されたテーブルを含むダンプを、MySQL 5.5.30 以前のサーバーにロードする場合にも適用されます。)

    また、MySQL 5.6.11 以降では、ALGORITHM=1mysqldumpと同じ方法で、バージョン管理されたコメントを使用してSHOW CREATE TABLEの出力に必要に応じて表示されます。ALGORITHM=2は、元のテーブルを作成するときにこのオプションが指定された場合でも、SHOW CREATE TABLEの出力から常に省略されます。

    PARTITION BY KEYでは、VALUES LESS THANまたはVALUES INのどちらの句も使用できません。

  • RANGE(expr): この場合、exprは、VALUES LESS THAN演算子のセットを使用して値の範囲を示します。範囲のパーティション化を使用する場合は、VALUES LESS THANを使用して、少なくとも 1 つのパーティションを定義する必要があります。範囲のパーティション化ではVALUES INを使用できません。

    注記

    RANGEによってパーティション化されたテーブルでは、VALUES LESS THANを整数リテラル値、または 1 つの整数値に評価される式のどちらかとともに使用する必要があります。MySQL 5.6 では、このセクションのあとの方で説明されている、PARTITION BY RANGE COLUMNSを使用して定義されたテーブルでこの制限を克服できます。

    次のスキームに従って、年の値を含むカラムに関してパーティション化するテーブルがあるとします。

    パーティション番号: 年の範囲:
    0 1990 以前
    1 1991 から 1994 まで
    2 1995 から 1998 まで
    3 1999 から 2002 まで
    4 2003 から 2005 まで
    5 2006 以降

    このようなパーティション化スキームを実装するテーブルは、次に示すCREATE TABLEステートメントによって実現できます。

    CREATE TABLE t1 ( year_col INT, some_data INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2002), PARTITION p4 VALUES LESS THAN (2006), PARTITION p5 VALUES LESS THAN MAXVALUE );

    PARTITION ... VALUES LESS THAN ...ステートメントは、連続的に機能します。VALUES LESS THAN MAXVALUEは、それ以外で指定されている最大値より大きい残りの値を指定するように機能します。

    VALUES LESS THAN句は (C、Java、PHP などの多くのプログラミング言語に見られるような)switch ... caseブロックのcase部分と同様の方法で連続的に機能します。つまり、この句は、連続した各VALUES LESS THANで指定されている上限が前の句の上限より大きく、かつMAXVALUEを参照している句がリスト内のすべての句の最後に来るような方法で配置されている必要があります。

  • RANGE COLUMNS(column_list):RANGEに対するこのバリアントは、複数のカラムに関する範囲条件を使用した (つまり、WHERE a = 1 AND b < 10WHERE a = 1 AND b = 10 AND c < 10などの条件を持つ) クエリーに対するパーティションプルーニングを容易にします。これにより、COLUMNS句内のカラムのリストと、各PARTITION ... VALUES LESS THAN (value_list)パーティション定義句内のカラム値のセットを使用して、複数のカラム内の値の範囲を指定できるようになります。(もっとも単純なケースでは、このセットは単一カラムで構成されます。)column_listおよびvalue_listで参照できるカラムの最大数は 16 です。

    COLUMNS句で使用されるcolumn_listには、カラムの名前のみを含めることができます。リスト内の各カラムは MySQL のデータ型のうち、整数型、文字列型、および時間または日付カラム型のいずれかである必要があります。BLOBTEXTSETENUMBIT、または空間データ型を使用したカラムは許可されていません。浮動小数点数型を使用するカラムも許可されていません。また、COLUMNS句では、関数や演算式も使用できません。

    パーティション定義で使用されるVALUES LESS THAN句は、COLUMNS()句に現れるカラムごとにリテラル値を指定する必要があります。つまり、各VALUES LESS THAN句で使用される値のリストには、COLUMNS句にリストされているカラムの数と同じ数の値が含まれている必要があります。VALUES LESS THAN句でCOLUMNS句に存在する数より多いか,または少ない値を使用しようとすると、このステートメントはエラー我nconsistency in usage of column lists for partitioning...で失敗します。VALUES LESS THANに現れるどの値にもNULLは使用できません。この例に示すように、最初のカラム以外の特定のカラムでMAXVALUEを複数回使用できます。

    CREATE TABLE rc ( a INT NOT NULL, b INT NOT NULL ) PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (10,5), PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (MAXVALUE,15), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) );

    VALUES LESS THAN値リストで使用されている各値が、対応するカラムの型に正確に一致している必要があります。変換は行われません。たとえば、整数型を使用するカラムに一致する値として文字列'1'を使用したり (代わりに、数値1を使用する必要があります)、文字列型を使用するカラムに一致する値として数値1を使用したりすることはできません (このような場合は、引用符で囲まれた文字列'1'を使用する必要があります)。

    詳細は、セクション19.2.1「RANGE パーティショニング」およびセクション19.4「パーティションプルーニング」を参照してください。

  • LIST(expr): これは、州または国コードなどの、制限された指定可能な値のセットを持つテーブルカラムに基づいてパーティションを割り当てる場合に役立ちます。このような場合は、特定の州または国に関連するすべての行を単一パーティションに割り当てたり、特定の州または国のセットのためにパーティションを予約したりできます。これはRANGEに似ていますが、各パーティションに許可される値を指定するためにVALUES INしか使用できない点が異なります。

    VALUES INは、一致させる値のリストとともに使用されます。たとえば、次のようなパーティション化スキームを作成できます。

    CREATE TABLE client_firms ( id INT, name VARCHAR(35) ) PARTITION BY LIST (id) ( PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) );

    リストのパーティション化を使用する場合は、VALUES INを使用して、少なくとも 1 つのパーティションを定義する必要があります。PARTITION BY LISTではVALUES LESS THANを使用できません。

    注記

    LISTによってパーティション化されたテーブルでは、VALUES INで使用される値リストを整数値のみで構成する必要があります。MySQL 5.6 では、このセクションのあとの方で説明されている、LIST COLUMNSによるパーティション化を使用してこの制限を克服できます。

  • LIST COLUMNS(column_list):LISTに対するこのバリアントは、複数のカラムに関する比較条件を使用した (つまり、WHERE a = 5 AND b = 5WHERE a = 1 AND b = 10 AND c = 5などの条件を持つ) クエリーに対するパーティションプルーニングを容易にします。これにより、COLUMNS句内のカラムのリストと、各PARTITION ... VALUES IN (value_list)パーティション定義句内のカラム値のセットを使用して、複数のカラム内の値を指定できるようになります。

    LIST COLUMNS(column_list)で使用されるカラムリストとVALUES IN(value_list)で使用される値リストに関連したデータ型を管理するルールは、VALUES IN句ではMAXVALUEが許可されず、NULLを使用できる点を除き、それぞれRANGE COLUMNS(column_list)で使用されるカラムリストとVALUES LESS THAN(value_list)で使用される値リストの場合のルールと同じです。

    PARTITION BY LIST COLUMNSVALUES INに使用される値のリストには、PARTITION BY LISTで使用された場合と比較して重要な違いが 1 つあります。PARTITION BY LIST COLUMNSで使用された場合、VALUES IN句内の各要素は、カラム値のセットである必要があります。各セット内の値の数はCOLUMNS句で使用されているカラム数と同じである必要があり、これらの値のデータ型はそれらのカラムのデータ型に一致している (しかも、同じ順序で現れる) 必要があります。もっとも単純なケースでは、このセットは単一カラムで構成されます。column_listおよびvalue_listを構成する各要素で使用できるカラムの最大数は 16 です。

    次のCREATE TABLEステートメントで定義されるテーブルは、LIST COLUMNSパーティション化を使用したテーブルの例を示しています。

    CREATE TABLE lc ( a INT NULL, b INT NULL ) PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) );
  • オプションで、PARTITIONSnum句を使用してパーティションの数を指定できます。ここで、numはパーティションの数です。この句ほかのいずれかのPARTITION句の両方が使用されている場合、numは、PARTITION句を使用して宣言されているすべてのパーティションの総数と同じである必要があります。

    注記

    RANGEまたはLISTによってパーティション化されたテーブルの作成でPARTITIONS句を使用するかどうかにかかわらず、テーブル定義には引き続き、少なくとも 1 つのPARTITION VALUES句を含める必要があります (下を参照してください)。

  • オプションで、パーティションを複数のサブパーティションに分割できます。これは、オプションのSUBPARTITION BY句を使用して示すことができます。サブパーティション化は、HASHまたはKEYによって実行できます。これらのどちらもLINEARにすることができます。これらは、同等のパーティショニングタイプについて前に説明したのと同じように機能します。(LISTまたはRANGEによってサブパーティション化することはできません。)

    サブパーティションの数は、SUBPARTITIONSキーワードと、そのあとの整数値を使用して示すことができます。

  • PARTITIONSまたはSUBPARTITIONS句で使用されている値の厳密なチェックが適用され、この値は次のルールに従っている必要があります。

    • この値は 0 以外の正の整数である必要があります。

    • 先頭の 0 は許可されません。

    • この値は整数リテラルである必要があり、式にすることはできません。たとえば、0.2E+012に評価されたとしても、PARTITIONS 0.2E+01は許可されません。(Bug #15890)

注記

PARTITION BY句で使用される式 (expr) は、作成されているテーブルにはないどのカラムも参照できません。このような参照は明確に禁止されており、そのステートメントがエラーで失敗する原因になります。(Bug #29444)

各パーティションは、partition_definition句を使用して個別に定義できます。この句を構成する個別の部分は次のとおりです。

  • PARTITIONpartition_name: これはパーティションの論理名を指定します。

  • VALUES句: 範囲のパーティション化では、各パーティションにVALUES LESS THAN句が含まれている必要があります。リストのパーティション化では、パーティションごとにVALUES IN句を指定する必要があります。これは、このパーティションにどの行を格納するかを決定するために使用されます。構文の例については、第19章「パーティション化にあるパーティショニングタイプの説明を参照してください。

  • オプションのCOMMENT句を使用すると、このパーティションを説明する文字列を指定できます。例:

    COMMENT = 'Data for the years previous to 1999'

    MySQL 5.6.6 から、パーティションのコメントの最大長は 1024 文字です。(以前は、この制限が明示的に定義されていませんでした。)

  • DATA DIRECTORY我NDEX DIRECTORYは、それぞれ、このパーティションのデータとインデックスが格納されるディレクトリを示すために使用できます。data_dirindex_dirはどちらも、絶対システムパス名である必要があります。例:

    CREATE TABLE th (id INT, name VARCHAR(30), adate DATE) PARTITION BY LIST(YEAR(adate)) ( PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx', PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx', PARTITION p2001 VALUES IN (1997, 2001, 2005) DATA DIRECTORY = '/var/appdata/97/data' INDEX DIRECTORY = '/var/appdata/97/idx', PARTITION p2002 VALUES IN (1998, 2002, 2006) DATA DIRECTORY = '/var/appdata/98/data' INDEX DIRECTORY = '/var/appdata/98/idx' );

    DATA DIRECTORY我NDEX DIRECTORYは、MyISAMテーブルに使用されるCREATE TABLEステートメントのtable_option句と同じように動作します。

    パーティションごとに 1 つのデータディレクトリと 1 つのインデックスディレクトリを指定できます。指定されないままになっている場合、データとインデックスは、デフォルトではそのテーブルのデータベースディレクトリ内に格納されます。

    Windows では、DATA DIRECTORYおよび我NDEX DIRECTORYオプションはMyISAMテーブルの個々のパーティションまたはサブパーティションに対してサポートされず、我NDEX DIRECTORYオプションは我nnoDBテーブルの個々のパーティションまたはサブパーティションに対してサポートされません。これらのオプションは、警告が生成される点を除き、Windows では無視されます。(Bug #30459)

    注記

    DATA DIRECTORYおよび我NDEX DIRECTORYオプションは、NO_DIR_IN_CREATEが有効になっている場合、パーティション化されたテーブルの作成では無視されます。(Bug #24633)

  • MAX_ROWSMIN_ROWSは、それぞれ、このパーティションに格納される行の最大数と最小数を指定するために使用できます。max_number_of_rowsmin_number_of_rowsの値は正の整数である必要があります。同じ名前を持つテーブルレベルのオプションと同様に、これらはサーバーへの提案としてのみ機能し、強い制限値ではありません。

  • オプションのTABLESPACE句を使用すると,このパーティションのテーブルスペースを指定できます。MySQL集群にのみ使用されます。

  • パーティション化ハンドラは、PARTITIONSUBPARTITIONの両方について[STORAGE] ENGINEオプションを受け入れます。現在,これを使用するには,すべてのパーティションまたはすべてのサブパーティションを同じストレージエンジンに設定するしか方法がなく、同じテーブル内のパーティションまたはサブパーティションに対して異なるストレージエンジンを設定しようとするとエラーERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in this version of MySQLが発生します。将来の MySQL リリースでは、このパーティション化に関する制限を解消する予定です。

  • オプションで、パーティション定義に 1 つ以上のsubpartition_definition句を含めることができます。これらの各句は、少なくともSUBPARTITIONnameで構成されます。ここで、nameはそのサブパーティションの識別子です。PARTITIONキーワードがSUBPARTITIONに置き換えられる点を除き、サブパーティション定義の構文はパーティション定義の構文と同じです。

    サブパーティション化はHASHまたはKEYによって実行する必要があり、RANGEまたはLISTパーティションに対してのみ実行できます。セクション19.2.6「サブパーティショニング」を参照してください。

パーティションに対しては変更、マージ、テーブルへの追加、およびテーブルからの削除が可能です。これらのタスクを実行するための MySQL ステートメントに関する基本情報については、セクション13.1.7「ALTER TABLE 構文」を参照してください。より詳細な説明および例については、セクション19.3「パーティション管理」を参照してください。

重要

元のCREATE TABLEステートメント (すべての指定およびテーブルオプションを含む) は、そのテーブルが作成されるときに MySQL によって格納されます。この情報が保持されるのは、ALTER TABLEステートメントを使用してストレージエンジン、照合順序、またはその他の設定を変更した場合に、指定された元のテーブルオプションが保持されるようにするためです。これにより、2 つのエンジンによってサポートされる行フォーマットが異なっていても、我nnoDBMyISAMのテーブルタイプ間での変更が可能になります。

元のステートメントのテキストは保持されますが、特定の値やオプション (ROW_FORMATなど) が暗黙のうちに再構成される可能性があるため、アクティブなテーブル定義 (DESCRIBEまたはSHOW TABLE STATUSによってアクセス可能) やテーブル作成文字列 (SHOW CREATE TABLEによってアクセス可能) は異なる値をレポートします。

テーブルのクローニングまたはコピー

CREATE TABLEステートメントの最後にSELECTステートメントを追加することによって、あるテーブルを別のテーブルから作成できます。

CREATE TABLEnew_tblSELECT * FROMorig_tbl;

詳細は、セクション13.1.17.1「CREATE TABLE ... SELECT 構文」を参照してください。

別のテーブルの定義 (元のテーブルで定義されているすべてのカラム属性やインデックスを含む) に基づいて空のテーブルを作成するには、LIKEを使用します。

CREATE TABLEnew_tblLIKEorig_tbl;

このコピーは、元のテーブルと同じバージョンのテーブルストレージフォーマットを使用して作成されます。元のテーブルに対するSELECT権限が必要です。

LIKEは、ビューに対してではなく、ベーステーブルに対してのみ機能します。

重要

MySQL 5.6.1 から、LOCK TABLESステートメントが有効になっている間はCREATE TABLEまたはCREATE TABLE ... LIKEを実行できません。

また MySQL 5.6.1 の時点では、CREATE TABLE ... LIKECREATE TABLEと同じチェックを行い、単に.frmファイルをコピーするだけではありません。つまり、現在の SQL モードが、元のテーブルが作成されたときの有効なモードとは異なっている場合、テーブル定義が新しいモードでは無効と見なされる可能性があり、ステートメントは失敗します。

CREATE TABLE ... LIKEは、元のテーブルや、すべての外部キー定義に対して指定されたどのDATA DIRECTORYまたは我NDEX DIRECTORYテーブルオプションも保持しません。

元のテーブルがTEMPORARYテーブルである場合、CREATE TABLE ... LIKETEMPORARYを保持しません。TEMPORARY宛先テーブルを作成するには、CREATE TEMPORARY TABLE ... LIKEを使用します。