Golang数据库编程(MySQL)

向右看齐 2023-06-15 11:24 62阅读 0赞

## 1、数据库配置 ##

*  Golang连接数据库用户的驱动是go-sql-driver
 *  安装依赖包

go get -u github.com/go-sql-driver/mysql

## 2、数据库连接 ##

*  导入驱动包

import _ "github.com/go-sql-driver/mysql"

## 3、创建数据库表 ##

CREATE TABLE `product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(16) NOT NULL,
      `price` float NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    INSERT INTO `product` VALUES ('1', 'Rice', '10.11');
    INSERT INTO `product` VALUES ('2', 'Water', '3.33');

## 4、建立连接 ##

db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/golangdemo")

## 5、查询记录 ##

rows, err := db.Query("SELECT * FROM product")
    	var id, name, price string
    	for rows.Next() {
    		rows.Scan(&id, &name, &price)
    		fmt.Println(id, name, price)
    	}
    	rows.Close()

## 6、示例 ##

### 6.1 示例一 ###

package main
    
    import (
    	"database/sql"
    	"fmt"
    )
    
    import _ "github.com/go-sql-driver/mysql"
    
    const (
    	USERNAME = "root"
    	PASSWORD = "123456"
    	NETWORK  = "tcp"
    	SERVER   = "localhost"
    	PORT     = 3306
    	DATABASE = "golangdemo"
    )
    
    func main() {
    
    	//db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/golangdemo")
    	//优雅的写法
    	dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
    	db, err := sql.Open("mysql", dsn)
    
    	if err != nil {
    		fmt.Println(err)
    	}
    
    	ret, _ := db.Exec("INSERT INTO product(name,price) values('meat',99.99)")
    	insID,_ := ret.LastInsertId()
    	fmt.Println(insID)
    
    
    	rows, err := db.Query("SELECT * FROM product")
    
    	var id, name, price string
    
    	for rows.Next() {
    		rows.Scan(&id, &name, &price)
    		fmt.Println(id, name, price)
    
    	}
    	rows.Close()
    
    }

### 6.2 示例二 ###

package main
    
    import (
    	"database/sql"
    	"fmt"
    )
    
    import _ "github.com/go-sql-driver/mysql"
    
    func main() {
    
    	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/golangdemo")
    
    	if err != nil {
    		fmt.Println(err)
    	}
    
    	stmt, _ := db.Prepare("select name,price from product where name =? or price =? ")
    	row4,_ := stmt.Query("meat","19.99")
    
    	var id, name string
    
    	for row4.Next() {
    		row4.Scan(&id, &name)
    		fmt.Println(id, name)
    
    	}
    	row4.Close()
    
    }