問題描述
如何在 mysql 中聲明一個變量，以便我的第二個查詢可以使用它?
How to declare a variable in mysql, so that my second query can use it?
我想寫一些類似的東西:
I would like to write something like:
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.
它們可以在使用 SELECT 查詢中使用/">高級 MySQL 用戶變量技術.
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 中聲明一個變量?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！