MySQL 如何在mysql视图中传递动态参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19946526/
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
how pass dynamic parameter in mysql view
提问by java baba
I have created this in mysql
我在 mysql 中创建了这个
CREATE VIEW MYVIEW AS (
SELECT A.FNAME
, A.LNAME
, B.EMAIL
FROM EMPLOYEE A, EMPEMAIL B
WHERE A.EID = :empId
AND A.EID = B.EID
AND B.EMAILTYP = :emailType)
now i want make " empId " and " emailType " dynamic.I mean pass the value at select time. what need to change in code?? thanx in advance
现在我想让“ empId ”和“ emailType ”成为动态的。我的意思是在选择时间传递值。代码需要改什么？？提前谢谢
回答by Devart
You can use this solution with a function -
您可以将此解决方案与功能一起使用 -
CREATE FUNCTION func() RETURNS int(11)
RETURN @var;
CREATE VIEW view1 AS
SELECT * FROM table1 WHERE id = func();
Using example:
使用示例：
SET @var = 1;
SELECT * FROM view1;
回答by Mureinik
Just create the view without the parameters (i.e., to take care of the join only):
只需创建没有参数的视图（即，仅处理连接）：
CREATE VIEW MYVIEW AS (
SELECT A.FNAME
, A.LNAME
, B.EMAIL
, A.EID AS EID -- added to be used in the WHERE
, B.EMAILTYP AS EMAILTYP -- added to be used in the WHERE
FROM EMPLOYEE A, EMPEMAIL B
WHERE A.EID = B.EID)
And apply the dynamic parameters when you query:
并在查询时应用动态参数：
SELECT FNAME, LNAME, EMAIL
FROM my_view
WHERE eid = 'your_empId' AND emailtyp = 'your_emailType'
Note the WHEREshown above, it uses the two extra fields declared in the VIEW
请注意WHERE上面显示的内容，它使用了声明中的两个额外字段VIEW
