GORM UUID太长

Question

Currently I am using GO-GORM for all of my database queries (mostly CRUD) and I am having some issues inserting a generated UUID into a MySQL database column.

The column is a BINARY(16) as suggested in multiple blogs, the UUID is generated using github.com/satori/go.uuid package for Golang.

I am using GORM's BeforeCreate hook to generate the UUID if one does not already exist on the user, the code that I am using is as follows:

func (u *User) BeforeCreate(scope *gorm.Scope) (err error) {
    if u.UserID == uuid.Nil {
        uuid, err := uuid.NewV4().MarshalBinary()
        scope.SetColumn("user_id", uuid)
    }
}

I have also used len to get the length that MarshalBinary outputs and it returns as 16.

The error I get from GORM when trying to insert the UUID into MySQL is as follows:

(Error 1406: Data too long for column 'user_id' at row 1)

I have also fmt.Println(uuid) to see the results and they are also as follows (obviosuly changes as the UUID is generated every insert)

[93 132 59 55 102 96 72 35 137 185 34 21 195 88 213 127]

My MYSQL schema is as follows also:

CREATE TABLE users
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    user_id BINARY(16) NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    email VARCHAR(255),
    address_id VARCHAR(255)
);
CREATE INDEX idx_users_deleted_at ON users (deleted_at);
CREATE UNIQUE INDEX username ON users (username);
CREATE UNIQUE INDEX user_id ON users (user_id);

I have tried different methods and libraries to generate UUIDs and convert them to binary to insert with similar results.

Answer 1

I think the problem is in the definition of model User. To save the GUID as 16-bytes binary, you need to define the UserID column as []byte not uuid.UUID.

type User struct {
    //other fields ..
    UserID    []byte

    //other fields ...
}

func (u *User) BeforeCreate(scope *gorm.Scope) (err error) {
    if u.UserID == nil {
        uuid, err := uuid.NewV4().MarshalBinary()
        scope.SetColumn("user_id", uuid)
    }
    return nil
}

If you define the field as uuid.UUID, gorm "misinterpreted" the field as string and then insert that string into the database as binary. For example, the following UUID,

uuid: 16ac369b-e57f-471b-96f6-1068ead0bf98
//16-bytes equivalent
bytes: [22 172 54 155 229 127 71 27 150 246 16 104 234 208 191 152]

will be inserted to database as the ASCII codes of the UUID which are

0x31 0x36 0x61 0x63 0x33 0x36 0x39 0x62 0x2D 0x65 ...
('1' '6'  'a'  'c'  '3'  '6'  '9'  'b'  '-'  'e'  ...)

which are 36-bytes in length, thus you're getting Error 1406: ...