MySQL variable definition and assignment, mysql variable assignment

MySQL variable definition and assignment, mysql variable assignment

Variables can be declared and used in subprograms (functions, stored procedures, anonymous blocks). These variables are used in the BEGIN... END program.

Syntax format for defining variables:

DECLARE var_name [, var_name]... data_type [ DEFAULT value ];

Var_name is the name of a local variable;

The DEFAULT value clause specifies the DEFAULT value of a variable. In addition to being declared as a constant, value can also be specified as an expression. If no DEFAULT clause is available, the initial value of the variable is NULL.

Example:

DECLARE myparam INT DEFAULT 100;

Variable assignment

After a variable is defined, assigning a value to the variable can change the default value of the variable. MySQL uses the SET statement to assign values to variables.

The SET statement in the storage Program is an extended version of the general SET statement. The referenced variables may be variables declared in the subroutine or global server variables, such as system variables or user variables.

Syntax 1:

SET var_name=expr [, var_name=expr]...;

Example 1:

Declare three variables: var1, var2, and var3. SET the data type to INT and assign values to the variables.

DECLARE var1, var2, var3 INT;SET var1=10, var2=20;SET var3=var1+var2;

Syntax 2: select into assign values to variables

The select into syntax stores the selected columns directly to the variables at the corresponding position.

MySQL can also assign values to one or more variables using SELECT... INTO. The syntax is as follows:

SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];

Example 2:

Declare the variables v_employee_name and v_employee_salary. query the specified records using the SELECT... INTO statement and assign values to the variables:

DECLARE v_employee_name VARCHAR(100);DECLARE v_employee_salary DECIMAL(8,4);SELECT employee_name, employee_salaryINTO v_employee_name, v_employee_salaryFROM employeesWHERE employee_id=1;

Bytes ------------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!

Assign values to variables in MYSQL triggers

SELECT @ uc_id = user_head FROM et_users WHERE user_id = NEW. user_id;
 
A simple question: how can I define variables in mysql and assign values to them immediately?

BEGIN
Declare sqlStr_total varchar (500 );

Set sqlStr_total = 'select aaa, bbb from ';
If condition then
Set sqlStr_total = concat (sqlStr_total, '××× ...... ',' ××× ...... ');
End if;
#### Omit other processing #####