在“只读" MySQL 数据库中声明一个变量 [英] Declare a variable in a 'read only' MySQL database

问题描述

我想声明一个变量，以在将日期信息输入到WHERE"子句中时消除重复输入.但是，数据库是只读的，不允许我声明任何内容.这是我尝试过的:

I would like to declare a variable to eliminate double entry when entering date information into a 'WHERE' clause. However, the database is read-only and isn't letting me declare anything. This is what I have tried:

SET @begin_date = '2017-12-01'
SET @end_date = '2017-12-30'
WHERE (ship.dateShipped BETWEEN @begin_date AND @end_date) 
OR (fulfill.datefulfilled BETWEEN @begin_date AND @end_date)

为了这篇文章，查询被简化了，但基本上是我想要做的.我必须将其交给销售人员才能根据需要运行，并希望尽可能轻松地更改日期参数.

The query is simplified for the sake of this post, but is basically what I am trying to do. I have to hand this off to a salesman to run as needed, and want to make it as easy as possible to change the date parameters.

有没有办法在这种情况下声明变量?或者也许是编写WHERE"子句的另一种方法，这样日期只需输入一次?

Is there a way to declare variables in this scenario? Or maybe an alternate way to write the 'WHERE' clause so the dates only have to be entered once?

我也试过:

SELECT @begin_date := '2017-12-01', @end_date := '2017-12-30'......

还有

DECLARE begin_date  INT unsigned DEFAULT '2017-12-01'
DECLARE end_date  INT unsigned DEFAULT '2017-12-30'

无济于事.

推荐答案

您的查询几乎被简化到了胡言乱语的地步，但这样的事情应该可行.

Your query is simplified almost to the point of gibberish, but something like this should work.

SELECT ...
FROM ....
INNER JOIN (SELECT '2017-12-01' AS beginDate, '2017-12-30' AS endDate) AS dr
ON (ship.dateShipped BETWEEN dr.beginDate AND dr.endDate) 
OR (fulfill.datefulfilled BETWEEN dr.beginDate AND dr.endDate)
...

真的不会让你用@变量吗?

It really will not let you use @ variables?

这篇关于在“只读" MySQL 数据库中声明一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！