MySQL: A utility function for comparision of UUID fields

[realbucksavage]

Assume working with a table that has UUIDs stored in a varbinary field. Here's an example:

CREATE TABLE `some_table` (
  `id` varbinary(16) NOT NULL,
  `some_column` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Querying data by id in this table would look like this:

select * from `some_table` where id = uuid_to_bin('0000-0000...');

The type of the id column would be of mysql.ColumnString in the generated code.

type someTableTable struct {
    mysql.Table

    ID         mysql.ColumnString
    SomeColumn mysql.ColumnString
}

Generating a query using the .EQ method on SomeTable.ID would result in a query that looks like: select .... whereid= '0000-0000-.....'; mysql.UUID also appears to be just a wrapper that takes a fmt.Stringer and generates the same output as the .EQ method.

To get the desired output, we can use something like this:

id := "00000-00000-....."
expr := mysql.Func("uuid_to_bin", mysql.String(id))

statement := table.SomeTable.SELECT(table.SomeTable.AllColumns).
        WHERE(table.SomeTable.ID.EQ(mysql.StringExp(expr)))

Would it make sense for the library to add a couple of utility functions to shorten this? For example, functions.go could have these:

func UUIDToBin(str fmt.Sringer) StringExpression { ... }

func StringUUIDToBin(str string) StringExpression { ... }

With this, the user code can be shortened as:

id := "00000-00000-....."
statement := table.SomeTable.SELECT(table.SomeTable.AllColumns).
        WHERE(table.SomeTable.ID.EQ(mysql.StringUUIDToBin(id)))

[houten11]

Would CAST work in this case?

statement := table.SomeTable.SELECT(table.SomeTable.AllColumns).
        WHERE(table.SomeTable.ID.EQ(CAST(String(id)).AS_BINARY())

[realbucksavage]

@houten11 the query produced by using CAST doesn't work:

SELECT *
FROM testdb.some_table
WHERE some_table.id = CAST('00000000-0000-0000-0000-000000000000' AS BINARY);