Share 8 MySQL traps that have to be said

Mysql is easy to install, fast and rich in functions. In addition, it is also the benchmark of the open source movement, and its great achievements show us that a successful company can be built on open source code.

However, people who have used mysql have waved their fists at the monitor. But you can't invent a technology that can hold thousands of rows of Internet data every second without a single error.

To get cranky this summer, we've listed eight reasons to complain about open source relational databases. The reasons listed below are not limited to MySQL, but one is for relational databases. If we don't sort out relational databases and MySQL, we'll be stuck forever in the '90s. We need to tear down and rebuild these. Or let's switch to a recently popular database that doesn't exist long enough to list a heap of reasons like the one below.

1. Deep-rooted bugs

Any large software package has bug. However, if you know a little more about it, you will find that bugs related to Mysql has its own system. Suddenly you need to pay attention, because NULL doesn't appear in the same way, foreign key constraints don't perform as you think, and even primary key auto-growth will go wrong.

Small problems abound and are not always fixable, which is why 1 people keep 1 list. Fortunately, MySQL maintains a very good bug reporting system, so that we can know something we can't imagine and know that other people are going through the same hardships.

2. Inflexibility of relational tables

Relational tables are organized, and organization is good--however, it makes programmers have to fabricate or hard-plug some data into columns where schemas have been defined. One of the reasons NoSQL is becoming more and more popular is that it gives programmers enough flexibility to speed up the use of databases. If you need to add a line to a street address, you can easily insert it into an NoSQL document. If you want to add a complete new data block, no matter what it contains, the document model can accept your data as it is, without having to change to the data format it requires.

Imagine that you have created a table with all zip codes in integer format. This table is 10-point efficient, and its rules are also very good. Suddenly, someone uploaded a 9-digit zip code with hyphens. Or maybe, you got a letter from a Canadian customer with a zip code on it.

At this time, 1 cut is in chaos. The boss asked the website to resume normal work within a few hours. However, there is no time to rebuild the database. What can programmers do? Perhaps the Canadian zip code could be changed from the digital format of base64 to the format of base 10 by hacking? Or set up an auxiliary table using escape codes to explain the real zip code or something? Who knows? There are hackers everywhere, and they are all dangerous. But you don't have time to fix it.

The association rules of MySQL make everyone honest and cautious, but it can force us to avoid the trouble of being vulnerable to attack and deception.

3. JOIN Joint Query

Once upon a time, saving data in tables was a great innovation in the history of computer science. The separated table is not only simple in structure, but also simplified in use. However, it needs to be queried using join statement.

The complex queries built by sql through series 1 of join push developers into the abyss of confusion and despair. Moreover, the storage engine also needs to parse join statements efficiently in an optimal way. Developers need to rack their brains to write query statements, and then the database parses them.

This is why many developers who focus on running speed give up data sub-tables and use irregular data tables instead. Save all data in a large table without distinguishing data entities-to avoid complex queries. This is really fast, and the server will not run out of memory.

Disk space is cheap now. The disks of 8TB are already on sale, and larger ones will be on the market. We no longer need to rack our brains to use join.

4. Chaos of branches

Yes, a reliable and well-supported MySQL branch can bring competition and choice, but it can also cause confusion and confusion. To make matters worse, a MySQL branch called MariaDB is maintained by Monty Widenius. He is also involved in writing MySQL. So, is MariaDB truly independent and worthy of our support? Or is it MySQL? Should we stick to the core code run by the organization that created the original MySQL database? Or should we join those traitors who are considered smarter and often cool?

Also, how should we get information about compatibility? 1, we are convinced that MariaDB and MySQL10 are similar. On the other hand, we have to believe that there is a difference-otherwise why is everyone arguing about it? Maybe they work the same way in both camps within the scope of performance and our query? But maybe they are different-or they will be different in the future.

5. The storage engine is chaotic

MySQL is not a de facto identical database; It consists of several databases, most of which are concealed by the surface of Unification 1. At the beginning, there was an MyISAM engine, which was fast but not complete in front and back. Sometimes it is good when you need speed and can accept the result without 1.

When people needed more, InnoDB with complete transaction support appeared. But that's not enough. Now, it might have 20 storage engine options--enough to drive a database administrator crazy. Of course, there are times when switching between different storage engines without having to rewrite your SQL is fine, but there will always be confusion after switching. Is the engine I chose for this table MyISAM or innoDB? Or, do I decide that the output data is in CSV format?

6. Motivation for profit

Although MySQL is a successful open source product, it is still a business full of professional developers who earn salaries from it. While most users continue to enjoy the best experience of open source licenses, there is no doubt that the company is still trying to earn enough money to maintain its operations. This leads to a strange divergence between the "community version" of Free Code and the complete product sold to enterprises.
Should you pay? How much money do you make here? Is it fair to conduct business on top of the community version? Is the extra function in the enterprise edition just a gimmick to entice us to keep paying? This explains at least one point, which is another group of questions to be answered. Which version to choose? What kind of license do you follow? Which feature set is selected?

7. Lack of native JSON support

The best way to see the age of MySQL is to install it, and then you will realize that you need to add more drivers to make it available. MySQL usually communicates on port 3306, and it generally outputs formatted data that it is difficult to understand. If you want your code to communicate with it, you must add another layer of code to convert the language of MySQL into something useful. These layers of code, distributed in the form of libraries, often require people to purchase a commercial license.

Modern data storage layers usually communicate directly with JSON. Although MySQL and MariaDB now have the ability to parse the JSON part of SQL, this is far from good enough, and the native JSON interface has been widely used in CouchDB, MongoDB, or any of the latest tools.

8. The rise of closed sources and proprietary modules

Did I say that MySQL is open source? It is, but apart from one of the newer, non-open source code developed around the "open source core", proprietary modules. Programmers need to eat, and Oracle needs to exchange its hard work for money, which is the first reality of business. Unlike those hospitals, MySQL can provide free medical care. Unlike those farmers, MySQL can give away food.

It is a bit unfair to ask MySQL to stick to a very high standard all the time, because the success of open source may be a trap. This is because it can be free at first, but it doesn't mean it can always be so. If businesses need many new features, they will have to pay in one way or another. Sometimes paying for Oracle is much cheaper than writing your own code. Sometimes commercial, non-open source code makes sense. The facts speak for themselves.

Summarize