MySQL存储过程传参后无法更新表问题求助

Let's walk through the possible issues step by step to fix your updateContracts procedure not updating the work_contract_objective table:

• Check if you're calling the correct procedure
  Your call statement cuts off at call morpheus_mvp.upd... — make sure you're using the full, correct procedure name:

  call morpheus_mvp.updateContracts(@p_work_contract_id, @p_product);
  

  A typo here means you're not executing the procedure you defined at all.

• Verify your parameter values match existing records
  You set @p_work_contract_id = 0 — run this query to check if any records exist with that ID:

  SELECT * FROM morpheus_mvp.work_contract_objective WHERE work_contract_id = 0;
  

  If no rows come back, the UPDATE statement in your procedure is executing but affecting 0 rows (since there's nothing to update). Double-check that you're passing a work_contract_id that actually exists in the table.

• Check for uncommitted transactions
  If you're using InnoDB tables (the default), transactions might be holding up your changes. First, check if autocommit is enabled:

  SELECT @@autocommit;
  

  If it returns 0, autocommit is off, so you need to manually commit after calling the procedure:

  COMMIT;
  

  You can also check for active, uncommitted transactions with:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
  

• Confirm permissions are set correctly
  The procedure's DEFINER is root@localhost, but the user you're using to call the procedure might lack necessary permissions:

  • Ensure your user has EXECUTE permission on morpheus_mvp.updateContracts
  • Ensure your user has UPDATE permission on morpheus_mvp.work_contract_objective
    Run this to check your current user's grants:

  SHOW GRANTS FOR CURRENT_USER;
  

• Validate the stored procedure's definition
  Double-check that the procedure doesn't have typos in table/column names. Run this to view the full procedure definition:

  SHOW CREATE PROCEDURE morpheus_mvp.updateContracts;
  

  Make sure work_contract_objective, product, and work_contract_id all match the actual table schema (no misspellings like work_contract_ids or prodcut).

• Test the update manually
  Bypass the procedure entirely to test the core logic:

  UPDATE morpheus_mvp.work_contract_objective SET product = '0' WHERE work_contract_id = 0;
  

  If this updates rows, the issue is with how you're calling the procedure or parameter handling. If it still doesn't update rows, you know the problem is with the WHERE clause or missing records.

内容的提问来源于stack exchange，提问作者Danish