Share eight MySQL traps that have to be said.

Share eight 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. Its great achievements show us that a successful company can be built on open source code.

However, those who used mysql once waved their fists at the display. However, you cannot develop a technology that can store thousands of Internet data lines per second and has no errors.

In this summer, we listed eight reasons for complaints about open-source relational databases. The reasons listed below are not limited to MySQL, but some are for relational databases. If we do not understand the relational database and MySQL, we will always fall into the idea of 1990s. We need to rebuild it. Or we can turn to a recently popular database that is not long enough to list for the same reason as below.

1. Deep-rooted bugs

Any major software package has bugs. However, if you have a little in-depth understanding, you will find that the Mysql-related bugs has its own system. Suddenly you need to pay attention, because NULL does not appear in the same way, foreign key constraints are not executed as you think, and even automatic growth of primary keys will also cause errors.

A large number of small problems exist, and they cannot always be fixed. That is why some people keep a list. Fortunately, MySQL maintains a very good bug reporting system, so that we can know what we cannot imagine and that others are suffering the same hardships.

2. Non-flexibility of Relational Tables

A relational table is organized and organized-however, it forces programmers to fabricate or forcibly insert some data into columns with defined patterns. One of the reasons NoSQL is becoming increasingly popular is that it provides programmers with sufficient flexibility to accelerate database usage. If a street address needs to add a line, you can easily insert it into a NoSQL document. If you want to add a complete new data block, no matter what it contains, the document model can still accept your data without changing it to the required data format.

Imagine that you have created a table in integer format that contains all zip codes. This table is very efficient and its execution rules are also good. Suddenly, someone uploaded a nine-digit ZIP code with a hyphen. Alternatively, you may have received a mail from a Canadian customer with a zip code.

At this moment, everything is messy. The boss asked the website to resume normal operation within several hours. However, there is no time to recreate the database. What can programmers do? Maybe, you can use hacker means to change the Canadian postal code from base64 digital format to base 10 format? Or set up an auxiliary table that uses escape encoding to describe the real zip code or others? Who knows? Hackers are everywhere. They are dangerous. But you don't have time to deal with it.

MySQL's association rules allow everyone to be honest and cautious, but they can force us to avoid the trouble of being vulnerable to attacks and spoofing.

3. JOIN query

Once upon a time, the preservation of data sub-tables was a great innovation in the history of computer science. The separated tables are not only simple in structure, but also simplified in use. However, it needs to use the join statement for query.

SQL queries built through a series of join queries push developers into the abyss of confusion and despair. In addition, the storage engine needs to efficiently parse join statements in the optimal way. Developers need to write query statements with their brains, and then the database will parse them.

This is why many developers who focus on running speed abandon data table sharding and use non-standard data tables. Save all data to a large table without differentiating data entities to avoid complex queries. This is indeed fast, and the server will not exhaust the memory.

Disk Space is cheap now. 8 TB disks are on sale, and larger disks will be available soon. We no longer need to worry about using join.

4. Branch chaos

Yes, a reliable and well-supported MySQL branch can bring about competition and choice, but it also causes confusion and confusion. Worse, a MySQL branch called MariaDB is maintained by Monty Widenius. He is also writing MySQL. So is MariaDB truly independent and worthy of our support? Or is it MySQL? Should we stick to the core code operated by the organization that created the original MySQL database? Or should we join those who are considered smarter and often cool-looking rebels?

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

5. Storage engine chaos

MySQL is not actually the same database; it consists of several databases, and most of their details are covered by the uniform surface. At the beginning, there was a MyISAM engine, which was fast but not complete in terms of consistency. Sometimes you need speed and can accept inconsistent results.

When people need more, InnoDB with full transaction support is emerging. But this is not enough. Now, it may have 20 storage engine options-this is enough to make a database administrator crazy. Of course, it is good to switch between different storage engines without rewriting your SQL statements, but it will always bring confusion after the switch. In this table, is the engine I selected MyISAM or innoDB? Or, do I decide to output the data in CSV format?

6. Profit motive

Although MySQL is a successful open-source product, it is still a business, full of professional developers who rely on it to earn salaries. When most users continue to enjoy the best experience from open-source licenses, there is no doubt that the company is still working to earn enough money to maintain its operation. This leads to a strange gap between the free code "community edition" and the entire product sold to the Enterprise.
Should you pay? How much have you earned here? Is it fair to carry out business operations on the Community edition? Is the additional features in the Enterprise Edition just a gimmick to lure us into continuous payment? This at least indicates that it is another group of questions to be answered. Which version is used? Which license does it comply? Which function set does it use?

7. Lack of native JSON support

The best way to look at MySQL's age 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. It generally outputs formatted data that is hard to understand. If you want your code to communicate with it, you must add another layer of code to convert the MySQL language into something useful. These layer codes are distributed in the form of libraries, and people often need to buy a commercial license.

The modern data storage layer usually communicates directly with JSON. Although MySQL and MariaDB are now able to parse the JSON part of SQL, it is far from good enough. Native JSON interfaces are widely used in CouchDB, MongoDB, or any latest tools.

8. Rise of closed sources and proprietary modules

Have I said that MySQL is open source? It is, but in addition to some newer, non-open-source code and proprietary modules developed around the "open-source core. Programmers need to eat, and Oracle needs to exchange money for its hard work, which is one of the commercial realities. Unlike hospitals, MySQL provides free medical care. Unlike farmers, MySQL can give away food.

It is unfair to require MySQL to always stick to a high standard, because the success of open source may be a trap. This is because it is free at first, but does not mean it can always be so. If enterprises require many new features, they will have to pay in one way or another. Sometimes it is much cheaper to pay for Oracle than to write your own code. Sometimes commercial and non-open-source code makes sense. The fact is self-evident.

Summary

The above is the eight MySQL traps that I have to say for everyone. I hope they will help you. If you have any questions, please leave a message and I will reply to you in time. Thank you very much for your support for the help House website!