MariaDBでデータ変換を極める：CONVERT関数と代替手段の全知識

2025-05-27

データ型の変換 (Type Conversion): あるデータ型の値を別のデータ型に変換します。
文字セットの変換 (Character Set Conversion): 文字列のエンコーディング（文字コード）を変換します。

データ型の変換 (Type Conversion)

CONVERT(値, データ型)という形式で使われます。指定された値を、指定されたデータ型に変換します。

例

数値から文字列へ

SELECT CONVERT(123, CHAR); -- 結果: '123' (文字列型)

文字列から数値へ

SELECT CONVERT('123', SIGNED); -- 結果: 123 (符号付き整数)
SELECT CONVERT('3.14', DECIMAL(3, 2)); -- 結果: 3.14 (小数点以下2桁の10進数)

サポートされる主なデータ型

BINARY (バイナリ文字列)
UNSIGNED (符号なし64ビット整数)
SIGNED (符号付き64ビット整数)
NCHAR (CHARと同様ですが、国家文字セットを使用)
CHAR[(N)] (Nは固定長文字列の長さ)
DECIMAL[(M, D)] (Mは桁数、Dは小数点以下の桁数)
TIME
DATETIME
DATE

文字セットの変換 (Character Set Conversion)

CONVERT(expr USING 文字セット名)という形式で使われます。これは、文字列exprを、指定された文字セット名のエンコーディングに変換します。

この用途は、特にデータベースやテーブル、カラムの文字セットと、入力されるデータの文字セットが異なる場合に重要です。例えば、ウェブアプリケーションからUTF-8のデータが送られてくるが、データベースが異なる文字セットで設定されている場合などに利用できます。

例

SELECT CONVERT('日本語' USING utf8mb4);

MariaDBにはCAST関数という、CONVERT関数と非常によく似た機能を持つ関数もあります。

CONVERT: データ型の変換と文字セットの変換の両方に使えます。
CAST(値 AS データ型): 主にデータ型の変換に使用されます。

多くのデータ型変換においては、CASTとCONVERTは同じように機能します。ただし、文字セットの変換を行う場合はCONVERT(expr USING charset)の形式を使用する必要があるため、この点がCONVERTの大きな特徴と言えます。

データ変換時のエラー

よくあるエラー

NULL値の扱い
CONVERTはNULL値をNULLに変換します。これはエラーではありませんが、意図しない結果になる可能性があります。
```
SELECT CONVERT(NULL, SIGNED);
-- 結果: NULL
```
範囲外の値
変換先のデータ型の許容範囲を超える値を変換しようとするとエラーになったり、切り捨てられたりします。
- 例: DECIMAL(3,0) に 12345 を変換しようとする。
```
SELECT CONVERT(12345, DECIMAL(3,0));
-- 結果: 999 または警告 (SQLモードによる)
```
データ形式の不一致
変換先のデータ型に合わない形式のデータを変換しようとするとエラーになります。
- 例: 'ABC' を SIGNED (整数) に変換しようとする。
- 例: '2023/01/01' を DATE 型に変換しようとしたが、デフォルトの日付形式 ('YYYY-MM-DD') と異なる場合。
```
SELECT CONVERT('ABC', SIGNED);
-- エラー例: #1367 - Illegal non-numeric value for type SIGNED
SELECT CONVERT('2023/01/01', DATE);
-- 結果: NULL または警告 (SQLモードによる)
```

トラブルシューティング

エラーログの確認
MariaDBのエラーログ (error.log) に詳細なエラーメッセージが出力されている場合があります。これを参照して、問題の原因を特定します。
SQLモードの確認
MariaDBのSQLモード（例: STRICT_TRANS_TABLES）によっては、変換エラーが警告ではなく、エラーとして扱われ、クエリが中断されることがあります。必要に応じてSQLモードを確認・調整します。
```
SELECT @@sql_mode;
```
STR_TO_DATE()やDATE_FORMAT()との併用
日付や時間の文字列を扱う場合、CONVERTだけでなく、STR_TO_DATE() (文字列から日付へ) や DATE_FORMAT() (日付から文字列へ) を組み合わせて使うことで、柔軟な形式に対応できます。
```
-- 正しい日付形式に変換してからDATE型にする
SELECT CONVERT(STR_TO_DATE('2023年01月15日', '%Y年%m月%d日'), DATE);
```
適切なデータ型の選択
変換先のデータ型が、変換しようとしている値の範囲や精度を十分にカバーできるかを確認します。
入力データの確認
変換しようとしている値が、変換先のデータ型として妥当な形式であるかを確認します。特に、文字列から数値や日付への変換では、入力文字列の形式が重要です。

文字セット変換時のエラー

よくあるエラー

不正なシーケンス
入力文字列が指定された文字セットのルールに従っていない場合（例: UTF-8の途中でバイトシーケンスが切れているなど）、エラーが発生することがあります。
文字の欠落 (Loss of Data)
変換先の文字セットが、元の文字セットに含まれる特定の文字を表現できない場合、その文字が?に置換されたり、欠落したりすることがあります。
- 例: UTF-8で表現される絵文字などを、より表現範囲の狭い文字セット（例: latin1 や sjis）に変換しようとする場合。
文字化け (Mojibake)
文字セットの指定が間違っている場合、データは変換されたように見えても、表示すると意味不明な文字（文字化け）になることがあります。これは、特にエラーメッセージとして表示されないため、発見が難しい場合があります。
- 例: UTF-8の文字列をEUC-JPとして表示しようとする、あるいはその逆。

CONVERT(expr USING charset_name) の正しい使い方
CONVERTで文字セット変換を行う際は、USINGキーワードを使って正しい文字セット名を指定しているかを確認します。
```
SELECT CONVERT(binary_data USING utf8mb4);
```
バイナリデータ（BLOB型など）から文字列に変換する場合、この方法は特に有効です。
utf8mb4 の利用推奨
MariaDBの標準的な文字セットとして、絵文字なども含め幅広い文字を扱える utf8mb4 を利用することを強く推奨します。これにより、多くの文字欠落の問題を回避できます。
元のデータの文字セットの特定
変換しようとしている文字列が、実際にどの文字セットでエンコードされているかを正確に把握することが重要です。これは、ファイルからのインポート時や、他のシステムからのデータ連携時に特に問題になります。
クライアント接続時の文字セット確認
クライアントがMariaDBに接続する際の文字セットが正しく設定されているかを確認します。これは、SET NAMES charset_name; や、接続ドライバーの設定で行われます。
```
SHOW VARIABLES LIKE 'character_set%';
```
特に重要なのは character_set_client、character_set_connection、character_set_results です。これらが不一致だと文字化けの原因になります。
データベース、テーブル、カラムの文字セット確認
SHOW CREATE DATABASE database_name;、SHOW CREATE TABLE table_name;、SHOW FULL COLUMNS FROM table_name; コマンドを使用して、それぞれの文字セット設定を確認します。

公式ドキュメントの参照
MariaDBの公式ドキュメントは、CONVERT関数や文字セットに関する詳細な情報、およびエラーメッセージの解説を提供しています。
最小限の再現コード
エラーが発生する場合、問題のSQL文を最小限のデータで再現できる形に切り詰めてテストすると、原因の特定が容易になります。
バージョン確認
使用しているMariaDBのバージョンを確認します（SELECT VERSION();）。古いバージョンでは、機能の制限や既知のバグがある可能性があります。

CONVERT(値, データ型) の形式で、あるデータ型の値を別のデータ型に変換します。

例1: 文字列を数値に変換する

ウェブフォームからの入力など、数値として扱いたいデータが文字列として渡される場合に便利です。

-- 文字列を符号付き整数 (SIGNED) に変換
SELECT CONVERT('12345', SIGNED) AS converted_signed_int;

-- 文字列を小数点数 (DECIMAL) に変換
SELECT CONVERT('98.76', DECIMAL(5, 2)) AS converted_decimal;

-- 不正な文字列を数値に変換しようとすると、結果は0になるか、SQLモードによってはエラーになる
SELECT CONVERT('abc', SIGNED) AS invalid_conversion_to_int;

出力例

+------------------------+
| converted_signed_int   |
+------------------------+
|                  12345 |
+------------------------+

+-------------------+
| converted_decimal |
+-------------------+
|             98.76 |
+-------------------+

+---------------------------+
| invalid_conversion_to_int |
+---------------------------+
|                         0 | -- SQLモードによる。STRICT_TRANS_TABLESではエラーになる場合がある
+---------------------------+

例2: 文字列を日付/時刻型に変換する

CSVファイルからのインポートや、特定の形式で保存された日付文字列をデータベースの日付型に変換する場合に役立ちます。

-- 文字列を日付 (DATE) 型に変換
SELECT CONVERT('2023-01-15', DATE) AS converted_date;

-- 文字列を時間 (TIME) 型に変換
SELECT CONVERT('14:30:00', TIME) AS converted_time;

-- 文字列を日時 (DATETIME) 型に変換
SELECT CONVERT('2023-01-15 14:30:00', DATETIME) AS converted_datetime;

-- 'YYYY/MM/DD' 形式の文字列を DATE 型に変換（直接はできないため、STR_TO_DATEと組み合わせる）
SELECT CONVERT(STR_TO_DATE('2023/01/15', '%Y/%m/%d'), DATE) AS converted_date_with_str_to_date;

出力例

+----------------+
| converted_date |
+----------------+
| 2023-01-15     |
+----------------+

+----------------+
| converted_time |
+----------------+
| 14:30:00       |
+----------------+

+--------------------+
| converted_datetime |
+--------------------+
| 2023-01-15 14:30:00|
+--------------------+

+-------------------------------+
| converted_date_with_str_to_date |
+-------------------------------+
| 2023-01-15                    |
+-------------------------------+

例3: 数値を文字列に変換する

数値を表示目的で文字列として扱いたい場合や、文字列結合を行う場合などに使用します。

-- 整数を文字列 (CHAR) に変換
SELECT CONVERT(12345, CHAR) AS converted_char_from_int;

-- 小数点を文字列 (CHAR) に変換
SELECT CONVERT(3.14159, CHAR(10)) AS converted_char_from_float; -- 長さを指定することも可能

出力例

+-------------------------+
| converted_char_from_int |
+-------------------------+
| 12345                   |
+-------------------------+

+---------------------------+
| converted_char_from_float |
+---------------------------+
| 3.14159                   |
+---------------------------+

CONVERT(expr USING 文字セット名) の形式で、文字列のエンコーディング（文字コード）を変換します。これは、異なる文字セットのシステム間でデータをやり取りする際に特に重要です。

例4: 文字列の文字セットを変換する

データベースの文字セットと異なる文字セットで入力された文字列を、データベースの文字セットに合わせる場合などに利用します。

前提
MariaDBのセッション文字セットがutf8mb4であると仮定します。

-- 現在の文字セット (例: utf8mb4) での文字列
SELECT 'こんにちは' AS original_string;

-- 文字列を明示的にutf8mb4に変換 (通常、現在の文字セットと同じなら変化なし)
SELECT CONVERT('こんにちは' USING utf8mb4) AS converted_to_utf8mb4;

-- 他の文字セットに変換 (例: sjis) - 表示される文字は変わらないが、内部エンコーディングが変わる
-- 注意: sjisで表現できない文字は?に置き換わる可能性がある
SELECT CONVERT('こんにちは' USING sjis) AS converted_to_sjis;

-- バイナリデータから特定の文字セットの文字列に変換
-- これは、例えば、ある文字セットでエンコードされたバイナリデータを読み込む場合に使う
-- ここでは例として、utf8mb4でエンコードされたバイナリデータと仮定
SELECT CONVERT(UNHEX('E38193E38293E381ABE381A1E381AF') USING utf8mb4) AS binary_to_string_utf8;
-- UNHEX('E38193E38293E381ABE381A1E381AF') はUTF-8の「こんにちは」のバイナリ表現

出力例

+-------------------+
| original_string   |
+-------------------+
| こんにちは        |
+-------------------+

+----------------------+
| converted_to_utf8mb4 |
+----------------------+
| こんにちは           |
+----------------------+

+-------------------+
| converted_to_sjis |
+-------------------+
| こんにちは        | -- 内部エンコーディングはsjisだが、ターミナルがutf8mb4なら表示は同じ
+-------------------+

+-----------------------+
| binary_to_string_utf8 |
+-----------------------+
| こんにちは            |
+-----------------------+

PHPなどのプログラミング言語からMariaDBに接続し、CONVERT関数を使用する例です。

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$user = 'your_user';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "--- データ型の変換の例 ---\n";

    // 文字列をSIGNEDに変換
    $stmt = $pdo->prepare("SELECT CONVERT(?, SIGNED) AS converted_value");
    $stmt->execute(['12345']);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Converted '12345' to SIGNED: " . $result['converted_value'] . " (Type: " . gettype($result['converted_value']) . ")\n";

    // 文字列をDATEに変換
    $stmt = $pdo->prepare("SELECT CONVERT(?, DATE) AS converted_date");
    $stmt->execute(['2024-05-25']);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Converted '2024-05-25' to DATE: " . $result['converted_date'] . "\n";

    echo "\n--- 文字セットの変換の例 ---\n";

    // UTF-8の文字列を明示的にUTF-8に変換（通常は不要だが、例として）
    $original_string = 'こんにちは';
    $stmt = $pdo->prepare("SELECT CONVERT(? USING utf8mb4) AS converted_string");
    $stmt->execute([$original_string]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Original string: " . $original_string . "\n";
    echo "Converted string (using utf8mb4): " . $result['converted_string'] . "\n";

    // 注意: SJISでの文字セット変換は、PHPのエンコーディングとDBのエンコーディングに注意が必要
    // ここではデモンストレーションのみ
    // 実際には、PHPの文字列をSJISに変換してからDBに渡すか、DBがSJIS対応している必要がある
    // $sjis_string_binary = mb_convert_encoding($original_string, 'SJIS', 'UTF-8');
    // $stmt = $pdo->prepare("SELECT CONVERT(? USING sjis) AS converted_to_sjis");
    // $stmt->bindParam(1, $sjis_string_binary, PDO::PARAM_LOB); // バイナリとしてバインド
    // $stmt->execute();
    // $result = $stmt->fetch(PDO::FETCH_ASSOC);
    // echo "Converted string (using sjis): " . $result['converted_to_sjis'] . "\n";


} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

このPHPコードの実行結果例

--- データ型の変換の例 ---
Converted '12345' to SIGNED: 12345 (Type: string) -- PDOは整数を文字列として返すことが多い
Converted '2024-05-25' to DATE: 2024-05-25

--- 文字セットの変換の例 ---
Original string: こんにちは
Converted string (using utf8mb4): こんにちは

(PDOはデフォルトで数値を文字列として返すため、gettypeがstringとなる点に注意してください。これはATTR_EMULATE_PREPARESなどの設定で変わることがあります。)

CAST() 関数

CAST() 関数は、CONVERT関数と同様にデータ型を変換するために最もよく使われる代替手段です。構文が標準SQLに準拠しているため、他のデータベースシステムとの互換性も高いです。

構文
CAST(値 AS データ型)

例

-- 文字列をSIGNED整数に変換
SELECT CAST('12345' AS SIGNED) AS casted_signed_int;

-- 文字列をDECIMALに変換
SELECT CAST('98.76' AS DECIMAL(5, 2)) AS casted_decimal;

-- 文字列をDATE型に変換
SELECT CAST('2023-01-15' AS DATE) AS casted_date;

-- 整数をCHAR型に変換
SELECT CAST(12345 AS CHAR) AS casted_char_from_int;

CONVERTとCASTの主な違い

標準性
CASTはSQL標準に準拠しており、互換性が高いです。
文字セット変換
CASTは文字セット変換には直接使用できません。文字セット変換を行うにはCONVERT(expr USING charset)を使う必要があります。
構文
CONVERT(expr, type) または CONVERT(expr USING charset) vs CAST(expr AS type)

ほとんどのデータ型変換のシナリオでは、CASTとCONVERTは同じように機能します。どちらを使用するかは個人の好みやプロジェクトのコーディング規約によることが多いです。

算術演算子や比較演算子による暗黙的な型変換

MariaDBは、算術演算や比較演算を行う際に、必要に応じて自動的にデータ型を変換（暗黙的な型変換）します。

例

-- 文字列と数値の足し算 (文字列が数値に変換される)
SELECT '100' + 20 AS implicit_numeric_conversion; -- 結果: 120

-- 文字列と数値の比較 (文字列が数値に変換される)
SELECT '50' > 40 AS implicit_comparison_conversion; -- 結果: 1 (TRUE)

-- 日付と文字列の比較 (文字列が日付に変換される)
SELECT CURDATE() > '2023-01-01' AS implicit_date_comparison; -- 結果: 1 (TRUE)

注意点

曖昧な場合や予期せぬ結果を避けるために、明示的な変換 (CAST または CONVERT) を使用することが推奨されます。
暗黙的な型変換は便利ですが、意図しない変換やパフォーマンスの問題を引き起こす可能性があります。特に、インデックスが使用されなくなるなど。

特定のデータ型変換に特化した関数も存在します。

INET_ATON(expr) / INET_NTOA(expr)
IPアドレスの文字列表現と数値表現を相互変換します。

SELECT INET_ATON('192.168.1.1') AS ip_to_int; -- 結果: 3232235777
SELECT INET_NTOA(3232235777) AS int_to_ip; -- 結果: '192.168.1.1'

DATE_FORMAT(date, format)
日付型を特定の形式の文字列に変換します。
```
SELECT DATE_FORMAT(CURRENT_DATE(), '%Y/%m/%d') AS formatted_date_string;
```
STR_TO_DATE(str, format)
文字列を特定の日付形式で日付型に変換します。CONVERTよりも柔軟な日付形式に対応できます。
```
SELECT STR_TO_DATE('2023年01月15日', '%Y年%m月%d日') AS specific_date_conversion;
```

クライアント接続時の文字セット設定

データベースに接続する際に、クライアント側で適切な文字セットを指定することが、最も一般的かつ効果的な文字セット変換の代替手段です。これにより、データベースとの間のデータ転送で自動的に文字セット変換が行われます。

プログラミング言語ごとの設定

PHP (PDO)
DSN (Data Source Name) にcharsetパラメータを追加します。

$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$pdo = new PDO($dsn, $user, $password);

Python (mysql-connector-python)
接続オプションでcharsetを指定します。

cnx = mysql.connector.connect(user='user', password='password',
                              host='127.0.0.1',
                              database='testdb',
                              charset='utf8mb4')

Java (JDBC)
接続URLにcharacterEncodingとuseUnicodeパラメータを追加します。

String url = "jdbc:mariadb://localhost:3306/testdb?characterEncoding=utf8&useUnicode=true";
Connection conn = DriverManager.getConnection(url, "user", "password");

SQLコマンド

SET NAMES utf8mb4; -- クライアント、コネクション、リザルトの文字セットをutf8mb4に設定

テーブルやカラムの文字セット定義

テーブルやカラムを作成する際に、適切な文字セットを指定することで、そのカラムに格納されるデータの文字セットを管理できます。これは、データの永続的な保存形式を決定します。

例

CREATE TABLE my_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

プログラミング言語レベルでの文字エンコーディング変換

データベースとは独立して、プログラミング言語の機能を使って文字列のエンコーディングを変換する方法です。これは、データベースに格納する前や、データベースから取得した後にデータを処理する場合に有効です。

注意点

一貫性のないエンコーディング変換は、文字化けやデータ破損の原因となるため、慎重に行う必要があります。
この方法は、データベースとのインターフェースで文字セットの問題を解決できない場合に、最後の手段として使われることが多いです。

MariaDBでのデータ型変換や文字セット変換には、CONVERT関数以外にも様々な方法があります。

文字セット変換
最も推奨されるのは、クライアント接続時に正しい文字セットを設定することと、データベース、テーブル、カラムで適切な文字セットを定義することです。CONVERT(expr USING charset)は、主にすでにバイナリとして格納されているデータや、特定の文字セットを持つことが分かっている文字列を別の文字セットで解釈し直す場合に有効です。プログラミング言語レベルでの変換は、最後の手段として考えられます。
データ型変換
ほとんどのケースでCAST()がCONVERTの代替として利用でき、SQL標準に準拠しているため推奨されます。特定の変換にはSTR_TO_DATE()などの専用関数が便利です。