By ssobczak


2010-02-17 15:25:33 8 Comments

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:

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

But MySQL doesn't allow this.

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.

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:

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:

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.

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

3 comments

@Justin Swanhart 2011-04-06 17:20:44

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.

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.

@marzapower 2011-09-16 07:01:50

Which one? Please tell us something more.

@Justin Swanhart 2013-05-17 08:34:00

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.

@Rick James 2016-10-25 18:20:09

KLUDGE! But cute.

@Leonard Strashnoy 2011-03-16 21:09:25

Actually if you create 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.

@ssobczak 2011-03-20 11:05:50

Wow, this is one of the most hacky things that i ever saw in SQL ;) But it's exactly what I wanted to do.

@MosheElisha 2012-04-04 05:31:32

Shouldn't it be NON DETERMINISTIC?

@MosheElisha 2012-05-09 14:53:20

@MichaelMior The MySQL docs say: "A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly." - as I understand it is not only during the view execution.

@Michael Mior 2012-05-09 15:53:22

@MosheElisha If the view is the only place you are using this function and you know that the function is deterministic within the view, you will get a slight performance boost from specifying DETERMINISTIC and it's perfectly safe to do so. I don't believe MySQL caches function results across queries.

@Clayton Stanley 2012-10-18 18:50:20

This technique works when creating a view within a stored procedure, when the created view is dependent on a varchar passed to the stored procedure. In this case, I had to 'set @p1 = 12;' on the line before the call to create the view.

@Gruber 2013-10-18 20:57:13

Is there any potential for problems (tenant data mixup) if several database tenants call this code concurrently?

@Mr_and_Mrs_D 2014-01-31 14:23:52

What is parm ?

@Robin Kanters 2014-06-24 22:32:40

@Mr_and_Mrs_D the derived table needs an alias. you can call it whatever you want, but you cannot omit it

@BuvinJ 2015-12-29 22:50:36

The variable p1 retains its value after this, so if you use the view again without passing the parameter, it will use the prior one passed - which maybe confusing! You can "clear" it after it's use like this: select s.* from (select p1:=12 p) pass, h_parm s, (select @p1:=-1) clear; (Assuming -1 is an invalid value for this purpose)

@Mir-Ismaili 2018-09-25 02:10:11

Seems there's no need to s. This is enough (as I tested): SELECT h_parm.* FROM (SELECT @p1:=12 p) parm , h_parm;

@Ngoc Nam 2018-09-28 02:33:56

Hi all, I got a problem when creating a new function. My account is not SUPER, so I cannot create it. And for security, and server management company did not want to change any about security (like they said). What should I do in this case? We are using RDS.

@MindStalker 2010-02-17 15:47:14

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.

Note: Unless the View is very complicated, MySQL will optimize this just fine.

@ssobczak 2010-02-18 11:27:47

In my case the WHERE part, in which I want to use parameter is in neasted select, so it's imposible to filter it from outside of the view.

@ssobczak 2010-02-18 14:29:07

Actually neasted selects are not allowed in views, but I splitted them into two views. V1 filters and aggregates data, and on top of V1 there is V2. I can't filter data from V1 outside it (in V2), becouse outside they are visible as aggregated.

@MindStalker 2010-02-18 14:40:11

Then don't use a view at all, if you need exact control build the entire query every time, or build the query inside a stored procedure. Saving as a view seems pointless. Though if you post the queries you are trying to achieve someone might be able to suggest a different/better route.

@ssobczak 2010-02-22 09:22:23

I wanted not to do this, becouse it will make my simple question quite complex, but if you think it may be usefull, I'll try.

@Doğa Özkaracaabatlıoğlu 2016-05-15 21:36:10

it won't work, if the values are in the inner join..

Related Questions

Sponsored Content

4 Answered Questions

[SOLVED] What are the -Xms and -Xmx parameters when starting JVM?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

20 Answered Questions

[SOLVED] Does Java support default parameter values?

41 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

15 Answered Questions

[SOLVED] How to get a list of MySQL user accounts

  • 2009-07-16 03:23:53
  • burntsugar
  • 1392664 View
  • 1277 Score
  • 15 Answer
  • Tags:   mysql mysql5

35 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

22 Answered Questions

[SOLVED] Set a default parameter value for a JavaScript function

21 Answered Questions

[SOLVED] How can I pass a parameter to a setTimeout() callback?

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

8 Answered Questions

[SOLVED] How are parameters sent in an HTTP POST request?

Sponsored Content