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.

Continue reading

What is a Generated Invisible Primary Key (GIPK) in MySQL?

While primary keys are generally considered a necessity when it comes to relational databases, they’re usually included in the SQL code that creates the database table.

MySQL 8.0.30 introduced generated invisible primary keys which provide an alternative to explicitly specifying a primary key for a table.

A generated invisible primary key (GIPK) is a primary key that’s created implicitly by the MySQL server. If we create a table without an explicit primary key, the MySQL server automatically creates a generated invisible primary key for us (assuming it’s an InnoDB table and that GIPKs are enabled).

Continue reading

Quick Intro to SHOW CREATE DATABASE in MySQL

MySQL has a SHOW CREATE DATABASE statement that shows the CREATE DATABASE statement that would be used to recreate the database.

We can use the SHOW CREATE DATABASE statement to create an identical database on another server, or to create a similar database on the same server (while changing the name).

We can alternatively use SHOW CREATE SCHEMA to do the same thing (SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE).

Continue reading

What is a Composite Primary Key?

Primary keys are a crucial part of relational database management systems (RDBMSs). Most of the time we create a primary key constraint against a single column that is the unique identifier for the table.

But we can also create primary key constraints against more than one column. When we do this, we call it a composite primary key.

Composite keys can be handy when we don’t have a single column that contains unique values, but multiple columns can be combined to create a unique value.

Continue reading

A Quick Overview of the SHOW CREATE TABLE Statement in MySQL

In MySQL, we can use the SHOW CREATE TABLE statement to produce a CREATE TABLE statement from an existing table.

This enables us to generate a script that we can use to recreate the table on another database, or on the same database at a later date. Or we could use it to create another similar table. We would need to modify the code accordingly when doing this, but it could give us a good starting point.

We can also use SHOW CREATE TABLE for a quick way to take a look at the table’s structure, and check for things like data types, primary keys, foreign keys, etc.

Continue reading

A Quick Intro to the ‘sql_quote_show_create’ System Variable in MySQL

MySQL provides the sql_quote_show_create system variable, which allows us to specify whether or not to include quotes around identifiers when using the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

By default, this variable is set to 1, which means that quotes/backticks will be generated whenever these statements are run.

However, we might not always want quotes or backticks around the object names, and so we can change the value of this variable to disable quoted identifiers.

Continue reading

A Quick Overview of the MySQL DO Statement

In MySQL we can use the DO statement if we want to execute an expression without getting a result set.

DO works in a similar way to the SELECT statement, but without returning a result set like SELECT normally would. DO can also be slightly faster, given it doesn’t return a result set.

This could be useful in stored functions or triggers that don’t allow us to run statements that return a result set.

Continue reading

A Quick Overview of the REPLACE Statement in MySQL

In MySQL, we can use the REPLACE statement to insert data if we think that some rows need to replace existing rows.

The REPLACE statement works just like the INSERT statement, except that if it contains rows with the same PRIMARY KEY or UNIQUE index as those in the table, then it replaces those rows in the table. It does this by deleting the old row and then inserting the new row.

To use the REPLACE statement, we must have both the INSERT and DELETE privileges for the table.

Continue reading

An Introduction to the ‘explain_json_format_version’ System Variable in MySQL

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