Game Developers Analysis and Correction of 10 MySQL Mistakes Commonly Made by PHP Developers

1. Use MyISAM instead of InnoDB
Completely wrong, rebuttal reason:
First of all, the original article said that MyISAM is used by default, but in fact, by MySQL 5.5.x, InnoDB has become the default table engine.
In addition, simply using InnoDB is not a solution to all problems. Blind use may even reduce application performance by 10% or even 40%.
　The best method is to deal with specific businesses, such as forum section tables, news classification tables, various code tables, and other tables that are not operated for a long time. You still need to use the MyISAM engine with excellent performance.
　And those that need to use transaction processing, such as users, accounts, pipelines, etc., that strictly require data integrity and timing, need to use the InnoDB engine, and the application must also make good use of the transaction processing mechanism. Of course, transaction processing will inevitably bring a lot of performance losses, but this is necessary in simple high-concurrency applications.
Finally, foreign key constraints are generally not used in public web Internet applications because they will seriously affect performance. Data integrity still relies on programmers or the robustness of the application architecture itself to maintain it. The formal third paradigm is only used in corporate internal MIS systems and websites such as 12306.
2. Use PHP’s mysql method
It’s not completely wrong, but you should choose it as appropriate:
Mysqli is good, but not all servers have compiled mysqli support for PHP.
　When your application can be determined to only use servers deployed by yourself, and the application is completely developed by yourself, then mysqli is the best choice.
　But once your application is likely to be deployed on a virtual host or deployed by others (such as a distributed project), it is better to use the MySQL function set honestly, encapsulate it well or use a mature framework to prevent SQL injection.
3. Don’t filter user input
　 Needless to say, this is either MagicQuote or a mature framework. SQL injection is an old topic.
4. Do not use UTF-8
　Yes in most cases, but you must also consider carefully:
　You must know that one UTF-8 character occupies 3 bytes, so it is 33% larger than files in other encodings such as GBK. In other words, if the same web page is 100KB in UTF-8 encoding, it will only be 66KB in GBK encoding. So even if your PHP is determined to use UTF-8, the front-end page must choose the required encoding according to the situation. However, if PHP uses UTF-8, the front-end template is GBK, and the template engine is not powerful, then the transcoding work will be enough for you. So try to use the encoding you need instead of simply choosing UTF-8.
　Final remark: Under UTF-8: strlen("I")=3, and under GBK: strlen("I")=2
5. Use PHP where SQL should be used
　Similarly consider:
　For example, some People are accustomed to filling in CURRENT_TIMESTAMP as the default value when creating a table, which is used to achieve the effect of registration time and posting time. Or in the time judgment SQL statement, write something like SELECT x FROM tab1 WHERE regdate. The correct way is: do not use any time function of MySQL, but calculate the time in the application. If it is a distributed application, there must be a time server to manage time uniformly.
　 Some of the MySQL mathematical functions mentioned in the article should also be used with caution. Because in large applications, the burden on the database is often the greatest, and complex WHERE statements are the culprit of slow queries. Therefore, calculations should be placed as much as possible on cheap application servers that do not affect global stability, rather than on the core database.
6. Not optimizing queries
It goes without saying that large applications are not even allowed to use various JOINs. Even if you write two queries, you can use PHP to merge the data.
7. Using the wrong data type
　There is nothing wrong with the reasonable selection of field types such as INT, TinyINT, VARCHAR, CHAR, and TEXT.
　The three types of Date, DateTime, and TIMESTAMP must not be used in large applications. Instead, INT(10) UNSIGNED must be used instead.
　One is performance, and the other is that it is very convenient for applications, especially PHP, to convert UNIX_TIMESTAMP timestamps. It is troublesome to use Date to output various time formats.
8. Use * in SELECT queries
　Co-encourage
9. Inadequate or over-indexed indexing
　Indexing is necessary, but if the query cannot be solved by the index, consider memcache or nosql solutions.
10. No backup
Is this the author just making up the numbers?
11. In addition: other databases are not considered
This is quite correct. In applications, it is not only necessary to select other databases for the application, but also to use multiple databases in parallel in the same application for specific business types. Even if it is not a database, but other various caching, memory storage and other solutions.

The above introduces the correction analysis of 10 MySQL errors commonly made by game developers and PHP developers, including content for game developers. I hope it will be helpful to friends who are interested in PHP tutorials.