10 MySQL Mistakes PHP Developers Commonly Make_PHP Tutorial

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 in 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 MyISAM and InnoDB are generally 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 MySQL function libraries 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 high-level extensions for MySQL, has some advantages:

Has object-oriented interface

prepared statements (preprocessed statements, which can effectively prevent SQL-injection attacks and improve performance)

Supports multiple statements and transactions

In addition, 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. Don't trust Javascript. SQL statements like the following are easily attacked:

$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:

SELECT userid FROM usertable WHERE username='admin';

In this way, the intruder can log in as admin without entering a password.

4. Don’t use UTF-8

Users from British and American countries rarely consider language issues, which results in many products that cannot be used elsewhere. 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 MySQL's character set 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 native MySQL 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. Often it's more efficient to loop through PHP after all results have been obtained.

Generally, using powerful database methods can improve efficiency when processing large amounts of data.

6. Not optimizing the query

99% of PHP performance problems are caused by the database. A bad SQL statement can 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 the wrong data type

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 defined 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, this 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, suppose 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. No backup

It may not happen often, but database corruption, hard drive failure, service outage, etc., can cause catastrophic damage to data. So you must make sure to automatically back up your data or save a copy.

11. Also: 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.