Shulou(Shulou.com)06/03 Report--

PHP developers often make 10 MySQL mistakes, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. Use MyISAM instead of InnoDB

MySQL has a lot of database engines, and generally uses MyISAM and InnoDB.

MyISAM is used by default. But unless you are building 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 ensuring data integrity. In addition, MyISAM locks the entire table when adding or updating data, which is found in the

There will be big problems with extended performance in the future.

The solution is simple: use InnoDB. But I usually try MyISAM here.

2. Use the mysql method of PHP

PHP has provided MySQL's function library from the very beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but PHP hands

It is recommended in the book:

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

Mysqli, or the advanced extension of MySQL, has some advantages:

There are object-oriented interfaces

Prepared statements (preprocessing statements, which can effectively prevent SQL- injection attacks and improve performance)

Support for multiple statements and transactions

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

3. Do not filter user input

Never trust the user's input. Use the back-end PHP to verify and filter every piece of input, and don't trust Javascript. SQL statements like this 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';", is equivalent to the following:

SELECT userid FROM usertable WHERE username='admin'

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

4. Do not use UTF-8

Those users in Britain and the United States seldom consider the problem of language, so many products cannot be used in other places. There are also some GBK codes, and there will be a lot of trouble.

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

5. Use PHP where you should use SQL

If you are new to MySQL, sometimes you may consider using a language you are familiar with when solving a problem. This may lead to some waste and poor performance.

The situation. For example, instead of using MySQL's native AVG () method when calculating averages, PHP loops through all the values and then accumulates the averages.

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.

Generally, when dealing with a large amount of data, a powerful database method is used to improve the efficiency.

6. Do not optimize the query

99% of PHP performance problems are caused by the database, and a bad SQL statement can make your entire program very slow. EXPLAIN statement,Query of MySQL

Profiler,many other tools's tools can help you find those naughty SELECT.

7. Use the wrong data type

MySQL provides a range of data types for numbers, strings, times, and so on. If you want to store dates, use DATE or DATETIME types, plastic shapes or words

Strings make things more complicated.

Sometimes you want to use your own defined data types, for example, to use strings to store serialized PHP objects. It may be easy to add a database, but in that case, MySQL

It will become bulky and may cause some problems in the future.

8. Use * in SELECT query

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 take out all the fields, then maybe your table needs to

It's going to change.

9. Insufficient or excessive indexing

In general, the index should appear in all fields after WHERE in the SELECT statement.

For example, suppose our user table has a numeric ID (primary key) and email address. After logging in, MySQL should find the appropriate ID through email. By indexing, MySQL can

To quickly locate the email through the search algorithm. If there is no index, MySQL needs to check each record until it is found.

In that case, you may want to add an index to every field, but the consequence is that when you update or add, the index will be done again, when the amount of data is large.

There will be performance problems. So, index only the fields you need.

10. No backup

It may not happen often, but database corruption, hard drive failure, service outage, and so on, can cause catastrophic damage to the data. So you must make sure that the data is backed up automatically or

Save a copy of the.

After reading the above, have you mastered the 10 MySQL mistakes that PHP developers often make? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.