8 must-tell MySQL pitfalls

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

However, anyone who has used mysql has waved their fist at the monitor. But you can't invent a technology that can save thousands of rows of Internet data per second without any errors.

To get excited this summer, we list 8 reasons to complain about open source relational databases. The reasons listed below are not limited to MySQL, some are specific to relational databases. If we don't figure out relational databases and MySQL, we will always be stuck in the thinking of the 1990s. We need to tear these down and rebuild them. Or we turn to a recently popular database that hasn't been around long enough to justify a bunch of reasons like the one below.

Deep-rooted bugs

Any large software package has bugs. But if you dig a little deeper, you will find that the bugs related to Mysql are self-contained. Suddenly you need to pay attention, because NULL doesn't appear the same way, foreign key constraints don't perform the way you think, and even primary key auto-increment goes wrong.

Small problems abound and aren't always fixable, which is why some people keep a list. Fortunately, MySQL maintains a very good bug reporting system, so that we can know things we can't imagine and know that others are going through the same ordeal.

Inflexibility of relational tables

Relational tables are organized, and organization is good - but it forces programmers to make up or shoehorn some data into a defined schema in the column. One of the reasons why NoSQL is becoming more and more popular is that it provides programmers with enough flexibility to speed up the use of databases. If a street address needs an additional row, you can easily insert it into a NoSQL document. If you want to add a complete new block of data, no matter what it contains, the document model can also accept your data unchanged without having to change it to the data format it requires.

Imagine that you create a table with all zip codes in integer format. This table is very efficient and the rules it enforces are very good. Suddenly, someone uploaded a nine-digit zip code that used hyphens. Or maybe, you get a letter from a customer in Canada with a postal code on it.

At this time, everything was in chaos. The boss demands that the website be back up and running within a few hours. However, there is no time to rebuild the database. What can programmers do? Perhaps, hackers can be used to change the Canadian postal code from base64 digital format to base 10 format? Or set up an auxiliary table that uses escape codes to indicate the real zip code or other? Who knows? There are hackers everywhere and they are dangerous. But you don’t have time to figure it out.

MySQL's association rules keep everyone honest and cautious, but they force us to avoid the trouble of being vulnerable and cheating.

JOIN Union Query

Once upon a time, saving data in separate tables was a great innovation in the history of computer science. The separated table not only has a simple structure, but also simplifies use. But it requires using join statement for query.

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

This is the reason why many developers who focus on running speed abandon data sub-tables and use non-standard data tables instead. Do not differentiate between data entities and save all data into one large table - to avoid complex queries. This is really fast and the server doesn't run out of memory.

Disk space is cheap nowadays. 8TB disks are already on sale, and larger ones are coming soon. We no longer need to rack our brains to use join.

The confusion of branches

Yes, a reliable, well-supported MySQL fork can bring competition and choice, but it can also cause confusion and confusion. To make matters worse, there is a fork of MySQL called MariaDB, 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 with the core code operated by the organization that created the original MySQL database? Or should we join in the often-cool defectors who are considered smarter?

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

Storage Engine Confusion

MySQL is not de facto the same database; it is composed of several databases, most of their details obscured by a uniform surface. In the beginning, there was a MyISAM engine, which was fast but not complete in terms of consistency. Sometimes it's good when you need speed and can accept inconsistent results.

When people needed more, InnoDB with complete transaction support appeared. But this is not enough. Now, it may have 20 storage engine choices - enough to drive a database administrator crazy. Of course, there are times when it's nice to switch between different storage engines without having to rewrite your SQL, but there will always be chaos after switching. Should I choose MyISAM or innoDB as the engine for this table? Alternatively, will the data I decide to output be in CSV format?

Profit Motive

Although MySQL is a successful open source product, it is still a business full of professional developers who get paid by it . While most users continue to enjoy the best experience brought by open source licenses, there is no doubt that this company is still struggling to make enough money to maintain operations. This leads to a strange bifurcation of free code between "community editions" and full products sold to enterprises.

Should you pay? How much money do you make here? Is it fair to operate on a community version? Are the extra features in the enterprise version just a gimmick to lure us into paying more? This at least shows that it is another set of questions that need to be answered. Which version to choose? Under which license? Which feature set should I use?

Lack of native JSON support

The best way to see MySQL age is to install it, and then you'll realize you need to add more drivers to make it usable. MySQL usually communicates on port 3306, and it typically outputs formatted data that it cannot understand. If you want your code to talk to it, you have to add another layer of code that translates the language of MySQL into something useful. The code for these layers is distributed as a library, often requiring people to purchase a commercial license.

Modern data storage layers often communicate directly in JSON. While 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 is already widely used in CouchDB, MongoDB, or whichever of the latest tools.

The Rise of Closed Source and Proprietary Modules

Did I mention MySQL is open source? It is, except for some newer, non-open source code and proprietary modules developed around the "open source core". Programmers need to eat, and Oracle needs to exchange the fruits of its hard work for money. This is one of the realities of business. It's not like those hospitals where you get free medical care using MySQL. It's not like those farmers who use MySQL to give away food.

It's a bit unfair to require MySQL to always hold itself to a very high standard, because open source success can be a trap. That's just because it can be free to begin with, but that doesn't mean it can always be that way. If businesses want many new features, they will have to pay for them one way or another. Sometimes it's cheaper to pay Oracle than to write the code yourself. Sometimes commercial, non-open source code makes sense. The facts speak for themselves.

The above are the content of 8 MySQL traps that have to be said. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!