第 11 章 データ型

目次

11.1 データ型の概要
11.1.1 数値型の概要
11.1.2 日付と時間型の概要
11.1.3 文字列型の概要
11.2 数値型
11.2.1 整数型 (真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT
11.2.2 固定小数点型 (真数値) - DECIMAL、NUMERIC
11.2.3 浮動小数点型 (概数値) - FLOAT、DOUBLE
11.2.4 ビット値型 - BIT
11.2.5 数値型の属性
11.2.6 範囲外およびオーバーフローの処理
11.3 日付と時間型
11.3.1 DATE、DATETIME、および TIMESTAMP 型
11.3.2 TIME 型
11.3.3 YEAR 型
11.3.4 YEAR(2) の制限と YEAR(4) への移行
11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能
11.3.6 時間値での小数秒
11.3.7 日付と時間型間での変換
11.3.8 日付での 2 桁の年
11.4 文字列型
11.4.1 CHAR および VARCHAR 型
11.4.2 BINARY および VARBINARY 型
11.4.3 BLOB 型と TEXT 型
11.4.4 ENUM 型
11.4.5 SET 型
11.5 空間データの拡張
11.5.1 空間データ型
11.5.2 OpenGIS 幾何モデル
11.5.3 空間データの使用
11.6 データ型デフォルト値
11.7 データ型のストレージ要件
11.8 カラムに適した型の選択
11.9 その他のデータベースエンジンのデータ型の使用

MySQL では、数値型、日付と時間型、文字列 (文字およびバイト) 型、空間型という複数のカテゴリにわたる多数の SQL データ型をサポートしています。この章では、これらのデータ型の概要、各カテゴリの型のプロパティーに関する詳細、およびデータ型ストレージ要件のサマリーについて説明します。最初の概要は意図的に簡単なものにしています。値を指定可能な許可される形式など、特定のデータ型に関する追加情報については、この章で後述する詳細な説明を参照してください。

データ型の説明では、次の規則を使用しています。

11.1 データ型の概要

11.1.1 数値型の概要

数値データ型のサマリーについて説明します。数値型のプロパティーおよびストレージ要件の追加情報については、セクション11.2「数値型」およびセクション11.7「データ型のストレージ要件」を参照してください。

M は整数型の最大表示幅を示します。最大表示幅は 255 です。セクション11.2「数値型」で説明しているように、表示幅はその型に含めることができる値の範囲とは関係ありません。浮動小数点型と固定小数点型の場合、M は格納可能な桁数の合計です。

数値カラムに対して ZEROFILL を指定すると、MySQL は自動的にそのカラムに UNSIGNED 属性を追加します。

UNSIGNED 属性を許可している数値データ型は、SIGNED も許可します。ただし、このデータ型はデフォルトで符号付きになっているため、SIGNED 属性を指定しても効果はありません。

SERIALBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE のエイリアスです。

整数カラム定義の中の SERIAL DEFAULT VALUENOT NULL AUTO_INCREMENT UNIQUE のエイリアスです。

警告

一方が UNSIGNED 型のときに 2 つの整数値の間で減算を行うと、NO_UNSIGNED_SUBTRACTION SQL モードが有効でないかぎり、結果の値は符号なしになります。セクション12.10「キャスト関数と演算子」を参照してください。

  • BIT[(M)]

    ビットフィールド型。M は、値あたりのビット数 (1 から 64) を表します。M を省略した場合のデフォルトは 1 です。

  • TINYINT[(M)] [UNSIGNED] [ZEROFILL]

    非常に小さい整数。符号付きの範囲は -128 から 127 です。符号なしの範囲は 0 から 255 です。

  • BOOLBOOLEAN

    これらの型は TINYINT(1) のシノニムです。ゼロの値は false と見なされます。ゼロ以外の値は true と見なされます。

    mysql> SELECT IF(0, 'true', 'false');+------------------------+
    | IF(0, 'true', 'false') |
    +------------------------+
    | false |
    +------------------------+
    mysql> SELECT IF(1, 'true', 'false');+------------------------+
    | IF(1, 'true', 'false') |
    +------------------------+
    | true |
    +------------------------+
    mysql> SELECT IF(2, 'true', 'false');+------------------------+
    | IF(2, 'true', 'false') |
    +------------------------+
    | true |
    +------------------------+

    ただし、ここに示されているように、TRUE 値と FALSE 値はそれぞれ、10 の単なるエイリアスです。

    mysql> SELECT IF(0 = FALSE, 'true', 'false');+--------------------------------+
    | IF(0 = FALSE, 'true', 'false') |
    +--------------------------------+
    | true |
    +--------------------------------+
    mysql> SELECT IF(1 = TRUE, 'true', 'false');+-------------------------------+
    | IF(1 = TRUE, 'true', 'false') |
    +-------------------------------+
    | true |
    +-------------------------------+
    mysql> SELECT IF(2 = TRUE, 'true', 'false');+-------------------------------+
    | IF(2 = TRUE, 'true', 'false') |
    +-------------------------------+
    | false |
    +-------------------------------+
    mysql> SELECT IF(2 = FALSE, 'true', 'false');+--------------------------------+
    | IF(2 = FALSE, 'true', 'false') |
    +--------------------------------+
    | false |
    +--------------------------------+

    最後の 2 つのステートメントは、21 とも 0 とも等しくないために示される結果を表示します。

  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

    小さい整数。符号付きの範囲は -32768 から 32767 です。符号なしの範囲は 0 から 65535 です。

  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

    中間サイズの整数。符号付きの範囲は -8388608 から 8388607 です。符号なしの範囲は 0 から 16777215 です。

  • INT[(M)] [UNSIGNED] [ZEROFILL]

    普通サイズの整数。符号付きの範囲は -2147483648 から 2147483647 です。符号なしの範囲は 0 から 4294967295 です。

  • INTEGER[(M)] [UNSIGNED] [ZEROFILL]

    この型は INT のシノニムです。

  • BIGINT[(M)] [UNSIGNED] [ZEROFILL]

    大きい整数。符号付きの範囲は -9223372036854775808 から 9223372036854775807 です。符号なしの範囲は 0 から 18446744073709551615 です。

    SERIALBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE のエイリアスです。

    BIGINT カラムについて注意の必要な点は、次のとおりです。

    • すべての演算は符号付きの BIGINT 値または DOUBLE 値を使用して行われるため、ビット関数を使用しないかぎり、9223372036854775807 (63 ビット) よりも大きい符号なしの整数を使用しないでください。そのようにした場合、BIGINT 値から DOUBLE 値への変換時に、丸め誤差のために結果の最後の数桁に誤差が生じる可能性があります。

      MySQL は、次の場合に、BIGINT を扱うことができます。

      • 符号なしの大きな値を BIGINT カラムに格納するために整数を使用するとき。

      • MIN(col_name) または MAX(col_name) 内。ここで col_nameBIGINT カラムを指します。

      • 演算子 (+-* など) を使用する場合。ここで両方のオペランドは整数です。

    • 文字列を使用して格納すると、いつでも正確な整数値を BIGINT カラムに格納できます。この場合、MySQL は、中間倍精度表現を含まない文字列から数値に変換します。

    • 両方のオペランドが整数値の場合、-+、および * の演算子は、BIGINT 演算を使用します。これは、2 つの大きい整数 (または整数を返す関数からの結果) を掛け合わした場合、その結果が 9223372036854775807 より大きいときには、予期しない結果になるということを意味します。

  • DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

    パックされた正確な固定小数点数。M は桁数の合計 (精度) で、D は小数点以下の桁数 (スケール) です。小数点と、負の数に対する-の記号は M にはカウントされません。D が 0 のときは、小数点や小数部はありません。DECIMAL の最大桁数 (M) は 65 です。サポートされる小数部の最大桁数 (D) は 30 です。D が省略された場合のデフォルトは 0 です。M が省略された場合のデフォルトは 10 です。

    UNSIGNED が指定されている場合、負の値は許可されません。

    DECIMAL カラムを使用したすべての基本的な計算 (+, -, *, /) は、65 桁の精度で行われます。

  • DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

    これらの型は DECIMAL のシノニムです。FIXED シノニムは、ほかのデータベースシステムとの互換性のために使用できます。

  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

    小さい (単精度) 浮動小数点数。許可される値は、-3.402823466E+38 から -1.175494351E-380、および 1.175494351E-38 から 3.402823466E+38 です。これらは、IEEE スタンダードに基づいた理論的な限度です。使用しているハードウェアまたはオペレーティングシステムによっては、実際の範囲は少し小さくなる場合があります。

    M は桁数の合計で、D は小数点以下の桁数です。MD を省略した場合、値はハードウェアで許可された限度まで格納されます。単精度小数点数はおおよそ小数第 7 位まで正確です。

    UNSIGNED が指定されている場合、負の値は許可されません。

    MySQL ではすべての計算が倍精度で行われているので、FLOAT を使用すると、予想外の問題が起きることがあります。セクションB.5.5.7「一致する行がない場合の問題の解決」を参照してください。

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

    普通サイズ (倍精度) の浮動小数点数。許可されている値は、-1.7976931348623157E+308 から -2.2250738585072014E-3080、および 2.2250738585072014E-308 から 1.7976931348623157E+308 です。これらは、IEEE スタンダードに基づいた理論的な限度です。使用しているハードウェアまたはオペレーティングシステムによっては、実際の範囲は少し小さくなる場合があります。

    M は桁数の合計で、D は小数点以下の桁数です。MD を省略した場合、値はハードウェアで許可された限度まで格納されます。倍精度小数点数はおおよそ小数第 15 位まで正確です。

    UNSIGNED が指定されている場合、負の値は許可されません。

  • DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

    これらの型は DOUBLE のシノニムです。例外: REAL_AS_FLOAT SQL モードが有効な場合は、DOUBLE ではなく REALFLOAT のシノニムになります。

  • FLOAT(p) [UNSIGNED] [ZEROFILL]

    浮動小数点数です。p は精度をビットで表現しますが、MySQL は、結果として得られるデータ型に対して FLOAT または DOUBLE のどちらを使用するかを決めるためだけにこの値を使用します。p が 0 から 24 のとき、そのデータ型は M 値も D 値もない FLOAT になります。p が 25 から 53 のとき、そのデータ型は M 値も D 値もない DOUBLE になります。結果となるカラムの範囲は、このセクションで前述した単精度 FLOAT または倍精度 DOUBLE データ型の場合と同じです。

    FLOAT(p) 構文は ODBC との互換性を確保するために用意されています。

11.1.2 日付と時間型の概要

時間データ型のサマリーについて説明します。時間型のプロパティーおよびストレージ要件の追加情報については、セクション11.3「日付と時間型」およびセクション11.7「データ型のストレージ要件」を参照してください。時間値を演算する関数の説明については、セクション12.7「日付および時間関数」を参照してください。

DATE および DATETIME 範囲の説明では、サポートされているとは、以前の値は機能するが、保証はないことを意味します。

MySQL 5.6.4 以降では、マイクロ秒 (6 桁) までの精度を持つ TIMEDATETIME、および TIMESTAMP 値の小数秒に対応できるようになりました。小数秒部を含むカラムを定義するには、type_name(fsp) の構文を使用します。ここで、type_nameTIMEDATETIME、または TIMESTAMP であり、fsp は小数秒の精度です。例:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

fsp 値を指定する場合、0 から 6 の範囲にする必要があります。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。(これは、以前の MySQL バージョンと互換性を保つため、標準 SQL のデフォルトである 6 とは異なっています。)

MySQL 5.6.5 には、時間型の拡張された自動初期化および更新機能が導入されました。テーブルごとに最大 1 つのカラムではなく、テーブル内のすべての TIMESTAMP カラムにこれらのプロパティーを割り当てられます。さらに、これらのプロパティーは、DATETIME カラムで使用できるようになりました。

YEAR(2) データ型には、使用する前に考慮する必要のある特定の問題があります。MySQL 5.6.6 以降、YEAR(2) は非推奨です。既存のテーブル内の YEAR(2) カラムは以前のとおりに扱われますが、新規または変更したテーブルでは YEAR(2)YEAR(4) に変換されます。詳細は、セクション11.3.4「YEAR(2) の制限と YEAR(4) への移行」を参照してください。

  • DATE

    日付です。サポートしている範囲は '1000-01-01' から '9999-12-31' です。MySQL は 'YYYY-MM-DD' の形式で DATE 値を表示しますが、文字列または数値のどちらかを使用した DATE カラムへの値の割り当てを許可しています。

  • DATETIME[(fsp)]

    日付と時間の組み合わせです。サポートしている範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' です。MySQL は、'YYYY-MM-DD HH:MM:SS[.fraction]' の形式で DATETIME 値を表示しますが、文字列または数値のどちらかを使用した DATETIME カラムへの値の割り当てを許可しています。

    MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションの fsp 値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。

    MySQL 5.6.5 以降、DATETIME カラムに対する自動初期化および現在の日時への自動更新は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」で説明しているように、DEFAULT および ON UPDATE カラム定義句を使用して指定できます。

  • TIMESTAMP[(fsp)]

    タイムスタンプです。範囲は '1970-01-01 00:00:01.000000' UTC から '2038-01-19 03:14:07.999999' UTC です。TIMESTAMP 値は、エポック ('1970-01-01 00:00:00' UTC) からの秒数として格納されます。TIMESTAMP は、'1970-01-01 00:00:00' という値を表すことはできません。これは、エポックからの秒数が 0 であることと同等で、0 という値は '0000-00-00 00:00:00'、つまりゼロTIMESTAMP 値を表すために予約されているからです。

    MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションの fsp 値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。

    サーバーで TIMESTAMP 定義をどのように扱うかは、explicit_defaults_for_timestamp システム変数の値によって異なります (セクション5.1.4「サーバーシステム変数」を参照してください)。デフォルトでは、explicit_defaults_for_timestamp は無効であり、サーバーは次のように TIMESTAMP を扱います。

    特に指定されていないかぎり、テーブル内の最初の TIMESTAMP カラムは、明示的に値が割り当てられていなければもっとも新しい変更の日時に自動的に設定されるように定義されています。これにより、TIMESTAMP は、INSERT または UPDATE 操作のタイムスタンプの記録に役立ちます。NULL 値を許可するように NULL 属性で定義されていないかぎり、NULL 値を割り当てることによって、すべての TIMESTAMP カラムを現在の日付と時間に設定することもできます。

    自動初期化および現在の日付と時間への自動更新は、DEFAULT CURRENT_TIMESTAMP および ON UPDATE CURRENT_TIMESTAMP カラム定義句を使用して指定できます。デフォルトでは、前述のように最初の TIMESTAMP カラムにこれらのプロパティーが含まれます。MySQL 5.6.5 以降では、テーブル内のどの TIMESTAMP カラムでもこれらのプロパティーを割り当てるように定義できます。5.6.5 より前では、これらを割り当てられる TIMESTAMP カラムはテーブルごとに最大 1 つにかぎられますが、最初のカラムでは抑制し、代わりに別の TIMESTAMP カラムに割り当てることが可能です。セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」を参照してください。

    explicit_defaults_for_timestamp が有効な場合、すべての TIMESTAMP カラムへの DEFAULT CURRENT_TIMESTAMP または ON UPDATE CURRENT_TIMESTAMP 属性の自動的な割り当ては行われません。これらはカラム定義に明示的に含める必要があります。また、NOT NULL として明示的に宣言されていないすべての TIMESTAMP は、NULL 値を許可します。

    explicit_defaults_for_timestamp は、MySQL 5.6.6 以降で使用できます。5.6.6 より前では、サーバーは、explicit_defaults_for_timestamp が無効の場合について説明したように TIMESTAMP を扱います。これらの動作は、デフォルトのままになっていますが、標準外であり、5.6.6 以降では非推奨です。explicit_defaults_for_timestamp を有効化したインストールのアップグレードに関する説明については、セクション2.11.1.3「MySQL 5.5 から 5.6 へのアップグレード」を参照してください。

  • TIME[(fsp)]

    時間です。範囲は、'-838:59:59.000000' から '838:59:59.000000' です。MySQL は、'HH:MM:SS[.fraction]' 形式で TIME 値を表示しますが、文字列または数値のどちらかを使用した TIME カラムの値への割り当てを許可します。

    MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションの fsp 値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。

  • YEAR[(2|4)]

    2 桁または 4 桁の形式の年です。デフォルトは 4 桁の形式です。YEAR(2)YEAR(4) は表示形式が違いますが、値の範囲は同じです。4 桁の形式では、値は 1901 から 21550000 として表示されます。2 桁の形式では、値は 70 から 69 として表示され、1970 から 2069 の年を表します。MySQL では、YEAR 値は YYYY または YY の形式で表示されますが、文字列または数値を使用して YEAR カラムに値を割り当てられます。

    注記

    YEAR(2) データ型には、使用する前に考慮する必要のある特定の問題があります。MySQL 5.6.6 以降、YEAR(2) は非推奨です。既存のテーブル内の YEAR(2) カラムは以前のとおりに扱われますが、新規または変更したテーブルでは YEAR(2)YEAR(4) に変換されます。詳細は、セクション11.3.4「YEAR(2) の制限と YEAR(4) への移行」を参照してください。

    入力値の YEAR の表示形式および解釈に関する追加情報については、セクション11.3.3「YEAR 型」を参照してください。

SUM() および AVG() 集計関数は時間値を扱いません。(これらは値を数字に変換するので、最初の数字以外の文字のあとのすべての情報が失われます。)この問題を回避するには、数値単位に変換し、集計操作を実行してから、時間値に戻します。例:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
注記

MySQL Server は、MAXDB SQL モードを有効にして実行できます。この場合、TIMESTAMPDATETIME と同じです。テーブルの作成時にこのモードが有効になっている場合、TIMESTAMP カラムは DATETIME カラムとして作成されます。その結果、このようなカラムでは DATETIME 表示形式が使用され、値の範囲は同じになり、自動初期化や現在の日付と時間への自動更新は行われなくなります。セクション5.1.7「サーバー SQL モード」を参照してください。

11.1.3 文字列型の概要

文字列データ型のサマリーについて説明します。文字列型のプロパティーおよびストレージ要件の追加情報については、セクション11.4「文字列型」およびセクション11.7「データ型のストレージ要件」を参照してください。

MySQL は、文字列カラムを CREATE TABLE または ALTER TABLE ステートメントで与えられている型とは異なる型に変更することがあります。セクション13.1.17.3「暗黙のカラム指定の変更」を参照してください。

MySQL は、文字カラム定義の長さ指定を文字単位で解釈します。これは、CHARVARCHAR、および TEXT 型に適応されます。

多くの文字列データ型のカラム定義には、カラムの文字セットまたは照合順序を指定する属性を含めることができます。これらの属性は CHARVARCHARTEXT 型、ENUM、および SET データ型に適用されます。

  • CHARACTER SET 属性は文字セットを指定し、COLLATE 属性は文字セットの照合順序を指定します。例:

    CREATE TABLE t
    ( c1 VARCHAR(20) CHARACTER SET utf8, c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
    );

    このテーブル定義は、utf8 の文字セットとその文字セットのデフォルト照合順序を持つ c1 という名前のカラムと、latin1 の文字セットと大文字と小文字を区別する照合順序を持つ c2 という名前のカラムを作成します。

    CHARACTER SET 属性または COLLATE 属性、あるいはその両方がない場合に、文字セットや照合順序を割り当てるためのルールは、セクション10.1.3.4「カラム文字セットおよび照合順序」で説明しています。

    CHARSETCHARACTER SET のシノニムです。

  • 文字データ型に CHARACTER SET binary 属性を指定すると、カラムは対応するバイナリデータ型として作成されます。つまり、CHARBINARY になり、VARCHARVARBINARY になり、TEXTBLOB になります。ENUM および SET データ型では、これは行われず、宣言されたとおりに作成されます。この定義を使用して、テーブルを指定したとします。

    CREATE TABLE t
    ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary
    );

    結果のテーブルには、この定義が含まれています。

    CREATE TABLE t
    ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary
    );
  • ASCII 属性は CHARACTER SET latin1 の短縮形です。

  • UNICODE 属性は CHARACTER SET ucs2 の短縮形です。

  • BINARY 属性は、カラム文字セットのバイナリ照合順序を指定する短縮形です。この場合、ソートと比較は数字の値に基づきます。

文字カラムのソートと比較は、カラムに割り当てられた文字セットに基づきます。CHARVARCHARTEXTENUM、および SET データ型では、辞書順ではなくベースとなる文字コード値をソートおよび比較で使用できるように、バイナリ照合順序または BINARY 属性を持つカラムを宣言できます。

セクション10.1「文字セットのサポート」では、MySQL の文字セットの使用に関する追加情報が記述されています。

  • [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

    格納時に必ず、指定された長さになるように右側がスペースで埋められる固定長文字列です。M はカラムの長さを文字数で表します。M の範囲は 0 から 255 です。M を省略すると、長さは 1 になります。

    注記

    PAD_CHAR_TO_FULL_LENGTH SQL モードが有効になっていないかぎり、CHAR 値が取り出されるときに末尾のスペースは削除されます。

    CHARCHARACTER の短縮形です。NATIONAL CHAR (またはそれと同等の短縮形である NCHAR) は、CHAR カラムが事前に定義された文字セットを使用する必要があることを定義する標準 SQL の方法です。MySQL 4.1 以降では、この事前に定義された文字セットとして utf8 を使用します。セクション10.1.3.6「各国文字セット」を参照してください。

    CHAR BYTE データ型は BINARY データ型のエイリアスです。これは互換性機能です。

    MySQL では、CHAR(0) の型のカラムを作成できます。これは主に、カラムの存在に依存するが、実際にはその値を使用しない古いアプリケーションに準拠する必要があるときに役立ちます。CHAR(0) は、2 つの値しか取れないカラムが必要な場合にも非常に便利です。CHAR(0) NULL として定義されたカラムは 1 ビットだけを占め、NULL'' (空の文字列) 値だけを取ることができます。

  • [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

    可変長文字列です。M はカラムの最大長を文字数で表します。M の範囲は 0 から 65,535 です。VARCHAR の有効な最大長は、最大行サイズ (65,535 バイト、すべてのカラムで共有されます) と使用される文字セットによって決まります。たとえば、utf8 の文字は 1 文字につき最大 3 バイトを必要とする場合があるため、utf8 の文字セットを使用する VARCHAR カラムは、最大 21,844 文字になるように宣言できます。セクションD.10.4「テーブルカラム数と行サイズの制限」を参照してください。

    MySQL は、VARCHAR 値を 1 バイトまたは 2 バイト長のプリフィクスが付いたデータとして格納します。長さプリフィクスは、値に含まれるバイト数を示します。VARCHAR カラムは、格納できる値が 255 バイト以下の場合は 1 バイト長のプリフィクスを使用し、255 バイトより大きい場合は 2 バイト長のプリフィクスを使用します。

    注記

    MySQL 5.6 は、標準 SQL 仕様に従い、VARCHAR 値から末尾のスペースを削除しません

    VARCHARCHARACTER VARYING の短縮形です。NATIONAL VARCHAR は、VARCHAR カラムが事前定義された文字セットを使用する必要があることを定義するための標準 SQL の方法です。MySQL 4.1 以降では、この事前に定義された文字セットとして utf8 を使用します。セクション10.1.3.6「各国文字セット」を参照してください。NVARCHARNATIONAL VARCHAR の短縮形です。

  • BINARY(M)

    BINARY 型は CHAR 型と似ていますが、非バイナリ文字列ではなく、バイナリバイト文字列を格納します。M はカラムの長さをバイト数で表します。

  • VARBINARY(M)

    VARBINARY 型は VARCHAR 型と似ていますが、非バイナリ文字列ではなく、バイナリバイト文字列を格納します。M はカラムの最大の長さをバイト数で表します。

  • TINYBLOB

    最大長が 255 (28 − 1) バイトの BLOB カラム。各 TINYBLOB 値は、値のバイト数を示す 1 バイト長のプリフィクスを使用して格納されます。

  • TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    最大長が 255 (28 − 1) 文字の TEXT カラム。値にマルチバイト文字が含まれる場合、有効な最大長は少なくなります。各 TINYTEXT 値は、値のバイト数を示す 1 バイト長のプリフィクスを使用して格納されます。

  • BLOB[(M)]

    最大長が 65,535 (216 − 1) バイトの BLOB カラム。各 BLOB 値は、値のバイト数を示す 2 バイト長のプリフィクスを使用して格納されます。

    この型には、オプションの長さ M を指定できます。これが行われた場合、MySQL は M バイトの長さの値を保持するのに十分な最小の BLOB 型としてカラムを作成します。

  • TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

    最大長が 65,535 (216 − 1) 文字の TEXT カラム。値にマルチバイト文字が含まれる場合、有効な最大長は少なくなります。各 TEXT 値は、値のバイト数を示す 2 バイト長のプリフィクスを使用して格納されます。

    この型には、オプションの長さ M を指定できます。これが行われた場合、MySQL は M 文字の長さの値を保持するのに十分な最小 TEXT 型としてカラムを作成します。

  • MEDIUMBLOB

    最大長が 16,777,215 (224 − 1) バイトの BLOB カラム。各 MEDIUMBLOB 値は、値のバイト数を示す 3 バイト長のプリフィクスを使用して格納されます。

  • MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    最大長が 16,777,215 (224 − 1) 文字の TEXT カラム。値にマルチバイト文字が含まれる場合、有効な最大長は少なくなります。各 MEDIUMTEXT 値は、値のバイト数を示す 3 バイト長のプリフィクスを使用して格納されます。

  • LONGBLOB

    最大長が 4,294,967,295 または 4G バイト (232 − 1) バイトの BLOB カラム。LONGBLOB カラムの有効な最大長は、クライアント/サーバープロトコルと使用可能なメモリー内の構成済み最大パケットサイズにより決まります。各 LONGBLOB 値は、値のバイト数を示す 4 バイト長のプリフィクスを使用して格納されます。

  • LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    最大長が 4,294,967,295 または 4G バイト (232 − 1) 文字の TEXT カラム。値にマルチバイト文字が含まれる場合、有効な最大長は少なくなります。LONGTEXT カラムの有効な最大長もまた、クライアント/サーバープロトコルと使用可能メモリー内の構成済みの最大パケットサイズにより決まります。各 LONGTEXT 値は、値のバイト数を示す 4 バイト長のプリフィクスを使用して格納されます。

  • ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

    列挙です。'value1''value2'... の値、NULL、または特殊な '' エラー値のリストから選択された値を 1 つだけを持つことができる文字列オブジェクトです。ENUM 値は、内部では整数として表されます。

    ENUM カラムには、最大 65,535 個の個別の要素を含めることができます。(実用的な限度は 3000 個までです。)テーブルには、グループと見なされる ENUM および SET カラムの中の一意の要素リスト定義を、255 個以下を含めることができます。これらの制限の詳細は、セクションD.10.5「.frm ファイル構造により課せられる制限」を参照してください。

  • SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

    セットです。ゼロ個以上の値を持つことができる文字列オブジェクトであり、そのそれぞれの値は、'value1''value2'... 値のリストから選択する必要があります。SET 値は整数として内部で表されます。

    SET カラムには最大 64 個の個別のメンバーを含めることができます。テーブルには、グループと見なされる ENUM および SET カラムの中の一意の要素リスト定義を、255 個以下を含めることができます。この制限の詳細は、セクションD.10.5「.frm ファイル構造により課せられる制限」を参照してください。

11.2 数値型

MySQL はすべての標準 SQL 数値データ型をサポートします。これらの型は、概数値データ型 (FLOATREALDOUBLE PRECISION) だけでなく、真数値データ型 (INTEGERSMALLINTDECIMALNUMERIC) を含みます。キーワード INTINTEGER のシノニムで、キーワード DEC および FIXEDDECIMAL のシノニムです。MySQL では、DOUBLEDOUBLE PRECISION (非標準の拡張) のシノニムと見なされます。また、REAL_AS_FLOAT SQL モードが有効でないかぎり、REALDOUBLE PRECISION (非標準のバリエーション) のシノニムと見なされます。

BIT データ型は、ビットフィールド値を格納し、MyISAMMEMORYInnoDB、および NDB テーブルでサポートされています。

範囲外の値のカラムへの割り当てと、式の評価中のオーバーフローに対する MySQL での処理の詳細は、セクション11.2.6「範囲外およびオーバーフローの処理」を参照してください。

数値型のストレージの要件の詳細は、セクション11.7「データ型のストレージ要件」を参照してください。

数値オペランドで計算の結果に使用されるデータ型は、オペランドの型と実行される演算によって異なります。詳細は、セクション12.6.1「算術演算子」を参照してください。

11.2.1 整数型 (真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT

MySQL では、INTEGER (または INT) および SMALLINT の SQL 標準整数型をサポートします。標準に対する拡張として、MySQL では、TINYINTMEDIUMINT、および BIGINT の整数型もサポートします。次の表に、整数型ごとの必要なストレージと範囲を示します。

ストレージ最小値最大値
 (バイト)(符号付き/符号なし)(符号付き/符号なし)
TINYINT1-128127
  0255
SMALLINT2-3276832767
  065535
MEDIUMINT3-83886088388607
  016777215
INT4-21474836482147483647
  04294967295
BIGINT8-92233720368547758089223372036854775807
  018446744073709551615

11.2.2 固定小数点型 (真数値) - DECIMAL、NUMERIC

DECIMAL および NUMERIC 型は真数値データ値を格納します。これらの型は、金銭データを扱う場合など、正確な精度を保持することが重要な場合に使用されます。MySQL では、NUMERICDECIMAL として実装されるので、DECIMAL に関する次の注意事項が NUMERIC にも同様に適用されます。

MySQL 5.6 は DECIMAL 値をバイナリ形式で格納します。セクション12.20「高精度計算」を参照してください。

DECIMAL カラム宣言で、精度およびスケールはたとえば次のように指定できます (通常は指定されています)。

salary DECIMAL(5,2)

この例では、5 が精度で、2 がスケールです。精度は、その値に格納された有効な桁数を表し、スケールは小数点以下に格納できる桁数を表しています。

標準 SQL では、DECIMAL(5,2) には小数部が 2 桁の合計 5 桁の値を格納できる必要があるので、salary カラムに格納できる値は、-999.99 から 999.99 の範囲になります。

標準 SQL では、構文 DECIMAL(M) は、DECIMAL(M,0) と同等です。同様に、構文 DECIMALDECIMAL(M,0) と同等です。M の値を決定するために、実装は許可されています。MySQL は、DECIMAL 構文のこれらのバリアント形式をどちらもサポートします。M のデフォルト値は 10 です。

スケールが 0 の場合、DECIMAL 値には小数点も小数部も含まれません。

DECIMAL の最大桁数は 65 ですが、指定した DECIMAL カラムの実際の範囲は、その指定したカラムの精度またはスケールによって制約される場合があります。指定のスケールで許可されている数より多くの桁が小数点以下にある値が、このようなカラムに割り当てられた場合、値はそのスケールに変換されます。(正確な動作はオペレーティングシステム固有ですが、一般的には効果は許可されている桁数に切り捨てられます。)

11.2.3 浮動小数点型 (概数値) - FLOAT、DOUBLE

FLOAT および DOUBLE 型は概数値データ値を表します。MySQL は、単精度値には 4 バイトを、倍精度値には 8 バイトを使用します。

FLOAT については、SQL 標準では、オプションで、キーワード FLOAT に続く括弧内のビットで (指数の範囲ではなく) 精度を指定できます。MySQL はまた、このオプションの精度指定もサポートしますが、その精度値はストレージサイズを決定するためだけに使用されます。0 から 23 の精度は、4 バイト単精度の FLOAT カラムになります。24 から 53 の精度は、8 バイト倍精度の DOUBLE カラムになります。

MySQL は、FLOAT(M,D) または REAL(M,D) または DOUBLE PRECISION(M,D) の非標準の構文を許可します。ここで、(MD) は、値は合計で M 桁まで格納でき、そのうちの D 桁は小数点以下です。たとえば、FLOAT(7,4) として定義されたカラムは、表示されたときには -999.9999 のようになります。MySQL は、値を格納するときに丸めを行うので、FLOAT(7,4) カラムに 999.00009 を挿入すると、近似の結果は 999.0001 になります。

浮動小数点値は概数値であり、真数値としては格納されないので、比較で値を真数値として扱おうとすると、問題が発生することがあります。これらはまた、プラットフォームまたは実装の依存関係にも従います。詳細は、セクションB.5.5.8「浮動小数点値に関する問題」を参照してください。

移植性を最大にするために、概数値データ値のストレージを必要とするコードでは、精度または桁数が指定されていない FLOAT または DOUBLE PRECISION を使用する必要があります。

11.2.4 ビット値型 - BIT

BIT データ型は、ビットフィールド値を格納するのに使用されます。BIT(M) の型は、M ビット値のストレージを有効にします。M の範囲は 1 から 64 までが可能です。

ビット値を指定するには、b'value' 表記を使用できます。value は、0 と 1 で書かれたバイナリ値です。たとえば、b'111'b'10000000' はそれぞれ 7 と 128 を表しています。セクション9.1.6「ビットフィールドリテラル」を参照してください。

M ビット長よりも短い BIT(M) カラムに値を割り当てた場合、その値の左側はゼロで埋められます。たとえば、b'101' という値を BIT(6) カラムに割り当てると、実際には b'000101' を割り当てた場合と同じことになります。

11.2.5 数値型の属性

MySQL では、整数データ型の基本キーワードに続く括弧内で、その型の表示幅をオプションで指定する拡張をサポートしています。たとえば、INT(4) は、4 桁の表示幅の INT を指定しています。このオプションの表示幅は、左側をスペースでパディングすることによって、カラムに対して指定された幅よりも狭く整数値を表示するために、アプリケーションで使用される場合があります。(つまり、この幅は結果セットで返されるメタデータの中にあります。これを使用するかどうかは、アプリケーションしだいです。)

表示幅は、カラムに格納できない値の範囲を制約しません。カラムの表示幅より広い値が正しく表示されなくなることもありません。たとえば、SMALLINT(3) として指定されたカラムには、-32768 から 32767 の通常の SMALLINT 範囲があり、3 桁が許可されたこの範囲外の値は、4 桁以上を使用してすべて表示されます。

オプション (非標準) の属性 ZEROFILL と一緒に使用すると、デフォルトのスペースのパディングはゼロに置き換えられます。たとえば、INT(4) ZEROFILL として宣言されたカラムの場合、5 の値は 0005 として取得されます。

注記

ZEROFILL 属性は、カラムが式や UNION クエリーに含まれているときは無視されます。

ZEROFILL 属性を持つ整数カラムに表示幅より大きな値を格納した場合、MySQL が一部の複雑な結合に対して一時テーブルを生成するときに問題が発生することがあります。これらの場合、MySQL は、カラムの表示幅内でデータ値が適合すると想定します。

すべての整数型には、オプション (非標準) 属性 UNSIGNED を指定できます。符号なしの型は、カラムで負ではない数値しか許可しないとき、またはカラムの上限の数値範囲を大きくする必要があるときに使用できます。たとえば、INT カラムが UNSIGNED である場合、カラム範囲のサイズは同じですが、その終点は -21474836482147483647 から、04294967295 に変化します。

浮動小数点と固定小数点も UNSIGNED になり得ます。整数型と同じように、この属性は負の値がカラムに格納されるのを防ぎます。整数型とは異なり、カラム値の上限範囲は変わりません。

数値カラムに対して ZEROFILL を指定すると、MySQL は自動的にそのカラムに UNSIGNED 属性を追加します。

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

MySQL 5.6.9 以降では、AUTO_INCREMENT カラムの負の値はサポートされません。

11.2.6 範囲外およびオーバーフローの処理

MySQL が、カラムデータ型の許可できる範囲外にある数値カラムに値を格納すると、結果は、その時点で有効な SQL モードによって異なります。

  • 厳密な SQL モードが有効な場合、SQL 標準に従って、MySQL は範囲外の値を拒否してエラーを表示し、挿入は失敗します。

  • 制限の強いモードが有効になっていない場合、MySQL は、範囲の適切な終点に値を切り落とし、その結果の値を代わりに格納します。

    範囲外の値が整数カラムに割り当てられると、MySQL は、カラムデータ型の範囲の対応する終点を表す値を格納します。TINYINT または TINYINT UNSIGNED カラムに 256 を格納すると、MySQL はそれぞれに 127 または 255 を格納します。

    浮動小数点または固定小数点カラムに、指定された (またはデフォルトの) 精度とスケールによって暗示された範囲を超えた値が割り当てられると、MySQL はその範囲の対応する終点を表す値を格納します。

MySQL が厳密モードで動作していないときの切り落としのために起きるカラム割り当て変換は、ALTER TABLELOAD DATA INFILEUPDATE、および複数行の INSERT ステートメントに対する警告としてレポートされます。厳密モードでは、これらのステートメントは失敗し、テーブルがトランザクションテーブルかどうかやほかの要因に応じて、一部またはすべての値が挿入または変更されません。詳細は、セクション5.1.7「サーバー SQL モード」を参照してください。

MySQL 5.6 では、数値式評価中のオーバーフローはエラーになります。たとえば、符号付きの BIGINT の最大値は 9223372036854775807 なので、次の式ではエラーが発生します。

mysql> SELECT 9223372036854775807 + 1;ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

この場合に演算を成功させるには、値を符号なしに変換します。

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
| 9223372036854775808 |
+-------------------------------------------+

オーバーフローが起きるかどうかはオペランドの範囲に応じて異なります。したがって、前述の式を処理するもう 1 つの方法として、DECIMAL 値に整数より大きな範囲があるので正確な値の演算を使用します。

mysql> SELECT 9223372036854775807.0 + 1;+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
| 9223372036854775808.0 |
+---------------------------+

一方が UNSIGNED 型のときに 2 つの整数値の間で減算を行うと、デフォルトでは符号なしの結果が生成されます。MySQL 5.5.5 より前では、それ以外では結果が負になっていた場合、最大の整数値になります。

mysql> SET sql_mode = '';mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+

MySQL 5.5.5 以降では、それ以外では結果が負になっていた場合、エラーになります。

mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CAST(0 AS UNSIGNED) - 1;ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

NO_UNSIGNED_SUBTRACTION SQL モードが有効な場合は、結果は負になります。

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+

このような演算の結果を使用して UNSIGNED 整数カラムが更新されると、結果はそのカラム型の最大値に切り落とされます。NO_UNSIGNED_SUBTRACTION が有効になっている場合は、0 に切り落とされます。厳密な SQL モードが有効になっている場合は、エラーが発生し、カラムは変わりません。

11.3 日付と時間型

時間値を表すための日付と時間型は、DATETIME, DATETIMETIMESTAMP、および YEAR です。それぞれの時間型には、一定範囲の有効な値のほかに、MySQL では表すことのできない無効な値の指定時に使用できるゼロ値があります。TIMESTAMP 型には、後述するように、特殊な自動更新処理があります。時間型のストレージ要件については、セクション11.7「データ型のストレージ要件」を参照してください。

日付と時間型を処理するときに、次の考慮事項に留意してください。

  • MySQL は、標準出力形式で所定の日付または時間型の値を取得しますが、(たとえば、日付または時間型に割り当てたり、比較したりする値を指定するときに) 入力した入力値に対してさまざまな形式を解釈しようとします。日付と時間型に許可されている形式の説明については、セクション9.1.3「日付リテラルと時間リテラル」を参照してください。有効な値を入力する必要があります。ほかの形式で値を使用すると、予期しない結果が生じることがあります。

  • MySQL は、複数の形式で値を解釈しようとしますが、日付の部分は、ほかでは一般的に使用される月-日-年や日-月-年の順 ('09-04-98''04-09-98' など) ではなく、年-月-日の順 ('98-09-04' など) で常に指定する必要があります。

  • 2 桁の年を含む日付の値は、世紀が不明なためあいまいです。MySQL は次のルールを使用して 2 桁の年の値を解釈します。

    • 70-99 の範囲の値は 1970-1999 に変換されます。

    • 00-69 の範囲の値は 2000-2069 に変換されます。

    セクション11.3.8「日付での 2 桁の年」も参照してください。

  • ある時間型から別の時間型への値の変換は、セクション11.3.7「日付と時間型間での変換」でのルールに従って行われます。

  • MySQL は自動的に、値が数値のコンテキストで使用される場合には日付または時間値を数値に、またはその反対に変換します。

  • デフォルトで MySQL は、日付または時間型の値で、範囲外であるか、それ以外で型にとって無効である値を見つけた場合、値をその型のゼロ値に変換します。その例外では、範囲外の TIME 値は TIME 範囲の適切な終点に切り落とされます。

  • SQL モードを適切な値に設定することで、MySQL がサポートする日付の種類をより正確に指定できます。(セクション5.1.7「サーバー SQL モード」を参照してください。)ALLOW_INVALID_DATES SQL モードを有効にすることによって、'2009-11-31' などの特定の日付を MySQL に受け入れさせることができます。これは、ユーザーが今後の処理のために、(たとえば Web フォームで) 指定した間違っている可能性のある値をデータベースに格納するときに役立ちます。このモードでは、MySQL は、月が 1 から 12 までの範囲にあることと、日付が 1 から 31 までの範囲にあることのみ検証します。

  • MySQL では、DATE または DATETIME カラムに、日がゼロ、または月および日がゼロである日付の格納を許可しています。これは、正確な日付がわかっていない可能性のある生年月日を格納する必要があるアプリケーションで役立ちます。この場合は、単に日付を '2009-00-00' または '2009-01-00' として格納します。このような日付を格納する場合は、DATE_SUB()DATE_ADD() などの完全な日付を必要とする関数で正しい結果が返されることは期待しないでください。日付でゼロの月または日の部分を無効にするには、NO_ZERO_IN_DATE SQL モードを有効にします。

  • MySQL では、ダミーの日付として '0000-00-00'ゼロの値を格納できます。場合によっては、これは、NULL 値を使用するよりも便利であり、使用するデータおよびインデックススペースが少なくなります。'0000-00-00' を無効にするには、NO_ZERO_DATE SQL モードを有効にします。

  • Connector/ODBC で使用されるゼロの日付または時間の値は、ODBC がこのような値を処理できないため、NULL に自動的に変換されます。

次の表に、それぞれの型のゼロ値の形式を示します。ゼロ値は特別ですが、表に示されている値を使用して、格納したり、明示的に参照したりできます。また、より簡単に記述できる '0'0 の値を使用してこれを行うこともできます。日付部分 (DATEDATETIME、および TIMESTAMP) を含む時間型では、NO_ZERO_DATE SQL モードが有効な場合、これらの値を使用すると警告が発生します。

データ型ゼロ
DATE'0000-00-00'
TIME'00:00:00'
DATETIME'0000-00-00 00:00:00'
TIMESTAMP'0000-00-00 00:00:00'
YEAR0000

11.3.1 DATE、DATETIME、および TIMESTAMP 型

DATEDATETIME、および TIMESTAMP 型は関連しています。このセクションでは、これらの特徴、似ている点、および異なる点について説明します。MySQL は、セクション9.1.3「日付リテラルと時間リテラル」で説明している複数の形式で、DATEDATETIME、および TIMESTAMP 値を認識します。DATE および DATETIME 範囲の説明では、サポートされているとは、以前の値は機能するが、保証はないということを意味します。

DATE 型は、日付部分を含むが時間部分は含まない値に使用されます。MySQL では、DATE 値の取得と表示は 'YYYY-MM-DD' 形式で行われます。サポートしている範囲は '1000-01-01' から '9999-12-31' です。

DATETIME 型は、日付と時間の両方の部分を含む値に使用されます。MySQL では、DATETIME 値の取得と表示は 'YYYY-MM-DD HH:MM:SS' 形式で行われます。サポートしている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

TIMESTAMP データ型は、日付と時間の両方の部分を含む値に使用されます。TIMESTAMP には、'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC の範囲があります。

DATETIME または TIMESTAMP 値には、マイクロ秒 (6 桁) までの精度で後続の小数秒部分を含めることができます。特に、MySQL 5.6.4 以降では、DATETIME または TIMESTAMP カラムに挿入された値の小数部はすべて破棄されずに格納されます。小数部が含まれる場合、これらの値の形式は 'YYYY-MM-DD HH:MM:SS[.fraction]' であり、DATETIME 値の範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' であり、TIMESTAMP 値の範囲は '1970-01-01 00:00:01.000000' から '2038-01-19 03:14:07.999999' です。小数部は、常に時間の残りの部分から小数点で区分する必要があります。これ以外の小数秒区切り文字は認識されません。MySQL の小数秒のサポートの詳細は、セクション11.3.6「時間値での小数秒」を参照してください。

TIMESTAMP および (MySQL 5.6.5 以降の) DATETIME データ型では、自動初期化と現在の日付および時間への自動更新機能が用意されています。詳細は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」を参照してください。

MySQL は、TIMESTAMP 値を、ストレージでは現在のタイムゾーンを UTC に変換し、取得では UTC から現在のタイムゾーンに戻します。(DATETIME などのほかの型ではこれは行われません。)デフォルトでは、接続ごとの現在のタイムゾーンはサーバーの時間です。タイムゾーンは接続ごとに設定できます。タイムゾーン設定が一定であるかぎり、格納した値と同じ値に戻すことができます。TIMESTAMP 値を格納したあとで、タイムゾーンを変更して値を取り出すと、取り出された値は格納した値とは異なります。これは、同じタイムゾーンが両方向への変換に使用されなかったために起こります。現在のタイムゾーンは、time_zone システム変数の値として使用できます。詳細は、セクション10.6「MySQL Server でのタイムゾーンのサポート」を参照してください。

無効な DATEDATETIME、または TIMESTAMP 値は、適切な型のゼロ値 ('0000-00-00' または '0000-00-00 00:00:00') に変換されます。

MySQL では日付値解釈の特定のプロパティーに注意してください。

  • MySQL は、文字列として指定された値に、緩やかな形式を使用でき、この形式では、どの句読点文字でも日付部分と時間部分の区切り文字として使用できます。場合によっては、この構文は偽りになることがあります。たとえば、'10:11:12' などの値は、:区切り文字のために時間値のように見えることがありますが、日付のコンテキストで使用された場合は、'2010-11-12' の年と解釈されます。値 '10:45:15' は、'45' が有効な月ではないので、'0000-00-00' に変換されます。

    日付および時間の部分と小数秒部分との間の区切り文字として認識される唯一の文字が小数点です。

  • サーバーは、月と日の値が、それぞれが 1 から 12 と 1 から 31 の範囲内にあるだけではなく、有効である必要があります。厳密モードが無効になっていると、'2004-04-31' のような無効な日付は '0000-00-00' に変換され、警告メッセージが表示されます。厳密モードが有効なときは、無効な日付によってエラーが発生します。このような日付を許可するには、ALLOW_INVALID_DATES を有効にします。詳細は、セクション5.1.7「サーバー SQL モード」を参照してください。

  • MySQL は、日または月カラムにゼロを含んだ TIMESTAMP 値や、無効な日付の値を受け入れません。このルールに対する唯一の例外は、特殊なゼロ値である '0000-00-00 00:00:00' です。

  • MySQL 5.6.4 より前では、テーブルから選択しない場合、CAST()TIMESTAMP 値を文字列として扱います。(これは、FROM DUAL を指定した場合にも当てはまります。)セクション12.10「キャスト関数と演算子」を参照してください。

  • 2 桁の年を含む日付の値は、世紀が不明なためあいまいです。MySQL は次のルールを使用して 2 桁の年の値を解釈します。

    • 00-69 の範囲の値は 2000-2069 に変換されます。

    • 70-99 の範囲の値は 1970-1999 に変換されます。

    セクション11.3.8「日付での 2 桁の年」も参照してください。

注記

MySQL Server は、MAXDB SQL モードを有効にして実行できます。この場合、TIMESTAMPDATETIME と同じです。テーブル作成時にこのモードが有効になっている場合、TIMESTAMP カラムは DATETIME カラムとして作成されます。この結果、このようなカラムは DATETIME 表示形式を使用し、同じ範囲の値を持ち、自動初期化機能や、現在の日付と時間に自動的に更新する機能はありません。セクション5.1.7「サーバー SQL モード」を参照してください。

11.3.2 TIME 型

MySQL では、TIME 値の取り出しと表示は 'HH:MM:SS' 形式 (時間の部分の値が大きい場合は 'HHH:MM:SS' 形式) で行われます。TIME 値の範囲は、'-838:59:59' から '838:59:59' です。TIME 型は、時間 (24 時間以下にする必要があります) を表すだけでなく、経過時間や、2 つのイベント間の時間 (24 時間よりも非常に長くなる場合も、負になる場合もあります) を表すこともできるので、時間の部分は非常に大きくなる可能性があります。

MySQL が TIME 値を認識する形式は複数あり、そのいくつかにはマイクロ秒 (6 秒) までの精度で後続の小数秒部分を含めることができます。セクション9.1.3「日付リテラルと時間リテラル」を参照してください。MySQL の小数秒のサポートの詳細は、セクション11.3.6「時間値での小数秒」を参照してください。特に、MySQL 5.6.4 以降では、TIME カラムに挿入された値の小数部はすべて破棄されずに格納されます。小数部が含まれている場合、TIME 値の範囲は '-838:59:59.000000' から '838:59:59.000000' です。

TIME カラムに省略された値を割り当てる場合は注意してください。MySQL は、コロン付きの省略された TIME 値を時間と解釈します。つまり、'11:12''00:11:12' ではなく '11:12:00' を意味します。MySQL は、右端の 2 桁が秒を表すという仮定を使用して (つまり、時間としてではなく経過時間として)、コロンのない省略された値を解釈します。たとえば、'1112'1112'11:12:00' (11 時 12 分) を表すように見えますが、MySQL では '00:11:12' (11 分 12 秒) と解釈されます。同様に、'12'12'00:00:12' と解釈されます。

時間部分と小数秒部分との間の区切り文字として認識される唯一の文字が小数点です。

デフォルトでは、TIME 範囲外にあるが、それ以外は有効な値は、範囲のもっとも近い終点に切り落とされます。たとえば、'-850:00:00''850:00:00' は、それぞれ '-838:59:59''838:59:59' に変換されます。無効な TIME 値は、'00:00:00' に変換されます。'00:00:00' はそれ自体が有効な TIME 値なので、元の値が '00:00:00' と指定されたのかどうか、無効であったかどうか、テーブルに格納された '00:00:00' の値から判断できません。

無効な TIME 値の制限を厳しくするには、エラーが発生するように厳密な SQL モードを有効にしてください。セクション5.1.7「サーバー SQL モード」を参照してください。

11.3.3 YEAR 型

YEAR 型は年の値を表すために使用される 1 バイトの型です。これは YEAR(4) または YEAR(2) と宣言して、4 文字または 2 文字の表示幅を指定できます。幅が指定されていない場合、デフォルトは 4 文字になります。

注記

YEAR(2) データ型には、使用する前に考慮する必要のある特定の問題があります。また、MySQL 5.6.6 以降では、YEAR(2) は非推奨です。既存のテーブル内の YEAR(2) カラムは以前のとおりに扱われますが、新規または変更したテーブルでは YEAR(2)YEAR(4) に変換されます。詳細は、セクション11.3.4「YEAR(2) の制限と YEAR(4) への移行」を参照してください。

YEAR(4)YEAR(2) は表示形式が異なりますが、値の範囲は同じです。4 桁の形式の場合、MySQL は、YYYY の形式と 1901 から 2155、または 0000 の範囲で、YEAR 値を表示します。2 桁形式の場合、MySQL は、70 (1970 または 2070) や 69 (2069) など、最後 (最下位) の 2 桁だけを表示します。

YEAR の入力値は、次に示すさまざまな形式で指定できます。

  • 1901 から 2155 の範囲の 4 桁の数値として。

  • '1901' から '2155' の範囲の 4 桁の文字列として。

  • 1 から 99 の範囲の 1 桁または 2 桁の数値として。MySQL は、1 から 6970 から 99 の範囲の値を、2001 から 20691970 から 1999 の範囲の YEAR 値に変換します。

  • '0' から '99' の範囲の 1 桁または 2 桁の文字列として。MySQL は、'0' から '69''70' から '99' の範囲の値を、2000 から 20691970 から 1999 の範囲の YEAR 値に変換します。

  • 数値 0 を挿入した場合、その効果は YEAR(2)YEAR(4) で異なります。YEAR(2) の場合、00 の表示値と 2000 の内部値の結果になります。YEAR(4) の場合、0000 の表示値と 0000 の内部値の結果になります。YEAR(4) にゼロを指定し、これを 2000 として解釈させるには、文字列 '0' または '00' としてこれを指定します。

  • NOW() などの YEAR コンテキストで許容される値を返す関数の結果として。

MySQL は無効な YEAR 値を 0000 に変換します。

セクション11.3.8「日付での 2 桁の年」も参照してください。

11.3.4 YEAR(2) の制限と YEAR(4) への移行

このセクションでは、YEAR(2) の使用時に生じる可能性のある問題を取り上げ、既存の YEAR(2) カラムを YEAR(4) に変換するための情報について説明します。

YEAR(4)YEAR(2) の値の内部範囲は同じですが (1901 から 2155、および 0000)、YEAR(2) の表示幅は、表示値が内部値の最後の 2 桁しか示さず、世紀を表す最初の 2 桁を省略するので、その型があいまいになります。特定の状況下では情報が失われる結果になることもあります。このため、YEAR データ型が必要なときは必ず、アプリケーション全体で YEAR(2) の使用を避け、YEAR(4) を使用するよう検討してください。MySQL 5.6.6 以降では、4 以外の表示値の YEAR データ型 (特に YEAR(2)) のサポートが縮小し、今後のリリースで完全に廃止される予定なので、ある時点で変換が必要になります。

YEAR(2) の制限

YEAR(2) データ型に関する問題には、表示値のあいまいさと、値のダンプおよびリロード時または文字列への変換時に情報損失の可能性があります。

  • YEAR(2) の表示値はあいまいな場合があります。次の例で示すように、異なる内部値を持つ最大 3 つの YEAR(2) 値を同じ表示値にできます。

    mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR(4));Query OK, 0 rows affected (0.01 sec)
    mysql> INSERT INTO t (y2) VALUES(1912),(2012),(2112);Query OK, 3 rows affected (0.00 sec)
    Records: 3 Duplicates: 0 Warnings: 0
    mysql> UPDATE t SET y4 = y2;Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3 Changed: 3 Warnings: 0
    mysql> SELECT * FROM t;+------+------+
    | y2 | y4 |
    +------+------+
    | 12 | 1912 |
    | 12 | 2012 |
    | 12 | 2112 |
    +------+------+
    3 rows in set (0.00 sec)
  • mysqldump を使用して、前述の項目に作成されたテーブルをダンプする場合、ダンプファイルは、同じ 2 桁の表現 (12) を使用してすべての y2 値を表します。ダンプファイルからテーブルをリロードした場合、すべての結果の行に内部値 2012 と表示値 12 を含まれるので、これらの違いが失われます。

  • YEAR(2) または YEAR(4) データ値を文字列形式に変換した場合、YEAR 型の表示幅が使用されます。YEAR(2)YEAR(4) の両方のカラムに 1970 の値が含まれるとします。それぞれのカラムを文字列に割り当てた結果、それぞれ '70''1970' の値になります。つまり、YEAR(2) から文字列への変換で情報の損失が起こります。

  • 1970 から 2069 の範囲から外れた値は、CSV テーブルの YEAR(2) カラムに挿入されるときに、間違って格納されます。たとえば、2111 を挿入すると、表示値は 11 になりますが、内部値は 2011 になります。

これらの問題を避けるには、YEAR(2) ではなく YEAR(4) を使用してください。移行戦略に関した提案は、このセクションで後述します。

MySQL 5.6 の YEAR(2) サポートの縮小

MySQL 5.6.6 以降で、YEAR(2) のサポートは縮小されます。

  • 新しいテーブルの YEAR(2) カラム定義は、YEAR(4) に (警告付きで) 変換されます。

    mysql> CREATE TABLE t1 (y YEAR(2));Query OK, 0 rows affected, 1 warning (0.03 sec)
    mysql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 1818
    Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
    1 row in set (0.00 sec)
    mysql> SHOW CREATE TABLE t1\G*************************** 1. row *************************** Table: t1
    Create Table: CREATE TABLE `t1` ( `y` year(4) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
  • 既存のテーブルの YEAR(2)YEAR(2) として残され、古いバージョンの MySQL と同様にクエリーで処理されます。ただし、複数のプログラムまたはステートメントが、YEAR(2)YEAR(4) に自動的に変換します。

    • テーブルの再構築を招く ALTER TABLE ステートメント。

    • REPAIR TABLE (YEAR(2) カラムがテーブルに含まれることが CHECK TABLE で検出された場合に、使用するように推奨されます)。

    • mysql_upgrade (REPAIR TABLE を使用します)。

    • mysqldump でのダンプおよびダンプファイルのリロード。上記の 3 つの項目が実行する変換とは異なり、ダンプとリロードは値を変更する可能性があります。

    MySQL アップグレードでは通常、最後の 2 つの項目のうち少なくとも 1 つを含みます。ただし、YEAR(2) については mysql_upgrade をお勧めします。前述のように値を変更する可能性があるので、mysqldump は使用しないでください。

YEAR(2) から YEAR(4) への移行

YEAR(2) カラムを YEAR(4) に変換するには、手動でアップグレードなしにいつでも行えます。または、YEAR(2) のサポートが縮小したバージョンの MySQL (MySQL 5.6.6 以降) にアップグレードしてから、MySQL で YEAR(2) カラムを自動的に変換できます。後者の場合は、データのダンプとリロードによるアップグレードは行わないでください。データ値を変更する可能性があります。さらに、レプリケーションを使用する場合は、注意の必要なアップグレードに関する考慮事項があります。

YEAR(2) カラムを YEAR(4) に手動で変換するには、ALTER TABLE を使用してください。テーブル t1 に次の定義があるとします。

CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');

次のように ALTER TABLE を使用してカラムを変更します。必ず、NOT NULLDEFAULT などのすべてのカラム属性を含めてください。

ALTER TABLE t1 MODIFY ycol YEAR(4) NOT NULL DEFAULT '1970';

ALTER TABLE ステートメントは、YEAR(2) 値を変更しないでテーブルを変換します。サーバーがレプリケーションマスターである場合、ALTER TABLE ステートメントはスレーブに複製され、各対応するテーブルを変更します。

別の移行方法では、バイナリアップグレードを実行します。データのダンプおよびリロードを行わないで MySQL 5.6.6 以降をインストールします。続いて、mysql_upgrade を実行します。これは、REPAIR TABLE を使用して、データ値を変更しないで YEAR(2) カラムを YEAR(4) に変換します。サーバーがレプリケーションマスターである場合、--skip-write-binlog オプションを付けて mysql_upgrade を呼び出さないかぎり、REPAIR TABLE ステートメントはスレーブに複製され、各対応するテーブルを変更します。

レプリケーションサーバーへのアップグレードでは通常、新しいバージョンの MySQL へのスレーブのアップグレードと、マスターのアップグレードが行われます。たとえば、マスターとスレーブの両方で MySQL 5.5 が実行している場合、通常のアップグレードシーケンスでは、スレーブを 5.6 にアップグレードしてからマスターを 5.6 にアップグレードします。MySQL 5.6.6 以降での YEAR(2) の異なる扱いに関しては、このアップグレードシーケンスにより問題が生じます。スレーブがアップグレードされているが、マスターがまだアップグレードされていないとします。この場合、マスター上に YEAR(2) カラムを含んだテーブルを作成すると、スレーブ上では YEAR(4) カラムを含むテーブルが作成されます。この結果、ステートメントベースのレプリケーションを使用した場合、これらの操作はマスターとスレーブで異なる結果になります。

  • 数値 0 の挿入。結果の値は、マスター上では 2000 の内部値になりますが、スレーブ上では 0000 の内部値になります。

  • 文字列への YEAR(2) の変換。この操作は、マスター上では YEAR(2) の表示値を使用しますが、スレーブ上では YEAR(4) の表示値を使用します。

このような問題を避けるには、次の戦略のいずれかを使用します。

  • ステートメントベースのレプリケーションの代わりに行ベースのレプリケーションを使用します。

  • アップグレード前に、マスター上のすべての YEAR(2) カラムを YEAR(4) に変更します。(前述のように ALTER TABLE を使用します。)続いて、YEAR(2) から YEAR(4) の移行でマスターとスレーブ間で違いが生じることなく、通常どおりに (最初にスレーブ、次にマスター) アップグレードできます)。

mysqldump でデータをダンプし、アップグレードしたあとにダンプファイルをリロードするという移行方法は使用しないでください。前述のように、これは YEAR(2) 値を変更する可能性があります。

YEAR(2) から YEAR(4) への移行では、次のような条件下で動作が変更された可能性がないかどうかアプリケーションコードの検査も行う必要があります。

  • YEAR カラムを選択すれば正確に 2 桁が生成されると予想しているコード。

  • 0YEAR(2) または YEAR(4) に挿入すると、それぞれ 2000 または 0000 の内部値になるという、数値 0 の挿入に対する別々の処理に対応していないコード。

11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能

MySQL 5.6.5 以降では、TIMESTAMP および DATETIME カラムを自動的に初期化でき、現在の日付および時間 (つまり、現在のタイムスタンプ) に自動的に更新できます。5.6.5 より前では、これは TIMESTAMP にしか当てはまらず、テーブルあたり最大で 1 つの TIMESTAMP カラムにしか当てはまりません。次の注意事項では、最初に、MySQL 5.6.5 以降での自動初期化および更新機能について、次に 5.6.5 より前のバージョンでの相違点について説明します。

テーブル内のあらゆる TIMESTAMP または DATETIME カラムに対して、デフォルト値または自動更新値、あるいはその両方として、現在のタイムスタンプを割り当てることができます。

  • 自動初期化されたカラムは、カラムに値を指定しない挿入行に対して現在のタイムスタンプに設定されます。

  • 自動更新されたカラムは、行内のほかのカラムの値がその現在の値から変更されると、現在のタイムスタンプに自動的に更新されます。自動更新されたカラムは、ほかのすべてのカラムがその現在の値に設定されていれば、変更されないまま保持されます。ほかのカラムが変更したときに、自動更新したカラムが更新しないようにするには、明示的にこれを現在の値に設定します。ほかのカラムが変更しない場合でも、自動更新カラムを更新するには、明示的にこれを必要な値に設定します (たとえば CURRENT_TIMESTAMP に設定します)。

さらに、NULL 属性を使用して NULL 値を許可するように定義されていないかぎり、NULL 値を割り当てることによって、すべての TIMESTAMP カラムを初期化したり、現在の日付と時間に更新したりできます。

自動プロパティーを指定するには、カラム定義で DEFAULT CURRENT_TIMESTAMP および ON UPDATE CURRENT_TIMESTAMP 句を使用します。句の順序は関係ありません。両方がカラム定義にある場合、どちらも最初に実行できます。CURRENT_TIMESTAMP のシノニムのいずれも、CURRENT_TIMESTAMP と同じ意味があります。これらは、CURRENT_TIMESTAMP()NOW()LOCALTIMELOCALTIME()LOCALTIMESTAMP、および LOCALTIMESTAMP() です。

DEFAULT CURRENT_TIMESTAMP および ON UPDATE CURRENT_TIMESTAMP の使用は、TIMESTAMP および DATETIME に固有です。DEFAULT 句も、DEFAULT 0DEFAULT '2000-01-01 00:00:00' などの一定 (非自動) のデフォルト値を指定するために使用できます。

注記

DEFAULT 0 を使用した次の例は、NO_ZERO_DATE SQL モードが有効な場合には機能しません。このモードではゼロの日付値 (たとえば 0'0000-00-00 00:00:00' として指定) が拒否されるからです。TRADITIONAL SQL モードに NO_ZERO_DATE が含まれています。

TIMESTAMP または DATETIME カラム定義では、現在のタイムスタンプをデフォルト値と自動更新値の両方に対して指定することも、どちらか一方について指定することも、両方について指定しないこともできます。異なるカラムは、自動プロパティーの別々の組み合わせを持つことができます。次のルールは可能性のある場合について記述しています。

  • DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP の両方を使用した場合、カラムは、デフォルト値が現在のタイムスタンプになり、現在のタイムスタンプに自動的に更新されます。

    CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
  • DEFAULT 句を使用するが ON UPDATE CURRENT_TIMESTAMP 句を使用しない場合、カラムには所定のデフォルト値が設定され、現在のタイムスタンプに自動的に更新されません。

    デフォルトは、DEFAULT 句で CURRENT_TIMESTAMP を指定するか定数値を指定するかに応じて異なります。CURRENT_TIMESTAMP を使用した場合、デフォルトは現在のタイムスタンプになります。

    CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    定数を使用した場合、デフォルトは所定の値になります。この場合、カラムには自動的なプロパティーはありません。

    CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0, dt DATETIME DEFAULT 0
    );
  • ON UPDATE CURRENT_TIMESTAMP 句と定数の DEFAULT 句を使用した場合、カラムは、現在のタイムスタンプに自動的に更新され、所定の定数のデフォルト値があります。

    CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
    );
  • ON UPDATE CURRENT_TIMESTAMP 句を使用するが DEFAULT 句を使用しない場合、カラムは、自動的に現在のタイムスタンプに更新され、そのデフォルト値に現在のタイムスタンプは使用されません。

    この場合のデフォルトは型により異なります。TIMESTAMP は、NULL 属性を使用して定義されていないかぎり (この場合はデフォルトは NULL です)、デフォルトは 0 です。

    CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
    );

    DATETIME は、NOT NULL 属性で定義されていないかぎり (この場合、デフォルトは 0 です)、デフォルトは NULL です。

    CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
    );

TIMESTAMP および DATETIME カラムには、明示的に指定されないかぎり自動プロパティーはありません。ただし、DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP がどちらも明示的に指定されていない場合は、デフォルトで最初のTIMESTAMP カラムに両方とも存在します。最初の TIMESTAMP カラムについて自動プロパティーを抑制するには、次のいずれかの戦略を使用します。

  • explicit_defaults_for_timestamp システム変数を有効にします。この変数が有効な場合、自動初期化および更新機能を指定する DEFAULT CURRENT_TIMESTAMP および ON UPDATE CURRENT_TIMESTAMP 句は使用可能ですが、カラム定義に明示的に含まれていないかぎり、どの TIMESTAMP カラムにも割り当てられません。

  • または、explicit_defaults_for_timestamp が無効な場合 (デフォルト)、次のどちらかを行います。

    • 定数のデフォルト値を指定する DEFAULT 句を含むカラムを定義します。

    • NULL 属性を指定します。またこれにより、カラムで NULL 値が許可されます。つまり、カラムを NULL に設定することによって現在のタイムスタンプを割り当てることができなくなります。NULL を割り当てると、カラムは NULL に設定されます。

次のテーブル定義を考慮してください。

CREATE TABLE t1 ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 ( ts1 TIMESTAMP NULL, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 ( ts1 TIMESTAMP NULL DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

テーブルには次のプロパティーがあります。

  • 各テーブル定義において、最初の TIMESTAMP カラムには、自動初期化または更新機能はありません。

  • 各テーブルでは、ts1 カラムで NULL 値を処理する方法が異なります。t1 の場合、ts1NOT NULL であり、これに NULL の値を割り当てると、現在のタイムスタンプに設定されます。t2t3 の場合、ts1 では NULL を使用でき、これに NULL の値を割り当てると、NULL に設定されます。

  • t2t3 では、ts1 のデフォルト値が異なります。t2 の場合、ts1 は、NULL を許可するように定義されているので、明示的な DEFAULT 句がない場合はデフォルトも NULL です。t3 の場合、ts1NULL を使用できますが、明示的なデフォルトは 0 です。

TIMESTAMP または DATETIME カラム定義のいずれかの場所に明示的な小数秒精度値が含まれる場合、カラム定義全体で同じ値を使用する必要があります。次の場合は許可されます。

CREATE TABLE t1 ( ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

次の場合は許可されません。

CREATE TABLE t1 ( ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

MySQL 5.6.5 より前の自動タイムスタンププロパティー

MySQL 5.6.5 より前では、自動初期化および更新機能のサポートは非常に限定的です。

  • DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP は、DATETIME カラムで使用できません。

  • DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP は、テーブルあたり最大で 1 つの TIMESTAMP カラムでしか使用できません。現在のタイムスタンプを、あるカラムのデフォルト値にして、別のカラムの自動更新値にはできません。

これらのプロパティーを使用するかどうか、どの TIMESTAMP カラムで必要になるかを選択できます。これは、自動的に初期化される、または現在のタイムスタンプに自動的に更新されるテーブル内の最初のカラムにする必要はありません。別の TIMESTAMP カラムに対して自動初期化または更新を指定するには、前述のように、最初のカラムに対する自動プロパティーを制約する必要があります。この場合、ほかの TIMESTAMP カラムでは、DEFAULT および ON UPDATE 句のルールは、最初の TIMESTAMP カラムの場合と同じですが、両方の句を省略した場合、自動初期化も更新も行われません。

TIMESTAMP の初期化と NULL 属性

デフォルトでは、TIMESTAMP カラムは NOT NULL であり、NULL 値を含めることはできず、NULL を割り当てると現在のタイムスタンプが割り当てられます。NULL を含めるように TIMESTAMP カラムを許可するには、NULL 属性で明示的に宣言します。この場合、別のデフォルト値を指定する DEFAULT 句でオーバーライドされないかぎり、デフォルト値も NULL になります。DEFAULT NULL を使用すると、デフォルト値として NULL を明示的に指定できます。(NULL 属性が宣言されていない TIMESTAMP カラムの場合、DEFAULT NULL は無効です。)TIMESTAMP カラムで NULL 値を許可する場合、NULL を割り当てると、このカラムは現在のタイムスタンプではなく NULL に設定されます。

次のテーブルには、NULL 値を許可している複数の TIMESTAMP カラムが含まれています。

CREATE TABLE t
( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

NULL 値を許可する TIMESTAMP カラムは、次のいずれかの状況に当てはまる場合を除き、挿入時に現在のタイムスタンプを取りません

  • デフォルト値が CURRENT_TIMESTAMP と定義され、カラムに対して値が指定されていない

  • CURRENT_TIMESTAMP、またはNOW() などのそのいずれかのシノニムが明示的にカラムに挿入されている

つまり、NULL 値を許可するように定義されている TIMESTAMP カラムは、その定義に DEFAULT CURRENT_TIMESTAMP が含まれている場合にのみ自動初期化します。

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

TIMESTAMP カラムで NULL 値を許可しているが、定義に DEFAULT CURRENT_TIMESTAMP が含まれていない場合、現在の日付と時間に対応する値を明示的に挿入する必要があります。t1 および t2 テーブルに次の定義があるとします。

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);

挿入時にどちらかのテーブルの TIMESTAMP カラムを現在のタイムスタンプに設定するには、明示的にそのカラムにこの値を割り当てます。例:

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

11.3.6 時間値での小数秒

MySQL 5.6.4 より前では、時間値で小数秒部分が許可されているインスタンスは制限されています。後続の小数部は、リテラル値などのコンテキストで許可され、一部の時間関数への引数またはそこからの戻り値で許可されています。例:

mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+

ただし、MySQL は時間データ型のカラムに値を格納するときに、小数部を破棄し、それを格納しません。

MySQL 5.6.4 以降では、マイクロ秒 (6 桁) までの精度を持つ TIMEDATETIME、および TIMESTAMP 値に対して小数秒のサポートを拡張しています。

  • 小数秒部を含むカラムを定義するには、type_name(fsp) の構文を使用します。ここで、type_nameTIMEDATETIME、または TIMESTAMP であり、fsp は小数秒の精度です。例:

    CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

    fsp 値を指定する場合、0 から 6 の範囲にする必要があります。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。(これは、以前の MySQL バージョンと互換性を保つため、標準 SQL のデフォルトである 6 とは異なっています。)

  • 小数秒部分を持つ TIMEDATE、または TIMESTAMP 値を同じ型のカラムに挿入するが、小数部の桁数が少ない場合、次の例に示すように丸めが行われます。

    mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );Query OK, 0 rows affected (0.33 sec)
    mysql> INSERT INTO fractest VALUES > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');Query OK, 1 row affected (0.03 sec)
    mysql> SELECT * FROM fractest;+-------------+------------------------+------------------------+
    | c1 | c2 | c3 |
    +-------------+------------------------+------------------------+
    | 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
    +-------------+------------------------+------------------------+
    1 row in set (0.00 sec)

    このような丸め行われたときに、警告やエラーは表示されません。この動作は、SQL 標準に従い、サーバーの sql_mode 設定の影響は受けません。

  • 時間引数を取る関数は、小数秒を含む値を受け入れます。時間関数からの戻り値には、必要に応じて小数秒が含まれます。たとえば、引数を付けない NOW() は、小数部のない現在の日付と時間を返しますが、0 から 6 のオプション引数を取って、その桁数の小数秒部が戻り値に含まれていることを指定します。

  • 時間リテラルの構文は、DATE 'str'TIME 'str'、および TIMESTAMP 'str' の時間値と ODBC 構文同等の値を生み出します。指定されている場合、結果の値には後続の小数秒部分が含まれます。以前は、時間型キーワードは無視され、これらの構造は文字列値を生成していました。標準 SQL と ODBC の日付および時間リテラルを参照してください。

場合によっては、以前に受け入れられていた構文が別の結果を生成することがあります。次の項目は、問題を回避するために既存のコードのどの箇所を変更する必要があるかを示します。

  • 式の中には、以前の結果とは異なる結果を生成するものがあります。例: timestamp システム変数は、整数ではなくマイクロ秒小数部を含む値を返します。現在の時間を含む結果を返す関数 (CURTIME()SYSDATE()UTC_TIMESTAMP() など) は、fsp 値として引数を解釈し、戻り値にはその桁の小数秒部分が含まれます。以前には、これらの関数は引数を許可していましたが、無視していました。

  • TIME 値は、現在の日付に時間を追加することにより DATETIME に変換されます。(これは、時間値が '00:00:00' から '23:59:59' の範囲から外れている場合に、結果の日付部分が現在の日付と異なることを意味します。)以前には、TIME 値の DATETIME への変換は信頼性がありませんでした。セクション11.3.7「日付と時間型間での変換」を参照してください。

  • TIMESTAMP(N) は古い MySQL バージョンで許可されていましたが、N は小数秒精度ではなく表示幅でした。この動作のサポートは MySQL 5.5.3 で廃止されたので、適度に最新の状態に維持されているアプリケーションであれば、この問題の影響を受けません。それ以外の場合では、コードを書き換える必要があります。

11.3.7 日付と時間型間での変換

ある程度まで、ある時間型から別の時間型に値を変換できます。ただし、値の変更や情報の損失が生じることがあります。どの場合でも、時間型間の変換は、変換される型で有効な値の範囲に依存します。たとえば、DATEDATETIME、および TIMESTAMP 値はすべて、同じセットの形式を使用して指定できますが、すべての型で値の範囲が同じであるわけではありません。TIMESTAMP 値は、1970 UTC より古い値にしたり、'2038-01-19 03:14:07' UTC より新しい値にしたりできません。つまり、'1968-01-01' などの日付は、DATE または DATETIME 値としては有効ですが、TIMESTAMP 値としては有効ではなく、0 に変換されます。

DATE 値の変換:

  • DATE 値には時間情報が含まれないので、DATETIME または TIMESTAMP 値に変換すると、'00:00:00' の時間部分が追加されます。

  • TIME 値への変換は有用ではありません。結果は '00:00:00' になります。

DATETIME および TIMESTAMP 値の変換:

  • DATE 型には時間情報が含まれないので、DATE 値に変換すると時間部分が破棄されます。

  • TIME 型には日付情報が含まれないので、TIME 値に変換すると日付部分が破棄されます。

TIME 値のほかの時間型への変換はバージョンによって異なります。

  • MySQL 5.6.4 以降では、CURRENT_DATE() の値が日付部分に使用されます。TIME は (時間ではなく) 経過時間として解釈され、日付に追加されます。これは、時間値が '00:00:00' から '23:59:59' の範囲から外れている場合に、結果の日付部分が現在の日付と異なることを意味します。

    現在の日付が '2012-01-01' であるとします。'12:00:00''24:00:00''-12:00:00'TIME 値は、DATETIME または TIMESTAMP 値に変換されると、それぞれ '2012-01-01 12:00:00''2012-01-02 00:00:00''2011-12-31 12:00:00' になります。

    TIME から DATE への変換も同様ですが、結果から時間部分が破棄され、それぞれ '2012-01-01''2012-01-02''2011-12-31' になります。

  • 5.6.4 より前の MySQL では、時間の文字列を日付または日付時間として解析することによって、時間値を日付または日付時間値に変換します。これが役立つ可能性はありません。たとえば、'23:12:31' は、日付として解釈されると '2023-12-31' になります。日付として有効でない時間は '0000-00-00' または NULL になります。

明示的な変換を使用して暗黙的な変換をオーバーライドできます。たとえば、DATE および DATETIME 値の比較で、DATE 値は、'00:00:00' の時間部分を追加することにより、強制的に DATETIME 型に変更されます。代わりに DATETIME 値の時間部分を無視して比較を実行するには、次の方法で CAST() 関数を使用します。

date_col = CAST(datetime_col AS DATE)

TIME および DATETIME 値の数値形式への (+0 の追加などによる) 変換は、次のように行われます。

  • MySQL 5.6.4 以降では、TIME(N) または DATETIME(N) は、N が 0 (または省略) の場合は整数に、N が正の数の場合は N の 10 進数を含む DECIMAL 値に変換されます。

    mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;+-----------+-------------+--------------+
    | CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
    +-----------+-------------+--------------+
    | 09:28:00 | 92800 | 92800.887 |
    +-----------+-------------+--------------+
    mysql> SELECT NOW(), NOW()+0, NOW(3)+0;+---------------------+----------------+--------------------+
    | NOW() | NOW()+0 | NOW(3)+0 |
    +---------------------+----------------+--------------------+
    | 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
    +---------------------+----------------+--------------------+
  • MySQL 5.6.4 より前では、変換の結果は、マイクロ秒部分が .000000 である倍精度値になります。

    mysql> SELECT CURTIME(), CURTIME()+0;+-----------+--------------+
    | CURTIME() | CURTIME()+0 |
    +-----------+--------------+
    | 09:28:00 | 92800.000000 |
    +-----------+--------------+
    mysql> SELECT NOW(), NOW()+0;+---------------------+-----------------------+
    | NOW() | NOW()+0 |
    +---------------------+-----------------------+
    | 2012-08-15 09:28:00 | 20120815092800.000000 |
    +---------------------+-----------------------+

11.3.8 日付での 2 桁の年

2 桁の年を含む日付の値は、世紀が不明なためあいまいです。MySQL では、年は内部的に 4 桁で格納されるため、そのような値は 4 桁の形式に変換する必要があります。

DATETIMEDATE、および TIMESTAMP 型では、MySQL は、次のルールを使用して、あいまいな年の値で指定された日付を変換します。

  • 00-69 の範囲の値は 2000-2069 に変換されます。

  • 70-99 の範囲の値は 1970-1999 に変換されます。

YEAR ではルールは同じですが、YEAR(4) に挿入された数値 002000 ではなく 0000 になります。YEAR(4) にゼロを指定し、これを 2000 として解釈させるには、文字列 '0' または '00' としてこれを指定します。

これらのルールは、データ値が何を表すかを妥当に推測する単なる経験則であることを覚えておいてください。MySQL で使用されるルールで必要な値が生成されない場合、4 桁の年を含む明確な入力値を指定する必要があります。

ORDER BY は、2 桁の年を持つ YEAR 値を正しくソートします。

MIN()MAX() などの一部の関数は、YEAR を数値に変換します。つまり、2 桁の年の値は、これらの関数では正しく機能しません。この場合の解決策としては、YEAR を 4 桁の年の形式に変換します。

11.4 文字列型

文字列型には、CHARVARCHARBINARYVARBINARYBLOBTEXTENUM、および SET があります。このセクションでは、これらの型の機能と、クエリーでの使用方法について説明します。文字列型のストレージ要件については、セクション11.7「データ型のストレージ要件」を参照してください。

11.4.1 CHAR および VARCHAR 型

CHAR 型と VARCHAR 型は似ていますが、格納および取得方法が異なります。また、最大長と、末尾のスペースが保持されるかどうかという点でも異なります。

CHAR 型と VARCHAR 型には、格納する最大文字数を表す長さが宣言されています。たとえば、CHAR(30) には最大 30 文字を格納できます。

CHAR カラムの長さは、テーブルを作成したときに宣言した長さに修正されます。この長さには、0 から 255 までの任意の値を指定できます。CHAR 値は格納されると、指定された長さになるように右側がスペースで埋められます。PAD_CHAR_TO_FULL_LENGTH SQL モードが有効になっていないかぎり、CHAR 値が取り出されるときに、末尾のスペースが削除されます。

VARCHAR カラム内の値は可変長の文字列です。長さは 0 から 65,535 までの値で指定できます。VARCHAR カラムの有効な最大長は、最大行サイズ (65,535 バイト、すべてのカラムで共有される) と使用される文字セットによって決まります。セクションD.10.4「テーブルカラム数と行サイズの制限」を参照してください。

CHAR とは対照的に、VARCHAR 値は、1 バイトまたは 2 バイト長のプリフィクスの付いたデータとして格納されます。長さプリフィクスは、値に含まれるバイト数を示します。255 バイト以下の値を格納するカラムでは 1 バイト長のプリフィクスを使用し、255 バイトよりも大きい値を格納するカラムでは 2 バイト長のプリフィクスを使用します。

厳密な SQL モードが有効でない場合に、CHAR または VARCHAR カラムにその最大長を超える値を割り当てると、その値はカラムの最大長に合わせて切り捨てられ、警告メッセージが表示されます。スペース以外の文字の切り捨てに関しては、厳密な SQL モードを使用することで、警告ではなくエラーを発生させて、その値の挿入を抑制できます。セクション5.1.7「サーバー SQL モード」を参照してください。

VARCHAR カラムの場合、使用している SQL モードに関係なく、カラム長を超える末尾のスペースは挿入前に切り捨てられ、警告メッセージが表示されます。CHAR カラムの場合、SQL モードに関係なく、超過した末尾のスペースは通知なしに挿入される値から切り捨てられます。

VARCHAR 値は格納されるときに埋められません。標準 SQL に従い、値を格納し取り出すときに末尾のスペースは保持されます。

次の表は、CHAR(4) カラムと VARCHAR(4) カラムにさまざまな文字列値を格納した結果を表示して、CHARVARCHAR の違いを示しています (カラムには latin1 などのシングルバイト文字セットを使用するものとします)。

CHAR(4)必要なストレージVARCHAR(4)必要なストレージ
'''    '4 バイト''1 バイト
'ab''ab  '4 バイト'ab'3 バイト
'abcd''abcd'4 バイト'abcd'5 バイト
'abcdefgh''abcd'4 バイト'abcd'5 バイト

テーブルの最終行に格納済みとして示されている値は、厳密モードを使用していないときにだけ当てはまります。MySQL が厳密モードで実行されている場合、カラム長を超える値は格納されず、エラーが発生します。

所定の値が CHAR(4) および VARCHAR(4) カラムに格納されると、取り出しのときに末尾のスペースが CHAR カラムから削除されるので、カラムから取り出された値は必ずしも同じではありません。次の例はこの違いを示しています。

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab ) | (ab) |
+---------------------+---------------------+
1 row in set (0.06 sec)

CHAR カラムと VARCHAR カラムの値は、そのカラムに割り当てられた文字セットの照合順序に従ってソートおよび比較されます。

MySQL のすべての照合順序は、PADSPACE 型のものです。これは、MySQL 内のすべての CHARVARCHAR、および TEXT 値が、末尾のスペースに関係なく比較されることを意味します。このコンテキストでの比較には、末尾のスペースが意味を持つ LIKE パターンマッチング演算子は含まれません。例:

mysql> CREATE TABLE names (myname CHAR(10));Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO names VALUES ('Monty');Query OK, 1 row affected (0.00 sec)
mysql> SELECT myname = 'Monty', myname = 'Monty ' FROM names;+------------------+--------------------+
| myname = 'Monty' | myname = 'Monty ' |
+------------------+--------------------+
| 1 | 1 |
+------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty ' FROM names;+---------------------+-----------------------+
| myname LIKE 'Monty' | myname LIKE 'Monty ' |
+---------------------+-----------------------+
| 1 | 0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

これは MySQL のすべてのバージョンに当てはまり、サーバーの SQL モードの影響は受けません。

注記

MySQL の文字セットおよび照合順序の詳細は、セクション10.1「文字セットのサポート」を参照してください。ストレージ要件の追加情報については、セクション11.7「データ型のストレージ要件」を参照してください。

末尾の埋め込み文字が取り除かれたり、比較で無視されたりする場合では、一意の値を必要とするインデックスがカラムに含まれていれば、末尾の埋め込み文字の個数だけが異なるカラム値への挿入は、重複キーエラーになります。たとえば、テーブルに 'a' が含まれている場合、'a ' を格納しようとすると、重複キーエラーが発生します。

11.4.2 BINARY および VARBINARY 型

BINARY および VARBINARY 型は、CHAR および VARCHAR 型に似ていますが、非バイナリ文字列ではなく、バイナリ文字列を格納します。つまり、それらには文字の文字列ではなく、バイトの文字列が含まれています。これは、それらに文字セットがなく、ソートおよび比較は値の中のバイトの数値に基づいていることを意味します。

BINARY および VARBINARY で許可される最大長は、CHAR および VARCHAR の場合と同じですが、BINARY および VARBINARY の長さが文字数ではなくバイト数で表される点が異なります。

BINARY および VARBINARY データ型は CHAR BINARY および VARCHAR BINARY データ型とは異なります。後者の型は、BINARY 属性によってカラムがバイナリ文字列カラムとして扱われることはありません。その代わり、これによってカラムの文字セットのバイナリ照合順序が使用され、カラム自体にはバイナリバイト文字列ではなく非バイナリ文字列が格納されます。たとえば、CHAR(5) BINARY は、デフォルト文字セットが latin1 とすれば、CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin として扱われます。これは、文字セットや照合順序を持たない 5 バイトのバイナリ文字列を格納する BINARY(5) とは異なります。非バイナリ文字列のバイナリ照合順序とバイナリ文字列の違いについては、セクション10.1.7.6「_bin および binary 照合順序」を参照してください。

厳密な SQL モードが有効でない場合に、BINARY または VARBINARY カラムにその最大長を超える値を割り当てると、その値はカラムの最大長に合わせて切り捨てられ、警告メッセージが表示されます。値を切り捨てる場合、厳密な SQL モードを使用することで、警告ではなくエラーを発生させて、その値の挿入を抑制できます。セクション5.1.7「サーバー SQL モード」を参照してください。

BINARY 値は格納されると、特定の長さまで右側がパッド値で埋められます。パッド値は 0x00 (ゼロバイト) です。値は挿入時には右側が 0x00 で埋められ、選択時に後続のバイトは削除されません。すべてのバイトは、ORDER BY および DISTINCT 操作を含め比較で意味があります。0x00 バイトとスペースは比較では異なり、0x00 < スペースです。

例: BINARY(3) カラムの場合、'a ' は挿入時に 'a \0' になります。'a\0' は挿入時に 'a\0\0' になります。選択時、挿入された両方の値は変更されません。

VARBINARY では、挿入時にパディングされることも、選択時にバイトが削除されることもありません。すべてのバイトは、ORDER BY および DISTINCT 操作を含め比較で意味があります。0x00 バイトとスペースは比較では異なり、0x00 < スペースです。

後続のパッドバイトが取り除かれたり、比較で無視されたりする場合では、一意の値を必要とするインデックスがカラムに含まれていれば、後続のパッドバイトの個数だけが異なるカラム値への挿入は、重複キーエラーになります。たとえば、テーブルに 'a' が含まれている場合、'a\0' を格納しようとすると、重複キーエラーが発生します。

バイナリデータの格納に BINARY データ型を使用する予定であり、取り出した値を格納した値とまったく同じにする必要がある場合は、先行のパディングと削除文字を考慮する必要があります。次の例は、BINARY 値の 0x00 パディングによって、カラム値の比較がどのような影響を受けるかについて示しています。

mysql> CREATE TABLE t (c BINARY(3));Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET c = 'a';Query OK, 1 row affected (0.01 sec)
mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 | 0 | 1 |
+--------+---------+-------------+
1 row in set (0.09 sec)

取り出される値を、パディングなしのストレージに指定した値と同じにする必要がある場合は、代わりに VARBINARY か、いずれかの BLOB データ型を使用することをお勧めします。

11.4.3 BLOB 型と TEXT 型

BLOB はさまざまな容量のデータを保持できる大きなバイナリオブジェクトです。BLOB 型は、TINYBLOBBLOBMEDIUMBLOB、および LONGBLOB の 4 つがあります。これらの違いは、保持できる値の最大長だけです。TEXT 型は、TINYTEXTTEXTMEDIUMTEXT、および LONGTEXT の 4 つがあります。これらは 4 つの BLOB 型に対応し、最大長とストレージ要件は同じです。セクション11.7「データ型のストレージ要件」を参照してください。

BLOB 値はバイナリ文字列 (バイトの文字列) として扱われます。これらには文字セットがなく、ソートおよび比較はカラム値内のバイトの数値に基づきます。TEXT 値は非バイナリ文字列 (文字の文字列) として扱われます。これらには文字セットがあり、値は文字セットの照合順序に基づいてソートおよび比較されます。

厳密な SQL モードが有効でない場合に、BLOB または TEXT カラムにその最大長を超える値を割り当てると、その値はカラムの最大長に合わせて切り捨てられ、警告メッセージが表示されます。スペース以外の文字の切り捨てに関しては、厳密な SQL モードを使用することで、警告ではなくエラーを発生させて、その値の挿入を抑制できます。セクション5.1.7「サーバー SQL モード」を参照してください。

TEXT カラムに挿入される値から、超過した末尾のスペースを切り捨てると、SQL モードには関係なく、常に警告が生成されます。

TEXT および BLOB カラムでは、挿入時にパディングは行われず、選択時にバイトは削除されません。

TEXT カラムにインデックスが設定されている場合、インデックスエントリの比較では末尾がスペースで埋められます。これは、インデックスに一意の値が必要な場合、末尾のスペースの個数だけが異なる値に対して重複キーエラーが発生するということを意味します。たとえば、テーブルに 'a' が含まれている場合、'a ' を格納しようとすると、重複キーエラーが発生します。これは BLOB カラムには当てはまりません。

ほとんどの点で、BLOB カラムを、任意の長さに設定できる VARBINARY カラムと見なすことができます。同様に、TEXT カラムを VARCHAR カラムと見なすことができます。BLOBTEXT は、次の点で VARBINARYVARCHAR とは異なっています。

  • BLOBTEXT カラムのインデックスには、インデックスプリフィクス長を指定する必要があります。CHARVARCHAR では、プリフィクス長はオプションです。セクション8.3.4「カラムインデックス」を参照してください。

  • BLOB および TEXT カラムに DEFAULT 値を含めることはできません。

BINARY 属性を TEXT データ型と一緒に使用した場合、カラム文字セットのバイナリ照合順序がそのカラムに割り当てられます。

LONGLONG VARCHARMEDIUMTEXT データ型にマップします。これは互換性機能です。

MySQL Connector/ODBC は BLOB 値を LONGVARBINARY として、TEXT 値を LONGVARCHAR として定義します。

BLOB 値と TEXT 値は非常に長くなる可能性があるので、使用するときに次の制約が生じることがあります。

  • ソート時には、カラムの max_sort_length バイトだけが使用されます。max_sort_length のデフォルト値は 1024 です。サーバーの起動時または実行時に、max_sort_length の値を増やすことによって、ソートまたはグループ化に影響するバイトを増やすことができます。すべてのクライアントで max_sort_length セッション変数の値を変更できます。

    mysql> SET max_sort_length = 2000;mysql> SELECT id, comment FROM t -> ORDER BY comment;
  • 一時テーブルを使用して処理されるクエリーの結果に BLOB カラムまたは TEXT カラムのインスタンスがあると、MEMORY ストレージエンジンがこれらのデータ型をサポートしていないので、サーバーはメモリー内ではなくディスク上でテーブルを使用します (セクション8.4.4「MySQL が内部一時テーブルを使用する仕組み」を参照してください)。ディスクの使用はパフォーマンスの低下を伴うので、クエリーの結果に BLOB カラムまたは TEXT カラムを含めるのは必要な場合に限定してください。たとえば、SELECT * はすべてのカラムを選択するので使用しないでください。

  • BLOB または TEXT オブジェクトの最大サイズはその型で決まりますが、クライアントとサーバー間で実際に送信できる最大値は、使用可能なメモリーの容量と通信バッファーのサイズで決まります。max_allowed_packet 変数の値を変更することでメッセージバッファーサイズを変更できますが、サーバーとクライアントプログラムの両方で変更する必要があります。たとえば、mysqlmysqldump のどちらを使用しても、クライアント側の max_allowed_packet 値を変更できます。セクション8.11.2「サーバーパラメータのチューニング」セクション4.5.1「mysql — MySQL コマンド行ツール」セクション4.5.4「mysqldump — データベースバックアッププログラム」を参照してください。パケットサイズおよびソートしているデータオブジェクトのサイズを、ストレージ要件と比較することもできます。セクション11.7「データ型のストレージ要件」を参照してください。

BLOB 値または TEXT 値はそれぞれ、別々に割り当てられたオブジェクトによって内部的に表現されます。これは、テーブルが開かれるときにカラムごとに一度ストレージが割り当てられる、ほかのすべてのデータ型と対照的です。

メディアファイルなどのバイナリデータを BLOB または TEXT カラムに格納するほうがよい場合もあります。このようなデータの処理には、MySQL の文字列操作関数が役立つことがあります。セクション12.5「文字列関数」を参照してください。セキュリティーなどの理由のために、通常は、アプリケーションユーザーに FILE 権限を与えるのではなく、アプリケーションコードを使用して実行することをお勧めします。MySQL フォーラム (http://forums.mysql.com/) では、さまざまな言語やプラットフォームの詳細について話し合うことができます。

11.4.4 ENUM 型

ENUM は、テーブル作成時にカラム仕様に明示的に列挙された、許可されている値のリストから選択された値を持つ文字列オブジェクトです。これには次の利点があります。

  • 指定可能な値のセットがカラムで制限されている状況でのコンパクトなデータストレージ。入力値として指定した文字列は自動的に数値としてエンコードされます。ENUM 型のストレージ要件については、セクション11.7「データ型のストレージ要件」を参照してください。

  • 読みやすいクエリーと出力。数値は、クエリー結果で対応する文字列に戻されます。

また、次のような考慮が必要な問題が生じる可能性があります。

  • Enumeration Limitationsで説明しているように、数値のように見える列挙値を作成した場合、リテラル値とその内部インデックス番号を混同しやすくなります。

  • Enumeration Sortingで説明しているように、ORDER BY 句で ENUM カラムを使用するには特に注意が必要です。

ENUM カラムの作成と使用

列挙値は引用符で囲んだ文字列リテラルにする必要があります。たとえば、次のように ENUM カラムを持つテーブルを作成できます。

CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name | size |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

'medium' の値を持つ 100 万個の行をこのテーブルに挿入するには、100 万バイトのストレージが必要ですが、実際の文字列 'medium'VARCHAR カラムに格納した場合は、600 万バイト必要になります。

列挙リテラルのインデックス値

それぞれの列挙値にはインデックスが設定されています。

  • カラム仕様にリストされている要素には、1 から始まるインデックス番号が割り当てられています。

  • 空の文字列エラー値のインデックス値は 0 です。つまり、次の SELECT ステートメントを使用して、無効な ENUM 値が割り当てられた行を検索できます。

    mysql> SELECT * FROM tbl_name WHERE enum_col=0;
  • NULL 値のインデックスは NULL です。

  • ここでのインデックスという語は、列挙値のリスト内での位置を示します。これは、テーブルインデックスとはまったく関係ありません。

たとえば、ENUM('Mercury', 'Venus', 'Earth') と指定されたカラムには、次に示すどの値でも含めることができます。それぞれの値のインデックスも示しています。

インデックス
NULLNULL
''0
'Mercury'1
'Venus'2
'Earth'3

ENUM カラムには、最大 65,535 個の個別の要素を含めることができます。(実用的な限度は 3000 個までです。)テーブルには、グループと見なされる ENUM および SET カラムの中の一意の要素リスト定義を、255 個以下を含めることができます。これらの制限の詳細は、セクションD.10.5「.frm ファイル構造により課せられる制限」を参照してください。

ENUM 値を数値コンテキストで取得した場合、カラム値のインデックスが返されます。たとえば、次のように ENUM カラムから数値を取得できます。

mysql> SELECT enum_col+0 FROM tbl_name;

数値引数を取る SUM()AVG() などの関数は、必要に応じて引数を数値にキャストします。ENUM 値の計算にはインデックス番号が使用されます。

列挙リテラルの処理

テーブルが作成されるときに、テーブル定義内の ENUM メンバー値から末尾のスペースが自動的に削除されます。

ENUM カラムに格納された値は、取得されたときに、カラム定義で使用された大文字/小文字で表示されます。ENUM カラムには文字セットと照合順序を割り当てられています。バイナリ照合順序、または大文字と小文字を区別する照合順序の場合、カラムに値を割り当てるときに、大文字/小文字が考慮されます。

ENUM カラムに数字を格納すると、その数字は指定可能な値のインデックスとして扱われ、格納された値がそのインデックスを持つ列挙メンバーとなります。(ただし、これはすべての入力を文字列として扱う LOAD DATA では機能 しません。)数値が引用符で囲まれている場合、列挙値のリストに一致する文字列がなければ、そのままインデックスとして解釈されます。これらの理由により、ENUM カラムを数字のように見える列挙値で定義することは、混乱を招きやすくなるのでお勧めできません。たとえば、次のカラムには '0''1'、および '2' の文字列値を持つ列挙メンバーが指定されていますが、数値インデックス値は 12、および 3 です。

numbers ENUM('0','1','2')

2 を格納すると、それはインデックス値と解釈され、'1' (インデックス 2 の値) になります。'2' を格納すると、それは列挙値と一致するので、'2' として格納されます。'3' を格納すると、どの列挙値とも一致しないのでインデックスとして扱われ、'2' (インデックス 3 の値) になります。

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');mysql> SELECT * FROM t;+---------+
| numbers |
+---------+
| 1 |
| 2 |
| 2 |
+---------+

ENUM カラムのすべての指定可能な値を特定するには、SHOW COLUMNS FROM tbl_name LIKE 'enum_col' を使用して、出力の Type カラム内の ENUM 定義を構文解析します。

C API では、ENUM 値は文字列として返されます。結果セットのメタデータを使用してこれらをほかの文字列から区別する方法については、セクション23.7.5「C API データ構造」を参照してください。

空または NULL の列挙値

以下のような特定の状況下では、列挙値は、空の文字列 ('') や NULL になることもあります。

  • ENUM に無効な値 (つまり、許可された値のリストに存在しない文字列) を挿入すると、特殊なエラー値として空の文字列が代わりに挿入されます。この文字列は、この文字列に 0 の数値が含まれていることで、通常の空の文字列と区別できます。列挙値の数値インデックスの詳細は、Index Values for Enumeration Literalsを参照してください。

    厳密な SQL モードが有効な場合は、無効な ENUM 値を挿入しようとするとエラーが発生します。

  • ENUM カラムが NULL を許可するように宣言されている場合、NULL 値は、そのカラムに対して有効な値であり、デフォルト値は NULL になります。ENUM カラムが NOT NULL として宣言されている場合、デフォルト値は許可されている値のリストの最初の要素になります。

列挙のソート

ENUM 値は、インデックス番号に基づいてソートされますが、この数値は、カラム仕様で列挙メンバーがリストされていた順序に従います。たとえば、ENUM('b', 'a') の場合、'b''a' の前にソートされます。空の文字列は空ではない文字列の前にソートされ、NULL 値はその他のすべての列挙値の前にソートされます。

ENUM カラムで ORDER BY 句の使用時に予想外の結果になることを回避するには、次のいずれかの手法を使用します。

  • アルファベット順で ENUM リストを指定します。

  • ORDER BY CAST(col AS CHAR) または ORDER BY CONCAT(col) をコード化することにより、カラムがインデックス番号ではなく、辞書順でソートされることを確認します。

列挙の制限

列挙値は、文字列値に評価されるものであっても、式にはできません。

たとえば、次の CREATE TABLE ステートメントは、CONCAT 関数を列挙値の構築に使用できないので、機能しません

CREATE TABLE sizes ( size ENUM('small', CONCAT('med','ium'), 'large')
);

ユーザー変数を列挙値として使用することもできません。次のステートメントのペアは機能しません

SET @mysize = 'medium';
CREATE TABLE sizes ( size ENUM('small', @mysize, 'large')
);

数字を列挙値として使用しないことを強くお勧めします。これは、適切な TINYINT または SMALLINT 型よりもストレージを節約するわけでもなく、ENUM 値を間違って引用符で囲んだ場合には、文字列とベースになる数値とを混同しやすくなる (同じでない場合もあります) からです。数字を列挙値として使用する場合は、必ず引用符で囲んでください。引用符を省略した場合は、その数字はインデックスと見なされます。Handling of Enumeration Literalsを参照して、引用符で囲まれた数字でも間違って数字のインデックス値として使用されるか場合について確認してください。

定義の中に重複した値が含まれていると、警告 (厳密な SQL モードが有効になっている場合はエラー) が発生します。

11.4.5 SET 型

SET は、ゼロ以上の値を取ることができる文字列オブジェクトであり、それぞれの値は、テーブルの作成時に指定された許可される値のリストから選択する必要があります。SET カラム値が複数のセットメンバーで構成される場合は、各メンバーはカンマ (,) で区切って指定されます。このため、SET メンバーの値自体にはカンマを含めないでください。

たとえば、SET('one', 'two') NOT NULL として指定したカラムは、次に示す値のいずれかを取ります。

''
'one'
'two'
'one,two'

SET カラムには最大 64 個の個別のメンバーを含めることができます。テーブルには、グループと見なされる ENUM および SET カラムの中の一意の要素リスト定義を、255 個以下を含めることができます。この制限の詳細は、セクションD.10.5「.frm ファイル構造により課せられる制限」を参照してください。

定義の中に重複した値が含まれていると、警告 (厳密な SQL モードが有効になっている場合はエラー) が発生します。

テーブルが作成されるときに、テーブル定義内の SET メンバー値から末尾のスペースが自動的に削除されます。

SET カラムに格納された値は、取得されるときに、カラム定義で使用されていた大文字/小文字で表示されます。SET カラムには、文字セットと照合順序を割り当てることができます。バイナリ照合順序、または大文字と小文字を区別する照合順序の場合、カラムに値を割り当てるときに、大文字/小文字が考慮されます。

MySQL は、最初のセットメンバーに対応する格納値の低位ビットを使用して SET 値を数値で格納します。SET 値を数値コンテキストで取得した場合、その取得された値には、カラム値を構成するセットメンバーに対応するビットセットが含まれます。たとえば、次のように SET カラムから数値を取得できます。

mysql> SELECT set_col+0 FROM tbl_name;

メンバーが SET カラムに格納されると、その数字のバイナリ表現に設定されているビットからカラム値のセットメンバーが特定されます。カラムが SET('a','b','c','d') として指定されている場合、セットメンバーは次の 10 進値と 2 進値を持ちます。

SET メンバー10 進値2 進値
'a'10001
'b'20010
'c'40100
'd'81000

このカラムに 9 の値を割り当てた場合、2 進数では 1001 となるため、SET 値の最初と 4 番目のメンバーである 'a''d' が選択され、結果として得られる値は 'a,d' になります。

1 つ以上の SET 要素を含む値には、値を挿入するときに要素がどの順序でリストされているかは関係ありません。また、所定の要素が値の中で何回リストされているかも関係ありません。あとから値を取得するときに、値内のそれぞれの要素は、テーブル作成時に指定された順序に従って、一度表示されます。たとえば、カラムが SET('a','b','c','d') と指定されているとします。

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

'a,d''d,a''a,d,d''a,d,a'、および 'd,a,d' の値を挿入した場合、

mysql> INSERT INTO myset (col) VALUES -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

これらの値はすべて、取得されるときに 'a,d' と表示されます。

mysql> SELECT col FROM myset;+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)

サポートされていない値に SET カラムを設定すると、その値は無視され警告が表示されます。

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS;+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)
mysql> SELECT col FROM myset;+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
6 rows in set (0.01 sec)

厳密な SQL モードが有効な場合、無効な SET 値を挿入しようとするとエラーが発生します。

SET 値は数値でソートされます。NULL 値は非 NULLSET 値の前にソートされます。

数値引数を取る SUM()AVG() などの関数は、必要に応じて引数を数値にキャストします。SET 値の場合は、キャスト操作によって数値が使用されます。

通常は、FIND_IN_SET() 関数か LIKE 演算子を使用して SET 値を検索します。

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

最初のステートメントは set_colvalue セットメンバーを含む行を見つけます。2 番目も似ていますが、同じではありません。ほかのセットメンバーの部分文字列としてであっても、set_colvalue を含む行を見つけます。

次のステートメントも使用できます。

mysql> SELECT * FROM tbl_name WHERE set_col & 1;mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

これらのうち最初のステートメントが最初のセットメンバーを含む値を探します。2 番目のステートメントは正確に一致する値を探します。2 番目の型は慎重に比較してください。セット値を 'val1,val2' と比較すると、値を 'val2,val1' と比較した場合とは異なる結果が返されます。カラム定義にリストされている順序どおりに値を指定する必要があります。

SET カラムの指定可能な値をすべて特定するには、SHOW COLUMNS FROM tbl_name LIKE set_col を使用して、出力の Type カラム内の SET 定義を構文解析します。

C API では、SET 値は文字列として返されます。結果セットのメタデータを使用してこれらをほかの文字列から区別する方法については、セクション23.7.5「C API データ構造」を参照してください。

11.5 空間データの拡張

Open Geospatial Consortium (OGC) は、空間データを管理するあらゆる種類のアプリケーションで役立つ、公的に利用可能な概念的ソリューションの開発に携わっている 250 以上の企業、機関、および大学の国際的なコンソーシアムです。

空間データをサポートするように SQL RDBMS を拡張するための複数の概念的な方法を提案したドキュメントとして、Open Geospatial Consortium から「OpenGIS® Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option」が発行されています。この仕様書は、OGC Web サイト (http://www.opengeospatial.org/standards/sfs) から入手できます。

MySQL は、OGC の仕様書に従って、幾何型を含む SQL 環境のサブセットとして空間拡張を実装しています。この用語は、一連の幾何型で拡張された SQL 環境を意味しています。幾何値を含む SQL カラムは、幾何型のカラムとして実装されています。仕様書では、一連の SQL 幾何型のほか、幾何値を作成し分析するためにこれらの型に対して行われる関数について説明しています。

MySQL 空間拡張により、地理的特性の生成、ストレージ、および分析が可能になります。

  • 空間値を表すデータ型

  • 空間値を操作する関数

  • 空間カラムへのアクセス時間を改善するための空間インデックス設定

データ型と関数は、MyISAMInnoDBNDB、および ARCHIVE テーブルで使用できます。空間カラムのインデックス設定については、MyISAM は、SPATIAL インデックスと非 SPATIAL インデックスの両方をサポートします。その他のストレージエンジンは、セクション13.1.13「CREATE INDEX 構文」で説明しているように、非 SPATIAL インデックスをサポートします。

地理的特性とは、位置を特定できる世界中のあらゆるもののことです。特性は次のいずれかになります。

  • 実体。山、池、都市など。

  • 領域。町の区域や熱帯地域など。

  • 定義可能な位置。2 つの道路が交差する特定の場所となる交差点など。

ドキュメントによっては、地理空間特性という用語を地理的特性の意味で使用している場合もあります。

幾何図形も地理的特性を表す用語です。幾何図形という用語はもともと、地球の測量を意味していました。地図作成者が世界のマッピングに使用する幾何特性を指す別の意味は、地図作成の分野からのものです。

ここでの説明では、地理的特性地理空間特性特性幾何図形の用語をシノニムと見なします。もっともよく使用される用語は幾何図形であり、位置を特定できる世界中のあらゆるものを表す地点または地点の集約として定義されています。

次の資料では次のトピックを取り上げます。

  • MySQL モデルに実装された空間データ型

  • OpenGIS 幾何モデルでの空間拡張の基本

  • 空間データを表現するためのデータ形式

  • MySQL で空間データを使用する方法

  • 空間データのインデックスの使用方法

  • OpenGIS 仕様と MySQL 実装との差異

空間データを演算する関数の詳細は、セクション12.15「空間分析関数」を参照してください。

MySQL の GIS に対する適合性と互換性

MySQL は次の GIS 機能を実装していません。

  • 追加のメタデータビュー

    OpenGIS の仕様書では追加メタデータビューがいくつか提案されています。たとえば、GEOMETRY_COLUMNS という名前のシステムビューには、データベース内の幾何カラムごとに 1 行ずつ、幾何カラムの記述が含まれます。

  • LineString および MultiLineString での OpenGIS 関数 Length() は、MySQL で GLength() として呼び出す必要があります。

    これは、文字列値の長さを計算する既存の SQL 関数 Length() が存在していますが、この関数がテキスト、空間のどちらのコンテキストで呼び出されたのかを判定できない場合があるからです。

追加のリソース

  • 空間データをサポートするように SQL RDBMS を拡張するための複数の概念的な方法を提案したドキュメントとして、Open Geospatial Consortium から「OpenGIS® Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option」が発行されています。Open Geospatial Consortium (OGC) は、http://www.opengeospatial.org/ で Web サイトを管理しています。仕様書は http://www.opengeospatial.org/standards/sfs で入手できます。ここでの資料に関連した追加情報が用意されています。

  • MySQL に対する空間拡張の使用について質問や関心がある場合は、GIS フォーラム (https://forums.mysql.com/list.php?23) で議論できます。

11.5.1 空間データ型

MySQL には OpenGIS クラスに対応するデータ型が用意されています。これらのデータ型の中には、次のように単一の幾何値を格納するものがあります。

  • GEOMETRY

  • POINT

  • LINESTRING

  • POLYGON

GEOMETRY にはどの型の幾何値でも格納できます。その他の単一値型 (POINTLINESTRING、および POLYGON) では、特定の幾何型に値が制限されます。

次に示すその他のデータ型には、値のコレクションが格納されます。

  • MULTIPOINT

  • MULTILINESTRING

  • MULTIPOLYGON

  • GEOMETRYCOLLECTION

GEOMETRYCOLLECTION には、任意の型のオブジェクトのコレクションを格納できます。その他のコレクション型 (MULTIPOINTMULTILINESTRINGMULTIPOLYGON、および GEOMETRYCOLLECTION) では、コレクションのメンバーは、特定の幾何型を持つメンバーに制限されます。

MySQL 空間データは、セクション11.5.2「OpenGIS 幾何モデル」で説明しているように、OpenGIS 幾何モデルに基づいています。MySQL で空間データ型を使用する方法を示した例については、セクション11.5.3「空間データの使用」を参照してください。

11.5.2 OpenGIS 幾何モデル

OGC の幾何型を含む SQL 環境で提案されている一連の幾何型は、OpenGIS 幾何モデルに基づいています。このモデルの各幾何オブジェクトには、次のような一般的なプロパティーがあります。

  • オブジェクトが定義されている座標空間を記述する空間参照システムに関連付けられています。

  • 特定の幾何クラスに属しています。

11.5.2.1 幾何クラスの階層

幾何クラスの階層は次のように定義されています。

  • Geometry (インスタンス化不可能)

    • Point (インスタンス化可能)

    • Curve (インスタンス化不可能)

      • LineString (インスタンス化可能)

        • Line

        • LinearRing

    • Surface (インスタンス化不可能)

      • Polygon (インスタンス化可能)

    • GeometryCollection (インスタンス化可能)

      • MultiPoint (インスタンス化可能)

      • MultiCurve (インスタンス化不可能)

        • MultiLineString (インスタンス化可能)

      • MultiSurface (インスタンス化不可能)

        • MultiPolygon (インスタンス化可能)

インスタンス化不可能なクラスのオブジェクトは作成できません。インスタンス化可能なクラスのオブジェクトは作成できます。どのクラスもプロパティーを持ちますが、インスタンス化可能なクラスはさらに表明 (有効なクラスインスタンスを定義するルール) も持つことができます。

Geometry は基本クラスです。これは抽象クラスです。Geometry のインスタンス化可能なサブクラスは、2 次元座標空間内に存在する 0 次元、1 次元、および 2 次元の幾何オブジェクトに限定されます。インスタンス化可能な幾何クラスはすべて、幾何クラスの有効なインスタンスが位相的に閉じている (つまり、定義されたすべての幾何図形に境界が含まれる) ように定義されています。

Geometry 基本クラスには、PointCurveSurface、および GeometryCollection のサブクラスがあります。

  • Point は 0 次元のオブジェクトを表します。

  • Curve は 1 次元のオブジェクトを表し、そのサブクラス LineString は、LineLinearRing をサブクラスに持ちます。

  • Surface は 2 次元のオブジェクト用に設計されたもので、Polygon をサブクラスに持ちます。

  • GeometryCollection には MultiPointMultiLineStringMultiPolygon という 0、1、2 次元の特殊化コレクションクラスが用意されており、それぞれ PointsLineStringsPolygons のコレクションに対応する幾何図形をモデル化しています。MultiCurveMultiSurface は、このコレクションインタフェースを汎化して Curves および Surfaces を処理できるよう抽象スーパークラスとして導入されたものです。

GeometryCurveSurfaceMultiCurve、および MultiSurface は、インスタンス化不可能なクラスとして定義されています。これらはサブクラスに共通する一連のメソッドを定義しており、今後の拡張に含められます。

PointLineStringPolygonGeometryCollectionMultiPointMultiLineString、および MultiPolygon はインスタンス化可能なクラスです。

11.5.2.2 Geometry クラス

Geometry は階層のルートクラスです。これはインスタンス化不可能なクラスですが、次のリストに説明しているように、Geometry サブクラスのいずれかから作成したすべての幾何値に共通である多数のプロパティーがあります。個々のサブクラスも独自のプロパティーを備えていますが、これについては後述します。

Geometry のプロパティー

幾何値に含まれるプロパティーは次のとおりです。

  • その。各幾何図形は、階層内のインスタンス化可能クラスのいずれかに属します。

  • その SRID、つまり空間参照識別子。この値は、幾何図形に関連付けられた、その幾何オブジェクトが定義されている座標空間を記述する空間参照システムを識別します。

    MySQL の SRID 値は、幾何値に関連付けられた整数です。すべての計算はユークリッド (平面) 幾何学を前提にして実行されます。使用可能な SRID の最大値は 232−1 です。より大きな値が指定されると、低位の 32 ビットだけが使用されます。

  • 空間参照システムでの座標は、倍精度 (8 バイト) 数として表現されます。空でない幾何図形には必ず、(X,Y) 座標ペアが少なくとも 1 つ含まれます。空の幾何図形には座標は含まれません。

    座標は SRID に対する相対的なものです。たとえば、異なる座標系では、オブジェクトの座標が同じ場合でも、2 つのオブジェクト間の距離が異なることがあります。これは、平面座標系での距離と地球を中心とした系 (地球表面の座標) の距離は異なるためです。

  • 内部境界外部

    幾何図形は必ず、ある位置の領域を占有します。幾何図形の外部とは、その幾何図形によって占有されていないすべての領域のことです。内部とは、その幾何図形によって占有されている領域のことです。境界とは、幾何図形の内部と外部が接する部分のことです。

  • その MBR (最小外接矩形)、または包絡線。これは範囲を規定する幾何図形であり、次のように最小および最大の (X,Y) 座標から形成されます。

    ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
  • 値が単純である単純でないのいずれであるか。LineStringMultiPointMultiLineString の型の幾何値は「単純である」、「単純でない」のいずれかになります。「単純である」、「単純でない」のいずれであるかの表明は、型ごとに決定されます。

  • 値が閉じている閉じていないのいずれであるか。LineStringMultiString の型の幾何値は「閉じている」「閉じていない」のいずれかになります。「閉じている」、「閉じていない」のいずれであるかの表明は、型ごとに決定されます。

  • 値が空である空でないのいずれであるか。点を 1 つも含まない幾何図形は空です。空の幾何図形の外部、内部、および境界は定義されていません (つまり、それらは NULL 値で表されます)。空の幾何図形は、常に単純で面積が 0 になるように定義されています。

  • その次元。幾何図形には −1、0、1、または 2 の次元があります。

    • −1 は、空の幾何図形を表します。

    • 0 は長さも面積も持たない幾何図形を表します。

    • 1 は、長さがゼロ以外で面積がゼロの幾何図形を表します。

    • 2 は、面積がゼロ以外の幾何図形を表します。

    Point オブジェクトの次元は 0 です。LineString オブジェクトの次元は 1 です。Polygon オブジェクトの次元は 2 です。MultiPointMultiLineString、および MultiPolygon オブジェクトの次元は、構成要素の次元と同じになります。

11.5.2.3 Point クラス

Point は、座標空間内の単一の位置を表す幾何図形です。

Point の例

  • 多数の都市を含む大規模な世界地図を想像してください。Point オブジェクトは各都市を表すことができます。

  • 市内地図で、Point オブジェクトはバス停を表すことができます。

Point のプロパティー

  • X 座標値。

  • Y 座標値。

  • Point は 0 次元の幾何図形として定義されています。

  • Point の境界は空セットになります。

11.5.2.4 Curve クラス

Curve は 1 次元の幾何図形であり、通常は一連の点で表されます。点の間の補間方法は、Curve の特定のサブクラスで定義されています。Curve はインスタンス化不可能なクラスです。

Curve のプロパティー

  • Curve はその点の座標を持ちます。

  • Curve は 1 次元の幾何図形として定義されています。

  • 同じ点を 2 度通過しなければ、Curve は単純です。

  • 始点と終点が等しい場合、Curve は閉じています。

  • 閉じた Curve の境界は、空になります。

  • 閉じていない Curve の境界は、その 2 つの端点から構成されます。

  • 単純で閉じた Curve としては、LinearRing が挙げられます。

11.5.2.5 LineString クラス

LineString は、点の間を直線で補間した Curve です。

LineString の例

  • 世界地図で、LineString オブジェクトは河川を表すことができます。

  • 市内地図で、LineString オブジェクトは通りを表すことができます。

LineString のプロパティー

  • LineString は、隣り合う 1 対の点で定義される各線分の座標を持ちます。

  • ちょうど 2 つの点から構成されている場合、LineStringLine になります。

  • 閉じていて、かつ単純である場合は、LineStringLinearRing になります。

11.5.2.6 Surface クラス

Surface は 2 次元の幾何図形です。これはインスタンス化不可能なクラスです。その唯一のインスタンス化可能なサブクラスは、Polygon です。

Surface のプロパティー

  • Surface は 2 次元の幾何図形として定義されています。

  • OpenGIS 仕様では、単純な Surface が、1 個の外側の境界と 0 個以上の内側の境界に関連付けられた単一のパッチからなる幾何図形として定義されています。

  • 単純な Surface の境界は、その外側と内側の境界に対応する一連の閉じた曲線になります。

11.5.2.7 Polygon クラス

Polygon は、多辺の幾何図形を表す平面 Surface です。これは 1 個の外側の境界と 0 個以上の内側の境界で定義されますが、それらの内側の各境界によって Polygon 内の 1 個の穴が定義されます。

Polygon の例

  • 地域マップで、Polygon オブジェクトは森林や区域などを表すことができます。

Polygon の表明

  • Polygon の境界は、外側と内側の境界を構成する一連の LinearRing オブジェクト (つまり、単純かつ閉じた LineString オブジェクト) から構成されます。

  • Polygon のリングは交差しません。Polygon の境界に含まれるリングは、Point で交わりますが、接することしかできません。

  • Polygon には線分、突起、亀裂は含まれません。

  • Polygon は、連続した点集合からなる内部を持ちます。

  • Polygon は穴を持つことができます。穴のある Polygon の外部は、連続していません。それぞれの穴が、連続した 1 つの外部コンポーネントを定義します。

以上の表明により、Polygon は単純な幾何図形になります。

11.5.2.8 GeometryCollection クラス

GeometryCollection は、任意のクラスに属する 1 つ以上の幾何図形のコレクションとなる幾何図形です。

GeometryCollection の各要素の空間参照システム (つまり座標系) はすべて同じである必要があります。GeometryCollection の要素に関する制約はこれだけですが、後続の各セクションで説明する GeometryCollection のサブクラスでは、メンバーシップに関する制限が課される可能性があります。これらの制限は次の情報に基づくことがあります。

  • 要素の型 (たとえば、MultiPoint に格納できるのは Point 要素だけです)

  • 次元

  • 要素間の空間的な重なり具合に関する制約

11.5.2.9 MultiPoint クラス

MultiPoint は、Point 要素から構成される幾何図形コレクションです。点の接続や順序付けは一切行われません。

MultiPoint の例

  • 世界地図で、MultiPoint は一連の小さな島々を表すことができます。

  • 市内地図で、MultiPoint はチケットオフィスの系列店を表すことができます。

MultiPoint のプロパティー

  • MultiPoint は 0 次元の幾何図形です。

  • この 2 つの Point の値 (座標値) が等しくない場合は、MultiPoint は単純になります。

  • MultiPoint の境界は空セットになります。

11.5.2.10 MultiCurve クラス

MultiCurve は、Curve 要素から構成される幾何図形コレクションです。MultiCurve はインスタンス化不可能なクラスです。

MultiCurve のプロパティー

  • MultiCurve は 1 次元の幾何図形です。

  • MultiCurve が単純になるのは、そのすべての要素が単純である場合だけです。2 つの要素の唯一の交点は、両方の要素の境界上にある点になります。

  • MultiCurve の境界を取得するには、mod 2 union ルール (odd-even ルールとも呼ばれます) を適用します。ある点が MultiCurve の境界に含まれるのは、その点が、奇数個の MultiCurve 要素の境界に含まれている場合です。

  • すべての要素が閉じている場合、MultiCurve は閉じています。

  • 閉じた MultiCurve の境界は、常に空になります。

11.5.2.11 MultiLineString クラス

MultiLineString は、LineString 要素から構成される MultiCurve 幾何図形コレクションです。

MultiLineString の例

  • 地域マップで、MultiLineString は河川系や高速道路システムを表すことができます。

11.5.2.12 MultiSurface クラス

MultiSurface は、面要素から構成される幾何図形コレクションです。MultiSurface はインスタンス化不可能なクラスです。その唯一のインスタンス化可能なサブクラスは、MultiPolygon です。

MultiSurface の表明

  • MultiSurface の 2 つの面の内部が交差することはありません。

  • MultiSurface の 2 つの要素の境界が無限個の点で交わることはありません。

11.5.2.13 MultiPolygon クラス

MultiPolygon は、Polygon 要素から構成される MultiSurface オブジェクトです。

MultiPolygon の例

  • 地域マップで、MultiPolygon は湖の系列を表すことができます。

MultiPolygon の表明

  • MultiPolygon のどの 2 つの Polygon 要素も、交差する内部を持つことはありません。

  • MultiPolygon のどの 2 つの Polygon 要素も、交差したり (交差は 1 つ前の表明でも禁止されています)、無限個の点で接したりしません。

  • MultiPolygon にカットライン、突起、亀裂を含めることはできません。MultiPolygon は通常の閉じた点集合です。

  • 複数の Polygon を含む MultiPolygon は、連続していない内部を持ちます。MultiPolygon の連続する内部コンポーネントの個数は、MultiPolygon 内の Polygon 値の数と等しくなります。

MultiPolygon のプロパティー

  • MultiPolygon は 2 次元の幾何図形です。

  • MultiPolygon の境界は、その Polygon 要素の境界に対応する一連の閉じた曲線 (LineString 値) になります。

  • MultiPolygon の境界に含まれる各 Curve は、どれか 1 つの Polygon 要素の境界にのみ含まれます。

  • Polygon 要素の境界に含まれる Curve は必ず、MultiPolygon の境界にも含まれます。

11.5.3 空間データの使用

このセクションでは、空間データ型カラムを含むテーブルの作成方法と、空間情報の操作方法について説明します。

11.5.3.1 サポートされる空間データ形式

クエリーで幾何オブジェクトを表現するために、次の 2 つの標準空間データ形式が使用されます。

  • WKT (Well-Known Text) 形式

  • WKB (Well-Known Binary) 形式

MySQL の内部では、WKT、WKB のどちらの形式とも異なる形式で幾何値が格納されます。

異なるデータ形式間の変換に使用できる関数があります。セクション12.15.6「幾何形式変換関数」を参照してください。

11.5.3.1.1 WKT (Well-Known Text) 形式

幾何値の WKT (Well-Known Text) 表現は、ASCII 形式の幾何データを交換するために設計されています。OpenGIS 仕様書には、WKT 値を書き込むための公式の運用ルールを指定するバッカス-ナウア記法が用意されています (セクション11.5「空間データの拡張」を参照してください)。

幾何オブジェクトの WKT 表現の例:

  • Point:

    POINT(15 20)

    点の座標は、区切り用のカンマなしに指定されます。これは、座標間にカンマを必要とする SQL Point() 関数の構文とは異なります。特定の空間演算のコンテキストに適した構文を慎重に使用してください。たとえば、次のステートメントはどちらも、Point オブジェクトから X 座標を抽出します。最初の場合は、Point() 関数を直接使用してオブジェクトを生成します。2 番目の場合は、GeomFromText()Point に変換された WKT 表現を使用します。

    mysql> SELECT X(POINT(15, 20));+------------------+
    | X(POINT(15, 20)) |
    +------------------+
    | 15 |
    +------------------+
    mysql> SELECT X(GeomFromText('POINT(15 20)'));+---------------------------------+
    | X(GeomFromText('POINT(15 20)')) |
    +---------------------------------+
    | 15 |
    +---------------------------------+
  • 4 つの点を含む LineString:

    LINESTRING(0 0, 10 10, 20 25, 50 60)

    点の座標のペアはカンマで区切られます。

  • 外側のリングと内側のリングを 1 つずつ含む Polygon:

    POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
  • 3 つの Point 値を含む MultiPoint:

    MULTIPOINT(0 0, 20 20, 60 60)
  • 2 つの LineString 値を含む MultiLineString:

    MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
  • 2 つの Polygon 値を含む MultiPolygon:

    MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
  • 2 つの Point 値と 1 つの LineString から構成された GeometryCollection:

    GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
11.5.3.1.2 WKB (Well-Known Binary) 形式

幾何値の WKB (Well-Known Binary) 表現は、幾何 WKB 情報を含む BLOB 値によって表現されたバイナリストリームとして幾何データを交換するために使用されます。この形式は、OpenGIS 仕様によって定義されています (セクション11.5「空間データの拡張」を参照してください)。これはまた、ISO の SQL/MM Part 3: Spatial 標準でも定義されています。

WKB は、1 バイトの符号なしの整数、4 バイトの符号なしの整数、および 8 バイトの倍精度数 (IEEE 754 形式) を使用します。1 バイトは 8 ビットです。

たとえば、POINT(1 1) に対応する WKB 値は、それぞれ 2 つの 16 進数で表された次の 21 バイトのシーケンスから構成されます。

0101000000000000000000F03F000000000000F03F

このシーケンスは次のコンポーネントから構成されます。

Byte order: 01
WKB type: 01000000
X coordinate: 000000000000F03F
Y coordinate: 000000000000F03F

各コンポーネントが表す内容は次のとおりです。

  • バイト順序は 1 または 0 のどちらかで、リトルエンディアンまたはビッグエンディアンストレージを示します。リトルエンディアンバイト順序、ビッグエンディアンバイト順序はそれぞれ NDR (Network Data Representation)、XDR (External Data Representation) とも呼ばれます。

  • WKB 型は幾何型を示すコードです。1 から 7 の値は PointLineStringPolygonMultiPointMultiLineStringMultiPolygon、および GeometryCollection を示します。

  • Point 値には X 座標と Y 座標が含まれますが、それぞれ倍精度値として表現されます。

さらに複雑な幾何値の WKB 値は、OpenGIS 仕様書に詳しく記されているように、より複雑なデータ構造になります。

11.5.3.2 空間カラムの作成

MySQL には、CREATE TABLEALTER TABLE を使用する方法など、幾何型の空間カラムを作成するための標準的な方法が用意されています。空間カラムは、MyISAMInnoDBNDB、および ARCHIVE テーブルでサポートされています。セクション11.5.3.6「空間インデックスの作成」の空間インデックスに関するノートも参照してください。

  • 空間カラムを含むテーブルを作成するには、CREATE TABLE ステートメントを使用します。

    CREATE TABLE geom (g GEOMETRY);
  • 既存のテーブルに対して空間カラムの追加や削除を行うには、ALTER TABLE ステートメントを使用します。

    ALTER TABLE geom ADD pt POINT;
    ALTER TABLE geom DROP pt;

11.5.3.3 空間カラムへのデータ移入

空間カラムを作成し終わったら、空間データを移入できます。

値は内部幾何形式で格納する必要がありますが、WKT (Well-Known Text)、WKB (Well-Known Binary) のいずれの形式からでも、その形式に値を変換できます。次の例は、WKT 値を内部幾何形式に変換することによって、幾何値をテーブルに挿入する方法を示しています。

  • 次のように INSERT ステートメント内で直接変換を実行します。

    INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
    SET @g = 'POINT(1 1)';
    INSERT INTO geom VALUES (GeomFromText(@g));
  • 次のように INSERT の前に変換を実行します。

    SET @g = GeomFromText('POINT(1 1)');
    INSERT INTO geom VALUES (@g);

次の例では、より複雑な幾何図形をテーブルに挿入しています。

SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (GeomFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (GeomFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (GeomFromText(@g));

前述の例では、GeomFromText() を使用して幾何値を作成しています。次のように型に固有の関数を使用することもできます。

SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (PointFromText(@g));
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (LineStringFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (PolygonFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (GeomCollFromText(@g));

幾何値の WKB 表現を使用するクライアントアプリケーションプログラムが、クエリーで正しく作成された WKB のサーバーへの送信を担います。この要件を満たす方法は複数あります。例:

  • 次のように、16 進リテラル構文を使用して、POINT(1 1) 値を挿入します。

    mysql> INSERT INTO geom VALUES -> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));
  • ODBC アプリケーションは、BLOB 型の引数を使用して WKB 表現をプレースホルダにバインドし、WKB 表現を送信できます。

    INSERT INTO geom VALUES (GeomFromWKB(?))

    ほかのプログラミングインタフェースも似たようなプレースホルダメカニズムをサポートしている可能性があります。

  • C プログラムでは、mysql_real_escape_string() を使用してバイナリ値をエスケープし、その結果をクエリー文字列に含めてサーバーに送信できます。セクション23.7.7.54「mysql_real_escape_string()」を参照してください。

11.5.3.4 空間データのフェッチ

テーブルに格納された幾何値は内部形式でフェッチできます。WKT 形式から WKB 形式に変換することもできます。

  • 内部形式での空間データのフェッチ:

    内部形式で幾何値をフェッチする方法は、テーブル間でデータの転送を行う場合に便利です。

    CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;
  • WKT 形式での空間データのフェッチ:

    AsText() 関数は幾何図形を内部形式から WKT 文字列に変換します。

    SELECT AsText(g) FROM geom;
  • WKB 形式での空間データのフェッチ:

    AsBinary() 関数は幾何図形を内部形式から WKB 値を含む BLOB に変換します。

    SELECT AsBinary(g) FROM geom;

11.5.3.5 空間分析の最適化

MyISAM テーブルの場合、空間データを含むカラムでの検索操作は、SPATIAL インデックスを使用して最適化できます。もっとも典型的な操作は次のとおりです。

  • 指定された点を含むすべてのオブジェクトを検索する点クエリー

  • 所定の領域と重なるすべてのオブジェクトを検索する領域クエリー

MySQL では、2 次分割 R ツリーを使用して空間カラムの SPATIAL インデックスが実装されています。SPATIAL インデックスは、幾何図形の最小外接矩形 (MBR) を使用して構築されます。大部分の幾何図形では、MBR はその幾何図形を囲む最小矩形となります。水平または垂直方向のライン文字列では、MBR は矩形からライン文字列に縮退します。点の場合、MBR は矩形から点に縮退します。

空間カラムに通常のインデックスを作成することも可能です。非 SPATIAL インデックスでは、POINT カラムを除くすべての空間カラムでプリフィクスを宣言する必要があります。

MyISAM は、SPATIAL インデックスと非 SPATIAL インデックスの両方をサポートします。その他のストレージエンジンはセクション13.1.13「CREATE INDEX 構文」で説明しているように、非 SPATIAL インデックスをサポートします。

11.5.3.6 空間インデックスの作成

MyISAM テーブルでは、MySQL は、通常のインデックスを作成するための似た構文を使用するが、SPATIAL キーワードを使用して、空間インデックスを作成できます。空間インデックスのカラムは、NOT NULL と宣言する必要があります。次の各例では空間インデックスの作成方法を示します。

  • CREATE TABLE を使用する場合:

    CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=MyISAM;
  • ALTER TABLE を使用する場合:

    ALTER TABLE geom ADD SPATIAL INDEX(g);
  • CREATE INDEX を使用する場合:

    CREATE SPATIAL INDEX sp_index ON geom (g);

SPATIAL INDEX は R ツリーインデックスを作成します。空間カラムの非空間インデックスをサポートするストレージエンジンでは、B ツリーインデックスが作成されます。空間値に対する B ツリーインデックスは、正確な値の検索に役立ちますが、範囲スキャンには役立ちません。

空間カラムのインデックス作成の詳細については、セクション13.1.13「CREATE INDEX 構文」を参照してください。

空間インデックスを削除するには、次のように ALTER TABLE または DROP INDEX を使用します。

  • ALTER TABLE を使用する場合:

    ALTER TABLE geom DROP INDEX g;
  • DROP INDEX を使用する場合:

    DROP INDEX sp_index ON geom;

例: テーブル geom に 32,000 件を超える幾何図形が含まれていて、それらの図形が型 GEOMETRY のカラム g に格納されているものとします。またこのテーブルには、オブジェクト ID の値を格納するための AUTO_INCREMENT カラム fid も含まれています。

mysql> DESCRIBE geom;+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| fid | int(11) | | PRI | NULL | auto_increment |
| g | geometry | | | | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM geom;+----------+
| count(*) |
+----------+
| 32376 |
+----------+
1 row in set (0.00 sec)

カラム g に空間インデックスを追加するには、次のステートメントを使用します。

mysql> ALTER TABLE geom ADD SPATIAL INDEX(g) ENGINE=MyISAM;Query OK, 32376 rows affected (4.05 sec)
Records: 32376 Duplicates: 0 Warnings: 0

11.5.3.7 空間インデックスの使用

オプティマイザは、WHERE 句で MBRContains()MBRWithin() などの関数が使用されているクエリーの検索に、使用可能な空間インデックスを含めることができるかどうかを調べます。次のクエリーは、所定の矩形に含まれるすべてのオブジェクトを検索します。

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> SELECT fid,AsText(g) FROM geom WHERE -> MBRContains(GeomFromText(@poly),g);+-----+---------------------------------------------------------------+
| fid | AsText(g) |
+-----+---------------------------------------------------------------+
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.00 sec)

このクエリーがどのように実行されているのかを、EXPLAIN を使用して確認します。

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> EXPLAIN SELECT fid,AsText(g) FROM geom WHERE -> MBRContains(GeomFromText(@poly),g)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: geom type: range
possible_keys: g key: g key_len: 32 ref: NULL rows: 50 Extra: Using where
1 row in set (0.00 sec)

空間インデックスがないとどうなるのかを確認します。

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> EXPLAIN SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE -> MBRContains(GeomFromText(@poly),g)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: geom type: ALL
possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 32376 Extra: Using where
1 row in set (0.00 sec)

空間インデックスを使用せずに SELECT ステートメントを実行しても結果は同じになりますが、実行時間は 0.00 秒から 0.46 秒に増大します。

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> SELECT fid,AsText(g) FROM geom IGNORE INDEX (g) WHERE -> MBRContains(GeomFromText(@poly),g);+-----+---------------------------------------------------------------+
| fid | AsText(g) |
+-----+---------------------------------------------------------------+
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... |
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.46 sec)

11.6 データ型デフォルト値

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

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

カラム定義に明示的な DEFAULT 値が含まれていない場合、MySQL はデフォルト値を次のように特定します。

NULL を値として取ることができる場合は、そのカラムは明示的な DEFAULT NULL 句で定義ができます。

NULL を値として取ることができない場合は、MySQL は明示的な DEFAULT 句でカラムを定義できません。例外: カラムが PRIMARY KEY の一部として定義されているが、NOT NULL として明示的には定義されていない場合、MySQL はこれを NOT NULL カラムとして作成します (PRIMARY KEY カラムは NOT NULL である必要があるので) が、暗黙的なデフォルト値を使用してこれに DEFAULT 句も割り当てます。これを防止するには、すべての PRIMARY KEY カラムの定義に明示的な NOT NULL を含めてください。

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

  • 厳密な SQL モードを有効にした場合、トランザクションテーブルに対してエラーが発生し、ステートメントがロールバックされます。非トランザクションテーブルではエラーが起きるが、これが複数行ステートメントの 2 行目以降の行に対するエラーの場合、先行する行が挿入されています。

  • 厳密モードが有効でない場合、MySQL はカラムデータ型の暗黙的なデフォルト値にカラムを設定します。

テーブル t が次のように定義されるとします。

CREATE TABLE t (i INT NOT NULL);

この場合、i は明示的なデフォルトがないので、厳密モードでは次のそれぞれはステートメントはエラーになり、行は挿入されません。厳密モードを使用しない場合、3 番目のステートメントだけでエラーが発生します。最初の 2 つのステートメントでは暗黙のデフォルトが挿入されますが、DEFAULT(i) が値を生成できないので 3 番目のステートメントは失敗します。

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

セクション5.1.7「サーバー SQL モード」を参照してください。

所定のテーブルに対して、SHOW CREATE TABLE ステートメントを使用すると、どのカラムに明示的な DEFAULT 句があるかを確認できます。

暗黙的なデフォルトは次のように定義されます。

  • 数値型のデフォルトは 0 です。ただし、例外として AUTO_INCREMENT 属性で宣言された整数型または浮動小数点型のデフォルトは、そのシーケンスの次の値になります。

  • TIMESTAMP 以外の日付と時間型のデフォルトには、ゼロ値が適切です。explicit_defaults_for_timestamp システム変数が有効な場合、これは TIMESTAMP にも当てはまります (セクション5.1.4「サーバーシステム変数」を参照してください)。それ以外の場合、テーブルの最初の TIMESTAMP カラムのデフォルト値は現在の日付と時間になります。セクション11.3「日付と時間型」を参照してください。

  • ENUM ではない文字列型のデフォルト値は空の文字列です。ENUM のデフォルトは、最初の列挙値です。

整数カラム定義の中の SERIAL DEFAULT VALUENOT NULL AUTO_INCREMENT UNIQUE のエイリアスです。

11.7 データ型のストレージ要件

ディスク上のテーブルデータのストレージ要件は、複数の要因によって異なります。別々のストレージエンジンは異なる方法でデータ型を表し、ローデータを格納します。カラムか行全体のどちらかでテーブルデータを圧縮できますが、テーブルまたはカラムのストレージ要件の計算が複雑になります。

ディスク上のストレージレイアウトが違っていても、テーブル行に関する情報を通信および交換する内部 MySQL API は、すべてのストレージエンジンにわたって適用される一貫したデータ構造を使用します。

このセクションでは、データ型の固定サイズ表現を使用するストレージエンジンの内部形式およびサイズを含め、MySQL がサポートするデータ型ごとのストレージ要件に関するガイドラインおよび情報について説明します。情報はカテゴリまたはストレージエンジンごとに示します。

テーブルの内部表現の最大行サイズは 65,535 バイトであり、ストレージエンジンがこれ以上のサイズの行をサポートできる場合でもこのサイズになります。BLOB または TEXT カラムはこのサイズに 9 から 12 バイトしか関与しないので、これらのカラムはこのサイズに含まれません。BLOB および TEXT データについての情報は、行バッファーとは異なるメモリー領域に内部的に格納されます。それぞれのストレージエンジンは、対応する型の処理に使用する方法に従って異なる方法で、このデータの割り当ておよびストレージを扱います。詳細は、第15章「代替ストレージエンジンおよびセクションD.10.4「テーブルカラム数と行サイズの制限」を参照してください。

InnoDB テーブルのストレージ要件

InnoDB テーブルのストレージ要件の詳細は、セクション14.2.13.7「物理的な行構造」を参照してください。

NDBCLUSTER テーブルのストレージ要件

重要

NDB テーブルは、4 バイトアライメントを使用します。すべての NDB データストレージは、4 バイトの倍数で行われます。したがって、通常であれば 15 バイトを使用するカラム値は、NDB テーブルでは 16 バイトを必要とします。たとえば、NDB テーブルでは、TINYINTSMALLINTMEDIUMINT、および INTEGER (INT) カラム型はそれぞれ、アライメント係数により、レコードあたり 4 バイトのストレージが必要になります。

BIT(M) カラムは M ビットのストレージ領域を使用します。各 BIT カラムは 4 バイトアライメントが行われていませんが、NDB は、BIT カラムに必要な最初の 1 から 32 ビットに行あたり 4 バイト (32 ビット) を、33 から 64 ビットに別の 4 ビットを、というように予約します。

NULL 自体はストレージ領域を必要としませんが、NDB は、テーブル定義に NULL として定義されたカラム (最大 32 の NULL カラム) が含まれる場合、行あたり 4 バイトを予約します。(MySQL Cluster テーブルが 32 以上の NULL カラムから 64 の NULL カラムで定義されている場合、行あたり 8 バイトが予約されます。)

NDB ストレージエンジンを使用するすべてのテーブルで主キーが必要になります。主キーを定義していない場合、非表示の主キーが NDB によって作成されます。この非表示の主キーはテーブルレコードあたり 31 から 35 バイトを消費します。

ndb_size.pl Perl スクリプトを使用して、NDB ストレージ要件を評価します。これは、(MySQL Cluster ではなく) 現在の MySQL データベースに接続し、そのデータベースが NDB ストレージエンジンを使用した場合にどれだけの領域を必要とするかについてレポート作成します。詳細は、セクション18.4.25「ndb_size.pl — NDBCLUSTER サイズ要件エスティメータ」を参照してください。

数値型のストレージ要件

データ型必要なストレージ
TINYINT1 バイト
SMALLINT2 バイト
MEDIUMINT3 バイト
INTINTEGER4 バイト
BIGINT8 バイト
FLOAT(p)0 <= p <= 24 の場合は 4 バイト、25 <= p <= 53 の場合は 8 バイト
FLOAT4 バイト
DOUBLE [PRECISION]REAL8 バイト
DECIMAL(M,D)NUMERIC(M,D)変動; 次の説明を参照
BIT(M)約 (M+7)/8 バイト

DECIMAL (および NUMERIC) カラムの値は、9 桁の 10 進数 (10 進法) を 4 バイトにパックするバイナリ形式を使用して表現されます。各値の整数部と小数部のストレージは、個別に決定されます。9 桁の倍ごとに 4 バイトが必要であり、余りの桁には 4 バイトのうちの一部が必要です。余りの桁に必要なストレージ要件を次の表に示します。

余りの桁バイト数
00
11
21
32
42
53
63
74
84

日付と時間型のストレージ要件

TIMEDATETIME、および TIMESTAMP カラムの場合、MySQL 5.6.4 よりも前に作成されたテーブルに必要なストレージは、5.6.4 以降で作成されたテーブルとは異なります。これは、5.6.4 で、0 から 3 バイトを必要とする小数部をこれらの型が持つことを許可するように変更されたためです。

データ型MySQL 5.6.4 より前で必要なストレージMySQL 5.6.4 以降で必要なストレージ
YEAR1 バイト1 バイト
DATE3 バイト3 バイト
TIME3 バイト3 バイト + 小数秒ストレージ
DATETIME8 バイト5 バイト + 小数秒ストレージ
TIMESTAMP4 バイト4 バイト + 小数秒ストレージ

MySQL 5.6.4 以降、YEAR および DATE のストレージは変更ありません。ただし、TIMEDATETIME、および TIMESTAMP は異なって表現されます。DATETIME はより効率的にパックされ、非小数部に必要なバイト数は 8 バイトではなく 5 バイトであり、3 つの部分すべてに、格納値の小数秒精度に応じて 0 から 3 バイトが必要な小数部があります。

小数秒精度必要なストレージ
00 バイト
1、21 バイト
3、42 バイト
5、63 バイト

たとえば、TIME(0)TIME(2)TIME(4)、および TIME(6) はそれぞれ 3、4、5、6 バイトを使用します。TIMETIME(0) は同等で、必要なストレージは同じです。

時間値の内部表現の詳細は、「MySQL Internals: Important Algorithms and Structures」を参照してください。

文字列型のストレージ要件

次の表では、M は宣言されたカラムの長さを、非バイナリ文字列型の場合は文字数で、バイナリ文字列型の場合はバイト数で表します。L は指定された文字列値の実際の長さをバイト数で表します。

データ型必要なストレージ
CHAR(M)M × w バイト、0 <= M <= 255、ここで w は、文字セット内の最大長の文字に必要なバイト数です。InnoDB テーブルの CHAR データ型のストレージ要件の詳細は、セクション14.2.13.7「物理的な行構造」を参照してください。
BINARY(M)M バイト、0 <= M <= 255
VARCHAR(M)VARBINARY(M)カラム値が 0 から 255 バイトを必要とする場合は、L + 1 バイト、値が 255 バイト以上を必要とする可能性のある場合は、L + 2 バイト
TINYBLOBTINYTEXTL + 1 バイト、ここで L < 28
BLOBTEXTL + 2 バイト、ここで L < 216
MEDIUMBLOBMEDIUMTEXTL + 3 バイト、ここで L < 224
LONGBLOBLONGTEXTL + 4 バイト、ここで L < 232
ENUM('value1','value2',...)列挙値の数 (最大 65,535 個の値) により 1 または 2 バイト
SET('value1','value2',...)セットメンバーの数 (最大 64 メンバー) により、1、2、3、4、または 8 バイト

可変長の文字列型は、長さプリフィクスが付いたデータを使用して格納されます。長さプリフィクスにはデータ型に応じて 1 から 4 バイトが必要で、プリフィクスの値は L (文字列のバイト長) です。たとえば、MEDIUMTEXT 値のストレージには、値を格納するための L バイトに加えて、値の長さを格納するための 3 バイトが必要です。

特定の CHARVARCHAR、または TEXT カラム値の格納に使用されるバイト数を計算するには、そのカラムに使用される文字セットと、値にマルチバイト文字が含まれるかどうかを考慮する必要があります。特に、utf8 (または utf8mb4) Unicode 文字セットを使用する場合、すべての文字セットが同じバイト数を使用するわけではなく、文字あたり最大 3 (4) バイトを必要とするわけではないことに注意する必要があります。utf8 または utf8mb4 文字の異なるカテゴリに使用されるストレージの詳細は、セクション10.1.10「Unicode のサポート」を参照してください。

VARCHARVARBINARY、および BLOBTEXT 型は可変長型です。それぞれのストレージ要件は次の要因によって決まります。

  • カラム値の実際の長さ

  • カラムの可能な最大の長さ

  • カラムに使用される文字セット。一部の文字セットにはマルチバイト文字が含まれるため。

たとえば、VARCHAR(255) カラムには最大 255 文字の長さの文字列を格納できます。そのカラムが latin1 文字セット (1 文字あたり 1 バイト) を使用すると仮定すると、実際に必要なストレージは文字列の長さ (L) に、文字列の長さを記録するための 1 バイトを加えた大きさとなります。文字列 'abcd' の場合、L は 4 で、ストレージ要件は 5 バイトになります。同じカラムが代わりにダブルバイト文字セット ucs2 を使用するように宣言されている場合、ストレージ要件は 10 バイトになります。'abcd' の長さは 8 バイトで、カラムの最大長が 255 よりも大きい (最大 510 バイト) ため、長さを格納するために 2 バイト必要になります。

VARCHAR または VARBINARY カラムに格納できる有効な最大バイト数は最大行サイズ (65,535 バイト、すべてのカラムで共有される) によって決まります。複数バイト文字を格納する VARCHAR カラムの場合、文字の有効な最大数は少なくなります。たとえば、utf8 の文字は 1 文字につき最大 3 バイトを必要とする場合があるため、utf8 の文字セットを使用する VARCHAR カラムは、最大 21,844 文字になるように宣言できます。セクションD.10.4「テーブルカラム数と行サイズの制限」を参照してください。

NDB ストレージエンジンは可変幅カラムをサポートします。これは、MySQL Cluster テーブル内の VARCHAR カラムは、このような値に対して 4 バイトアライメントが行われる点を除き、ほかのストレージエンジンと同じ容量のストレージを必要とするということを意味します。したがって、latin1 文字セットを使用して VARCHAR(50) カラムに格納された文字列 'abcd' は、(MyISAM テーブル内の同じカラム値に対する 5 バイトではなく) 8 バイトを必要とします。

TEXTBLOB カラムは、NDB ストレージエンジンでは異なって実装されます。ここでは、TEXT カラム内の各行は 2 つの別々の部分から構成されています。そのうちの 1 つは固定サイズ (256 バイト) で、実際に元のテーブルに格納されます。もう 1 つは 256 バイトを超えるデータで構成され、非表示のテーブルに格納されます。2 番目のテーブルの行の長さは常に 2,000 バイトです。これは、size <= 256 (ここで size は行のサイズを表します) の場合、TEXT カラムのサイズが 256 であり、それ以外の場合はサイズが 256 + size + (2000 − (size − 256) % 2000) であることを意味します。

ENUM オブジェクトのサイズは異なる列挙値の数によって決まります。最大 255 の値を持つ列挙に 1 バイトが使用されます。256 から 65,535 の値を持つ列挙に 2 バイトが使用されます。セクション11.4.4「ENUM 型」を参照してください。

SET オブジェクトのサイズは異なるセットメンバーの数によって決まります。セットサイズが N である場合、オブジェクトは 1、2、3、4、または 8 バイトに丸められた (N+7)/8 バイトを占めます。SET は最大 64 メンバーを持つことができます。セクション11.4.5「SET 型」を参照してください。

11.8 カラムに適した型の選択

最適なストレージのために、毎回もっとも正確な型を使用するよう試みる必要があります。たとえば、整数カラムを 1 から 99999 の範囲の値に使用する場合、MEDIUMINT UNSIGNED が最適な型になります。必要なすべての値を表す型の中で、これが、使用するストレージの容量がもっとも少ない型になります。

DECIMAL カラムを使用した基本的なすべての計算 (+-*、および /) は、65 桁 (10 進法) の精度で行われます。セクション11.1.1「数値型の概要」を参照してください。

精度がそれほど重要でない場合や、スピードが最優先事項である場合は、DOUBLE 型で十分と考えられます。精度を高めるために、BIGINT に格納されている固定小数点型にいつでも変換できます。これにより、64 ビット整数のすべての計算を行い、続いて必要に応じて結果を浮動小数点値に戻すことができます。

PROCEDURE ANALYSE を使用すると、最適なカラムデータ型の選択に役立つ情報を入手できます。詳細は、セクション8.4.2.4「PROCEDURE ANALYSE の使用」を参照してください。

11.9 その他のデータベースエンジンのデータ型の使用

ほかのベンダーからの SQL 実装用に作成されたコードを使用しやすくするために、次の表に示すように、MySQL はデータ型をマップします。これらのマッピングにより、ほかのデータベースシステムから MySQL へのテーブル定義の取り込みが簡単に行えるようになります。

その他のベンダーの型MySQL の型
BOOLTINYINT
BOOLEANTINYINT
CHARACTER VARYING(M)VARCHAR(M)
FIXEDDECIMAL
FLOAT4FLOAT
FLOAT8DOUBLE
INT1TINYINT
INT2SMALLINT
INT3MEDIUMINT
INT4INT
INT8BIGINT
LONG VARBINARYMEDIUMBLOB
LONG VARCHARMEDIUMTEXT
LONGMEDIUMTEXT
MIDDLEINTMEDIUMINT
NUMERICDECIMAL

データ型のマッピングはテーブル作成時に行われ、作成後に元の型の仕様は破棄されます。ほかのベンダーで使用されている型でテーブルを作成したあとで、DESCRIBE tbl_name ステートメントを発行した場合、MySQL は、その型と同等の MySQL の型を使用したテーブル構造をレポートします。例:

mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);Query OK, 0 rows affected (0.00 sec)
mysql> DESCRIBE t;+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | double | YES | | NULL | |
| c | mediumtext | YES | | NULL | |
| d | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
関連キーワード:  カラム,,します,11,TIMESTAMP,MySQL,バイト,および,されます,データ