过程中的 MySQL 错误 #1351 - 视图的 SELECT 包含变量或参数

2022-01-05 00:00:00 sql mysql mamp phpmyadmin

PhpMyAdmin 不接受程序内的参数id_jugador_IN".

PhpMyAdmin doesn't accept the parameters "id_jugador_IN" inside the procedure.

这是我们正在执行的过程:

This is the procedure we're executing:

SET GLOBAL event_scheduler = 1;

use traviandatabase;

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_monitoritzacio_jugador $$

CREATE PROCEDURE sp_monitoritzacio_jugador(IN id_jugador_IN INT(10))

BEGIN

    CREATE OR REPLACE 
    VIEW dades_ususari AS 
    SELECT j.nom AS jugador, j.sexe AS sexe, j.edat AS edat, j.correu AS correu, a.nom AS alianca, p.nom AS pais, c.nom_ciutat AS ciutat
    FROM jugador AS j
    JOIN alianca AS a
    JOIN pais AS p
    JOIN ciutat AS c
    ON j.id_jugador = id_jugador_IN
    GROUP BY id_jugador_IN;

END $$

DELIMITER ;

CALL sp_monitoritzacio_jugador(1);

这是jugador"表:

And this is the table "jugador":

DROP TABLE IF EXISTS `jugador`;
CREATE TABLE `jugador` (
  `id_jugador` int(10) NOT NULL AUTO_INCREMENT,
  `id_raca` int(10) NOT NULL,
  `id_pais` int(10) NOT NULL,
  `id_alianca` int(10) DEFAULT '0',
  `nom` varchar(20) NOT NULL,
  `sexe` enum('Home','Dona') NOT NULL,
  `edat` int(10) NOT NULL,
  `correu` varchar(20) NOT NULL,
  PRIMARY KEY (`id_jugador`),
  KEY `jugador-alianca` (`id_alianca`),
  KEY `id_pais` (`id_pais`),
  KEY `id_raca` (`id_raca`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

所以它不会停止显示相同的错误:

So it doesn't stop displaying the same error:

MySQL 说:1351 - 视图的 SELECT 包含一个变量或参数

MySQL said: 1351 - View's SELECT contains a variable or parameter

推荐答案

如您所见 - 视图不能使用变量，这是一个限制.您正在尝试创建具有精确 WHERE 条件的视图；先构造 CREATE VIEW 语句文本，然后使用 preparedstatements 来执行语句，它会帮助你.但是，您真的需要它来创建、创建和创建新视图吗?

As you see - view cannot use variables, it is a limitation. You are trying to create a view with exact WHERE condition; construct the CREATE VIEW statement text firstly, then use prepared statements to execute the statement, it will help you. But, do you really need it, to create, create and create new view?

这是评论中引用的内容.

This is the content of the reference in the comment.

Olexandr Melnyk 一个简单的解决方法MySQL 对视图中局部变量使用的限制是使用一个函数，它返回变量的值:

Olexandr Melnyk A simple workaround for MySQL's limitation on local variables usage in views is to use a function, which returns variable's value:

create function book_subject
returns varchar(64) as
return @book_subject;

create view thematical_books as
select title
, author
from books
where subject = book_subject();

相关文章