MariaDB(MySQL)で使用できるその他の関数

MariaDB(MySQL)で使用できる主なその他の関数をSQLで解説

CAST(expr AS type)

「expr」を型「type」に変換します。

SELECT CAST('2024-01-01' AS DATE) AS col;
+------------+
| col        |
+------------+
| 2024-01-01 |
+------------+

SELECT CAST('2024-01-01' AS DATETIME) AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-01-01 00:00:00 |
+---------------------+

SELECT CAST(CAST('2024-01-01' AS DATETIME) AS INT) AS col;
+----------------+
| col            |
+----------------+
| 20240101000000 |
+----------------+

SELECT CAST('0001' AS INT) AS col;
+-----+
| col |
+-----+
|   1 |
+-----+

COALESCE(expr1, expr2, ...)

引数の最初のNULLでない値を返す

SELECT COALESCE(NULL, 0, 'ABC') AS col;
+------+
| col  |
+------+
| 0    |
+------+

SELECT COALESCE(NULL, NULL, 'ABC', -24.3) AS col;
+------+
| col  |
+------+
| ABC  |
+------+

SELECT COALESCE(NULL, NULL) AS col;
+------+
| col  |
+------+
| NULL |
+------+

IF(condition, true_expr, false_expr)

「condition」が真(true)なら「true_expr」を返し、そうでなければ「false_expr」を返す

SELECT IF(1>2, '正しい', '間違い') AS col;
+-----------+
| col       |
+-----------+
| 間違い    |
+-----------+

SELECT IF(1<=2, '正しい', '間違い') AS col;
+-----------+
| col       |
+-----------+
| 正しい    |
+-----------+

IFNULL(expr1, expr2)

「expr1」がNULLでない場合は「expr1」を返し、NULLなら「expr2」を返す

SELECT IFNULL(3.99, 0) AS col;
+------+
| col  |
+------+
| 3.99 |
+------+

SET @a=NULL;
SET @b='NULLでした';
SELECT IFNULL(@a, @b) AS col;
+---------------+
| col           |
+---------------+
| NULLでした    |
+---------------+

NULLIF(expr1, expr2)

「expr1」＝「expr2」の場合はNULLを返し、そうでない場合は「expr1」を返す

SELECT NULLIF(3.14, 3.1415926) AS col;
+------+
| col  |
+------+
| 3.14 |
+------+

SELECT NULLIF('abcd', 'ABCD') AS col;
+------+
| col  |
+------+
| NULL |
+------+

ISNULL(expr)

「expr」がNULLの場合は 1 を返し、そうでない場合は 0を返す

SELECT ISNULL(NULL) AS col;
+-----+
| col |
+-----+
|   1 |
+-----+

SELECT ISNULL('abcd') AS col;
+-----+
| col |
+-----+
|   0 |
+-----+

SLEEP(second)

second秒待つ

SELECT SLEEP(2), '2秒待ってから表示される' AS col;
+----------+------------------------------------+
| SLEEP(2) | col                                |
+----------+------------------------------------+
|        0 | 2秒待ってから表示される            |
+----------+------------------------------------+

VERSION()

バージョン情報を返す

SELECT VERSION() AS col;
+----------------+
| col            |
+----------------+
| 11.1.2-MariaDB |
+----------------+

LOAD_FILE(path)

pathで指定したファイルを読み込んで返す

SELECT LOAD_FILE('/Windows/System32/drivers/etc/hosts') AS col;
+----------------------------------------------------------------------------------------+
| col                                                                                    |
+----------------------------------------------------------------------------------------+
| # Copyright (c) 1993-2009 Microsoft Corp. ・・・ 省略                                  |
+----------------------------------------------------------------------------------------+

MariaDB(MySQL)のサンプルSQL一覧に戻る