MySQL DROP FUNCTION

Summary: in this tutorial, you will learn how to use the MySQL DROP FUNCTION statement to drop a stored function.

Introduction to MySQL DROP FUNCTION statement

The DROP FUNCTION statement drops a stored function. Here is the syntax of the DROP FUNCTION statement:

DROP FUNCTION [IF EXISTS] function_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the stored function that you want to drop after the DROP FUNCTION keywords.

The IF EXISTS option allows you to conditionally drop a stored function if it exists. It prevents an error from arising if the function does not exist.

MySQL DROP FUNCTION example

We’ll use the orders table in the sample database for the demonstration:

First, create a new function called OrderLeadTime that calculates the number of days between the ordered date and the required date:

DELIMITER $$

CREATE FUNCTION OrderLeadTime (
    orderDate DATE,
    requiredDate DATE
) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN requiredDate - orderDate;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Second, use the DROP FUNCTION statement to drop the function OrderLeadTime:

DROP FUNCTION OrderLeadTime;Code language: SQL (Structured Query Language) (sql)

Third, use the DROP FUNCTION to drop a non-existing function:

DROP FUNCTION IF EXISTS NonExistingFunction;Code language: SQL (Structured Query Language) (sql)

MySQL issued a warning:

0 row(s) affected, 1 warning(s): 1305 FUNCTION classicmodels.NonExistingFunction does not existCode language: CSS (css)

If you want to view the warning in detail, use the SHOW WARNINGS statement:

SHOW WARNINGS;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the DROP FUNCTION statement to drop a stored function.