设置变量

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;