In the last few days, I’ve started to look at the JSON column type that I can now leverage in MySQL 5.7.32. It’s pretty cool that MySQL now allows for JSON (JavaScript Object Notation) structures; but, the offering in MySQL 5.7.x isn’t as robust as it is in more specialized databases like MongoDB or Redis. That said, MySQL appears to provide most of the necessary building blocks in its initial implementation. And, one feature that I wanted to see if I could implement in MySQL 5.7.32 is the ability to atomically increment a JSON column value in Lucee CFML 5.3.7.47.

In databases like MongoDB and Redis, there are functions specifically designed for atomic maths. For example, Redis has an INCR key operation; and, MongoDB has an $inc operation. But, in MySQL 5.7.32 there’s really only getting and setting values using functions like JSON_EXTRACT() and JSON_SET(), respectively. However, since MySQL is inherently atomic around updates on a single row, we don’t have to worry about the transactional nature of our queries the way you might in a MongoDB or Redis database (which is partly why those database have so many specialized functions).

For example, in MySQL, you can perform atomic maths on a column in an UPDATE query by doing something as simple as:

columnValue = ( columnValue + 1 )

Since a SQL query can reference its own row values, we can atomically increment a column using the current column value right in our update statement.

To perform an atomic increment on a JSON value, I’m going to use the same exact approach. However, since the JSON structures are more complicated than a simple INT column value, our update statements are going to be quite a bit more complicated as well. But, again, the basic premise is the same.

#sql #mysql #json #lucee