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

This article focuses on "what are the differences in details between MySQL 5.7and MySQL 8.0". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the differences between MySQL 5.7and MySQL 8.0in detail".

Among the MySQL upgrade requirements over the years, one of the things that surprised me was that a big factor driving the upgrade of business from MySQL 5.5 to MySQL 5.7 was the JSON feature.

One of the main reasons for business concerns about upgrading from MySQL 5.7 to MySQL 8.0 is that the driver version is upgraded, so the overall motivation for upgrading from MySQL 5.7 to MySQL 8.0 is obviously lower, but one of the advantages of the planning is that we can put some work ahead or make its implementation smoother. For example, we follow the plan of MySQL 8.0 by default for the implementation of new business.

If you want to talk about some of the differences between MySQL 5.7 and MySQL 8.0, from my point of view, it has actually changed a lot, but taking a closer look, many features seem to be friendly or transparent support for the business.

Detail 1:

For example, we fully implement GTID in MySQL version 5.7, so the previous usage model of create table xxx as select * from xx does not work, so we recommend using:

Create table xxx like xxxxx; insert into xxx select * from xxxxx

This usage model, while many features brought by MySQL8.0 are in terms of experience and performance improvement, can actually be supported by a model that was not recommended to be used, while many of the business side is hindsight, and the habits that have already been cultivated make us a bit messy.

Details 2:

It is OK to call the field rank in MySQL 5.7. but in 8.0, because of the window function, the field is called rank. In fact, we take a look at the window function.

In fact, you will find that not only rank, the field name is first_value is not allowed, followed by SQL syntax errors, which may start to make people a little confused.

Create table test3 (id int primary key,first_value varchar (30))

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'first_value varchar (30))' at line 1

Details 3:

Here, by the way, complain about the table structure configuration of airflow

One of the table structures of airflow is as follows in MySQL 5.7:

CREATE TABLE kube_resource_version (one_row_id BOOL NOT NULL DEFAULT true, resource_version VARCHAR (255), PRIMARY KEY (one_row_id), CONSTRAINT kube_resource_version_one_row_id CHECK (one_row_id), CHECK (one_row_id IN (0,1) Query OK, 0 rows affected (0.06 sec) will actually be converted to the following table structure by default in MySQL: CREATE TABLE `kube_resource_ version` (`one_row_ id` tinyint (1) NOT NULL DEFAULT'1), `resource_ version`varchar (255) DEFAULT NULL, PRIMARY KEY (`one_row_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

If you view the actual data of the online business as follows:

Mysql > select * from kube_resource_version +-+-+ | one_row_id | resource_version | +-+-+ | 1 | | +-- -+ 1 row in set (0.01 sec)

It seems that this boolean type is really a bit of a chicken. Tinyint (1) has been used as an indirect escape by default in the database, but it still doesn't taste right.

The problem is that it can be created successfully in MySQL 5.7, but an error will be reported in 8.0:

CREATE TABLE kube_resource_version (one_row_id BOOL NOT NULL DEFAULT true, resource_version VARCHAR (255), PRIMARY KEY (one_row_id), CONSTRAINT kube_resource_version_one_row_id CHECK (one_row_id), CHECK (one_row_id IN (0,1)); ERROR 3812 (HY000): An expression of non-boolean type specified to a check constraint 'kube_resource_version_one_row_id'.

After analysis, in fact, 8.0 error prompt is more reasonable, at least I think 8.0 for the data level requirements have indeed become higher.

Details 4:

It's really embarrassing to delete a large table in MySQL, a bit of hindsight in MySQL 5.7in the output of show processlist. The State and Info columns show:

Executing event and delete from xxxxx

At the same time, the Seconds_Behind_Master is displayed as 0, in fact, the data has generated a lot of latency.

In contrast, in MySQL 8.0, the State and Info columns show:

Applying batch of row changes (delete) and delete from xxxxx

Can clearly indicate the batch operation, of course, this delay is really not decent, it is really very large.

Simple summary: many of the details in MySQL 8.0 are still approachable and cannot be subconsciously considered to be 100% compatible, and things that can be guaranteed by chest patting must be supported by in-depth testing and case studies.

At this point, I believe you have a deeper understanding of "what are the differences in details between MySQL 5.7and MySQL 8.0". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.