123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153
| package mainimport ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "time")//数据库连接信息const ( USERNAME = "root" PASSWORD = "123456" NETWORK = "tcp" SERVER = "127.0.0.1" PORT = 3306 DATABASE = "test")//user表结构体定义type User struct { Id int `json:"id" form:"id"` Username string `json:"username" form:"username"` Password string `json:"password" form:"password"` Status int `json:"status" form:"status"` // 0 正常状态, 1删除 Createtime int64 `json:"createtime" form:"createtime"`}func main() { conn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE) DB, err := sql.Open("mysql", conn) if err != nil { fmt.Println("connection to mysql failed:", err) return } DB.SetConnMaxLifetime(100*time.Second) //最大连接周期,超时的连接就close DB.SetMaxOpenConns(100) //设置最大连接数 CreateTable(DB) InsertData(DB) QueryOne(DB) QueryMulti(DB) UpdateData(DB) DeleteData(DB)}func CreateTable(DB *sql.DB) { sql := `CREATE TABLE IF NOT EXISTS users( id INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL, username VARCHAR(64), password VARCHAR(64), status INT(4), createtime INT(10) ); ` if _, err := DB.Exec(sql); err != nil { fmt.Println("create table failed:", err) return } fmt.Println("create table successd")}//插入数据func InsertData(DB *sql.DB) { result,err := DB.Exec("insert INTO users(username,password) values(?,?)","test","123456") if err != nil{ fmt.Printf("Insert data failed,err:%v", err) return } lastInsertID,err := result.LastInsertId() //获取插入数据的自增ID if err != nil { fmt.Printf("Get insert id failed,err:%v", err) return } fmt.Println("Insert data id:", lastInsertID) rowsaffected,err := result.RowsAffected() //通过RowsAffected获取受影响的行数 if err != nil { fmt.Printf("Get RowsAffected failed,err:%v",err) return } fmt.Println("Affected rows:", rowsaffected)}//查询单行func QueryOne(DB *sql.DB) { user := new(User) //用new()函数初始化一个结构体对象 row := DB.QueryRow("select id,username,password from users where id=?", 1) //row.scan中的字段必须是按照数据库存入字段的顺序,否则报错 if err := row.Scan(&user.Id,&user.Username,&user.Password); err != nil { fmt.Printf("scan failed, err:%v\n", err) return } fmt.Println("Single row data:", *user)}//查询多行func QueryMulti(DB *sql.DB) { user := new(User) rows, err := DB.Query("select id,username,password from users where id = ?", 2) defer func() { if rows != nil { rows.Close() //关闭掉未scan的sql连接 } }() if err != nil { fmt.Printf("Query failed,err:%v\n", err) return } for rows.Next() { err = rows.Scan(&user.Id, &user.Username, &user.Password) //不scan会导致连接不释放 if err != nil { fmt.Printf("Scan failed,err:%v\n", err) return } fmt.Println("scan successd:", *user) }}//更新数据func UpdateData(DB *sql.DB){ result,err := DB.Exec("UPDATE users set password=? where id=?","111111",1) if err != nil{ fmt.Printf("Insert failed,err:%v\n", err) return } fmt.Println("update data successd:", result) rowsaffected,err := result.RowsAffected() if err != nil { fmt.Printf("Get RowsAffected failed,err:%v\n",err) return } fmt.Println("Affected rows:", rowsaffected)}//删除数据func DeleteData(DB *sql.DB){ result,err := DB.Exec("delete from users where id=?",1) if err != nil{ fmt.Printf("Insert failed,err:%v\n",err) return } fmt.Println("delete data successd:", result) rowsaffected,err := result.RowsAffected() if err != nil { fmt.Printf("Get RowsAffected failed,err:%v\n",err) return } fmt.Println("Affected rows:", rowsaffected)}
|