我可以在 MySQL 中创建带参数的视图吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2281890/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I create view with parameter in MySQL?
提问by ssobczak
I have a view like this:
我有这样的看法：
CREATE VIEW MyView AS
SELECT Column FROM Table WHERE Value = 2;
I'd like to make it more generic, it means to change 2 into a variable. I tried this:
我想让它更通用，这意味着将 2 更改为变量。我试过这个：
CREATE VIEW MyView AS
SELECT Column FROM Table WHERE Value = @MyVariable;
But MySQL doesn't allow this.
但是 MySQL 不允许这样做。
I found an ugly workaround:
我发现了一个丑陋的解决方法：
CREATE FUNCTION GetMyVariable() RETURNS INTEGER DETERMINISTIC NO SQL
BEGIN RETURN @MyVariable; END|
And then the view is:
然后视图是：
CREATE VIEW MyView AS
SELECT Column FROM Table WHERE Value = GetMyVariable();
But it looks really crappy, and the usage is also crappy - I have to set the @MyVariable before each usage of the view.
但它看起来真的很糟糕，而且用法也很糟糕——我必须在每次使用视图之前设置 @MyVariable。
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;
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:
多重性是在同一秒内记录的相同请求的数量。我想显示一个拒绝列表，但有时，当应用程序被拒绝时，它会重试几次以确保。因此，通常情况下，当同一用户在几秒钟内在同一功能上被拒绝 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;
Then to show denials from user 1 and 2 on features 3 and 4 merged every 5 seconds all you have to do is:
然后要显示用户 1 和 2 对每 5 秒合并一次的功能 3 和 4 的拒绝，您所要做的就是：
SET @DenialMergingTime := 5;
SELECT GroupTime, FeatureId, UserHostId, MultiplicitySum FROM MergedDenials WHERE UserHostId IN (1, 2) AND FeatureId IN (3, 4);
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.
我使用视图是因为它很容易过滤数据并在 jQuery 网格中显式使用它、自动排序、限制记录数等。
But it's just an ugly workaround. Is there a proper way to do this?
但这只是一个丑陋的解决方法。有没有合适的方法来做到这一点？
回答by Leonard Strashnoy
Actually if you create func:
实际上，如果您创建 func：
create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1;
and view:
并查看：
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;
I hope it helps.
我希望它有帮助。
回答by MindStalker
CREATE VIEW MyView AS
SELECT Column, Value FROM Table;
SELECT Column FROM MyView WHERE Value = 1;
Is the proper solution in MySQL, some other SQLs let you define Views more exactly.
是 MySQL 中的正确解决方案，其他一些 SQL 可以让您更准确地定义视图。
Note: Unless the View is very complicated, MySQL will optimize this just fine.
注意：除非View很复杂，否则MySQL会优化这个就好了。
回答by Justin Swanhart
I previously came up with a different workaround that doesn't use stored procedures, but instead uses a parameter table and some connection_id() magic.
我之前想出了一个不同的解决方法，它不使用存储过程，而是使用参数表和一些 connection_id() 魔法。
EDIT (Copied up from comments)
编辑（从评论中复制）
create a table that contains a column called connection_id(make it a bigint). Place columns in that table for parameters for the view. Put a primary key on the connection_id. replace into the parameter table and use CONNECTION_ID()to populate the connection_id value. In the view use a cross join to the parameter table and put WHERE param_table.connection_id = CONNECTION_ID(). This will cross join with only one row from the parameter table which is what you want. You can then use the other columns in the where clause for example where orders.order_id = param_table.order_id.
创建一个包含名为connection_id（使其成为 bigint）列的表。在该表中放置列以获取视图的参数。将主键放在connection_id. 替换到参数表中并用于CONNECTION_ID()填充 connection_id 值。在视图中使用交叉连接到参数表并放置WHERE param_table.connection_id = CONNECTION_ID(). 这将仅与参数表中的一行交叉连接，这正是您想要的。然后您可以使用 where 子句中的其他列，例如 where orders.order_id = param_table.order_id。
