The objective of the command DELIMITERis to keep open the creation of the statements that are to compose the procedures to avoid premature termination, but why?
STORED PROCEDURES
When we write a regular statement in MySQL, we get something like this:
SELECT * FROM tabla;
Where we clearly notice that the symbol that indicates the term of it is the;
Now in the case of creating a PA , we have this:
DELIMITER //
CREATE PROCEDURE extraeUsuario(IN idUser INT)
BEGIN
SELECT * FROM users WHERE id = idUser;
END;
//
In the previous syntax, we have that the use of ;as the end indicator of a statement is replaced by //so that when we are writing this from the console, pressing ENTER does not end the creation of it on the line of SELECT.
Once the above is done, we can indicate that the entire PA statement starts where it first appears //and ends until this same symbol exists again.
TRIGGERS
case one
When we write a Trigger, we have a statement similar to this:
DELIMITER //
CREATE TRIGGER multiplicaId BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET New.id = (SELECT MAX(id) * 10 FROM users);
END;
//
Where in the same way we use the DELIMITERto indicate the start and end of a block of code, again to prevent its execution from TRIGGERbeing carried out as soon as it reads the ;of the line SET.
Mainly at the database manager console level, omitting the use of this operator; can lead to an error like this:
ERROR 1064 (42000): 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 '' at line 4
Although you were able to create them without the help of this operator, you should take it into account for its implementation when you execute the creation of these structures and they have in themselves the use of BEGINand END.
case 2
A TRIGGERcan be explicitly created without the need for DELIMITERstart and end when it lacks the: statements BEGIN, ENDwhich tells us that the statement has a simple structure.
The following triggermay be created without the use of the delimiter
CREATE TRIGGER multiplicaIds BEFORE INSERT ON users
FOR EACH ROW
SET New.id = (SELECT MAX(id) * 10 FROM users);
USE OF BEGINandEND
One way to identify if we require a complex structure and therefore will need BEGINand ENDis if, for example, we will have multiple statements SELECTwithin the same PA, for example:
DELIMITER //
CREATE PROCEDURE filtraDatos(IN id1 INT, IN id2 INT)
BEGIN
SELECT * FROM tabla1 WHERE id = id1;
SELECT * FROM tabla2 WHERE id = id2;
END;
//
It refers to a delimiter for our queries SQL, it allows us to tell MySQLthat everything before a delimiter is part of a single block of code , you can choose any delimiter .
Additional data and to take into account:
They are typically used when defining functions, stored procedures, and triggers where you need to define multiple statements. You define a delimitadordifferent, like $$, which is used to define the end of the whole procedure , but within it, the individual statements end in **;**That way, when the code is executed on the client mysql, the client can tell where the whole procedure ends. procedure and execute it as a unit instead of executing the individual statements inside it.
Note that the keyword DELIMITERis a command line function of the client mysql(and some other clients) only and not a normal language featureMySQL. It won't work if you try to pass it through a programming language APIto MySQL. Some other clients like PHPMyAdminhave other methods to specify a non-default delimiter.
STORED PROCEDURES
When we write a regular statement in MySQL, we get something like this:
Where we clearly notice that the symbol that indicates the term of it is the
;
Now in the case of creating a PA , we have this:
In the previous syntax, we have that the use of
;
as the end indicator of a statement is replaced by//
so that when we are writing this from the console, pressing ENTER does not end the creation of it on the line ofSELECT
.Once the above is done, we can indicate that the entire PA statement starts where it first appears
//
and ends until this same symbol exists again.TRIGGERS
case one
When we write a Trigger, we have a statement similar to this:
Where in the same way we use the
DELIMITER
to indicate the start and end of a block of code, again to prevent its execution fromTRIGGER
being carried out as soon as it reads the;
of the lineSET
.Mainly at the database manager console level, omitting the use of this operator; can lead to an error like this:
Although you were able to create them without the help of this operator, you should take it into account for its implementation when you execute the creation of these structures and they have in themselves the use of
BEGIN
andEND
.case 2
A
TRIGGER
can be explicitly created without the need forDELIMITER
start and end when it lacks the: statementsBEGIN
,END
which tells us that the statement has a simple structure.The following
trigger
may be created without the use of the delimiterUSE OF
BEGIN
andEND
One way to identify if we require a complex structure and therefore will need
BEGIN
andEND
is if, for example, we will have multiple statementsSELECT
within the same PA, for example:The same
PA
one that we invoke in this way:References
DELIMITER
DELIMITER:
It refers to a delimiter for our queries
SQL
, it allows us to tellMySQL
that everything before a delimiter is part of a single block of code , you can choose any delimiter .Additional data and to take into account:
They are typically used when defining functions, stored procedures, and triggers where you need to define multiple statements. You define a
delimitador
different, like$$
, which is used to define the end of the whole procedure , but within it, the individual statements end in**;**
That way, when the code is executed on the clientmysql
, the client can tell where the whole procedure ends. procedure and execute it as a unit instead of executing the individual statements inside it.Note that the keyword
DELIMITER
is a command line function of the clientmysql
(and some other clients) only and not a normal language featureMySQL
. It won't work if you try to pass it through a programming languageAPI
toMySQL
. Some other clients likePHPMyAdmin
have other methods to specify a non-default delimiter.