Summary of 11 common mistakes made by MySQL call novices

Preface

You may often receive warning emails from security departments about SQL injection, XSS attack vulnerabilities, etc., and occasionally your computer is hacked by hackers. Isn’t that frustrating? The database execution is too slow (according to experience, this is basically caused by incorrect use of indexes). Let's follow the editor to look at the 11 common mistakes made by MYSQL novices.

1. Use MyISAM instead of InnoDB

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

MyISAM is used by default. But unless you are setting up a very simple database or are just experimenting, 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 major problems in future expansion performance.

The solution is simple: use InnoDB.

2. Using PHP's mysql method

PHP has provided MySQL function libraries since the beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but

The PHP manual suggests:

If you are using MySQL version 4.1.3 or later, it is strongly recommended to use the mysqli extension.

mysqli, or MySQL Advanced Extensions, has some advantages:

Has an object-oriented interface

prepared statements (preprocessed statements can effectively prevent SQL-injection attacks and improve performance)

Support multiple statements and transactions

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

3. Not filtering user input

It should be: Never trust user input. Use backend PHP to verify and filter each input information, don't trust JAVAscript. SQL statements like the following are easily vulnerable:

$username = $_POST["name"]; 
$password = $_POST["password"]; 
$sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; // run query...

If the user enters "admin';" in this code, it will be equivalent to the following:

SELECT userid FROM usertable WHERE username='admin';

This way, the intruder can log in as admin without entering a password.

4. Not using UTF-8

Users in the United Kingdom and the United States rarely consider language issues, which results in many products not being usable in other places. There are also some GBK-encoded ones, which will also 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 the MySQL character set to UTF-8.

5. Use PHP where SQL should be used

If you are new to MySQL, you may sometimes consider solving a problem using a language you are familiar with. This may result in some waste and poor performance. For example, when calculating the average, the native MySQL AVG() method is not used. Instead, PHP is used to loop through all the values ​​and then add them up to calculate the average.

Also be careful with the PHP loop inside the SQL query. It is usually more efficient to loop over the results in PHP once all the results have been obtained.

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

6. Not optimizing queries

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, and many other tools can help you find those naughty SELECTs.

7. Using the wrong data type

MySQL provides a series of data types such as numbers, strings, and times. If you want to store dates, use DATE or DATETIME types. Using integers or strings will make things more complicated.

Sometimes you want to use your own data types, for example, to store serialized PHP objects in strings. It might be easy to add a database, but then MySQL becomes cumbersome and could cause problems later.

8. Use * in SELECT query

Do not use * to return all fields in a table, this will be very slow. You only need to extract the data fields you need. If you need to remove all fields, then perhaps your table needs to be altered.

9. Under-indexing or over-indexing

Generally speaking, all fields that appear after WHERE in the SELECT statement should be indexed.

For example, suppose our users table has a numeric ID (primary key) and email address. After logging in, MySQL should find the corresponding ID through email. With the index, MySQL can quickly locate emails through search algorithms. If there is no index, MySQL needs to check every record until it finds it.

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

10. Not backing up

It may not happen often, but database corruption, hard drive failure, service outage, etc. can all cause catastrophic damage to data. So you should always make sure to automatically back up your data or save a copy.

11. In addition: do not consider other databases

MySQL may be the most commonly used database for PHP, but it is not the only choice. PostgreSQL and Firebird are also competitors, both of which are 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. SQLite is also a good choice for some small or embedded applications.

Summarize

The above is the full content of this article. I hope that the content of this article can be of some help to everyone in learning or using MySQL. If you have any questions, you can leave a message to communicate.