golang中对于调用mysql 存储过程的问题解决思路

研究了很久，发现mysql最流程的那个库（github.com/go-sql-driver/mysql）怎么都解决不了调用存储过程的问题，最后找到另外一个库github.com/ziutek/mymysql/mysql，问题终于得以解决。不过mymysql似乎也有个小问题，调用存储过程的时候，似乎不能先Prepare。

详见：https://github.com/yudeguang/mysql/blob/master/db.go#L424

/* 执行存储过程,传入一个切片的指针，结果被返回到该切片中；

暂不支持Prepare,需自行拼接SQL.

结构体字段顺序必须与查询列顺序一致.

函数只返回第一个结果集，如果想处理多个结果集，调用MySqlStruct.Mymysqldb自行处理.

原始SQL:

call pro_get_students("男",12)

CallProcedure:

type student struct {

    Id   int
    Name string
    Age  int

}

var result []student

err = CallProcedure(&result, call pro_get_students("男",12))

*/

调用mymysql连接上数据可后，再调用如下函数CallProcedure:

func (m *MySqlStruct) CallProcedure(intoResultPtr interface{}, query string) error {

if !strings.HasPrefix(strings.TrimSpace(strings.ToLower(query)), `call `) {
    return fmt.Errorf(fmt.Sprintf("there is a err in your query: %v .", query))
}
//先判断传入的数据是否是指针,now the value shoule be: *[]interface{},top kind is a ptr
refValue := reflect.ValueOf(intoResultPtr)
if refValue.Kind() != reflect.Ptr { //&& refValue.IsNil()
    return errors.New("the first argument resultPtr must be a pointer,not a value.")
}
//再判断下一级是否是切片,now dirValue shoule be: []interface{},top kind is a slice
dirValue := reflect.Indirect(refValue)
if dirValue.Kind() != reflect.Slice {
    return errors.New("the first argument resultPtr must be a slice.")
}
//判断切片是否为空
if l := dirValue.Len(); l != 0 {
    return errors.New(fmt.Sprintf("the first argument resultPtr has %v records,and it's must be empty.", l))
}
// 再判断切片元素类型，只支持int,int8...等基础类型以及结构体.
// 其它类型则诸如:Uintptr,Complex64,Complex128,Array,Chan,
// Func,Interface,Map,Ptr,Slice,UnsafePointer,直接报错返回.
itemNum := 1
IsBaseType := false
structElem := reflect.Value{}
structObject := reflect.New(dirValue.Type().Elem())
arrayObject := reflect.MakeSlice(dirValue.Type(), 0, 0)
switch dirValue.Type().Elem().Kind() {
case reflect.Bool,
    reflect.Int,
    reflect.Int8,
    reflect.Int16,
    reflect.Int32,
    reflect.Int64,
    reflect.Uint,
    reflect.Uint8,
    reflect.Uint16,
    reflect.Uint32,
    reflect.Uint64,
    reflect.Float32,
    reflect.Float64,
    reflect.String:
    IsBaseType = true
case reflect.Struct:
    //判断结构体中字段的数字母是否是大写，因为反射只在大写情况才起作用
    structElem = structObject.Elem()
    itemNum = structElem.NumField()
    for i := 0; i < itemNum; i++ {
        if !structElem.Field(i).CanSet() {
            fieldName := structElem.Type().Field(i).Name
            intoResultPtrName := dirValue.Type().Elem().Name()
            return errors.New(fmt.Sprintf("the field name %v.%v should be %v.%v,because the first letter is capitalized can be exported in reflect.",
                intoResultPtrName, fieldName, intoResultPtrName, strings.Title(fieldName)))
        }
    }
default:
    return errors.New("the first argument resultPtr is not a support type.")
}

res, err := m.Mymysqldb.Start(query)
if err != nil {
    return err
}
i := 0
for !res.StatusOnly() {
    rows, err := res.GetRows()
    if i == 0 {
        lenColumns := len(res.Fields())
        //判断两方元素数量是否一样多
        if itemNum != lenColumns {
            return errors.New(fmt.Sprintf("intoResultPtr fileds num %v doesn't mutch columns num %v from database.", itemNum, lenColumns))
        }
        for _, row := range rows {
            oneRowPtr := make([]interface{}, itemNum)
            //实例化oneRowPtr
            for i := 0; i < itemNum; i++ {
                if IsBaseType {
                    oneRowPtr[i] = structObject.Interface()
                } else {
                    oneRowPtr[i] = structElem.Field(i).Addr().Interface()
                }
                err := convertAssign(oneRowPtr[i], row[i])
                if err != nil {
                    fieldName := structElem.Type().Field(i).Name
                    names := []string{}
                    for ii := 0; ii < lenColumns; ii++ {
                        names = append(names, res.Fields()[ii].Name)
                    }
                    columnName := names[i]
                    return errors.New(fmt.Sprintf("intoResultPtr %vth fileds %v doesn't mutch database %vth column %v or %v.", i, fieldName, i, columnName, err))
                }
            }
            arrayObject = reflect.Append(arrayObject, structObject.Elem())
        }
    }
    //剩余的不要了,但是也必须遍历，不然程序会报如下错误:reply is not completely read
    //第一个返回值竟然还不能忽略
    res, err = res.NextResult()
    if err != nil {
        return err
    }
    i++
}

dirValue.Set(arrayObject)
return nil

}