3
回答
Mysql执行存储过程,32位可以成功,64位执行失败
开发十年,就只剩下这套Java开发体系了   
Win2008 , MySQL 5.7
base_product :70w行数据
txnrack :140w行数据
结果insert是:140w行数据
同样的数据库结构,在32位上执行成功, 在64位上执行失败。
更郁闷的是,把sql直接在64位上执行也能成功, 放在存储过程里就不能成功。
哪位大神给看看!


CREATE PROCEDURE `sp_abc`(IN `stockInfoNumber` varchar(50))
BEGIN
    DECLARE startDateStr VARCHAR(14) DEFAULT '20100101000000';
    DECLARE createDateStr VARCHAR(14);
    DECLARE warehouse_code VARCHAR(50);


    DECLARE t_error INTEGER DEFAULT 0;  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;


    START TRANSACTION;  


    select history_create_date_str into startDateStr from stock_info si where result_confirm='已确认' ORDER BY si.result_apv_date limit 1;
    select history_create_date_str into createDateStr from stock_info si where stock_info_number = stockInfoNumber;
    select warehouse into warehouse_code from stock_info si where stock_info_number = stockInfoNumber;


    DELETE FROM stock_history WHERE stock_info_number = stockInfoNumber;


    insert into stock_history (
        stock_history_d_id,
        stock_info_number,
        create_date,
        warehouse,
        rack_code,
        plucode,
        title,
        qty)
    SELECT
        CONCAT(SUBSTR(createDateStr, 3, 6) ,tx.rack_code, tx.plucode),
        stockInfoNumber,
        createDateStr,
        warehouse_code,
        tx.rack_code,
        tx.plucode,
        bp.title,
        ifnull(ttr.qty, 0) qty
    FROM
        base_product bp LEFT JOIN txnrack tx on bp.plucode = tx.plucode
        LEFT JOIN 
        (SELECT tt.rack_code, tt.plucode, sum(tt.qty) qty FROM (
        SELECT sr.rack_code, sr.plucode, sr.qty FROM stock_result sr WHERE sr.apv_date = startDateStr
        UNION ALL
        SELECT rd.torack rack_code, rd.plucode, sum(rd.qty) qty FROM receive_d rd WHERE rd.receive_detail_state = '已确认' and rd.apv_date >= startDateStr and rd.apv_date <= createDateStr group by rd.torack,rd.plucode
        UNION ALL
        SELECT smd.fromrack as rack_code, smd.plucode, sum(smd.qty) * -1 qty FROM stock_move_d smd group by smd.fromrack,smd.plucode
        UNION ALL
        SELECT sd.rack_code, sd.plucode, sum(sd.qty) * -1 qty FROM sale_d sd WHERE sd.sale_detail_state = '已确认' group by sd.rack_code,sd.plucode
        ) tt GROUP BY tt.rack_code, tt.plucode) ttr ON tx.rack_code = ttr.rack_code AND tx.plucode = ttr.plucode;


    IF t_error = 1 THEN  
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;


    select t_error;


END
举报
_简约
发帖于2年前 3回/192阅
顶部