MYSQL

MYSQL

SQL语句

DDL：操作数据库的
DML：操作表的CRUD
DCL：操作用户及权限

存储引擎

MyISAM:
1.查询速度快
2.不支持事务
3.只支持表锁
InnoDB
1.支持事务
2.整体速度快
3.支持表锁和行锁

什么是表锁和行锁？
https://blog.csdn.net/nicajonh/article/details/78814987
什么是事务？
ACID
原子性：要么成功，要么失败，没有中间操作
一致性：事务开始和结束，数据库的完整性没有被破坏
隔离性：事务之间互相隔离
持久性：事务操作的结果是不会丢失的

索引原理

索引数据结构：B树和B+树，减少IO操作，目录查找
索引命中：
索引类型：
分库分表
SQL注入
SQL慢查询优化：

MYSQL主从：binglog
MYSQL读写分离
MYSQL主主（MGR）

Go操作Mysql

准备数据库

#建库
create database goday02 character set utf8mb4 collate utf8mb4_bin;
#建用户
create user 'test'@'%' identified by 'test';
#赋权
grant all on goday02.* to test@'%';
#刷新权限
FLUSH PRIVILEGES;
#选择数据库
use goday02;
#建表
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int COLLATE utf8mb4_bin DEFAULT NULL,
  `city` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

下载数据库驱动

go get -u github.com/go-sql-driver/mysql

使用数据库驱动

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只执行init
    "time"
)
var db *sql.DB
//安装驱动
//go  get -u github.com/go-sql-driver/mysql
//使用database/sql库进行数据库操作
//database/sql原生支持连接池功能是并发安全的，但没有具体实现只是列出了第三方库需要实现的内容
func initDB() (err error) {
    //数据库信息
    dsn := "test:test@tcp(192.168.72.10:3306)/goday02"
    db, err = sql.Open("mysql", dsn) //不会校验数据库的账户密码信息，只校验格式
    if err != nil {
        fmt.Println("dsn  error", err)
        return
    }
    //校验账户密码是否正确
    err = db.Ping()
    if err != nil {
        fmt.Println("check mysql user error", err)
        return
    }
    //设置数据库最大连接池数
    db.SetMaxOpenConns(10)
    //设置数据库中最大的闲置连接数
    db.SetMaxIdleConns(5)
    //设置数据库链接存活的时间
    db.SetConnMaxLifetime(time.Second * 30)
    return
}
type User struct {
    Id   int
    Name string
    Age  int
    City string
}
//QueryRowDemo 单行查询
func QueryRowDemo() {
    sqlStr := `select id,user_name,age,city from user where id = ?`
    var u User
    err := db.QueryRow(sqlStr, 1).Scan(&u.Id, &u.Name, &u.Age, &u.City)
    if err != nil {
        return
    }
    fmt.Println(u)
}
//QueryDemo 多行查询
func QueryDemo() {
    sqlStr := `select id,user_name,age,city from user`
    rows, err := db.Query(sqlStr)
    defer rows.Close()
    if err != nil {
        return
    }
    for rows.Next() {
        var u User
        rows.Scan(&u.Id, &u.Name, &u.Age, &u.City)
        fmt.Println(u)
    }
}
//InsertDemo 插入数据
func InsertDemo(name string, age int, city string) {
    sqlStr := `insert into user(user_name,age,city) values(?,?,?)`
    ret, err := db.Exec(sqlStr, name, age, city)
    if err != nil {
        return
    }
    //如果是插入数据操作，可以获取插入数据的id
    theId, err := ret.LastInsertId()
    if err != nil {
        return
    }
    fmt.Println(theId)
}
//UpdateDemo 更新数据
func UpdateDemo(age int, id int) {
    sqlStr := `update user set age=? where id=?`
    ret, err := db.Exec(sqlStr, age, id)
    if err != nil {
        return
    }
    //如果是插入数据操作，可以获取修改数据影响的行数
    rowNum, err := ret.RowsAffected()
    if err != nil {
        return
    }
    fmt.Println(rowNum)
}
//DeleteDemo 删除数据
func DeleteDemo(id int) {
    sqlStr := "delete from user where id=?"
    ret, err := db.Exec(sqlStr, id)
    if err != nil {
        return
    }
    //如果是插入数据操作，可以获取删除数据影响的行数
    rowNum, err := ret.RowsAffected()
    if err != nil {
        return
    }
    fmt.Println(rowNum)
}
func main() {
    initDB()
    defer db.Close()
    QueryDemo()
    //InsertDemo("测试",20,"北京")
    //UpdateDemo(24, 3)
    //DeleteDemo(2)
    //fmt.Printf("select * from user where id = %s", "ssss")
}

Mysql预处理

为什么要预处理？
1.优化mysql服务器重复执行SQL的方法，可以提升服务器效率，提前让服务器编译，批量执行重复操作，一次编译多次执行
2.避免SQL注入的问题

Go预处理操作

// QueryPrepareDemo 查询预处理
func QueryPrepareDemo() {
    sqlStr := `select id,user_name,age,city from user where id > ?`
    stmt, err := db.Prepare(sqlStr)
    defer stmt.Close()
    if err != nil {
        return
    }
    rows, err := stmt.Query(0)
    defer rows.Close()
    if err != nil {
        return
    }
    for rows.Next() {
        var u User
        rows.Scan(&u.Id, &u.Name, &u.Age, &u.City)
        fmt.Println(u)
    }
}
// InsertPrepareDemo 新增预处理
func InsertPrepareDemo(name string, age int, city string) {
    sqlStr := `insert into user(user_name,age,city) values(?,?,?)`
    stmt, err := db.Prepare(sqlStr)
    defer stmt.Close()
    if err != nil {
        return
    }
    ret, err := stmt.Exec(name, age, city)
    if err != nil {
        return
    }
    //如果是插入数据操作，可以获取插入数据的id
    theId, err := ret.LastInsertId()
    if err != nil {
        return
    }
    fmt.Println(theId)
}

Mysql事务

Go事务操作

GO中事务主要为三个函数
tx,err:=db.Begin()
tx.Rollback()
tx.Commit()

//开启事务
func transactionUpdateDemo(age,id int) {
    //开启事务
    tx, err := db.Begin()
    if err!=nil{
        return
    }
    sqlStr := `update user set age=? where id=?`
    ret, err := tx.Exec(sqlStr, age, id)
    if err != nil {
        //回滚
        tx.Rollback()
        return
    }
    //如果是插入数据操作，可以获取修改数据影响的行数
    rowNum, err := ret.RowsAffected()
    if err != nil {
        //回滚
        tx.Rollback()
        return
    }
    //提交
    err = tx.Commit()
    if err != nil {
        //回滚
        tx.Rollback()
        return
    }
    fmt.Println(rowNum)
}

SQLX第三方包操作msyql

sqlx查询与原有操作不同，其他操作大致相同

package main
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只执行init
    "github.com/jmoiron/sqlx"
    "time"
)
//安装sqlx go get -u github.com/jmoiron/sqlx
var dbx *sqlx.DB
func initXDB() (err error) {
    //数据库信息
    dsn := "test:test@tcp(192.168.72.10:3306)/goday02"
    dbx, err = sqlx.Open("mysql", dsn) //不会校验数据库的账户密码信息，只校验格式
    if err != nil {
        fmt.Println("dsn  error", err)
        return
    }
    //校验账户密码是否正确
    err = dbx.Ping()
    if err != nil {
        fmt.Println("check mysql user error", err)
        return
    }
    //设置数据库最大连接池数
    dbx.SetMaxOpenConns(10)
    //设置数据库中最大的闲置连接数
    dbx.SetMaxIdleConns(5)
    //设置数据库链接存活的时间
    dbx.SetConnMaxLifetime(time.Second * 30)
    return
}
type User struct {
    Id   int    `db:"id"`
    Name string `db:"user_name"`
    Age  int    `db:"age"`
    City string `db:"city"`
}
//QueryRowXDemo 单行查询
func QueryRowXDemo() {
    sqlStr := `select id,user_name,age,city from user where id = ?`
    var u User
    err := dbx.Get(&u, sqlStr, 1)
    if err != nil {
        return
    }
    fmt.Println(u)
}
//QueryXDemo 多行查询
func QueryXDemo() {
    sqlStr := `select id,user_name,age,city from user`
    var u []User
    err := dbx.Select(&u, sqlStr)
    if err != nil {
        return
    }
    fmt.Println(u)
}
func main() {
    initXDB()
    QueryXDemo()
}

golang学习

MYSQL数据库