研究了很久，发现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
}
有疑问加站长微信联系（非本文作者）
