Additional string formatting for $wpdb->insert

I really like using the helper $wpdb class for inserts but occasionally I have a need to insert data using more than just %s, %d or %f format strings. Almost everyone will tell you to manually write your own INSERT statement and then just pass that to $wpdb->prepare() (which $wpdb->insert() does for you) but there is another way!

And it is actually documented in the code! Kind of. In wp-db on line 1307 (as of WP 3.7.1) it says:

A format is one of ‘%d’, ‘%f’, ‘%s’ (integer, float, string). If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.

The $wpdb has a member variable called $field_types which is an associative that you can use to override the formatting. To use it you cannot specify any formatting parameters when calling insert.

In my case, I had a need insert a GUID-like string into binary(16) column using the UNHEX() MySQL function.

global $wpdb;

//Set out override (anything not specified will be treated as %s)
$wpdb->field_types['transaction_id'] = "UNHEX('%s')";

//Call the normal insert
$wpdb->insert(
             $wpdb->payments,
             array(
                   'transaction_id' => $transaction_id,
                   'name'           => $name
                  )
             );

//Be polite, reset the array just in case future statements are run on this with the same column names
$wpdb->field_types = array();