mariadb 在删除查询中使用存储过程参数将删除所有记录

好吧，你们听好了
我想创建一个存储过程来删除某些表中的值。这是它看起来的样子：

CREATE OR REPLACE PROCEDURE `delete_sap`(sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    SELECT sap_to_delete as ' '; -- Just want to make sure it's coming in. It is.
    DELETE FROM data01 WHERE sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_reviewers WHERE sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_readonly WHERE sap_sapid=sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_editors WHERE sap_sapid=sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM page WHERE sapid=sap_to_delete;
    DELETE FROM element WHERE element.sapid=sap_to_delete;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END//

DELIMITER ;

我意识到我可以根据外键将其中一些表更改为ON DELETE CASCADE，但这不是我的数据库，所以...
无论如何，目前，当我用CALL delete_sap("idtodelete")调用时，一切都被删除了。或者，每个表中的所有内容，我只想删除其中的某些记录。我没有收到任何警告。我在某种程度上打错了一些东西，使这删除一切。
假设我的data 01表是这样构建的：

DROP TABLE IF EXISTS `data01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `data01` (
  `sapid` char(10) NOT NULL,
  `pageid` char(10) NOT NULL,
  `elemid` char(10) NOT NULL,
  `json` longtext DEFAULT NULL,
  PRIMARY KEY (`sapid`,`pageid`,`elemid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

我在里面放了一些东西

| sapid      | pageid     | elemid     | json  
--------------------------------------------------------------------------        
| 3Fdk...jo8 | HhN...ajn8 | xYh5...uUz | { "os": "Mac", "name": "Safari" }                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| 6moo...Vwm | 0wR...2n47 | Bo3F...1lC | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 6moo...Vwm | 0wR...2n47 | DRvb...oje | NULL   
| 6moo...Vwm | kYAy...AJq | GhJG...KzA | {"title":"My test sap","preparedBy":"Ghost McFee","date":"2023-09-13T03:23:21.062Z"}
| p4th...fvW | Cokg...Ly4 | thF...CmpT | "What is the goal!"

同样，当我使用语句CALL delete_sap("6moo...Vwm")时，我希望只有具有该sapid的记录被删除。事实并非如此。一切都没了
我错过了什么？TIA！TIA！
编辑添加，当我改变到这个，什么都没有删除（甚至不是我期望去），但没有错误。

DELIMITER //

CREATE OR REPLACE PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    SELECT sap_to_delete as ' ';
    DELETE FROM data01 WHERE data01.sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_has_owners.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_reviewers WHERE sap_has_reviewers.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_readonly WHERE sap_has_readonly.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_editors WHERE sap_has_editors.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM page WHERE page.sapid = sap_to_delete;
    DELETE FROM element WHERE element.sapid = sap_to_delete;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END//

DELIMITER ;

根据一些问题（我做了一些修改，试图清除任何坏苹果），这是我从SHOW CREATE PROCEDURE delete_sap中看到的：

| Procedure  | sql_mode                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| delete_sap | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`admin`@`localhost` PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    
    START TRANSACTION;
    DELETE FROM data01 WHERE data01.sapid LIKE sap_to_delete;
    DELETE from sap WHERE sap.sapid LIKE sap_to_delete;
    DELETE FROM page WHERE page.sapid LIKE sap_to_delete;
    DELETE FROM element WHERE element.sapid LIKE sap_to_delete;
    IF `_rollback` THEN
    ROLLBACK;
    ELSE
    COMMIT;
    END IF;
END | utf8mb3              | utf8mb3_general_ci   | utf8mb4_general_ci |

排序规则是否为utf8mb3_general_ci有关系吗？如何更改此过程定义的值？

最终，我的问题是整理，正如我在遵循@NandalalSeth的建议检查SHOW CREATE PROCEDURE delete_sap时发现的那样。运行该查询显示

| Procedure  | sql_mode                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| delete_sap | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`admin`@`localhost` PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    
    START TRANSACTION;
    DELETE FROM data01 WHERE data01.sapid LIKE sap_to_delete;
    DELETE from sap WHERE sap.sapid LIKE sap_to_delete;
    DELETE FROM page WHERE page.sapid LIKE sap_to_delete;
    DELETE FROM element WHERE element.sapid LIKE sap_to_delete;
    IF `_rollback` THEN
    ROLLBACK;
    ELSE
    COMMIT;
    END IF;
END | utf8mb3              | utf8mb3_general_ci   | utf8mb4_general_ci |

但这一过程也是极其笨重和繁琐的。在更新了一些外键之后，我只需要一个delete语句。
此外，我可以简单地退出并回滚sqlexception，而不是更新布尔值。
最终，我的解决方案如下。

DELIMITER //

CREATE OR REPLACE PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SHOW ERRORS;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    SELECT sap_to_delete;
    DELETE from sap WHERE sap.sapid LIKE sap_to_delete COLLATE utf8mb4_unicode_ci;
    COMMIT;
END//

DELIMITER ;

感谢大家提供指导性的问题/建议，帮助我来到这里。

mariadb 在删除查询中使用存储过程参数将删除所有记录

1条答案

相关问题

热门标签

最新问答