Summary of the 11 errors that mysql beginners often make when calling mysql 11

Summary of the 11 errors that mysql beginners often make when calling mysql 11

Preface

You may often receive alerts from the security department, such as SQL injection, XSS attacks, and other vulnerabilities. You may also be down by hackers, so don't you feel depressed? In addition, the database execution is too slow (based on experience, the index is not properly used). Let's take a look at the 11 errors that MYSQL beginners often make.

1. Use MyISAM instead of InnoDB

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

MyISAM is used by default. However, unless you build a very simple database or just experiment, most of the time this option is incorrect. MyISAM does not support foreign key constraints. This is the essence of ensuring data integrity. In addition, MyISAM locks the entire table when adding or updating data, which will cause great problems in future scalability.

The solution is simple: Use InnoDB.

2. Use the mysql method of PHP

PHP has provided the MySQL function library from the very beginning. Many programs depend on mysql_connect, mysql_query, mysql_fetch_assoc, and so on. However

Suggestions in the PHP manual:

If your MySQL version is later than 4.1.3, we strongly recommend that you use the mysqli extension.

Mysqli, or MySQL advanced extension, has some advantages:

Object-oriented interfaces

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

Supports multiple statements and transactions

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

3. Do not filter user input

It should be:Never trust your input. Use the backend PHP to verify and filter the information of each input. Do not trust JAVAscript. The following SQL statements are vulnerable to attacks:

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

If the user enters "admin"; ", the code is equivalent to the following:

SELECT userid FROM usertable WHERE username='admin';

In this way, the attacker can log on as admin without entering the password.

4, do not use UTF-8

Users in the United States and the United States seldom consider language issues, which leads to many products that cannot be used elsewhere. There are also some GBK encoding, and there will be a lot of trouble.

UTF-8 has solved many internationalization problems. Although PHP6 can solve this problem perfectly, it does not prevent you from setting the MySQL character set to UTF-8.

5. Use PHP where SQL is used

If you are new to MySQL, you may first consider using a familiar language to solve the problem. This may cause some waste and poor performance. For example, the AVG () method of MySQL native is not applicable when calculating the average value. Instead, PHP is used to loop all values and accumulate the average value.

In addition, pay attention to the PHP loop in SQL query. Generally, it is more efficient to use PHP for loop after all results are obtained.

Generally, a powerful database method is used to process large amounts of data, improving efficiency.

6. Query Optimization

99% of PHP performance problems are caused by databases. A bad SQL statement may slow your entire program. MySQL's EXPLAIN statement, Query Profiler, and other tools can help you find the naughty SELECT statements.

7. Incorrect Data Type

MySQL provides a series of data types, such as numbers, strings, and time. If you want to store dates, you can use the DATE or DATETIME type. Using an integer or string will make things more complicated.

Sometimes you want to use your own data types, such as using strings to store serialized PHP objects. It may be easy to add databases, but in this way, MySQL will become very heavy and may cause some problems in the future.

8. Use * In SELECT query *

Do not use * to return all fields in the table, which is very slow. You only need to retrieve the required data fields. If you need to retrieve all the fields, your table may need to be changed.

9. Insufficient or excessive Indexing

In general, all the fields after WHERE should be indexed in the SELECT statement.

For example, assume that our user table has a Number ID (primary key) and email address. After logging on, MySQL should find the corresponding ID through email. Through indexing, MySQL can quickly locate emails using search algorithms. If no index exists, MySQL checks each record until it is found.

In this case, you may want to add an index to each field. However, when you update or add an index, the index will be re-created, when the data volume is large, performance problems may occur. Therefore, only the required fields are indexed.

10. No backup

It may not happen often, but the database is damaged, the hard disk is broken, the service is stopped, and so on. These will cause catastrophic damage to the data. Therefore, make sure that the data is automatically backed up or the copy is saved.

11. In addition, do not consider other databases

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

Summary

The above is all about this article. I hope this article will help you learn or use mysql. If you have any questions, please leave a message.