What is new?
- Supports terabyte-sized databases even though it was created to handle 100M
- Supports SQL standards as new as SQL 2016
New Defaults and Variable in MySQL 8.0
- The default charset in MySQL 8.0 is utf8mb4
- That means you can have mathematical equations and emojis
- Binary log (log_bin) is enabled by default
- SHA-2 for authentification using SSL
- A mandatory default value for
MySQL id doing certain new things in the database world.
Transaction Scheduling (CATS vs FIFO)
Usually, databases use FIFO (First In First Out) for scheduling transactions, now MySQL 8.0 uses CAT (Conventional Aware Transaction Scheduling)
Other important changes include - Invisible Indexes (Not used by the optimizer) - Descending Indexes
Window functions, aka analytical functions, are functions which operate on a set of rows and return a single aggregated value. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.
Example of Windows Functions
- Enumerate rows,
- Show aggregated sums,
- Rank results,
- Look at neighboring rows,
CTE - Common Table Expressions
Allows us to create like a temporary table, with a capability to refer other tables and other CTEs. CTE is like a table but doesn't need a
CREATE privilege. CTEs can also be recursive and reference themselves.
CTEs simplify complex queries with complex joins and subqueries. A CTE can be used to:
Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.