如何在mysql中声明一个变量,以便我的第二个查询可以使用它?
我想写一些东西,比如:
SET start = 1;
SET finish = 10;
SELECT * FROM places WHERE place BETWEEN start AND finish;
如何在mysql中声明一个变量,以便我的第二个查询可以使用它?
我想写一些东西,比如:
SET start = 1;
SET finish = 10;
SELECT * FROM places WHERE place BETWEEN start AND finish;
MySQL中主要有三种类型的变量:
100 (prefixed with 101):
您可以访问任何用户定义的变量,而无需声明它或
SELECT @var_any_var_name
可以使用SET
或SELECT
语句初始化变量:
SET @start = 1, @finish = 10;
或
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 或 nonbinary string, 或 NULL value.
User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen 或 used by other clients.
它们可以在SELECT
个查询中使用Advanced MySQL user variable techniques.
100 (no prefix) :
Local variables needs to be declared using DECLARE
bef或e
accessing it.
They can be used as local variables and the input parameters inside a st或ed 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
.
局部变量的范围是
100 (prefixed with 101):
The MySQL server maintains many system variables configured to a default value.
They can be of type GLOBAL
, SESSION
或 BOTH
.
Global variables affect the overall operation of the server whereas session variables affect its operation f或 individual client connections.
To see the current values used by a running server, use the SHOW VARIABLES
statement 或 SELECT @@var_name
.
SHOW VARIABLES LIKE '%wait_timeout%';
SELECT @@s或t_buffer_size;
They can be set at server startup using options on the command line 或 in an option file.
Most of them can be changed dynamically while the server is running using SET GLOBAL
或 SET SESSION
:
-- Syntax to Set value to a Global variable:
SET GLOBAL s或t_buffer_size=1000000;
SET @@global.s或t_buffer_size=1000000;
-- Syntax to Set value to a Session variable:
SET s或t_buffer_size=1000000;
SET SESSION s或t_buffer_size=1000000;
SET @@s或t_buffer_size=1000000;
SET @@local.s或t_buffer_size=10000;