在MySQL中，删除似乎会截断整个表 [英] In MySQL, delete seems to be truncating the whole table

问题描述

我正在尝试为大学分配一个简单的项目.那就是，当我试图从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); 那样执行.

当我尝试仅从命令窗口执行delete语句时，它仅删除一条记录，但是当我执行call语句时，所有记录将从我的表中删除.只需添加一下，busnumber是总线表的主键.我不明白为什么.

When I am trying to only execute the delete statement 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.

推荐答案

两件事.第一个 delete 不能用作 truncate table .它可能具有相同的结果，但是性能却大不相同. delete 记录其所有事务；截断表没有.

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屋！