Delimiters in MySQL
Delimiters other than the default ;
are typically used when defining functions, stored procedures, and triggers wherein you must define multiple statements. You define a different delimiter like $$
which is used to define the end of the entire procedure, but inside it, individual statements are each terminated by ;
. That way, when the code is run in the mysql
client, the client can tell where the entire procedure ends and execute it as a unit rather than executing the individual statements inside.
Note that the DELIMITER
keyword is a function of the command line mysql
client (and some other clients) only and not a regular MySQL language feature. It won't work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.
Example:
DELIMITER $$
/* This is a complete statement, not part of the procedure, so use the custom delimiter $$ */
DROP PROCEDURE my_procedure$$
/* Now start the procedure code */
CREATE PROCEDURE my_procedure ()
BEGIN
/* Inside the procedure, individual statements terminate with ; */
CREATE TABLE tablea (
col1 INT,
col2 INT
);
INSERT INTO tablea
SELECT * FROM table1;
CREATE TABLE tableb (
col1 INT,
col2 INT
);
INSERT INTO tableb
SELECT * FROM table2;
/* whole procedure ends with the custom delimiter */
END$$
/* Finally, reset the delimiter to the default ; */
DELIMITER ;
Attempting to use DELIMITER
with a client that doesn't support it will cause it to be sent to the server, which will report a syntax error. For example, using PHP and MySQLi:
$mysqli = new mysqli('localhost', 'user', 'pass', 'test');
$result = $mysqli->query('DELIMITER $$');
echo $mysqli->error;
Errors with:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'DELIMITER $$' at line 1
How to change mysql Delimiter inside Kohana framework during call one model method?
As you can see in this answer, you cannot use the DELIMITER
via PHP as it is a command line statement. Just leave it out and use an array of queries (Kohana sadly does not support multiple queries via one call to the DB
)
MySQL delimiter syntax error
DELIMITER
is not a MySQL command. It's a command that your MySQL client needs to support. I was running PHPMyAdmin 2.8.2.4, which didn't support it. When I upgraded to the newest version, which is currently 3.4.9, it worked just fine. Your MySQL version has nothing to do with DELIMITER
and whether it's supported or not.
Related Topics
Pass Value from HTML Form to PHP Without Submitting the Form
Why Does (0 == 'Hello') Return True in PHP
Set_Error_Handler() Doesn't Work for Fatal Error
Simple PHP Stuff:Variable Evaluation
PHP Array_Sum on Multi Dimensional Array
How to Get Http Url of File Uploaded to Ftp Server
Undefined Offset When Using PHP Explode()
"Warning: MySQL_Query(): Supplied Argument Is Not a Valid MySQL-Link Resource"
PHP for ; Foreach Variable Scope
PHP Pthreads: Fatal Error: Class 'Thread' Not Found
Converting to Date in PHP from Yyyymmdd Format
PHP Values of One Array to Key of Another Array
How to Install PHP_Id3 on Wamp
Single Result from Sum with MySQLi
Rewrite All Queries to Not Need the .PHP Extension Using a Mod_Rewrite Rewriterule