Since MySQL 8.0.23 we’ve had the ability to create invisible columns, which are columns that are normally hidden to queries (but can easily be accessed if required).
With the introduction of invisible columns comes the inevitable requirement to check a table to see if it contains any invisible columns.
Fortunately there are many ways we can do this. Below are five ways to check a table for invisible columns.
In MySQL we can use the SHOW COLUMNS statement to get information about the columns in a table or view.
We can narrow the columns down to just a certain table and database. We can also filter by column name using a pattern.
The SHOW COLUMNS statement only shows information about columns that we have some privilege.
In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.
An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.
Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.
Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.
MySQL has the handy TABLE statement that allows us to get the contents of a table with less code than what we’d need if we’d used the SELECT statement.
When we use the TABLE statement, it returns the columns using the actual column names. In most cases this is fine, and there’s no need to change anything. But sometimes we might want to rename one or more columns. In such cases, we can use the following techniques to achieve that outcome.
When combining multiple query blocks in MySQL, we can use the EXCEPT clause to return just those rows from the first block that are not present in the second block.
If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.
When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.
To fix, make sure both queries select the same number of columns.
When combining multiple query blocks in MySQL, we can use the INTERSECT clause to return just those rows that are common to both query blocks.
It’s a bit like the UNION clause, except that it excludes rows that aren’t present in both queries.
MySQL 8.0.19 introduced the VALUES DML statement, which is a table constructor that can be used as a stand alone SQL statement.
This is not to be confused with the VALUES keyword that’s used with the INSERT or REPLACE statements when inserting data (although it can be used for that purpose too). It also shouldn’t be confused with the VALUES() function that’s used with INSERT … ON DUPLICATE KEY UPDATE statements.
MySQL has a LAG() function that we can use to return the value of a previous row of our choosing, based on how many rows back it is from the current row.
Armed with that value, we can then calculate the difference between it and the current row’s value.
MySQL has a bunch of functions that enable us to get values from another row in the same column. This makes it easy for us to do stuff like calculate the difference between a value in the current row and one in another row, even if it’s in the same column.
Here are five options for calculating the difference between a value in the current row and a value in a different row but in the same column.