我能翻译您的问题：在MySQL中，我可以创建带参数的视图吗？

Question

我有一个视图如下：

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

我希望让它更加通用，也就是将2改为一个变量。我尝试了以下代码：

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

但是MySQL不允许这样做。我找到了一个丑陋的解决方法：

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

然后视图是：

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

但它看起来真的很糟糕，使用起来也很糟糕 - 我必须在每次使用视图之前设置@MyVariable。

是否有解决方案，我可以像这样使用：

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

具体情况如下： 我有一个存储被拒绝请求信息的表格：

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,2,24,26,27,45（用户1在功能1上）的拒绝，并且用户希望将彼此更接近的拒绝分组而不是4秒，他应该得到像这样的结果：1（x2），24（x3），45（x1）。我们可以假设真正的拒绝之间的间隔要比复制之间的间隔大得多。我通过以下方式解决了这个问题：

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;

如果要显示用户1和2对功能3和4的拒绝，每5秒合并一次，您需要做的只是：

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

我使用视图，因为在其中过滤数据并在jQuery表格中明确使用它非常容易，可以自动排序、限制记录数量等等。

但这只是一个丑陋的解决方法。有没有更好的方法来做到这一点？

- ssobczak

Answer 1

实际上，如果您创建了一个函数：

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

并查看：

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

你可以使用参数调用一个视图：

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

- Leonard Strashnoy

---

哇，这是我在SQL中看到的最“hacky”的事情之一；）但这正是我想要做的。 - ssobczak

如果视图是您仅使用此函数的地方，并且您知道该函数在视图内是确定性的，那么指定“DETERMINISTIC”将使性能略有提高，这样做是完全安全的。我不认为MySQL会在查询之间缓存函数结果。 - Michael Mior

当在存储过程中创建视图时，如果所创建的视图依赖于传递给存储过程的 varchar，则可以使用此技术。在这种情况下，在调用创建视图之前，必须在该行上设置“set @p1 = 12;”。 - Clayton Stanley

如果多个数据库租户同时调用此代码，是否存在问题（如租户数据混淆）的潜在风险？ - Gruber

变量p1在此后保留其值，因此如果您再次使用该视图而没有传递参数，它将使用之前传递的值 - 这可能会让人感到困惑！您可以像这样在使用后“清除”它：select s.* from (select p1:=12 p) pass, h_parm s, (select @p1:=-1) clear; （假设-1是此目的无效的值） - BuvinJ

Answer 2

CREATE VIEW MyView AS
   SELECT Column, Value FROM Table;


SELECT Column FROM MyView WHERE Value = 1;

在MySQL中，是否有合适的解决方案，其他一些SQL语言可以更精确地定义视图。

注意：除非视图非常复杂，否则MySQL将对其进行优化处理。

- MindStalker

---

在我的情况下，我想使用参数的 WHERE 部分位于嵌套选择中，因此无法从视图外部进行过滤。 - ssobczak

如果您需要精确控制，请根本不要使用视图，每次构建整个查询，或在存储过程中构建查询。 保存为视图似乎是毫无意义的。 不过，如果您发布要实现的查询，可能会有人能够建议不同/更好的路线。 - MindStalker

这种格式不允许您根据参数更改结果集或表的名称。 - MMEL

从根本上讲，视图的行为类似于表格，因此您可以针对其任何列使用WHERE子句。 - quickshiftin

实际上，这样做的性能非常不同。过滤视图比直接执行查询要慢得多。 - WaveMax

Answer 3

我之前想出了一种不使用存储过程的解决方法，而是使用参数表和一些connection_id()技巧。

编辑（从评论中复制）

创建一个包含名为connection_id的列（将其设置为bigint）的表。在该表中放置用于视图参数的列。在connection_id上放置主键。使用replace into替换参数表，并使用CONNECTION_ID()填充connection_id值。在视图中使用交叉连接到参数表，并将WHERE param_table.connection_id = CONNECTION_ID()放入其中。这将与参数表中的一个行交叉连接，这正是您想要的。然后可以在where子句中使用其他列，例如where orders.order_id = param_table.order_id。

- Justin Swanhart