Shulou(Shulou.com)06/01 Report--

The following mainly brings you what you must pay attention to when using MySQL. I hope these contents can bring you practical use, which is also the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

1. Deep-rooted bugs

Any large software package has bug. But if you dig a little deeper, you will find that the bugs related to Mysql has its own system. Suddenly you need to pay attention, because NULL does not appear in the same way, foreign key constraints are not implemented as you might think, and even primary key auto-growth can go wrong.

There are a lot of minor problems, and they can't always be fixed, which is why some people keep a list. Fortunately, MySQL maintains a very good bug reporting system that allows us to know things we can't imagine and that other people are going through the same ordeal.

2. Inflexibility of relational tables

Relational tables are organized and well organized-but it forces programmers to fabricate or force some data into columns that have already defined schemas. One of the reasons why NoSQL is becoming more and more popular is that it provides programmers with enough flexibility to accelerate the use of databases. If a street address needs to add a line, 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 intact without having to change the data format it requires.

Imagine that you create a table full of zip codes in integer format. This table is very efficient and it enforces good rules. Suddenly, someone uploaded a nine-digit zip code with a hyphen. Or maybe you get a letter from a Canadian customer with a zip code on it.

At this time, everything was in a mess. The boss asked the website to return to normal work within a few hours. However, there is no time to rebuild the database. What can programmers do? Maybe we can use hackers 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 something? Who knows? There are hackers everywhere. They are all dangerous. But you don't have time to fix it.

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

3. JOIN federated query

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

Sql pushes developers into an abyss of confusion and despair through a series of complex queries built by join. And 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 parse them in the database.

This is why many developers who focus on running speed abandon data sub-tables and use non-standard data tables. Regardless of data entities, save all data in a large table-to avoid complex queries. This is really fast, and the cloud server will not run out of memory.

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

4. Confusion of branches

Yes, a reliable, 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 really 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 defectors who are thought to be smarter and often cool?

Also, how should we get 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 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. Storage engine confusion

MySQL is not actually the same database; it consists of several databases, most of which are masked by a unified surface. In the beginning, there was a MyISAM engine that was fast but not complete in terms of consistency. Sometimes it's good when you need speed and can accept inconsistent results.

When people need more, InnoDB with full transaction support appears. But that's not enough. Now, it may have 20 storage engine options-enough to drive a database administrator crazy. Of course, sometimes it's nice to switch between different storage engines without having to rewrite your SQL, but switching can always lead to confusion. Do I choose the engine MyISAM or innoDB for this table? Or, do I decide to export the data in CSV format?

6. Profit motivation

Although MySQL is a successful open source product, it is still a business full of professional developers who rely on it to get paid. 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 stay in business. This leads to strange differences in free code between the "community version" and the complete product sold to the enterprise.

Should you pay? How much money did you make here? Is it fair to run a business on the community version? Are the extra features in the enterprise version just a gimmick to tempt us to keep paying? This at least shows that it is another set of questions that need to be answered. Which version do you choose? What kind of license do you follow? Which feature set do you choose?

7. Lack of native JSON support

The best way to look at 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 is difficult for it to understand. If you want your code to communicate with it, you have to add another layer of code to convert the language of MySQL into something useful. The code for these layers, distributed in the form of libraries, often requires people to buy a commercial license.

Modern data storage layers usually communicate directly through 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 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 its hard work for money, which is one of the realities of business. Unlike those hospitals, MySQL provides free medical care. Unlike those farmers, it can give away food with MySQL.

It is a bit unfair to require MySQL to stick to a high standard all the time, because the success of open source can be a trap. This is because it can be free at first, but that doesn't mean it can always be so. If companies need a lot of new features, they will have to pay in one way or another. Sometimes it's much cheaper to pay Oracle than to write your own code. Sometimes commercial, non-open source code makes sense. The facts are self-evident.

For the above about what must be paid attention to when using MySQL, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.