Puis-je créer une vue avec des paramètres dans MySQL ?

Question

J'ai un point de vue comme celui-ci :

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

Je voudrais le rendre plus générique, c'est-à-dire changer 2 en variable. J'ai essayé ceci :

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

Mais MySQL ne le permet pas.

J'ai trouvé une vilaine solution de contournement :

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

Et puis la vue est :

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

Mais l'apparence est vraiment mauvaise, et l'utilisation est également mauvaise - je dois définir la @MyVariable avant chaque utilisation de la vue.

Existe-t-il une solution que je pourrais utiliser comme ça ?

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

La situation concrète est la suivante : J'ai une table qui stocke les informations sur la demande refusée :

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;

Une multiplicité est un nombre de demandes identiques enregistrées dans la même seconde. Je veux afficher une liste des refus, mais parfois, lorsque la demande est refusée, elle réessaie plusieurs fois, juste pour être sûre. Donc, généralement, lorsque le même utilisateur se voit refuser 3 fois la même fonction en quelques secondes, il s'agit en fait d'un seul refus. Si nous avions une ressource de plus, pour répondre à cette demande, les deux prochains refus n'auraient pas lieu. Nous voulons donc regrouper les refus dans un rapport permettant à l'utilisateur de spécifier l'intervalle de temps dans lequel les refus doivent être regroupés. Par exemple, si nous avons des refus (pour l'utilisateur 1 sur la fonction 1) dans les timestamps : 1,2,24,26,27,45 et l'utilisateur veut regrouper les refus qui sont plus proches les uns des autres que 4 sec, il devrait obtenir quelque chose comme ceci : 1 (x2), 24 (x3), 45 (x1). Nous pouvons supposer que les espaces entre les vrais refus sont beaucoup plus grands qu'entre les duplications. J'ai résolu le problème de la manière suivante :

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;

Ensuite, pour montrer les refus des utilisateurs 1 et 2 sur les fonctions 3 et 4 fusionnées toutes les 5 secondes, tout ce que vous devez faire est :

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

J'utilise la vue car il est facile d'y filtrer les données et de les utiliser explicitement dans la grille jQuery, de les ordonner automatiquement, de limiter le nombre d'enregistrements et ainsi de suite.

Mais c'est juste une vilaine solution de contournement. Existe-t-il une façon correcte de procéder ?

Answer 1

En fait, si vous créez func :

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

et voir :

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

Ensuite, vous pouvez appeler une vue avec un paramètre :

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

J'espère que cela vous aidera.

Answer 2

CREATE VIEW MyView AS
   SELECT Column, Value FROM Table;

SELECT Column FROM MyView WHERE Value = 1;

C'est la solution appropriée dans MySQL, d'autres SQLs vous permettent de définir les vues plus précisément.

Remarque : à moins que la vue ne soit très compliquée, MySQL optimisera parfaitement cette opération.

Answer 3

J'ai déjà trouvé une autre solution qui n'utilise pas de procédures stockées, mais plutôt une table de paramètres et un peu de magie de connection_id().

EDIT (Copié à partir des commentaires)

créer une table qui contient une colonne appelée connection_id (faites-en un bigint). Placez des colonnes dans cette table pour les paramètres de la vue. Mettez une clé primaire sur le connection_id . remplacer dans la table des paramètres et utiliser CONNECTION_ID() pour remplir la valeur connection_id. Dans la vue, utilisez une jointure croisée avec la table des paramètres et mettez WHERE param_table.connection_id = CONNECTION_ID() . Cette jointure croisée ne comportera qu'une seule ligne de la table des paramètres, ce qui est ce que vous voulez. Vous pouvez ensuite utiliser les autres colonnes dans la clause where, par exemple where orders.order_id = param_table.order_id .

Answer 4

Je voudrais partager quelque chose sur la vue paramétrée. N'hésitez pas à exprimer vos divergences d'opinion, car je suis toujours à la recherche de preuves concrètes de mes résultats.

Lorsque vous utilisez la vue paramétrée comme mentionné par @Leonard Strashnoy dans la clause from, cela fonctionne bien. Mais pour mes besoins, j'utilise la même vue paramétrée dans l'instruction Select au lieu de l'utiliser après la clause from.

select col1,col2,(select @p1:=12 p) parm , (select col4 from h_parm where ....) viewcol from table 1;

Cela semble être un bogue. Ce que je vois, c'est que les résultats de la colonne (viewcol) sont aléatoirement affichés comme étant nuls. Pour le prouver, j'ai écrit la même requête de vue comme une fonction et je l'ai placée à côté de cette colonne, en passant les mêmes paramètres. Cette fonction a fonctionné comme un charme.

Quelqu'un peut-il avoir une expérience similaire ? Veuillez partager.