准备测试数据

连接MySQL

1	mysql -uroot -proot@12345

选择数据库test

1 2	CREATE DATABASE IF NOT EXISTS `test`; USE `test`;

创建测试用的user表和order表，并插入测试数据

#创建user表
DROP TABLE IF EXISTS `order`;
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (`uid` SERIAL PRIMARY KEY, `name` VARCHAR(20) NOT NULL, `password` VARCHAR(20) NOT NULL) ENGINE=`innodb`, CHARACTER SET=utf8;
#创建order表
CREATE TABLE IF NOT EXISTS `order`(`oid` SERIAL PRIMARY KEY, `uid`  BIGINT(20) UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`uid`) REFERENCES `user`(`uid`))ENGINE=innodb,CHARACTER SET=utf8;
#插入测试数据
INSERT INTO  `user`(`name`,`password`) VALUES('nick', 'nick'),('jacky', 'jacky');
INSERT INTO `order`(`uid`) VALUES(1),(2);

详细操作

 
    root@mysql-5b49484695-28spp:/# mysql -uroot -proot@12345
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 20
    Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE IF NOT EXISTS `test`
Query OK, 1 row affected (0.00 sec)

mysql> USE `test`;
Database changed
mysql> DROP TABLE IF EXISTS `order`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP TABLE IF EXISTS `order`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS `user`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `order`(`oid` SERIAL PRIMARY KEY, `uid`  BIGINT(20) UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`uid`) REFERENCES `user`(`uid`))ENGINE=innodb,CHARACTER SET=utf8;
ERROR 1824 (HY000): Failed to open the referenced table 'user'
mysql> CREATE TABLE IF NOT EXISTS `user` (`uid` SERIAL PRIMARY KEY, `name` VARCHAR(20) NOT NULL, `password` VARCHAR(20) NOT NULL) ENGINE=`innodb`, CHARACTER SET=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `order`(`oid` SERIAL PRIMARY KEY, `uid`  BIGINT(20) UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`uid`) REFERENCES `user`(`uid`))ENGINE=innodb,CHARACTER SET=utf8;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> INSERT INTO  `user`(`name`,`password`) VALUES('nick', 'nick'),('jacky', 'jacky');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `order`(`uid`) VALUES(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

连接数据库

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // 打开连接
    db, err:= sql.Open("mysql", "root:root@12345@tcp(10.0.105.121:30591)/test?charset=utf8")
    // 打开连接  方法是 sql.Open 第一个参数是 数据库类型. 第二个是 用户名:密码@网络协议(ip:port)/需要查询的数据库名
    if err != nil {
        fmt.Println("failed to open database:", err.Error())
        panic(err.Error())
        return
    }
    defer db.Close()

    //尝试连接数据库
    err = db.Ping()
    if err != nil {
        fmt.Println("connect mysql failed：", err.Error())
        panic(err.Error())
        return
    }
    fmt.Println("connect mysql success")

返回的DB对象,实际封装了一个数据库连接池,对于goroutine是线程安全的，可以放心使用。这个数据库连接池由”database/sql”包负责自动创建和回收。连接池的大小可以由SetMaxIdleConns指定。
需要注意的是，创建DB对象成功，并不代表已经成功的连接了数据库，数据库连接只有在真正需要的时候才会被创建。因此如果，在创建DB对象后想验证数据库连接是否有效，可以调用Ping()或者通过

关闭数据库

1	defer db.Close()

关闭数据库并释放全部已打开的资源。实际上，很少需要进行关闭操作，DB对象实际上长期存活并在所有的goroutine之间共享

CRUD

DB中执行SQL通过Exec和Query方法，查询操作是通过Query完成，它会返回一个sql.Rows的结果集，包含一个游标用来遍历查询结果；Exec方法返回的是sql.Result对象，用于检测操作结果，及被影响记录数

查询

// 获取USERS表中的记录
rows, err := db.Query("SELECT * FROM user")
if err != nil {
    fmt.Println("fetech data failed:", err.Error())
    return
}
defer rows.Close()
for rows.Next() {
    var uid int
    var name, password string
    rows.Scan(&uid, &name, &password)
    fmt.Println("uid:", uid, "name:", name, "password:", password)
}

插入

// 插入一条新数据
result, err := db.Exec("INSERT INTO `user`(`name`,`password`) VALUES('tom', 'tom')")
if err != nil {
    fmt.Println("insert data failed:", err.Error())
    return
}
id, err := result.LastInsertId()
if err != nil {
    fmt.Println("fetch last insert id failed:", err.Error())
    return
}
fmt.Println("insert new record", id)

更新

// 更新一条数据
result, err = db.Exec("UPDATE `user` SET `password`=? WHERE `name`=?", "tom_new_password", "tom")
if err != nil {
    fmt.Println("update data failed:", err.Error())
    return
}
num, err := result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("update recors number", num)

删除

// 删除数据
result, err = db.Exec("DELETE FROM `user` WHERE `name`=?", "tom")
if err != nil {
    fmt.Println("delete data failed:", err.Error())
    return
}
num, err = result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("delete record number", num)

完整代码及运行结果

 
    完整代码
    package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // 打开连接
    db, err:= sql.Open("mysql", "root:root@12345@tcp(10.0.105.121:30591)/test?charset=utf8")
    // 打开连接  方法是 sql.Open 第一个参数是 数据库类型. 第二个是 用户名:密码@网络协议(ip:port)/需要查询的数据库名
    if err != nil {
        fmt.Println("failed to open database:", err.Error())
        panic(err.Error())
        return
    }
    defer db.Close()

    // 尝试连接数据库
    err = db.Ping()
    if err != nil {
        fmt.Println("connect mysql failed：", err.Error())
        panic(err.Error())
        return
    }
    fmt.Println("connect mysql success")

    // 获取USERS表中的记录
    rows, err := db.Query("SELECT * FROM user")
    if err != nil {
        fmt.Println("fetech data failed:", err.Error())
        panic(err.Error())
        return
    }
    defer rows.Close()
    for rows.Next() {
        var uid int
        var name, password string
        rows.Scan(&uid, &name, &password)
        fmt.Println("uid:", uid, "name:", name, "password:", password)
    }

    // 插入一条新数据
    result, err := db.Exec("INSERT INTO `user`(`name`,`password`) VALUES('tom', 'tom')")
    if err != nil {
        fmt.Println("insert data failed:", err.Error())
        panic(err.Error())
        return
    }
    id, err := result.LastInsertId()
    if err != nil {
        fmt.Println("fetch last insert id failed:", err.Error())
        panic(err.Error())
        return
    }
    fmt.Println("insert new record", id)

    // 更新一条数据
    result, err = db.Exec("UPDATE `user` SET `password`=? WHERE `name`=?", "tom_new_password", "tom")
    if err != nil {
        fmt.Println("update data failed:", err.Error())
        panic(err.Error())
        return
    }
    num, err := result.RowsAffected()
    if err != nil {
        fmt.Println("fetch row affected failed:", err.Error())
        panic(err.Error())
        return
    }
    fmt.Println("update record number", num)

    // 删除数据
    result, err = db.Exec("DELETE FROM `user` WHERE `name`=?", "tom")
    if err != nil {
        fmt.Println("delete data failed:", err.Error())
        panic(err.Error())
        return
    }
    num, err = result.RowsAffected()
    if err != nil {
        fmt.Println("fetch row affected failed:", err.Error())
        panic(err.Error())
        return
    }
    fmt.Println("delete record number", num)

    # 运行结果
    connect mysql success
    uid: 1 name: nick password: nick
    uid: 2 name: jacky password: jacky
    insert new record 3
    update record number 1
    delete record number 1
    Process finished with exit code 0

参考

1、https://studygolang.com/articles/10629

2、https://tutorialedge.net/golang/golang-mysql-tutorial/