無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

第10章 データタイプ

MySQLは次のようないくつかのカテゴリのデータタイプをサポートします。数値タイプ、データと時刻タイプ、そして文字列(文字)タイプです。この章ではまず最初にこれらのデータタイプの概要を紹介します。そして次にそれぞれのカテゴリタイプの特性についてのより詳しい説明と、データタイプに必要な記憶容量に関する条件の要約を紹介します。最初の概要はあえて簡単な物になっています。値の指定ができる許容フォーマットのような特定のデータタイプに関する追加情報に関しては、この章の後半で紹介しているより詳しい説明を参照してください。

MySQLは、空間データの拡張子もサポートします。章 16. Spatial Extensionsにこれらのデータタイプの情報があります。

いくつかのデータタイプの紹介の中で、これらの規則が使用されています。

  • M は整数タイプの最大ディスプレイ幅を示しています。浮動小数点と固定小数点タイプに関しては、M が格納可能な桁数の合計です。文字列タイプは M が最大長さです。M の最大許容値はデータタイプによって違います。

  • D は浮動小数点と固定小数点タイプに適用され、小数点以下の桁数を表します。最大値は30ですが、M–2以上であってはいけません。

  • 角かっこ (‘[’ と ‘]’) はタイプ定義のオプションを表します。

10.1. データタイプ概要

10.1.1. 数値タイプの概要

数値データタイプの要約が次に紹介されます。追加情報については 項10.2. 「数値タイプ」 を参照してください。必要とする記憶容量は 項10.5. 「データタイプが必要とする記憶容量」 に紹介されています。

M は整数タイプの最大ディスプレイ幅を示しています。最大法定ディスプレイ幅は255です。項10.2. 「数値タイプ」で説明されているように、ディスプレイ幅はそのタイプの許容値幅とは関係ありません。浮動小数点と固定小数点タイプに関しては、M が格納可能な桁数の合計です。

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

SERIALBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE の別名です。

整数カラム定義の中の SERIAL DEFAULT VALUENOT NULL AUTO_INCREMENT UNIQUE の別名です。

警告:1つが UNSIGNED タイプの時に整数値間で減算を行うと、NO_UNSIGNED_SUBTRACTION SQLモードが有効でない限り、その結果から符号がなくなります。項11.8. 「キャスト関数と演算子」を参照してください。

  • BIT[(M)]

    ビットフィールドタイプM は1から64の、各値のビット数を表しています。M が削除された場合、デフォルトは1です。

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

    大変小さい整数符号が付く範囲は -128 から 127 です。符号が付かない範囲は 0 から 255 です。

  • BOOLBOOLEAN

    これらのタイプは TINYINT(1) の同義語です。ゼロの値は誤りであるとみなされます。ゼロ以外の値は正確だとみなされます。

    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 は、1 とも 0 とも等しくないので、表示される結果を表しています。

    今後リリースされるMySQLの中で、標準SQLに基づき、ブーリアンタイプの扱いについて完全にカバーしていく予定です。

  • 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 です。

    BIGINT カラムに関して注意するべき事

    • 全ての演算は符号付の BIGINTDOUBLE 値を利用しているので、ビット関数を使わない限り 9223372036854775807 (63ビット) 以上の大きい符号無し整数は利用してはいけません!もしそれをしてしまうと、 BIGINT 値から DOUBLE に変換する時、丸め誤差の為に、結果の最後のいくつかの桁に誤差が出るかもしれません。

      MySQLは、次のような時に BIGINT を扱う事ができます。

      • BIGINT カラムに符号無しの大きい値を格納するのに整数を使用する時

      • col_nameBIGINT カラムを参照する、MIN(col_name)MAX(col_name) の中

      • 両方の演算数が整数の場合に、(+-*、等の) 演算子を利用する時

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

    • 両方の演算数が整数の場合、-+、そして * 演算子は、 BIGINT 演算を利用します。これは、もし二つの大きい整数を掛け合わした場合、(または整数を戻す関数からの結果)、その結果が 9223372036854775807 以上の時には、予期しない結果になるという事を意味します。

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

    小さい(単精度) 浮動小数点数許容値は -3.402823466E+38 から -1.175494351E-380、そして 1.175494351E-38 から 3.402823466E+38 です。これらは、IEEEスタンダードに基づいた理論的な限界です。利用するハードウェアやOSによっては、実際の範囲は少し小さくなるかも知れません。

    M は桁数の合計で、D は小数点以下の桁数の合計です。もし MD が削除された場合、値はハードウェアに許容された限界まで格納されます。単精度小数点数は大体小数第7位まで正確です。

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

    MySQLでは全ての計算が倍精度で行われているので、FLOAT を利用すると、予想外の問題が起きます。項B.1.5.7. 「Solving Problems with No Matching Rows」を参照してください。

  • DOUBLE[(MD)] [UNSIGNED] [ZEROFILL]

    普通サイズ(倍精度)浮動小数点数許容値は -1.7976931348623157E+308 から -2.2250738585072014E-3080、そして 2.2250738585072014E-308 から 1.7976931348623157E+308です。これらは、IEEEスタンダードに基づいた理論的な限界です。利用するハードウェアやOSによっては、実際の範囲は少し小さくなるかも知れません。

    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は結果となるデータタイプに対して、FLOATDOUBLE のどちらを利用するかを決める為だけにこの値を利用します。p が0から24の時、そのデータタイプは MD 値が無い FLOAT になります。p が25から53の時、そのデータタイプは MD 値が無い DOUBLE になります。結果となるカラムの範囲は、このセクションの最初の方で説明されているように、単精度 FLOAT か倍精度 DOUBLE データタイプの物と同じです。

    FLOAT(p) 構文はODBC互換性に添付されている。

  • 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 同義語は他のデータベースと互換性があります。

10.1.2. データと時刻タイプの概要

時間データタイプの要約が次に紹介されます。追加情報については 項10.3. 「日付と時刻タイプ」 を参照してください。必要とする記憶容量は 項10.5. 「データタイプが必要とする記憶容量」 に紹介されています。

DATETIMEDATE 範囲の説明では、「サポートする」というのは、以前の値が有効であったとしても、その保障は無いという意味になります。

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;
  • DATE

    日付です。サポートされている範囲は '1000-01-01' から '9999-12-31'です。MySQL は、DATE 値を 'YYYY-MM-DD' フォーマットで表示しますが、文字列と数字のどちらで DATE カラムに値を指示してもよいです。

  • DATETIME

    日付と時刻の組み合わせです。サポートされている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。MySQL は、 DATETIME 値を 'YYYY-MM-DD HH:MM:SS' フォーマットで表示しますが、文字列と数字のどちらで DATETIME カラムに値を指示してもよいです。

  • TIMESTAMP

    タイムスタンプです。範囲は '1970-01-01 00:00:01' UTCから 2037 年の途中までです。TIMESTAMP 値は ('1970-01-01 00:00:00' UTC)からの秒数として格納されます。TIMESTAMP は、'1970-01-01 00:00:00' 値を表す事はできません。なぜならば、これはその時から0秒である事に相当し、0という値は '0000-00-00 00:00:00' つまり、「ゼロTIMESTAMP 値 を表すのに用いられるからです。

    TIMESTAMP カラムは INSERT または UPDATE 操作の日付と時刻を記録するのに役立ちます。自分で値を指定しない限り、テーブルの TIMESTAMP カラムはデフォルトで一番最近の操作の日付と時刻に自動的にセットされます。NULL 値を指定する事で、現在の日付と時刻を TIMESTAMP カラムに設定する事もできます。自動初期設定と更新の特徴については 項10.3.1.1. 「TIMESTAMP MySQL 4.1での性質」 の中で説明されています。

    ディスプレイ幅が19文字に固定されている 'YYYY-MM-DD HH:MM:SS' フォーマットの中では、TIMESTAMP 値は文字列として戻されます。数字の値を得る為には +0 をタイムスタンプカラムに加える必要があります。

    :MySQL 4.1以前で使用されていた TIMESTAMP フォーマットはMySQL 5.1 の中ではサポートされていません。古いフォーマットに関する情報については、 MySQL 3.23, 4.0, 4.1 リファレンスマニュアル を参照してください。

  • TIME

    時刻です。範囲は '-838:59:59' から '838:59:59' です。MySQL は、TIME 値を 'HH:MM:SS' フォーマットで表示しますが、文字列と数字のどちらで TIME カラムに値を指示してもよいです。

  • YEAR[(2|4)]

    2桁、または4桁のフォーマットでの年です。デフォルトは4桁のフォーマットです。4桁のフォーマットでは、許容値は 1901 から 2155、そして 0000 です。2桁のフォーマットでは、許容値は1970年から2069年を表す、70 から 69 です。MySQLは YEAR の値を YYYY フォーマットで表示しますが、YEAR カラムには文字列と数字のどちらを使って値を指定する事もできます。

10.1.3. 文字列タイプの概要

文字列データタイプの要約が次に紹介されています。追加情報については 項10.4. 「文字列タイプ」 を参照してください。必要とする記憶容量は 項10.5. 「データタイプが必要とする記憶容量」 に紹介されています。

MySQLは時々、文字列カラムを CREATE TABLEALTER TABLE ステートメントで与えられているタイプとは違う物に変更する事があります。項12.1.8.1. 「サイレント カラム仕様変更」を参照してください。

MySQL 4.1以降の中には、MySQL4.1以前のバージョンには無かった文字列データタイプの特徴が含まれます。

  • MySQLは、文字単位の中の文字カラム定義の長さ仕様を解明します。(MySQL 4.1以前は、カラム長さはバイトで解釈されていました。)これは、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 という名前のカラムを作成します。

      CHARSETCHARACTER SET の同義語です。

    • ASCII 属性は CHARACTER SET latin1 の省略表現です。

    • UNICODE 属性は CHARACTER SET ucs2 の省略表現です。

    • BINARY 属性は、カラム文字セットのバイナリ照合を指定する省略表現です。この場合、ソートと比較は数値文字値に基づきます。(MySQL 4.1以前のバージョンでは、BINARY はカラムにバイナリ文字列を格納させ、ソートと比較は数値バイト値に基づいていました。これは1バイト文字セットに文字値を使用するのと同じですが、複数バイト文字セットに使用するのとは違います。)

  • 文字カラムのソートと比較は、カラムに割り当てられた文字セットに基づいています。(MySQL 4.1バージョン以前は、ソートと比較はサーバー文字セットの照合に基づいていました。)CHARVARCHARTEXTENUM、そして SET データタイプに対して、語彙の順番ではなく基礎となる文字コード値を利用する為に、バイナリ照合を持つカラムか、ソートと比較を行う BINARY 属性を宣言する事ができます。

章 9. キャラクタセットサポートMySQLの中での文字セットの使用についての追加情報を紹介しています。

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

    格納時に必ずスペースを使って指定された長さに詰められる固定長文字列。M はカラム長さを表します。M の範囲は、0から255文字です。

    :CHAR 値が検索された時、後続スペースは削除されます。

    CHAR の長さを255以上に設定しようとすると、CREATE TABLEALTER TABLE ステートメントが実行されたテーブル内でエラーが発生し、その作業は失敗します。

    mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500));
    ERROR 1074 (42000): Column length too big for column 'col' (max = 255);
    use BLOB or TEXT instead
    mysql> SHOW CREATE TABLE c1;
    ERROR 1146 (42S02): Table 'test.c1' doesn't exist
    

    CHARCHARACTER の省略表現です。NATIONAL CHAR (またはそれと同等である NCHAR) は、CHAR カラムが定義済文字セットを使用しなければいけないという事を定義する為の標準のSQLの方法です。MySQL 4.1以降のバージョンでは、この定義済文字セットとして utf8 を利用します。 項9.3.6. 「各国キャラクタセット」.

    CHAR BYTE データタイプは BINARY データタイプの別名です。これは互換性の特徴です。

    MySQLで CHAR(0) タイプのカラムを作成する事ができます。これは主に、カラムの存在に頼っていても、その値は実際には使用しない古いアプリケーションに対応する必要がある時に便利な物です。CHAR(0) はまた、二つの値だけを取り込む事ができるカラムが必要な時にも大変便利です。CHAR(0) NULL として定義されたカラムは1ビットだけ使用し、NULL'' (空の文字列)値だけを取り込む事ができます。

  • CHAR [CHARACTER SET charset_name] [COLLATE collation_name]

    このタイプは CHAR(1) の同義語です。

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

    可変長文字列です。M はカラムの最大長さを表します。M の範囲は0から65,535です。(VARCHAR の実際の最大長さは使用する最大行サイズと文字セットによって決まります。最大有効カラム長さは65,532バイトの行サイズによります。)

    :MySQL 5.1 は、標準SQL仕様に従うので、VARCHAR 値から後続スペースを削除しません

    VARCHARCHARACTER VARYING の省略表現です。

    VARCHAR は1バイト、または2バイトの長さのプリフィックスに加え、データと共に格納されています。VARCHAR カラムが255以上の長さであればプリフィックスの長さは2バイトです。

  • BINARY[(N)]

    BINARY タイプは CHAR タイプと似ていますが、非バイナリ文字の文字列ではなく、バイナリバイト文字列を格納します。

  • VARBINARY(M)

    VARBINARY タイプはVARCHAR タイプと似ていますが、非バイナリ文字の文字列ではなく、バイナリバイト文字列を格納します。

  • TINYBLOB

    最長255 (28 – 1) バイトの BLOB カラムです。

  • TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    最長255 (28 – 1) 文字の TEXT カラムです。

  • BLOB[(M)]

    最長65,535 (216 – 1) バイトの BLOB カラムです。

    長さ M を任意で利用する事もできます。もしこれを実行すると、MySQLは M バイトの長さの値を保持するのに十分な最小 BLOB を作成します。

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

    最長65,535 (216 – 1) 文字の TEXT カラムです。

    長さ M を任意で利用する事もできます。もしこれを実行すると、MySQLは M 文字の長さの値を保持するのに十分な最小 TEXT タイプを作成します。

  • MEDIUMBLOB

    最長16,777,215 (224 – 1) バイトの BLOB カラムです。

  • MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    最長16,777,215 (224 – 1) 文字の TEXT カラムです。

  • LONGBLOB

    最長4,294,967,295、または4GB (232 – 1) バイトの BLOB カラムです。LONGBLOB カラムの有効な(許可されている)最長長さは、クライアント/サーバープロトコルと使用可能メモリの中に組み込まれている最大パケットサイズにより決まります。

  • LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    最長4,294,967,295、または4GB (232 – 1) バイトの TEXT カラムです。LONGTEXT カラムの有効な(許可されている)最長長さは、クライアント/サーバープロトコルと使用可能メモリの中に組み込まれている最大パケットサイズにより決まります。

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

    一覧表です。'value1''value2'...NULL または特別な'' エラー値のリストから選択された、1つの値しか持つ事ができない文字列オブジェクトです。ENUM カラムは最高65,535 の異なる値を持つ事ができます。ENUM 値は、内部的には整数として表されます。

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

    設定です。それぞれが、'value1''value2'... 値のリストから選択されなければいけない、ゼロ、またはそれ以上の値を持つ事ができる文字列オブジェクトです。SET カラムは最高64メンバを持つ事ができます。SET 値は、内部的には整数として表されます。

10.1.4. データタイプデフォルト値

データタイプ仕様の中の DEFAULT value 条項はカラムのデフォルト値を表します。例外がひとつあります。デフォルト値は一定でなければいけませんので、それは関数や式にはなり得ません。これは例えば、日付カラムの値に NOW()CURRENT_DATE のような関数の値をデフォルトとして設定する事はできないという意味です。例外として、TIMESTAMP カラムのデフォルトとして CURRENT_TIMESTAMP を指定する事ができます。項10.3.1.1. 「TIMESTAMP MySQL 4.1での性質」を参照してください。

BLOBTEXT カラムはデフォルト値として指定する事ができません。

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

もし NULL を値として取る事ができるなら、そのカラムは明示的な DEFAULT NULL 条項で定義する事ができます。

もし NULL を値として取る事ができなければ、MySQLは明示的な DEFAULT 条項でカラムを定義できません。データの入力に関しては、もし INSERTREPLACE ステートメントがカラムの値を含んでいなければ、MySQLはその時有効なSQLモードに従ってカラムを扱います。

  • もしストリクトSQLモードが有効でなければ、MySQLはカラムデータタイプに暗黙のデフォルト値を設定します。

  • もしストリクトモードが有効だと、トランザクションテーブルにエラーが起き、ステートメントがロールバックされます。非トランザクションテーブルではエラーが起きますが、もしこれが複数行ステートメントの2行目か後続の行に対してのエラーだとすると、その先行する行が挿入されるでしょう。

テーブル 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));

詳しくは 項4.2.6. 「SQL モード」 を参照してください。

与えられたテーブルに対して、どのカラムが明示的な DEFAULT 条項を持つかを確かめる為に、 SHOW CREATE TABLE ステートメントを利用する事ができます。

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

  • AUTO_INCREMENT 属性で宣言された整数タイプ以外の数値タイプのデフォルトは 0 です。AUTO_INCREMENT カラムのデフォルト値は、その配列の中の次の値です。

  • TIMESTAMP 以外の日付と時刻タイプのデフォルトには、「ゼロ」値が適切です。テーブルの最初の TIMESTAMP カラムのデフォルト値は現在の日付けと時刻です。項10.3. 「日付と時刻タイプ」を参照してください。

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

10.2. 数値タイプ

MySQLは標準SQLの全ての数値データタイプをサポートします。これらのタイプは、概数値データタイプ(FLOATREALDOUBLE PRECISION)だけでなく、真数値データタイプ(INTEGERSMALLINTDECIMALNUMERIC)を含みます。キーワード INTINTEGER のシノニムで、キーワード DECDECIMAL のシノニムです。数値タイプが必要とする記憶容量に関しては、項10.5. 「データタイプが必要とする記憶容量」を参照してください。

BIT データタイプはビットフィールド値を格納します。これは、MyISAMMEMORYInnoDB テーブルに対してサポートされています。

SQL標準への拡張として、MySQLは TINYINTMEDIUMINTBIGINT などの整数タイプもサポートします。次のテーブルには、各整数タイプが必要とする容量と値の範囲が示されています。

タイプバイト最小値最大値
  (Signed/Unsigned)(Signed/Unsigned)
TINYINT1-128127
  0255
SMALLINT2-3276832767
  065535
MEDIUMINT3-83886088388607
  016777215
INT4-21474836482147483647
  04294967295
BIGINT8-92233720368547758089223372036854775807
  018446744073709551615

MySQLがサポートするその他の拡張として、各タイプの基本キーワードに続くカッコ内に整数データタイプの表示幅を指定するオプションがあります(例 INT(4))。この表示幅オプションは、カラムに指定された幅よりも小さい幅の整数値を表示する際に左側をスペースで埋めるために使用されます。

この表示幅は、カラムに格納する事ができる値の範囲も、カラムに指定された幅を超える値の表示される桁数も制限しません。例えば、SMALLINT(3) として指定されたカラムは、通常の -32768 から 32767SMALLINT 範囲を持ち、そして、3文字で許容された範囲外の値は3文字以上の文字を使って表示されます。

オプションの拡張属性 ZEROFILL が続いて指定された時は、デフォルトはスペースである埋め込み文字はゼロで置き換えられます。例えば、INT(5) ZEROFILL として宣言されたカラムには、4 の値は 00004 として表示されます。もし整数カラムの中に表示幅よりも大きい値を格納すると、MySQLが複雑なJOINに対してテンポラリテーブルを生成した時に問題が発生するという事に注意してください。これはMySQLは、データは元のカラム幅に収まると仮定するからです。

:ZEROFILL 属性はカラムが式や UNION クエリに含まれている時は無効になります。

全ての整数タイプは、任意の(標準ではない) 拡張子である UNSIGNED を持つ事ができます。正数だけをコラムの中で許可し、大きい上位数値範囲が必要な時には符号無しの値を利用できます。例えば、INT カラムが UNSIGNED の時、カラム範囲のサイズは同じですが、その終点は -21474836482147483647 から、04294967295 までシフトします。

浮動小数点と固定小数点も UNSIGNED になり得ます。整数タイプと同じように、この拡張子は負の値がカラムの中に格納されるのを防ぎます。しかし整数タイプとは違い、カラム値の上限は同じままです。

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

浮動小数点タイプでは、MySQLは単精度値には4バイトを使用し、倍精度値には8バイト使用します。

FLOATDOUBLE データタイプは、近似数値データ値を表す為に利用されます。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 になります。

MySQLは DOUBLEDOUBLE PRECISION (標準ではない拡張子)の同義語として扱います。MySQLはまた、REAL_AS_FLOAT SQLモードが有効でない限り REALDOUBLE PRECISION (標準ではない変動)の同義語として扱います。

最大ポータビリティの為に、おおよその数値データ値のコードを必要とする格納は、精度仕様や桁数の無いFLOATDOUBLE PRECISION を利用する必要があります。

DECIMALNUMERIC データタイプは、真数値データ値を格納するために利用されます。MySQLでは、NUMERICDECIMAL として実行されます。これらのタイプは、金銭データ等の正確な精度を必要とする物を格納するために利用されます。

MySQL 5.1 は、DECIMALNUMERIC 値をバイナリフォーマットに格納します。MySQL 5.0.3バージョン以前では、それらは文字列として格納されていました。章 22. 精密計算を参照してください。

DECIMALNUMERIC カラムを宣言する時は、精度とスケールを指定する事ができます。(そして実際に通常は指定しています。) 次がその例です。

salary DECIMAL(5,2)

この例の中では、5 は精度で、2 がスケールです。精度は、その値に格納された有効な桁数を表し、スケールは小数点以下に格納できる桁数を表しています。もしスケールが0なら、DECIMALNUMERIC 値は小数点と、小数点以下の数字を持ちません。

標準SQLでは、salary カラムは5桁で、かつ小数点以下2桁の値を格納する必要があります。それ故この場合、salary カラムに格納できる値の範囲は、-999.99 から 999.99 です。

標準SQLでは、構文 DECIMAL(M) は、DECIMAL(M,0) に相当します。同じように、構文 DECIMALは、DECIMAL(M,0) に相当し、その M の値はインプリメンテーションが決定する事ができます。MySQLは、この両方の DECIMALNUMERIC 構文の改良形をサポートします。M のデフォルト値は10です。

DECIMALNUMERIC の最大桁数は65ですが、DECIMALNUMERIC カラムの実際の範囲はカラムの精度やスケールに制約される事があります。そのようなカラムが、指定スケールで許容されている小数点以下の桁数よりも多い桁数の値を指定した場合、その値はそのスケールに変換されます。(厳密には、OS特有の機能をするのですが、通常その結果は許容桁数の切捨てになります。)

BIT データタイプは、ビットフィールド値を格納するのに利用されます。BIT(M) のタイプは、M ビット値の格納を許容します。M の範囲は1から64までが可能です。

ビット値を指定するには、b'value' 表記を利用する事ができます。value はゼロと1で書かれたバイナリ値です。例えば、b'111'b'10000000' は、それぞれ7と128を表しています。項8.1.5. 「ビットフィールド値」を参照してください。

M ビットよりも短い BIT(M) カラムに値を指定すると、その値の左側にゼロが埋め込まれます。例えば、BIT(6) カラムに b'101' の値を指定すると、実際には b'000101' を指定した事と同じ意味になるのです。

そのデータタイプの許容範囲外の値を数値カラムに格納しようとすると、MySQLの反応はその時有効なSQLモードによって決まります。例えば、もし制限モードが有効でない場合、MySQLは値をその範囲の適切な長さに短縮し、その結果出た値を代わりに格納します。しかし、もしモードが TRADITIONAL に設定されていると、SQLスタンダードに従いエラーが発生し、MySQLは範囲外の値を受け付けず、挿入は失敗します。

非ストリクトモードで整数カラムに範囲外の値が指定された時、MySQLはそのカラムデータタイプの範囲に相当する終点の値を格納します。TINYINTTINYINT UNSIGNED カラムに256を格納すると、MySQLはそれぞれに127か255を格納します。浮動小数点や固定小数点カラムが、指定された(またはデフォルトの)精度とスケールの暗黙範囲を超えた値を割り当てると、MySQLはその範囲に対応する終点を表す値を格納します。

MySQLがストリクトモードで機能していない時の短縮によって行われた変換は、ALTER TABLELOAD DATA INFILEUPDATE、そして複数行 INSERT ステートメントに対しては警告としてレポートされます。MySQLがストリクトモードで機能している時は、そのテーブルがトランザクションテーブルかそれ以外の物なのかによって、これらのステートメントは失敗となり、いくつかの、または全ての値の挿入も変更も行われません。詳細に関しては 項4.2.6. 「SQL モード」 を参照して下さい。

10.3. 日付と時刻タイプ

時間的な値を表す日付と時刻タイプは、DATETIMEDATETIMESTAMPTIME、そして YEAR です。MySQLが表す事のできない不正データを指示した時に利用される「ゼロ」値と同様に、各時刻タイプは有効値範囲を持ちます。TIMESTAMP タイプには、後に紹介されますが、特別な更新機能があります。時刻タイプが必要とする記憶容量に関しては、項10.5. 「データタイプが必要とする記憶容量」 を参照してください。

MySQLは不正データを挿入すると警告かエラーを発生させます。SQLモードを適切な値に設定する事で、MySQLがサポートする日付のタイプを指定する事ができます。(詳しくは 項4.2.6. 「SQL モード」 をご確認ください。)ALLOW_INVALID_DATES SQLモードを利用する事によって、'1999-11-31' のような確実な日付をMySQLに指定する事ができます。これは、ユーザーが将来の処理の為にデータベースの中で指定した「間違っているかもしれない」値を格納したい時に便利です。このモードの時MySQLは、月は0から12の範囲、日付は0から31の範囲でしか確認しません。MySQLが DATEDATETIME カラムの中で日付、または月と日付がゼロであるデータの格納を許可するので、これらの範囲はゼロを含むと定義されています。これは、誕生日など、正確な日付が不明なデータを格納する必要があるアプリケーションに大変便利です。その場合は、'1999-00-00''1999-01-00' 等のようにデータを格納するだけでよいのです。このようなデータを格納する時は、DATE_SUB()DATE_ADD など、正確な日付を必要とする物のように、正確な結果を期待する事はできません。(もし日付にゼロを利用したくないのであれば、NO_ZERO_IN_DATE SQLモードを利用する事ができます。)

MySQLではまた、'0000-00-00' を 「ダミーの日付」 (もしNO_ZERO_DATE SQLモードを利用していないのであれば)として格納する事ができます。これは、NULL 値を利用するよりも便利な事が多いです。(データとインデックスの容量も少量で済みます。)

日付と時刻タイプを利用する際の注意事項があります。

  • MySQLは標準アウトプットフォーマットの中で入力された日付や時刻の値を検索しますが、提供された値に対して、いくつものフォーマットを読み取ろうとします。(例えば、日付や時刻タイプに指定される、または比較される値を指定した時。)次のセクションで紹介されているフォーマットだけがサポートされています。正当な値を提供しなければいけません。もし他のフォーマットの値を使用すると、予期しない結果が出る可能性があります。

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

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

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

  • MySQLが値をいくつかのフォーマットで読み取るとしても、日付は一般的に頻繁に使われる、月-日-年や、日-月-年の順番ではなく、(例えば '09-04-98''04-09-98')、年-月-日の順番(例えば '98-09-04')で入力する必要があります。

  • MySQLは、その値が数字で利用されれば、日付と時刻タイプの値を数字に変換し、またその反対も行います。

  • デフォルトでは、MySQLに日付や時刻タイプの範囲外の値や、そのタイプには不正なデータ(このセクションの始めで触れたように)が入力された時は、その値を 「ゼロ」 に変換します。その例外は、範囲外の TIME 値は TIME 範囲の適切な終点にクリップされるという事です。

    次のテーブルに、それぞれのタイプの 「ゼロ」 値のフォーマットが表されています。NO_ZERO_DATE SQLモードが有効な場合は、これらの値を利用すると警告メッセージが表示される事を覚えておいて下さい。

    データタイプゼロ」 値
    DATETIME'0000-00-00 00:00:00'
    DATE'0000-00-00'
    TIMESTAMP'0000-00-00 00:00:00'
    TIME'00:00:00'
    YEAR0000
  • ゼロ」 値は特別ですが、テーブルに表されている値を利用して格納したり、正確に参照したりする事ができます。'0'0 の値を利用して行う事も可能です。こちらの方が書き込むよりも簡単です。

  • MyODBCで利用される 「ゼロ」 の日付や時刻値はODBCでは扱う事ができないので、MyODBC 2.50.12以前のバージョンでは自動的に NULL に変換されます。

10.3.1. DATETIMEDATE、そして TIMESTAMP タイプ

DATETIMEDATE、そして TIMESTAMP タイプは関連しています。このセクションでは、それらがどのように似ているのか、そしてどのような点で異なっているのかなどの、特徴について説明しています。

DATETIME タイプは、日付と時刻の両方の情報を含む値が必要な時に利用します。MySQLは、DATETIME 値を 'YYYY-MM-DD HH:MM:SS' のフォーマットで検索、表示します。サポートされている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

DATE タイプは時刻の部分は無く、日付の値だけが必要な時に利用します。MySQLは、DATE 値を 'YYYY-MM-DD' のフォーマットで検索、表示します。サポートされている範囲は '1000-01-01' から '9999-12-31' です。

DATETIMEDATE 範囲の説明では、「サポートする」というのは、以前の値が有効であったとしても、その保障は無いという意味になります。

TIMESTAMP データタイプは、MySQLのバージョンと、そのサーバーが稼動しているSQLモードによって様々な性質を持っています。これらの性質は、このセクションの後のほうで説明します。

DATETIMEDATE、そして TIMESTAMP 値を、フォーマットの共通セットを利用して、指定する事ができます。

  • 'YYYY-MM-DD HH:MM:SS''YY-MM-DD HH:MM:SS' フォーマットの文字列として。「柔軟な」 構文が許可されています。句読点文字が、日付部分と時刻部分の区切り文字として利用される事があります。例えば、'98-12-31 11:30:45''98.12.31 11+30+45''98/12/31 11*30*45'、そして '98@12@31 11^30^45' は同等です。

  • 'YYYY-MM-DD''YY-MM-DD' フォーマットの文字列として。「柔軟な」 構文がここでも許可されています。例えば、'98-12-31''98.12.31''98/12/31'、そして'98@12@31' は同等です。

  • 文字列が日付として成り立つという条件で、 'YYYYMMDDHHMMSS''YYMMDDHHMMSS' フォーマットの区切り文字が無い文字列として。例えば、'19970523091528''970523091528''1997-05-23 09:15:28'を表しますが、'971122129015' は不正データで、(これは意味を成さない分の部分がある為)'0000-00-00 00:00:00' となります。

  • 文字列が日付として成り立つという条件で、'YYYYMMDD''YYMMDD' フォーマットの区切り文字が無い文字列として。例えば、'19970523''970523''1997-05-23' を表しますが、'971332' は不正データで、(これは意味を成さない月と日の部分がある為)'0000-00-00' となります。

  • 文字列が日付として成り立つという条件で、YYYYMMDDHHMMSSYYMMDDHHMMSS フォーマットの数字として。例えば、19830905132800830905132800'1983-09-05 13:28:00' という意味になります。

  • 文字列が日付として成り立つという条件で、YYYYMMDDYYMMDD フォーマットの数字として。例えば、19830905830905'1983-09-05 という意味になります。

  • NOW()CURRENT_DATE のような、DATETIMEDATE、または TIMESTAMP コンテキストで許容可能な値を戻す関数の結果。

不正な DATETIMEDATE、または TIMESTAMP 値は、適切なタイプ ('0000-00-00 00:00:00''0000-00-00')の 「ゼロ」 値に変換されます。

日にち部分の区切り文字を含む文字列として指定された値には、月か日にちの値に 10 以下の2桁の値を指定する必要はありません。'1979-6-9''1979-06-09' と同じです。同じように、時刻部分の区切り文字を含む文字列として指定された値には、時、分、または秒の値に 10 以下の2桁の値を指定する必要はありません。'1979-10-30 1:2:3''1979-10-30 01:02:03' と同じです。

数字として指定された値は、6、8、12、または14桁の長さである必要があります。もし数字が8、または14桁の長さなら、それはYYYYMMDDYYYYMMDDHHMMSS フォーマットであり、年は最初の4桁で表されていると仮定されます。もしその数字が6、または12桁であれば、YYMMDDYYMMDDHHMMSS フォーマットであり、年は最初の2桁で表されていると仮定されます。これらの長さではない数字は後ろがゼロで詰められ、これらの中の一番近い桁数と仮定して判断されます。

区切り文字が無い文字列として指定された値はそれ自体の長さのまま判断されます。もしその文字列が8か14文字なら、年は最初の4文字で表されていると判断されます。そうでなければ、年は最初の2文字で表されていると判断されます。文字列は、左から右に順番に、年、月、日、時、分、そして秒として、その文字列に存在する限りの情報が読み取られます。これは、6文字以下の文字列は利用してはいけないという事を意味します。例えば、もし1999年3月を表そうとして '9903' と指定すると、MySQLは日付の値に 「ゼロ」 を挿入します。年と月の値は 9903 ですが、日付の部分が全く無いのでこのような事が起こります。ですので、この値は不当な値という事になります。しかし、欠落している月や日付の部分をゼロの値を使って明確に指定する事ができます。例えば、'1999-03-00' という値を挿入する為に、'990300' を利用する事ができます。

1つの日付タイプの値を異なる日付タイプのオブジェクトに割り当てる事がある程度可能です。しかし、値の変更や情報の損失などが起こる可能性があります。

  • もし DATE 値を DATETIMETIMESTAMP オブジェクトに割り当てると、DATE 値は時刻の情報を持たないので、その結果の時刻の部分は '00:00:00' に設定されます。

  • もし DATETIMETIMESTAMP 値を DATE オブジェクトに割り当てると、DATE 値は時刻の情報を格納しないので、その結果の時刻の部分は 削除されます。

  • DATETIMEDATE、そして TIMESTAMP 値は、全て同じフォーマットの組み合わせを利用して指定する事ができますが、それらのタイプは全て同じ範囲の値を持つわけではない事を覚えておいてください。例えば、TIMESTAMP 値は 1970 以前や、2037 以降にはなり得ないという事です。これは、'1968-01-01' のような日付は、DATETIMEDATE 値としては有効ですが、TIMESTAMP 値としては無効で、0 に変換されるという意味になります。

日付値を指定する時には、特定の落とし穴に気をつけてください。

  • 文字列として指定された値に許容される柔軟なフォーマットはまぎらわしい事があります。例えば、'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 を有効にしてください。詳細については、項4.2.6. 「SQL モード」 をご参照ください。

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

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

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

10.3.1.1. TIMESTAMP MySQL 4.1での性質

:MySQLの古いバージョン (4.1以前)の TIMESTAMP データの性質は、このセクションで紹介されている物とは様々な面で明らかに違いました。古い TIMESTAMP データをMySQL 5.1 で利用する為に変換するには、その詳細について MySQL 3.23, 4.0, 4.1 リファレンスマニュアル を必ず参照してください。

TIMESTAMP カラムは DATETIME カラムと同じフォーマットで表示されます。言い換えると、表示幅は19文字に決められていて、フォーマットは YYYY-MM-DD HH:MM:SS となります。

MySQLサーバは MAXDB SQLモードが有効な時も実行する事ができます。このモードが有効な状態でサーバが実行された時、TIMESTAMPDATETIME と同一です。これは、もしテーブルが作成された時にこのモードが有効だと、TIMESTAMP カラムは DATETIME カラムとして作成される、という意味になります。その結果、そのようなカラムは DATETIME 表示フォーマットを利用し、同じ範囲の値を持ち、自動初期化機能や、現在の日付と時刻に自動的にアップデートする機能はないという事になります。

MAXDB モードを有効にするには、起動の際に、--sql-mode=MAXDB サーバオプションを利用するか、ランタイム時にグローバル sql_mode 変数を設定して、サーバSQLのモードを MAXDB に設定してください。

mysql> SET GLOBAL sql_mode=MAXDB;

クライアントは接続の為に、次のようにサーバを MAXDB モードで起動させる事ができます。

mysql> SET SESSION sql_mode=MAXDB;

次に紹介されている情報は、MAXDB モードが有効な状態で作成されなかったテーブルだけの TIMESTAMP カラムに適合するという事を覚えておいて下さい。なぜならば、そのようなカラムは DATETIME カラムとして作成されるからです。

MySQLは、日付か月のカラムにゼロを含むタイムスタンプ値や、有効でない日付値は許容しません。このルールの唯一の例外は、'0000-00-00 00:00:00' の特別値です。

いつ TIMESTAMP の自動初期化とアップデートが起こるのか、そしてどのカラムがそれらを行うべきなのかを決めるのに、相当な柔軟性があります。

  • テーブル内の1つの TIMESTAMP カラムに対して、現在のタイムスタンプをデフォルト値と自動更新値として指定する事ができます。現在のタイムスタンプを、カラムを初期化するデフォルト値にする事、または自動更新のデフォルト値にする事、またはその両方にする事が可能です。現在のタイムスタンプを、1つのカラムを初期化するデフォルト値にし、別のカラムの自動更新のデフォルト値にする事は不可能です。

  • どのTIMESTAMP カラムが現在の日付と時刻を自動的に初期化したり更新したりするのか指定する事ができます。これは、最初の TIMESTAMP カラムである必要はありません。

次のルールが TIMESTAMP カラムの初期化と更新を管理しています。

  • もし DEFAULT 値がテーブルの最初の TIMESTAMP カラムに指定されたら、それは無視されません。CURRENT_TIMESTAMP、または一定の日付と時刻値がデフォルトになり得ます。

  • 最初の TIMESTAMP カラムにとって、DEFAULT NULLDEFAULT CURRENT_TIMESTAMP と同じです。それ以外の全ての TIMESTAMP カラムにとっては、DEFAULT NULLDEFAULT 0 として扱われます。

  • テーブル内の全ての1つの TIMESTAMP カラムは、現在のタイムスタンプに初期化された物か、自動的に更新された物として利用されます。

  • CREATE TABLE ステートメントの中では、最初の TIMESTAMP カラムは次の方法のどれかで宣言する事ができます。

    • DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 条項の両方で、カラムはそのデフォルトに現在のタイムスタンプを持ち、それは自動的に更新されます。

    • DEFAULTON UPDATE 条項のどちらも、DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP とは同じではありません。

    • DEFAULT CURRENT_TIMESTAMP 条項と、ON UPDATE ではない 条項で、カラムはそのデフォルトに現在のタイムスタンプを持ちますが、それは自動的に更新されません。

    • DEFAULT 条項が無い、ON UPDATE CURRENT_TIMESTAMP がある条項では、カラムのデフォルトは0で、それは自動的に更新されます。

    • 一定の DEFAULT 値の時は、カラムは一定のデフォルトを持ちます。もしカラムが ON UPDATE CURRENT_TIMESTAMP 条項を持っていればそれは自動的に更新されますが、そうでない時は更新されません。

    言い換えると、現在のタイムスタンプを初期値と自動更新値の両方、またはそのどちらかに利用する事ができる、または、両方とも利用しない事も可能です。(例えば、自動初期化されたカラムを持たずに自動更新を可能にする為に ON UPDATE を指定する事が可能です。)

  • CURRENT_TIMESTAMP またはその同義語(CURRENT_TIMESTAMP()NOW()LOCALTIMELOCALTIME()LOCALTIMESTAMP、または LOCALTIMESTAMP())は DEFAULTON UPDATE 条項の中で利用する事ができます。それらは全て 「現在のタイムスタンプ」 を意味します。(UTC_TIMESTAMP は許容されていません。現在のタイムゾーンが UTC でない限り、その値の範囲は TIMESTAMP カラムの値の範囲と並びません。)

  • DEFAULTON UPDATE 属性の順番は関係ありません。もし DEFAULTON UPDATE の両方が TIMESTAMP カラムに指定されると、どちらかがもう片方に先行します。例えば、これらのステートメントは同等になります。

    CREATE TABLE t (ts TIMESTAMP);
    CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP);
    CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                                 DEFAULT CURRENT_TIMESTAMP);
    
  • TIMESTAMP カラムに最初の物以外の自動デフォルトや更新を指定するには、一定の DEFAULT 値(例えば、DEFAULT 0DEFAULT '2003-01-01 00:00:00')を明確に指定する事によって、最初の TIMESTAMP カラムの自動初期化や更新動作を抑圧する必要があります。そして、それ以外の TIMESTAMP カラムに対しては、DEFAULTON UPDATE 条項の両方を削除しなければルールは最初の TIMESTAMP カラムと同じで、自動初期化や更新は行われません。

    例:これらのステートメントは同等です。

    CREATE TABLE t (
        ts1 TIMESTAMP DEFAULT 0,
        ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                      ON UPDATE CURRENT_TIMESTAMP);
    CREATE TABLE t (
        ts1 TIMESTAMP DEFAULT 0,
        ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                      DEFAULT CURRENT_TIMESTAMP);
    

項4.10.8. 「MySQL サーバのタイム ゾーン サポート」 で説明されているように、現在のタイムゾーンをそれぞれの接続ごとに設定する事ができます。TIMESTAMP 値は、現在のタイムゾーンから変換されて格納され、また検索された時に現在のタイムゾーンに再変換されながら、UTCに格納されます。タイムゾーン設定が一定である限り、格納した値と同じ値を復帰させる事ができます。もし TIMESTAMP 値を格納してから、タイムゾーンを変更して値を検索すると、検索された値は格納した値とは違ってきます。これは、同じタイムゾーンが両方向への変換に利用されなかった為に起こります。現在のタイムゾーンは、time_zone システム変数の値のように有効です。

カラムが NULL 値を含む事を許容する為に TIMESTAMP カラムの定義の中に NULL 属性を含める事ができます。例:

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

もし NULL 属性が指定されていなければ、カラムを NULL に設定すると、現在のタイムスタンプに設定されます。NULL 値を許容する TIMESTAMP カラムは、下記の条件の時以外は現在のタイムスタンプを採用 しない という事を覚えておいて下さい。

  • そのデフォルト値は CURRENT_TIMESTAMP として定義されます。

  • NOW()CURRENT_TIMESTAMP がカラムに挿入されます。

言い換えると、NULL として定義された TIMESTAMP カラムは次のような定義を利用して作成された時だけ自動初期化します。

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

そうでなければ— ここに表されているように、もし TIMESTAMP カラムが DEFAULT TIMESTAMP を利用せずに NULL 値を許容するために定義されると …

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

… 現在の日付と時刻に対応した値を明確に挿入しなければいけません。例:

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

10.3.2. TIME タイプ

MySQLは TIME 値を 'HH:MM:SS' フォーマットで検索、表示します。(または長時間値を表すには 'HHH:MM:SS' フォーマット)TIME 値の範囲は '-838:59:59' から '838:59:59' です。TIME タイプは、一日のうちの時刻を表す事ができるだけでなく(24時間以下)、経過時間や、二つの出来事の間の時間を表す事もできるので(24時間よりも長い、またはマイナスの事も有る)、時間を表す部分がとても長くなる事があります。

TIME 値は様々なフォーマットで指定する事ができます。

  • 'D HH:MM:SS.fraction' フォーマットの文字列として次のうちの 「柔軟な」 構文の1つを利用する事もできます。'HH:MM:SS.fraction''HH:MM:SS''HH:MM''D HH:MM:SS''D HH:MM''D HH'、または 'SS'。ここでは、D は日を表し、0から34の値を持つ事ができます。MySQLは端数部分を格納しない事を覚えておいて下さい。

  • 時刻を表す、'HHMMSS' フォーマットで区切り文字を利用しない文字列として例えば、'101112''10:11:12' として理解されますが、'109712' は不正データとなり(意味を成さない分の部分を持つ為)、'00:00:00' となります。

  • 時刻を表す、'HHMMSS' フォーマットの数字として例えば、101112'10:11:12' として理解されます。以下のフォーマットもまた理解されます。SSMMSSHHMMSSHHMMSS.fractionMySQLは端数部分を格納しない事を覚えておいて下さい。

  • CURRENT_TIME のように TIME コンテキストの中で許容される値を返す関数の結果として

時刻部分の区切り文字を含む文字列として指定された TIME 値には、時、分、または秒の値に 10 以下の2桁の値を指定する必要はありません。'8:3:2''08:03:02' と同じです。

TIME カラムに省略された値を指定する際には注意してください。MySQLは、コロンが付いていない値は、その値の一番右の二桁が秒を表していると解釈します。(MySQLは TIME 値を、一日の内の時刻ではなく、経過時間として解釈します。)例えば、'1112'1112 は、'11:12:00' (11時12分過ぎ)を意味するように感じますが、MySQLはそれを '00:11:12' (11分12秒)と解釈します。同じように、'12'12'00:00:12' という意味になります。コロンが付いた TIME 値は反対に、必ず一日の内の時刻として扱われます。それは、'11:12''11:12:00' を表し、'00:11: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モードを有効にしてください。項4.2.6. 「SQL モード」を参照してください。

10.3.3. YEAR タイプ

YEAR タイプは年を表すために利用される1バイトのタイプです。

MySQLは YEAR 値を YYYY フォーマットで検索、表示します。範囲は 1901 から 2155 です。

TIME 値は様々なフォーマットで指定する事ができます。

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

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

  • '00' から '99' の範囲の2桁の文字列として'00' から '69' と、'70' から '99' の範囲の値は、 2000 から 2069 と、1970 から 1999 の範囲の YEAR 値に変換されます。

  • 1 から 99 の範囲の2桁の数字として1 から 69 と、70 から 99 の範囲の値は、2001 から 2069 と、1970 から 1999 の範囲の YEAR 値に変換されます。ゼロを数字として直接指定して、2000 と解釈させる事ができないので、2桁の数字の範囲は2桁の文字列の範囲と少しだけ違う事を覚えておいて下さい。'0''00' の文字列として指定すると、0000 として解釈されます。

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

不正な YEAR 値は 0000 に変換されます。

10.3.4. 2000年問題とデータタイプ

MySQLサーバ自体は2000年(Y2K)コンプライアンスに対して何も問題はありません。

  • MySQLサーバは、TIMESTAMP 値に対して、日付を 2037 年まで扱う Unix時間関数を利用しています。DATEDATETIME 値には、9999 年までの日付が許容されています。

  • 全てのMySQLの日付機能は一つのソースファイル、sql/time.cc で実行されており、2000年問題に対して安全にプログラムされています。

  • MySQLでは、YEAR データタイプは 0 年と 1901 年から 2155 年を1バイトで格納する事ができ、2桁か4桁でそれらを表示します。全ての2桁の年は 1970 年から 2069 年の範囲だと判断されます。ですので、YEAR カラムに 01 を格納すると、MySQLサーバはそれを 2001 年として扱います。

MySQLサーバ自体がY2Kに対して安全だとしても、そうでないアプリケーションと共に利用すると問題が起きる可能性があります。例えば、多くの古いアプリケーションは、4桁の値よりも、曖昧である2桁の値を利用して年を格納したりコントロールしたりします。この問題は、「欠けている」値を表す為に 0099 などの値を利用するアプリケーションによって作られる可能性があります。残念ながら、異なるアプリケーションは異なるプログラマによって書かれており、それぞれが、異なる仕様や日付管理の関数を利用しているので、これらの問題を修正するのは難しいです。

それ故、MySQLサーバにY2Kの問題が無いとしても、曖昧でない値を入力する事は、アプリケーションの義務です。.2桁の年を含む日付の値は、世紀が不明な為曖昧です。MySQLは内部的に4桁を利用して年を格納するので、そのような値は4桁の形に修正されなければいけません。

DATETIMEDATETIMESTAMP、そして YEAR タイプに対して、MySQLは次のルールを利用して曖昧な年の値の日付を修正します。

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

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

これらのルールは、データ値が何を表すかを妥当に推測する単なる経験則である事を覚えておいて下さい。もしMySQLが利用するルールが正しい値を導かなければ、4桁の年の値を含む、曖昧ではない入力が必要になります。

ORDER BY は、2桁の年を持つ YEAR 値を正しく分類します。

MIN()MAX() 等のようないくつかの関数は、YEAR を数字に変換します。これは、これらの関数を利用すると、2桁の年の値は正確に機能しないという意味になります。この場合の修正は、TIMESTAMPYEAR を4桁の年のフォーマットに変換するという事になります。

10.4. 文字列タイプ

文字列タイプの種類は、CHARVARCHARBINARYVARBINARYBLOBTEXTENUM、そしてSET です。このセクションでは、これらのタイプがどのように機能するのか、クエリの中でどのように利用するのかを説明します。文字列タイプが必要とする記憶容量に関しては、項10.5. 「データタイプが必要とする記憶容量」 を参照してください。

10.4.1. CHARVARCHAR タイプ

CHARVARCHAR タイプは似ていますが、格納、検索される方法が異なります。また、最大長さと、末尾のスペースが保持されるかどうかという点でも異なります。格納と検索の最中にレターケースの変換は行われません。

CHARVARCHAR タイプには、格納したい最大文字数を表す長さが宣言されています。例えば、CHAR(30) は最大30文字まで持つ事ができます。

CHAR カラムの長さは、テーブルを作成した時に宣言した長さに修正されます。長さは0から255までのどの長さにもなり得ます。CHAR 値が格納された時、指定された長さになるよう、右側が詰められます。CHAR 値が検索された時、後続スペースは削除されます。

VARCHAR カラム内の値は可変長の文字列です。長さは0から65,535の値で指定できます。(VARCHAR の最大有効長さは、最大行サイズと利用される文字サイズによって決まります。最大カラム長さは65,532バイトの行サイズによります。)

CHAR とは対照的に、VARCHAR 値は必要な文字数と、長さを記録する為の1バイト(255よりも長いカラムは2バイト)だけを利用して格納できます。

VARCHAR 値は格納される時に詰められません。スタンダードSQLに適合して、値が格納、検索される時に後続スペースは保持されます。

CHARVARCHAR カラムに、その最大長を超える値を指定すると、その値は切り捨てられます。切り捨てられた文字がスペースで無い場合には警告メッセージが表示されます。スペース以外の文字の切捨てに関しては、ストリクトSQLモードを利用する事で警告ではなくエラーを表示させ、その値の挿入を食い止める事ができます。項4.2.6. 「SQL モード」を参照してください。

次のテーブルは、CHAR(4)VARCHAR(4) カラムに様々な文字列値を格納した結果を表示する事で、CHARVARCHAR の違いを表しています。

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)

CHARVARCHAR カラムの中の値は、そのカラムに指定された 文字セットの照合に従って格納、比較されます。

全てのMySQL照合は PADSPACE タイプの物だと覚えておいてください。これは、MySQLの中の全ての CHARVARCHAR 値が後続スペースを無視して比較されるという事を意味します。例:

mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty  ', yourname = 'Monty  ' FROM names;
+--------------------+----------------------+
| myname = 'Monty  ' | yourname = 'Monty  ' |
+--------------------+----------------------+
|                  1 |                    1 |
+--------------------+----------------------+
1 row in set (0.00 sec)

これは全てのMySQLバージョンに当てはまり、サーバのSQLモードに影響されないという事を覚えておいて下さい。

後続文字が剥ぎ取られたり、比較がそれらを無視する場合は、もしカラムが固有の値を要求するインデックスを持っていたら、後続文字数だけが異なるカラム値への挿入は重複キーエラーになります。例えば、もしテーブルが 'a' を含んでいると、'a ' を格納しようとした時重複キーエラーになります。

10.4.2. BINARYVARBINARY タイプ

BINARYVARBINARY タイプは、非バイナリ文字列ではなく、バイナリ文字列を含んでいるところ以外で CHARVARCHAR に似ています。それは、それらが文字の文字列ではなく、バイトの文字列を含んでいるという事です。これは、それらが文字セットを持たず、ソートと比較は値の中の数値バイトに基づいているという意味です。

許容される最大長は、CHARVARCHAR と同様で、BINARYVARBINARY の長さは文字ではなく長さのバイトであるという事以外、BINARYVARBINARY と同じです。

BINARYVARBINARY データタイプは CHAR BINARYVARCHAR BINARY データタイプとは異なります。後者のタイプは、BINARY 属性によってカラムがバイナリ文字列カラムとして扱われる事はありません。代わりに、利用されるカラム文字セットのバイナリ照合を実行し、そしてそのカラム自体がバイナリバイト文字列ではなく非バイナリ文字の文字列を含みます。例えば、CHAR(5) BINARY は、デフォルト文字セットが latin1 だと仮定して、CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin として扱われます。これは、文字セットや照合を持たない5バイトのバイナリ文字列 BINARY(5) とは異なります。

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

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

VARBINARY では、挿入時に詰められる事も、選択時にバイトが削除される事もありません。全てのバイトは、ORDER BYDISTINCT 操作を含め、比較において重要です。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 データタイプの1つを代わりに利用するのが好ましいです。

10.4.3. BLOBTEXT タイプ

BLOB は様々な大きさのデータを保持する事ができる大きいバイナリオブジェクトです。4つの BLOB タイプは、TINYBLOBBLOBMEDIUMBLOB、そしてLONGBLOB です。これらは、保持する事ができる最大長さだけが異なっています。4つの TEXT タイプは、TINYTEXTTEXTMEDIUMTEXT、そして LONGTEXT です。これらは4つの BLOB タイプに対応し、最大長さと必要とする記憶容量は同じです。項10.5. 「データタイプが必要とする記憶容量」を参照してください。TEXTBLOB カラムのレターケースの変換は格納や検索時には行われません。

BLOB カラムはバイナリ文字列(バイト文字列)として扱われます。TEXT カラムは非バイナリ文字列(文字の文字列)として扱われます。BLOB カラムは文字セットを持たないので、ソートと比較は値の中の数値バイトに基づいています。TEXT カラムは文字セットを持つので、値は文字セットの照合に基づいてソート、比較されます。

もし TEXT カラムがインデックスされていたら、インデックス入力比較は最後にスペースが詰められます。これは、もしそのインデックスが固有の値を要求するなら、後続スペースだけが異なる値に対して重複キーエラーが発生するという事を意味します。例えば、もしテーブルが 'a' を含んでいると、'a ' を格納しようとした時重複キーエラーになります。これは BLOB カラムには当てはまりません。

ストリクトモードで実行していない時に、BLOBTEXT カラムに、そのデータタイプの最大長を超える値を指定すると、その値は切り捨てられます。切り捨てられた文字がスペースで無い場合には警告メッセージが表示されます。ストリクトモードを利用すると、警告と共に値が切り捨てられるのではなく、エラーを発生させて、その値を拒否させる事ができます。項4.2.6. 「SQL モード」を参照してください。

あらゆる点で、BLOB カラムを、好きな長さに設定できる VARBINARY カラムだと考える事ができます。同じように、TEXT カラムを VARCHAR カラムと考える事ができます。BLOBTEXT は、次の点で VARBINARYVARCHAR とは異なっています。

  • BLOBTEXT カラムのインデックスには、インデックスプリフィックス長を指定しなければいけません。CHARVARCHAR では、プリフィックス長は任意です。項6.4.3. 「カラムインデックス」を参照してください。

  • BLOBTEXT カラムは DEFAULT を持つ事ができません。

LONGLONG VARCHARMEDIUMTEXT データタイプにマップします。これは互換性の特徴です。もし BINARY 属性を TEXT データタイプと一緒に利用するなら、そのカラムはカラム文字セットのバイナリ照合に指定されます。

MySQLコネクタ/ODBCは BLOB 値を LONGVARBINARY として、TEXT 値をLONGVARCHAR として定義します。

BLOBTEXT 値は大変長くなり得るので、それらを利用する時にいくつかの制約が発生します。

  • カラムの max_sort_length バイトだけがソートに利用されます。max_sort_length のデフォルト値は1024です。この値は、mysqldサーバーを開始する時に --max_sort_length=N オプションを利用して変更する事ができます。項4.2.3. 「システム変数」を参照してください。

    max_sort_length の値をランタイムに増やす事で、ソートとグループの際により多くのバイトを有効にする事ができます。全てのクライアントがそのセッション max_sort_length 変数の値を変更する事ができます。

    mysql> SET max_sort_length = 2000;
    mysql> SELECT id, comment FROM t
        -> ORDER BY comment;
    

    GROUP BYORDER BY を、max_sort_length バイトよりも多くのバイトを有効にしたい時の長い値を含む BLOBTEXT カラム上で利用する別の方法は、カラム値を固定長オブジェクト変換するという方法です。これを行う標準的な方法は SUBSTRING() 関数を利用する方法です。例えば、次のステートメントによって comment カラムの2000バイトがソートの際に考慮されるようになります。

    mysql> SELECT id, SUBSTRING(comment,1,2000) FROM t
        -> ORDER BY SUBSTRING(comment,1,2000);
    
  • BLOBTEXT オブジェクトの最大サイズはそのタイプによって判断されますが、クライアントとサーバーの間で実際に送信できる最大値は、有効メモリの量とコミュニケーションバッファのサイズによって判断されます。max_allowed_packet 変数の値を変更する事でメッセージバッファサイズを変更する事ができますが、サーバとクライアントプログラムの両方に対してその作業を行う必要があります。例えば、mysqlmysqldump の両方がクライアント側の max_allowed_packet 値を変更する事を許可します。項6.5.2. 「サーバパラメータのチューニング」項7.7. 「mysql — MySQL コマンド ライン ツール」項7.12. 「mysqldump — データベースバックアッププログラム」を参照して下さい。ソート中のパケットサイズとデータオブジェクトのサイズを必要とする記憶容量に基づいて比較したい場合には、 項10.5. 「データタイプが必要とする記憶容量」 を参照してください。

BLOBTEXT 値はそれぞれ内部的に別々に割り当てられたオブジェクトによって表現されます。これは、テーブルが開かれた時にそれぞれのカラムに容量が一度割り当てられるという形の、その他全てのデータタイプとは異なります。

時には、メディアファイルのようなバイナリデータを BLOBTEXT カラムに格納する事が望ましい場合もあるでしょう。MySQLの文字列操作関数がこのようなデータを利用するのに役に立つでしょう。項11.3. 「文字列関数」を参照してください。安全とその他の理由の為、これを行うには、アプリケーションユーザに FILE 特権の利用を許可するのではなく、アプリケーションコードを利用して行う方が望ましいです。詳細については、MySQLフォーラムで、様々な言語やプラットフォームで議論する事ができます。(http://forums.mysql.com/)

10.4.4. ENUM タイプ

ENUM は、テーブルを作成する際カラム仕様の中で明確に列挙された許容値リストから選択された値を持つ文字列オブジェクトです。

列挙値は引用された文字列直定数である必要があります。これは、式でも、文字列値を評価するものでもありません。これは、ユーザ変数を列挙値として採用するべきではないという事も意味します。

その値は、特定の条件下では('')や NULL の空の文字列になる事もあります。

  • もし ENUM に無効な値(許容値リストに存在しない文字列)を挿入すると、特別エラー値として空の文字列が代わりに挿入されます。この文字列は、ゼロの数値を持つという点で、「通常の」 空の文字列とは区別することができます。 この後でもう少し詳しく説明します。

    もしストリクトSQLモードが有効なら、無効な ENUM 値を挿入しようとするとエラーが発生します。

  • もし ENUM カラムが NULL の許容を宣言すると、NULL 値はそのカラムにとって正当な値となり、デフォルト値は NULL になります。もし ENUM カラムが NOT NULL を宣言すると、許容値リストの最初の要素がそのデフォルト値となります。

それぞれの列挙値はインデックスを持ちます。

  • カラム仕様の中の許容可能エレメントリストからの値は1から始まる番号がつけられています。

  • 空の文字列エラーインデックス値は0です。これは、どの無効な ENUM 値に行が指定されたのかを見つける為に、次の SELECT ステートメントを利用する事ができるという事を意味します。

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

  • インデックス」 という言葉は、列挙値リストの中の位置だけを表しています。これは、テーブルインデックスとは全く関係がありません。

例えば、ENUM('one', 'two', 'three') として指定されたカラムはここに表されている値のどれでも持つ事ができます。それぞれの値のインデックスも表示されています。

インデックス
NULLNULL
''0
'one'1
'two'2
'three'3

1つの列挙は最大65,535エレメントを持つ事ができます。

テーブルが作成された時に、テーブル定義の中の ENUM メンバー値から後続スペースが自動的に削除されます。

検索された時は、ENUM カラムに格納された値はカラム定義で使用されたレターケースで表示されます。ENUM カラムは文字セットと照合に指定できる事を覚えて置いてください。バイナリ、またはケースに敏感な照合には、カラムに値を指定する時レターケースが考慮されます。

もし ENUM 値を数値コンテキストで検索するなら、カラム値のインデックスは返されます。例えば、このようにして ENUM カラムから数値を検索する事ができます。

mysql> SELECT enum_col+0 FROM tbl_name;

もし 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 値は、カラム仕様にリストされた列挙番号の順番に従ってソートされます。(言い換えると、ENUM 値はそれらのインデックス番号によってソートされるという事になります。)例えば、'a'ENUM('a', 'b') では 'b' の前にソートしますが、'b'ENUM('b', 'a') では 'a' の前にソートします。空の文字列は、空ではない文字列の前にソートし、そして NULL 値はその他の全ての列挙値の前にソートします。予期しない結果を防ぐ為には、ENUM リストをアルファベット順に指定してください。カラムがインデックス番号ではなく、語彙的にソートされる為に、GROUP BY CAST(col AS CHAR)GROUP BY CONCAT(col) を利用する事もできます。

ENUM カラムに有効な全ての値を究明したければ、SHOW COLUMNS FROM tbl_name LIKE enum_col を利用し、アウトプットの Type カラムの中の ENUM 定義を解析してください。

10.4.5. SET タイプ

SET はゼロ、またはそれ以上の値を持つことができる文字列オブジェクトであり、それらはそれぞれ、テーブルが作成された時に指定された許容値リストから選択する必要があります。複数セットメンバーによって成り立つ SET カラム値は、カンマで区切られたメンバーによって指定されます。(‘,’)この結果は、SET メンバー値自体はコンマを含むべきではないという事です。

例えば、SET('one', 'two') NOT NULL として指定されたカラムはここに表されている値のどれでも持つ事ができます。

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

SET は最高64の異なるメンバを持つ事ができます。

テーブルが作成された時に、テーブル定義の中の SET メンバー値から後続スペースが自動的に削除されます。

検索された時は、SET カラムに格納された値はカラム定義で使用されたレターケースで表示されます。SET カラムは文字セットと照合に指定できる事を覚えて置いてください。バイナリ、またはケースに敏感な照合には、カラムに値を指定する時レターケースが考慮されます。

MySQLは、最初のセットメンバに対応する格納値の低位ビットを利用して SET 値を数値で格納します。SET 値を数値コンテキストで検索すると、その値は、カラム値を構成するセットメンバーに対応するビットセットを持ちます。例えば、このようにして SET カラムから数値を検索する事ができます。

mysql> SELECT set_col+0 FROM tbl_name;

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

SET メンバ少数値バイナリ値
'a'10001
'b'20010
'c'40100
'd'81000

もしこのカラムに、バイナリでは 1001 となる 9 を指定すると、最初と4番目の SET 値メンバである '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 値は非 NULL 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';

これらのステートメントの最初の部分が最初のセットメンバを含む値を探します。二つ目の部分が正確に適合する値を探します。二つ目のタイプの比較に注意してください。'val1val2' のセット値を比較すると、'val2val1' を比較するよりも異なる結果が返されます。カラム定義の中でリストされているのと同じ順番で値を指定する必要があります。

SET カラムに有効な全ての値を究明したければ、SHOW COLUMNS FROM tbl_name LIKE set_col を利用し、アウトプットの Type カラムの中の SET 定義を解析してください。

10.5. データタイプが必要とする記憶容量

MySQLにサポートされているデータタイプが必要とする記憶容量がカテゴリごとにリストされています。

MyISAM テーブル内の行の最大サイズは65,534バイトです。BLOBTEXT カラムはそれぞれ、このサイズに対してたった5から9バイトを占めています。

重要NDBCluster ストレージエンジンを利用しているテーブルには、必要とする記憶容量を計算する際考慮するべき 4-byteアラインメント の要因があります。これは、全ての NDB データ格納が4バイトの倍数単位で行われるという意味になります。それ故、—テーブルの中で NDB 以外のストレージエンジンを利用している —カラム値は、格納に15バイトを利用し、NDB テーブルの中で16バイトを必要とします。この要求は、このセクションで紹介される他の全ての条件に当てはまります。例えば、NDBCluster テーブルの中で、 TINYINTSMALLINTMEDIUMINT、そして INTEGER (INT)カラムタイプはそれぞれ1つのレコードにつき4バイトを必要とします。

さらに、クラスタテーブルが必要とする記憶容量を計算する時、NDBCluster ストレージエンジンを利用する全てのテーブルがプライマリキーを要求する事を覚えておく必要があります。もしプライマリキーがユーザによって定義されない時は、NDB によって 「隠れ」 プライマリキーが作成されます。この隠れプライマリキーは1つのテーブルレコードに付き31から35バイトを消費します。

クラスタメモリ要求を計算する時には、MySQLForge で有効な ndb_size.pl ユーティリティが便利です。このPerlスクリプトは現在のMySQL(非クラスタ)データベースに接続し、そのデータベースが NDBCluster ストレージエンジンを利用するとどれくらいの容量を必要とするかについてのレポートを作成します。

数値タイプが必要とする記憶容量

データタイプ要求ストレージ
TINYINT1バイト
SMALLINT2バイト
MEDIUMINT3バイト
INT, INTEGER4バイト
BIGINT8バイト
FLOAT(p)4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT4バイト
DOUBLE [PRECISION], REAL8 バイト
DECIMAL(M,D), NUMERIC(M,D)変動; 後の説明を参照
BIT(M)約(M+7)/8 バイト

DECIMAL (とNUMERIC )カラムの値は、少数第9位(10基準)の桁を4バイトにパックするバイナリフォーマットを利用して表現されます。各値の整数部と端数部の格納は別々に決定されます。9桁の倍ごとに4バイト、「余りの」 桁には4バイトの端数容量がそれぞれ必要です。余りの桁に必要なストレージ要求を以下のテーブルで紹介します。

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

データと時刻タイプが必要とする記憶容量

データタイプ記憶容量
DATE3バイト
DATETIME8バイト
TIMESTAMP4バイト
TIME3バイト
YEAR1バイト

文字列タイプの記憶容量

データタイプ記憶容量
CHAR(M)M バイト、0 <= M <= 255
VARCHAR(M)L + 1 バイト、 一方で L <= M そして 0 <= M <= 255 (下のメモを参照) または L + 2 バイト、一方で L <= M そして 256 <= M <= 65535 (下のメモを参照).
BINARY(M)M バイト、0 <= M <= 255
VARBINARY(M)L + 1 バイト 一方で L <= M そして 0 <= M <= 255 (下のメモを参照) または L + 2 バイト、 一方で L <= M そして 256 <= M <= 65535 (下のメモを参照).
TINYBLOB, TINYTEXTL+1 バイト、一方で L < 28
BLOB, TEXTL+2 バイト 一方で L < 216
MEDIUMBLOB, MEDIUMTEXTL+3 バイト、一方で L < 224
LONGBLOB, LONGTEXTL+4 バイト、一方で L < 232
ENUM('value1','value2',...)列挙値により1か2バイト(最高65,535値)
SET('value1','value2',...)セットメンバの数により、1、2、3、4、または8バイト(最高64メンバ)

CHARVARCHAR、そして TEXT タイプでは、先行テーブルの中の LM の値は文字数として解釈される必要があり、そしてカラム仕様の中のこれらのタイプの長さは文字数を表します。例えば、TINYTEXT 値を格納するには、L 文字に加え1バイトが必要です。

VARCHARVARBINARY、そして BLOBTEXT タイプは可変長タイプです。それぞれが必要とする記憶容量はこれらの要因によって決まります。

  • カラム値の実長さ

  • カラムの可能最大長さ

  • カラムに使用される文字セット

例えば、VARCHAR(10) カラムは最大長さ10の文字列を保持する事ができます。カラムが latin1 文字セットを利用すると仮定すると(一文字につき1バイト)、実際の記憶容量は文字列の長さ(L)と、その文字列の長さを記録する1バイトです。'abcd' 文字列では、L は4で、必要とする記憶容量は5バイトです。もし同じカラムが代わりに VARCHAR(500) として宣言されていたら、その文字列 'abcd' は 4 + 2 = 6 バイトを必要とします。カラム長は255以上なので、プリフィックスには1バイトではなく2バイトが要求されます。

特定の CHARVARCHAR、または TEXT カラム値を格納するのに利用される バイト 数を計算するには、そのカラムに利用される文字セットを考慮に入れなければいけません。特に、utf8 ユニコード文字セットを利用する時には、全ての utf8 文字セットが同じバイト数を利用するわけではないという事を覚えておく必要があります。utf8 文字の異なるカテゴリに利用される格納についての概要は、項9.7. 「Unicodeのサポート」 を参照してください。

:VARCHARVARBINARY カラムの 有効 最大長は65,532です。

MySQL 5.1の NDBCLUSTER ストレージエンジン は可変幅カラムをサポートします。これは、そのような値が4バイトにそろっている場合以外は、MySQLクラスタテーブルの VARCHAR カラムが、他のストレージエンジンを利用した時と同じ容量を必要とするという意味になります。それ故、latin1 文字セットを利用する VARCHAR(50) カラムに格納された 'abcd' 文字列は8バイトを必要とします。(MyISAM テーブルの中の同じカラム値に必要とされる6バイトではない)これは、VARCHAR(50) カラムが、格納された文字列の長さに関わらず、1つのレコードにつき52バイトを必要としていた、NDBCLUSTER の初期バージョンからの変更点を表しています。

BLOBTEXT タイプは、そのタイプの最大長さにより、カラム値の長さを記録する為に1、2、3、またはバイトを必要とします。項10.4.3. 「BLOBTEXT タイプ」を参照してください。

TEXTBLOB カラムは、TEXT カラム内のそれぞれの行が二つの部分で構成される、NDBクラスタストレージエンジンの中で異なる方法で実行されます。そのうちの1つは固定サイズ(256バイト)で、実際に元のテーブルに格納されます。それ以外の物は、隠れテーブルに格納された256バイト以上のデータで構成されます。この二つ目のテーブルの行は常に2,000バイトの長さです。もし サイズ <= 256 (サイズ が行のサイズを表している)時、TEXT カラムのサイズは256で、そうでない時のサイズは256 + サイズ + (2000 – (サイズ – 256) % 2000)です。

ENUM オブジェクトのサイズは異なる列挙値の数によって決定します。1バイトは、最大255の値を持つ列挙に使用されます。2バイトは、256から65,535の間の値を持つ列挙に使用されます。項10.4.4. 「ENUM タイプ」を参照してください。

SET オブジェクトのサイズは異なるセットメンバの数によって決定します。もしセットサイズがN なら、オブジェクトは1、2、3、4、または8バイトに丸められた (N+7)/8 バイトをコピーします。SET は最高64メンバを持つ事ができます。項10.4.5. 「SET タイプ」を参照してください。

10.6. カラムに適したタイプの選択

最適な格納の為には、毎回一番正確なタイプの利用を試みる必要があります。例えば、もし整数カラムが 1 から 99999 の範囲の値に利用されたら、MEDIUMINT UNSIGNED が最適タイプです。要求される値を全て表すタイプの中で、このタイプが使用する容量が一番少ないです。

DECIMAL カラムを利用した全ての基本的な計算 (+, -, *, /) は、65桁の精度で行われます。項10.1.1. 「数値タイプの概要」を参照してください。

もし精度がそれほど重要でなかったり、スピードが最優先事項でなければ、DOUBLE タイプでも十分でしょう。高精度の為に、BIGINT の中に格納されている固定小数点タイプにいつでも変換する事ができます。これで、64ビットの整数で全ての計算をし、その後必要に応じて結果を浮動小数点値に変換する事ができます。

10.7. その他のデータベースエンジンのデータタイプの利用

他のベンダーによってSQL推進の為に書かれたコードを促進する為に、次のテーブルに書かれているようにMySQLはデータタイプをマップします。これらのマッピングにより、他のデータベースシステムからテーブル定義をMySQLにインポートする事が容易になります。

他のベンダータイプMySQLタイプ
BOOL,TINYINT
BOOLEANTINYINT
CHAR VARYING(M)VARCHAR(M)
DECDECIMAL
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)

powered by SEO.CUG.NET