1.连接
| package main |
| |
| import ( |
| "database/sql" |
| "fmt" |
| _ "github.com/go-sql-driver/mysql" |
| "log" |
| "time" |
| ) |
| |
| func main() { |
| db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?parseTime=true&charset=utf8&loc=Local") |
| checkErr(err) |
| |
| defer db.Close() |
| |
| err = db.Ping() |
| checkErr(err) |
| |
| db.SetMaxIdleConns(10) |
| db.SetMaxOpenConns(100) |
| db.SetConnMaxLifetime(time.Second * 600) |
| |
| |
| _, err = db.Exec("create table if not exists test.hello(world varchar(50))") |
| checkErr(err) |
| |
| |
| |
| res, err := db.Exec("insert into test.hello(world) values('hello world-3')") |
| checkErr(err) |
| rowsCnt, _ := res.RowsAffected() |
| recordId, err := res.LastInsertId() |
| checkErr(err) |
| log.Printf("inserted %d rows, record-id: %d", rowsCnt, recordId) |
| |
| |
| |
| |
| rows, err := db.Query("select world from test.hello") |
| checkErr(err) |
| for rows.Next() { |
| var s string |
| err = rows.Scan(&s) |
| checkErr(err) |
| log.Printf("found row containing %q", s) |
| } |
| rows.Close() |
| } |
| |
| func checkErr(err error) { |
| if err != nil { |
| fmt.Println(err) |
| return |
| } |
| } |
2.CRUD
| package main |
| |
| import ( |
| "database/sql" |
| "fmt" |
| _ "github.com/go-sql-driver/mysql" |
| "log" |
| "time" |
| ) |
| |
| func main() { |
| |
| db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?charset=utf8&parseTime=true&loc=Local") |
| checkErr(err) |
| |
| err = db.Ping() |
| checkErr(err) |
| |
| db.SetMaxOpenConns(100) |
| db.SetMaxIdleConns(10) |
| db.SetConnMaxLifetime(time.Second * 60) |
| |
| |
| |
| rows, err := db.Query("select world from test.hello") |
| checkErr(err) |
| defer rows.Close() |
| for rows.Next() { |
| var s string |
| err = rows.Scan(&s) |
| checkErr(err) |
| log.Printf("found row containing %q", s) |
| } |
| rows.Close() |
| if err = rows.Err(); err != nil { |
| log.Fatal(err) |
| } |
| |
| |
| var s string |
| |
| err = db.QueryRow("select world from test.hello limit 1").Scan(&s) |
| if err != nil { |
| if err == sql.ErrNoRows { |
| log.Println("there is no row") |
| } else { |
| log.Fatal(err) |
| } |
| } |
| log.Printf("found a row: %q", s) |
| |
| |
| var world sql.NullString |
| err = db.QueryRow("select world from hello where id = ?", 2).Scan(&world) |
| if world.Valid { |
| |
| } else { |
| |
| } |
| |
| |
| _, err = db.Exec("create table if not exists `user` (`id` int auto_increment primary key, `user` varchar(20))") |
| checkErr(err) |
| |
| _, err = db.Exec("insert into `user` (`user`) values ('Nancy'), ('Lily'), ('Mary')") |
| |
| rows, err = db.Query("select * from user") |
| checkErr(err) |
| cols, err := rows.Columns() |
| checkErr(err) |
| fmt.Println(cols) |
| vals := make([][]byte, len(cols)) |
| scans := make([]interface{}, len(cols)) |
| |
| for i := range vals { |
| scans[i] = &vals[i] |
| } |
| var results []map[string]string |
| |
| for rows.Next() { |
| err = rows.Scan(scans...) |
| checkErr(err) |
| row := make(map[string]string) |
| for k, v := range vals { |
| key := cols[k] |
| row[key] = string(v) |
| } |
| results = append(results, row) |
| } |
| for k, v := range results { |
| fmt.Println(k, v) |
| } |
| |
| |
| |
| res, err := db.Exec("insert into `user` (`user`) values ('Maria')") |
| checkErr(err) |
| affectedRows, err := res.RowsAffected() |
| lastId, err := res.LastInsertId() |
| if lastId > 0 || affectedRows != 0 { |
| fmt.Printf("insert data successful, id: %d, affected rows: %d", lastId, affectedRows) |
| } |
| } |
| |
| func checkErr(err error) { |
| if err != nil { |
| log.Fatal(err) |
| } |
| } |
3.Prepare
| package main |
| |
| import ( |
| "database/sql" |
| "fmt" |
| _ "github.com/go-sql-driver/mysql" |
| "log" |
| "time" |
| ) |
| |
| func main() { |
| |
| db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?charset=utf8&parseTime=true&loc=Local") |
| checkErr(err) |
| |
| err = db.Ping() |
| checkErr(err) |
| |
| db.SetMaxOpenConns(100) |
| db.SetMaxIdleConns(10) |
| db.SetConnMaxLifetime(time.Second * 60) |
| |
| |
| |
| |
| |
| |
| rows, err := db.Query("select * from user where id = 3") |
| |
| rows, err = db.Query("select * from user where id = ?", 3) |
| |
| |
| stmt, err := db.Prepare("select user from user where id = ?") |
| checkErr(err) |
| defer stmt.Close() |
| rows, err = stmt.Query(3) |
| checkErr(err) |
| var val string |
| for rows.Next() { |
| rows.Scan(&val) |
| fmt.Println(val) |
| } |
| } |
| |
| func checkErr(err error) { |
| if err != nil { |
| log.Fatal(err) |
| } |
| } |
| |
4.事务
| package main |
| |
| import ( |
| "database/sql" |
| "fmt" |
| _ "github.com/go-sql-driver/mysql" |
| "log" |
| "time" |
| ) |
| |
| func main() { |
| |
| db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?charset=utf8&parseTime=true&loc=Local") |
| checkErr(err) |
| |
| err = db.Ping() |
| checkErr(err) |
| |
| db.SetMaxOpenConns(100) |
| db.SetMaxIdleConns(10) |
| db.SetConnMaxLifetime(time.Second * 60) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| tx, err := db.Begin() |
| checkErr(err) |
| defer clearTransaction(tx) |
| |
| res, err := tx.Exec("update `user` set gold = 50 where user = Maria") |
| checkErr(err) |
| rowsAffected, err := res.RowsAffected() |
| checkErr(err) |
| fmt.Println(rowsAffected) |
| |
| res, err = tx.Exec("update `user` set gold = 150 where id = 2") |
| checkErr(err) |
| |
| if err = tx.Commit(); err != nil { |
| log.Fatal(err) |
| } |
| } |
| |
| func checkErr(err error) { |
| if err != nil { |
| log.Fatal(err) |
| } |
| } |
| |
| func clearTransaction(tx *sql.Tx) { |
| err := tx.Rollback() |
| if err != sql.ErrTxDone && err != nil { |
| log.Fatal(err) |
| } |
| } |
| |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤