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

Preamble

• 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 a 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, resulting in a syntax error. To work around this issue, you must change the delimiter during the function definition.

Creating 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 start of the definition of the function hello_world.
• 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 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 regular SQL statements in your subsequent scripts:

DELIMITER ;