[js go]go连接mysql

下载数据库驱动

类似 npm install

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

引入数据库接口和数据库驱动

import (
	"fmt"
	"database/sql" // 类SQL数据库的泛用接口
	_ "github.com/go-sql-driver/mysql" // go get -u github.com/go-sql-driver/mysql 下载, 注入数据库驱动
)

打开数据库

	// DSN:Data Source Name
	dsn := "userName:password@tcp(ip:port)/database_name"
	db, err := sql.Open("mysql", dsn) // 使用mysql数据库驱动
	if err != nil {
		panic(err)
	}
   	defer db.Close()
	err = db.Ping()
	if err != nil {
		panic(err) 
	} else {
		fmt.Printf("db %v \n", db) // db &{0 0xc000130018 0 {0 0} [0xc000148120] map[] 0 1 0xc00010e0c0 false map[0xc000148120:map[0xc000148120:true]] map[] 0 0 0 0 <nil> 0 0 0 0 0x10ccd20}
	}

单行查询

// 定义结构体
type user struct {
	id   int
	age  int
	name string
}

	sqlStr := "select * from user where id=?"
	var u user
	// 单行查询
	err = db.QueryRow(sqlStr, 6).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Printf("scan err %v \n", err) // scan err sql: no rows in result set 
	} else {
		fmt.Printf("id: %d  name: %s  age: %d \n", u.id, u.name, u.age) // id: 5  name: hello  age: 18 
	}

多行查询

	sqlStr = "select id, name, age from user where id > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v \n", err)
	} else {
		defer rows.Close() // 关闭rows持有的数据库链接
		for rows.Next() {	
			var u user
			err := rows.Scan(&u.id, &u.name, &u.age)
			if err != nil {
				fmt.Printf("scan failed, err:%v \n", err)
			} else {
				fmt.Printf("id:%d name:%s age:%d \n", u.id, u.name, u.age)
			}
		}
	}

增create

新增一个即将被输出到社会的前端老哥

	sqlStr = "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "前端老哥", 35)
	if err != nil {
		fmt.Printf("insert failed, err:%v \n", err)
	} else {
		lastId, err := ret.LastInsertId() // 新插入数据的id
		if err != nil {
			fmt.Printf("get lastinsert id failed, err:%v \n", err)
		} else {
			fmt.Printf("insert success, the last id is %d. \n", lastId)
		}
	}

改update

让前端老哥重返十八岁, 爷曾轻

	sqlStr = "update user set age=? where age = ?"
	ret, err = db.Exec(sqlStr, 18, 35)
	if err != nil {
		fmt.Printf("update failed, err:%v \n", err)
	} else {
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v \n", err)
		} else {
			fmt.Printf("update success, affected rows:%d \n", n)
		}
	}

删delete

真的要保持危机意识, 学不动是不可能的

	sqlStr = "delete from user where age > ?"
	ret, err = db.Exec(sqlStr, 35)
	if err != nil {
		fmt.Printf("delete failed, err:%v \n", err)
	} else {
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v \n", err)
		} else {
			fmt.Printf("delete success, affected rows:%d \n", n)
		}
	}

附阿里云ECS安装mysql

centOS更新yum源

$ rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

安装mysql

$ yum -y install mysql-community-server

如果报错No match for argument, 可先禁用默认mysql模块, 再安装

$ yum module disable mysql
$ yum -y install mysql-community-server

查看mysql版本

$ mysql -V

启动mysql守护进程

$ systemctl start mysqld

查看mysql初始密码

$ grep "password" /var/log/mysqld.log

配置mysql安全性

mysql_secure_installation

接下来设置mysql可远程访问, 以及阿里云安全组添加mysql端口可访问, 具体可自行google

可参考 segmentfault.com/a/119000001…