Delimiters

The default delimiter in the mariadb client is the semicolon.

When creating stored programs from the command-line, it is likely you will need to differentiate between the regular delimiter and a delimiter inside a BEGIN END block. To understand better, consider the following example:

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
 DECLARE x TINYINT;
 SET x = 42;
 RETURN x;
END;

If you enter the above line by line, the mariadb client will treat the first semicolon, at the end of the DECLARE x TINYINT line, as the end of the statement. Since that's only a partial definition, it will throw a syntax error, as follows:

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
DECLARE x TINYINT;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version
 for the right syntax to use near '' at line 3

The solution is to specify a distinct delimiter for the duration of the process, using the DELIMITER command. The delimiter can be any set of characters you choose, but it needs to be a distinctive set of characters that won't cause further confusion. // is a common choice, and used throughout the documentation.

Here's how the function could be successfully entered from the mariadb client with the new delimiter.

DELIMITER //

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE x TINYINT;
  SET x = 42;
  RETURN x;
END 

//

DELIMITER ;

At the end, the delimiter is restored to the default semicolon. The \g and \G delimiters can always be used, even when a custom delimiter is specified.

_{This page is licensed: CC BY-SA / Gnu FDL}