如何在MySQL中声明变量？

本文翻译自：How to declare a variable in 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;

---

#1楼

参考：https://stackoom.com/question/nJxF/如何在MySQL中声明变量

---

#2楼

There are mainly three types of variables in MySQL: MySQL中主要有三种类型的变量：

1. 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用户变量技术在SELECT查询中使用它们。

2. 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 . 如果缺少DEFAULT子句，则初始值为NULL 。

   The scope of a local variable is the BEGIN ... END block within which it is declared. 局部变量的范围是在其内声明的BEGIN ... END块。

3. Server System Variables (prefixed with @@ ): 服务器系统变量 （以@@前缀）：

   The MySQL server maintains many system variables configured to a default value. MySQL服务器维护许多配置为默认值的系统变量 。 They can be of type GLOBAL , SESSION or BOTH . 它们的类型可以是GLOBAL ， SESSION或BOTH 。

   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语句或SELECT @@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; 

---

#3楼

Use set or select 使用设置或选择

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

---

example : 例如：

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

---

#4楼

SET 组

SET @var_name = value 

OR 要么

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中的最后一个值是keep（覆盖）；

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

in this case the result of select is not containing col2 values 在这种情况下，select的结果不包含col2值

---

#5楼

For any person using @variable in concat_ws function to get concatenated values, don't forget to reinitialize it with empty value. 对于在concat_ws函数中使用@variable来获取串联值的任何人，请不要忘记使用空值重新初始化它。 Otherwise it can use old value for same session. 否则，它可以将旧值用于同一会话。

Set @Ids = '';

select 
  @Ids := concat_ws(',',@Ids,tbl.Id),
  tbl.Col1,
  ...
from mytable tbl;

---

#6楼

SET Value 设定值

 declare Regione int;   
 set Regione=(select  id from users
 where id=1) ;
 select Regione ;