Tag: how to

How to Hide a GIPK from the SHOW CREATE TABLE Statement (and SHOW COLUMNS and SHOW INDEX) in MySQL

Posted on by Ian

When we have a table with a generated invisible primary key (GIPK) in MySQL, we can usually see its definition when we use various SHOW statements such as SHOW CREATE TABLE, SHOW COLUMNS, and SHOW INDEX, as well as when we query information schema tables such as information_schema.columns.

But there is a way of hiding the GIPK from such statements. It all comes down to the show_gipk_in_create_table_and_information_schema variable. Yes, there’s actually a system variable that allows us to hide GIPKs from the output of various SHOW statements and information schema tables.

Continue reading →

5 Ways to Find a Table’s Primary Key in MySQL

Posted on by Ian

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

How to Set and Check the ‘sql_quote_show_create’ System Variable in MySQL

Posted on by Ian

In MySQL there’s a system variable called sql_quote_show_create which is used to determine whether or not to use quoted identifiers when using the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

By default sql_quote_show_create is set to 1, which means that quotes/backticks will be generated whenever these statements are run, but we can also change this value to 0 in order to disable quotes.

Below are examples of checking this variable and setting it.

Continue reading →

4 Ways to Fix the “Failed to generate invisible primary key. Column ‘my_row_id’ already exists” Error in MySQL

Posted on by Ian

If you’re getting an error that reads “Failed to generate invisible primary key. Column ‘my_row_id’ already exists“, it’s probably because you’re trying to create a table without a primary key, and you’ve named a column my_row_id.

When your system has generated invisible primary keys (GIPKs) enabled, and you create an InnoDB table without explicitly defining a primary key, MySQL automatically creates an invisible column called my_row_id and creates a primary key constraint against it. This is called a generated invisible primary key.

However, if you name one of your columns my_row_id, then MySQL can’t create the GIPK due to the column already existing.

There are several ways to go about fixing this issue.

Continue reading →

How to Create an Invisible Column in MySQL

Posted on by Ian

MySQL supports invisible columns (from MySQL 8.0.23), which means we can control whether a column is considered visible or invisible.

Invisible columns are hidden from queries that use the asterisk wildcard (*) in their SELECT list, but can be selected by explicitly naming the column.

We can create invisible columns when creating a table. We can also add invisible columns to existing tables. And we can modify existing columns to be invisible (and vice-versa).

Continue reading →