If you have an existing column in MySQL that you want to add a DEFAULT clause to, the following example may help.
To explicitly add a DEFAULT clause to an existing column in MySQL, we can use the ALTER TABLE statement along with the MODIFY clause.
We can also add a DEFAULT clause implicitly by defining a column as nullable. When we do this, MySQL implicitly adds its own DEFAULT clause.
In MySQL, the sys.extract_schema_from_file_name() function is a stored function in the sys schema.
It accepts a path name, and returns the path component that represents the schema name.
This enables us to get the schema from a given file path.
Continue readingIf we have a CHECK constraint that’s currently not enforced, we can easily change this so that it’s enforced.
We can use the following code to enforce a CHECK constraint in MySQL.
If you’re getting error 1138 that reads “invalid use of NULL value“, it could be because you’re trying to change the definition of a column to require non-NULL values, but the column contains NULL values.
This can happen when we try to change a column from NOT NULL to NULL. It can also happen when we try to set a column as a primary key, but that column contains NULL values.
To fix this issue, be sure to ensure that the column you’re modifying doesn’t contain any NULL values. Alternatively, if the column should be able to accept NULL values, then you should change your column definition to allow NULL values.
As of MySQL 8.0.16, we can create CHECK constraints in MySQL. We can create them at the time of creating the table, or we can add the constraint later.
To add a CHECK constraint to an existing table, we can use the ALTER TABLE statement.
If you’ve ever encountered a SERIAL column in a MySQL database, you might’ve wondered how it differs from an AUTO_INCREMENT column. After all, they both generate values that automatically increment?
Read on to find out what the difference is between SERIAL and AUTO_INCREMENT in MySQL.
MySQL provides us with a few utility functions that act on JSON values, or strings that can be parsed as JSON values.
Below are three JSON utility functions that we can use for such tasks.
Continue readingMySQL provides us with the WITH ROLLUP modifier for the GROUP BY function for generating super aggregate data.
But by default, this modifier doesn’t label its data. By this I mean, it doesn’t provide us with meaningful labels for the rows that it generates. It simply uses NULL in those rows.
Maybe you’ve encountered this and are now looking for a solution.
Below is a handy little technique we can use to replace NULL with our own headings for these rows.
In MySQL, the sys.format_statement() system function reduces the length of a given string/SQL statement to the length stored in the statement_truncate_len configuration option.
This function can be handy for truncating potentially long SQL statements retrieved from Performance Schema tables into a more suitable length (64 characters by default).
Continue readingWhen we create or modify a table in MySQL, we have the option of applying a CHECK constraint against one or more columns. This allows us to check the data before it enters the database. Data can only enter the database if it doesn’t violate the rules in our CHECK constraint.
If the column is a JSON column, we have the option of creating a CHECK constraint that checks that the JSON document doesn’t violate its JSON schema. To do this, we can include the schema in the CHECK constraint.