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

Today Xiaobian to share with you MySQL how to customize variables and statement end delimiter related knowledge points, detailed content, clear logic, I believe most people still know too much about this knowledge, so share this article for everyone to refer to, I hope you read this article after some gains, let's learn about it together.

store program

Sometimes it takes a lot of statements to perform a common function, and it's annoying to type so many statements one at a time in the client. The MySQL designer kindly provided us with something called a stored program, which encapsulates statements and then provides a simple way for users to invoke the stored program to execute those statements indirectly. Stored programs can be classified into stored routines, triggers, and events, depending on how they are invoked. Among them, storage routines can be subdivided into storage functions and storage procedures. Let's draw a picture to show:

Don't worry, we'll break through them one by one. But before we get into stored programs, we need to understand the concepts of custom variables and statement end delimiters in MySQL.

Introduction to custom variables

We often encounter fixed values in life, such as the number 100, the string 'hello,' and we call these fixed values constants. But sometimes for convenience, we use a symbol to represent a value that can change. For example, if we specify that the symbol a stands for the number 1, then we can let the symbol a stand for the number 2. We call this variable something whose value can change, and the symbol a is called the variable name of this variable. In MySQL, we can customize some of our own variables with SET statements, such as:

mysql> SET @a = 1;Query OK, 0 rows affected (0.00 sec)mysql>

The above statement indicates that we define a variable called a and assign the integer 1 to it. However, we need to pay attention to the design of MySQL uncle rules, in front of our custom variables must add an @ symbol (although a bit strange, but this is the rules of others, we will abide by it).

If we want to see the value of this variable later, we can just use SELECT, but we still need to put an @ sign before the variable name:

mysql> SELECT @a;+------+| @a |+------+| 1 |+------+1 row in set (0.00 sec)mysql>

The same variable can also store different types of values, for example, we assign a string value to variable a:

mysql> SET @a = 'hahaha';Query OK, 0 rows affected (0.01 sec)mysql> SELECT @a;+--------+| @a |+-----------+| hahaha |+-----------+1 row in set (0.00 sec)mysql>

In addition to assigning a constant to a variable, we can also assign a variable to another variable:

mysql> SET @b = @a;Query OK, 0 rows affected (0.00 sec)mysql> select @b;+-----------+| @b |+-----------+| hahaha |+-----------+1 row in set (0.00 sec)mysql>

So variables a and b have the same value 'wahahaha'!

We can also assign the result of a query to a variable if the query has only one value:

mysql> SET @a = (SELECT m1 FROM t1 LIMIT 1);Query OK, 0 rows affected (0.00 sec)mysql>

Another form of statement can be used to assign the result of a query to a variable:

mysql> SELECT n1 FROM t1 LIMIT 1 INTO @b;Query OK, 1 row affected (0.00 sec)mysql>

Because SELECT m1 FROM t1 LIMIT 1 and SELECT n1 FROM t1 LIMIT 1 result in only one value, they can be assigned directly to variables a or b. Let's look at the values of these two variables:

mysql> SELECT @a, @b;+------+------+| @a | @b |+------+------+| 1 | a |+------+------+1 row in set (0.00 sec)mysql>

If the result of our query is a record with multiple column values, we want to assign these values to different variables separately, and we can only use the INTO statement:

mysql> SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b;Query OK, 1 row affected (0.00 sec)mysql>

The result set of this query statement contains only one record. We assign the value of column m1 of this record to variable a and the value of column n1 to variable b.

end-of-statement delimiter

In the MySQL client's interactive interface, when we finish typing and press the Enter key, the MySQL client detects whether our input contains one of the three symbols;,\g, or\G, and if so, sends our input to the server. Thus, if we want to send multiple statements to the server at once, we need to write these statements in a single line, such as:

mysql> SELECT * FROM t1 LIMIT 1;SELECT * FROM t2 LIMIT 1;SELECT * FROM t3 LIMIT 1;+------+------+| m1 | n1 |+------+------+| 1 | a |+------+------+1 row in set (0.00 sec)+------+------+| m2 | n2 |+------+------+| 2 | b |+------+------+1 row in set (0.00 sec)+------+------+| m3 | n3 |+------+------+| 3 | c |+------+------+1 row in set (0.00 sec)mysql>

The reason for this inconvenience is that the MySQL client detects that the symbol used to end the input is the same as the symbol separating the statements! In fact, we can also use the delimiter command to customize MySQL's detection statement input end symbol, which is the so-called statement end delimiter, such as this:

mysql> delimiter $mysql> SELECT * FROM t1 LIMIT 1; -> SELECT * FROM t2 LIMIT 1; -> SELECT * FROM t3 LIMIT 1; -> $+------+------+| m1 | n1 |+------+------+| 1 | a |+------+------+1 row in set (0.00 sec)+------+------+| m2 | n2 |+------+------+| 2 | b |+------+------+1 row in set (0.00 sec)+------+------+| m3 | n3 |+------+------+| 3 | c |+------+------+1 row in set (0.00 sec)mysql>

The delimiter $command means that the end-of-statement delimiter is modified to $, which means that the MySQL client later detects that the end-of-statement delimiter is $. In the above example, although we entered 3 query statements ending in semicolon and pressed Enter, the input content was not submitted until we typed the $symbol and entered, MySQL client will submit our input content to the server. At this time, our input content already contains 3 independent query statements, so we returned 3 result sets.

We can also redefine the end-of-statement delimiter to be a string other than $that contains one or more characters, for example:

mysql> delimiter EOFmysql> SELECT * FROM t1 LIMIT 1; -> SELECT * FROM t2 LIMIT 1; -> SELECT * FROM t3 LIMIT 1; -> EOF+------+------+| m1 | n1 |+------+------+| 1 | a |+------+------+1 row in set (0.00 sec)+------+------+| m2 | n2 |+------+------+| 2 | b |+------+------+1 row in set (0.00 sec)+------+------+| m3 | n3 |+------+------+| 3 | c |+------+------+1 row in set (0.00 sec)mysql>

We use EOF here as the MySQL client to detect the end of the input symbol, is not very easy ah! Of course, this is just for the convenience of us entering multiple statements at once. After the input is completed, it is best to change back to our commonly used semicolon;

mysql> delimiter ;

Tip: We should avoid using the backslash (\) character as an end-of-statement delimiter because it is MySQL's escape character.

The above is "MySQL how to customize variables and statement end separators" all the content of this article, thank you for reading! I believe everyone has a great harvest after reading this article. Xiaobian will update different knowledge for everyone every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.