PHP developers often make 10 MySQL errors

Source: Kaiyuanba Welcome to share the original to Bole headlines

Databases are the foundation of most web application development. If you are using PHP, then most of the database with MySQL is also an important part of the lamp architecture.

PHP looks simple, and a beginner can start writing functions in a few hours. But it takes time and experience to build a stable and reliable database. Here are some of the experience, not just MySQL, other databases can also be consulted.

1. Use MyISAM instead of InnoDB
MySQL has a lot of database engine, single general also use MyISAM and InnoDB.

The MyISAM is used by default. But unless you're building a very simple database or just experimenting, that choice is wrong most of the time. 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 the data, which will have a big problem in future extended performance.

The workaround is simple: use InnoDB.

2. mysql method using PHP
PHP provides a library of MySQL functions from the outset. Many programs rely on mysql_connect, mysql_query, MYSQL_FETCH_ASSOC, and so on, but the PHP manual suggests:

If you are using a MySQL version after 4.1.3, then it is strongly recommended to use the mysqli extension.

Mysqli, or MySQL's advanced extension, has some advantages:

• Object-oriented interfaces
• Prepared statements (preprocessing statements that 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 the user's input. Use the back-end PHP to check the filter information for each entry, and don't trust JavaScript. SQL statements such as the following can easily be attacked:

123	$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"; Well, that's the equivalent of this one:

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

So that the intruder can not enter the password, the admin as the login.

4, do not use UTF-8
Users in Anglo-American countries rarely consider language issues, which makes many products impossible to generalize elsewhere. There are also some GBK encoded, there will be a lot of trouble.

UTF-8 solves a lot of international problems. Although PHP6 can be a perfect solution to this problem, it does not prevent you from setting the MySQL character set to UTF-8.

5, the use of SQL place PHP
If you're new to MySQL, sometimes it's possible to solve problems by thinking about using the language you're familiar with. This can result in some waste and poor performance. For example, when calculating an average, the MySQL native avg () method is not applied, but instead it is used in PHP to cycle through all the values and accumulate the average value.

Also pay attention to the PHP loops in SQL queries. In general, it is more efficient to cycle with PHP after all the results have been obtained.

In general, the use of strong database methods when processing large amounts of data can improve efficiency.

6, do not optimize the query
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,many other tools can help you find those naughty select.

7. Using the wrong data type
MySQL provides a series of data types, such as numbers, strings, time, and so on. If you want to store the date, then using Date or datetime type, use shaping or string will make things more complicated.

Sometimes you want to use your own defined data type, for example, to store a serialized PHP object using a string. Database additions can be easy, but in this case, MySQL becomes cumbersome and can cause problems later.

8. Use in SELECT queries *
Do not use * to return all fields in the table, which can be very slow. You just need to take out the data fields you need. If you need to remove all the fields, your table may need to be changed.

9. Insufficient index or over-indexing
In general, you should index all of the fields that appear behind where in the SELECT statement.

For example, suppose our user table has a numeric ID (primary key) and an email address. After logging in, MySQL should find the appropriate ID via email. Through the index, MySQL can quickly locate the email through the search algorithm. Without an index, MySQL needs to check each record until it is found.

In this case, you may want to add an index to each field, but the result is that when you update or add it, the index will be re-done, and when the amount of data is large, there will be performance issues. So, just index the fields you need.

10, not backup
It may not happen very often, but the database is damaged, the hard drive is broken, the service stops, and so on, which can be disastrous for the data. So make sure you automatically back up the data or save the copy.

11, in addition: do not consider other databases
MySQL may be the most used database in PHP, but it's not the only option. PostgreSQL and Firebird are also competitors, and they are open source and not controlled by some companies. Microsoft provides SQL Server express,oracle with 10g Express, these enterprise-class also have free edition. SQLite is also a good choice for small or embedded applications.

PHP developers often make 10 MySQL errors