The opposite day, whereas recording a Working Code podcast episode, I discussed to Adam {that a} huge blind-spot in my database psychological mannequin was storing non-AUTO_INCREMENT main keys. Or, extra particularly, utilizing one thing like a UUID (Universally Distinctive Identifier), GUID, or CUID as a desk’s main key. As such, I wished to start out increase some foundational information. And, based mostly on what I have been studying, evidently with the ability to convert a UUID string to and from a binary worth is a crucial level of know-how. This put up seems to be at performing this String-to-Binary conversion in ColdFusion.
To be clear, I’m not a database skilled! Sure, I really like writing SQL. And sure, I really like considering deeply about database index design. However, I am not a kind of individuals who is aware of a lot about low-level storage particulars, engine ramifications, knowledge replication, or any of the numerous advanced matters that go into database administration. Contemplate this put up a note-to-self greater than something.
To begin studying about storing Strings as main keys, I did some studying:
From what I’ve seen in these articles – which is echoed in lots of StackOverflow posts – is that utilizing Strings as main keys is a trade-off: in return for having system-independent uniqueness, you incur bigger indexes, bigger working reminiscence, potential efficiency hits, much less intuitive values (pro-or-con relying on the way you see it), and extra advanced workflows.
This put up would not sort out all of these points – I am right here to noodle on simply considered one of them: bigger indexes. A part of the index-size challenge comes from how the worth is saved. If a UUID is a 35-character String, storing mentioned UUID as a String requires 35-bytes (1 byte per character).
And, that is only for the column worth itself. When you think about that the first key’s implicitly saved because the suffix on a secondary index, the storage necessities of a “UUID as String” is multiplied by the variety of indexes on the desk. To not point out that another desk utilizing mentioned UUID as a overseas key may also want 35-bytes.
A standard suggestion for lowering storage measurement is to persist the worth as a VARBINARY(16) as an alternative of a VARCHAR(35). This system is predicated on the truth that a UUID is already a HEX-encoded worth. As such, changing a UUID right into a Byte Array requires little greater than a binaryDecode() name.
Changing a binary worth again right into a UUID is a bit more work since we have now to re-insert the dashes (-) after we generate the String. This is two Person Outlined Capabilities (UDFs) that I created for managing this conversion in ColdFusion:
<cfscript>
/**
* I convert the given UUID string to a byte array (binary worth) to be used in a MySQL
* VARBINARY(16) database subject.
*/
public binary perform uuidToBinary( required string enter ) {
// The UUID string is already a hex-encoded illustration of knowledge. As such, to
// convert it to binary, all we have now to do is strip-out the dashes and decode it.
return(
binaryDecode(
enter.change( "-", "", "all" ),
"hex"
)
);
}
/**
* I convert the given MySQL VARBINARY(16) byte array (binary worth) to a ColdFusion
* UUID string.
*/
public string perform binaryToUuid( required binary enter ) {
var asHex = binaryEncode( enter, "hex" );
// ColdFusion UUIDs use the format: xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx.
return(
asHex.left( 8 ) & "-" &
asHex.mid( 9, 4 ) & "-" &
asHex.mid( 13, 4 ) & "-" &
asHex.proper( 16 )
);
}
</cfscript>
To strive these features out, I created a easy MySQL database desk that makes use of a VARBINARY primary-key and a worth column that shops the UUID in plain-text in order that we are able to verify values:
CREATE TABLE `uuid_test` (
`uid` varbinary(16) NOT NULL,
`worth` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Then, I generated 10,000 rows on this take a look at desk. Notice that in my INSERT, I am utilizing a CFQueryParam of sort binary for the primary-key column.
<cfscript>
// Embody are `uuidToBinary()` and `binaryToUuid()` UDFs.
embrace "./features.cfm";
loop instances = 10000 {
uid = createUuid();
```
<cfquery>
INSERT INTO
uuid_test
SET
uid = <cfqueryparam worth="#uuidToBinary( uid )#" sqltype="binary" />,
worth = <cfqueryparam worth="#uid#" sqltype="varchar" />
;
</cfquery>
```
}
</cfscript>
To then take a look at the SELECTing of rows, I seemed within the database desk, grabbed a UUID from about half-way via the desk, and used it to find the row. Discover that I am utilizing the uuidToBinary() to carry out the look-up; after which, I am utilizing the binaryToUuid() to devour the important thing in my ColdFusion code:
<cfscript>
// Embody are `uuidToBinary()` and `binaryToUuid()` UDFs.
embrace "./features.cfm";
// A UUID randoly picked from half-way via the information.
uid = "6D9F382A-5164-48EF-8DDEA942D5EAE8E3";
```
<cfquery identify="outcomes">
SELECT
t.uid,
t.worth
FROM
uuid_test t
WHERE
t.uid = <cfqueryparam worth="#uuidToBinary( uid )#" sqltype="binary" />
;
</cfquery>
```
dump( outcomes.uid );
dump( outcomes.worth );
// Use our customized features to transform the VARBINARY again to a String for consumption
// throughout the ColdFusion software.
dump( binaryToUuid( outcomes.uid ) );
</cfscript>
Once we run this ColdFusion (Lucee CFML) code, we get the next output:
As you may see, we had been in a position to find the row within the database utilizing the VARBINARY worth that we generated with uuidToBinary(). Then, we had been in a position to convert the binary worth again right into a ColdFusion-formatted UUID utilizing the binaryToUuid() perform.
And, if we run an EXPLAIN on this question, we are able to see that it makes use of the implicit primary-key index to search for a single-row with none desk scanning!
NOTE: I am utilizing MySQL’s
UNHEX()technique on this case since I am working thisEXPLAINproper within the database interface:
As you may see, this SQL question is utilizing the implicit main key (PKEY) index. And, is ready to find the one row utilizing the index with none desk scanning.
MySQL 5.7 vs MySQL 8
Whereas MySQL 5.7 ships with a perform to generate UUIDS (time-based, model 1), it leaves the string-to-binary conversions as much as the applying server. As of MySQL 8, nevertheless, the database engine now contains further features to carry out these conversions within the SQL context:
UUID_TO_BIN()BIN_TO_UUID()
For the time-being, I will be sticking with Integer-based AUTO_INCREMENT columns for my main keys. However, at the least I really feel like I am lastly beginning to construct up my psychological mannequin for what a String-based primary-key would possibly appear to be. The UUIDs that ColdFusion generates (random, model 4) incur further issues with regard to index construction and storage; however, that is a complete different subject (of which I do know little or no).
Need to use code from this put up?
Take a look at the license.
