如何在MySQL存储过程中按ID获取行? [英] How to get a row by ID in a MySQL Stored Procedure?

问题描述

我在MySQL中的存储过程有问题.我想通过传递ID参数来获得一行，但是，看来SP忽略了WHERE过滤器并显示了表的所有列.

I have a problem with a Stored Procedure in MySQL. I want to get a single row by passing the ID parameter, however, it seems the SP ignores the WHERE filter and displays all the columns of the table.

我感到奇怪的是，如果我在存储过程之外的查询中传递了id的特定值(例如1003)，则会返回预期的结果.

What I find strange is that if I pass a specific value for id in a query outside the stored procedure, for example 1003, returns the expected result.

对不起，如果我的错误很愚蠢，我是新手.

Sorry if my mistake is silly, I'm newbie.

表结构是这样的:

CREATE TABLE Paciente
 (
    ID INT AUTO_INCREMENT NOT NULL,
    DNI CHAR(8) UNIQUE NULL,
    Nombre NVARCHAR(70) NOT NULL,
    Apellido_Paterno NVARCHAR(30) NOT NULL,
    Apellido_Materno NVARCHAR(30) NOT NULL,
    Edad TINYINT NOT NULL,
    Sexo CHAR(1) NOT NULL,
    Calle NVARCHAR(50) NULL,
    Numero_Domicilio SMALLINT(4) NULL,
    Telefono NVARCHAR(8) NULL,
    Movil NVARCHAR(10) NULL,
    Estado_Civil NVARCHAR(20) NULL,
    Ocupacion NVARCHAR(30) NULL,
    Fecha_Registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    Estado BOOLEAN NULL DEFAULT TRUE,
    PRIMARY KEY(ID)
)ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000;


Stored Procedure:

DELIMITER $$
CREATE PROCEDURE `buscarPaciente`(IN id INT)
BEGIN
SELECT * FROM Paciente WHERE ID = id LIMIT 1;
END
$$
DELIMITER ;

推荐答案

该行为是由于将输入参数命名为与列名相同而引起的，因此mysql无法区分列和where子句中的参数.将输入参数重命名为param_ID，它将返回带有请求的ID值的记录.

The behaviour is caused by naming the input parameter same as the column name, therefore mysql cannot distinguish between the column and the parameter within the where clause. Rename the input parameter to let's say param_ID and it will return the record with the requested ID value.

DELIMITER $$
CREATE PROCEDURE `buscarPaciente`(IN param_ID INT)
    BEGIN
        SELECT * FROM Paciente WHERE ID = param_ID LIMIT 1;
    END
$$
DELIMITER ;

这篇关于如何在MySQL存储过程中按ID获取行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！