Is it possible to use some kind of global constant in MySQL?-CodePudding

I am pretty new to this world so apologise if my question does not make sense. I started using rather large queries with several sub-quesries, and sometimes i need to run the same query several times while only changing the date range in all of the queries and sub-queries.

For example:

SELECT * FROM table1
WHERE last_visit BETWEEN '2022-02-01' AND '2022-03-01'
AND id IN (
SELECT id FROM users
WHERE created_date BETWEEN '2022-02-01' AND '2022-03-01'
)
AND id NOT IN (
SELECT id FROM users
WHERE deleted_date BETWEEN '2022-02-01' AND '2022-03-01'
)

I know this query might not make sense but is there a way to set a global constant and use it within all the subqueries? something like:

const_start_date = '2022-02-01'
const_end_date = '2022-03-01'

SELECT * FROM table1
WHERE last_visit BETWEEN const_start_date AND const_end_date
AND id IN (
SELECT id FROM users
WHERE created_date BETWEEN const_start_date AND const_end_date
)
AND id NOT IN (
SELECT id FROM users
WHERE deleted_date BETWEEN const_start_date AND const_end_date)

Thanks!!

CodePudding user response：

You can set a user variable for your current session. But it's not globally available to all sessions. They are dropped when the session is over.

set @const_start_date = '2022-02-01';
set @const_end_date = '2022-03-01';
SELECT id FROM users
WHERE created_date BETWEEN @const_start_date AND @const_end_date ;

If you want them to be persistent across all sessions, consider using a (one-row) table to store them.

create table global_variables (const_start_date date,const_end_date date);
insert into global_variables values ('2022-02-01','2022-03-01');
SELECT id FROM users
WHERE created_date BETWEEN select const_start_date from global_variables 
AND select const_end_date from global_variables ;

Update the table when the values are to be changed. Your SELECT statement doesn't need to change .