In MySQL we can use the SHOW BINARY LOGS statement to return a list of binary log files on the server.
Author: Ian
Check Whether GIPKs are Enabled in MySQL
In MySQL, GIPK stands for generated invisible primary key. These are created whenever we create an InnoDB table without explicitly defining primary key.
However, MySQL will only create a GIPK if we have enabled GIPKs. More specifically, a GIPK will only be created when our sql_generate_invisible_primary_key server system variable is set to ON. By default this is set to OFF.
An Overview of SHOW COLLATION in MySQL
In MySQL we can use the SHOW COLLATION statement to return all collations supported by the server. By default it returns all available collations, but we can filter the output down to just those collations that we’re interested in.
Using REPLACE with the TABLE Statement in MySQL
When using the MySQL REPLACE statement to update another table based on a source table, we have the option of using a SELECT statement to select the whole table (or part of it) or the TABLE statement to select the whole table.
Below is an example of refreshing a table from another one using the TABLE statement with the REPLACE statement.
An Overview of the SHOW CHARACTER SET Statement in MySQL
In MySQL we can use the SHOW CHARACTER SET statement to return all available character sets. We can also use it to narrow the output down to just those we’re interested in.
A Quick Intro to SHOW INDEX in MySQL
In MySQL we can use the SHOW INDEX statement to return information about the indexes on a table.
We specify which table to show indexes from, and we can optionally specify which database. We can also use a WHERE clause to filter by various criteria.
We can run it against any table where we have some privilege for any column in the table.
Continue reading5 Ways to Find a Table’s Primary Key in MySQL
Generally speaking, most tables we create in MySQL should have a primary key. A primary key is one or more columns that have been configured as the unique identifier for the table.
We usually create the primary key when creating the table, but we can also add a primary key to an existing table (assuming it doesn’t already have one).
Occasionally it might not be immediately apparent which column is the primary key for a given table. Or it might not be apparent whether or not the table has a composite primary key. Fortunately it’s easy enough to find out.
Below are five ways to get the primary key column/s from an existing table in MySQL.
Continue readingA Quick Overview of the ‘show_gipk_in_create_table_and_information_schema’ System Variable in MySQL
Depending on our configuration, if we create a table without a primary key column, MySQL will automatically create one behind the scenes. This is called a generated invisible primary key (GIPK).
We can normally use statements such as SHOW CREATE TABLE, SHOW COLUMNS, SHOW INDEX or even check the information schema to see if a table has a GIPK.
But this depends on the setting of our show_gipk_in_create_table_and_information_schema system variable.
This variable can be set to ON or OFF. When set to ON, we will see GIPKs in the output of the aforementioned statements. When set to OFF, we won’t see any GIPKs.
Understanding MySQL’s ‘sql_generate_invisible_primary_key’ System Variable
One of the more recent additions to MySQL’s list of system variables is the sql_generate_invisible_primary_key variable. This variable was introduced in MySQL 8.0.30 along with the introduction of generated invisible primary keys (GIPKs).
The purpose of this variable is to allow us to specify whether or not the system will generate a GIPK when we omit a primary key from a table’s definition when creating the table.
By default the sql_generate_invisible_primary_key is set to OFF, which basically means that GIPKs are disabled by default. But we can change this to ON in order to enable GIPKs.
Using the ‘key_column_usage’ Table to Get the Primary Key and Foreign Keys in MySQL
In MySQL we can use the information_schema.key_column_usage table to get information about the keys in a table. This table returns one row for each column that is constrained as a key.
We can use this table to find out which column/s is the primary key of a given table and to return any foreign keys from the table.
Continue reading