Differences between the value assignment of SQL server and mysql variables and MySql Declare

Document directory

• Lifecycle 2.8. DECLARE statement

Both SQL server and mysql are our frequently used database systems. Next we will introduce the differences between the values of SQL server and mysql variables, and hope to enlighten you.

Variables in SQL server must be affirmed first and then assigned values:

The local variables are identified with one @ and the global variables with two @ (common global variables are usually defined );

Declare the local variable Syntax: declare @ variable name data type; for example, declare @ num int;

Value assignment: There are two methods (@ num is the variable name and value is the value)

Set @ num = value; or select @ num = value;

If you want to obtain a field value in the query statement, you can use select to assign values to the variable, as shown below:

Select @ num = field name from table name where ......

You do not need to declare the value assignment of mysql variables beforehand. You can simply use "@ variable name" when using it.

First usage: set @ num = 1; or set @ num: = 1; // use the variable to save the data. Use the @ num variable directly.

Second usage: select @ num: = 1; or select @ num: = field name from table name where ...... (Zhang Ying: It seems that mysql cannot use the select @ p1: = c1, @ p2: = c2 from tb method to assign values)

Note that the above two values can be set with "=" or ": =", but ": =" must be used for select"

Mysql can declare local variables without @ in SP. For details, see declare.

 

Declare

 

Lifecycle 2.8. DECLARE statement

The DECLARE statement is used to place different projects to a subroutine: local variables (see section 2.9, "variables in storage programs"), conditions, and processing programs (see section 2.10, "condition and processing program") and the cursor (see section 2.11, "cursor "). The SIGNAL and RESIGNAL statements are currently not supported.

DECLARE is only used in the in... END compound statement and must BEGIN with the compound statement before any other statement.

The cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared.

Secrets 2.9. variables in the storage Program

20172.9.1. DECLARE local variables

Limit 2.9.2. Variable SET statement

Limit 2.9.3. SELECT... INTO statement

You can declare and use variables in subprograms.

20172.9.1. DECLARE local variables

DECLARE var_name[,...] type [DEFAULT value]

This statement is used to declare local variables. To provide a DEFAULT value for a variable, include a DEFAULT clause. The value can be specified as an expression and does not need to be a constant. If no DEFAULT clause exists, the initial value is NULL.

The scope of a local variable is within the declared BEGIN... END block. It can be used in nested blocks, except those that declare variables with the same name.

Limit 2.9.2. Variable SET statement

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

The SET statement in the storage Program is an extended version of the general SET statement. The referenced variable may be a variable declared in the subroutine or a global server variable.

The SET statement in the stored program is implemented as part of the pre-existing SET syntax. This allows the SET a = x, B = y,... extension syntax. Different variable types (local declaration variables and global and collective variables) can be mixed. This also allows you to combine local variables with some options that only make sense to system variables. In that case, this option is identified but ignored.

Limit 2.9.3. SELECT... INTO statement

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

This SELECT syntax stores the selected columns directly to variables. Therefore, only a single row can be retrieved.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

Note that user variable names are case-insensitive in MySQL 5.1. See section 9.3 "User variables ".

Important: The SQL variable name cannot be the same as the column name. If an SQL statement such as SELECT... INTO contains a reference to a column and a local variable with the same name as the column, MySQL interprets the reference as the name of a variable. For example, in the following statement, xname is interpreted as xname.VariableInstead of xnameColumnOf:

CREATE PROCEDURE sp1 (x VARCHAR(5))

 BEGIN

    DECLARE xname VARCHAR(5) DEFAULT 'bob';

    DECLARE newname VARCHAR(5);

    DECLARE xid INT;

    SELECT xname,id INTO newname,xid 

      FROM table1 WHERE xname = xname;

    SELECT newname;

 END;

When this program is called, regardless of the value of the table. xname column, the newname variable returns 'bob '.