在mysql中，删除工作为截断 [英] In mysql, delete working as truncate

问题描述

我正在尝试为大学分配一个简单的项目。这样，当我试图从mysql中的存储过程中删除一条记录时，该delete语句将删除所有记录。

I am trying to make a simple project for college assignment. In that,When I am trying to delete a record from a stored procedure in mysql, that delete statement is deleting all the records.

这是代码：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE 
           `removebus`(in busnumber int,out message varchar(255))
BEGIN

    delete from fare where busnumber = busnumber;
    delete from bus where busnumber = busnumber;
    set message = 'success';

    else
        set message = 'not found';
    end if;

END

我正在执行类似call removebus（1，@ message ）;

And I am executing like call removebus(1,@message);

当我尝试仅从命令窗口执行删除语句时，它仅删除一条记录，但是当我执行call语句时，所有记录都是从我的表格中删除。只需添加一下，busnumber是总线表的主键。我不明白为什么。请尽快提供帮助。

When I am trying to only execute the delete statment from command window, then it is deleting one record only but when I executed the call statement, all the records are deleted from my table. Just to add, busnumber is primary key of bus table. I am not able to understand why. Please help ASAP.

推荐答案

两件事。首先删除 不能作为截断表。它可能具有相同的结果，但是性能却大不相同。 删除记录其所有交易； 截断表不会。

Two things. First delete is not working as truncate table. It might have the same results, but the performance is much, much different. delete logs all its transactions; truncate table does not.

第二，养成用特殊前缀命名参数参数的习惯，例如：

Second, get into the habit of naming parameter arguments with a special prefix, such as:

CREATE DEFINER=`root`@`localhost` PROCEDURE 
           `removebus`(in in_busnumber int, out out_message varchar(255))
. . . 

如果您不习惯这种习惯，您可能会继续犯同样的错误时间长了（至少，这是我的经验）。

If you don't get into this habit, you are likely to continue making the same mistake for a long time (at least, that is my experience).

这篇关于在mysql中，删除工作为截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！