The default delimiter in the mysql 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.

Take 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 mysql 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:

MariaDB [test]> 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 knowledgebase.

Here's how the function could be successfully entered from the mysql 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.

\g and \G delimiters can always be used, even when a custom delimiter is specified.

Comments

Comments loading...