MySQL Client Delimiters

Syntax

DELIMITER delimiter_symbol

Remarks

When creating a function or a procedure using a MySQL-compatible client connected to SingleStore, you must change the client delimiter to ensure that the function or procedure definition is correctly passed to the server as a single statement. By default, all MySQL clients use a semicolon (;) as a statement delimiter. A problem arises when creating functions or procedures because they use semicolons as statement delimiters within the function body. Therefore, you must change the delimiter setting before creating your function or procedure, and then set it back to a semicolon after the alternate delimiter is no longer needed.

The DELIMITER commands must be on independent lines. These lines are interpreted by the client, not the server. When creating stored procedures or functions from your own client application created in a language such as Java, C++, or Python, the delimiters are not needed.

To change the MySQL delimiter, execute the following command before creating a new function or procedure:

DELIMITER //

When you’ve finished defining functions or procedures, make sure to change the MySQL delimiter back to a semicolon:

DELIMITER ;

Example

The function below uses the delimiter //, which can be executed directly in a MySQL-compatible client connected to SingleStore:

DELIMITER //
CREATE FUNCTION plus_one(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    b += 1;
    RETURN b;
  END //
DELIMITER ;

All SingleStore Procedural SQL (PSQL) topics include this MySQL delimiter consideration.