Share 8 MySQL pitfalls that you have to mention

MySQL is easy to install, fast and has rich functions. It is also a benchmark for the open source movement, and its great achievements show us that a successful company can be built on open source code.

However, anyone who has used MySQL has shaken their fist at the monitor. But you can't invent a technology that can store thousands of lines of Internet data per second without any errors.

To help get you excited this summer, we've compiled eight reasons to complain about open source relational databases. The reasons listed below are not limited to MySQL, some of them are specific to relational databases. If we don't figure out relational databases and MySQL, we will be stuck in the 90s forever. 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 list a bunch of reasons like the following.

1. Deep-rooted bugs

Any large software package has bugs. But if you dig a little deeper, you'll find that MySQL-related bugs are a world unto themselves. Suddenly you have to watch out because NULLs don't appear the same way, foreign key constraints don't work the way you think they should, and even primary key auto-increment doesn't work the same way.

Small problems abound and can't always be fixed, which is why some people keep a list. Fortunately, MySQL maintains a very good bug reporting system, which allows us to know things that we can't imagine and know that others are experiencing the same ordeal.

2. Inflexibility of relational tables

Relational tables have structure, and structure is good—but it forces programmers to make up or shoehorn data into columns with defined schemas. 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 whole new block of data, no matter what it contains, the document model can accept your data intact without having to change it to the data format it requires.

Imagine you created a table of all postal codes in integer format. This table is very efficient and it enforces the rules very well. Suddenly, someone uploaded a nine-digit postal code with a hyphen. Or maybe you get a letter from a customer in Canada with a postal code on it.

At this point, everything was in chaos. The boss required the website to be back to normal operation within a few hours. However, there is no time to rebuild the database. What can a programmer do? Perhaps, it is possible to use a hack to change the Canadian postal code from base64 numeric format to base 10 format? Or set up a secondary table that uses escape encoding to account for real postal codes or something? 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 it can force us to avoid the trouble of being vulnerable to attacks and deception.

3. JOIN joint query

Once upon a time, storing 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 its use. But it needs to use join statement to query.

The complex queries constructed by SQL through a series of joins push developers into the abyss of confusion and despair. And the storage engine also needs to parse the join statement efficiently in the best 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 partitioning and use non-standard data tables instead. Do not distinguish between data entities and save all data into one big table - to avoid complex queries. This is really fast and the server won't run out of memory.

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

4. Branching confusion

Yes, a solid, well-supported fork of MySQL can bring competition and choice, but it can also cause confusion and chaos. Even worse, there is a fork of MySQL called MariaDB, maintained by Monty Widenius. He also helped write 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 the supposedly smarter, often cooler, betrayers?

Also, how should we obtain information about compatibility? On the one hand, we are assured that MariaDB and MySQL are very 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, in terms of performance and the scope of our query? But maybe they are different - or will be different in the future.

5. Storage engine confusion

MySQL is not actually a single database; it consists of several databases whose most details are obscured by a unified surface. In the beginning, there was the MyISAM engine, which was fast but not completely consistent. It's good sometimes when you need speed and can accept inconsistent results.

When people needed more, InnoDB with full transaction support came along. But that’s not enough. Today, it has perhaps 20 choices of storage engines—enough to drive a database administrator crazy. Sure, sometimes it's nice to be able to switch between storage engines without having to rewrite your SQL, but switching will always bring confusion. Should I choose MyISAM or innoDB as the engine for this table? Or, do I decide to output the data in CSV format?

6. Profit Motivation

Although MySQL is a successful open source product, it is still a business filled with professional developers who get paid to work with it. While most users continue to enjoy the best experience possible with open source licenses, there is no doubt that the company is still struggling to make enough money to stay afloat. This has led to a strange bifurcation of free code between "community editions" and full products sold to businesses.
Should you pay? How much money did you make here? Is it fair to operate on top of the community version? Are the extra features in the Enterprise Edition 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 choose?

7. Lack of native JSON support

The best way to see the age of MySQL is to install it, then realize that you need to add more drivers to make it usable. MySQL normally communicates on port 3306, and it typically outputs data in a format that it itself cannot understand. If you want your code to communicate with it, you have to add another layer of code to translate MySQL's language into something useful. The code for these layers, distributed as libraries, often requires 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 portion of SQL, this is far from good enough, and native JSON interfaces are already widely used in CouchDB, MongoDB, or any of the latest tools.

8. 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, proprietary modules developed around the "open source core". Programmers need to eat, and Oracle needs to make money from its hard work. This is one of the realities of business. It's not like those hospitals where medical care is free using MySQL. It's not like those farmers are giving away food using MySQL.

It's a little unfair to hold MySQL to a high standard, because open source success can be a trap. Just because it starts out free doesn't mean it will always be free. If businesses want lots of new features, they're going to have to pay for them one way or another. Sometimes it's much cheaper to pay Oracle than to write the code yourself. Sometimes commercial, closed source code makes sense. The facts speak for themselves.

Summarize

The above are 8 MySQL traps that I share with you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!