Golang操作SQL: 增删改查

参考链接：sql增删改查    CRUD

sql编码注意事项

1. 采用占位符的方式，防止sql注入，尽可能避免使用字符串拼接

连接

*tmpDB, err = sql.Open("mysql", connUrl)

查询  

QueryRow()   查询单条    

rowTotal := getDBConn().QueryRow(sqlTotalStr, whereArgs...)
err = rowTotal.Scan(&stRsp.ITotal)
if err != nil {
	log.Errorf("exec sql fail, sqlStr:%s, Args:%v, err:%v", sqlTotalStr, whereArgs, err)
	return "", err
}

Query()      查询多条

// Query()
querySQL := `
		SELECT col1, col2
		FROM table_name
		where
	`
querySQL += whereConds
rows, err := getDBConn().Query(querySQL, whereArgs...)
if err != nil {
	log.Errorf("exec sql success, sqlStr:%s, Args:%v, err:%v", querySQL, whereArgs, err)
	return "", err
}
defer rows.Close() // 关闭

log.Debugf("exec sql success, sqlStr:%s, Args:%v", querySQL, whereArgs)
for rows.Next() { // 遍历
	if err := rows.Scan(&val1, &val2),
	); err != nil {
		log.Error("scan err:", err)
		return "", err
	}
	// deal
}

插入

func insertDB(stReq *proto_admin.AdminSendGuildInviteMsgReq) (err error) {
	insertSql := `
			INSERT INTO table_name (
				col1, col2, col3, 
				col4, col5, col6
			) 
			VALUES (
				?, ?, ?,
				val4, val5, val6
			)
		`
	insertArgs := []interface{}{
		val1,
		val2, 
		val3,
	}
	log.Debugf("exec sql, insertSql:%s, insertArgs:%v", insertSql, insertArgs)
	_, err = getDBconn().Exec(insertSql, insertArgs...) // 参数insertArgs后面必须加...
	if err != nil {
		log.Errorf("exec sql fail, insertSql:%s, insertArgs:%v, err:%v", insertSql, insertArgs, err)
		return err
	}

	return nil
}

更新

func updateDB(stReq *proto_admin.AdminSendGuildInviteMsgReq) (err error) {
	updateSql := `
			UPDATE table_name
			SET
				col1=?, 
				col2=val2, col3=val3, col4=val4
			where 
				col5=? and col6=? and col7=?
		`
	updateArgs := []interface{}{
		val1, 
		val5, 
		val6, 
		val7,
	}
	log.Debugf("exec sql, updateSql:%s, updateArgs:%v", updateSql, updateArgs)
	_, err = getDBconn().Exec(updateSql, updateArgs...)
	if err != nil {
		log.Errorf("exec sql fail, updateSql:%s, updateArgs:%v, err:%v", updateSql, updateArgs, err)
		return err
	}

	return nil
}

构造条件

// 构造查询语句、查询条件
func genWhereForQyin(req *Request) (string, []interface{}, error) {
	var (
		whereConds = make([]string, 0)
		whereArgs  = make([]interface{}, 0)
	)

	if req.colval1 != "" {
		whereConds = append(whereConds, "col like CONCAT('%', ?, '%')") // 模糊查询like
		whereArgs = append(whereArgs, req.colval1)
	}

	if req.colval2 != 0 {
		whereConds = append(whereConds, "col2=?")
		whereArgs = append(whereArgs, colval2)
	}

	return strings.Join(whereConds, " and "), whereArgs, nil
}

func test() {
	// 构造: 查询条件
	whereConds, whereArgs, err := genWhereForQyin(req)
	if err != nil {
		log.Errorf("genWhereForQyin fail, err%v", err)
		return "", err
	}

	// 查询语句
	sqlTotalStr := `
			SELECT COUNT(*) 
			FROM table_name 
			where
		`
	sqlTotalStr += whereConds
	rowTotal := getDBConn().QueryRow(sqlTotalStr, whereArgs...)
	err = rowTotal.Scan(&total)
	if err != nil {
		log.Errorf("exec sql fail, sqlStr:%s, Args:%v, err:%v", sqlTotalStr, whereArgs, err)
		return "", err
	}
}