What is the DELIMITER in MySQL and what it’s used for?

The DELIMITER command is used to change the standard delimiter of MySQL commands. The standard command delimiter is the ';' character that defines the end of the query.

Example:

INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (2, 'NICK', 'WAHLBERG', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (4, 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (5, 'JOHNNY', 'LOLLOBRIGIDA', '2006-02-15 04:34:33');

Now let’s have a look at the following script. It consists of several INSERT queries, procedure creating and several more INSERT queries.

INSERT INTO actor VALUES (6, 'BETTE', 'NICHOLSON', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (7, 'GRACE', 'MOSTEL', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (8, 'MATTHEW', 'JOHANSSON', '2006-02-15 04:34:33');
CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
  SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');
  PREPARE stmt FROM @select;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
INSERT INTO actor VALUES (9, 'JOE', 'SWANK', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (10, 'CHRISTIAN', 'GABLE', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (11, 'ZERO', 'CAGE', '2006-02-15 04:34:33');

When executing a script, MySQL client parses queries by delimiting them by ';' and sends them to a server.

If we execute the script in whole, we get the syntax error - 'ERROR 1064 (42000): You have an error in your SQL syntax;...'. This is because the MySQL client has extracted the following command and tried to execute it.

CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');

The point is that some objects in MySQL may contain executable code in the BEGIN...END clause and commands in the code are delimited by the ';' character; such objects may include: stored procedures and functions, triggers, and events.

In this case, somehow we have to extract the create procedure command in whole. There is the DELIMITER client side command that allows you to set its conditional query delimiter. Any character or string can be set as a delimiter. The most common are $$ and //. In our example the ‘$$’ string is used.

Below is the script with using the DELIMITER command -

INSERT INTO actor VALUES (6, 'BETTE', 'NICHOLSON', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (7, 'GRACE', 'MOSTEL', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (8, 'MATTHEW', 'JOHANSSON', '2006-02-15 04:34:33');
-- Set new delimiter '$$'
DELIMITER $$
CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
  SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');
  PREPARE stmt FROM @select;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$$ -- End of command
-- Set default delimiter ';'
DELIMITER ;
INSERT INTO actor VALUES (9, 'JOE', 'SWANK', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (10, 'CHRISTIAN', 'GABLE', '2006-02-15 04:34:33');
INSERT INTO actor VALUES (11, 'ZERO', 'CAGE', '2006-02-15 04:34:33');

Now our script will be executed with no errors and the create procedure command will succeed. It's also important to note that the DELIMITER command may be set only once for the whole set of commands.

The example -

-- Set new delimiter '$$'
DELIMITER $$
CREATE PROCEDURE find_actors(IN actor_list VARCHAR(255))
BEGIN
  SET @select = CONCAT('SELECT * FROM actor WHERE actor_id IN (', actor_list, ')');
  PREPARE stmt FROM @select;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$$ -- End of command
CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT)
  RETURNS INT(11)
  READS SQL DATA
BEGIN
  DECLARE v_customer_id INT;
  DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
  SELECT customer_id INTO v_customer_id FROM rental
    WHERE return_date IS NULL AND inventory_id = p_inventory_id;
  RETURN v_customer_id;
END
$$ -- End of command
CREATE TRIGGER ins_film
  AFTER INSERT
  ON film
  FOR EACH ROW
BEGIN
  INSERT INTO film_text (film_id, title, description) VALUES (NEW.film_id, NEW.title, NEW.description);
END
$$ -- End of command
-- Set default delimiter ';'
DELIMITER ;

Source: http://buysql.com/mysql/42-delimiter-mysql.html