This guide details the use of DELIMITER to create MySQL functions on Infomaniak's Cloud Server.

Introduction

• When creating functions or stored procedures in MySQL, it is crucial to understand the role of delimiters.
• The correct use of delimiters is essential to avoid syntax errors that can occur due to the presence of multiple SQL statements in a single function or procedure definition.

Understanding the Delimiter

A delimiter is a character or sequence of characters used to separate SQL statements in a script. By default, MySQL uses the semicolon (;) as the delimiter. However, when creating functions, stored procedures, or triggers that contain multiple SQL statements, it is necessary to temporarily change the delimiter to avoid syntax errors.

When you create a function, procedure, or trigger, you often need to use multiple SQL statements within the BEGIN...END block. Since the semicolon (;) is also used to terminate these internal statements, MySQL might interpret the first semicolon as the end of the function definition, which would result in a syntax error. To avoid this problem, you need to change the delimiter during the function definition.

Create a simple function using custom delimiters

Before defining the function, you must tell MySQL that you will be using a different delimiter. In the example below, $$ is used as the new delimiter:

DELIMITER $$

With the new delimiter in place, you can now define your function. The CREATE FUNCTION includes the function body, where you can use internal SQL statements separated by semicolons without any issues:

CREATE FUNCTION hello_world()
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN 'Hello World';
END;
$$

In this example:

• CREATE FUNCTION hello_world(): declares the beginning of the definition of the hello_world function.
• RETURNS TEXT: specifies the data type that the function returns.
• LANGUAGE SQL: indicates that the language used for the function is SQL.
• BEGIN ... END: encapsulates the function's code. Inside, the semicolon is used to separate SQL statements.
• RETURN 'Hello World';: SQL statement that returns the string Hello World.

After defining the function, reset the delimiter to its default state (the semicolon). This allows you to continue executing the usual SQL instructions in your subsequent scripts:

DELIMITER ;