You are viewing an older version of this section. View current production version.
MySQL Client Delimiters
Redefines the MySQL delimiter.
Syntax
DELIMITER delimiter_symbol
Remarks
When creating a function or a procedure using a MySQL-compatible client connected to SingleStore DB, 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 DB:
DELIMITER //
CREATE FUNCTION plus_one(a INT) RETURNS INT AS
DECLARE
b INT = a;
BEGIN
b += 1;
RETURN b;
END //
DELIMITER ;
All SingleStore DB (MemSQL) Procedural SQL (MPSQL) topics include this MySQL delimiter consideration.
