MySQLの組み込み関数と実践的な活用法に関する総合ガイド
関数はSQLにおける「スイスアーミーナイフ」のような存在であり、データの変換、書式設定、計算などを処理します。
このレッスンでは、MySQLで最もよく使われる組み込み関数について、体系的に解説します。
graph TB
A[MySQL Built-in Functions] --> B[Mathematical Functions<br/>ROUND/CEIL/FLOOR/RAND]
A --> C[String Functions<br/>CONCAT/LENGTH/SUBSTRING]
A --> D[Date Functions<br/>NOW/DATE_FORMAT/DATEDIFF]
A --> E[Conditional Functions<br/>IF/IFNULL/CASE]
A --> F[Aggregate Functions<br/>COUNT/SUM/AVG/MAX/MIN]
A --> G[Type Conversion<br/>CAST/CONVERT]
1. 学習内容
- 数学関数（ROUND/CEIL/FLOOR/MOD）
- 文字列関数（CONCAT／LENGTH／SUBSTRING／REPLACE）
- 日付関数（NOW／DATE_FORMAT／DATEDIFF／DATE_ADD）
- 型変換関数（CAST/CONVERT）
- 条件関数（IF/IFNULL/CASE）
2. データクレンジングの実例
(1) 課題：データ形式の不統一
ユーザー登録データ：
| 名前 | 電話番号 | 作成日時 |
|---|---|---|
| ジョン | 138-0000-1111 | 2026年7月3日 |
| ジェーン | 13800002222 | 2026年7月3日 |
電話番号の表記が統一されておらず、日付の表記もまちまちです。
(2) 関数の解法
SQL
-- Standardize Phone Number Formats
SELECT name, REPLACE(phone, '-', '') AS clean_phone FROM users;
-- Standardize Date Formats
SELECT name, STR_TO_DATE(created_at, '%Y/%m/%d') AS clean_date FROM users;
3. 数学関数
| 関数 | 説明 | 例 |
|---|---|---|
| 指定された小数点以下の桁数に丸める。 | ||
ROUND(x, d) |
小数点以下 d 桁に丸める | ROUND(3.1415, 2) → 3.14 |
CEIL(x) |
まとめ | CEIL(3.1) → 4 |
FLOOR(x) |
切り捨て | FLOOR(3.9) → 3 |
ABS(x) |
絶対値 | ABS(-5) → 5 |
MOD(a, b) |
モジュラス | MOD(10, 3) → 1 |
POWER(x, y) |
電源動作 | POWER(2, 3) → 8 |
SQRT(x) |
平方根 | SQRT(16) → 4 |
RAND() |
乱数 0–1 | RAND() → 0.723... |
▶ 例：数学関数の応用
SQL
-- Calculate the discounted price and round it off
SELECT
product_name,
price,
ROUND(price * 0.85, 2) AS sale_price
FROM products;
-- Calculate the number of pages (Round up)
SELECT CEIL(103 / 10) AS total_pages; -- 11 pages
-- Random sort
SELECT * FROM users ORDER BY RAND() LIMIT 5;
4. 文字列関数
| 関数 | 説明 | 例 |
|---|---|---|
CONCAT(s1, s2, ...) |
文字列の連結 | CONCAT('Hello', ' ', 'World') |
CONCAT_WS(sep, s1, ...) |
区切り文字を使用して連結する | CONCAT_WS('-', '2026', '07', '03') |
LENGTH(s) |
バイト数 | LENGTH('Hello') → 5 |
CHAR_LENGTH(s) |
文字数 | CHAR_LENGTH('你好') → 2 |
UPPER(s) |
大文字に変換 | UPPER('hello') → HELLO |
LOWER(s) |
小文字に変換 | LOWER('HELLO') → hello |
TRIM(s) |
先頭と末尾のスペースを削除 | TRIM(' hi ') → hi |
LTRIM(s) |
先頭のスペースを削除 | LTRIM(' hi') → hi |
RTRIM(s) |
末尾のスペースを削除 | RTRIM('hi ') → hi |
SUBSTRING(s, pos, len) |
部分文字列 | SUBSTRING('Hello', 2, 3) → ell |
LEFT(s, n) |
左に n 文字 | LEFT('Hello', 3) → ヘル |
RIGHT(s, n) |
右に n 文字 | RIGHT('Hello', 3) → llo |
REPLACE(s, old, new) |
置換 | REPLACE('Hi World', 'Hi', 'Hello') |
REVERSE(s) |
戻る | REVERSE('Hello') → olleH |
LPAD(s, len, pad) |
左パディング | LPAD('5', 3, '0') → 005 |
RPAD(s, len, pad) |
右詰め | RPAD('Hi', 5, '.') → こんにちは… |
▶ 例：文字列関数の応用
SQL
-- Full name (combined)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- Clean phone numbers
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone FROM users;
-- Extract the email domainSELECT
email,
SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;
-- Generate order number
SELECT CONCAT('ORD', LPAD(id, 6, '0')) AS order_no FROM orders;
出力：
TEXT
+-------------+------------+
| email | domain |
+-------------+------------+
| a@gmail.com | gmail.com |
| b@163.com | 163.com |
+-------------+------------+
5. 日付関数
| 関数 | 説明 | 例 |
|---|---|---|
NOW() |
現在の日付と時刻 | 2026-07-03 10:30:00 |
CURDATE() |
今日の日付 | 2026-07-03 |
CURTIME() |
現在時刻 | 10:30:00 |
DATE(dt) |
日付の部分を抽出 | DATE('2026-07-03 10:30:00') |
YEAR(dt) |
抽出年 | YEAR('2026-07-03') → 2026 |
MONTH(dt) |
月を抽出 | MONTH('2026-07-03') → 7 |
DAY(dt) |
抽出日 | DAY('2026-07-03') → 3 |
HOUR(dt) |
時間を抽出 | HOUR('10:30:00') → 10 |
DATE_FORMAT(dt, fmt) |
日付形式 | DATE_FORMAT(NOW(), '%Y-%m-%d') |
STR_TO_DATE(s, fmt) |
文字列を日付に変換 | STR_TO_DATE('2026-07-03', '%Y-%m-%d') |
DATEDIFF(d1, d2) |
日数の差（日） | DATEDIFF('2026-07-10', '2026-07-03') → 7 |
DATE_ADD(dt, INTERVAL) |
追加日 | DATE_ADD(NOW(), INTERVAL 7 DAY) |
DATE_SUB(dt, INTERVAL) |
日付の引き算 | DATE_SUB(NOW(), INTERVAL 1 MONTH) |
TIMESTAMPDIFF(unit, d1, d2) |
時間差 | TIMESTAMPDIFF(YEAR, birth, CURDATE()) |
▶ 例：日付関数の使用
SQL
-- Check today's data
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();
-- View recent 7 days
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);
-- Monthly Statistics
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY month
ORDER BY month;
-- Calculate Age
SELECT
name,
birth_date,
TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;
出力：
TEXT
+---------+-------------+--------------+
| month | order_count | total_amount |
+---------+-------------+--------------+
| 2026-01 | 120 | 45000.00 |
| 2026-02 | 135 | 52000.00 |
| 2026-03 | 148 | 58000.00 |
+---------+-------------+--------------+
▶ 例：日付の書式設定
SQL
-- Common Formats
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2026-07-03
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d'); -- 2026/07/03
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); -- 10:30:00
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Friday, July 03, 2026
6. 条件付き関数
| 関数 | 説明 | 例 |
|---|---|---|
IF(expr, t, f) |
条件文 | IF(score >= 60, 'Pass', 'Fail') |
IFNULL(expr, alt) |
NULLの置換 | IFNULL(email, 'N/A') |
NULLIF(a, b) |
同じリターン NULL | NULLIF(1, 1) → NULL |
CASE WHEN ... THEN ... END |
複数条件の評価 | 例を見る |
▶ 例：条件付き関数
SQL
-- IF Decision
SELECT
name,
score,
IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;
-- IFNULL Processing NULL
SELECT
name,
IFNULL(phone, 'No phone') AS phone
FROM users;
-- CASE Multiple conditions
SELECT
name,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END AS grade
FROM students;
出力：
TEXT
+-------+-------+-------+
| name | score | grade |
+-------+-------+-------+
| Alice | 95 | A |
| Bob | 82 | B |
| Carol | 67 | D |
| Dave | 45 | F |
+-------+-------+-------+
7. 集計関数のプレビュー
| 関数 | 説明 | 例 |
|---|---|---|
COUNT(*) |
カウント | SELECT COUNT(*) FROM users |
SUM(col) |
合計 | SELECT SUM(amount) FROM orders |
AVG(col) |
平均 | SELECT AVG(salary) FROM employees |
MAX(col) |
最大値 | SELECT MAX(price) FROM products |
MIN(col) |
最小値 | SELECT MIN(age) FROM users |
詳細については、第12課「集計とグループ化」を参照してください。
❓ よくある質問
Q CONCAT に NULL が含まれている場合、結果はどのようなものになりますか？
A 結果は NULL になります。
CONCAT_WS または IFNULL を使用して処理してください：CONCAT_WS(' ', 'Hello', NULL) → 'Hello'。Q NOW() と CURDATE() の違いは何ですか？
A NOW() は日付と時刻を返すのに対し、CURDATE() は日付のみを返します。
Q 日付の書式指定子はどこで確認できますか？
A 「MySQL DATE_FORMAT 書式指定子」で検索してください。よく使われるものには、%Y（年）、%m（月）、%d（日）、%H（時）、%i（分）、%s（秒）などがあります。
Q WHERE句で関数を使用できますか？
A はい、使用できますが、インデックスに悪影響を及ぼします。
WHERE DATE(created_at) = CURDATE() ではインデックスを使用できません。代わりに範囲指定を使用してください：WHERE created_at >= CURDATE() AND created_at < CURDATE() + 1。📖 まとめ
- 数学関数：ROUND／CEIL／FLOOR／MOD／RAND
- 文字列関数：CONCAT／LENGTH／UPPER／LOWER／TRIM／SUBSTRING／REPLACE
- 日付関数：NOW／CURDATE／DATE_FORMAT／DATEDIFF／DATE_ADD／DATE_SUB
- 条件関数：IF／IFNULL／CASE WHEN
- 集計関数：COUNT／SUM／AVG／MAX／MIN
📝 練習問題
-
基本問題（難易度 ⭐）：CONCAT 関数と LPAD 関数を使用して、「ORD000001」という形式の注文番号を生成してください。
-
上級問題（難易度：⭐⭐）：DATE_FORMAT を使用して日付の形式を整え、過去 30 日間の各日の注文数を照会してください。
-
チャレンジ問題（難易度：⭐⭐⭐）：各ユーザーの年齢を計算し、CASE WHEN文を使用して、「10代」、「若年成人」、「中年」、「高齢者」の4つのカテゴリーに分類してください。
