How to insert POINT into mysql using gorm?-CodePudding

func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
    return clause.Expr{
        SQL:  "POINT(?)",
        Vars: []interface{}{fmt.Sprintf("%f, %f", loc.X, loc.Y)},
    }
}

This is my valuer. And it creates a query like this POINT('XX.XXXXX, YY.YYYYY'). But this query does not work because of single quotation marks. How can I solve this problem?

CodePudding user response：

One solution might be to use MySQL-specific functions like GeomFromText or PointFromText to convert the string to spatial data type.

func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
    return clause.Expr{
        SQL:  "PointFromText(?)",
        Vars: []interface{}{fmt.Sprintf("POINT(%f %f)", loc.X, loc.Y)},
    }
}

More details are provided here. Also, please note that based on which version of MySQL you are using, these functions might be named differently. For example, the MySQL 5.6 PointFromText function corresponds to MySQL 8.0 ST_PointFromText function.