10 MySQL errors that PHP developers often make
news source: cnw
database is the foundation of Web's most application development. If you use PHP, most databases use MySQL, which is also an important part of LAMP architecture. PHP looks very simple. A beginner can start writing functions in a few hours. But building a stable and reliable database requires time and experience. Here are some of these experiences, not just MYSQL, but also other databases.
1. uses MyISAM instead of InnoDB
MySQL. There are many database engines.
MyISAM is used by default. But unless you build a very simple database or just experimental, then most of the time this choice is wrong. MyISAM does not support foreign key constraints, which is the essence of data integrity. In addition, MyISAM will lock up the entire table when adding or updating data, which will have great problems in the future expansion performance. The
solution is simple: use InnoDB.
2. uses PHP's MySQL method
PHP to provide MySQL's function library from the beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, and so on, but the PHP manual suggests:
if the MySQL version you use is after 4.1.3, then it is strongly recommended to use mysqli extensions.
mysqli, or the advanced extension of MySQL, has some advantages:
prepared statements (preprocessing statements that can effectively prevent SQL- injection attack, but also improve performance)
support a variety of statements and things, if you want to support the majority According to the library, you should think about PDO.
3. does not filter user input
should be: never trust user input. Use the back end PHP to check and filter every input information, and don't trust Javascript. SQL statements like the following are easy to be attacked:
$username = $_POST[.quot; name.quot;
$password = $_POST[.quot; password.quot; Sample code, if the user enters "admin"; "then, it is equivalent to the following:
SELECT userid FROM usertable WHERE username=? Admin?;
so the invaders can not enter the password, and log in through the identity.
4. does not use UTF-8
users of the British and American countries, with little consideration for language problems, so that many products can not be used elsewhere. There are also some GBK codes that will have a lot of trouble.
UTF-8 solves many international problems. Although PHP6 can perfectly solve this problem, it does not prevent you from setting the MySQL character set to UTF-8.
5. should use PHP
PHP
if you are just in touch with MySQL, you may sometimes consider using your familiar language to solve problems. This may cause some waste and poor performance. For example, when calculating the average value, it does not apply to the MySQL native AVG () method, but uses PHP to circle all the values and accumulate the average.
also pay attention to the PHP loop in SQL query. Usually, it is more efficient to recycle PHP after obtaining all the results.
usually uses powerful database methods when dealing with large amounts of data, which can improve efficiency.
6. not optimizes query
99% 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 tools can help you identify those naughty SELECT.
7. uses the wrong data type
MySQL to provide a series of data types such as numbers, strings, time and so on. If you want to store dates, then using DATE or DATETIME type, using shaping or string will make things more complicated.
sometimes you want to use your own defined data type, for example, to store serialized PHP objects using strings. Database additions may be very easy, but in this case, MySQL will become cumbersome and may cause some problems later.
8. uses *
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 that you need. If you need to remove all fields, you may need to change your table.
9. index is insufficient or over indexed
generally, indexes should appear in all fields behind WHERE in SELECT statement.
for example, if our user table has a number of ID (primary key) and email address. After login, MySQL should find the corresponding ID through email. By indexing, MySQL can locate email quickly through search algorithm. If there is no index, MySQL will need to check every record until it is found.
you may want to add index to each field, but the consequence is that when you update or add, the index will redo it again, and when the amount of data is large, there will be a performance problem. So, only the fields you need are indexed.
10. does not backup
may not happen often, but database damage, hard disk, service stop and so on, which can cause disastrous damage to the data. So make sure you automatically backup data or save copies.
11. plus: without considering other databases,
MySQL may be the most used database for PHP, but it is not the only option. PostgreSQL and Firebird are competitors. They are open source and are not controlled by some companies. Microsoft provides SQL Server Express, Oracle has 10g Express, and these enterprise level also have free version. SQLite is also a good choice for some small or embedded applications.