常用命令
查看所有的数据库show databases;
查看单个数据库show create database book;
创建create database taobao;
删除drop database taobao;
外网配置
默认mysql只能本机访问。外网是无法连接的，因此在开发阶段为了调试方便，需要设置为外部网络可以访问。
第一步use mysql;配置信息在mysql这个数据库中。
第二步select host from user where user="root";查询当前的配置，默认返回的结果是localhost,表示只能本机访问。
第三步update user set host="%" where user="root";修改允许所有的网路访问。
第四步flush privileges;刷新配置。
go连接mysql
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// 这里是权宜之计，因为在服务器上如果被贡献，则可以直接查看到密码。
// 推荐的做法1：启动的时候添加参数，从参数中获取密码（不知道历史记录是否可以删除，如果不能删除，也不安全）
// 推荐的做法2：启动的时候，提示用户输入mysql的密码，这样可以做到绝对安全。但是会涉及到宕机重启后应用无法启动的情况。需要综合考虑。
link := "root:Mima@1234567@tcp(xuejs.cn:3306)/book"
// 只会校验格式是否正确，不会真的发送请求
db, db_open_error := sql.Open("mysql", link)
if db_open_error != nil {
fmt.Println("链接格式不对:", db_open_error)
return
}
// 记得关闭连接
defer db.Close()
// 真正的开始连接到服务器，如果密码错误，则无法连接
ping_error := db.Ping()
if ping_error != nil {
fmt.Println("连接不上服务器:", ping_error)
return
}
fmt.Println("连接上了")
}
增删改查
use book;先切换到book数据库。
创建数据表,表明为user,utf8mb4表示utf8编码且支持emoji表情。
create table `user`(
`id` bigint(20) not null auto_increment,
`name` varchar(20) default "",
`age` int(11) default '0',
primary key(`id`)
)engine=InnoDB auto_increment=1 default charset=utf8mb4;
插入数据
insert into user(name,age) values("小明", 100);
insert into user(name,age) values("瑶瑶", 18);
查询数据
select * from user;
select name,age from user where id=1;
删除表
drop table user;
golang 增删改查
golang查询mysql
// 查询
{
sql_query := "select id,name,age from user where id=1;"
query_res := db.QueryRow(sql_query)
type User struct {
id int
name string
age int
}
var u User
query_error := query_res.Scan(&u.id, &u.name, &u.age)
if query_error != nil {
fmt.Println("查询失败")
}
fmt.Println("user is", u)
}
{
sql_query := "select id,name,age from user where id=?;"
// 第二个参数开始表示参数，用于依次替换查询条件中的问号
query_res := db.QueryRow(sql_query, 2)
type User struct {
id int
name string
age int
}
var u User
// 查询之后，必须调用Scan方法
query_error := query_res.Scan(&u.id, &u.name, &u.age)
if query_error != nil {
fmt.Println("查询失败")
}
fmt.Println("user is", u)
}
最大连接数。
如果不进行Scan则无法关闭连接，scan表示从连接中取数据。导致连接池被占用。SetMaxOpenConns用于设置连接池。默认是0表示不限制。
db.SetMaxOpenConns(5)
for i := 0; i < 11; i++ {
fmt.Println("begin:", i)
db.QueryRow("select * from user where id=1;")
fmt.Println("finish:", i)
}
/*
begin: 0
finish: 0
begin: 1
finish: 1
begin: 2
finish: 2
begin: 3
finish: 3
begin: 4
finish: 4
begin: 5
*/
多行查询
rows, err := db.Query("select * from user where id < 100")
if err != nil {
fmt.Println("err is ", err)
return
}
// 需要手动关闭
defer rows.Close()
type User struct {
id int
name string
age int
}
for rows.Next() {
var u User
err := rows.Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Println("查询失败", err)
continue
}
fmt.Println("u is ", u)
}
提前关闭，直接调用rows.Close这样下次.next()就会返回false。
最大空闲连接数
由于连接数也会占用资源，比如有100个连接数，但是经常只用到了10个，我们可以设置最大闲置数为20。当超过20个闲置的连接，则会释放掉连接。
因为对于数据库查询来说，每次新建一个连接都要进行三次握手，这样会导致延迟偏高。因此适当的空闲连接数有助于提升查询效率。
// 默认是2。 强烈建议改成20。2个连接太少，不够用呀。
db.SetMaxIdleConns(20)
插入数据
{
//插入数据
sql_insert := `insert into user(name, age) values("鲁班七号",8)`
ret, err := db.Exec(sql_insert)
if err != nil {
fmt.Println("插入失败", err)
} else {
fmt.Println("插入成功")
id, err1 := ret.LastInsertId()
if err1 != nil {
fmt.Println("get id failed:", err1)
} else {
fmt.Println("插入的id是:", id)
}
}
}
更新数据
{
// 修改
sql_update := `update user set age=? where id =? `
ret, err := db.Exec(sql_update, 100, 3)
if err != nil {
fmt.Println("修改失败", err)
} else {
fmt.Println("修改成功")
n, err1 := ret.RowsAffected()
if err1 != nil {
fmt.Println("get id failed:", err1)
} else {
fmt.Println("影响了:", n, "行数据")
}
}
}
删除数据
{
// 删除
sql_delete := `delete from user where age > ? `
ret, err := db.Exec(sql_delete, 10)
if err != nil {
fmt.Println("删除失败", err)
} else {
fmt.Println("删除成功")
n, err1 := ret.RowsAffected()
if err1 != nil {
fmt.Println("获取行失败:", err1)
} else {
fmt.Println("影响了:", n, "行数据")
}
}
}
预处理提升效率
正常的操作，如下两条命令，会依次发送给MYSQL服务器，这样会带来性能问题。这两条语句的内容大部分都是一样的，这样会导致客户端和服务器之间的数据明显增多。
insert into user(name, age) values("鲁班"，20)
insert into user(name, age) values("鲁班七号"，10)
使用预处理可以将相同的部分提前发送给服务器。随后每次只传递参数就可以提升效率。
预处理插入
// 预处理插入
t1 := time.Now()
sql_insert := `insert into user(name,age) values(?,?)`
stmt, error := db.Prepare(sql_insert)
if error != nil {
fmt.Println("预处理失败:", error)
} else {
defer stmt.Close()
for i := 0; i < 10000; i++ {
fmt.Println("\n当前插入:", i)
name := fmt.Sprintf("鲁班%d号", i)
ret, err := stmt.Exec(name, i)
if err != nil {
fmt.Println("插入 ", name, " 的时候失败:", err)
continue
}
data, err := ret.RowsAffected()
if err != nil {
fmt.Println("获取行号失败")
} else {
fmt.Println("插入了", data, "行")
}
}
t2 := time.Now()
fmt.Println("used ", t2.UnixMilli()-t1.UnixMilli())
预处理查询
{
// 预处理查询
sql_insert := `select * from user where id > ?`
stmt, error := db.Prepare(sql_insert)
if error != nil {
fmt.Println("预处理失败:", error)
} else {
defer stmt.Close()
rows, rows_error := stmt.Query(10)
if rows_error != nil {
fmt.Println("rows_error:", rows_error)
return
}
for rows.Next() {
var u User
user_error := rows.Scan(&u.age, &u.name, &u.id)
if user_error != nil {
fmt.Println("use-error:", user_error)
}
fmt.Println("user is ", u)
}
}
}
批量插入
虽然预处理可以提升效率，但是每次都是插入一次，然后插入成功再返回，这个过程是比较慢的。要插入的数据发送给服务器的时候，发送完成这个时候阻塞，直到客户端接收到了消息，完全可以在这个时间段继续往服务器塞数据。
{
// 批量插入
// insert into user(name,age) values (?,?),{?,?}`
t1 := time.Now()
sql_insert := `insert into user(name,age) values `
// interface{}表示任意类型
// type ALL_TYPE interface{}
// values1 := []ALL_TYPE{}
values := []interface{}{}
var length = 1000
for i := 0; i < length; i++ {
if i == length-1 {
sql_insert += "(?,?)"
} else {
sql_insert += "(?,?),"
}
values = append(values, fmt.Sprintf("鲁班%d号", i), i)
}
res, err := db.Exec(sql_insert, values...) // values...解构
if err != nil {
fmt.Println("插入失败")
} else {
id, _ := res.LastInsertId()
n, _ := res.RowsAffected()
fmt.Println("id is ", id, " n is ", n)
}
t2 := time.Now()
fmt.Println("used ", t2.UnixMilli()-t1.UnixMilli(), "ms")
/*
id is 12510 n is 1000
used 199 ms
*/
}
事务
事务是指多条指令，要么全部成功，要么全部失败。
// 开始事务
func (db *DB) Begin() (*Tx, error)
// 提交事务
func (tx *Tx) Commit() error
// 回滚事务
func (tx *Tx) Rollback() error
{
// 事务
tx, tx_error := db.Begin()
if tx_error != nil {
fmt.Println("开启事务失败:", tx_error)
return
}
// 执行多条语句
{
sql_insert1 := `update user set age=age-2 where id = 10000`
sql_insert2 := `update user set age=age+2 where id =10001`
// 表XXXX不存在，用于运行失败回滚操作
// sql_insert2 := `update XXXX set age=age+2 where id =3`
_, insert1_error := tx.Exec(sql_insert1)
if insert1_error != nil {
// 回滚
tx.Rollback()
}
fmt.Println("第一条记录更新完成")
// 这个时候休眠，然后通过客户端工具去查询数据库，并不能得到之前的更新
// 所有的操作必须等到事务被提交之后才能被搜索到
time.Sleep(time.Second * 10)
fmt.Println("休眠结束")
_, insert2_error := tx.Exec(sql_insert2)
if insert2_error != nil {
// 回滚
rollback := tx.Rollback()
if rollback != nil {
fmt.Println("回滚失败")
} else {
fmt.Println("第二次回滚完成")
}
}
fmt.Println("第二条记录完成")
// 提交事务, 如果之前进行了回滚操作，则无法进行提交事务。
commit_error := tx.Commit()
if commit_error != nil {
fmt.Println("事务提交失败:", commit_error)
panic(commit_error.Error())
}
fmt.Println("事务提交成功")
}
}
SQLX
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
func main() {
link := "root:Mima@1234567@tcp(xuejs.cn:3306)/book"
// 1.检查连接，2 ping
db, err := sqlx.Connect("mysql", link)
if err != nil {
fmt.Println("connect failed :", err)
return
}
db.SetMaxOpenConns(20)
// 关闭连接
defer db.Close()
// 成员变量必须大写，因为是通过反射拿到的
type User struct {
Id int
Name string
Age int
}
var u User
get_error := db.Get(&u, "select * from user where id=10000")
if get_error != nil {
fmt.Println("err:", get_error)
}
fmt.Println("u is ", u.Id, u.Name, u.Age)
var manay_user []User
// manay_user = make([]User, 1000) // 1000表示初始化1000个对象
manay_user = make([]User, 1000, 2000) // 1000表示初始化1000个对象,2000表示容量
fmt.Println("length is ", len(manay_user))
fmt.Println("cap is ", cap(manay_user))
get_many_err := db.Select(&manay_user, "select * from user where id > ?", 10000)
if get_many_err != nil {
fmt.Println("get_manay_error is ", get_many_err)
}
// fmt.Println("many_user is ", manay_user)
fmt.Println("length is ", len(manay_user))
}
插入数据,更新，删除没啥区别，不需要用到反射。
{
// 插入数据
ret, err := db.Exec("insert into user(name, age) values (?,?)", "程咬金", 20)
if err != nil {
fmt.Println("插入失败:", err)
return
}
n, n_error := ret.RowsAffected()
if n_error != nil {
fmt.Println("n_error is ", n_error)
return
}
fmt.Println("插入成功:", n)
}
sql注入
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
func main() {
link := "root:Mima@1234567@tcp(xuejs.cn:3306)/book"
db, error := sqlx.Connect("mysql", link)
if error != nil {
fmt.Println("连接失败:", error)
return
}
type User struct {
Id int
Name string
Age int
}
var manay_user []User = make([]User, 0, 1000)
//注入代码
sql_str := fmt.Sprintf("select * from user where id=%s", "10000 or 1=1")
fmt.Println("", sql_str)
error_select := db.Select(&manay_user, sql_str)
if error_select != nil {
fmt.Println("查询失败", error_select)
return
}
fmt.Println("len is ", len(manay_user))
}
使用预处理可以避免SQL注入，另外，或者自己不拼接字符串。
分页查询
（0,1,2,3,4,5,6,7,8,9的数据都需要跳过，0页为开始页的情况）比如第10页，每页为20条，则m为10*20，n为20
// 分页查询1
// 跳过m条，查询n条。因此跳过的数越大，查询效率越慢。因为需要扫描全部的m+n条数据
// select * from table order by id limit m, n;
// 分页查询2
// 根据上一页获取的id，可以快速查找
// 但是如果出现查询到了第一页，在查找100页，此时无法获取99页的id，所以查询受到限制
// select * from table where id= 100 order by id limit m, n;
// 分页查询3
// 只查找一个字段，
// select * from table where id > (select id from table order by id limit m, 1) limit n;
