JIYIK CN >

In this tutorial article, we will explain how to declare variables in SQL code for MySQL database.

In SQL scripts, you can use variables to store values ​​and use them in place of literals during the execution of a series of commands.

User-defined variables in MySQL

MySQL recognizes different types of variables. The first type is user-defined variables, @identified by a symbol as a prefix. In MySQL, you can access user-defined variables without declaring or initializing them first. If you do this, the variable is assigned a NULL value when initialized. For example, if you use a variable SELECTwithout assigning it a value, as in this case.

SELECT @SomeVariable;

MySQL will return a NULL value.

Initialize user-defined variables

To initialize a user defined variable, you need to use the <init> SETor SELECT<value> statement. You can initialize many variables at once by separating each assignment statement with a comma as shown below.

SET @FirstVar=1, @SecondVar=2;

Once you assign a value to a variable, it will have a type based on the value given. In the previous example, the types of @FirstVarand @SecondVarare int.

The lifecycle of a user-defined variable lasts as long as the session is active, and it is not visible to other sessions. Once the session is closed, the variable disappears.

There are 5 data types that can be assigned to user-defined variables.

• String (binary or non-binary
• Integer
• Decimal
• floating point
• NULL, which can be associated with any type.

To assign a value to a variable, you can use the symbols =or :=. The following two statements have the same effect.

SET @MyIntVar = 1;
SET @MyIntVar := 1;

SELECTUse variables as fields in a statement

Variables can be SELECTpart of the fields list of a select statement. When you specify fields in a select, you can mix variables and field names, as in this example.

SET @IndexVar := 1;
SELECT @IndexVar, ISBN FROM Books;

Declaring local variables in MySQL

Local variables do not require a prefix in their names @, but they must be declared before they are used. To declare a local variable, you can use DECLAREthe statement, or STORED PROCEDUREuse it as a parameter in a declaration.

When you declare a local variable, you can choose to assign it a default value. If you do not assign any default value, then the variable will be initialized to a NULL value.

Every variable lives in a scope, BEGIN ... ENDdefined by the block containing its declaration.

The following examples illustrate two different ways of using local variables: as procedure parameters and as variables inside a procedure.

DELIMITER $$

CREATE PROCEDURE GetUpdatedPrices(itemcount INT)
BEGIN
	DECLARE factor DECIMAL(5, 2);
	SET factor:=3.45;
	SELECT PartNo, Description, itemcount * factor * ListPrice FROM Catalogue;
END
$$

DELIMITER ;

In the previous example, the variable itemcountis used as a parameter to pass the value to the procedure. SELECTThe variable is then used in the statement to multiply the field obtained from the table ListPrice. The local variable factoris used to store a decimal value that is multiplied to obtain the price.

Declaring system variables in MySQL

There is a third type of variable called a system variable, which is used to store values ​​(variables) that affect a single client connection or values ​​( variables) SESSIONthat affect the operation of the server as a whole .GLOBAL

System variables are normally set at server startup. To do this, you can use the command line or include SETstatements in option files. However, their values ​​can be modified in SQL scripts.

System variables can be identified by using a double @-sign prefix or SETby using the words GLOBALor in an IN statement. Another way to distinguish between and system variables is to use a second prefix: or . The following are some examples of how to assign values ​​to system variables.SESSIONGLOBALSESSIONglobalsession

-- Alternative ways to set session system variables:
SET interactive_timeout=30000;
SET SESSION interactive_timeout=30000;
SET @@interactive_timeout=30000;
SET @@local.interactive_timeout=30000;

-- Alternative ways to set global system variables:
SET @@global.interactive_timeout=30000;
SET GLOBAL interactive_timeout=30000;

To view the system variables used in your session or server, use SHOW VARIABLESthe statement. If you want to get the value of some specific variable, you can add a comparison operator to filter the list. For example:

SHOW VARIABLES LIKE '%timeout%'