Golang MySQL存储过程的使用

Golang MySQL存储过程的使用

使用前阅读

ref-1：问题来源 [支付系统数据库设计的关键问题 ]
ref-2：[Golang 用go-sql-driver 调用MySQL存储过程时的问题排查]

编辑文件src/github.com/go-sql-driver/mysql/packets.go文件，在函数writeAuthPacket(cipher []byte) （大约在210行）的客户端标志位处添加下面两个标志：

func (mc *mysqlConn) writeAuthPacket(cipher []byte) error {
        // Adjust client flags based on server support                                                                                     
        clientFlags := clientProtocol41 |
                clientSecureConn |
                clientLongPassword |
                clientTransactions |
                clientLocalFiles |
                **clientMultiStatements |    // 添加这行
                clientMultiResults |**       //再添加这行
                mc.flags&clientLongFlag

        if mc.cfg.clientFoundRows {
                clientFlags |= clientFoundRows
        }

一、MySQL创建存储过程

-- 单行执行以下语句
SET NAMES utf8;
-- dsp_settle数据库
USE dsp_settle;

DROP PROCEDURE IF EXISTS settle_balance_deduction;

-- [1]设置分隔符为//
DELIMITER //

--[2] 执行source /path/to/procedure.sql;  !!! ERROR
--[2]copy /PATH/TO/procedure.sql to console and enter

--[3]还原分隔符
DELIMITER ;


--[4]调用示例
call settle_balance_deduction(5372539, 1234, 100, @out_status);
select @out_status;

-- 其他常用操作
-- 调研存储过程
call procedure_name(parameters);

-- 查询存储过程
SHOW PROCEDURE STATUS;

-- 显示一个存储过程详情
SHOW CREATE PROCEDURE procedure_name;

-- 删除存储过程
DROP PROCEDURE procedure_name;

存储过程代码

-- 创建存储过程
CREATE PROCEDURE settle_balance_deduction (in_userID bigint, in_planID INT, in_money INT, OUT out_status INT )
BEGIN
  -- 变量 用户实际余额
  DECLARE account_balance INT;

  START TRANSACTION;
  SELECT balance INTO account_balance FROM dsp_settle.user_balance WHERE userId = in_userID AND status = 0 AND isDelete = 0 FOR UPDATE;

  IF account_balance >= in_money THEN
    -- 扣费操作
    UPDATE user_balance SET balance = balance - in_money, opUserId = 1001 WHERE userId = in_userID;

    -- 扣费记录
    INSERT INTO dsp_settle.charge_record(userId, planId, charge, opUserId, addTime) VALUES (in_userID, in_planID, in_money, 1001, NOW());

    -- 提交事务处理
    COMMIT;

    -- 返回结果状态1
    SET out_status = 1;
  ELSE
    -- 余额小于扣费额，不操作
    ROLLBACK;

    -- 返回结果状态0
    SET out_status = 0;
  END IF;
END //

Go调用存储过程代码，亲测可用

package balance

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "strconv"
    "fmt"
)

const (
    //DSN = "root:tongzhen@tcp(127.0.0.1:3306)"
    DSN = "root:123456@tcp(10.48.23.51:8086)"
    DB_DSP = "dsp"
    DB_SETTLE = "dsp_settle"
)

//扣费结算
func Settle(userID int, planID int, charge int)  int{
    db, err := sql.Open("mysql", DSN + "/" + DB_SETTLE)
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    handle, err := db.Prepare("CALL dsp_settle.settle_balance_deduction(?, ?, ?, @out_status)")
    if err != nil {
        panic(err.Error())
    }
    defer handle.Close()

    //call procedure
    var result sql.Result
    result, err = handle.Exec(userID, planID, charge)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(result)

    var sql string = "SELECT @out_status as ret_status"
    selectInstance, err := db.Prepare(sql)
    if err != nil {
        panic(err.Error())
    }
    defer selectInstance.Close()

    var ret_status int
    err = selectInstance.QueryRow().Scan(&ret_status)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(ret_status)
    return ret_status
}

todo 完善该wiki