Summarize some MySQL pitfalls-Mysql Tutorial-php.cn

This time I will bring you a summary of some MySQL traps. What are the precautions when using MySQL? The following is a practical case, let's take a look.

MysqlInstallation is simple, 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, everyone who has used MySQL has waved their fists 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 then 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.

1. 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.

2. Inflexibility of relational tables

Relationship tables are organized, and organization is good - but it forces programmers to make up or force Stuff some data into a column with a defined schema. 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 extra 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.

3. JOINJoint 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 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.

4. Branch confusion

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 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.

5. Storage engine confusion

MySQL is not actually the same database; it is composed of several databases, and most of their details are covered by a unified surface. cover. 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?

6. Profit Motive

Although MySQL is a successful open source product, it is still a business full of professionals who rely on it to get paid Developer. 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?

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 It's available. MySQL usually communicates on port 3306, and it typically outputs formatted data that it has difficulty understanding. 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 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 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.

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the php Chinese website!