原文链接： Go语言 sql增删改查

获取所有列

rows, err := db.Query("SELECT * FROM Players")
	defer rows.Close()
	if err != nil {
		fmt.Println(err, "ruh roh")
	}
	for rows.Next() {
		columns, err := rows.Columns()
		if err != nil {
			fmt.Print(err)
			break
		}
		for _, c := range columns {
			fmt.Println(rows.Scan(&c))
		}
}

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    insert()
}
func insert() {
    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")
    checkErr(err)
    cr_table := "create table if not exists user(user_id int auto_increment primary key, user_name varchar(128),user_age int,user_sex int not null default 0)"
    stmt1, err := db.Prepare(cr_table)
    checkErr(err)
    _, err = stmt1.Exec()
    checkErr(err)
    stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
    checkErr(err)
    res, err := stmt.Exec("费大幅", 20, 1)
    checkErr(err)
    id, err := res.LastInsertId()
    checkErr(err)
    fmt.Println(id)
    //下面取消注释进入查询
    //query()
}
func query() {
    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")
    checkErr(err)
    rows, err := db.Query("SELECT * FROM user where user_name ='费大幅'")
    checkErr(err)
    for rows.Next() {
        var userId int
        var userName string
        var userAge int
        var userSex int
        rows.Columns()
        err = rows.Scan(&userId, &userName, &userAge, &userSex)
        checkErr(err)
        fmt.Println(userId, userName, userAge, userSex)
    }
    //下面取消注释进入更改
    //update()
}
func update() {
    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")
    checkErr(err)
    stmt, err := db.Prepare(`UPDATE user SET user_name=?,user_age=?,user_sex=? WHERE user_id=?`)
    checkErr(err)
    res, err := stmt.Exec("胖大海", 18, 3, 10)
    checkErr(err)
    num, err := res.RowsAffected()
    checkErr(err)
    fmt.Println(num)
    //下面取消注释进入删除
    //remove()
}
func check() {
    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")
    checkErr(err)
    rows, err := db.Query("SELECT * FROM user")
    checkErr(err)
    for rows.Next() {
        var userId, userAge, userSex int
        var userName string
        rows.Columns()
        err = rows.Scan(&userId, &userName, &userAge, &userSex)
        checkErr(err)
        fmt.Println(userId, userName, userAge, userSex, "update")
    }
}
func remove() {
    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")
    checkErr(err)
    check()
    stmt, err := db.Prepare(`DELETE FROM user WHERE user_name=?`)
    checkErr(err)
    res, err := stmt.Exec("sony")
    checkErr(err)
    num, err := res.RowsAffected()
    checkErr(err)
    fmt.Println(num)
    check()
}
func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

demo2

package main;

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

func main() {
    //打开数据库
    //DSN数据源字符串：用户名:密码@协议(地址:端口)/数据库?参数=参数值
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?charset=utf8");
    if err != nil {
        fmt.Println(err);
    }

    //关闭数据库，db会被多个goroutine共享，可以不调用
    defer db.Close();

    //查询数据，指定字段名，返回sql.Rows结果集
    rows, _ := db.Query("select id,name from test");
    id := 0;
    name := "";
    for rows.Next() {
        rows.Scan(&id, &name);
        fmt.Println(id, name);
    }

    //查询数据，取所有字段
    rows2, _ := db.Query("select * from test");
    //返回所有列
    cols, _ := rows2.Columns();
    //这里表示一行所有列的值，用[]byte表示
    vals := make([][]byte, len(cols));
    //这里表示一行填充数据
    scans := make([]interface{}, len(cols));
    //这里scans引用vals，把数据填充到[]byte里
    for k, _ := range vals {
        scans[k] = &vals[k];
    }

    i := 0;
    result := make(map[int]map[string]string);
    for rows2.Next() {
        //填充数据
        rows2.Scan(scans...);
        //每行数据
        row := make(map[string]string);
        //把vals中的数据复制到row中
        for k, v := range vals {
            key := cols[k];
            //这里把[]byte数据转成string
            row[key] = string(v);
        }
        //放入结果集
        result[i] = row;
        i++;
    }
    fmt.Println(result);

    //查询一行数据
    rows3 := db.QueryRow("select id,name from test where id = ?", 1);
    rows3.Scan(&id, &name);
    fmt.Println(id, name);

    //插入一行数据
    ret, _ := db.Exec("insert into test(id,name) values(null, '444')");
    //获取插入ID
    ins_id, _ := ret.LastInsertId();
    fmt.Println(ins_id);

    //更新数据
    ret2, _ := db.Exec("update test set name = '000' where id > ?", 2);
    //获取影响行数
    aff_nums, _ := ret2.RowsAffected();
    fmt.Println(aff_nums);

    //删除数据
    ret3, _ := db.Exec("delete from test where id = ?", 3);
    //获取影响行数
    del_nums, _ := ret3.RowsAffected();
    fmt.Println(del_nums);

    //预处理语句
    stmt, _ := db.Prepare("select id,name from test where id = ?");
    rows4, _ := stmt.Query(3);
    //注意这里需要Next()下，不然下面取不到值
    rows4.Next();
    rows4.Scan(&id, &name);
    fmt.Println(id, name);

    stmt2, _ := db.Prepare("insert into test values(null, ?, ?)");
    rows5, _ := stmt2.Exec("666", 66);
    fmt.Println(rows5.RowsAffected());

    //事务处理
    tx, _ := db.Begin();

    ret4, _ := tx.Exec("update test set price = price + 100 where id = ?", 1);
    ret5, _ := tx.Exec("update test set price = price - 100 where id = ?", 2);
    upd_nums1, _ := ret4.RowsAffected();
    upd_nums2, _ := ret5.RowsAffected();

    if upd_nums1 > 0 && upd_nums2 > 0 {
        //只有两条更新同时成功，那么才提交
        tx.Commit();
    } else {
        //否则回滚
        tx.Rollback();
    }
}