Summary of 11 common mistakes made in mysql calls

For many novices, using PHP can easily write code with specific functions in just a few hours. However, building a stable and reliable database requires some time and skills. The following article summarizes eleven common mistakes made by novices calling mysql. Friends who need it can refer to it.

Preface

You may often receive warning emails from the security department about SQL injection, XSS attack vulnerabilities, etc. , and occasionally get tricked by hackers, are you depressed? In addition, the database execution is too slow (according to experience, it is basically caused by not using index correctly). Let's follow the editor to take a look at 11 common mistakes made by MYSQL novices.

1. Use MyISAM instead of InnoDB

MySQL has many database engines, and MyISAM and InnoDB are generally used.

MyISAM is used by default. But unless you are building a very simple database or just doing it experimentally, most of the time this is the wrong choice. 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 will cause big problems in future expansion performance.

The solution is simple: use InnoDB.

2. Use PHP’s mysql method

PHP has provided MySQL functions from the beginning Library. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but

the PHP manual recommends:

If the MySQL version you are using is in 4.1.3 After that, then it is highly recommended to use the mysqli extension.

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

It has an object-oriented interface

prepared statements statement, which can effectively prevent SQL-injection attacks and improve performance)

Supports multiple statements and transactions

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

3. Do not filter user input

## should be: Never trust the user enter. Use back-end PHP to verify and filter each input information, don't 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';", then it is equivalent to the following:

SELECT userid FROM usertable WHERE username='admin';

Like this The intruder can log in as admin without entering a password.

4. Do not use UTF-8

Those users in the United Kingdom and the United States are very Less consideration of language issues will result in many products that cannot be used elsewhere. There are also some GBK encodings that will cause a lot of trouble.

UTF-8 solves many internationalization problems. Although PHP6 can solve this problem more perfectly, it does not prevent you from setting MySQL's

character set to UTF-8.

5. Use PHP where SQL should be used

If you are new to MySQL, sometimes the problem can be solved Sometimes you may consider using a language you are familiar with first. This may cause some waste and poor performance. For example: when calculating the average, the MySQL native AVG() method is not used. Instead, PHP is used to

cycle all the values ​​and then accumulate them to calculate the average.

Also pay attention to the PHP loop in SQL

query. Often it's more efficient to loop through PHP after all results have been obtained.

Generally, when processing large amounts of data, using powerful database methods can improve efficiency.

6. Not optimizing queries

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

filer, and many other tools can help you find those naughty SELECTs.

7. Using the wrong data type

MySQL provides a series of numbers and characters Data types such as string, time, etc. If you want to store dates, use the DATE or DATETIME type. Using integers or strings makes things more complicated.

Sometimes you want to use your own data type, for example, use strings to store serialized PHP objects. Adding databases may be easy, but then MySQL becomes unwieldy and may cause problems later.

8. Use *

in the SELECT query. Do not use * to return all fields in the table. This It will be very slow. You only need to take out the data fields you need. If you need to remove all fields, then maybe your table needs to be changed.

9. Insufficient or excessive indexing

Generally speaking, the index should appear after WHERE in the SELECT statement All fields.

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

In this case, you may want to add an index to each field, but the consequence of this is that when you update or add, the index will be redone. When the amount of data is large, , there will be performance problems. Therefore, only index the required fields.

10. No backup

#It may not happen often, but the database is damaged, the hard disk is damaged, the service is stopped, etc. etc., these will cause catastrophic damage to the data. So you must make sure to automatically back up your data or save a copy.

11. In addition: other databases are not considered

MySQL may be the most used database for PHP, but Nor is it the only option. PostgreSQL and Firebird are also competitors. They are both open source and not controlled by certain companies. Microsoft provides SQL Server Express, Oracle has 10g Express, and these enterprise-level ones also have free versions. SQLiteIt is also a good choice for some small or embedded applications.

Summarize

The above is the detailed content of Summary of 11 common mistakes made in mysql calls. For more information, please follow other related articles on the PHP Chinese website!