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)：函数需要一个整数输入，命名为xRETURNS 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有什么区别？什么时候使用？
A:
- 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;
9. 学习总结
9.1 核心知识回顾
通过这份学习笔记，你现在应该掌握了：
基础语法：
CREATE FUNCTION的基本结构和各部分含义DELIMITER的使用原因和正确方法- 函数特性的选择和应用场景
编程构造：
- 变量声明（
DECLARE）和赋值（SET） - 条件判断（
IF...THEN...ELSE、CASE...WHEN） - NULL值处理的重要性和方法
实际应用：
- 数学计算、字符串处理、日期时间函数
- 电商、教育、金融等业务场景的函数设计
- 性能优化和错误处理的最佳实践
9.2 下一步学习建议
初学者（刚完成本教程）：
- 练习编写20-30个不同类型的简单函数
- 尝试将日常计算需求封装成函数
- 学会调试和测试函数
进阶学习者：
- 学习存储过程（Stored Procedures）
- 学习触发器（Triggers）
- 深入理解MySQL的执行计划和性能优化
实践建议：
- 在实际项目中应用所学知识
- 关注函数的性能表现和优化
- 建立自己的函数库和最佳实践
记住：好的程序员是通过不断练习和学习成长的。每掌握一个概念，都要确保真正理解后再继续。祝你学习愉快！
树莓派摄像头rpicam-apps)
![[拓扑优化] 1.概述](http://pic.xiahunao.cn/[拓扑优化] 1.概述)
，全文最详细教程)
