A huge leap in sql: MySQL 8.0 released

"Are you still using SQL-92?" "Is my opening question in" New SQL "speech. After I raised this question, the majority of the audience confessed that they were still using the technology 25 years ago. And if I ask who is still using Windows 3.1, and this version was released in 1992, only a handful of people raised their hands ... And they are obviously joking.

Obviously, this comparison is not fair. But it shows at least that the technology spread around newer SQL standards is rather lacking. There have actually been five updates since SQL-92-many developers have never heard of them. The latest version is sql:2016.

As a result, many developers do not know that SQL has been no longer confined to relational algebra or relational models since 1999. Sql:1999 introduced an operation that was not in relational algebra (lateral recursion) and broke the type of the traditional interpretation of the first paradigm (arrays! ）。

Since then, it has been no longer important for the SQL function to conform to relational thinking for 19 of years. It is important that a feature has well-defined semantics and solves a real problem. Academic methods have been put in a practical way. Today, the SQL standard provides a practical solution for almost all data processing problems. Some of them remain within the relational domain, while others do not.

Attention

Don't say relational databases when you're talking about SQL databases. SQL is actually more than just a relationship.

It's too bad that many developers still use SQL in the same way they used SQL 25 years ago. I think the main reason is the lack of knowledge and interest from developers, and the lack of support for new SQL in database products.

Let's look at the level of support for MySQL. Given its market share, I think MySQL's contempt for new SQL has largely led to this unfortunate situation. My blog post in 2013, "MySQL's impact on SQL is just as bad as MongoDB is for NoSQL." The key to the message is that "MongoDB is popular, but it doesn't have a big representative value in its class, just like MySQL." Joe Celko expressed his view of MySQL in a different way: "MySQL is not SQL, it just borrows the keyword from SQL".

You can see some examples of problematic SQL explanations in the MySQL Wat address on YouTube. Please note that this video is in 2012, using MySQL 5.5 (then GA version). After that, MySQL 5.6 and 5.7 came out, which greatly improved the situation. The default settings for the new installation system are much better now.

They're really thinking about how to mitigate the effect of changing the default value, which is especially good. For example, when Only_full_group_by is enabled by default, they spend more time implementing the most complete functional dependency checks between major SQL databases:

At the same time as MySQL 5.7 was released, I stopped attacking MySQL. Of course, I was joking. Occasionally I'm still attacking MySQL ... But it's not easy to attack from then on.

By the way, do you know that MySQL still does not support check constraints? As in previous versions, you can use CHECK constraints in the CREATE table declaration, but they are ignored. Yes, that's right--without warning, it's just ignored. Even the mariadb solved the problem a year ago.

Well, I was attacking again! Sorry-old habits are hard to change.

However, in the past few versions, MySQL's development philosophy has changed dramatically. What happened? You already know the answer: MySQL is under new management since Oracle acquired MySQL through Sun. I have to admit: in the last 10 years, this is probably the best thing that happens to SQL, and I mean SQL instead of MySQL.

I think the reason that a single database version can have a huge impact on the entire SQL ecosystem is simple: MySQL is the weakest link in the chain. If you strengthen this ring, the whole chain becomes more powerful. Let me give you a detailed explanation.

MySQL is very popular. According to Db-engines.com, it is the second most popular SQL database. More importantly: It is the most popular free SQL database. This has a big impact on anyone who has to use multiple, specific SQL databases. These are usually software vendors that manufacture Content management systems (CRM), e-commerce software, or object-relational mapper (ORM) products. Because of its popularity, these vendors often need to support MySQL. Only a few of them bite the bullet, really support multi-database--java Object oriented querying (JOOQ) is really prominent in this respect. Many vendors are limited to the usual supported SQL language, which is MySQL.

Another important group affected by MySQL is the person who learns SQL. They can reasonably assume that the most popular free SQL database is a good foundation for learning. What they don't know is that MySQL restricts sql-foo to the weakest SQL language in many SQL languages. Based on Joe Celko's statement: These people know the keywords, but don't understand what they really mean. To make things worse, they have not heard of modern SQL features at all.

Last week, when Oracle finally released a universally available version of MySQL 8.0, it all changed. This is a milestone version, because MySQL eventually transcends the SQL-92 and the purely relational dogma. In some other standard SQL functions, MySQL now supports window functions (over) and common table expressions (with). There is no doubt that this is the two most important post-sql-92 feature.

The days when software vendors claim that these features will not be available because they are not supported by MySQL are about to pass. window functions and common table expressions are also included in the documentation for the most popular free SQL database today. Let me be bold to claim that MySQL 8.0 is a small step forward in the database and a big step in SQL progress.

Everything is getting better, the future is bright! Since MySQL is already mastered by Oracle, MySQL's former team (which has the original creator) has created the MySQL branch mariadb. Obviously, their strategy is to add many new features to take MySQL users into consideration for their competing products. Personally, I think they're sacrificing quality-just like MySQL used to be-but that's another thing. Here, MARIADB has made the check constraint valid for a year, which is more substantial. This creates a problem: how long can MySQL continue to ignore check constraints? Or in other words, how long can they endure my criticisms;)

In addition to check constraints, MariaDB 10.2 introduces window functions and common table expressions (CTE). At that time, MySQL had a CTE beta, but no window functionality. MARIADB is rapidly improving.

In 10.3, MARIADB is set to publish the system versioning table. In short: Once the table is activated, system versioning retains the old version of the update and delete rows. By default, the query returns to the current version as usual, but you can use the special syntax (as of) to get the old version. You can read more about this in MARIADB's announcement.

System versioning was introduced in the SQL standard in 2011. Now it appears that MARIADB will be the first free SQL database to support it. I hope this is an incentive for other vendors--for users who require vendors to support updated SQL features!

Now that the adoption of the new SQL has finally been supported, there is only one problem: meticulous detail. The functionality defined by the standard has many sub-functions, and because of its large number, it usually supports only one part of it. This means that it is not enough to simply say that the database supports window functions. What kind of window function does it actually support? What is the unit? (Rows, permutations, or groups?) The answers to these questions distinguish between marketing gimmicks and really powerful features.

To make it easier for developers to use new SQL, I'm testing these details to highlight the differences between the products. The results of these tests are presented in the same form as above. The remainder of this article will briefly describe the new standard SQL features introduced in MySQL 8.0 and discuss some implementation differences. As you can see, MySQL 8.0 is very good in this respect. Notable exceptions are its JSON capabilities.

Window functions

SQL is divided into SQL before the window function and SQL after the window function. It is no exaggeration to say that the window function has changed everything. Once you understand the window functions, you can't imagine how you would live without them. The most common uses are for example finding the best n rows for each group, building a running total or moving average, and grouping successive events, just the tip of the iceberg. Window functions are one of the most important tools to avoid self-linking. This allows queries to be less redundant and faster. Window functions are so powerful that even new things like Apache SQL implementation (Hive,impala,spark), NuoDB and Google BigQuery introduced them years ago. So it was really late to join MySQL.

The table below shows support for some of the major SQL databases in the over clause. As you can see, as PostgreSQL claims on its new homepage, MySQL's implementation actually exceeds the functionality of "the world's most advanced Open source relational database". However, PostgreSQL 11 will regain the leadership position in this area.

The actual window function set provided by MySQL 8.0 is also very close to the existing technical level:

Common table expressions (with [recursive])

From selenium import Webdriver
From selenium.webdriver.common.by Import by
From Selenium.webdriver.common.keys import keys
From Selenium.webdriver.support import expected_conditions as EC
From selenium.www.dfzx157.com webdriver.support.wait Import webdriverwait
From pyquery import Pyquery as PQ
Import time
Import JSON

Options = Webdriver. Chromeoptions (www.yingka178.com)
# Set No graph mode
Prefs = {
' Profile.default_content_setting_values ': {
' Images ': 2
}
}
Options.add_argument ('--headless ') # Browser hidden
Options.add_argument ('--disable-gpu ')
Options.add_experimental_option (' prefs ', prefs) #设置无图模式
Browser = Webdriver. Chrome (chrome_options=options)
wait = webdriverwait (browser,www.meiwanyule.cn 5)

def get_last_page (QQ):
Try
url = f ' https://www.douniu157.com user.qzone.qq.com/www.dejiaylsmile.cn {qq}/311 '
Browser.get (URL)
Try
Browser.find_element (By.www.wmyl11.com NAME, ' login_www.leyouzaixian2.com frame ') # found the landing frame

The next major enhancement to MySQL 8.0 is a universal table expression or a with [recursive] clause. The important use case is to use a single query to traverse the graph, generate any number of rows, convert the CSV string to rows (invert listagg/group_concat), or read SQL.

MySQL's first implementation narrowed the gap once again.

Other standard SQL features

In addition to window functions and with clauses, MySQL 8.0 introduces some other standard SQL features. But compared with the previous two, this is not a killer.

As you can see, Oracle pushes the SQL standard to support JSON. Oracle database and MySQL are now leaders in this area (both from the same supplier!) ）。 The Json_objectagg and Json_arrayagg features are even compatible with MySQL 5.7.22. However, it is important to note that MySQL does not follow the standard syntax for both functions. modifiers defined in the standard, such as an ORDER BY clause, are generally not supported. Json_objectagg neither identifies the keyword key and value nor accepts a colon (:) to separate the property name from the value. It looks like MySQL will parse them into regular function calls--not the syntax of the standard description.

Equally interesting is that, like the Oracle database (which does not default to absent on null), Json_arrayagg seems to have an error handling null values. It's always interesting to see the same problem in two supposedly unrelated products. Plus the fact that the two products came from the same supplier added a turning point.

The last two functions in the list, the grouping function (related to rollup), and the column name of the FROM clause are the methods to resolve a specific problem. Their MySQL 8.0 implementations are basically consistent with other databases.

In addition, MySQL 8.0 introduces a standard SQL role. The reason why the table above is not listed is simple: The table is based on the actual tests I ran on all of these databases. The test framework I developed myself does not yet support test cases that require multiple users--all tests currently run with the default user, so I cannot test access rights. But I will gradually improve, please continue to pay attention.

Other notable enhancements

I want to end this article with the MySQL 8.0 patch and an improvement that is not related to the SQL standard.

One of these is about using the Desc modifier in an index declaration:

Most (if not all) databases use the same logic as the ORDER BY clause in index creation, that is, by default, the order of column values is ascending. Sometimes you need to sort some of the indexed columns in the opposite direction. You need to specify DESC in the index. Here's what the MySQL 5.7 documentation says:

Index_col_name specifications can end with ASC or DESC. These keywords can be used in future extensions to specify the ascending or descending order of the index value store. At the moment, they will be parsed but ignored at the same time; Index values are always stored in ascending order.

"Will they be parsed but ignored at the same time?" More specifically, they will pass the syntax analysis, but are ignored like the check constraint mentioned above without warning.

However, this has been resolved in MySQL 8.0. Now there is a warning. Just kidding! Desc is still very special now.

There are many other improvements to MySQL 8.0. See "What's New in MySQL 8.0". Other Articles to check:

• Histogram (Optimizer statistics) (https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html)

• The Data dictionary in InnoDB (see also End of MyISAM) (https://mysqlserverteam.com/atomic-ddl-in-mysql-8-0/)

• Recessive index (https://mysqlserverteam.com/mysql-8-0-invisible-indexes/)

• Improved default value (no more latin1_swedish_ci!) ) (https://mysqlserverteam.com/new-defaults-in-mysql-8-0/)

A huge leap in sql: MySQL 8.0 released