Shulou(Shulou.com)05/31 Report--

How to define and use delimiter in Mysql? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

By default, delimiter is a semicolon (;).

In the command line client, if a line of command ends with a semicolon, mysql will execute the command when you enter. Such as entering the following statement

Mysql > select * from test_table

Then enter, and MySQL will execute the statement immediately.

But sometimes, you don't want MySQL to do that. More statements may be entered, and the statement contains a semicolon.

If you try to enter the following statement in the command line client

Mysql > CREATE FUNCTION `SHORTEN` (S VARCHAR, N INT) mysql > RETURNS varchar (255) mysql > BEGINmysql > IF ISNULL (S) THENmysql > RETURN'; mysql > ELSEIF N RETURN LEFT (S, N); mysql > ELSEmysql > IF CHAR_LENGTH (S) RETURNS; mysql > ELSEmysql > RETURN CONCAT (LEFT (S, NLV 10), '...', RIGHT (S, 5)); mysql > END IF;mysql > END IF;mysql > END

By default, it is not possible to wait until the user has entered all these statements before executing the entire statement.

Because mysql automatically executes when it encounters a semicolon.

That is, when the statement RETURN'; occurs, the mysql interpreter is about to execute.

In this case, you need to replace delimiter with other symbols, such as / / or $$.

Mysql > delimiter / / mysql > CREATE FUNCTION `SHORTEN` (S VARCHAR, N INT) mysql > RETURNS varchar (255) mysql > BEGINmysql > IF ISNULL (S) THENmysql > RETURN'; mysql > ELSEIF N RETURN LEFT (S, N); mysql > ELSEmysql > IF CHAR_LENGTH (S) RETURNS; mysql > ELSEmysql > RETURN CONCAT (LEFT (S, NM10),', RIGHT (S, 5)); mysql > END IF;mysql > END IF;mysql > END;//

In this way, the mysql interpreter will execute this statement only when / / appears.

Beyond that. In the stored procedure of mysql, one thing to note is:

Drop PROCEDURE if EXISTS proc_while_test;delimiter;; CREATE DEFINER = root@localhost PROCEDURE proc_while_test (IN n int) BEGIN DECLARE i int; DECLARE s int; SET i = 0; SET s = 0; WHILE i delimiter / / mysql > create trigger upd_check before update on account-> for each row-> begin-> if new.amount

Set new.amount=0;-> elseif new.amount > 100 then-> set new.amount= 100;-> end if;-> end;-> / / Query OK, 0 rows affected (0.00 sec) mysql > delimiter

Above, set the delimiter to / / first.

The statement is not executed as a whole until the next / / is encountered.

After execution, the last line, delimiter;, resets the mysql delimiter to the semicolon

If not modified, all delimiters in this session will be / /.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.