目录前言:连接数据库sql.DB增删改查插入数据更新数据查询数据删除数据总结前言:本篇文章对如何使用golang连接并操作postgre数据库进行了简要说明。文中使用到的主要工具:DBeaver21、...
目录
前言:连接数据库
sql.DB
增删改查
插入数据
更新数据
查询数据
删除数据
总结
前言:
本篇文章对如何使用golang连接并操作postgre数据库进行了简要说明。文中使用到的主要工具:DBeaver21、VSCode,Golang1.17。
以用户,文章,评论三个表作为例子,下面是数据库建表sql:
CREATE TABLE public.user_info ( u_id serial4 NOT NULL, user_name varchar NULL, create_time date NULL, CONSTRAINT user_info_pk PRIMARY KEY (u_id) ); CREATE TABLE public.user_info ( u_id serial4 NOT NULL, user_name varchar NULL, create_time date NULL, CONSTRAINT user_info_pk PRIMARY KEY (u_id) ); CREATE TABLE public."comment" ( c_id serial4 NOT NULL, "content" varchar NULL, CONSTRAINT comment_pk PRIMARY KEY (c_id) );
连接数据库
github.com/lib/pqpq_sqlpubliccurrentSchema=myschemamyschema.TABLE
package main import ( "database/sql" "fmt" _ "github.com/lib/pq" ) var db *sql.DB func DbOpen() { var err error //参数根据自己的数据库进行修改 db, err = sql.Open("postgres", "host=localhost port=5432 user=angelhand password=2222 dbname=ahdb sslmode=disable") checkError(err) err = db.Ping() checkError(err) }
sql.DB
sql.DB
type DB struct { // Atomic Access only. At top of struct to prevent mis-alignment // on 32-bit platforms. Of type time.Duration. waitDuration int64 // Total time waited for new connections. connector driver.Connector // numClosed is an atomic counter which represents a total number of // closed connections. Stmt.openStmt checks it before cleaning closed // connections in Stmt.css. numClosed uint64 mu sync.Mutex // protects following fields freeConn []*driverConn connRequests map[uint64]chan connRequest nextRequest uint64 // Next key to use in connRequests. numOpen int // number of opened and pending open connections // Used to signal the need for new connections // a goroutine running connectionOpener() reads on this chan and // maybeOpenNewConnections sends on the chan (one send per needed connection) // It is closed during db.Close(). The close tells the connectionOpener // goroutine to exit. openerCh chan struct{} closed bool dep map[finalCloser]depSet lastPut map[*driverConn]string // stacktrace of last conn's put; debug only maxIdleCount int // zero means defaultMaxIdleConns; negative means 0 maxOpen int // <= 0 means unlimited maxLifetime time.Duration // maximum amount of time a connection may be reused maxIdleTime time.Duration // maximum amount of time a connection may be idle before being closed cleanerCh chan struct{} waitCount int64 // Total number of connections waited for. maxIdleClosed int64 // Total number of connections closed due to idle count. maxIdleTimeClosed int64 // Total number of connections closed due to idle time. maxLifetimeClosed int64 // Total number of connections closed due to max connection lifetime limit. stop func() // stop cancels the connection opener. }
sql.DBsql.DBsql.DBOpen()Close()
增删改查
下面代码实现对数据简单的增删改查操作。
插入数据
func insert() { stmt, err := db.Prepare("INSERT INTO user_info(user_name,create_time) VALUES($1,$2)") if err != nil { panic(err) } android res, err := stmt.Exec("ah", time.Now()) if err != nil { panic(err) } fmt.Printf("res = %d", res) }
Exec()sql.ResultresLastInserId() (int64, error)RowsAffected() (int64, error)
更新数据
func update() { stmt, err := db.Prepare("update user_info set user_name=$1 WHERE u_id=$2") if err != nil { panic(err) } res, err := stmt.Exec("angelhand", 1) if err != nil { panic(err) } fmt.Printf("res = %d", res) }
查询数据
结构体如下:
type u struct { id int user_name string create_time time.Time }
接下来是查询的代码
func query() { rows, err := db.Query("select u_id, user_name, create_time from user_info where user_name=$1", "ah") if err != nil { panic(err) } //延迟关闭rows defer rows.Close() for rows.Next() { user := u{} err := rows.Scan(&user.id, &user.user_name, &user.create_time) if err != nil { panic(err) } fmt.Printf("id = %v, name = %v, time = %v\n", user.id, user.user_name, user.create_time) } }
rows.Close()rows.Next()rows.Scan()rows.Next()rows.Scan()
rows.Close()rowsdeferfinallyrowsrows.Close()
db.Query()db.Prepare()
还可以这样来查询单条记录:
err := db.Query("select u_id, user_name, create_time from user_info where user_name=$1", "ah").Scan(&user.user_name)
删除数据
func delete() { stmt, err := db.Prepare("delete from user_info where user_name=$1") if err != nil { panic(err) } res, err := stmt.Exec("angelhand") if err != nil { panic(err) } fmt.Printf("res = %d", res) }
总结
如有侵权,请发邮件到 [email protected]