Testing BIGINT AUTO_INCREMENT Usage In MySQL 5.7.32 And Lucee CFML 5.3.8.201
For the most part, I live in an Integer world. Meaning, almost every numeric value that I deal with falls below 4.29 billion, which is the maximum value that can be stored in an unsigned integer. So, it's not surprising that I don't have a great mental model for what happens in ColdFusion and MySQL when we venture beyond that data-type maximum. That said, I have need of a MySQL table that uses BIGINT instead of INTEGER as its auto-incrementing primary key. And, I want to make sure that nothing will explode when I transport large numbers across the divide between MySQL 5.7.32 and Lucee CFML 5.3.8.201.
To experiment with this, I created a MySQL database table that uses a BIGINT as the auto-incrementing primary key column; and, I defined the table to start incrementing at a value that won't fit into an unsigned integer:
CREATE TABLE `bigint_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) NOT NULL,
`version` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5000000000 DEFAULT CHARSET=utf8;
Note that we are starting the increment at 5000000000 (5 billion).
To explore the use of such large numbers, I'm going to create a basic List-Detail demo app in which we can click-through from the list of records into the detail page for a given record. This will require me to pass the id value through the url scope, parse it, and then pass it into MySQL via the JDBC connector. MySQL will, of course, then pass the value back to me as a record and I'll play around with the returned value.
First, the index page that creates new records:
<cfscript>
param name="url.create" type="boolean" default=false;
if ( url.create ) {
```
<cfquery name="insert">
INSERT INTO
bigint_test
SET
value = <cfqueryparam value="Uniquely #createUniqueId()#" sqltype="varchar" />,
version = <cfqueryparam value="1" sqltype="integer" />
</cfquery>
```
}
```
<cfquery name="records">
SELECT
id
FROM
bigint_test
</cfquery>
```
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>
Testing BIGINT Usage In MySQL 5.7.32 And Lucee CFML 5.3.8.201
</title>
</head>
<body>
<h1>
Testing BIGINT Usage In MySQL 5.7.32 And Lucee CFML 5.3.8.201
</h1>
<p>
<a href="./index.cfm?create=true">Create a new record</a>
</p>
<ul>
<cfloop query="records">
<li>
<a href="./view.cfm?id=#records.id#">
View #records.id# →
</a>
</li>
</cfloop>
</ul>
</body>
</html>
</cfoutput>
There's nothing much worth noting in this page - we're just inserting new records and letting the AUTO_INCREMENT functionality do its thing. The more interesting part is the detail page in which I look up the record using the url.id query-string parameter:
<cfscript>
param name="url.id" type="numeric" default=0;
// Since all URL values are string values, let's convert the string URL parameter
// into a true numeric data-type.
url.id = val( url.id );
```
<cfquery name="record">
SELECT
id, /* BIGINT */
value,
version /* INTEGER */
FROM
bigint_test
WHERE
id = <cfqueryparam value="#url.id#" sqltype="bigint" />
</cfquery>
```
if ( ! record.recordCount ) {
location( url = "./index.cfm", addToken = false );
}
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>
Testing BIGINT Usage In MySQL 5.7.32 And Lucee CFML 5.3.8.201
</title>
</head>
<body>
<h1>
Record Detail
</h1>
<p>
← <a href="./index.cfm">Back to Home</a>
</p>
<ul>
<li>
<strong>ID:</strong> #record.id#
</li>
<li>
<strong>Value:</strong> #record.value#
</li>
</ul>
<h2>
<code>URL.id</code>
</h2>
<cfdump var="#isNumeric( url.id )#" />
<cfdump var="#numberFormat( url.id )#" />
<cfdump var="#getMetadata( url.id ).name#" />
<cfdump var="#( url.id + 1 )#" />
<h2>
<code>RECORD.id</code> (BIGINT)
</h2>
<cfdump var="#isNumeric( record.id )#" />
<cfdump var="#numberFormat( record.id )#" />
<cfdump var="#getMetadata( record.id ).name#" />
<cfdump var="#( record.id + 1 )#" />
<h2>
<code>RECORD.version</code> (INTEGER)
</h2>
<cfdump var="#getMetadata( record.version ).name#" />
</body>
</html>
</cfoutput>
The most basic test here is did anything explode; was I able to make the round-trip of data using a BIGINT instead of an INTEGER. Beyond that, we're also looking at what data-type is actually being used on the Java / Lucee CFML side; and, whether or not the value can be consumed with standard ColdFusion functions.
And, when we run this ColdFusion code, we get the following output:
The exciting news here is that nothing exploded! I was able to pass a large numeric value over the URL, parse it into a numeric value in Lucee CFML, pass it through to MySQL as a BIGINT, and then consume the returned column value a number back in the Lucee CFML context.
The only stand-out point of interest here is that the Query object represents the BIGINT value as a String. Compare this to the version column, which is an INTEGER and is represented as a Long in the Query object. I don't know where this data-type control-flow is being enforced - possibly in the MySQL Connector-j database driver? Possibly in the Lucee CFML code for the Query object? I'm not sure.
At the end of the day, however, I am pleased that using a BIGINT in a MySQL database table should, more or less, work seamlessly with my Lucee CFML code.
Want to use code from this post? Check out the license.
Reader Comments
Typo?
@Danilo,
Ah, yes, good catch! Will fix 🙌