golang操作mysql数据库示例代码

sban · · 4815 次点击 · · 开始浏览

这是一个创建于的文章，其中的信息可能已经有所发展或是发生改变。

使用了github.com/go-sql-driver/mysql驱动。go在database/sql中实现了连接池，所以每次操作数据库，重复打开关闭即可。

 1 package main 
 2 
 3 import(
 4     "database/sql"
 5     _ "github.com/go-sql-driver/mysql"
 6     "fmt"
 7 )
 8 
 9 type User struct{
10     Id    int
11     UserName string
12     Passwd string
13     Number int 
14 }
15 
16 func main() {
17     db,err := sql.Open("mysql","root:sban@/test")
18     if err != nil {
19         panic(err.Error())
20     }
21     defer db.Close()
22 
23     err = db.Ping()
24     if err != nil {
25         panic(err.Error())
26     }
27 
28     stmtIns,err := db.Prepare("insert into user(`username`,`passwd`,`number`) values(?,?,?)")
29     if err != nil {
30         panic(err.Error())
31     }
32     defer stmtIns.Close()
33 
34     result,err := stmtIns.Exec("user1","passwd1",1)
35     if err != nil {
36         panic(err.Error())
37     }
38     m, err := result.LastInsertId()
39     n, err := result.RowsAffected()
40     fmt.Println("row1 LastInsertId, RowsAffected",m,n)
41     result,err = stmtIns.Exec("user2","passwd2",2)
42     if err != nil {
43         panic(err.Error())
44     }
45     m, err = result.LastInsertId()
46     n, err = result.RowsAffected()
47     fmt.Println("row2 LastInsertId, RowsAffected",m,n)
48 
49     var u User
50     stmtOut, err := db.Prepare("select `id`,`username`,`passwd`,`number` from user where number=? order by id desc")
51     err = stmtOut.QueryRow(1).Scan(&u.Id,&u.UserName,&u.Passwd,&u.Number)
52     if err != nil {
53         panic(err.Error())
54     }
55     fmt.Println(u)
56 
57     err = stmtOut.QueryRow(2).Scan(&u.Id,&u.UserName,&u.Passwd,&u.Number)
58     if err != nil {
59         panic(err.Error())
60     }
61     fmt.Println(u)
62 
63     rows, err := db.Query("select `username`,`passwd` from user order by id desc")
64     columns, err:= rows.Columns()
65     fmt.Println("cols 4", len(columns))
66 
67      values := make([]sql.RawBytes, len(columns))
68     scanArgs := make([]interface{},len(columns))
69     for i := range values {
70         scanArgs[i] = &values[i]
71     }
72 
73     for rows.Next() {
74         err = rows.Scan(scanArgs...)
75 
76         for i, col := range values {
77             fmt.Println(columns[i], string(col))
78         }
79         fmt.Println("-----------------------------------")
80     }
81 }