Home >Backend Development >PHP Tutorial >10 MySQL mistakes commonly made by PHP developers, mysql_PHP tutorial for php developers
10 MySQL mistakes commonly made by PHP developers, mysql_PHP tutorial for php developers
- WBOYOriginal
- 2016-07-13 09:56:30758browse
10 MySQL mistakes commonly made by PHP developers, php developer mysql
Original source: kaiyuanba Welcome to share the original to Bole Toutiao
Database is the basis for most WEB application development. If you are using PHP, then most databases use MYSQL, which is also an important part of the LAMP architecture.
PHP seems very simple, and a beginner can start writing functions within a few hours. But building a stable and reliable database does take time and experience. The following are some such experiences, not only MYSQL, but other databases can also be used for reference.
1. Use MyISAM instead of InnoDB
MySQL has many database engines, and generally only MyISAM and InnoDB are used.
MyISAM is used by default. But unless you are building a very simple database or just doing it experimentally, most of the time this is the wrong choice. MyISAM does not support foreign key constraints, which is the essence of ensuring data integrity. In addition, MyISAM will lock the entire table when adding or updating data, which will cause big problems in future expansion performance.
The solution is simple: use InnoDB.
2. Use PHP’s mysql method
PHP has provided the MySQL function library from the beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but the PHP manual recommends:
If the MySQL version you are using is after 4.1.3, it is strongly recommended to use the mysqli extension.
mysqli, or advanced extension for MySQL, has some advantages:
- Has object-oriented interface
- Prepared statements (prepared statements can effectively prevent SQL-injection attacks and improve performance)
- Supports multiple statements and transactions
Also, if you want to support multiple databases then you should consider PDO.
3. Do not filter user input
It should be: Never trust user input. Use back-end PHP to verify and filter each input information, do not trust JAVAscript. SQL statements like the following are easily attacked:
| 1 2 3 |
$username = $_POST["name"];
$password = $_POST["password"];
$sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; // run query...
|
Such code, if the user enters “admin’;”, then it is equivalent to the following:
| 1 |
SELECT userid FROM usertable WHERE username='admin';
|
In this way, the intruder can log in as admin without entering a password.
4. Do not use UTF-8
Those users in British and American countries rarely consider language issues, which results in many products that cannot be used in other places. There are also some GBK encodings that will cause a lot of trouble.
UTF-8 solves many internationalization problems. Although PHP6 can solve this problem more perfectly, it does not prevent you from setting the character set of MySQL to UTF-8.
5. Use PHP where SQL should be used
If you are new to MySQL, sometimes when solving problems, you may first consider using a language you are familiar with. This may cause some waste and poor performance. For example: when calculating the average, the MySQL native AVG() method is not used. Instead, PHP is used to loop through all the values and then accumulate them to calculate the average.
Also pay attention to PHP loops in SQL queries. It's usually more efficient to loop through PHP after all results have been obtained.
Generally, when processing large amounts of data, using powerful database methods can improve efficiency.
6. Not optimizing queries
99% of PHP performance problems are caused by the database. A bad SQL statement may make your entire program very slow. MySQL's EXPLAIN statement, Query Profiler, and many other tools can help you find those naughty SELECTs.
7. Using wrong data types
MySQL provides a series of data types such as numbers, strings, time, etc. If you want to store dates, use the DATE or DATETIME type. Using integers or strings makes things more complicated.
Sometimes you want to use your own data type, for example, using strings to store serialized PHP objects. Adding databases may be easy, but then MySQL becomes unwieldy and may cause problems later.
8. Use *
in SELECT query. Do not use * to return all fields in the table, which will be very slow. You only need to take out the data fields you need. If you need to remove all fields, then maybe your table needs to be changed.
9. Under-indexing or over-indexing
Generally speaking, all fields that appear after WHERE in the SELECT statement should be indexed.
For example, let’s say our users table has a numeric ID (primary key) and email address. After logging in, MySQL should find the corresponding ID via email. Through indexing, MySQL can quickly locate emails through search algorithms. Without an index, MySQL would need to check every record until it is found.
In this case, you may want to add an index to each field, but the consequence of this is that when you update or add, the index will be redone. When the amount of data is large, there will be performance problems. . Therefore, only index the required fields.
10. Not backing up
It may not happen often, but database damage, hard disk failure, service stoppage, etc., will cause catastrophic damage to the data. So you must make sure to automatically back up your data or save a copy.
11. In addition: other databases are not considered
MySQL may be the most commonly used database for PHP, but it is not the only choice. PostgreSQL and Firebird are also competitors. They are both open source and not controlled by certain companies. Microsoft provides SQL Server Express, Oracle has 10g Express, and these enterprise-level ones also have free versions. SQLite is also a good choice for some small or embedded applications.