Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the difference between Mysql8 and mysql5.7, I hope you will gain something after reading this article, let's discuss it together!

1. NoSql storage

Mysql provides NoSQL storage capabilities from version 5.7, and some changes have been made to this part in 8.0, but this is rarely used in practice.

two。 Hide index

The hidden index feature is very useful for performance debugging. In 8.0, the index can be hidden and displayed, and when an index is hidden, it will not be used by the query optimizer.

In other words, you can hide an index and observe the impact on the database. If the performance degrades, it means that the index is valid, so it can be "restored to display". If there is no change in the performance of the database, it means that the index is more than one and can be deleted.

Hide the syntax of an index

ALTER TABLE t ALTER INDEX i INVISIBLE

The syntax to restore and display the index is:

ALTER TABLE t ALTER INDEX i VISIBLE

When an index is hidden, we can see from the output summary of the show index command that the value of the index visible property is No

* * Note: * * when the index is hidden, its content is still updated in real time as the normal index. This feature itself is specially used for optimizing debugging. If you hide an index for a long time, you might as well kill it, because the existence of the index will affect the insert / update and delete functions of the data.

3. Set persistence

The settings for MySQL can be changed at run time through the SET GLOBAL command, but the change will only take effect temporarily, and the database will read from the configuration file the next time it starts.

MySQL 8 adds SET PERSIST commands, such as:

SET PERSIST max_connections = 500,

MySQL saves the configuration of the command to the mysqld-auto.cnf file in the data directory, which is read the next time it starts, and overwrites the default configuration file with the configuration.

4.UTF-8 coding

Starting with MySQL 8, the default encoding of the database will be changed to utf8mb4, which contains all emoji characters. For many years, we have to be careful in coding when we use MySQL, lest we forget to change the default latin and cause garbled problems. From now on, you don't have to worry.

5. Common table expressions (Common Table Expressions)

Complex queries use embedded tables, such as:

SELECT T1, T2 * FROM (SELECT col1 FROM table1) T1, (SELECT col2 FROM table2) T2

With CTE, we can write:

WITH T1 AS (SELECT col1 FROM table1), T2 AS (SELECT col2 FROM table2) SELECT T1, T2 * FROM T1, T2

In this way, it seems that the levels and areas are more clear, and it is more clear to know which part to change.

This feature is useful in many report scenarios and is an important feature of mysql optimization.

1.235 window function (Window Functions)

One of the most complained features of MySQL is the lack of the rank () function. When you need to rank in a query, you have to handwrite the @ variable. But since 8.0, MySQL has added a new concept called window function, which can be used to implement several new query methods.

The window function is a bit like a collection function like SUM () and COUNT (), but instead of merging the results of multiple rows of queries into one row, it puts the results back into multiple rows. In other words, window functions do not need GROUP BY.

Suppose we have a "class size" table:

If you want to rank the class size from small to large, you can use the window function like this:

Description: a window named w is created here, which requires it to sort the stu_count field, and then executes the rank () method on w in the select clause to output the result as a rank field.

This feature is also a new feature of Oracle11g and plays an important role in optimization.

After reading this article, I believe you have a certain understanding of "what's the difference between Mysql8 and mysql5.7". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.