You need to enable JavaScript to run this app. C#调用带输入参数的MySQL存储过程finish_record的错误排查
优惠活动
大模型
产品
解决方案
定价
更多

C#调用带输入参数的MySQL存储过程finish_record的错误排查

问题描述

调用带1个输入参数的存储过程finish_record时执行报错,排查同主题参考内容后未定位到代码问题,但确定故障点必然存在。

原始代码片段

存储过程实现

CREATE DEFINER=`root`@`localhost` PROCEDURE `finish_record`(
IN recordId INT
)
BEGIN
UPDATE bnirolovani.record r INNER JOIN(
select Sum(SumError) as Total, count(RollID) as Rolls, sum(Lenght) as Lenght, sum(ExtraMeter) as ExtraMeter from bnirolovani.roll where RecordID=recordId)
i on r.RecordID = recordId SET r.SumError = i.Total, r.SumReels=i.Rolls,r.SumProduced=i.Lenght, r.SumExtraMeter=ExtraMeter;

UPDATE record r
        INNER JOIN
    (SELECT 
        SUM(Lenght) AS Total
    FROM
        bnirolovani.roll
    WHERE
        RecordID = recordId AND Quality = 0) i ON r.RecordID = recordId 
SET 
    r.QualityE = i.Total;

UPDATE record r
        INNER JOIN
    (SELECT 
        SUM(Lenght) AS Total
    FROM
        bnirolovani.roll
    WHERE
        RecordID = recordId AND Quality = 1) i ON r.RecordID = recordId 
SET 
    r.QualityII = i.Total;

UPDATE record r 
SET 
    DateProducedF = NOW()
WHERE
    r.RecordID = recordID;
END

C# 调用逻辑

private void FinishRecord(int recordID)
{
    try
    {
        string con = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (MySqlConnection conection = new MySqlConnection(con))
        {
            using (MySqlCommand cmd = new MySqlCommand("finish_record", conection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("recordId", MySqlDbType.Int32).Value=recordID;
                conection.Open();
                cmd.ExecuteNonQuery();
                conection.Close();
            }
        }
    }
    catch (Exception ex)
    {
        throw;
    }
}

明确错误点

  • 所有UPDATE语句的INNER JOIN关联条件写的是r.RecordID = recordId,完全没有用到关联表i的字段做匹配,JOIN逻辑属于冗余无意义的写法,不仅会额外增加SQL解析开销,还可能在i表返回空值时触发更新异常。
  • 最后一条UPDATE的WHERE条件存在大小写不匹配问题:入参定义为recordId,条件中写的是recordID,在MySQL区分大小写的配置环境下,会将recordID识别为不存在的表字段,直接抛出Unknown column 'recordID' in 'where clause'错误。
  • 第一条UPDATE的SET子句中r.SumExtraMeter=ExtraMeter没有明确指定字段来源,当record表和i表存在同名字段时,会触发字段歧义报错,应补全为r.SumExtraMeter = i.ExtraMeter。
  • 存储过程入参recordId和业务表的RecordID字段高度重名,容易触发SQL解析歧义,部分版本的MySQL会将WHERE条件中的参数误识别为表自身的字段,导致过滤逻辑失效。建议给入参加统一前缀(如p_recordId)彻底规避冲突。
  • C#端定义参数时未显式指定输入方向,旧版本MySql.Data连接器会将未声明方向的参数默认识别为输出参数,导致传入的数值无法传递给存储过程,建议补充参数方向声明提升兼容性。

内容的提问来源于stack exchange,提问作者LukasVanek

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.02 02:36:04