如何在MySQL中声明变量? [英] How to declare a variable in MySQL?
问题描述
如何在mysql中声明变量，以便我的第二个查询可以使用它?
How to declare a variable in mysql, so that my second query can use it?
我想写些类似的东西:
SET start = 1;
SET finish = 10;
SELECT * FROM places WHERE place BETWEEN start AND finish;
推荐答案
MySQL中主要有三种类型的变量:
There are mainly three types of variables in MySQL:
-
用户定义的变量(前缀为
@):
User-defined variables (prefixed with
@):
您可以访问任何用户定义的变量而无需声明它，或者
初始化它.如果您引用的变量尚未
初始化后，它的值为NULL和字符串类型.
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.
SELECT @var_any_var_name
您可以使用SET或SELECT语句初始化变量:
You can initialize a variable using SET or SELECT statement:
SET @start = 1, @finish = 10;
或
SELECT @start := 1, @finish := 10;
SELECT * FROM places WHERE place BETWEEN @start AND @finish;
可以从一组有限的数据中为用户变量分配一个值 类型:整数，十进制，浮点数，二进制或非二进制字符串， 或NULL值.
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.
可以使用 Advanced MySQL在SELECT查询中使用它们用户变量技术.
They can be used in SELECT queries using Advanced MySQL user variable techniques.
局部变量(无前缀) ):
Local Variables (no prefix) :
在使用之前，必须使用DECLARE声明局部变量
访问它.
Local variables needs to be declared using DECLARE before
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);
如果缺少DEFAULT子句，则初始值为NULL.
If the DEFAULT clause is missing, the initial value is NULL.
局部变量的范围是其中的BEGIN ... END块
The scope of a local variable is the BEGIN ... END block within
which it is declared.
服务器系统变量(前缀与@@):
Server System Variables (prefixed with @@):
MySQL服务器维护许多配置为默认值的系统变量价值.
它们的类型可以为GLOBAL，SESSION或BOTH.
The MySQL server maintains many system variables configured to a default value.
They can be of type GLOBAL, SESSION or BOTH.
全局变量影响服务器的整体操作，而会话变量影响单个客户端连接的服务器操作.
Global variables affect the overall operation of the server whereas session variables affect its operation for individual client connections.
要查看正在运行的服务器使用的当前值，请使用SHOW VARIABLES语句或SELECT @@var_name.
To see the current values used by a running server, use the SHOW VARIABLES statement or SELECT @@var_name.
SHOW VARIABLES LIKE '%wait_timeout%';
SELECT @@sort_buffer_size;
可以在服务器启动时使用命令行或选项文件中的选项进行设置.
在服务器使用SET GLOBAL或SET SESSION运行时，可以动态更改其中的大多数内容:
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:
-- 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;
这篇关于如何在MySQL中声明变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
