· 7 years ago · Feb 04, 2019, 07:48 AM
1package main
2
3import (
4 "database/sql"
5 "fmt"
6
7 _ "github.com/go-sql-driver/mysql"
8)
9
10func main() {
11 dsn := "root:p4ssw0rd@/test"
12 db, err := sql.Open("mysql", dsn)
13 if err != nil {
14 fmt.Println(err)
15 return
16 }
17 defer db.Close()
18 _, err = db.Exec(`
19CREATE TABLE IF NOT EXISTS tbl1(
20 id INT NOT NULL AUTO_INCREMENT,
21 fld1 INT,
22 fld2 VARCHAR(30),
23 PRIMARY KEY (id)
24)
25 `)
26 if err != nil {
27 fmt.Println(err)
28 return
29 }
30 _, err = db.Exec("INSERT INTO tbl1( fld1, fld2 ) VALUES (1, 'abc'), (2, 'bcd'), (3, 'cde'), (4, 'def'), (5, 'efg')")
31 if err != nil {
32 fmt.Println(err)
33 return
34 }
35 type Flds map[string]sql.RawBytes
36 var rslt []Flds
37 rows, err := db.Query("SELECT *, ROW_NUMBER() OVER() FROM tbl1 WHERE id > 1 AND id < 5")
38 if err != nil {
39 fmt.Println(err)
40 return
41 }
42 defer rows.Close()
43 cols, err := rows.Columns()
44 if err != nil {
45 fmt.Println(err)
46 return
47 }
48 colsLen := len(cols)
49 valsPtr := make([]interface{}, colsLen)
50 colsLen--
51 vals := make([]sql.RawBytes, colsLen)
52 i := 0
53 for i < colsLen {
54 valsPtr[i] = &vals[i]
55 i++
56 }
57 var rowsLen int
58 valsPtr[i] = &rowsLen
59 for rows.Next() {
60 err = rows.Scan(valsPtr...)
61 if err != nil {
62 fmt.Println(err)
63 return
64 }
65 flds := Flds{}
66 for i = 0; i < colsLen; i++ {
67 flds[cols[i]] = vals[i]
68 }
69 if rslt == nil {
70 rslt = make([]Flds, 0, rowsLen)
71 }
72 rslt = append(rslt, flds)
73 }
74 err = rows.Err()
75 if err != nil {
76 fmt.Println(err)
77 return
78 }
79 fmt.Printf("%q\n", rslt)
80}