vardb*sql.DB//声明一个全局的 db 变量// 初始化 MySQL 函数funcinitMySQL()(errerror){dsn:="root:password@tcp(127.0.0.1:3306)/dbname"db,err=sql.Open("mysql",dsn)iferr!=nil{return}err=db.Ping()iferr!=nil{return}return}funcmain(){// 初始化 MySQLerr:=initMySQL()iferr!=nil{panic(err)}deferdb.Close()}
// 查询一行数据funcqueryRowDemo()(u1*user,errerror){// 声明查询语句sqlStr:="SELECT id,name,age FROM user WHERE id = ?"// 声明一个 user 类型的变量varuuser// 执行查询并且扫描至 uerr=db.QueryRow(sqlStr,1).Scan(&u.id,&u.age,&u.name)iferr!=nil{returnnil,err}u1=&ureturn}funcmain(){// 初始化 MySQLerr:=initMySQL()iferr!=nil{panic(err)}deferdb.Close()u1,err:=queryRowDemo()iferr!=nil{fmt.Printf("err:%s",err)}fmt.Printf("id:%d, age:%d, name:%s\n",u1.id,u1.age,u1.name)}
// 查询多行数据funcqueryMultiRowDemo(){sqlStr:="SELECT id,name,age FROM user WHERE id > ?"rows,err:=db.Query(sqlStr,0)iferr!=nil{fmt.Printf("query data failed,err:%s\n",err)return}// 查询完数据后需要进行关闭数据库链接deferrows.Close()forrows.Next(){varuusererr:=rows.Scan(&u.id,&u.age,&u.name)iferr!=nil{fmt.Printf("scan data failed, err:%v\n",err)return}fmt.Printf("id:%d name:%s age:%d\n",u.id,u.name,u.age)}}
// 增加一行数据funcinsertRowDemo(){sqlStr:="INSERT INTO user(name, age) VALUES(?, ?)"result,err:=db.Exec(sqlStr,"小羽",22)iferr!=nil{fmt.Printf("insert data failed, err:%v\n",err)return}id,err:=result.LastInsertId()iferr!=nil{fmt.Printf("get insert lastInsertId failed, err:%v\n",err)return}fmt.Printf("insert success, id:%d\n",id)}
执行结果:
1
insert success, id:4
更新数据 UPDATE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 更新一组数据funcupdateRowDemo(){sqlStr:="UPDATE user SET age = ? WHERE id = ?"result,err:=db.Exec(sqlStr,22,1)iferr!=nil{fmt.Printf("update data failed, err:%v\n",err)return}n,err:=result.RowsAffected()iferr!=nil{fmt.Printf("get rowsaffected failed, err:%v\n",err)return}fmt.Printf("update success, affected rows:%d\n",n)}
删除数据 DELETE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 删除一行数据funcdeleteRowDemo(){sqlStr:="DELETE FROM user WHERE id = ?"result,err:=db.Exec(sqlStr,2)iferr!=nil{fmt.Printf("delete data failed, err:%d\n",err)return}n,err:=result.RowsAffected()iferr!=nil{fmt.Printf("get affected failed, err:%v\n",err)return}fmt.Printf("delete success, affected rows:%d\n",n)}