MySQL 9.0 Community Edition: Key Features & Enhancements

Explore the new features and improvements in MySQL 9.0 Community Edition, released on July 1, 2024. This comprehensive overview covers updates in data types, performance, and new capabilities aimed at modern applications.

Introduction

On July 1, 2024, MySQL released its 9.0 Community Edition, labelling it as an “Innovation Release.” This new version promises enhanced performance, flexibility, and introduces new capabilities that aim to meet the dynamic needs of modern applications. But does it live up to the hype, especially after the tumultuous reception of MySQL 8.0? Let’s dive into what MySQL 9.0 has to offer.

Key Features and Enhancements

Data Dictionary and Data Type Overhauls

One of the major updates in MySQL 9.0 is the restructuring of its data dictionary and refinements in data types. The data dictionary has been reorganized to handle metadata operations better and minimize the overhead associated with schema operations. Data type enhancements focus on better data validation and error reporting, particularly in data conversion operations involving incompatible types. This overhauling is crucial for applications using diverse international character sets, ensuring data integrity and consistency.

For more detailed information and examples, you can visit the Data Dictionary Notes and Data Type Notes sections.

New Vector Data Type

The introduction of the VECTOR data type in MySQL 9.0 marks a significant development. This feature allows users to efficiently store lists or arrays of floating-point numbers, which is especially beneficial in data analytics and scientific computations.

Here’s a basic example of how to use the VECTOR data type:

-- Creating a table with a VECTOR column
CREATE TABLE vector_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    values VECTOR(100)  -- The VECTOR column can hold up to 100 floating-point values
);

-- Inserting data into the VECTOR column
INSERT INTO vector_example (values) VALUES (TO_VECTOR('[1.0, 2.0, 3.0]'));

-- Retrieving data from the VECTOR column
SELECT id, VECTOR_TO_STRING(values) as vector_values FROM vector_example;

To explore more about this new feature, check out the detailed Vector Data Type section.

Event Scheduler and JavaScript Programs

MySQL 9.0 expands its event scheduler capabilities to support more complex operations, enhancing automation within databases. Additionally, it introduces support for JavaScript-based stored procedures and functions, enabling developers to leverage JavaScript’s flexibility for scripting database operations.

Here’s an example of a JavaScript stored function:

-- Creating a JavaScript stored function
CREATE FUNCTION gcd(a INT, b INT) 
RETURNS INT 
NO SQL 
LANGUAGE JAVASCRIPT AS
$$
    let x = Math.abs(a);
    let y = Math.abs(b);
    while(y) {
        let t = y;
        y = x % y;
        x = t;
    }
    return x;
$$;

-- Using the JavaScript stored function
SELECT gcd(48, 18);

For further reading on this topic, visit the JavaScript Programs section.

Performance and Stability Improvements

Bugs Fixed

MySQL 9.0 addresses a broad spectrum of bugs, improving the overall robustness of the database system. Among the 127 bugs fixed, notable improvements include the removal of outdated functionality specific to Fusion IO atomic write features and enhancements to the Group BY issue.

You can see a detailed list of bug fixes in the Bugs Fixed section.

Performance Schema Enhancements

Two new tables, variables_metadata and global_variable_attributes, have been added to the performance schema. These tables track system variables and attribute-value pairs assigned by the server to global variables. Such updates equip database administrators and developers with better tools for monitoring, troubleshooting, and tuning the performance of MySQL instances.

-- Example usage of the new Performance Schema tables
SELECT * FROM performance_schema.variables_metadata LIMIT 2;

SELECT * FROM performance_schema.global_variable_attributes LIMIT 2;

For more details, you can refer to the Performance Schema Notes.

Deprecated Features

MySQL 9.0 sees the deprecation of several features, preparing for their complete removal in future releases:

The mysql_native_password authentication plugin has been moved to a dynamically loadable plugin, signalling its eventual phase-out.
MIN_VALUE and MAX_VALUE columns of the Performance Schema variables_info table are now deprecated. Instead, use the variables_metadata table.
Insecure SHA-1 encryption has been removed, and the system now mandates caching_sha2_password for authentication.

For a comprehensive list of deprecated features, visit the Deprecation and Removal Notes section.

Summary

While MySQL 9.0 offers significant engineering improvements and several new features, it may fall short of the high expectations set by an “Innovation Release.” Practical performance improvements are yet to be fully validated through benchmarks. The evolutionary steps, rather than revolutionary changes, suggest that MySQL 9.0 is a solid upgrade for those wanting to stay current but might not compel those content with earlier versions to make an immediate switch.

Conclusion

MySQL 9.0 Community Edition brings crucial enhancements, including improved data management, the new VECTOR data type, and JavaScript programming support. While not revolutionary, it represents a steady evolution of MySQL, providing robust features for developers and administrators. For those seeking stability, waiting for further refinements or opting for LTS versions is recommended.

Keep the Conversation Going

I hope you found this post helpful! If you have any questions or feedback, feel free to reach out. You can also find me on X (Twitter) @priyashpatil for additional insights and updates on my latest content.