設定變數

Created: November-22, 2018

以下是設定變數的一些方法：

你可以使用 SET 將變數設定為特定的字串，數字，日期

EX：SET @var_string =‘my_var’; SET @var_num =‘2’SET @var_date =‘2015-07-20’;
你可以使用：=將變數設定為 select 語句的結果

EX：選擇 @var：=‘123’; （注意：你需要使用：=在分配不使用 SET 語法的變數時，因為在其他語句中，（select，update …）使用“=”進行比較，所以在“…之前”新增冒號時=“，你說的是”這不是比較，這是一個 SET“。）
你可以使用 INTO 將變數設定為 select 語句的結果

（當我需要動態選擇要查詢的分割槽時，這特別有用）

EX：SET @start_date =‘2015-07-20’; SET @end_date =‘2016-01-31’;

#this gets the year month value to use as the partition names
SET @start_yearmonth = (SELECT EXTRACT(YEAR_MONTH FROM @start_date));
SET @end_yearmonth = (SELECT EXTRACT(YEAR_MONTH FROM @end_date));

#put the partitions into a variable
SELECT GROUP_CONCAT(partition_name)
FROM information_schema.partitions p 
WHERE table_name = 'partitioned_table'
AND SUBSTRING_INDEX(partition_name,'P',-1) BETWEEN @start_yearmonth AND @end_yearmonth
INTO @partitions;

#put the query in a variable. You need to do this, because mysql did not recognize my variable as a variable in that position. You need to concat the value of the variable together with the rest of the query and then execute it as a stmt.
SET @query =
CONCAT('CREATE TABLE part_of_partitioned_table (PRIMARY KEY(id))
SELECT partitioned_table.*
FROM partitioned_table PARTITION(', @partitions,')
JOIN users u USING(user_id)
WHERE date(partitioned_table.date) BETWEEN ', @start_date,' AND ', @end_date);

#prepare the statement from @query
PREPARE stmt FROM @query;
#drop table
DROP TABLE IF EXISTS tech.part_of_partitioned_table;
#create table using statement
EXECUTE stmt;