MySQL の CONCAT 関数：NULL 値の罠を回避して完璧な結合を実現
MySQL CONCAT 関数と NULL 値
MySQL の CONCAT() 関数は、複数の文字列を結合するために使用されます。しかし、引数のうち一つでも NULL 値である場合、CONCAT() 関数は NULL を返します。これは、多くの場合予期せぬ結果をもたらす可能性があります。
例
次の例を見てみましょう。
SELECT CONCAT(name, ' ', surname) AS full_name
FROM customers;
このクエリは、customers テーブル内の name 列と surname 列の値を結合して full_name という新しい列を作成します。しかし、name 列または surname 列のいずれかに NULL 値が含まれている場合、full_name 列にも NULL 値が格納されます。
| full_name |
|---|---|
| 山田 太郎 |
| 田中 花子 |
| 鈴木 NULL | <- NULL 値を含む行
| 佐藤 NULL |
CONCAT() 関数と NULL 値の扱い
CONCAT() 関数による NULL 値の扱いを回避するには、以下の方法があります。
- IFNULL() 関数を使用する
IFNULL() 関数は、最初の引数が NULL でない場合、その引数を返し、NULL である場合は 2 番目の引数を返します。
SELECT CONCAT(IFNULL(name, ''), ' ', IFNULL(surname, '')) AS full_name
FROM customers;
上記のクエリでは、name 列または surname 列が NULL であっても、full_name 列には空文字列が格納されます。
| full_name |
|---|---|
| 山田 太郎 |
| 田中 花子 |
| 鈴木 | <- 空白を含む行
| 佐藤 |
- COALESCE() 関数を使用する
COALESCE() 関数は、引数のリストを上から順に調べ、最初の非 NULL 値を返します。すべての引数が NULL である場合は、NULL を返します。
SELECT COALESCE(name, '') || ' ' || COALESCE(surname, '') AS full_name
FROM customers;
上記のクエリは、IFNULL() 関数と同様の効果を持ちますが、|| 演算子を使用して文字列を結合します。
CONCAT()関数は、引数のうち一つでも NULL 値である場合、NULL を返します。IFNULL()関数またはCOALESCE()関数を使用して、NULL 値を含む場合でも結合結果に適切な値を設定することができます。- 上記の方法は、データベース設計やクエリの目的によって使い分ける必要があります。
- MySQL 8.0 以降では、
CONCAT_WS()関数を使用することができます。この関数は、NULL 値を無視して文字列を結合することができます。
テーブル定義
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL
);
データ挿入
INSERT INTO customers (name, surname) VALUES
('田中', '太郎'),
('佐藤', '花子'),
('鈴木', NULL),
('高橋', NULL);
CONCAT() 関数を使用した場合
SELECT id, name, surname, CONCAT(name, ' ', surname) AS full_name
FROM customers;
出力結果：
| id | name | surname | full_name |
|---|---|---|---|
| 1 | 田中 | 太郎 | 田中 太郎 |
| 2 | 佐藤 | 花子 | 佐藤 花子 |
| 3 | 鈴木 | NULL | NULL |
| 4 | 高橋 | NULL | NULL |
CONCAT() 関数は、surname 列に NULL 値が含まれている行の full_name 列にも NULL 値を格納します。
SELECT id, name, surname, CONCAT(IFNULL(name, ''), ' ', IFNULL(surname, '')) AS full_name
FROM customers;
| id | name | surname | full_name |
|---|---|---|---|
| 1 | 田中 | 太郎 | 田中 太郎 |
| 2 | 佐藤 | 花子 | 佐藤 花子 |
| 3 | 鈴木 | NULL | 鈴木 |
| 4 | 高橋 | NULL | 高橋 |
SELECT id, name, surname, COALESCE(name, '') || ' ' || COALESCE(surname, '') AS full_name
FROM customers;
| id | name | surname | full_name |
|---|---|---|---|
| 1 | 田中 | 太郎 | 田中 太郎 |
| 2 | 佐藤 | 花子 | 佐藤 花子 |
| 3 | 鈴木 | NULL | 鈴木 |
| 4 | 高橋 | NULL | 高橋 |
MySQL で CONCAT 関数以外の方法で文字列を結合する方法
+ 演算子
最も単純な方法は、+ 演算子を使用することです。
SELECT name + ' ' + surname AS full_name
FROM customers;
この方法は、CONCAT 関数と同じ結果を返し、可読性も高くなります。ただし、+ 演算子は数値と文字列の両方を結合できるため、注意が必要です。
FORMAT() 関数
FORMAT() 関数は、数値をフォーマットするために使用されますが、文字列の結合にも使用できます。
SELECT FORMAT('%s %s', name, surname) AS full_name
FROM customers;
この方法は、CONCAT 関数よりも柔軟性が高く、小数点以下の桁数や区切り文字などを指定することができます。
SUBSTRING() 関数と INSERT() 関数
SUBSTRING() 関数と INSERT() 関数を組み合わせて、文字列を結合することもできます。
SELECT
SUBSTRING(CONCAT(name, ' ', surname), 1, LENGTH(name) + LENGTH(' ') + LENGTH(surname)) AS full_name
FROM customers;
この方法は、複雑で可読性が低くなりますが、高度な制御が必要な場合に役立ちます。
CONCAT_WS() 関数 (MySQL 8.0 以降)
SELECT CONCAT_WS(' ', name, surname) AS full_name
FROM customers;
CONCAT_WS() 関数は、CONCAT 関数の NULL 値の扱いに関する問題を解決するのに役立ちます。
上記以外にも、MySQL で文字列を結合する方法はいくつかあります。それぞれの方法には長所と短所があるため、状況に応じて適切な方法を選択する必要があります。
- 結合する文字列の長さに制限がある場合は、
SUBSTRING()関数を使用して文字列を切り詰める必要があります。 - 結合する文字列に特殊文字が含まれている場合は、エスケープ処理が必要になる場合があります。
- 複数の文字列を結合する必要がある場合は、ループを使用して結合することができます。
mysql sql null
