MySQL8.0 top10 new features

• MySQL document store
• The default utF8MB4 encoding
• JSON enhancement
• CTEs (Common Table Expresssions)
• The window function
• Descending index
• Better optimizer consumption model
• MySQL Server Component
• The promotion of Geographic Information System (GIS)
• InnoDB engine NO WAIT and SKIP \ LOCKED options

JiaZombie translated 4 months ago

By the Summer review

1. MySQL document storage

This is one of the most anticipated and popular features of MySQL 8.0… At the same time he is very easy to understand.

I am very excited about MySQL document storage, I have been showing it around the world for almost a year and have received a lot of good feedback. Why is MySQL DS so good? Because with one solution you can work with SQL and NoSQL. You can also combine the best of both languages. You can run CRUD commands on the same data, but you can also run more complex queries in SQL such as joining multiple tables and collections of and/or.

At the same time, the backend is known as the powerful InnoDB engine, MySQL document storage engine is fully compliant with ACID standard. Since it’s all inside MySQL, you can benefit from the content you’re familiar with and convert it to document storage: Replication, performance_schema,…

The default character set is UTF8MB4

With MySQL 8.0, we certainly focus on modern Web applications… That means mobile! When it comes to mobile, emoji and a large number of character sets and categories need to coexist.

This is why we decided to change the default character set from Latin-1 to UTF8MB4. MySQL supports the latest Unicode 9.0 new classification based on DUCET, accent and case sensitive classification, Japanese, Russian,…

Translated by BradStevens 4 months ago

By the Summer review

3. The JSON reinforcement

MySQL has brought some new JSON-related changes:

• JSON_UNQUOTE(JSON_EXTRACT()))
• New aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG()
• New JSON_PRETTY ()
• New JSON utility functions such as JSON_STORAGE_SIZE(), JSON_STORAGE_FREE()
• One of the most important improvements to JSON in MySQL 8.0 is the provision of a JSON_TABLE() function. This function takes data in JSON format and converts it into a relational table. Both field and data formats can be specified. You can also use normal SQL queries on data after JSON_TABLE(), such as JOINS, aggregate queries, etc… You can check out @Stoker’s blog post, and of course you can read the official documentation.

It is important to note that this does not only affect developer usage, but also MySQL performance. In the old system, when you update the JSON data, the old data is deleted and the new data is written. In the new system, if you want to update a field in the JSON data, the correct way is to update the field directly, which is more efficient. And database master-slave Replication performance also benefits.

4. Common table Expressions (CTEs)

Common Table Expresssions (CTEs) are new in MySQL 8.0. A CTE is a named temporary result set that exists only within the execution scope of a single SQL statement and can be self-referenced or referenced multiple times in the same query.

Translated by MRWang 4 months ago

By the Summer review

5. Statistical analysis methods

For each row in the query, a statistical analysis method performs a calculation using the row associated with that row. It’s like the GROUP BY method but it keeps the rows instead of collapsing them.

The following is a list of statistical analysis methods currently implemented in MySQL 8.0.4:

6. Descending index

Prior to MySQL 8.0, this flag was ignored when used in index definitions. Not anymore! Key values are now stored in descending order. Previously, indexes might have been scanned in reverse order, but performance suffered. Reverse indexes can be scanned sequentially, which is more efficient.

Translated by BradStevens 4 months ago

By the Summer review

7. Better optimizer overhead model

The new Optimizer Cost Model now calculates in-memory cache data as well as hard disk data. ø ystein’s blog post is recommended.

MySQL server module

You can use this feature to extend the functionality of MySQL server, which is much easier to develop and maintain than plug-ins. Read the official documentation.

9. Improvement of GIS

MySQL 8.0 has a very high level of support for Geographic Information System (GIS), which is almost as powerful as PostgreSQL.

Some examples:

• The axes are going to have units
• Geographic coordinate system
• The axes will not shift
• Coordinate axes support sorting
• Coordinate axes support orientation dependence

Translated by MRWang 4 months ago

By the Summer review

InnoDB engine NO WAIT and SKIP LOCKED

MySQL 8.0’s InnoDB engine now handles hot row contention better. InnoDB supports NOWAIT and SKIP\ LOCKED options with SELECT… FOR\ SHARE and SELECT… FOR\ UPDATE locks the read statement. NOWAIT returns the statement immediately if the requested row is locked by another transaction. SKIP LOCKED deletes LOCKED rows from the result set. See using NOWAIT and SKIP LOCKED to lock concurrent reads.

Of course, the list of good MySQL 8.0 features won’t end here. Support for regular expressions, for example, is also an interesting feature that just appeared in the [8.0.4] release (mysqlserverteam.com/the-mysql-8…). . New SQL GROUPING() feature, new services for IPV6 and UUID operations, more optimizer tips…

I hope this gives you a good overview of what kind of requests are driving innovation in MySQL. The next article will cover MySQL 8.0 features that should keep developers happy?

Lefred.be /content/top…