MySQL自定義函數零基礎學習教程

1. 引言

想象一下，你在用計算器做數學題。每次計算"圓形面積"時，你都要輸入：3.14 × 半徑 × 半徑。如果能把這個計算步驟保存起來，下次只要輸入半徑就自動算出面積，那該多方便！

MySQL自定義函數就是這樣的"保存的計算步驟"。它讓我們把復雜的計算過程寫成一個"函數"，以后只要調用這個函數名，就能自動完成計算。

就像給計算器增加了一個新按鈕：輸入半徑，按下"計算圓面積"按鈕，就得到結果。

自定義函數解決了數據庫開發中的核心問題：如何優雅地處理復雜的業務邏輯計算，避免重復編寫相同的代碼，同時保持數據庫操作的高效性和一致性。

2. 基礎概念和語法

2.1 什么是MySQL自定義函數？

最簡單的理解：自定義函數就是你教給MySQL的一個新"技能"。

輸入：給函數一些數據（叫做參數）
處理：函數按照你寫的規則進行計算
輸出：返回一個結果

就像數學中的函數：f(x) = x + 1

輸入x=5，計算：5+1，輸出：6

2.2 基本語法結構

CREATE FUNCTION 函數名(參數名 參數類型)
RETURNS 返回類型
[函數特性]
BEGIN-- 函數體：業務邏輯代碼DECLARE 變量聲明;SET 變量賦值;RETURN 返回值;
END

各部分詳細說明：

CREATE FUNCTION：告訴MySQL"我要創建一個新函數"
函數名：給這個函數起個名字，遵循MySQL標識符命名規則
參數列表：函數接受的輸入參數，格式：參數名數據類型
RETURNS：指定函數返回值的數據類型，必須與實際返回值類型匹配
函數特性：可選的函數屬性設置（DETERMINISTIC、READS SQL DATA等）
BEGIN...END：函數體的邊界標識，包含具體實現邏輯
DECLARE：聲明局部變量
RETURN：返回函數結果，函數必須包含return語句

2.3 關鍵字說明

關鍵字	作用	說明
DELIMITER	設置語句分隔符	避免函數體內分號與語句結束符沖突
CREATE FUNCTION	創建函數	函數定義的開始標識
RETURNS	聲明返回類型	必須指定，且與實際返回值類型匹配
DETERMINISTIC	確定性函數	相同輸入總是產生相同輸出
NOT DETERMINISTIC	非確定性函數	相同輸入可能產生不同輸出
READS SQL DATA	讀取數據	函數會讀取數據庫數據
NO SQL	不含SQL	函數不包含SQL語句
BEGIN...END	函數體邊界	包含函數的具體實現邏輯
DECLARE	聲明變量	在函數內部聲明局部變量
RETURN	返回結果	函數必須包含return語句

2.4 第一個函數：從最簡單開始

我們從最最簡單的開始：一個把數字乘以2的函數。

-- 告訴MySQL：現在用$$作為語句結束標記，不要用分號
DELIMITER $$CREATE FUNCTION double_number(x INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGINRETURN x * 2;  -- 這個分號不是語句結束，只是函數內部的分號
END $$  -- 這個$$才是語句結束-- 恢復默認的分隔符
DELIMITER ;

逐行解釋：

DELIMITER $$：設置新的語句分隔符，避免函數內部分號沖突
CREATE FUNCTION double_number：創建一個叫"double_number"的函數
(x INT)：函數需要一個整數輸入，命名為x
RETURNS INT：函數會返回一個整數
DETERMINISTIC：相同輸入總是相同輸出
NO SQL：函數不包含SQL查詢語句
RETURN x * 2：把輸入的數字乘以2并返回

使用這個函數：

SELECT double_number(5);  -- 結果：10
SELECT double_number(8);  -- 結果：16

3. 函數特性詳解

3.1 DETERMINISTIC vs NOT DETERMINISTIC

DETERMINISTIC（確定性）：告訴MySQL"這個函數很老實"，相同的輸入總是得到相同的輸出。

DELIMITER $$-- 確定性函數：數學計算
CREATE FUNCTION add_two_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGINRETURN a + b;  -- 3+5總是等于8
END $$-- 確定性函數：字符串處理
CREATE FUNCTION format_phone(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGINRETURN CONCAT('(', LEFT(phone, 3), ') ', SUBSTRING(phone, 4, 3), '-', RIGHT(phone, 4));
END $$DELIMITER ;

NOT DETERMINISTIC（非確定性）：相同的輸入可能得到不同的輸出，因為依賴于時間、隨機數等外部因素。

DELIMITER $$-- 非確定性函數：時間相關
CREATE FUNCTION get_current_hour()
RETURNS INT
NOT DETERMINISTIC
NO SQL
BEGINRETURN HOUR(NOW());  -- 每個小時返回的結果都不同
END $$-- 非確定性函數：隨機數
CREATE FUNCTION get_random_discount()
RETURNS DECIMAL(3,2)
NOT DETERMINISTIC
NO SQL
BEGINRETURN ROUND(RAND() * 0.2, 2);  -- 0到0.2之間的隨機折扣
END $$-- 非確定性函數：計算年齡
CREATE FUNCTION calculate_age(birth_year INT)
RETURNS INT
NOT DETERMINISTIC
NO SQL
BEGINRETURN YEAR(CURDATE()) - birth_year;  -- 結果隨當前年份變化
END $$DELIMITER ;

3.2 NO SQL vs READS SQL DATA

NO SQL：函數只做計算，不查詢數據庫表。

DELIMITER $$-- 純數學計算
CREATE FUNCTION calculate_circle_area(radius DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGINRETURN 3.14159 * radius * radius;
END $$-- 字符串處理
CREATE FUNCTION mask_credit_card(card_number VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGINIF LENGTH(card_number) < 4 THENRETURN card_number;END IF;RETURN CONCAT(REPEAT('*', LENGTH(card_number) - 4), RIGHT(card_number, 4));
END $$DELIMITER ;

READS SQL DATA：函數需要從數據庫表中讀取數據。

首先創建測試表：

-- 創建學生表
CREATE TABLE IF NOT EXISTS students (id INT PRIMARY KEY,name VARCHAR(50),score INT,class_id INT
);-- 插入測試數據
INSERT IGNORE INTO students VALUES 
(1, '小明', 85, 1),
(2, '小紅', 92, 1),
(3, '小李', 78, 2),
(4, '小王', 88, 2);

DELIMITER $$-- 查詢單個值
CREATE FUNCTION get_student_score(student_id INT)
RETURNS INT
READS SQL DATA
BEGINDECLARE student_score INT;SELECT score INTO student_score FROM students WHERE id = student_id;RETURN COALESCE(student_score, 0);
END $$-- 查詢統計數據
CREATE FUNCTION get_class_average(class_id INT)
RETURNS DECIMAL(5,2)
READS SQL DATA
BEGINDECLARE avg_score DECIMAL(5,2);SELECT AVG(score) INTO avg_scoreFROM students WHERE class_id = class_id;RETURN COALESCE(avg_score, 0.00);
END $$-- 查詢計數
CREATE FUNCTION count_excellent_students(min_score INT)
RETURNS INT
READS SQL DATA
BEGINDECLARE student_count INT;SELECT COUNT(*) INTO student_countFROM students WHERE score >= min_score;RETURN student_count;
END $$DELIMITER ;

測試函數：

-- 純計算函數
SELECT calculate_circle_area(5.0);  -- 結果：78.54-- 數據查詢函數
SELECT get_student_score(1);        -- 結果：85
SELECT get_class_average(1);        -- 結果：88.50
SELECT count_excellent_students(90); -- 結果：1

3.3 函數特性選擇指南

場景	推薦特性	示例
純數學計算	DETERMINISTIC + NO SQL	計算面積、稅費、折扣
字符串格式化	DETERMINISTIC + NO SQL	電話號碼格式化、脫敏
時間相關計算	NOT DETERMINISTIC + NO SQL	計算年齡、當前時間
隨機數生成	NOT DETERMINISTIC + NO SQL	隨機折扣、驗證碼
簡單數據查詢	DETERMINISTIC + READS SQL DATA	查詢用戶信息、商品價格
統計分析	NOT DETERMINISTIC + READS SQL DATA	實時統計、動態分析

4. 變量和控制結構

4.1 變量的聲明和使用

變量就像一個"盒子"，可以往里面放數據，也可以把數據取出來。

DELIMITER $$-- 基礎變量使用
CREATE FUNCTION demonstrate_variables(input_num INT)
RETURNS VARCHAR(100)
DETERMINISTIC
NO SQL
BEGINDECLARE num1 INT;                         -- 聲明整數變量，默認NULLDECLARE num2 INT DEFAULT 0;              -- 聲明整數變量，初始值0DECLARE message VARCHAR(50) DEFAULT 'Result: ';  -- 聲明字符串變量DECLARE final_result VARCHAR(100);       -- 聲明結果變量-- 變量賦值SET num1 = input_num;SET num2 = num2 + input_num * 2;-- 字符串拼接SET final_result = CONCAT(message, num1, ' + ', num2, ' = ', num1 + num2);RETURN final_result;
END $$-- 復雜變量操作
CREATE FUNCTION calculate_compound_result(principal DECIMAL(10,2), rate DECIMAL(4,3), years INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC
NO SQL
BEGINDECLARE annual_amount DECIMAL(12,2);DECLARE total_amount DECIMAL(12,2);DECLARE interest_earned DECIMAL(12,2);DECLARE i INT DEFAULT 1;SET total_amount = principal;-- 使用WHILE循環計算復利WHILE i <= years DOSET total_amount = total_amount * (1 + rate);SET i = i + 1;END WHILE;RETURN ROUND(total_amount, 2);
END $$DELIMITER ;

4.2 條件判斷結構

IF語句

DELIMITER $$-- 簡單IF判斷
CREATE FUNCTION check_score_level(score INT)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGINIF score >= 90 THENRETURN 'Excellent';ELSEIF score >= 80 THENRETURN 'Good';ELSEIF score >= 70 THENRETURN 'Average';ELSEIF score >= 60 THENRETURN 'Pass';ELSERETURN 'Fail';END IF;
END $$-- 復雜條件判斷
CREATE FUNCTION calculate_shipping_fee(weight DECIMAL(5,2), distance INT, is_express BOOLEAN)
RETURNS DECIMAL(8,2)
DETERMINISTIC
NO SQL
BEGINDECLARE base_fee DECIMAL(8,2);DECLARE distance_fee DECIMAL(8,2);DECLARE express_fee DECIMAL(8,2) DEFAULT 0;DECLARE total_fee DECIMAL(8,2);-- 基礎費用計算IF weight <= 1.0 THENSET base_fee = 8.00;ELSEIF weight <= 5.0 THENSET base_fee = 12.00;ELSEIF weight <= 10.0 THENSET base_fee = 18.00;ELSESET base_fee = 25.00;END IF;-- 距離費用IF distance <= 100 THENSET distance_fee = 0;ELSEIF distance <= 500 THENSET distance_fee = 5.00;ELSESET distance_fee = 10.00;END IF;-- 加急費用IF is_express THENSET express_fee = base_fee * 0.5;END IF;SET total_fee = base_fee + distance_fee + express_fee;RETURN total_fee;
END $$DELIMITER ;

CASE語句

DELIMITER $$-- 基礎CASE使用
CREATE FUNCTION get_month_name(month_num INT)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGINCASE month_numWHEN 1 THEN RETURN 'January';WHEN 2 THEN RETURN 'February';WHEN 3 THEN RETURN 'March';WHEN 4 THEN RETURN 'April';WHEN 5 THEN RETURN 'May';WHEN 6 THEN RETURN 'June';WHEN 7 THEN RETURN 'July';WHEN 8 THEN RETURN 'August';WHEN 9 THEN RETURN 'September';WHEN 10 THEN RETURN 'October';WHEN 11 THEN RETURN 'November';WHEN 12 THEN RETURN 'December';ELSE RETURN 'Invalid Month';END CASE;
END $$-- 條件CASE使用
CREATE FUNCTION calculate_tax_rate(income DECIMAL(12,2), tax_type VARCHAR(20))
RETURNS DECIMAL(5,4)
DETERMINISTIC
NO SQL
BEGINDECLARE tax_rate DECIMAL(5,4);CASE tax_typeWHEN 'individual' THENCASEWHEN income <= 36000 THEN SET tax_rate = 0.03;WHEN income <= 144000 THEN SET tax_rate = 0.10;WHEN income <= 300000 THEN SET tax_rate = 0.20;ELSE SET tax_rate = 0.25;END CASE;WHEN 'corporate' THENCASEWHEN income <= 2500000 THEN SET tax_rate = 0.20;ELSE SET tax_rate = 0.25;END CASE;ELSESET tax_rate = 0.00;END CASE;RETURN tax_rate;
END $$DELIMITER ;

4.3 NULL值處理

DELIMITER $$-- 基礎NULL處理
CREATE FUNCTION safe_divide(dividend DECIMAL(10,2), divisor DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN-- 檢查NULL值IF dividend IS NULL OR divisor IS NULL THENRETURN NULL;END IF;-- 檢查除零IF divisor = 0 THENRETURN NULL;END IF;RETURN dividend / divisor;
END $$-- 使用COALESCE處理NULL
CREATE FUNCTION calculate_total_with_defaults(base_amount DECIMAL(10,2),tax_rate DECIMAL(4,3),discount_amount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGINDECLARE total DECIMAL(10,2);-- 使用默認值處理NULLSET base_amount = COALESCE(base_amount, 0.00);SET tax_rate = COALESCE(tax_rate, 0.000);SET discount_amount = COALESCE(discount_amount, 0.00);-- 計算總額SET total = base_amount * (1 + tax_rate) - discount_amount;-- 確保結果不為負IF total < 0 THENSET total = 0.00;END IF;RETURN total;
END $$-- 數據庫查詢中的NULL處理
CREATE FUNCTION get_student_info_safe(student_id INT)
RETURNS VARCHAR(200)
READS SQL DATA
BEGINDECLARE student_name VARCHAR(50);DECLARE student_score INT;DECLARE result VARCHAR(200);SELECT name, score INTO student_name, student_scoreFROM students WHERE id = student_id;-- 處理查詢結果為NULL的情況IF student_name IS NULL THENRETURN 'Student not found';END IF;SET result = CONCAT('Name: ', student_name,', Score: ', COALESCE(student_score, 0),', Level: ', CASEWHEN student_score IS NULL THEN 'No Score'WHEN student_score >= 90 THEN 'A'WHEN student_score >= 80 THEN 'B'WHEN student_score >= 70 THEN 'C'WHEN student_score >= 60 THEN 'D'ELSE 'F'END);RETURN result;
END $$DELIMITER ;

測試函數：

-- 變量操作測試
SELECT demonstrate_variables(5);
SELECT calculate_compound_result(1000.00, 0.05, 3);-- 條件判斷測試
SELECT check_score_level(85);
SELECT calculate_shipping_fee(2.5, 300, TRUE);
SELECT get_month_name(6);
SELECT calculate_tax_rate(50000, 'individual');-- NULL處理測試
SELECT safe_divide(10, 3);
SELECT safe_divide(10, 0);
SELECT safe_divide(NULL, 5);
SELECT calculate_total_with_defaults(100.00, NULL, 10.00);
SELECT get_student_info_safe(1);
SELECT get_student_info_safe(99);

5. 實際應用場景

5.1 電商價格計算系統

創建電商相關表：

-- 商品表
CREATE TABLE IF NOT EXISTS products (id INT PRIMARY KEY,name VARCHAR(100),base_price DECIMAL(8,2),category VARCHAR(50),weight DECIMAL(5,2)
);-- 用戶表
CREATE TABLE IF NOT EXISTS customers (id INT PRIMARY KEY,name VARCHAR(50),level VARCHAR(20),total_orders INT
);-- 優惠券表
CREATE TABLE IF NOT EXISTS coupons (code VARCHAR(20) PRIMARY KEY,discount_type ENUM('percentage', 'fixed'),discount_value DECIMAL(8,2),min_amount DECIMAL(8,2),is_active BOOLEAN
);-- 插入測試數據
INSERT IGNORE INTO products VALUES 
(1, 'iPhone 15', 7999.00, 'Electronics', 0.2),
(2, 'T-Shirt', 99.00, 'Clothing', 0.3),
(3, 'Programming Book', 89.00, 'Books', 0.5);INSERT IGNORE INTO customers VALUES 
(1, '張三', 'VIP', 25),
(2, '李四', 'Gold', 15),
(3, '王五', 'Silver', 8);INSERT IGNORE INTO coupons VALUES 
('SAVE10', 'percentage', 10.00, 100.00, TRUE),
('SAVE50', 'fixed', 50.00, 200.00, TRUE),
('VIP20', 'percentage', 20.00, 500.00, TRUE);

DELIMITER $$-- 計算會員折扣率
CREATE FUNCTION get_member_discount_rate(customer_level VARCHAR(20))
RETURNS DECIMAL(4,3)
DETERMINISTIC
NO SQL
BEGINCASE customer_levelWHEN 'VIP' THEN RETURN 0.080;      -- 8%折扣WHEN 'Gold' THEN RETURN 0.050;     -- 5%折扣WHEN 'Silver' THEN RETURN 0.030;   -- 3%折扣WHEN 'Bronze' THEN RETURN 0.010;   -- 1%折扣ELSE RETURN 0.000;                 -- 無折扣END CASE;
END $$-- 計算運費
CREATE FUNCTION calculate_shipping_cost(weight DECIMAL(5,2), distance INT)
RETURNS DECIMAL(6,2)
DETERMINISTIC
NO SQL
BEGINDECLARE base_cost DECIMAL(6,2);DECLARE weight_cost DECIMAL(6,2);DECLARE distance_cost DECIMAL(6,2);-- 基礎運費SET base_cost = 8.00;-- 重量費用IF weight <= 0.5 THENSET weight_cost = 0.00;ELSEIF weight <= 2.0 THENSET weight_cost = 5.00;ELSEIF weight <= 5.0 THENSET weight_cost = 12.00;ELSESET weight_cost = 20.00;END IF;-- 距離費用IF distance <= 100 THENSET distance_cost = 0.00;ELSEIF distance <= 500 THENSET distance_cost = 8.00;ELSESET distance_cost = 15.00;END IF;RETURN base_cost + weight_cost + distance_cost;
END $$-- 應用優惠券
CREATE FUNCTION apply_coupon_discount(amount DECIMAL(10,2), coupon_code VARCHAR(20))
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGINDECLARE discount_type ENUM('percentage', 'fixed');DECLARE discount_value DECIMAL(8,2);DECLARE min_amount DECIMAL(8,2);DECLARE is_active BOOLEAN;DECLARE discount_amount DECIMAL(10,2) DEFAULT 0.00;-- 查詢優惠券信息SELECT discount_type, discount_value, min_amount, is_activeINTO discount_type, discount_value, min_amount, is_activeFROM couponsWHERE code = coupon_code;-- 驗證優惠券IF discount_type IS NULL OR NOT is_active THENRETURN amount;  -- 優惠券無效，返回原價END IF;IF amount < min_amount THENRETURN amount;  -- 不滿足最低消費，返回原價END IF;-- 計算折扣IF discount_type = 'percentage' THENSET discount_amount = amount * (discount_value / 100);ELSESET discount_amount = discount_value;END IF;-- 確保折扣后價格不為負IF amount - discount_amount < 0 THENRETURN 0.00;END IF;RETURN amount - discount_amount;
END $$-- 綜合價格計算
CREATE FUNCTION calculate_final_price(product_id INT,quantity INT,customer_id INT,coupon_code VARCHAR(20),shipping_distance INT
)
RETURNS JSON
READS SQL DATA
BEGINDECLARE product_price DECIMAL(8,2);DECLARE product_weight DECIMAL(5,2);DECLARE customer_level VARCHAR(20);DECLARE subtotal DECIMAL(10,2);DECLARE member_discount DECIMAL(10,2);DECLARE coupon_discount DECIMAL(10,2);DECLARE shipping_cost DECIMAL(6,2);DECLARE final_total DECIMAL(10,2);DECLARE result JSON;-- 查詢商品信息SELECT base_price, weight INTO product_price, product_weightFROM products WHERE id = product_id;-- 查詢客戶等級SELECT level INTO customer_levelFROM customers WHERE id = customer_id;-- 驗證數據IF product_price IS NULL THENRETURN JSON_OBJECT('error', 'Product not found');END IF;-- 計算小計SET subtotal = product_price * quantity;-- 應用會員折扣SET member_discount = subtotal * get_member_discount_rate(COALESCE(customer_level, 'Bronze'));SET subtotal = subtotal - member_discount;-- 應用優惠券IF coupon_code IS NOT NULL THENSET coupon_discount = subtotal - apply_coupon_discount(subtotal, coupon_code);SET subtotal = apply_coupon_discount(subtotal, coupon_code);ELSESET coupon_discount = 0.00;END IF;-- 計算運費SET shipping_cost = calculate_shipping_cost(product_weight * quantity, shipping_distance);-- 最終總價SET final_total = subtotal + shipping_cost;-- 構建結果JSONSET result = JSON_OBJECT('product_id', product_id,'quantity', quantity,'unit_price', product_price,'subtotal_before_discount', product_price * quantity,'member_discount', member_discount,'coupon_discount', coupon_discount,'subtotal_after_discount', subtotal,'shipping_cost', shipping_cost,'final_total', final_total,'customer_level', COALESCE(customer_level, 'Bronze'));RETURN result;
END $$DELIMITER ;

5.2 學生成績管理系統

-- 擴展學生表
CREATE TABLE IF NOT EXISTS student_grades (id INT PRIMARY KEY,name VARCHAR(50),math DECIMAL(5,2),english DECIMAL(5,2),science DECIMAL(5,2),chinese DECIMAL(5,2),class_id INT
);INSERT IGNORE INTO student_grades VALUES 
(1, '小明', 85.5, 78.0, 92.5, 88.0, 1),
(2, '小紅', 92.0, 88.5, 85.0, 90.5, 1),
(3, '小李', 78.5, 85.0, 90.0, 82.5, 2),
(4, '小王', 88.0, 92.0, 78.5, 85.5, 2),
(5, '小張', 95.0, 89.5, 94.0, 93.5, 1);DELIMITER $$-- 計算學生總分和平均分
CREATE FUNCTION calculate_student_stats(student_id INT)
RETURNS JSON
READS SQL DATA
BEGINDECLARE math_score, english_score, science_score, chinese_score DECIMAL(5,2);DECLARE total_score DECIMAL(6,2);DECLARE average_score DECIMAL(5,2);DECLARE grade_level VARCHAR(10);DECLARE student_name VARCHAR(50);-- 查詢學生成績SELECT name, math, english, science, chineseINTO student_name, math_score, english_score, science_score, chinese_scoreFROM student_gradesWHERE id = student_id;-- 檢查學生是否存在IF student_name IS NULL THENRETURN JSON_OBJECT('error', 'Student not found');END IF;-- 處理NULL成績SET math_score = COALESCE(math_score, 0);SET english_score = COALESCE(english_score, 0);SET science_score = COALESCE(science_score, 0);SET chinese_score = COALESCE(chinese_score, 0);-- 計算總分和平均分SET total_score = math_score + english_score + science_score + chinese_score;SET average_score = total_score / 4;-- 確定等級CASEWHEN average_score >= 90 THEN SET grade_level = 'A';WHEN average_score >= 80 THEN SET grade_level = 'B';WHEN average_score >= 70 THEN SET grade_level = 'C';WHEN average_score >= 60 THEN SET grade_level = 'D';ELSE SET grade_level = 'F';END CASE;RETURN JSON_OBJECT('student_id', student_id,'name', student_name,'scores', JSON_OBJECT('math', math_score,'english', english_score,'science', science_score,'chinese', chinese_score),'total_score', total_score,'average_score', ROUND(average_score, 2),'grade_level', grade_level);
END $$-- 班級排名計算
CREATE FUNCTION get_student_rank_in_class(student_id INT)
RETURNS INT
READS SQL DATA
BEGINDECLARE student_avg DECIMAL(5,2);DECLARE student_class_id INT;DECLARE student_rank INT;-- 獲取學生平均分和班級SELECT (COALESCE(math,0) + COALESCE(english,0) + COALESCE(science,0) + COALESCE(chinese,0))/4, class_idINTO student_avg, student_class_idFROM student_gradesWHERE id = student_id;IF student_avg IS NULL THENRETURN 0;END IF;-- 計算排名（比該學生平均分高的人數 + 1）SELECT COUNT(*) + 1 INTO student_rankFROM student_gradesWHERE class_id = student_class_idAND (COALESCE(math,0) + COALESCE(english,0) + COALESCE(science,0) + COALESCE(chinese,0))/4 > student_avg;RETURN student_rank;
END $$-- 科目強弱分析
CREATE FUNCTION analyze_subject_strength(student_id INT)
RETURNS JSON
READS SQL DATA
BEGINDECLARE math_score, english_score, science_score, chinese_score DECIMAL(5,2);DECLARE max_score, min_score DECIMAL(5,2);DECLARE strongest_subject, weakest_subject VARCHAR(20);SELECT math, english, science, chineseINTO math_score, english_score, science_score, chinese_scoreFROM student_gradesWHERE id = student_id;IF math_score IS NULL THENRETURN JSON_OBJECT('error', 'Student not found');END IF;-- 處理NULL值SET math_score = COALESCE(math_score, 0);SET english_score = COALESCE(english_score, 0);SET science_score = COALESCE(science_score, 0);SET chinese_score = COALESCE(chinese_score, 0);-- 找最高分科目SET max_score = GREATEST(math_score, english_score, science_score, chinese_score);SET min_score = LEAST(math_score, english_score, science_score, chinese_score);-- 確定最強科目CASE max_scoreWHEN math_score THEN SET strongest_subject = 'Math';WHEN english_score THEN SET strongest_subject = 'English';WHEN science_score THEN SET strongest_subject = 'Science';WHEN chinese_score THEN SET strongest_subject = 'Chinese';END CASE;-- 確定最弱科目CASE min_scoreWHEN math_score THEN SET weakest_subject = 'Math';WHEN english_score THEN SET weakest_subject = 'English';WHEN science_score THEN SET weakest_subject = 'Science';WHEN chinese_score THEN SET weakest_subject = 'Chinese';END CASE;RETURN JSON_OBJECT('strongest_subject', strongest_subject,'strongest_score', max_score,'weakest_subject', weakest_subject,'weakest_score', min_score,'score_gap', max_score - min_score,'recommendation', CASEWHEN max_score - min_score > 20 THEN 'Focus on improving weak subjects'WHEN max_score - min_score > 10 THEN 'Balanced development needed'ELSE 'Well-balanced performance'END);
END $$DELIMITER ;

5.3 金融計算系統

DELIMITER $$-- 復利計算
CREATE FUNCTION calculate_compound_interest(principal DECIMAL(12,2),annual_rate DECIMAL(6,4),years INT,compound_frequency INT
)
RETURNS JSON
DETERMINISTIC
NO SQL
BEGINDECLARE final_amount DECIMAL(12,2);DECLARE total_interest DECIMAL(12,2);DECLARE effective_rate DECIMAL(8,6);-- 輸入驗證IF principal <= 0 OR annual_rate < 0 OR years < 0 OR compound_frequency <= 0 THENRETURN JSON_OBJECT('error', 'Invalid input parameters');END IF;-- 計算復利：A = P(1 + r/n)^(nt)SET final_amount = principal * POWER(1 + annual_rate/compound_frequency, compound_frequency * years);SET total_interest = final_amount - principal;SET effective_rate = POWER(1 + annual_rate/compound_frequency, compound_frequency) - 1;RETURN JSON_OBJECT('principal', principal,'annual_rate_percent', annual_rate * 100,'years', years,'compound_frequency', compound_frequency,'final_amount', ROUND(final_amount, 2),'total_interest', ROUND(total_interest, 2),'effective_annual_rate_percent', ROUND(effective_rate * 100, 4));
END $$-- 貸款月供計算
CREATE FUNCTION calculate_loan_payment(loan_amount DECIMAL(12,2),annual_rate DECIMAL(6,4),years INT
)
RETURNS JSON
DETERMINISTIC
NO SQL
BEGINDECLARE monthly_rate DECIMAL(8,6);DECLARE total_payments INT;DECLARE monthly_payment DECIMAL(10,2);DECLARE total_paid DECIMAL(12,2);DECLARE total_interest DECIMAL(12,2);-- 輸入驗證IF loan_amount <= 0 OR annual_rate <= 0 OR years <= 0 THENRETURN JSON_OBJECT('error', 'Invalid input parameters');END IF;SET monthly_rate = annual_rate / 12;SET total_payments = years * 12;-- 計算月供：M = P * [r(1+r)^n] / [(1+r)^n - 1]IF monthly_rate = 0 THENSET monthly_payment = loan_amount / total_payments;ELSESET monthly_payment = loan_amount * (monthly_rate * POWER(1 + monthly_rate, total_payments)) / (POWER(1 + monthly_rate, total_payments) - 1);END IF;SET total_paid = monthly_payment * total_payments;SET total_interest = total_paid - loan_amount;RETURN JSON_OBJECT('loan_amount', loan_amount,'annual_rate_percent', annual_rate * 100,'loan_term_years', years,'monthly_payment', ROUND(monthly_payment, 2),'total_payments', total_payments,'total_amount_paid', ROUND(total_paid, 2),'total_interest_paid', ROUND(total_interest, 2));
END $$-- 投資回報率計算
CREATE FUNCTION calculate_investment_return(initial_investment DECIMAL(12,2),final_value DECIMAL(12,2),years DECIMAL(5,2)
)
RETURNS JSON
DETERMINISTIC
NO SQL
BEGINDECLARE absolute_return DECIMAL(12,2);DECLARE percentage_return DECIMAL(8,4);DECLARE annualized_return DECIMAL(8,4);-- 輸入驗證IF initial_investment <= 0 OR final_value < 0 OR years <= 0 THENRETURN JSON_OBJECT('error', 'Invalid input parameters');END IF;SET absolute_return = final_value - initial_investment;SET percentage_return = (final_value - initial_investment) / initial_investment;-- 年化收益率：(Final/Initial)^(1/years) - 1SET annualized_return = POWER(final_value / initial_investment, 1/years) - 1;RETURN JSON_OBJECT('initial_investment', initial_investment,'final_value', final_value,'investment_period_years', years,'absolute_return', ROUND(absolute_return, 2),'percentage_return', ROUND(percentage_return * 100, 2),'annualized_return_percent', ROUND(annualized_return * 100, 2),'performance_rating', CASEWHEN annualized_return >= 0.15 THEN 'Excellent'WHEN annualized_return >= 0.10 THEN 'Good'WHEN annualized_return >= 0.05 THEN 'Average'WHEN annualized_return >= 0 THEN 'Below Average'ELSE 'Loss'END);
END $$DELIMITER ;

6. 性能優化和最佳實踐

6.1 性能優化原則

優化原則	說明	示例
最小化數據庫查詢	盡量用一次查詢獲取多個值	避免在函數中多次SELECT同一表
合適的數據類型	使用精確的數據類型	年齡用TINYINT而不是INT
避免在WHERE中使用函數	防止索引失效	建立計算列或使用其他優化方式
減少復雜計算	避免在大數據集上使用復雜函數	考慮預計算或緩存結果
合理使用DETERMINISTIC	確定性函數可以被緩存	純計算函數標記為DETERMINISTIC

6.2 常見錯誤和解決方案

-- ? 錯誤1：忘記DELIMITER
CREATE FUNCTION bad_function(x INT)
RETURNS INT
BEGINRETURN x * 2;  -- MySQL會在這里認為語句結束
END-- ? 正確寫法
DELIMITER $$
CREATE FUNCTION good_function(x INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGINRETURN x * 2;
END $$
DELIMITER ;-- ? 錯誤2：返回類型不匹配
DELIMITER $$
CREATE FUNCTION type_mismatch(x INT)
RETURNS INT  -- 聲明返回INT
DETERMINISTIC
NO SQL
BEGINRETURN 'hello';  -- 實際返回字符串
END $$
DELIMITER ;-- ? 正確寫法
DELIMITER $$
CREATE FUNCTION type_correct(x INT)
RETURNS VARCHAR(10)  -- 聲明返回字符串
DETERMINISTIC
NO SQL
BEGINRETURN 'hello';
END $$
DELIMITER ;-- ? 錯誤3：未處理NULL值
DELIMITER $$
CREATE FUNCTION unsafe_calculation(a INT, b INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGINRETURN a + b;  -- 如果a或b為NULL，結果也是NULL
END $$
DELIMITER ;-- ? 正確寫法
DELIMITER $$
CREATE FUNCTION safe_calculation(a INT, b INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGINRETURN COALESCE(a, 0) + COALESCE(b, 0);
END $$
DELIMITER ;-- ? 錯誤4：性能問題 - 多次查詢
DELIMITER $$
CREATE FUNCTION slow_student_info(student_id INT)
RETURNS VARCHAR(200)
READS SQL DATA
BEGINDECLARE name VARCHAR(50);DECLARE score INT;SELECT name INTO name FROM students WHERE id = student_id;SELECT score INTO score FROM students WHERE id = student_id;  -- 重復查詢RETURN CONCAT(name, ':', score);
END $$
DELIMITER ;-- ? 正確寫法
DELIMITER $$
CREATE FUNCTION fast_student_info(student_id INT)
RETURNS VARCHAR(200)
READS SQL DATA
BEGINDECLARE result VARCHAR(200);SELECT CONCAT(name, ':', COALESCE(score, 0)) INTO resultFROM students WHERE id = student_id;RETURN COALESCE(result, 'Student not found');
END $$
DELIMITER ;

6.3 調試和測試策略

DELIMITER $$-- 調試技巧：分步返回中間結果
CREATE FUNCTION debug_complex_calculation(input_val INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGINDECLARE step1 DECIMAL(10,2);DECLARE step2 DECIMAL(10,2);DECLARE final_result DECIMAL(10,2);SET step1 = input_val * 1.5;SET step2 = step1 + 100;SET final_result = step2 * 0.8;-- 調試時可以返回中間值-- RETURN step1;  -- 調試第一步-- RETURN step2;  -- 調試第二步RETURN final_result;  -- 最終結果
END $$-- 錯誤處理和驗證
CREATE FUNCTION robust_calculation(amount DECIMAL(10,2),rate DECIMAL(5,4),periods INT
)
RETURNS JSON
DETERMINISTIC
NO SQL
BEGINDECLARE result DECIMAL(12,2);DECLARE error_msg VARCHAR(100) DEFAULT '';-- 輸入驗證IF amount IS NULL THENSET error_msg = 'Amount cannot be NULL';ELSEIF amount <= 0 THENSET error_msg = 'Amount must be positive';ELSEIF rate IS NULL THENSET error_msg = 'Rate cannot be NULL';ELSEIF rate < 0 THENSET error_msg = 'Rate cannot be negative';ELSEIF periods IS NULL THENSET error_msg = 'Periods cannot be NULL';ELSEIF periods <= 0 THENSET error_msg = 'Periods must be positive';END IF;-- 如果有錯誤，返回錯誤信息IF error_msg != '' THENRETURN JSON_OBJECT('error', error_msg);END IF;-- 執行計算SET result = amount * POWER(1 + rate, periods);RETURN JSON_OBJECT('success', TRUE,'input', JSON_OBJECT('amount', amount, 'rate', rate, 'periods', periods),'result', ROUND(result, 2));
END $$DELIMITER ;

7. 與相關技術對比

7.1 自定義函數 vs 存儲過程 vs 視圖

特性	自定義函數	存儲過程	視圖
返回值	必須返回單個值	可以返回多個結果集或不返回	返回查詢結果集
調用方式	在SQL表達式中調用	使用CALL語句調用	像表一樣查詢
事務控制	? 不支持	? 完全支持	? 不適用
參數支持	只支持IN參數	支持IN、OUT、INOUT參數	? 不支持參數
數據修改	🔸 受限支持	? 完全支持	? 受限支持
性能	🔸 中等（計算密集）	? 高（批處理）	? 高（簡單查詢）
復用性	? 很好	? 很好	🔸 受限
調試難度	🔸 中等	🔸 中等	? 簡單

7.2 選擇指南

使用自定義函數的場景：

? 需要在SELECT語句中使用計算結果
? 計算邏輯相對簡單
? 需要返回單個值
? 不需要事務控制

使用存儲過程的場景：

? 需要復雜的業務邏輯處理
? 需要事務控制
? 需要返回多個結果集
? 涉及多表數據修改

使用視圖的場景：

? 簡化復雜查詢
? 提供數據安全性
? 查詢邏輯相對固定
? 不適合動態計算

8. 面試常見問題

8.1 基礎概念類

Q: 什么是MySQL自定義函數？它有什么作用？

A: MySQL自定義函數是用戶創建的可重復使用的程序單元，它接受參數、執行特定的計算邏輯并返回一個確定的值。

核心作用：

代碼復用：避免重復編寫相同的計算邏輯
性能優化：在數據庫層面進行計算，減少數據傳輸
邏輯封裝：將復雜的業務規則封裝成簡單的函數調用
一致性保證：確保相同的計算在不同地方得到一致的結果

Q: DETERMINISTIC和NOT DETERMINISTIC有什么區別？什么時候使用？

DETERMINISTIC（確定性）：相同的輸入參數總是產生相同的輸出結果，不依賴于外部變量
NOT DETERMINISTIC（非確定性）：相同的輸入可能產生不同的輸出，依賴于時間、隨機數等

使用場景：

-- DETERMINISTIC：純數學計算
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGINRETURN amount * 0.08;  -- 相同金額總是相同稅費
END $$-- NOT DETERMINISTIC：時間相關
CREATE FUNCTION get_age(birth_year INT)
RETURNS INT
NOT DETERMINISTIC
BEGINRETURN YEAR(CURDATE()) - birth_year;  -- 結果隨年份變化
END $$

8.2 實際應用類

Q: 在什么情況下使用自定義函數？請舉例說明

A: 自定義函數適用于以下場景：

復雜計算需要復用

-- 電商積分計算
CREATE FUNCTION calculate_points(amount DECIMAL(10,2), level VARCHAR(20))
RETURNS INT
DETERMINISTIC
BEGINDECLARE multiplier DECIMAL(3,2);CASE levelWHEN 'VIP' THEN SET multiplier = 2.0;WHEN 'Gold' THEN SET multiplier = 1.5;ELSE SET multiplier = 1.0;END CASE;RETURN FLOOR(amount * multiplier / 10);
END $$

數據格式化和轉換

-- 手機號脫敏
CREATE FUNCTION mask_phone(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGINIF LENGTH(phone) >= 7 THENRETURN CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4));END IF;RETURN phone;
END $$

Q: 如何優化自定義函數的性能？

A: 主要優化策略：

減少數據庫查詢

-- ? 多次查詢
SELECT name INTO name FROM users WHERE id = user_id;
SELECT email INTO email FROM users WHERE id = user_id;-- ? 一次查詢
SELECT name, email INTO name, email FROM users WHERE id = user_id;

使用合適的數據類型

-- ? 精確的數據類型
CREATE FUNCTION get_age(birth_date DATE)
RETURNS TINYINT  -- 年齡用TINYINT足夠

避免在WHERE子句中使用函數

-- ? 無法使用索引
SELECT * FROM orders WHERE calculate_total(id) > 1000;-- ? 預計算或使用其他方式
ALTER TABLE orders ADD total_amount DECIMAL(10,2);
SELECT * FROM orders WHERE total_amount > 1000;