The simplest MySQL tutorial in history (40) "database variables 」,

The simplest MySQL tutorial in history (40) "database variables 」,

Tip: This series of blog posts have been synchronized to GitHub. The address is "mysql-tutorial". Thank you for your interest.Star,Fork, Error correction.

Variable

In the MySQL database, there are two types of variables:System VariablesAndCustom Variables.

According to the scope of the variables, they can be divided:

• Session-level variables: Valid only for the current client connection;
• Global Variables: Valid for any client connection.

System Variables

System variables, as the name implies, are the variables set by the system (all global variables) and used to control server performance. For exampleautocommit,wait_timeout.

Most of the time, we don't need to use system variables, but we still need to know that it can help us with special requirements when necessary.

First, check the system variables. Syntax:

• Basic syntax:show variables;

Run the following SQL statement for testing:

-- View the system variable show variables;

As shown in, MySQL contains 506 system variables on this server. In a closer step, we can view the specific system variable value. Syntax:

• Basic syntax:Select + @ variable name + [, @ variable name,..., @ variable name];

Run the following SQL statement for testing:

-- View the specific system variable values: select @ autocommit, @ version, @ version_compile_ OS, @ wait_timeout;

As shown in, we found the specific variable value. In addition,Any query operation with returned content is usedselectTo complete.

Next, we try to modify the system variables, first modify the session-level variables, and then modify the global-level variables.

You can use either of the following methods to modify session-level variables:

• Basic Syntax 1:Set variable name = value;
• Basic syntax 2:Set @ variable name = value;

Run the following SQL statement for testing:

-- Set the session-level variable set autocommit = 0; set @ wait_timeout = 20000; -- view the system variable select @ autocommit, @ wait_timeout;

As shown in, we modifiedautocommitAndwait_timeoutOnly applies to the session level, that is, only the current connection is valid. When a new window is opened again, we will find that all the variable values are restored.

For modifying global variables, the syntax is:

• Basic syntax:Set global variable name = value;

Run the following SQL statement for testing:

-- Set the global variable set global autocommit = 0; -- view the system variable select @ autocommit;

As shown in, when we modify the global variables, the effect is valid for any client connection. But, if a client is connected to the server before we modify the global variable and does not exit, the modification will not take effect for the current connection. You need to log on again to make the change take effect.

Custom Variables

Custom variables, as the name implies, are User-Defined variables and are session-level variables.

To distinguish between system variables and custom variablesThe user-defined variable must use one@Symbol. Syntax for setting custom variables:

• Basic syntax:Set @ variable name = value;

Run the following SQL statement for testing:

-- Set the custom variable set @ name = 'binguo '; -- view the custom variable select @ name;

Observe, we will find that there are some minor differences between viewing custom variables and system variables, that is, when viewing system variables,selectFollowed@@And when viewing custom variables,selectFollowed@. Note the following:In MySQL=Therefore, MySQL also provides another value assignment symbol.:=, That is, a colon and an equal sign..

In addition, MySQL allows us to retrieve data from the data table and assign values directly to variables. There are two methods:

1st types: Assign values while viewing the results. Syntax:

• Basic syntax:Select @ variable name: = field name from table name;

Run the following SQL statement for testing:

-- Obtain data from the data table and assign the User-Defined variable a value of select @ name = name from student; -- view the User-Defined variable select @ name;

As shown in, er, what is this? Well, careful students may have discovered it.selectStatement, we mistakenly:=Write=Then MySQL will=Process as a comparison symbol, andstudentThe table does not findbinguoThe matched name.0If the match is successful1. Next, modify the value assignment symbol and perform a test again:

-- Obtain data from the data table and assign the User-Defined variable the value select @ name: = name from student; -- view the User-Defined variable select @ name;

As shown in, we will find the aboveselectStatement: FromstudentRead data from the table, and assign values to the custom variables in turn.@nameAnd the value first assigned will be overwritten, and only the last value assignment result will be retained.

2nd types: Only assign values, not view results. Syntax:

• Basic syntax:Select + Field List + from + Table name + into + Variable list;

Run the following SQL statement for testing:

-- Obtain data from the data table and assign the select name from student into @ name to the custom variable directly; -- view the User-Defined variable select @ name; -- view the data in the student table select * from student;

As shown in, obviouslyEERORThe returned result contains more than one column. In fact, without restrictions, we directly retrieve data from the table and retrieve all the data. Therefore, we ignoreinto @nameThe returned result is allnameValue, which is more than one. In this case, the system reports an error, but assigns the first value of the retrieved data@nameThat is, when more than one record is retrieved, the system assigns the first value to the custom variable by default.

Although the preceding SQL statement has been modified@nameBut it is an incorrect value assignment method, which is uncontrollable. The result is often not what we want. MySQL imposes strict requirements on the above assignment method, so that only one record can be obtained at a time. Therefore, the correct method is to addwhereCondition to limit the query result to one, for example

-- Obtain data from the data table and assign the select name from student where id = 2 into @ name to the custom variable directly; -- view the custom variable select @ name;

As shown in, data is obtained and assigned a value.

Finally, I would like to emphasize that:User-Defined variables are all session-level variables, which will be affected as long as they are connected by the current user, regardless of the database..

Tip: Symbol[]The enclosed content, indicating the option; symbol+, Indicates the meaning of the connection.

---- ☆☆☆-- Return-> the simplest MySQL tutorial in history <-directory -- ☆☆☆ ----

Top

1

Step on

0

View comments