go 通过sql操作mysql

﹏ヽ暗。殇╰゛Y 2024-04-08 08:52 51阅读 0赞

> > **GoLang学习更方便，一文在手，面试我有**  
> > ![30adef0f7f31e917f38a041312d42413.png_t_M666][] [GoLang面试][GoLang] | [GoLang面试][GoLang] | [GoLang面试][GoLang]

--------------------

go 通过sql操作mysql

`亲测、2022/08/15 20:53 北京朝阳`

#### 文章目录 ####

*   *  前言
     *  go代码
     *  sql
     *  汇总-增删改查

### 前言 ###

表格

![在这里插入图片描述][91310ee7ebfe464ca7794bb0183f095e.png]

结果

![在这里插入图片描述][dfa2b657bf3c4e7797215d3c83d373b2.png]

### go代码 ###

package main
    
    import (
    	"database/sql"
    	"fmt"
    	"time"
    
    	_ "github.com/go-sql-driver/mysql"
    )
    
    var db *sql.DB   //全局变量client
    
    func initMySQL() (err error) {
        
    	dsn := "root:123456@tcp(127.0.0.1:3306)/test"
    	db, err = sql.Open("mysql", dsn)
    	if err != nil {
        
    		panic(err)
    	}
    	err = db.Ping() //检测是否连接成功
    	if err != nil {
        
    		return
    	}
    	db.SetMaxOpenConns(200)                 //最大连接数
    	db.SetMaxIdleConns(10)                  //连接池里最大空闲连接数。必须要比maxOpenConns小
    	db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
    	db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
    	return
    }
    
    func main() {
        
    	if err := initMySQL(); err != nil {
        
    		fmt.Printf("connect to db failed,err:%v\n", err)
    	} else {
        
    		fmt.Println("connect to db success")
    	}
    
    	sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
    	var u user
    	//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
    	if err != nil {
        
    		fmt.Printf("scan failed， err: %v\n", err)
    		return
    	}
    	fmt.Printf("id:%d,name:%s,age:%d\n", u.id, u.name)
    
    	defer db.Close()
    
    }
    
    //user结构体
    type user struct {
        
    	id int
    	name string
    }

### sql ###

`database: test`

CREATE TABLE `sys_user` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

### 汇总-增删改查 ###

结果：

![在这里插入图片描述][b46a714acf8f4cc8948e758bec151dfb.png]

package main
    
    import (
    	"database/sql"
    	"fmt"
    	"time"
    
    	_ "github.com/go-sql-driver/mysql"
    )
    
    var db *sql.DB
    
    func initMySQL() (err error) {
        
    	dsn := "root:1234qwer@tcp(bj-cdb-mw08tjgs.sql.tencentcdb.com:60042)/test"
    	db, err = sql.Open("mysql", dsn)
    	if err != nil {
        
    		panic(err)
    	}
    	err = db.Ping()
    	if err != nil {
        
    		return
    	}
    	db.SetMaxOpenConns(200)                 //最大连接数
    	db.SetMaxIdleConns(10)                  //连接池里最大空闲连接数。必须要比maxOpenConns小
    	db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
    	db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
    	return
    }
    
    func main() {
        
    	if err := initMySQL(); err != nil {
        
    		fmt.Printf("connect to db failed,err:%v\n", err)
    	} else {
        
    		fmt.Println("connect to db success")
    	}
    	select_one()
    	queryMultiRowDemo()
    	insertRowDemo()
    	updateRowDemo()
    	deleteRowDemo()
    	defer db.Close()
    
    }
    
    type user struct {
        
    	id int
    	// age  int
    	name string
    }
    
    //查询
    func select_one() {
        
    	sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
    	var u user
    	//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
    	if err != nil {
        
    		fmt.Printf("scan failed， err: %v\n", err)
    		return
    	}
    	fmt.Printf("id:%d,name:%s\n", u.id, u.name)
    }
    
    // 查询多条数据示例
    func queryMultiRowDemo() {
        
    	sqlStr := "SELECT id,name FROM sys_user WHERE id>?"
    	var u user
    	rows, err := db.Query(sqlStr, 0)
    	if err != nil {
        
    		fmt.Printf("query failed, err:%v\n", err)
    		return
    	}
    	//非常重要，关闭rows释放持有的数据库链接
    	defer rows.Close()
    	for rows.Next() {
        
    		err := rows.Scan(&u.id, &u.name)
    		if err != nil {
        
    			fmt.Printf("scan failed, err: %v\n", err)
    			return
    		}
    		fmt.Printf("id:%d,name:%s\n", u.id, u.name)
    	}
    }
    
    //插入数据
    func insertRowDemo() {
        
    	sqlStr := "INSERT INTO sys_user(id,name) VALUES(?,?)"
    	ret, err := db.Exec(sqlStr, 23, "javapub")
    	if err != nil {
        
    		fmt.Printf("insert failed, err:%v\n", err)
    		return
    	}
    	var theID int64
    	theID, err = ret.LastInsertId()
    	if err != nil {
        
    		fmt.Printf("get lastinsert ID failed,err:%v\n", err)
    		return
    	}
    	fmt.Printf("insert success, the id is %d\n", theID)
    }
    
    //更新数据
    func updateRowDemo() {
        
    	sqlStr := "UPDATE sys_user SET name=? WHERE id=?"
    	ret, err := db.Exec(sqlStr, "i love javapub", 23)
    	if err != nil {
        
    		fmt.Printf("updated failed,err:%v\n", err)
    		return
    	}
    	var n int64
    	n, err = ret.RowsAffected()
    	if err != nil {
        
    		fmt.Printf("get rowsAffected failed,err:%v\b", err)
    		return
    	}
    	fmt.Printf("updated success, the rows affected is %d\n", n)
    }
    
    //删除数据
    func deleteRowDemo() {
        
    	sqlStr := "DELETE FROM sys_user WHERE id=?"
    	ret, err := db.Exec(sqlStr, 1)
    	if err != nil {
        
    		fmt.Printf("delete failed,err:%v\n", err)
    		return
    	}
    	var n int64
    	n, err = ret.RowsAffected()
    	if err != nil {
        
    		fmt.Printf("get RowsAffected failed, err:%v\n", err)
    		return
    	}
    	fmt.Printf("delete success, affected rows:%d\n", n)
    }

--------------------

![f0dfc15b686e4c18bfcbddc702464327.gif_pic_center][]

回复 golang，获取学习路线思维导图、加入GO计划学习

[30adef0f7f31e917f38a041312d42413.png_t_M666]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/72b47d1d5a45404589d9ae7f658f9da7.png
[GoLang]: https://javapub.blog.csdn.net/category_11938137.html
[91310ee7ebfe464ca7794bb0183f095e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/dcaf0e84c0a44a96aef957a6cd81fe6a.png
[dfa2b657bf3c4e7797215d3c83d373b2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/e6de35cb952f4542ae89ab687dc0a2ee.png
[b46a714acf8f4cc8948e758bec151dfb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/82642eae1e8b46a8bd243ac1fe86548e.png
[f0dfc15b686e4c18bfcbddc702464327.gif_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/350d6fae813249068459ec1dce428f33.gif