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

Question

提问by cdub

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;

Answer 1

回答by Omesh

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 NULLand a type of string.
```
SELECT @var_any_var_name
```
You can initialize a variable using SETor SELECTstatement:
```
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.
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 SELECTqueries using Advanced MySQL user variable techniques.
Local Variables(no prefix) :
Local variables needs to be declared using DECLAREbefore accessing it.
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 DEFAULTclause is missing, the initial value is NULL.
The scope of a local variable is the BEGIN ... ENDblock within which it is declared.
Server System Variables(prefixed with @@):
The MySQL server maintains many system variablesconfigured to a default value. They can be of type GLOBAL, SESSIONor 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 VARIABLESstatement or 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 GLOBALor 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;
```

用户定义的变量（以为前缀@）：
您可以访问任何用户定义的变量而无需声明或初始化它。如果您引用一个尚未初始化的变量，则它的值为NULL和类型为字符串。
```
SELECT @var_any_var_name
```
您可以使用SETorSELECT语句初始化变量：
```
SET @start = 1, @finish = 10;    
```
或者
```
SELECT @start := 1, @finish := 10;

SELECT * FROM places WHERE place BETWEEN @start AND @finish;
```
可以从一组有限的数据类型中为用户变量分配一个值：整数、十进制、浮点、二进制或非二进制字符串，或 NULL 值。
用户定义的变量是特定于会话的。也就是说，一个客户端定义的用户变量不能被其他客户端看到或使用。
它们可以在SELECT使用高级 MySQL 用户变量技术的查询中使用。

局部变量（无前缀）：

局部变量需要DECLARE在访问之前声明 using 。

它们可以用作局部变量和存储过程中的输入参数：

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);

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

局部变量的BEGIN ... END范围是声明它的块。

服务器系统变量（以为前缀@@）：
MySQL 服务器维护了许多配置为默认值的系统变量。它们的类型可以是GLOBAL,SESSION或BOTH。
全局变量影响服务器的整体操作，而会话变量影响其对单个客户端连接的操作。
要查看正在运行的服务器使用的当前值，请使用SHOW VARIABLES语句或SELECT @@var_name。
```
SHOW VARIABLES LIKE '%wait_timeout%';

SELECT @@sort_buffer_size;
```
它们可以在服务器启动时使用命令行或选项文件中的选项进行设置。其中大多数可以在服务器运行时动态更改，使用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;
```

Answer 2

回答by bortunac

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

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

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

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

Ex both methods used

Ex 两种方法都使用

-- TRIGGER_BEFORE_INSERT --- setting a column value from calculations

-- TRIGGER_BEFORE_INSERT --- 从计算中设置列值

...
SELECT count(*) INTO @NR FROM a_table WHERE a_condition;
SET NEW.ord_col =  IFNULL( @NR, 0 ) + 1;
...

Answer 3

回答by Mohammad Mahdi KouchakYazdi

Use setor select

使用设置或选择

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

example :

例子：

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

Answer 4

回答by mohammadAli

Different types of variable:

不同类型的变量：

local variables(which are not prefixed by @) are strongly typed and scoped to the stored program block in which they are declared. Note that, as documented under DECLARE Syntax:

局部变量（没有@ 前缀）是强类型的，并且作用域限定在声明它们的存储程序块中。请注意，如DECLARE Syntax 中所述：

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

DECLARE 只允许在 BEGIN ... END 复合语句中使用，并且必须在其开头，在任何其他语句之前。

User variables(which are prefixed by @) are loosely typed and scoped to the session. Note that they neither need nor can be declared—just use them directly.

用户变量（以@ 为前缀）是松散类型的并且作用域为会话。请注意，它们既不需要也不能声明——只需直接使用它们即可。

Therefore, if you are defining a stored program and actually do want a "local variable", you will need to drop the @ character and ensure that your DECLARE statement is at the start of your program block. Otherwise, to use a "user variable", drop the DECLARE statement.

因此，如果您正在定义一个存储程序并且确实需要一个“局部变量”，您将需要删除 @ 字符并确保您的 DECLARE 语句位于程序块的开头。否则，要使用“用户变量”，请删除 DECLARE 语句。

Furthermore, you will either need to surround your query in parentheses in order to execute it as a subquery:

此外，您需要将查询括在括号中，以便将其作为子查询执行：

SET @countTotal = (SELECT COUNT(*) FROM nGrams);

Or else, you could use SELECT ... INTO:

否则，您可以使用 SELECT ... INTO：

SELECT COUNT(*) INTO @countTotal FROM nGrams;

Answer 5

回答by Imran Javed

For any person using @variable in concat_ws function to get concatenated values, don't forget to reinitialize it with empty value. Otherwise it can use old value for same session.

对于在 concat_ws 函数中使用 @variable 来获取连接值的任何人，不要忘记用空值重新初始化它。否则它可以为同一会话使用旧值。

Set @Ids = '';

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

Answer 6

回答by yaya

Declare: SET @a = 1;
Usage: INSERT INTO `t` (`c`) VALUES (@a);

宣布： SET @a = 1;
用法： INSERT INTO `t` (`c`) VALUES (@a);

Answer 7

回答by Hari Lakkakula

SET Value

设定值

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

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

提问by cdub

回答by Omesh

回答by bortunac

回答by Mohammad Mahdi KouchakYazdi

回答by mohammadAli

回答by Imran Javed

回答by yaya

回答by Hari Lakkakula

SET Value

设定值

相关推荐

最近更新

标签

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

提问by cdub

回答by Omesh

回答by bortunac

回答by Mohammad Mahdi KouchakYazdi

回答by mohammadAli

回答by Imran Javed

回答by yaya

回答by Hari Lakkakula

SET Value

设定值

相关推荐

mysql 触发器存储触发器已被调用存储触发器的语句使用

“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册以获取正确的语法错误”Hibernate 4

复制 MySQL 数据库的最简单方法？

MySQL BIGINT UNSIGNED VALUE 超出范围我的 SQL

相关推荐

最近更新

标签