When we create a scheduled event in MySQL, the current sql_mode is stored with the event. That causes the event to use the sql_mode that was in effect at the time the event was created. Same with altering an event.
Therefore, if we change the sql_mode after the event was created or altered, it won’t have any impact on the event. In other words, we can safely change our system’s sql_mode without worrying about whether it’s going to mess up any existing scheduled events.
MySQL provides us with the ability to create scheduled events. These are tasks that run according to a specified schedule.
Often referred to simply as events, scheduled events can be created with the CREATE EVENT statement, they can be modified with the ALTER EVENT statement, and they can be removed with the DROP EVENT statement.
In MySQL we can use the SHOW BINLOG EVENTS statement to return a list of events in the binary log. We can specify which binary log to use, or we can let it default to the first one.
In MySQL we can use the SHOW BINARY LOGS statement to return a list of binary log files on the server.
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.
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.
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.
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.
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.
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).