MySQL常用函數完全指南：提升你的數據處理效率詳情 - MySQL,JSON,SQL,代碼人生編程小白狼博客

MySQL作為最流行的關係型數據庫之一，提供了豐富的內置函數來簡化數據處理和轉換操作。掌握這些函數不僅能提高開發效率，還能優化查詢性能。本文將系統性地介紹MySQL中各類常用函數，並通過實際示例幫助你快速上手。

一、字符串處理函數

字符串函數是日常開發中最常用的函數類別，主要用於文本數據的處理和轉換。

1. 基礎字符串操作

-- 字符串連接
SELECT CONCAT('Hello', ' ', 'World'); -- Hello World
SELECT CONCAT_WS('-', '2023', '12', '01'); -- 2023-12-01 (使用分隔符連接)

-- 大小寫轉換
SELECT UPPER('mysql'); -- MYSQL
SELECT LOWER('MySQL'); -- mysql

-- 去除空格
SELECT TRIM('  hello  '); -- 'hello'
SELECT LTRIM('  hello'); -- 'hello'
SELECT RTRIM('hello  '); -- 'hello'

2. 字符串截取與定位

-- 獲取字符串長度
SELECT LENGTH('MySQL'); -- 5 (字節數)
SELECT CHAR_LENGTH('數據庫'); -- 3 (字符數)

-- 字符串截取
SELECT SUBSTRING('MySQL Database', 1, 5); -- MySQL
SELECT LEFT('MySQL', 2); -- My
SELECT RIGHT('MySQL', 3); -- SQL

-- 查找位置
SELECT LOCATE('SQL', 'MySQL SQL'); -- 3
SELECT INSTR('MySQL', 'SQL'); -- 3

3. 字符串替換與格式化

-- 替換字符串
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- Hello MySQL

-- 重複字符串
SELECT REPEAT('Ha', 3); -- HaHaHa

-- 反轉字符串
SELECT REVERSE('ABCDE'); -- EDCBA

-- 格式化字符串
SELECT LPAD('5', 3, '0'); -- 005
SELECT RPAD('Name', 10, '*'); -- Name******

二、數值計算函數

數值函數用於數學計算和數值格式化，在統計分析和數據計算中非常實用。

1. 基本數學運算

-- 四捨五入
SELECT ROUND(123.4567, 2); -- 123.46
SELECT ROUND(123.4567); -- 123

-- 向上/向下取整
SELECT CEIL(123.1); -- 124
SELECT FLOOR(123.9); -- 123

-- 絕對值
SELECT ABS(-123); -- 123

-- 取餘數
SELECT MOD(10, 3); -- 1

-- 冪運算
SELECT POWER(2, 3); -- 8
SELECT SQRT(16); -- 4

2. 數值格式化與比較

-- 格式化數字
SELECT FORMAT(1234567.89, 2); -- 1,234,567.89

-- 截斷小數
SELECT TRUNCATE(123.4567, 2); -- 123.45

-- 符號判斷
SELECT SIGN(-10); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(10); -- 1

-- 隨機數
SELECT RAND(); -- 0.0到1.0之間的隨機數
SELECT RAND() * 100; -- 0到100之間的隨機數

三、日期時間函數

日期時間函數對於處理時間序列數據、生成報表和進行時間計算至關重要。

1. 獲取當前時間

-- 獲取當前日期時間
SELECT NOW(); -- 2023-12-01 15:30:45
SELECT CURDATE(); -- 2023-12-01
SELECT CURTIME(); -- 15:30:45

-- 系統時間戳
SELECT CURRENT_TIMESTAMP();
SELECT UNIX_TIMESTAMP(); -- 秒級時間戳
SELECT UNIX_TIMESTAMP(NOW()) * 1000; -- 毫秒級時間戳

2. 日期時間提取與計算

-- 提取日期部分
SELECT DATE('2023-12-01 15:30:45'); -- 2023-12-01
SELECT TIME('2023-12-01 15:30:45'); -- 15:30:45
SELECT YEAR('2023-12-01'); -- 2023
SELECT MONTH('2023-12-01'); -- 12
SELECT DAY('2023-12-01'); -- 1

-- 日期加減
SELECT DATE_ADD('2023-12-01', INTERVAL 7 DAY); -- 2023-12-08
SELECT DATE_SUB('2023-12-01', INTERVAL 1 MONTH); -- 2023-11-01

-- 日期差值
SELECT DATEDIFF('2023-12-31', '2023-12-01'); -- 30
SELECT TIMEDIFF('15:30:45', '09:00:00'); -- 06:30:45

3. 日期格式化

-- 格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2023-12-01 15:30:45
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Friday, December 01, 2023

-- 常用格式符號：
-- %Y 四位年份，%y 兩位年份
-- %m 月份(01-12)，%c 月份(1-12)
-- %d 日期(01-31)，%e 日期(1-31)
-- %H 24小時制，%h 12小時制
-- %i 分鐘，%s 秒

四、聚合函數

聚合函數對數據集執行計算並返回單個值，常用於數據統計和分析。

-- 基本聚合函數
SELECT 
    COUNT(*) AS total_rows, -- 行數統計
    COUNT(DISTINCT user_id) AS unique_users, -- 去重計數
    SUM(amount) AS total_amount, -- 求和
    AVG(amount) AS avg_amount, -- 平均值
    MIN(amount) AS min_amount, -- 最小值
    MAX(amount) AS max_amount, -- 最大值
    GROUP_CONCAT(product_name) AS all_products -- 連接字符串
FROM orders
GROUP BY category_id;

-- 條件聚合
SELECT 
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_amount,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_amount
FROM orders;

-- 統計示例：月度銷售報告
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MAX(total_amount) AS max_order
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

五、條件判斷函數

條件函數用於實現類似程序中的if-else邏輯，讓SQL查詢更加靈活。

-- IF函數
SELECT 
    product_name,
    price,
    IF(price > 100, '高價', IF(price > 50, '中等', '低價')) AS price_level
FROM products;

-- CASE WHEN表達式
SELECT 
    user_id,
    order_amount,
    CASE 
        WHEN order_amount >= 1000 THEN 'VIP客户'
        WHEN order_amount >= 500 THEN '重要客户'
        WHEN order_amount >= 100 THEN '普通客户'
        ELSE '新客户'
    END AS customer_level
FROM orders;

-- NULL值處理
SELECT 
    COALESCE(NULL, NULL, 'default_value'); -- 返回第一個非NULL值
SELECT 
    IFNULL(NULL, 'replacement'); -- 如果為NULL則替換
SELECT 
    NULLIF(expr1, expr2); -- 兩值相等返回NULL

六、類型轉換函數

類型轉換函數確保數據在不同類型間正確轉換和處理。

-- 顯式類型轉換
SELECT CAST('123' AS UNSIGNED); -- 字符串轉無符號整數
SELECT CONVERT('2023-12-01', DATE); -- 字符串轉日期

-- 隱式轉換示例
SELECT '100' + 200; -- 300 (字符串自動轉數字)
SELECT CONCAT('ID:', 123); -- ID:123 (數字自動轉字符串)

-- 數據類型檢查
SELECT 
    column_name,
    CASE 
        WHEN ISNULL(column_name) THEN '為空'
        WHEN column_name = '' THEN '空字符串'
        ELSE '有值'
    END AS status
FROM table;

七、高級實用函數

1. 窗口函數（MySQL 8.0+）

-- 排名函數
SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 累計計算
SELECT 
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales,
    AVG(daily_sales) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM sales;

2. JSON函數（MySQL 5.7+）

-- JSON處理
SELECT 
    JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- "John"
SELECT 
    JSON_SET('{"name": "John"}', '$.age', 30); -- 添加/修改屬性
SELECT 
    JSON_ARRAY(1, 2, 3); -- [1, 2, 3]
SELECT 
    JSON_OBJECT('name', 'John', 'age', 30); -- {"name": "John", "age": 30}

八、性能優化建議

索引友好性：避免在WHERE條件中對字段使用函數，這會破壞索引使用

-- 不推薦（索引失效）
SELECT * FROM orders WHERE DATE(order_date) = '2023-12-01';

-- 推薦（可使用索引）
SELECT * FROM orders WHERE order_date >= '2023-12-01' AND order_date < '2023-12-02';

函數緩存：確定性函數（如NOW()）在查詢中只計算一次
批量處理：儘量在數據庫層面完成計算，減少數據傳輸

九、實戰應用示例

用户行為分析查詢

-- 分析用户活躍度
SELECT 
    user_id,
    DATE(login_time) AS login_date,
    COUNT(*) AS login_count,
    TIMESTAMPDIFF(MINUTE, MIN(login_time), MAX(login_time)) AS session_duration,
    GROUP_CONCAT(DISTINCT DATE_FORMAT(login_time, '%H:00')) AS active_hours
FROM user_logs
WHERE login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY user_id, DATE(login_time)
HAVING login_count >= 3
ORDER BY login_date DESC, login_count DESC;

銷售數據統計

-- 生成銷售報表
SELECT 
    p.category,
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    COUNT(DISTINCT o.customer_id) AS customer_count,
    COUNT(o.order_id) AS order_count,
    SUM(o.quantity) AS total_quantity,
    SUM(o.amount) AS total_amount,
    ROUND(AVG(o.amount), 2) AS avg_amount,
    SUM(CASE WHEN o.payment_status = 'paid' THEN o.amount ELSE 0 END) AS paid_amount
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY p.category, DATE_FORMAT(o.order_date, '%Y-%m')
WITH ROLLUP; -- 添加小計行

總結

MySQL函數庫提供了強大的數據處理能力，合理使用這些函數可以：

減少應用層邏輯：將數據處理下推到數據庫層
提高查詢效率：減少不必要的數據傳輸
保證數據一致性：統一的數據處理規則
簡化複雜操作：用一條SQL完成複雜的數據轉換

掌握這些函數的關鍵在於理解其適用場景，避免濫用導致的性能問題。建議在實際開發中根據具體需求選擇合適的函數，並在使用前通過EXPLAIN分析查詢性能。

記住，最好的學習方式是在實際項目中應用這些函數，通過實踐來加深理解和記憶。

編程小白狼博客

編程小白狼博客

博客 / 詳情

MySQL常用函數完全指南：提升你的數據處理效率

一、字符串處理函數

1. 基礎字符串操作

2. 字符串截取與定位

3. 字符串替換與格式化

二、數值計算函數

1. 基本數學運算

2. 數值格式化與比較

三、日期時間函數

1. 獲取當前時間

2. 日期時間提取與計算

3. 日期格式化

四、聚合函數

五、條件判斷函數

六、類型轉換函數

七、高級實用函數

1. 窗口函數（MySQL 8.0+）

2. JSON函數（MySQL 5.7+）

八、性能優化建議

九、實戰應用示例

用户行為分析查詢

銷售數據統計

總結

發佈評論

Product

Company

Support

Company

博客 / 詳情

MySQL常用函數完全指南：提升你的數據處理效率

一、字符串處理函數

1. 基礎字符串操作

2. 字符串截取與定位

3. 字符串替換與格式化

二、數值計算函數

1. 基本數學運算

2. 數值格式化與比較

三、日期時間函數

1. 獲取當前時間

2. 日期時間提取與計算

3. 日期格式化

四、聚合函數

五、條件判斷函數

六、類型轉換函數

七、高級實用函數

1. 窗口函數（MySQL 8.0+）

2. JSON函數（MySQL 5.7+）

八、性能優化建議

九、實戰應用示例

用户行為分析查詢

銷售數據統計

總結

發佈 評論

發佈評論