MySQL User-Defined Functions
Introduction
MySQL provides many built-in functions for manipulating data and performing calculations, but sometimes you need functionality that isn't available out of the box. This is where User-Defined Functions (UDFs) come in - they allow you to create custom functions to extend MySQL's capabilities according to your specific requirements.
In this tutorial, you'll learn how to create, use, and manage your own MySQL functions. These user-defined functions help you encapsulate complex logic, improve code reusability, and streamline your database operations.
What are User-Defined Functions?
A User-Defined Function (UDF) is a custom function that you create to perform operations not available through MySQL's built-in functions. Once created, you can use these functions just like any built-in MySQL function in your SQL queries.
There are three types of User-Defined Functions in MySQL:
- Scalar Functions: Return a single value
- Aggregate Functions: Operate on sets of values and return a single summary value
- Table Functions: Return a table (available in MySQL 8.0+)
Creating Your First User-Defined Function
Let's start by creating a simple scalar function. The basic syntax for creating a function is:
CREATE FUNCTION function_name([parameter list])
RETURNS return_data_type
[characteristic ...]
BEGIN
-- Function body
-- Statements
RETURN value;
END;
Example: Simple Greeting Function
Let's create a function that takes a name as input and returns a greeting message:
DELIMITER //
CREATE FUNCTION hello_user(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
RETURN CONCAT('Hello, ', name, '! Welcome to MySQL functions.');
END//
DELIMITER ;
In this example:
DELIMITER //changes the statement delimiter temporarily so we can use semicolons within the function definitionDETERMINISTICindicates that the function always returns the same result for the same input parametersCONCATis a built-in MySQL function that joins multiple strings
Using the Function
Now let's call our function:
SELECT hello_user('John');
Output:
'Hello, John! Welcome to MySQL functions.'
You can also use it in more complex queries:
SELECT username, hello_user(username) AS greeting
FROM users
LIMIT 3;
Output:
+----------+------------------------------------------+
| username | greeting |
+----------+------------------------------------------+
| maria | Hello, maria! Welcome to MySQL functions.|
| alex | Hello, alex! Welcome to MySQL functions. |
| sarah | Hello, sarah! Welcome to MySQL functions.|
+----------+------------------------------------------+
Function Characteristics
When creating UDFs, you can specify characteristics that define their behavior:
DETERMINISTIC: The function always returns the same result for the same input parametersNOT DETERMINISTIC: The function might return different results for the same input (default)CONTAINS SQL: The function contains SQL statements, but neither reads nor modifies dataNO SQL: The function contains no SQL statementsREADS SQL DATA: The function contains statements that read data but don't modify itMODIFIES SQL DATA: The function contains statements that may modify data
Example with characteristics:
DELIMITER //
CREATE FUNCTION calculate_discount(price DECIMAL(10,2), discount_percent INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN price * (1 - discount_percent/100);
END//
DELIMITER ;
Working with Variables in Functions
You can declare and use variables within your functions to store intermediate results:
DELIMITER //
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2), tax_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE tax_amount DECIMAL(10,2);
SET tax_amount = amount * (tax_rate / 100);
RETURN amount + tax_amount;
END//
DELIMITER ;
Using the function:
SELECT calculate_tax(100.00, 7.5);
Output:
107.50
Control Flow in Functions
MySQL functions support control flow statements like IF, CASE, and loops:
IF-ELSE Example
DELIMITER //
CREATE FUNCTION get_discount_level(customer_spent DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE discount_level VARCHAR(20);
IF customer_spent < 100 THEN
SET discount_level = 'Bronze';
ELSEIF customer_spent < 500 THEN
SET discount_level = 'Silver';
ELSEIF customer_spent < 1000 THEN
SET discount_level = 'Gold';
ELSE
SET discount_level = 'Platinum';
END IF;
RETURN discount_level;
END//
DELIMITER ;
Usage:
SELECT get_discount_level(750);
Output:
'Gold'
CASE Statement Example
DELIMITER //
CREATE FUNCTION weekday_name(day_number INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE day_name VARCHAR(10);
CASE day_number
WHEN 1 THEN SET day_name = 'Monday';
WHEN 2 THEN SET day_name = 'Tuesday';
WHEN 3 THEN SET day_name = 'Wednesday';
WHEN 4 THEN SET day_name = 'Thursday';
WHEN 5 THEN SET day_name = 'Friday';
WHEN 6 THEN SET day_name = 'Saturday';
WHEN 7 THEN SET day_name = 'Sunday';
ELSE SET day_name = 'Invalid';
END CASE;
RETURN day_name;
END//
DELIMITER ;
Usage:
SELECT weekday_name(3);
Output:
'Wednesday'
Working with Loops in Functions
You can use loops in functions for repetitive tasks:
DELIMITER //
CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 1;
DECLARE i INT DEFAULT 1;
WHILE i <= n DO
SET result = result * i;
SET i = i + 1;
END WHILE;
RETURN result;
END//
DELIMITER ;
Usage:
SELECT factorial(5);
Output:
120
Creating Functions with Error Handling
To make your functions more robust, you can add error handling:
DELIMITER //
CREATE FUNCTION divide_numbers(numerator DECIMAL(10,2), denominator DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE result DECIMAL(10,2);
-- Error handling for division by zero
IF denominator = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot divide by zero';
END IF;
SET result = numerator / denominator;
RETURN result;
END//
DELIMITER ;
Usage:
SELECT divide_numbers(10, 2);
Output:
5.00
Attempting division by zero will trigger the error:
SELECT divide_numbers(10, 0);
Output:
Error: Cannot divide by zero
Real-World Examples
Let's look at some practical examples of user-defined functions that solve common business problems:
1. Calculating Age from Birthdate
DELIMITER //
CREATE FUNCTION calculate_age(birthdate DATE)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN FLOOR(DATEDIFF(CURRENT_DATE(), birthdate) / 365);
END//
DELIMITER ;
Usage in a query:
SELECT
name,
birthdate,
calculate_age(birthdate) AS age
FROM employees;
2. Generating Username from Name
DELIMITER //
CREATE FUNCTION generate_username(first_name VARCHAR(50), last_name VARCHAR(50), birth_year YEAR)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE username VARCHAR(50);
SET username = LOWER(CONCAT(
LEFT(first_name, 1),
REPLACE(LOWER(last_name), ' ', ''),
SUBSTRING(birth_year, 3, 2)
));
RETURN username;
END//
DELIMITER ;
Usage:
SELECT generate_username('John', 'Smith', '1990');
Output:
'jsmith90'
3. Address Formatting Function
DELIMITER //
CREATE FUNCTION format_address(street VARCHAR(100), city VARCHAR(50), state CHAR(2), zip VARCHAR(10))
RETURNS VARCHAR(200)
DETERMINISTIC
BEGIN
RETURN CONCAT(street, ', ', city, ', ', state, ' ', zip);
END//
DELIMITER ;
Usage with a table:
SELECT
customer_id,
format_address(street, city, state, zip_code) AS full_address
FROM customers;
Managing User-Defined Functions
Viewing Function Information
To see all functions in your database:
SHOW FUNCTION STATUS WHERE db = 'your_database_name';
To view the definition of a specific function:
SHOW CREATE FUNCTION function_name;
Altering Functions
To modify a function, you typically need to drop and recreate it:
DROP FUNCTION IF EXISTS function_name;
-- Then recreate with the new definition
Dropping Functions
To remove a function:
DROP FUNCTION IF EXISTS function_name;
Security Considerations
When creating functions, keep in mind:
- Functions run with the privileges of the creator by default
- Use
SQL SECURITY DEFINERto make a function execute with the permissions of the user who defined it - Use
SQL SECURITY INVOKERto make a function run with the permissions of the user who calls it
Example:
DELIMITER //
CREATE FUNCTION get_customer_status(customer_id INT)
RETURNS VARCHAR(20)
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
DECLARE status VARCHAR(20);
SELECT status_name INTO status
FROM customer_statuses
WHERE id = customer_id;
RETURN status;
END//
DELIMITER ;
Best Practices
- Name your functions clearly: Use descriptive names that indicate what the function does
- Document your functions: Add comments to explain complex logic
- Specify characteristics: Always define if your function is DETERMINISTIC or NOT DETERMINISTIC
- Handle errors: Include proper error checking for parameters
- Test thoroughly: Test your functions with edge cases
- Consider performance: Be mindful of resource usage in functions
- Limit function complexity: Break complex logic into smaller functions when possible
Advanced Topics
Creating Aggregate Functions
MySQL 8.0+ allows you to create aggregate functions. Here's an example of a custom median function:
DELIMITER //
CREATE AGGREGATE FUNCTION median(val DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
DECLARE result DECIMAL(10,2);
WITH ordered_values AS (
SELECT val, ROW_NUMBER() OVER (ORDER BY val) as row_num,
COUNT(*) OVER () as total_rows
FROM tmp_table
)
SELECT AVG(val) INTO result
FROM ordered_values
WHERE row_num BETWEEN total_rows/2.0 AND total_rows/2.0 + 1;
RETURN result;
END//
DELIMITER ;
Creating Table Functions
Table functions (introduced in MySQL 8.0) return a table result:
DELIMITER //
CREATE FUNCTION get_employee_projects(emp_id INT)
RETURNS TABLE (project_id INT, project_name VARCHAR(100), role VARCHAR(50))
READS SQL DATA
BEGIN
RETURN TABLE (
SELECT p.project_id, p.name, ep.role
FROM projects p
JOIN employee_projects ep ON p.project_id = ep.project_id
WHERE ep.employee_id = emp_id
);
END//
DELIMITER ;
Summary
User-Defined Functions in MySQL are a powerful way to extend the database's functionality and make your SQL code more maintainable and reusable. Key points to remember:
- UDFs encapsulate logic that can be reused across many queries
- There are scalar, aggregate, and table functions
- Functions can include variables, control flow statements, and error handling
- Best practices include clear naming, documentation, and proper error handling
- Functions can significantly simplify complex calculations and data transformations
By mastering User-Defined Functions, you can write more efficient, cleaner SQL code and extend MySQL's capabilities to meet your specific needs.
Practice Exercises
- Create a function that calculates the total price of an order with tax and shipping costs
- Write a function that converts a number to its Roman numeral representation
- Create a function to calculate the distance between two geographic coordinates
- Develop a function that returns a password strength rating (weak/medium/strong) based on a set of rules
- Write a function that formats a phone number into a consistent format
Additional Resources
- MySQL Official Documentation on Functions
- MySQL 8.0 SQL Syntax for User-Defined Functions
- MySQL Performance Considerations for Stored Programs
Happy coding with MySQL User-Defined Functions!
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)