Talk about 10 MySQL error _php tutorials that PHP developers often commit

Recently saw an article: "PHP developers often make 10 MySQL error", found that many of the content of the text is outdated, and over time, technology development changes and become inapplicable. In order to prevent misleading novice, special spirit with the spirit of advancing with the times to write this article, not to the original author of disrespect.

1. Use MyISAM instead of InnoDB
A complete error, a reason for refuting:
The original text says MyISAM is used by default, and in fact MySQL 5.5.x,innodb has become the default table engine.

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

The best way to do this is to deal with specific business, such as forum table, News classification table, various codes and other long-time non-operational tables, or use the MyISAM engine with excellent performance.
The need for transaction processing such as users, accounts, water and other stringent requirements of data integrity and time-series, you need to use the InnoDB engine, and the application should also use a good transaction processing mechanism. Of course, transaction processing is bound to bring a lot of performance loss, but this is necessary in simple and high concurrency applications.

Finally, foreign key constraints are generally not used on public web Internet applications, because they can severely affect performance. Data integrity is maintained by the programmer or by the robustness of the application architecture itself. And the formal third paradigm is only used in the enterprise internal MIS system and 12306 of such websites.

2. mysql method using PHP
Not entirely wrong, but choose as appropriate:
Mysqli is good, but not all servers have compiled mysqli support for PHP.
Mysqli is the best choice when your application is able to determine which servers are deployed solely on their own, and if the application is fully self-developed.
But once your application is likely to be deployed on a virtual host or deployed by someone else (such as a distributed project), use the MySQL function set properly, encapsulating it or using a mature framework to eliminate SQL injection.

3. Do not filter user input
Needless to say, it's either magicquote or a mature framework. SQL injected into the old topic.

4. Do not use UTF-8
Most of the cases, but also seriously consider:
To be sure, a UTF-8 character accounts for 3 bytes, so it is 33% larger than other encoded files such as GBK. In other words, the same Web page with UTF-8 encoding if it is 100KB, then GBK encoding is only 66KB. So even if your PHP is determined to use UTF-8, then the front-end page should choose the desired encoding according to the situation. However, if PHP with UTF-8, the front-end template is GBK, coupled with the template engine is not strong, then transcoding work enough for you to suffer. So as far as possible to choose the code you need, rather than the simple choice UTF-8.
The last wordy sentence: UTF-8: strlen ("i") = 3, while GBK under: strlen ("I") =2

5. Use PHP where SQL is used
Also consider, as appropriate:
For example, some people get used to the default value of Current_timestamp when building a table, which is used to achieve the effect of registration time and posting time. Or in a time-determined SQL statement, write similar to select X from Tab1 WHERE regdate < Unix_timestamp (). Then I can only say that you buried a very hidden bug in the system. Because databases and applications are often not on the same machine, time is prone to deviations. When the time reference for your set of applications is inaccurate, a big problem arises.
The right thing to do is to not use any of MySQL's time functions, but to calculate the time in the application. In the case of distributed applications, be sure to have a time server for unified management time.
And some of the MySQL mathematical functions, but also to use caution. Because in large-scale applications, the burden of the database is often the largest, and the complex where statement is the culprit of slow query. So, instead of the core database, try to keep the calculations as low as possible on inexpensive, globally stable application servers.

6. Do not optimize queries
Needless to say, large applications do not even allow the use of a variety of joins, even if you write two queries, check back in PHP to merge data.

7. Using the wrong data type
Int,tinyint,varchar,char,text The rational selection of these field types is understandable.
The three types of date, DateTime, and timestamp are absolutely not available in large-scale applications, but instead with int (ten) unsigned.
One is performance, the other is the application in particular PHP to unix_timestamp timestamp conversion is too convenient. Using date to output a variety of time formats instead of trouble.

8. Use in SELECT queries *
Share

9. Insufficient index or over-indexing
Indexes are necessary, but if the index is not able to solve the query, consider memcache or nosql solutions.

10. Do not back up
Is this the author dine?

11. Also: Do not consider other databases
This one is quite right. Not only does the application choose a different database for the application, it also uses multiple databases in parallel for the same set of applications, even for specific business types. Even if it is not a database, it is a variety of other solutions such as cache, memory storage.

http://www.bkjia.com/PHPjc/478437.html www.bkjia.com true http://www.bkjia.com/PHPjc/478437.html techarticle recently saw an article: "PHP developers often make 10 MySQL error", found that many of the content of the text is outdated, and over time, technology development changes and become inapplicable. To prevent misleading new ...

