When we use the REPLACE statement in MySQL, we have the option of replacing the values of all columns in the row or just certain columns.
If we choose to replace just certain columns, we need to be aware of how this will affect the outcome. The “missing columns” in the title of this article refers to when we specify a value for just some, but not all columns.
Continue readingIf you’ve ever tried to copy a table with invisible columns in MySQL, you may have been baffled when you realised that none of the invisible columns were copied. Or, you may have copied them, but they ended up VISIBLE instead of INVISIBLE on the new table.
Fortunately, there’s an easy fix to this issue. Actually, there are at least a couple of ways we can approach it.
Continue readingFrom MySQL 8.0.23 we’ve had the ability to create invisible columns. But with that comes a potential “gotcha” with our existing queries, and even with new queries we write, depending on how we write them.
The issue is that invisible columns are not returned whenever we use the asterisk wildcard (*) to select data. One of the most common ways to start a query is with SELECT *. This typically selects all columns from the table.
But it doesn’t select invisible columns.
So if we want to include invisible columns, we need to explicitly include them in our SELECT list.
If you’re getting an error that reads “A table must have at least one visible column” in MySQL, it could be that you’re trying to create a table with nothing but invisible columns. Or you could be altering an existing column to be invisible, but it would leave the table with nothing but invisible columns.
To fix this issue, be sure to have at least one visible column in the table.
Continue readingIf you’ve been using EXPLAIN ANALYZE to analyse your MySQL queries, you may have encountered a little quirk regarding the output format: When we explicitly use FORMAT=TRADITIONAL in our EXPLAIN ANALYZE statement, we get an error. But when our explain_format system variable is set to TRADITIONAL we don’t get an error.
What’s going on with that? Is this a bug?
First up, no it’s not a bug. It can all be explained by the way EXPLAIN ANALYZE handles our explain_format setting.
Invisible columns allow us to hide columns from certain SQL queries, such as those that use the asterisk wildcard (*) to implicitly select all columns. But along with this comes some implications for when we want to insert data into those columns.
If we want to insert data into an invisible column, we need to explicitly name that column in the column list. We can’t use a blank column list to insert data into all columns like we can with visible columns. If we try to do that, we’ll get an error. That said, we can omit the invisible column from the column list in order to have its default value inserted.
Continue readingIf you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format’” in MySQL, it’s probably because you’re trying to run EXPLAIN ANALYZE with the wrong output format.
The EXPLAIN ANALYZE statement only supports the TREE output format. However, there are a few things to be aware of regarding this.
Either way, the quickest way to fix the error is to use FORMAT=TREE in your EXPLAIN ANALYZE statement.
If you’ve got a table with an invisible column in MySQL, but you no longer want the column to be invisible, you can use the ALTER TABLE statement to make it visible.
Invisible columns are columns that can’t be accessed by queries that use the asterisk wildcard (*) to select all columns (although they can be accessed by explicitly naming the invisible column in the SELECT list).
If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE FOR CONNECTION’” in MySQL, it’s probably because you’re trying to use the FOR CONNECTION option with EXPLAIN ANALYZE, but EXPLAIN ANALYZE doesn’t support the FOR CONNECTION option.
It’s true that we can use the FOR CONNECTION option with the EXPLAIN statement, we can’t use it when using the ANALYZE variant of EXPLAIN (we can use EXPLAIN with or without ANALYZE). At least, this is how it works as of this writing (MySQL 8.3).
To fix, either remove the ANALYZE keyword from the statement or don’t use the FOR CONNECTION option.