Gorm: Gorm ignores UTF8MB4 character set in connection string when creating tables

[enahs]

It seems like gorm ignores the value provided for the character set in the connection and simply defaults to UTF8 when creating tables. This causes issues when trying to insert emojis into varchar columns.

What version of Go are you using (go 1.8)?

Which database and its version are you using?

Mysql Server version: 5.7.19 Homebrew

Please provide a complete runnable program to reproduce your issue. IMPORTANT

Need to runnable with GORM's docker compose config or please provides your config.

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)

var db *gorm.DB

type User struct {
    ID        int64
    FirstName string
    LastName  string
}

func init() {
    var err error
    // NOTE THE CHARACTER SET!!!!!!!!
    // this seems to be ignored by createTable
    db, err = gorm.Open("mysql", "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8mb4,utf8&parseTime=True")
    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

func main() {
    if err := db.CreateTable(&User{}).Error; err != nil {
        panic(err)
    }
    // UTF8 strings. works as expected
    you := &User{
        FirstName: "Bob",
        LastName:  "Smith",
    }
    if err := db.Create(you).Error; err != nil {
        panic(err)
    }
    // UTF8MB4 strings. does not work
    me := &User{
        FirstName: "🔫",
        LastName:  "💯",
    }
    if err := db.Create(me).Error; err != nil {
        fmt.Println(err)
    }

}

[enahs]

this is really just a reproducible version of https://github.com/jinzhu/gorm/issues/1328

[dreambo8563]

the same issue happen on
gorm v1.9.1,
if I manually create the table with charset utf8. I can insert data without the error

[enahs]

after poking around more in the source code, there is a way to do this that isn't documented.
scope's createTable method checks for a gorm:table_options value on the scope which allows you to augment the create table statement. It would be good to add a mention to table_options in the documentation and perhaps include this as an example.

tx.Set("gorm:table_options", "CHARSET=utf8mb4").AutoMigrate(&user{})

[CraigChilds94]

@jinzhu I agree that there should be a way to set it by default. or use the value passed into the connection string.

[ithinco]

After adding charset params db, err = gorm.Open("mysql", "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8mb4,utf8&parseTime=True") and change the table's charset to utf8mb4, I solved the problem of incorrect string value when storing unicode in mysql.

[github-actions[bot]]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog