{"id":8488,"date":"2019-09-06T21:33:32","date_gmt":"2019-09-07T04:33:32","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8488"},"modified":"2023-11-29T18:43:49","modified_gmt":"2023-11-30T01:43:49","slug":"mysql-delimiter","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-delimiter\/","title":{"rendered":"MySQL Delimiter"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to change the default MySQL delimiter by using the DELIMITER<\/code> command.<\/p>\n\n\n\n

When you want to execute multiple SQL statements<\/a>, you use the semicolon (;<\/code>) to separate two statements, as shown in the following example:<\/p>\n\n\n

SELECT<\/span> * FROM<\/span> products;\n\nSELECT<\/span> * FROM<\/span> customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
A MySQL client program, such as MySQL Workbench or  the mysql program, uses the default delimiter (;<\/code>) to separate statements and execute each separately.<\/p>\n\n\n\n
However, a stored procedure consists of multiple statements separated by a semicolon (;<\/code>).<\/p>\n\n\n\n
If you use a MySQL client program to define a stored procedure that contains semicolons, the MySQL client program will not treat the entire stored procedure as a single statement; instead, it will recognize it as multiple statements.<\/p>\n\n\n\n
Therefore, it is necessary to temporarily redefine the delimiter so that you can pass the entire stored procedure to the server as a single statement.<\/p>\n\n\n\n
To redefine the default delimiter, you use the DELIMITER<\/code> command as follows:<\/p>\n\n\n
DELIMITER delimiter_character<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The delimiter_character<\/code> may consist of a single character or multiple characters, such as \/\/<\/code> or $$<\/code>. However, you should avoid using the backslash (\\<\/code>) because it’s the escape character<\/em> in MySQL.<\/p>\n\n\n\n
MySQL uses backslash (\\) as the escape character, which allows you to include special characters within strings without triggering syntax errors. For example, you can use the escape character to include a single quote in a string like this: SELECT 'It\\'s a sunny day';<\/code><\/p>\n\n\n\n
The following example illustrates how to change the current delimiter to \/\/<\/code>:<\/p>\n\n\n
DELIMITER \/\/<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After you change the delimiter, you can use the new delimiter to end a statement, as follows:<\/p>\n\n\n
DELIMITER \/\/\n\nSELECT<\/span> * FROM<\/span> customers \/\/\n\nSELECT<\/span> * FROM<\/span> products \/\/<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To revert to the default delimiter, which is a semicolon (;<\/code>), you use the following statement:<\/p>\n\n\n
DELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Utilizing MySQL DELIMITER for stored procedures<\/h2>\n\n\n\n
Usually, a stored procedure contains multiple statements separated by semicolons (;). <\/p>\n\n\n\n
To compile the entire stored procedure as a single compound statement, you must temporarily change the delimiter from the semicolon (;<\/code>) to another delimiter such as $$<\/code> or \/\/<\/code>:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> PROCEDURE<\/span> CreatePersonTable()\nBEGIN<\/span>\n    -- drop persons table <\/span>\n    DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> persons;\n    \n    -- create persons table<\/span>\n    CREATE<\/span> TABLE<\/span> persons(\n        id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n        first_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n        last_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n    );\n    \n    -- insert data into the persons table<\/span>\n    INSERT<\/span> INTO<\/span> persons(first_name, last_name)\n    VALUES<\/span>('John'<\/span>,'Doe'<\/span>),\n\t\t  ('Jane'<\/span>,'Doe'<\/span>);\n\t\n    -- retrieve data from the persons table<\/span>\n    SELECT<\/span> id<\/span>, first_name, last_name \n    FROM<\/span> persons;\nEND<\/span> $$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this code:<\/p>\n\n\n\n
Note that you will learn the syntax of creating a stored procedure<\/a> in the next tutorial.<\/p>\n\n\n\n