As you may have noticed, MySQL 8.0.16 has been released today !
One of the major long expected feature is the support of
<a href="https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html">CHECK contraints</a> .
In this post, I wanted to show how we could take advantage of this new feature to validate JSON values.
Let’s take the following example:
So we have a collection of documents representing rates from a user on some episodes. Now, I expect that the value for the rating should be between 0 and 20.
Currently I could enter whatever value, even characters…
To avoid characters, I can already create a virtual column as integer:
So now, only integer value for
rating should be allowed:
Perfect, but can I enter any integer value ?
In fact yes of course ! And that’s where the new
CHECK Constraints enter in action !
We need first to modify the current document having a value for the
ratingattribute that won’t be valid for the new constraints.
And now we can test again:
Woohooo! Nice feature that also benefits to the MySQL Document Store !
For the curious that want to see how the table looks like in SQL definition:
Enjoy NoSQL with MySQL 8.0 Document Store #MySQL8isGreat.