PHP developers often make 10 MySQL errors. php developer mysql_PHP tutorial

PHP developers often make 10 MySQL errors. PHP developers often make 10 MySQL errors. the Source of the php developer mysql is kaiyuanba. You are welcome to share the original article with Bole Toutiao database, which is the basis for most WEB application development. If you are using p php, the php Developer often makes 10 MySQL errors.

Source: kaiyuanba

Databases are the basis for most WEB application development. If you use PHP, most databases use MYSQL, which is also an important part of the LAMP architecture.

PHP looks very simple. a beginner can start writing functions in just a few hours. However, building a stable and reliable database requires time and experience. The following are some such experiences, not only MYSQL, but also other databases.

1. use MyISAM instead of InnoDB
MySQL has many database engines, and MyISAM and InnoDB are generally used.

MyISAM is used by default. However, unless you build a very simple database or just experiment, most of the time this option is incorrect. MyISAM does not support foreign key constraints. this is the essence of ensuring data integrity. In addition, MyISAM locks the entire table when adding or updating data, which will cause great problems in future scalability.

The solution is simple: use InnoDB.

2. use the mysql method of PHP
PHP has provided the MySQL function library from the very beginning. Many programs depend on mysql_connect, mysql_query, mysql_fetch_assoc, and so on. However, it is recommended in the PHP manual:

If your MySQL version is later than 4.1.3, we strongly recommend that you use the mysqli extension.

Mysqli, or MySQL advanced extension, has some advantages:

• Object-oriented interfaces
• Prepared statements (preprocessing statements can effectively prevent SQL-injection attacks and improve performance)
• Supports multiple statements and transactions

In addition, if you want to support multiple databases, consider PDO.

3. do not filter user input
It should be: never trust user input. Use the backend PHP to verify and filter the information of each input. do not trust JAVAscript. The following SQL statements are vulnerable to attacks:

123	$username = $_POST["name"]; $password = $_POST["password"]; $sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; // run query...

If the user enters "admin"; ", the code is equivalent to the following:

1	SELECT userid FROM usertable WHERE username='admin';

In this way, the attacker can log on as admin without entering the password.

4, do not use UTF-8
Users in the United States and the United States seldom consider language issues, which leads to many products that cannot be used elsewhere. There are also some GBK encoding, and there will be a lot of trouble.

UTF-8 has solved many internationalization problems. Although PHP6 can solve this problem perfectly, it does not prevent you from setting the MySQL character set to UTF-8.

5. use PHP where SQL is used
If you are new to MySQL, you may first consider using a familiar language to solve the problem. This may cause some waste and poor performance. For example, the AVG () method of MySQL native is not applicable when calculating the average value. Instead, PHP is used to loop all values and accumulate the average value.

In addition, pay attention to the PHP loop in SQL query. Generally, it is more efficient to use PHP for loop after all results are obtained.

Generally, a powerful database method is used to process large amounts of data, improving efficiency.

6. query optimization
99% of PHP performance problems are caused by databases. a bad SQL statement may slow your entire program. MySQL's EXPLAIN statement, Query Profiler, and other tools can help you find the naughty SELECT statements.

7. incorrect data type
MySQL provides a series of data types, such as numbers, strings, and time. If you want to store dates, you can use the DATE or DATETIME type. using an integer or string will make things more complicated.

Sometimes you want to use your own data types, such as using strings to store serialized PHP Objects. It may be easy to add databases, but in this way, MySQL will become very heavy and may cause some problems in the future.

8. use * in SELECT query *
Do not use * to return all fields in the table, which is very slow. You only need to retrieve the required data fields. If you need to retrieve all the fields, your table may need to be changed.

9. insufficient or excessive indexing
In general, all the fields after WHERE should be indexed in the SELECT statement.

For example, assume that our user table has a number ID (primary key) and email address. After logging on, MySQL should find the corresponding ID through email. Through indexing, MySQL can quickly locate emails using search algorithms. If no index exists, MySQL checks each record until it is found.

In this case, you may want to add an index to each field. However, when you update or add an index, the index will be re-created, when the data volume is large, performance problems may occur. Therefore, only the required fields are indexed.

10. no backup
It may not happen often, but the database is damaged, the hard disk is broken, the service is stopped, and so on. these will cause catastrophic damage to the data. Therefore, make sure that the data is automatically backed up or the copy is saved.

11. In addition, do not consider other databases
MySQL may be the most widely used database in PHP, but it is not the only choice. PostgreSQL and Firebird are also competitors. they are both open-source and not controlled by some companies. Microsoft provides SQL Server Express and Oracle has 10 GB Express. These enterprise-level versions also have free versions. SQLite is also a good choice for small or embedded applications.

Source of original article: kaiyuanba welcome to share the original article with Bole Toutiao database, which is the basis for most WEB application development. If you use P...