我可以在MySQL中使用参数创建视图吗? [英] Can I create view with parameter in MySQL?

问题描述

我有这样的看法:

CREATE VIEW MyView AS
   SELECT Column FROM Table WHERE Value = 2;

我想使其更通用，这意味着将2更改为变量.我试过了:

I'd like to make it more generic, it means to change 2 into a variable. I tried this:

CREATE VIEW MyView AS
   SELECT Column FROM Table WHERE Value = @MyVariable;

但是MySQL不允许这样做.

But MySQL doesn't allow this.

我发现了一个丑陋的解决方法:

I found an ugly workaround:

CREATE FUNCTION GetMyVariable() RETURNS INTEGER DETERMINISTIC NO SQL
BEGIN RETURN @MyVariable; END|

然后视图是:

CREATE VIEW MyView AS
   SELECT Column FROM Table WHERE Value = GetMyVariable();

但是它看起来确实很糟糕，用法也很糟糕-我必须在每次使用视图之前设置@MyVariable.

But it looks really crappy, and the usage is also crappy - I have to set the @MyVariable before each usage of the view.

有没有可以像这样使用的解决方案:

Is there a solution, that I could use like this:

SELECT Column FROM MyView(2) WHERE (...)

具体情况如下: 我有一个表，用于存储有关被拒绝的请求的信息:

The concrete situation is as follows: I have a table storing information about the denied request:

CREATE TABLE Denial
(
    Id INTEGER UNSIGNED AUTO_INCREMENT,
        PRIMARY KEY(Id),
    DateTime DATETIME NOT NULL,
    FeatureId MEDIUMINT UNSIGNED NOT NULL,
        FOREIGN KEY (FeatureId)
            REFERENCES Feature (Id)
            ON UPDATE CASCADE ON DELETE RESTRICT,
    UserHostId MEDIUMINT UNSIGNED NOT NULL,
        FOREIGN KEY (UserHostId)
            REFERENCES UserHost (Id)
            ON UPDATE CASCADE ON DELETE RESTRICT,
    Multiplicity MEDIUMINT UNSIGNED NOT NULL DEFAULT 1,
    UNIQUE INDEX DenialIndex (FeatureId, DateTime, UserHostId)
) ENGINE = InnoDB;

多重性是在同一秒内记录的多个相同请求.我想显示一个拒绝列表，但是有时候，当应用程序被拒绝时，它会重试几次以确保.因此，通常，当同一用户在几秒钟内对同一功能拒绝3次时，实际上是一次拒绝.如果我们还有一个资源可以满足此请求，则不会发生接下来的两次拒绝.因此，我们希望将拒绝项分组到报告中，以便用户指定应将拒绝项分组的时间跨度.例如.如果我们在时间戳中有拒绝(针对功能1上的用户1):1,2,24,26,27,45，并且用户希望将彼此之间相距近于4秒的拒绝分组，则他应该得到以下信息:1 (x2)，24(x3)，45(x1).我们可以假设，实际拒绝之间的间隔比重复之间的间隔大得多.我通过以下方式解决了这个问题:

A multiplicity is a number of identical requests recorded in the same second. I want to display a list of denials, but sometimes, when the application gets denied, it retries a couple times just to make sure. So usually, when the same user gets denial 3 times on the same feature in a couple seconds it is actually one denial. If we'd have one more resource, to fulfill this request, the next two denials would not happen. So we want to group the denials in report allowing the user to specify the timespan in which denials should be grouped. E.g. if we have denials (for user 1 on feature 1) in timestamps: 1,2,24,26,27,45 and user wants to group denials that are closer to each other than 4 sec, he should get something like this: 1 (x2), 24 (x3), 45 (x1). We can assume, that spaces between real denials are much bigger than between duplications. I solved the problem in the following way:

CREATE FUNCTION GetDenialMergingTime()
    RETURNS INTEGER UNSIGNED
    DETERMINISTIC NO SQL
BEGIN
    IF ISNULL(@DenialMergingTime) THEN
        RETURN 0;
    ELSE
        RETURN @DenialMergingTime;
    END IF;
END|

CREATE VIEW MergedDenialsViewHelper AS
    SELECT MIN(Second.DateTime) AS GroupTime,
        First.FeatureId,
        First.UserHostId,
        SUM(Second.Multiplicity) AS MultiplicitySum
    FROM Denial AS First 
        JOIN Denial AS Second 
            ON First.FeatureId = Second.FeatureId
                AND First.UserHostId = Second.UserHostId
                AND First.DateTime >= Second.DateTime
                AND First.DateTime - Second.DateTime < GetDenialMergingTime()
    GROUP BY First.DateTime, First.FeatureId, First.UserHostId, First.Licenses;

CREATE VIEW MergedDenials AS
    SELECT GroupTime, 
        FeatureId,
        UserHostId, 
        MAX(MultiplicitySum) AS MultiplicitySum
    FROM MergedDenialsViewHelper
    GROUP BY GroupTime, FeatureId, UserHostId;

然后显示每5秒合并的功能1和3的用户1和2的拒绝，您要做的就是:

Then to show denials from user 1 and 2 on features 3 and 4 merged every 5 seconds all you have to do is:

SET @DenialMergingTime := 5;
SELECT GroupTime, FeatureId, UserHostId, MultiplicitySum FROM MergedDenials WHERE UserHostId IN (1, 2) AND FeatureId IN (3, 4);

我使用视图是因为它很容易过滤数据并在jQuery网格中显式使用它，自动排序，限制记录数等.

I use the view because in it it's easy to filter data and to use it explicitly in the jQuery grid, automatically order, limit number of records and so on.

但这只是一个丑陋的解决方法.有适当的方法吗?

But it's just an ugly workaround. Is there a proper way to do this?

推荐答案

实际上，如果您创建func:

Actually if you create func:

create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1;

并查看:

create view h_parm as
select * from sw_hardware_big where unit_id = p1() ;

然后您可以使用参数调用视图:

Then you can call a view with a parameter:

select s.* from (select @p1:=12 p) parm , h_parm s;

希望对您有帮助.

这篇关于我可以在MySQL中使用参数创建视图吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！