如何在MySQL中声明一个变量?

How to declare a variable in mysql, so that my second query can use it?

如何在mysql中声明一个变量，以便第二个查询可以使用它?

I would like to write something like:

我想这样写:

SET start = 1;
SET finish = 10;

SELECT * FROM places WHERE place BETWEEN start AND finish;

4 个解决方案

#1

384

There are mainly three types of variables in MySQL:

MySQL中主要有三种变量:

User-defined variables (prefixed with @):

用户定义变量(以@为前缀):

You can access any user-defined variable without declaring it or initializing it. If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

您可以不声明或初始化任何用户定义的变量。如果引用一个未初始化的变量，它的值为NULL，并且是一种字符串类型。
```
SELECT @var_any_var_name
```
You can initialize a variable using SET or SELECT statement:

您可以使用SET或SELECT语句初始化一个变量:
```
SET @start = 1, @finish = 10;    
```
or

或
```
SELECT @start := 1, @finish := 10;

SELECT * FROM places WHERE place BETWEEN @start AND @finish;
```
User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

用户变量可以从一组有限的数据类型中分配值:整数、十进制、浮点数、二进制或非二进制字符串或NULL值。

User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients.

那些用户定义的变量。也就是说，一个客户机定义的用户变量不能被其他客户机看到或使用。

They can be used in SELECT queries using Advanced MySQL user variable techniques.

可以使用高级MySQL用户变量技术来选择查询。
Local Variables (no prefix) :

局部变量(无前缀):

Local variables needs to be declared using DECLARE before accessing it.

在访问局部变量之前，需要使用DECLARE声明它。

They can be used as local variables and the input parameters inside a stored procedure:

它们可以作为本地变量和存储过程中的输入参数:
```
DELIMITER //

CREATE PROCEDURE sp_test(var1 INT) 
BEGIN   
    DECLARE start  INT unsigned DEFAULT 1;  
    DECLARE finish INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

    SELECT * FROM places WHERE place BETWEEN start AND finish; 
END; //

DELIMITER ;

CALL sp_test(5);
```
If the DEFAULT clause is missing, the initial value is NULL.

如果缺少缺省子句，则初始值为NULL。

The scope of a local variable is the BEGIN ... END block within which it is declared.

局部变量的范围是开始……声明它的结束块。
Server System Variables (prefixed with @@):

服务器系统变量(前缀@@):

The MySQL server maintains many system variables configured to a default value. They can be of type GLOBAL, SESSION or BOTH.

MySQL服务器维护许多配置为默认值的系统变量。它们可以是全局类型、会话类型或两者都有。

Global variables affect the overall operation of the server whereas session variables affect its operation for individual client connections.

全局变量影响服务器的整体操作，而会话变量影响单个客户端连接的操作。

To see the current values used by a running server, use the SHOW VARIABLES statement or SELECT @@var_name.

要查看正在运行的服务器使用的当前值，请使用SHOW VARIABLES语句或选择@@var_name。
```
SHOW VARIABLES LIKE '%wait_timeout%';

SELECT @@sort_buffer_size;
```
They can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running using SET GLOBAL or SET SESSION:

可以使用命令行或选项文件中的选项在服务器启动时设置它们。当服务器使用SET GLOBAL或SET SESSION运行时，它们中的大多数都可以动态更改:
```
-- Syntax to Set value to a Global variable:
SET GLOBAL sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000;

-- Syntax to Set value to a Session variable:
SET sort_buffer_size=1000000;
SET SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@local.sort_buffer_size=10000;
```

#2

SET

集

SET @var_name = value

或

SET @var := value

both operators = and := are accepted

运算符=和:=都被接受

SELECT

选择

SELECT col1, @var_name := col2 from tb_name WHERE "conditon";

if multiple record sets found only the last value in col2 is keep (override);

如果多个记录集只发现col2中的最后一个值，则保持(override);

SELECT col1, col2 INTO @var_name, col3 FROM .....

in this case the result of select is not containing col2 values

在这种情况下，select的结果不包含col2值

#3

Use set or select

使用设置或选择

SET @counter := 100;
SELECT @variable_name := value;

example :

例子:

SELECT @price := MAX(product.price)
FROM product

#4

DECLARE var_name [, var_name] ... type [DEFAULT value] This statement declares local variables within stored programs.

声明var_name [， var_name]…类型[默认值]此语句声明存储程序中的本地变量。

Ex. DECLARE id INT unsigned DEFAULT 1;

Ex.声明id INT unsigned DEFAULT 1;

To provide a default value for a variable, include a DEFAULT clause. The value can be specified as an expression; it need not be a constant. If the DEFAULT clause is missing, the initial value is NULL.

要为变量提供默认值，请包含默认子句。可以将值指定为表达式;它不一定是常数。如果缺少缺省子句，则初始值为NULL。

Local variables are treated like stored routine parameters with respect to data type and overflow checking.

对于数据类型和溢出检查，本地变量被视为存储的例程参数。

Variable declarations must appear before cursor or handler declarations.

变量声明必须出现在游标或处理程序声明之前。

Local variable names are not case sensitive. Permissible characters and quoting rules are the same as for other identifiers

局部变量名不区分大小写。允许字符和引用规则与其他标识符相同

The scope of a local variable is the BEGIN ... END block within which it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name.

局部变量的范围是开始……声明它的结束块。变量可以在嵌套在声明块中的块中引用，但声明同名变量的块除外。

#1

384