Golang database/sql & GORM

0.Models写法技巧及Migration标准

法则

基本满足大驼峰命名法则 首字母大写 “_” 忽略后大写

实例

device_id 对应 DeviceID create_time 对应 CreateTime location 对应 Location

官方实例

type User struct {
    ID           int
    Birthday     time.Time
    Age          int
    Name         string  `sql:"size:255"` // Default size for string is 255, you could reset it with this tag
    Num          int     `sql:"AUTO_INCREMENT"`
    CreatedAt    time.Time
    UpdatedAt    time.Time
    DeletedAt    *time.Time

    Emails            []Email         // One-To-Many relationship (has many)
    BillingAddress    Address         // One-To-One relationship (has one)
    BillingAddressID  sql.NullInt64   // Foreign key of BillingAddress
    ShippingAddress   Address         // One-To-One relationship (has one)
    ShippingAddressID int             // Foreign key of ShippingAddress
    IgnoreMe          int `sql:"-"`   // Ignore this field
    Languages         []Language `gorm:"many2many:user_languages;"` // Many-To-Many relationship, 'user_languages' is join table
}

type Email struct {
    ID      int
    UserID  int     `sql:"index"` // Foreign key (belongs to), tag `index` will create index for this field when using AutoMigrate
    Email   string  `sql:"type:varchar(100);unique_index"` // Set field's sql type, tag `unique_index` will create unique index
    Subscribed bool
}

type Address struct {
    ID       int
    Address1 string         `sql:"not null;unique"` // Set field as not nullable and unique
    Address2 string         `sql:"type:varchar(100);unique"`
    Post     sql.NullString `sql:"not null"`
}

type Language struct {
    ID   int
    Name string `sql:"index:idx_name_code"` // Create index with name, and will create combined index if find other fields defined same name
    Code string `sql:"index:idx_name_code"` // `unique_index` also works
}

1.GORM实践

数据库

MariaDB [zwjj]> desc zw_device;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| device_id   | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id     | int(10)             | YES  |     | NULL    |                |
| device_sn   | varchar(32)         | NO   |     | NULL    |                |
| device_imei | varchar(32)         | NO   |     | NULL    |                |
| description | text                | NO   |     | NULL    |                |
| sort        | int(10) unsigned    | NO   |     | NULL    |                |
| type        | tinyint(1) unsigned | NO   |     | 0       |                |
| status      | tinyint(1)          | NO   |     | 1       |                |
| location    | varchar(32)         | YES  |     | NULL    |                |
| lat         | varchar(255)        | YES  |     | NULL    |                |
| lng         | varchar(125)        | NO   |     | NULL    |                |
| last_time   | int(10) unsigned    | NO   |     | NULL    |                |
| create_time | int(10) unsigned    | NO   |     | NULL    |                |
+-------------+---------------------

import

import(
        "fmt"
        _ "github.com/go-sql-driver/mysql"
        "github.com/jinzhu/gorm"
        _ "github.com/lib/pq"
        "log"
)

define Structs(Models) in gorm

type Device struct {
        DeviceID    int
        UserID      int
        DeviceSN    string
        DeviceImei  string
        Description string
        Sort        int
        Type        int
        Status      int
        Location    string
        Lat         string
        Lng         string
}
func (d Device) TableName() string {
        return "zw_device"
}

get/handle row in gorm

Locsearch实现了查找device_id = 4设备的详细信息

# select by Models
func Locsearch(info string) {
        fmt.Println(info)
        db, err := gorm.Open("mysql", "zwjj:zwjj@tcp(gis.anasit.com:3306)/zwjj?charset=utf8&parseTime=True")
        if err != nil {
                log.Fatal(err)
        }
        db.LogMode(true)
        var device Device
        row := db.Where("device_id = ?", "4").First(&device)
        row.Scan(&device)
        fmt.Println(device.DeviceImei, device.DeviceID, device.Lat)
}


# raw select lat, lng start
    var device Device
        row := db.Table("zw_device").Where("device_id = ?", "4").Select("lat, lng").Row()
        row.Scan(&device.Lat, &device.Lng)
        fmt.Println(device.Lat)
# raw end

# raw select * start
    var device Device
        row := db.Table("zw_device").Where("device_id = ?", "4").Select("*")
        row.Scan(&device)
        fmt.Println(device.DeviceImei, device.DeviceID, device.Lat)
# raw end
微信扫一扫交流

作者:ryanemax
微信关注:ryanemax (刘雨飏)
本文出处:https://romantic-hoover-f991f1.netlify.com/cookbook/backend/golangsqlorm/
授权协议: CC BY-SA 4.0