Variables in the Mariadb/mysql

There are several types of variables in MYSQL/MARIADB: User-defined variables, system variables, general temporary variables (local variables, or local variables).

1. User Variables

User variables are session-based and user-based, so I think it's more appropriate to call it a session variable, but the session variable is typically used to represent a system session variable (which is explained later), so it's called a user variable.

Only this user can reference its own user variable, which cannot be referenced by other users, and the user variable is automatically destroyed when the user exits the session.

User variables begin with "@" and user variables can be assigned directly without prior declaration. When you reference a user variable that is not assigned, the value of the variable is null.

There are three ways to set the user variable:

1. Set statement, you can use the "=" or ": =" operator at this time;
2. Select statement, you can only assign a value using the ": =" format, because "=" is treated as a comparison operator in addition to the SET statement.;
3. Select ... into var_name the FROM table statement, at which point the SELECT statement is required to return only a scalar value, that is, a single row of data. Therefore, in order to insure, select into var_name should try to combine limit statement to restrict output.

set@a1=1,@a2=3,@a3:=2;select@a4:=@a1+@a3;select33,‘abc‘@a5,@a6 from dual;

View variable values you can use the SELECT statement.

MariaDB [test]>Select @a1,@a2,@a3,@a4,@a5,@a6,@a7;+------+------+------+------+------+------+------+|@a1|@a2|@a3|@a4|@a5|@a6|@a7|+------+------+------+------+------+------+------+|1|2|3|4|5| ABC | NULL |+------+------+------+------+------+------+------+

In Mariadb 10.2.6, a System schema table is introduced that information_schema.USER_VARIABLES records the user variable information for the current user's current session definition. This information schema table is not in MySQL.

  MariaDB [test]> SELECT * from INFORMATION_SCHEMA.           User_variables; +---------------+----------------+---------------+--------------------+  | variable_name | Variable_value | Variable_type | Character_set_name | +---------------+----------------+---------------+--------------------+  | A6 | ABC | VARCHAR | UTF8 | | I | 2 | INT | UTF8 | | A5 | 33 | INT | UTF8 | | A1 | 1 | INT | UTF8 | | A4 | 3 | INT | UTF8 | | A2 | 3 | INT | UTF8 | | A3 | 2 | INT | UTF8 | +---------------+----------------+---------------+--------------------+   

2. System Variables

Maintenance of two system variables in MYSQL/MARIADB: Global System Variables and session system variables. System variables are used to set the running properties and state of the MySQL service.

Global System variables Use global or "@ @global." Keyword to set. Session System variables use session or "@ @session." Keyword to set, where sessions can be replaced with local, which are synonyms. If you omit these keywords, the default is the session system variable. Setting the global system variable requires super privilege.

--Set global system variables Set global sort_buffer_size=+ M;  Set @@ global sort_buffer_size=-M;--Set session system variables Set session sort_buffer_size=+ M;  Set @@ session. sort_buffer_size=-M; set sort_buffer_size=+ M;--View Global system variable values  Select @@ Global sort_buffer_size; Show global variables  like "sort_buffer%";--View Session system variables, cannot use select Sort_buffer_size  Select @@ session. sort_buffer_size; Select @ @sort_buffer_size; Show [session] variables  like "sort_buffer%";--Set multiple variables at once, including session variables, global variables, and user variables SET @x = 1, SESSION sql_mode = '; SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000< /c5>;  SET @@ Global sort_buffer_size = 1000000, @@ Global. Sort_buffer_size =  1000000; SET GLOBAL max_connections = sort_buffer_size = 1000000;

Global System variables are globally valid, and new sessions inherit the values of global system variables when a new session is opened, so the newly opened session inherits the set value after setting the global system variable. Setting a global system variable is not valid for a connection that is already open, but other open connections can see the value of the set global system variable.

System variables are also classified as dynamic variables and static variables , depending on whether they are allowed to be modified at run time. Variables that can be modified during operation are called dynamic variables, and variables that can be modified only when the DB instance is closed are called static variables or read-only variables. Dynamic variables are modified using set. If you modify a static variable while the DB instance is running, an error is given. Such as:

set @@innodb_undo_tablespaces=3;ERROR1238‘innodb_undo_tablespaces‘read only variable

System variables can be set in a running environment, in a configuration file, or in a command line such as Mysqld/mysqld_safe, or even the MySQL client command line can be passed. When you set system variables in a configuration file, underscores or dashes allow them to represent the same meaning. For example, the following two lines of configuration are equivalent:

innodb_file_per_table=1innodb-file-per-table=1

3. Local Variables

Local variables, also known as local variables, can only take effect in the Begin...and statement block. Unlike user variables, local variables must be declared in advance using declare, so declare must also be used in Begin...end .

Local variables, whether declared or called, do not require any extra symbols (that is, do not require the @ symbol), and use their name var_name directly.

Using declare to declare variables, you can declare multiple variables of the same type at once, you can specify a default value directly when needed, or null by default when not specified.

decalre var_name,... type [defaultvalue];

Use set to assign a value to a variable. The set in MYSQL/MARIADB supports assigning multiple variables at once.

The set in Begin...end is an extended version of the general Set statement, which can set either system variables, user variables, or local variables here.

set var_name=expr,[var_name=expr1,...]

Or you can use the Select...into statement to get a value from a table to assign a value to a variable, but such assignment behavior requires that the table's return result must be a single-column, single-row scalar result. For example, the following statement assigns the column value of col to the Var_name variable.

select col into var_name from table_name;

Because local variables can only be used in Begin...end, this is illustrated here with examples of stored procedures.

 DROP PROCEDURE IF EXISTS haha;DELIMITER $$ CREATE PROCEDURE haha ()BEGINDECLARE a INT;         SET a=1;     SET @i:=2;     SELECT a,@i; END$ $DELIMITER;Call   haha ();A @i------  --------1 2

In MySQL, begin...end can only be defined in a stored program, so declare can only be defined within a stored program. In mariadb, however, Begin...end is allowed to be defined outside of stored programs (stored functions, stored procedures, triggers, events), so decalre can be defined outside the storage program. Use the begin not atomic keyword when you need to define outside the stored program. For example:

begin not atomic    declare a int;    set a=3;    select a;end$$

3.1 Declare anchoring data types for other objects

In mariadb 10.3 (note the version number, currently the 10.3 version is still in beta), the Declare statement allows the data type to be anchored in the stored program using the TYPE OF and ROW TYPE OF keywords based on the table or cursor. The data type anchoring function is not supported in MySQL.

For example:

DECLARE tmp TYPE OF t1.a;-- 基于表t1中的a列获取数据类型DECLARE rec1 ROW TYPE OF t1;-- 锚定表t1中行数据类型DECLARE rec2 ROW TYPE OF cur1;-- 基于游标cur1获取行数据类型

When the data type of a local variable is anchored by another object, the local data type changes if the object's data type is changed. This is very helpful at some point in maintaining stored procedures.

When you define a stored program, the declare anchor object is not checked for existence. However, when the stored program is called, the Anchor object is checked for existence.

When the Declare statement is anchored based on a Table object (not a cursor), the instant the stored program is called checks whether the anchored table exists and immediately declares the variable. So:

• (1). The DECALRE statement with anchoring function can be defined anywhere in the stored program;
• (2). Deleting the anchored table object in the stored program, or modifying the anchored table structure, does not alter the type of variables declared when the stored program is called;
• (3). All declare with anchoring functions are assigned at the beginning of the stored procedure call.

When the anchor of a declare statement is based on a cursor object, the data type of the variable is obtained when the variable declaration statement is executed. The data type is only anchored once, and then no longer changes. If a variable in ROW TYPE OF a cursor is defined in a loop, the data type is acquired at the beginning of the loop, and the subsequent loop no longer changes.

Example:

 Create table T1 (a int, b Char()); Drop procedure if exists haha;Delimiter $$ Create procedure haha ()begindeclare x type  of t1.a;         Set x=1;     Select x; end$ $delimiter;Call   haha ();

Back to Linux series article outline: http://www.cnblogs.com/f-ck-need-u/p/7048359.html
Back to Site Architecture series article outline: http://www.cnblogs.com/f-ck-need-u/p/7576137.html
Back to Database series article outline: http://www.cnblogs.com/f-ck-need-u/p/7586194.html
Reprint Please specify source: http://www.cnblogs.com/f-ck-need-u/p/8695767.html

Note: If you think this article is not bad please click on the lower right corner of the recommendation, your support can inspire the author more enthusiasm for writing, thank you very much!

Variables in the Mariadb/mysql