Mysql 简明教程

MySQL - Variables

通常，变量是用于在一个程序中存储一些信息的容器。一个变量的值可以根据需要更改多次。每个变量都有一个数据类型，用于指定我们可以存储在变量中的数据类型，如整数、字符串、浮点数等。

In general, variables are the containers that store some information in a program. The value of a variable can be changed as many times as required. Each variable has a datatype specifying the type of data we can store in it such as integer, string, float etc.

In some programming languages such as Java, C, C++ etc., we need to declare the datatype of a variable before assigning values to it.
In languages like python the datatype of a variable is presumed based on the values assigned to it. There is no need of declaring the datatype separately.
In MySQL there is no need to declare the datatype we can simply define a variable with a value using the SET statement.

Variables in MySQL

变量的主要目的是标记一个或多个内存位置并在其中存储数据，以便可以在整个程序中使用它。

The main purpose of a variable is to label a memory location(s) and store data in it so that it can be used throughout the program.

我们用来声明和定义变量的字符称为文字，并且文字可以是除了特殊字符、数字和保留关键字之外的任何内容。

The characters we use to declare and define a variables are called literals and a literal can be anything other than special characters, numbers and, reserved keywords.

在 MySQL 中，有三种类型的变量。如下所示：

In MySQL, there are three types of variables. The same is described below −

User-Defined Variable
Local Variable
System Variable

User-Defined Variables

用户定义变量允许我们在一个语句中存储值，然后在另一个语句中引用它。为此，MySQL 提供了 SET 和 SELECT 命令来声明变量。这些变量名将以符号“@”作为前缀。我们可以根据情况使用 = 或 := 符号。用户定义的数据类型可以是以下任何一种：整数、十进制数、布尔值等。

The User-Defined variable allows us to store a value in one statement and subsequently refer to it in another. To do so, MySQL provides SET and SELECT commands to declare a variable. These variable names will have the symbol "@" as a prefix. We can use either = or := symbols depending on the situation. The user-defined data type can be any of the following: integer, decimal, Boolean, etc.

Syntax

以下是使用 SET 语句在 MySQL 中声明用户定义变量的语法：

Following is the syntax to declare a user-defined variable in MySQL using the SET statement −

SELECT @variable_name = value

Example

在以下查询中，我们将使用 SET 语句将一个值分配给一个变量，如下所示 −

In the following query, we are assigning a value to a variable using the SET statement as follows −

SET @Name = 'Michael';

使用 SELECT 语句，我们可以显示 @name 变量的值 −

Using the SELECT statement, we can display the value of @name variable −

SELECT @Name;

Output

以上查询的输出如下所示：

The output for the query above is produced as given below −

Example

在这里，我们使用 SELECT 语句将一个值分配给一个变量 −

Here, we are assigning a value to a variable using the SELECT statement −

SELECT @test := 10;

Output

执行给定的查询后，输出如下：

On executing the given query, the output is displayed as follows −

Example

让我们使用以下查询创建一个名为 CUSTOMERS 的表 −

Let us create table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

现在，让我们使用 INSERT INTO 语句向以上创建的表中插入值 −

Now, let us insert values into the above-created table using the INSERT INTO statement −

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

CUSTOMERS 表创建如下 −

The CUSTOMERS table is created as follows −

现在，让我们使用 SELECT 语句声明一个名为 @max_salary 的变量，以显示 CUSTOMERS 表中的最大工资值 −

Now, let us declare a variable with the name @max_salary using the SELECT statement to display the maximum salary value from the CUSTOMERS table −

SELECT @max_salary := MAX(salary) FROM CUSTOMERS;

然后，我们将从表中选择工资等于 @max_salary 变量的记录 −

Then, we will select records from the table where the salary is equal to @max_salary variable −

SELECT * FROM CUSTOMERS WHERE SALARY = @max_salary;

Output

以上查询的输出如下所示：

The output for the query above is produced as given below −

Local Variables

可以使用 DECLARE 关键字声明 MySQL 本地变量。在声明本地变量时，不使用 @ 符号作为前缀。此变量是一个强类型变量，这意味着我们肯定需要声明一个数据类型。

The MySQL local variable can be declared using the DECLARE keyword. When we declare the local variable, the @ symbol is not used as prefix. This variable is a strongly typed variable, which means that we definitely need to declare a data type.

在声明变量时，可以使用 MySQL DEFAULT 关键字设置变量的默认值。这是一个可选参数，如果我们不定义该参数，初始值为 NULL 。

The MySQL DEFAULT keyword can be used while declaring a variable to set the default value of the variable. This is an optional parameter, if we do not define this, the initial value will be NULL.

Syntax

以下是 MySQL 中声明局部变量的语法 −

Following is the syntax to declare a local variable in MySQL −

DECLARE variable_name1, variabale_name2, ...

data_type [DEFAULT default_value];

Example

在以下示例中，我们在存储过程中使用 DECLARE 语句。

In the following example, we are using the DECLARE statement in a stored procedure.

DELIMITER //
CREATE PROCEDURE salaries()
BEGIN
   DECLARE Ramesh INT;
   DECLARE Khilan INT DEFAULT 30000;
   DECLARE Kaushik INT;
   DECLARE Chaitali INT;
   DECLARE Total INT;
   SET Ramesh = 20000;
   SET Kaushik = 25000;
   SET Chaitali = 29000;
   SET Total = Ramesh+Khilan+Kaushik+Chaitali;
   SELECT Total,Ramesh,Khilan,Kaushik,Chaitali;
END //

现在，让我们使用以下查询调用存储过程 −

Now, let us call the stored procedure using the following query −

CALL salaries() //;

Output

输出如下：

Following is the output −

System Variables

system variables 由 MySQL 预定义。这些包含我们需要的数据，以便使用数据库。每个 MySQL 系统变量都有一个默认值。

The system variables are predefined by the MySQL. These contains the data we need, to work with the database. Each MySQL system variable has a default value.

MySQL 中的 SET 命令可以在运行时动态更改系统变量的值。

The SET command in MySQL can be used at the runtime to dynamically change the values of the system variables.

SHOW VARIABLES 命令有两种变量作用域修饰符。它们是 GLOBAL 和 SESSION。

There are two variable scope modifiers available for the SHOW VARIABLES command. They are GLOBAL and SESSION.

The GLOBAL variables are active throughout the lifecycle.
The SESSION variables can be available only in the current session.

以下是显示 MySQL 中所有系统变量的命令 −

Following is the command to display all the system variables in MySQL −

SHOW [GLOBAL | SESSION] VARIABLES;

Example

在以下示例中，让我们使用 SHOW VARIABLES 请求显示现有的全局系统变量 −

In the following example, let us display the existing global system variables using the SHOW VARIABLES query −

SHOW VARIABLES LIKE '%table%';

变量以以下表格格式显示 −

The variables are displayed in the table format as follows −

现在，使用以下请求，我们将获取 MySQL "key_buffer_size" 的当前值 −

Now, using the query below, we will fetch the current value of the MySQL "key_buffer_size" variable −

SELECT @@key_buffer_size;

Output

以下是以上查询的输出：

Following is the output of the above query −