package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func query_columns(db *sql.DB){
//查询数据,指定字段名,返回sql.Rows结果集
rows, err := db.Query("select ip,port from Ipport limit 20")
if err != nil {
fmt.Printf("select fail [%s]", err)
}
ip := ""
port := 0
for rows.Next() {
rows.Scan(&ip, &port);
fmt.Printf("IP ->[%S], port ->[%S] \n", ip, port)
}
}
func query_columns2(db *sql.DB){
//查询数据,取所有字段
rows2, _ := db.Query("select * from tid_content limit 20")
//返回所有列
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 --> \n", result);
}
func other_func(db *sql.DB){
//查询一行数据
tid := 0
title := ""
rows3 := db.QueryRow("select tid,covertitle, score from tid_content where tid = ?", 17538824);
rows3.Scan(&tid, &title);
fmt.Println(tid, title);
//插入一行数据
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);
//事务处理
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();
}
}
func main() {
db, err := sql.Open("mysql", "user1:6656778@tcp(127.0.0.1:3306)/nga?charset=utf8")
if err != nil {
fmt.Printf("connect mysql fail ! [%s]", err)
} else {
fmt.Println("connect to mysql success")
}
//关闭数据库,db会被多个goroutine共享,可以不调用
defer db.Close()
query_columns(db)
fmt.Println("第一种方式结束---")
query_columns2(db)
}