MySQL - 型変換・キャスト

概要

MySQLでは、データ型間の変換が頻繁に発生する。
型変換には、暗黙的な変換と明示的な変換の2種類が存在する。

暗黙的な型変換は、演算や比較の際にMySQLが自動的に実行する変換である。
明示的な型変換は、CAST 関数や CONVERT 関数を使用してユーザが指定する変換である。

暗黙的な型変換は、便利である一方で、予期しない結果やパフォーマンスの低下を引き起こす場合がある。
特に、WHERE句でカラムに対して型変換が適用されると、インデックスが使用されなくなる可能性が高い。

MySQL 8.0.17以降では、CAST 関数および CONVERT 関数で使用可能な型が拡張されている。
FLOAT 型、DOUBLE 型への変換がサポートされ、精度指定も可能になった。

また、MySQL 8.0.22以降では、YEAR 型への変換、および AT TIME ZONE 演算子が追加された。

型変換の動作を正確に理解することは、データの整合性を保ち、パフォーマンスを最適化する上で重要である。

暗黙的な型変換

暗黙的な型変換は、MySQLが自動的に実行する型変換である。
演算子や関数が異なる型の引数を受け取った場合、MySQLは自動的に型を変換して処理を行う。

暗黙的変換が発生する場面

暗黙的な型変換は、以下に示す場面で発生する。

暗黙的な型変換が発生する場面
場面	説明
比較演算子を使用する場合	`=`、`!=`、`<`、`>`、`<=`、`>=` 等
算術演算子を使用する場合	`+`、`-`、`*`、`/`、`%` 等
関数の引数として使用する場合	`CONCAT` 関数、`SUBSTRING` 関数等
INSERT文またはUPDATE文での値代入	カラムの型と異なる型の値を挿入または更新する場合
WHERE句での条件評価	カラムと異なる型の値で比較する場合

暗黙的変換の例を以下に示す。

暗黙的変換は、コードの記述を簡潔にする一方で、予期しない動作を引き起こす可能性がある。

 SELECT '100' + 50;
 -- 結果: 150 (文字列 '100' が数値 100 に変換される)
 
 SELECT CONCAT(123, ' items');
 -- 結果: '123 items' (数値 123 が文字列 '123' に変換される)
 
 SELECT * FROM users WHERE user_id = '42';
 -- user_idが整数型の場合、文字列 '42' が数値 42 に変換される

文字列から数値への暗黙変換

文字列が数値として解釈される場合、MySQLは文字列の先頭から数値として読み取れる部分までを変換する。

変換ルールを以下に示す。

文字列から数値への変換規則
条件	説明
先頭が数字の場合	数値として読み取れる部分まで変換
先頭が数字でない場合	0に変換
指数表記 (科学的表記法) をサポート	`1.5e2` は 150 に変換
カンマ区切りは数値として認識されない	`1,234,567` は 1 に変換 (カンマで終了)

文字列から数値への変換例を以下に示す。

このルールにより、予期しない結果が生じる可能性がある。
例えば、'1,234,567' という文字列は、1に変換される。

 SELECT '123' + 0;
 -- 結果: 123
 
 SELECT '123abc' + 0;
 -- 結果: 123 (先頭の数値部分のみ変換)
 
 SELECT 'abc123' + 0;
 -- 結果: 0 (先頭が数字でないため0に変換)
 
 SELECT '1.5e2' + 0;
 -- 結果: 150 (指数表記を解釈)
 
 SELECT '1,234,567' + 0;
 -- 結果: 1 (カンマで数値部分が終了)
 
 SELECT '  456  ' + 0;
 -- 結果: 456 (前後の空白は無視)

数値から文字列への暗黙変換

数値が文字列として解釈される場面では、MySQLは数値を文字列に自動変換する。

数値から文字列への変換は、比較的直感的である。

文字列コンテキストの例を以下に示す。

 SELECT CONCAT(2, ' items');
 -- 結果: '2 items' (数値 2 が文字列 '2' に変換)
 
 SELECT CONCAT(123.45, ' dollars');
 -- 結果: '123.45 dollars'
 
 SELECT CONCAT(NULL, ' test');
 -- 結果: NULL

日付型の暗黙変換

MySQLは、日付型の文字列表現を"緩やかな"フォーマットで受け入れる。

日付型の暗黙変換の特徴を以下に示す。

文字列から日付/時刻への変換規則
規則	説明
区切り文字の柔軟性	`-`、`/`、`#` 等の区切り文字を使用可能
区切り文字なしの形式	`20240815` のような形式も認識
時刻部分のオプション	日付のみ、または日付と時刻の両方を指定可能

日付型の暗黙変換の例を以下に示す。

ただし、日本語の日付表記 (例: 2024年8月15日) は認識されない。
このような形式の日付を変換する場合は、STR_TO_DATE 関数を使用する。

 SELECT CAST('2024-08-15' AS DATE);
 -- 結果: 2024-08-15
 
 SELECT CAST('2024/08/15' AS DATE);
 -- 結果: 2024-08-15 (区切り文字 / を認識)
 
 SELECT CAST('2024#08#15' AS DATE);
 -- 結果: 2024-08-15 (区切り文字 # を認識)
 
 SELECT CAST('20240815' AS DATE);
 -- 結果: 2024-08-15 (区切り文字なしも認識)
 
 SELECT CAST('2024-08-15 14:30:00' AS DATETIME);
 -- 結果: 2024-08-15 14:30:00

比較時の型変換ルール

MySQLは、異なる型の値を比較する際に、一定のルールに従って型変換を実行する。

型変換の優先順位

MySQLは、以下に示す優先順位で型変換を実行する。

型変換の優先順位
優先順位	条件	変換規則
1	NULLの場合	結果は `NULL` (`<=>` 演算子は例外)
2	両方が文字列の場合	文字列として比較
3	両方が整数の場合	整数として比較
4	DECIMALが含まれる場合	DECIMAL精度ルールに従って比較
5	その他の場合	浮動小数点数として比較

比較時の型変換の例を以下に示す。

 SELECT 1 = '1';
 -- 結果: 1 (true) (文字列 '1' が数値 1 に変換)
 
 SELECT 0 = 'abc';
 -- 結果: 1 (true) (文字列 'abc' が数値 0 に変換)
 
 SELECT 1 = 1.0;
 -- 結果: 1 (true) (整数と浮動小数点の比較)
 
 SELECT 'abc' = 'ABC';
 -- 結果: 1 (true) (デフォルトの照合順序は大文字小文字を区別しない)

文字列と数値の比較

文字列と数値を比較する場合、文字列が数値に変換される。

文字列と数値の比較例を以下に示す。

この動作により、予期しない比較結果が生じる可能性がある。
特に、'abc' = 0 が true となる点に注意すること。

 SELECT 1 > '6x';
 -- 結果: 0 (false) ('6x' は 6 に変換、1 > 6 は false)
 
 SELECT 7 > '6x';
 -- 結果: 1 (true) ('6x' は 6 に変換、7 > 6 は true)
 
 SELECT 0 = 'x6';
 -- 結果: 1 (true) ('x6' は 0 に変換、0 = 0 は true)
 
 SELECT 'abc' = 0;
 -- 結果: 1 (true) ('abc' は 0 に変換)
 
 SELECT '123' > 99;
 -- 結果: 1 (true) ('123' は 123 に変換、123 > 99 は true)

照合順序と比較

文字列同士の比較では、照合順序 (Collation) が比較結果に影響する。

大文字小文字を区別する比較を行う場合は、BINARY 演算子または COLLATE utf8mb4_bin を使用する。

照合順序の影響を以下に示す。

 SELECT 'abc' = 'ABC';
 -- 結果: 1 (true) (デフォルトの照合順序は大文字小文字を区別しない)
 
 SELECT 'abc' = 'ABC' COLLATE utf8mb4_bin;
 -- 結果: 0 (false) (バイナリ比較では区別)
 
 SELECT BINARY 'abc' = 'ABC';
 -- 結果: 0 (false) (BINARY演算子でバイナリ比較を強制)

明示的な型変換

明示的な型変換は、CAST 関数または CONVERT 関数を使用して実行する。
これらの関数により、ユーザが意図した型変換を明確に指定できる。

CAST関数

CAST 関数は、SQL標準の型変換関数である。

基本構文を以下に示す。

 CAST(expr AS type [ARRAY])

`CAST`関数の引数
引数	説明
`expr`	変換する値または式
`type`	変換先の型
`ARRAY`	JSON配列への変換 (オプション、MySQL 8.0.17以降)

CAST 関数の使用例を以下に示す。

 SELECT CAST('123' AS SIGNED);
 -- 結果: 123 (文字列を符号付き整数に変換)
 
 SELECT CAST(123.456 AS DECIMAL(5,2));
 -- 結果: 123.46 (浮動小数点数を固定小数点数に変換、四捨五入)
 
 SELECT CAST('2024-08-15' AS DATE);
 -- 結果: 2024-08-15 (文字列を日付型に変換)
 
 SELECT CAST('2024-08-15 14:30:00' AS DATETIME);
 -- 結果: 2024-08-15 14:30:00 (文字列を日時型に変換)
 
 SELECT CAST(123 AS CHAR);
 -- 結果: '123' (数値を文字列に変換)

CONVERT関数

CONVERT 関数は、MySQL独自の型変換関数である。
CONVERT 関数には、2つの構文が存在する。

基本構文を以下に示す。

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

`CONVERT`関数の構文
構文	説明
`CONVERT(expr, type)`	`CAST`関数と同様の型変換
`CONVERT(expr USING transcoding_name)`	文字セット変換

CONVERT 関数の使用例を以下に示す。

 SELECT CONVERT('123', SIGNED);
 -- 結果: 123
 
 SELECT CONVERT(123.456, DECIMAL(5,2));
 -- 結果: 123.46
 
 SELECT CONVERT('Hello' USING utf8mb4);
 -- 結果: 'Hello' (文字セットをutf8mb4に変換)
 
 SELECT CONVERT('データ' USING latin1);
 -- エラーまたは文字化け (latin1は日本語をサポートしない)

文字セット指定付き CAST 関数の構文を以下に示す。

 CAST(expr AS CHAR CHARACTER SET charset_name)

文字セット指定の例を以下に示す。

 SELECT CAST('Hello' AS CHAR CHARACTER SET utf8mb4);
 -- 結果: 'Hello'
 
 SELECT CAST('データベース' AS CHAR CHARACTER SET utf8mb4);
 -- 結果: 'データベース'

CAST / CONVERTで使用可能な型の詳細

CAST 関数および CONVERT 関数では、以下に示す型への変換がサポートされている。

サポートされる型の一覧
型	説明	追加されたバージョン
`BINARY[(N)]`	バイナリ文字列型
`CHAR[(N)]`	固定長文字列型
`DATE`	日付型
`DATETIME`	日時型
`TIME`	時刻型
`DECIMAL[(M[,D])]`	固定小数点型
`SIGNED [INTEGER]`	符号付き整数型
`UNSIGNED [INTEGER]`	符号なし整数型
`FLOAT[(p)]`	単精度浮動小数点型	MySQL 8.0.17以降
`DOUBLE`	倍精度浮動小数点型	MySQL 8.0.17以降
`REAL`	REAL型 (FLOATまたはDOUBLE)	MySQL 8.0.17以降
`JSON`	JSON型
`YEAR`	年型	MySQL 8.0.22以降
空間型	Point、LineString等	MySQL 8.0.24以降

BINARY / CHAR

BINARY 型および CHAR 型への変換は、文字列またはバイナリ文字列を生成する。

BINARY[(N)] の場合、N はバイト数を指定する。
CHAR[(N)] の場合、N は文字数ではなくバイト数を指定する点に注意すること。

 SELECT CAST(123 AS CHAR);
 -- 結果: '123'
 
 SELECT CAST('Hello' AS BINARY(10));
 -- 結果: 'Hello\0\0\0\0\0' (10バイトに拡張、NULLバイトでパディング)
 
 SELECT CAST('Hello World' AS CHAR(5));
 -- 結果: 'Hello' (5バイトに切り詰め)

数値型

数値型への変換には、整数型、固定小数点型、浮動小数点型が含まれる。

数値型への変換
型	説明
`SIGNED [INTEGER]`	符号付き整数型 (-9223372036854775808 ～ 9223372036854775807)
`UNSIGNED [INTEGER]`	符号なし整数型 (0 ～ 18446744073709551615)
`DECIMAL[(M[,D])]`	固定小数点型 (M: 精度、D: 小数点以下桁数)
`FLOAT[(p)]`	単精度浮動小数点型 (p: 精度 0-53)
`DOUBLE`	倍精度浮動小数点型
`REAL`	REAL型 (REAL_AS_FLOATモード設定時はFLOAT、それ以外はDOUBLE)

FLOAT[(p)] の精度指定について以下に示す。

`FLOAT[(p)]` の精度指定
精度 `p` の範囲	変換先
0〜24	`FLOAT` 型 (単精度浮動小数点)
25〜53	`DOUBLE` 型 (倍精度浮動小数点)

数値型への変換の例を以下に示す。

 SELECT CAST('123' AS SIGNED);
 -- 結果: 123
 
 SELECT CAST('-456' AS SIGNED);
 -- 結果: -456
 
 SELECT CAST('789' AS UNSIGNED);
 -- 結果: 789
 
 SELECT CAST('-123' AS UNSIGNED);
 -- 結果: 18446744073709551493 (オーバーフロー)
 
 SELECT CAST(123.456 AS DECIMAL(5,2));
 -- 結果: 123.46 (四捨五入)
 
 SELECT CAST('123.456' AS FLOAT);
 -- 結果: 123.456
 
 SELECT CAST('123.456' AS DOUBLE);
 -- 結果: 123.456
 
 SELECT CAST('1.23e2' AS DOUBLE);
 -- 結果: 123 (指数表記を解釈)

日付・時刻型

日付型および時刻型への変換には、以下に示す型が含まれる。

日付・時刻型への変換
型	形式	説明
`DATE`	YYYY-MM-DD	日付型
`TIME`	HH:MM:SS	時刻型
`DATETIME`	YYYY-MM-DD HH:MM:SS	日時型
`YEAR`	YYYY	年型 (MySQL 8.0.22以降)

日付・時刻型への変換の例を以下に示す。

 SELECT CAST('2024-08-15' AS DATE);
 -- 結果: 2024-08-15
 
 SELECT CAST('14:30:00' AS TIME);
 -- 結果: 14:30:00
 
 SELECT CAST('2024-08-15 14:30:00' AS DATETIME);
 -- 結果: 2024-08-15 14:30:00
 
 SELECT CAST('2024' AS YEAR);
 -- 結果: 2024 (MySQL 8.0.22以降)
 
 SELECT CAST('2024-08-15 14:30:00' AS DATE);
 -- 結果: 2024-08-15 (時刻部分は切り捨て)
 
 SELECT CAST('2024-08-15 14:30:00' AS TIME);
 -- 結果: 14:30:00 (日付部分は切り捨て)

AT TIME ZONE 演算子による時刻変換 (MySQL 8.0.22以降) を以下に示す。

 SELECT CAST('2024-08-15 14:30:00' AS DATETIME) AT TIME ZONE '+00:00';
 -- 結果: UTCタイムゾーンに変換

JSON

JSON 型への変換は、文字列、数値、オブジェクト、配列をJSON形式に変換する。

 SELECT CAST('{}' AS JSON);
 -- 結果: {} (空のJSONオブジェクト)
 
 SELECT CAST('[1,2,3]' AS JSON);
 -- 結果: [1,2,3] (JSON配列)
 
 SELECT CAST(123 AS JSON);
 -- 結果: 123 (JSON数値)
 
 SELECT CAST('"Hello"' AS JSON);
 -- 結果: "Hello" (JSON文字列)
 
 SELECT CAST('{"name":"Alice","age":30}' AS JSON);
 -- 結果: {"name":"Alice","age":30} (JSONオブジェクト)

JSON関連の型変換

MySQL 8.0では、JSON型と他の型の間での変換が強化されている。

JSON型への変換

JSON型への変換は、CAST 関数を使用して実行する。

 SELECT CAST('{"name":"Alice"}' AS JSON);
 -- 結果: {"name":"Alice"}
 
 SELECT CAST('[1,2,3,4,5]' AS JSON);
 -- 結果: [1,2,3,4,5]
 
 SELECT CAST(123 AS JSON);
 -- 結果: 123
 
 SELECT CAST(true AS JSON);
 -- 結果: true
 
 SELECT CAST(NULL AS JSON);
 -- 結果: null (JSONのnull値)

JSON型からの変換

JSON型から他の型への変換には、JSON_UNQUOTE 関数と JSON_EXTRACT 関数を組み合わせる方法がある。

 SET @json = '{"name":"Alice","age":30}';
 
 SELECT JSON_EXTRACT(@json, '$.name');
 -- 結果: "Alice" (JSON文字列、ダブルクォート付き)
 
 SELECT JSON_UNQUOTE(JSON_EXTRACT(@json, '$.name'));
 -- 結果: Alice (ダブルクォートを除去)
 
 SELECT CAST(JSON_EXTRACT(@json, '$.age') AS SIGNED);
 -- 結果: 30 (数値に変換)

MySQL 8.0.21以降では、JSON_VALUE 関数が利用可能である。

JSON_VALUE 関数の構文を以下に示す。

 JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

`JSON_VALUE`関数の引数
引数	説明
`json_doc`	JSON文書
`path`	JSONパス式
`RETURNING type`	戻り型 (オプション、デフォルト: VARCHAR(512))
`on_empty`	値が存在しない場合の動作
`on_error`	エラー発生時の動作

JSON_VALUE 関数で使用可能な型を以下に示す。

JSON_VALUE関数で使用可能な型
分類	型
数値型	`FLOAT`、`DOUBLE`、`DECIMAL`、`SIGNED`、`UNSIGNED`
日付・時刻型	`DATE`、`TIME`、`DATETIME`、`YEAR`
文字列型	`CHAR`
その他	`JSON`

JSON_VALUE 関数の使用例を以下に示す。

 SET @json = '{"name":"Alice","age":30,"salary":50000.50}';
 
 SELECT JSON_VALUE(@json, '$.name');
 -- 結果: 'Alice' (デフォルトはVARCHAR(512))
 
 SELECT JSON_VALUE(@json, '$.age' RETURNING SIGNED);
 -- 結果: 30 (符号付き整数に変換)
 
 SELECT JSON_VALUE(@json, '$.salary' RETURNING DECIMAL(10,2));
 -- 結果: 50000.50 (固定小数点数に変換)
 
 SELECT JSON_VALUE(@json, '$.unknown' RETURNING SIGNED NULL ON EMPTY);
 -- 結果: NULL (値が存在しない場合はNULLを返す)
 
 SELECT JSON_VALUE(@json, '$.unknown' RETURNING SIGNED DEFAULT 0 ON EMPTY);
 -- 結果: 0 (値が存在しない場合は0を返す)
 
 SELECT JSON_VALUE(@json, '$.invalid' RETURNING SIGNED ERROR ON ERROR);
 -- エラー発生 (変換エラー時にエラーを返す)

ON EMPTY 句および ON ERROR 句の動作を以下に示す。

ON EMPTY / ON ERROR句の動作
句	説明
`NULL ON EMPTY`	値が存在しない場合、NULLを返す (デフォルト)
`DEFAULT value ON EMPTY`	値が存在しない場合、指定したデフォルト値を返す
`ERROR ON EMPTY`	値が存在しない場合、エラーを発生
`NULL ON ERROR`	変換エラー時、NULLを返す (デフォルト)
`DEFAULT value ON ERROR`	変換エラー時、指定したデフォルト値を返す
`ERROR ON ERROR`	変換エラー時、エラーを発生

※注意
ON EMPTY 句は、ON ERROR 句より前に置く必要がある。

JSONと他の型の比較

JSON型と他の型を比較する場合、MySQLは自動的に型変換を実行する。

 SET @json = '{"value":123}';
 
 SELECT JSON_EXTRACT(@json, '$.value') = 123;
 -- 結果: 1 (true) (JSON数値と整数を比較)
 
 SELECT JSON_EXTRACT(@json, '$.value') = '123';
 -- 結果: 1 (true) (JSON数値と文字列を比較)

型変換に関する注意点

データ損失のリスク

型変換により、データが損失または変更される場合がある。

データ損失が発生する例を以下に示す。

固定小数点数から整数への変換

小数部が切り捨てられる

 SELECT CAST(123.456 AS SIGNED);
 -- 結果: 123 (小数部 .456 が切り捨て)

浮動小数点数から固定小数点数への変換
丸め誤差が発生する可能性
```
 SELECT CAST(123.456789 AS DECIMAL(5,2));
 -- 結果: 123.46 (四捨五入)
```

文字列から整数への変換

数値以外の文字が喪失

 SELECT CAST('123abc' AS SIGNED);
 -- 結果: 123 ('abc' が喪失)
 
 SELECT CAST('abc123' AS SIGNED);
 -- 結果: 0 (全ての文字が喪失)

日時型から日付型への変換

時刻部分が切り捨てられる

 SELECT CAST('2024-08-15 14:30:00' AS DATE);
 -- 結果: 2024-08-15 (時刻部分 14:30:00 が切り捨て)

インデックスへの影響

WHERE句でカラムに対して型変換を適用すると、インデックスが使用されなくなる。
これにより、クエリのパフォーマンスが大幅に低下する可能性がある。

良い例

 -- 値側で型変換を行う
 SELECT * FROM users WHERE user_id = 123;
 -- インデックスが使用される
 
 -- または、CASTを使わずに比較
 SELECT * FROM users WHERE user_id = CAST('123' AS SIGNED);
 -- インデックスが使用される

悪い例

 -- user_idが整数型の場合
 SELECT * FROM users WHERE CAST(user_id AS CHAR) = '123';
 -- インデックスが使用されない

カラムに対して関数または型変換を適用しないことが、インデックスを有効活用するための基本原則である。

NULLの扱い

CAST 関数および CONVERT 関数は、NULL を変換すると NULL を返す。

 SELECT CAST(NULL AS SIGNED);
 -- 結果: NULL
 
 SELECT CAST(NULL AS CHAR);
 -- 結果: NULL
 
 SELECT CAST(NULL AS DATE);
 -- 結果: NULL

NULL を別の値に置き換える場合は、COALESCE 関数または IFNULL 関数を使用する。

 SELECT COALESCE(CAST(NULL AS SIGNED), 0);
 -- 結果: 0
 
 SELECT IFNULL(CAST(NULL AS SIGNED), -1);
 -- 結果: -1

文字列と数値の比較でインデックスが使用されない

 -- user_idが整数型インデックスの場合
 SELECT * FROM users WHERE user_id = '42';
 -- 文字列 '42' が数値 42 に変換されるが、インデックスは使用される
 
 -- しかし、以下はインデックスが使用されない
 SELECT * FROM users WHERE CAST(user_id AS CHAR) = '42';

カンマ区切り数値の変換

 SELECT '1,234,567' + 0;
 -- 結果: 1 (カンマで数値部分が終了)
 
 -- 正しく変換するには、カンマを除去する
 SELECT CAST(REPLACE('1,234,567', ',', '') AS SIGNED);
 -- 結果: 1234567

浮動小数点数の比較

 SELECT 0.1 + 0.2 = 0.3;
 -- 結果: 0 (false) (浮動小数点誤差により不一致)
 
 SELECT ABS((0.1 + 0.2) - 0.3) < 0.0001;
 -- 結果: 1 (true) (誤差を考慮した比較)
 
 -- または、DECIMAL型を使用
 SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) = CAST(0.3 AS DECIMAL(10,2));
 -- 結果: 1 (true)

日本語日付のCAST不可

 SELECT CAST('2024年8月15日' AS DATE);
 -- 結果: NULL (日本語日付は認識されない)
 
 -- STR_TO_DATE関数を使用
 SELECT STR_TO_DATE('2024年8月15日', '%Y年%m月%d日');
 -- 結果: 2024-08-15

大きな整数と浮動小数点の比較

 SELECT 9223372036854775807 = 9223372036854775808.0;
 -- 結果: 1 (true) (浮動小数点の精度制限により誤った結果)
 
 -- 整数同士で比較する
 SELECT 9223372036854775807 = CAST(9223372036854775808.0 AS SIGNED);
 -- 結果: 0 (false) (ただし、オーバーフローに注意)