12.10 キャスト関数と演算子

表 12.14 キャスト関数

名前	説明
BINARY	文字列をバイナリ文字列にキャストします
CAST()	値を特定の型としてキャストします
CONVERT()	値を特定の型としてキャストします

• BINARY

  BINARY 演算子は、それに続く文字列をバイナリ文字列にキャストします。これは、カラムの比較を文字ごとでなく、バイトごとに強制的に実行させる簡単な方法です。これにより、カラムが BINARY または BLOB として定義されていない場合でも、比較で大文字と小文字が区別されます。また、BINARY では末尾の空白文字が重要になります。

  mysql> SELECT 'a' = 'A';
          -> 1
  mysql> SELECT BINARY 'a' = 'A';
          -> 0
  mysql> SELECT 'a' = 'a ';
          -> 1
  mysql> SELECT BINARY 'a' = 'a ';
          -> 0

  比較時に、BINARY によって演算全体が影響を受けます。これは、同じ結果を持ついずれかのオペランドの前に指定できます。

  BINARY str は、CAST(str AS BINARY) の略です。

  一部のコンテキストでは、インデックス付きのカラムを BINARY にキャストすると、MySQL はインデックスを効率的に使用できなくなります。

• CAST(expr AS type)

  CONVERT() と同様に、CAST() 関数には任意の型の式が指定され、指定された型の結果値が生成されます。詳細は、CONVERT() の説明を参照してください。

• CONVERT(expr,type), CONVERT(expr USING transcoding_name)

  CONVERT() および CAST() 関数には、任意の型の式が指定され、指定された型の結果値が生成されます。

  結果の type には、次の値のいずれかを指定できます。

  • BINARY[(N)]

  • CHAR[(N)]

  • DATE

  • DATETIME

  • DECIMAL[(M[,D])]

  • SIGNED [INTEGER]

  • TIME

  • UNSIGNED [INTEGER]

  BINARY は、BINARY データ型の文字列を生成します。これによる比較への影響度については、セクション11.4.2「BINARY および VARBINARY 型」を参照してください。オプションの長さ N が指定されている場合に、BINARY(N) を使用すると、キャストで N バイトの引数しか使用されなくなります。値が N バイトよりも短い場合は、N の長さになるまで 0x00 バイトでパディングされます。

  CHAR(N) 句を使用すると、キャストで N 文字の引数しか使用されなくなります。

  CAST() および CONVERT(... USING ...) は標準 SQL の構文です。USING 形式以外の CONVERT() は ODBC の構文です。

  USING とともに CONVERT() を使用すると、さまざまな文字セット間でデータが変換されます。MySQL では、トランスコーディング名は対応する文字セット名と同じです。たとえば、このステートメントは、デフォルトの文字セットの文字列 'abc' を utf8 文字セットの対応する文字列に変換します。

  SELECT CONVERT('abc' USING utf8);

バイナリ文字列には文字セットがなく、大文字と小文字の区別の概念もないため、通常は、大文字と小文字が区別されない方法で、BLOB 値またはその他のバイナリ文字列を比較できません。大文字と小文字が区別されない比較を実行するには、CONVERT() 関数を使用して、値を非バイナリ文字列に変換します。結果の比較には、文字列照合順序が使用されます。たとえば、結果の文字セットに大文字と小文字が区別されない照合順序が含まれる場合は、LIKE 演算子でも大文字と小文字が区別されません。

SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name;

別の文字セットを使用するには、上記のステートメントの latin1 にその名前を代入します。変換された文字列用に特定の照合順序を指定するには、セクション10.1.9.2「CONVERT() と CAST()」で説明するように、CONVERT() の呼び出しのあとに COLLATE 句を使用します。たとえば、latin1_german1_ci を使用する場合は次のとおりです。

SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci
  FROM tbl_name;

CONVERT() は、さまざま文字セットで表される文字列を比較する際に、より一般的に使用できます。

LOWER() (および UPPER()) をバイナリ文字列 (BINARY、VARBINARY、BLOB) に適用しても、何の効果もありません。大文字/小文字の変換を実行するには、バイナリ文字列を非バイナリ文字列に変換します。

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York    | new york                          |
+-------------+-----------------------------------+

キャスト関数は、CREATE TABLE ... SELECT ステートメントで特定の型を持つカラムを作成する際に役立ちます。

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

この関数は、ENUM カラムを語彙順にソートする際に役立つこともあります。通常、ENUM カラムのソートは、内部数値を使用して実行されます。値を CHAR にキャストすると、語彙順にソートされます。

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY) は BINARY str と同じです。CAST(expr AS CHAR) では、式はデフォルトの文字セットを持つ文字列として処理されます。

CONCAT('Date: ',CAST(NOW() AS DATE)) のように、より複雑な式の一部として使用する場合でも、CAST() の結果が変わります。

データを別の書式で抽出するには、CAST() ではなく、代わりに LEFT() または EXTRACT() のような文字列関数を使用してください。セクション12.7「日付および時間関数」を参照してください。

数値のコンテキストで文字列を数値にキャストするには、通常は、文字列値を数字と同様に使用する以外には何もする必要はありません。

mysql> SELECT 1+'1';
       -> 2

算術演算で文字列を使用する場合は、式の評価時に浮動小数点数に変換されます。

文字列のコンテキストで数字を使用する場合は、自動的に数字が文字列に変換されます。

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

MySQL 5.6.4 よりも前では、どのテーブルからも選択されないステートメントの TIMESTAMP 値で明示的に CAST() を使用すると、変換が実行される前に、その値が MySQL 5.6 で文字列として処理されます。これにより、値を数値型にキャストすると、次に示すように値が切り捨てられます。

mysql> SELECT CAST(TIMESTAMP '2014-09-08 18:07:54' AS SIGNED);
+-------------------------------------------------+
| CAST(TIMESTAMP '2014-09-08 18:07:54' AS SIGNED) |
+-------------------------------------------------+
|                                            2014 |
+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '2014-09-08 18:07:54' |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

次に示すように、これはテーブルから行を選択するときには適用されません。

mysql> USE test;

Database changed
mysql> CREATE TABLE c_test (col TIMESTAMP);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO c_test VALUES ('2014-09-08 18:07:54');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT col, CAST(col AS UNSIGNED) AS c_col FROM c_test;
+---------------------+----------------+
| col                 | c_col          |
+---------------------+----------------+
| 2014-09-08 18:07:54 | 20140908180754 |
+---------------------+----------------+
1 row in set (0.00 sec)

MySQL 5.6.4 以降では、次に示すように、どの行も選択されていないクエリーでも、選択されている場合と同様に CAST() で TIMESTAMP 値が処理されます。

mysql> SELECT CAST(TIMESTAMP '2014-09-08 18:07:54' AS SIGNED);
+-------------------------------------------------+
| CAST(TIMESTAMP '2014-09-08 18:05:07' AS SIGNED) |
+-------------------------------------------------+
|                                  20140908180754 |
+-------------------------------------------------+
1 row in set (0.00 sec)

数字から文字列への暗黙的な変換については、セクション12.2「式評価での型変換」を参照してください。

MySQL では、符号付きと符号なしの両方の 64 ビット値を使用した演算がサポートされています。数値演算子 (+ や - など) を使用していて、オペランドのいずれかが符号なし整数である場合は、デフォルトの結果が符号なしになります (セクション12.6.1「算術演算子」を参照してください)。これは、SIGNED または UNSIGNED キャスト演算子を使用して、値をそれぞれ符号付きまたは符号なしの 64 ビット整数にキャストすることでオーバーライドできます。

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

オペランドのいずれかが浮動小数点値である場合は、結果が浮動小数点値になり、上記のルールによる影響を受けません。(このコンテキストでは、DECIMAL カラム値は浮動小数点値とみなされます。)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

SQL モードは、変換演算の結果に影響を与えます。例:

• 「zero」 日付文字列を日付に変換する場合、CONVERT() と CAST() は NULL を返し、NO_ZERO_DATE SQL モードが有効になると警告を発行します。

• 整数の減算では、NO_UNSIGNED_SUBTRACTION SQL モードが有効になっている場合は、オペランドのいずれかが符号なしでも、結果が符号付きになります。

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