Author: Ian

How to Select Data from an Invisible Column in MySQL

Posted on by Ian

From 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.

Continue reading →

How to Fix the “A table must have at least one visible column” Error in MySQL

Posted on by Ian

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 reading →

Why ‘FORMAT=TRADITIONAL’ Fails but ‘explain_format=TRADITIONAL’ Succeeds When Running EXPLAIN ANALYZE in MySQL

Posted on by Ian

If 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.

Continue reading →

How to Insert Data into an Invisible Column in MySQL

Posted on by Ian

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 reading →

An Introduction to the ‘explain_json_format_version’ System Variable in MySQL

Posted on by Ian

MySQL 8.3 introduced the explain_json_format_version system variable that allows us to specify which JSON output format should be used when we use the EXPLAIN statement with FORMAT=JSON.

It also applies when we omit the FORMAT argument and the explain_format system variable is set to JSON (because explain_format specifies the default format to be used by EXPLAIN).

Continue reading →

How the EXPLAIN Statement Works in MySQL

Posted on by Ian

In MySQL we can use the EXPLAIN statement to get information about SQL queries that we run against the database. We can also get information about tables and views.

EXPLAIN is a synonym for DESCRIBE (and its short form DESC). Even though these statements all work exactly the same, there’s a common convention amongst MySQL developers to use DESCRIBE for certain tasks and EXPLAIN for others. Typically, DESCRIBE is often used to get information about tables and views, whereas EXPLAIN is used to get information about queries, such as query execution plans.

Continue reading →

Fix “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format'” in MySQL

Posted on by Ian

If 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.

Continue reading →

How to “Unhide” an Invisible Column in MySQL

Posted on by Ian

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).

Continue reading →