Let's talk about the 10 MySQL errors commonly made by PHP developers _ PHP Tutorial

Let's talk about the 10 MySQL errors that PHP developers often make. Recently, I saw an article titled 10 MySQL errors that PHP developers often make. I found that many of the articles are outdated and will become inapplicable over time due to technological changes. To prevent misleading new users

Recently, I saw an article titled 10 MySQL errors that PHP developers often make. I found that many of the articles are outdated and will become inapplicable over time due to technological changes. To avoid misleading new users, writing this article in the spirit of advancing with the times is not disrespectful to the original author.

1. use MyISAM instead of InnoDB
Completely false. counterargument reason:
In the original article, MyISAM is used by default. In fact, InnoDB has become the default table engine in MySQL 5.5.x.

In addition, the simple use of InnoDB is not a solution to all problems. blind use may even reduce the application performance by 10% or even 40%.

The best way is to deal with specific services, such as Forum tables, news classification tables, various code tables, and other tables that are not operated for a long time, or use the MyISAM engine with excellent performance.
For transactions that require strict requirements on data integrity and timing, such as users, accounts, and transactions, the InnoDB engine must be used, and applications must also use the transaction processing mechanism. Of course, transaction processing will inevitably bring about a lot of performance loss, but this is necessary for simple and high-concurrency applications.

Finally, foreign key constraints are generally not used in public web internet applications because they seriously affect performance. Data integrity is maintained by the robustness of programmers or application architectures. The formal third paradigm is only used on the enterprise's internal MIS system and 12306 websites.

2. use the mysql method of PHP
Not completely wrong, but you should choose as appropriate:
Mysqli is good, but not all servers have compiled mysqli support for PHP.
Mysqli is the best choice if your application is only deployed on the server and the application is fully developed on its own.
However, once your application may be deployed on a VM or by someone else (such as a distributed project), you can still use the mysql function set honestly, encapsulate it or use a mature framework to prevent SQL injection.

3. do not filter user input
Needless to say, either MagicQuote or mature framework is used. SQL injection is an old topic.

4. do not use UTF-8
In most cases, consider the following:
You know, a UTF-8 character occupies 3 bytes, so it is 33% larger than other encoded files such as GBK. In other words, if the same web page is encoded in UTF-8 for KB, then the change to GBK encoding is only 66KB. So even if your PHP is determined to use the UTF-8, the front-end page should also select the encoding as needed. However, if PHP uses UTF-8, the front-end template is GBK, coupled with the template engine is not powerful, then transcoding work enough for you. So as much as possible to choose the encoding you need, rather than a simple choice of UTF-8.
Last long-winded: Under the UTF-8: strlen ("I") = 3, while under the GBK: strlen ("I") = 2

5. use PHP where SQL is used
Consider the following as appropriate:
For example, when creating a table, some people enter CURRENT_TIMESTAMP by default to achieve the registration time and posting time. Or in the SQL statement for time determination, write the statement similar to SELECT x FROM tab1 WHERE regdate <UNIX_TIMESTAMP (). So I can only say that you have hidden bugs for the system. Because databases and applications are often not on the same machine, the time is prone to deviations. When the time of your application is inaccurate, there will be a big problem.
The correct method is: do not use any time functions of MySQL, but calculate the time in the application. For distributed applications, you must have time servers for unified management of time.
Some MySQL mathematical functions mentioned in this article should also be used with caution. In large applications, the burden on databases is often the largest, and complicated WHERE statements are the culprit of slow queries. Therefore, we should try our best to put computing on a cheap application server that does not affect global stability, rather than a core database.

6. query not optimized
Needless to say, large applications do not even allow various Joins. even if two queries are generated, PHP is used to merge data.

7. use the wrong data type
Reasonable selection of INT, TinyINT, VARCHAR, CHAR, and TEXT fields is understandable.
However, the Date, DateTime, and TIMESTAMP types cannot be used in large applications. Instead, they must be replaced by INT (10) UNSIGNED.
One is performance, and the other is that the conversion of UNIX_TIMESTAMP timestamps in applications, especially PHP, is too convenient. It is difficult to output time formats using Date.

8. use * in SELECT query *
Sharing

9. insufficient or excessive indexing
Indexes are required, but if indexes cannot solve queries, consider the memcache or nosql solution.

10. do not back up
Is this the number of authors?

11. In addition: do not consider other databases
This is quite correct. In an application, you must select other databases for the application, or even use multiple databases in parallel for the specific service type in the same application. It is not a database, but a variety of other cache, memory storage and other solutions.

Bytes. To prevent misleading new...