PHP developers often make 10 MySQL errors

Database is the foundation of most web application development. If you're using PHP, then most databases use MySQL as an important part of the lamp architecture.

PHP looks very simple, and a beginner can start writing functions in a few hours. But it takes time and experience to build a stable, reliable database. Here are some of these experiences, not only MySQL, but also other databases can be referenced.

1. Use MyISAM instead of InnoDB

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

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

The solution is simple: use InnoDB.

2, the use of Php MySQL method

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 use the MySQL version after 4.1.3, it is strongly recommended that you use the mysqli extension.

Mysqli, or MySQL's advanced extensions, have some advantages:

Object-oriented interfaces

Prepared statements (preprocessing statements, can effectively prevent sql-injection attacks, but also improve performance)

Support for multiple statements and transactions

Also, if you want to support multiple databases, you should consider PDO.

3, do not filter user input

Should be: Never trust the user input. Use back-end PHP to verify that every input is filtered, and do not trust JavaScript. SQL statements like the following can easily be 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"; Well, it's the same as the following one:

SELECT userid from usertable WHERE username= ' admin ';

This way the intruder will be able to log in as admin without entering the password.

4, do not use UTF-8

Users in the UK and the United States rarely consider language issues, which makes it impossible for many products to be common elsewhere. There are some GBK coding, there will be a lot of trouble.

UTF-8 solves a lot of internationalization problems. Although PHP6 can solve this problem perfectly, it does not prevent you from setting MySQL's character set to UTF-8.

5. Use PHP where SQL is used

If you're new to MySQL, sometimes you might want to solve a problem by thinking about the language you're familiar with. This can result in some wasteful and poor performance situations. For example: When the average is calculated, the MySQL native avg () method is not applied, but it is used in PHP to loop through all the values and then calculate the average.

Also note the PHP loop in the SQL query. In general, it is more efficient to loop with PHP after all the results have been achieved.

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

6, do not optimize the query

99% of the PHP performance problems are caused by the database, a bad SQL statement may make your entire program is 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 range of data types such as numbers, strings, times, and so on. If you want to store dates, it's more complicated to use a date or datetime type, or a shape or string.

Sometimes you want to use your own defined data type, for example, to store the serialized PHP object with a string. Adding a database can be easy, but in this case MySQL becomes unwieldy and can cause problems later.

8. Use in select query *

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

9, insufficient index or excessive index

In general, you should index all the fields that appear in the SELECT statement after the where.

For example, if our user table has a number ID (primary key) and an email address. After logging in, MySQL should find the appropriate ID by email. By indexing, MySQL can quickly locate email via the search algorithm. If there is no index, MySQL needs to check every record until it is found.

In this case, you may want to add an index to each field, but the consequence is that when you update or add it, the index will do it again, and when the amount of data is large, there will be a performance problem. So, only the fields you want are indexed.

10, do not backup

It may not happen often, but the database is corrupted, the hard drive is broken, the service stops, and so on, which can cause catastrophic damage to the data. So you have to make sure that you automatically back up your data or save your 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 are not controlled by some companies. Microsoft offers SQL Server express,oracle with 10g Express, and these enterprise-class also have free edition. SQLite is also a good choice for some small or embedded applications.