gorm 连接mysql_mysql之GORM接口

Mysql在golang中推荐使用Gorm：https://gorm.io/docs/。

import "github.com/jinzhu/gorm"

1. 连接数据库

user:password@(localhost)/dbname?charset=utf8&parseTime=True&loc=Local

db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")

defer db.Close()

db.SingularTable(true)

db.LogMode(true)

db.DB().SetMaxIdleConns(10)

db.DB().SetMaxOpenConns(100)

In order to handle time.Time correctly, you need to include parseTime as a parameter.

In order to fully support UTF-8 encoding, you need to change charset=utf8 to charset=utf8mb4.

2. model

由于gorm是使用的orm映射，所以需要定义要操作的表的model，在go中需要定义一个struct，struct的名字就是对应数据库中的表名，注意gorm查找struct名对应数据库中的表名的时候会默认把你的struct中的大写字母转换为小写并加上“s”，所以可以加上 db.SingularTable(true) 让gorm转义struct名字的时候不用加上s。可以提前在数据库中创建好表的然后再用gorm去查询的，也可以用gorm去创建表，建议直接在数据库上创建，修改表字段的操作方便，gorm只用来查询和更新数据。

type User struct{

gorm.Model//嵌入常用字段

Name stringAge sql.NullInt64

Birthday*time.Time

Emailstring `gorm:"type:varchar(100);unique_index"`

Rolestring `gorm:"size:255"` //set field size to 255

MemberNumber *string `gorm:"unique;not null"` //set member number to unique and not null

Num int `gorm:"AUTO_INCREMENT"` //set num to auto incrementable

Address string `gorm:"index:addr"` //create index with name `addr` for address

IgnoreMe int `gorm:"-"` //ignore this field

}

* tag(如：gorm、json form)后面的冒号与双引号之间不能有空格；

* 同一种tag，不同属性定义使用“；”分隔；不同tag 使用空格“ ”分隔；

* 使用预加载Preload，需要在结构体中指明外键(不指名则默认是预加载表的主键作为外键)

定义models时tags是可选项，gorm支持如下tags：

Column：指定列名

Type：指定列数据类型

Size：指定列大小，默认255

PRIMARY_KEY：指定列作为主键

UNIQUE：指定列唯一

DEFAULT：指定列默认值

PRECISION：指定列精度

NOT NULL：指定列NOT NULL

AUTO_INCREMENT：指定列自动增加

INDEX：创建索引with or without name，same name creates composite indexes

UNIQUE_INDEX：Like INDEX，Create unique index

EMBEDDED：Set struct as embedded

EMBEDDED_PREFIX：Set embedded struct’s prefix name

-：忽略此域fields

gorm.Model

gorm.Model is a basic GoLang struct which includes the following fields: ID, CreatedAt, UpdatedAt, DeletedAt.

It may be embedded into your model or you may build your own model without it.

//gorm.Model definition

type Model struct{

IDuint `gorm:"primary_key"`

CreatedAt time.Time

UpdatedAt time.Time

DeletedAt*time.Time

}//Inject fields `ID`, `CreatedAt`, `UpdatedAt`, `DeletedAt` into model `User`

type User struct{

gorm.Model

Namestring}//Declaring model w/o gorm.Model

type User struct{

IDintNamestring}

GORM uses any field with the name ID as the table’s primary key by default.

type User struct{

IDstring //field named `ID` will be used as primary field by default

Name string}//Set field `AnimalID` as primary field

type Animal struct{

AnimalID int64 `gorm:"primary_key"`

NamestringAge int64

}

You can apply any rules on the default table name by defining the DefaultTableNameHandler.

gorm.DefaultTableNameHandler = func (db *gorm.DB, defaultTableName string) string{return "prefix_" +defaultTableName;

}

3. 增删改查

注：域的零值不会被插入数据库，也不会被查询，但使用时的值为默认零值。

all fields having a zero value, like 0, '', false or other zero values, won’t be saved into the database but will use its default value. If you want to avoid this, consider using a pointer type or scanner/valuer.

When query with struct, GORM will only query with those fields has non-zero value, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) SELECT * FROM users WHERE name = "jinzhu";

Struct& map查询

//Struct

db.Where(&User{Name: "jinzhu", Age: 20}).First(&user) SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;<