11 MySQL errors that most programmers often make

For most web applications, databases are a basic part. If you are using PHP, you are likely to be part of the MySQL-LAMP series. For many new users, using PHP can easily write code with specific functions in just a few hours. However, building a stable and reliable database requires time and related skills. The following lists the 11 most serious MySQL-related errors you have ever made (some errors are also reflected in the use of other languages/databases ).

1. Use MyISAM instead of InnoDB

MySQL has many database engines, but you are most likely to encounter MyISAM and InnoDB.
MySQL uses MyISAM by default. However, in many cases this is a bad choice unless you are creating a very simple or experimental database. Foreign key constraints or transaction processing are very important for data integrity, but none of MyISAM supports these. In addition, when a record is inserted or updated, the entire data table is locked. When the usage increases, the operation efficiency is very poor.
Conclusion: InnoDB is used.

2. Use the mysql function of PHP

The MySQL library function (or near as makes no difference) is provided on the date of generation of PHP ). Many applications still use functions such as mysql_connect, mysql_query, and mysql_fetch_assoc, even though the PHP manual says:
If you are using MySQL v4.1.3 or the latest version, we strongly recommend that you use the mysqli extension.
Mysqli (MySQL enhanced version extension) has the following advantages:
Optional object-oriented Interfaces
Prepared expression, which can prevent SQL injection attacks and improve performance.
Supports more expressions and transaction processing
In addition, if you want to support multiple database systems, you can also consider PDO.

3. directly use unprocessed user input data

Bill Gates, the famous Microsoft founder, once said: Never trust users' input. What Should php programmers do with this? It is to use the PHP program on the server to verify the input of each character. Do not send it to Java Script. The simplest SQL injection attacks use code similar to the following:

$ Username = $ _ POST ["name"];
$ Password = $ _ POST ["password"];
$ SQL = "SELECT userid FROM usertable WHERE username = '$ username' AND password =' $ password ';";

As long as you enter "admin"; -- "in the username field, it will be hacked. The preceding SQL statement will be interpreted as the following statement for execution:

SELECT userid FROM usertable WHERE username = 'admin ';

Hackers can log on to the system as admin. They do not need to know the password because the password segment is commented out.

4. UTF-8 not used

We seldom consider other languages except Chinese and English. We are proud to complete our "masterpiece", but we find that they cannot run normally elsewhere. UTF-8 solves many internationalization problems. Although it was not well supported before PHP v6.0, it does not affect setting the MySQL character set as a UTF-8.

5. PHP is preferred over SQL.

If you are not familiar with MySQL soon, you will prefer to use the language you have mastered to solve the problem, which will lead to writing redundant and inefficient code. For example, if you do not use the MySQL AVG () function, you will first sum the values in the record set and then use the PHP loop to calculate the average value. In addition, pay attention to SQL queries in PHP loops. Generally, executing a query is more efficient than performing iteration in the result.

Therefore, when analyzing data, please take advantage of the database system, understanding some SQL knowledge will be of great benefit.

6. database query not optimized

99% of PHP performance problems are caused by databases. A poor SQL query can completely paralyze your web applications. MySQL's EXPLAIN statement, Query Profiler, and many other tools will help you find these evil SELECT statements.

7. The data type cannot be correctly used.

MySQL provides data types such as numeric, string, and date. If you want to store a time, use the DATE or DATETIME type. If the INTEGER or STRING type is used at this time, the SQL query will be very complicated, provided that you can use INTEGER or STRING to define that type.

Many people tend to customize some data formats without authorization, such as using string to store serialized PHP objects. In this way, database management may become simpler, but MySQL may become a bad data storage and may cause faults.

8. Use * in Query *

Never use * to return data in all columns of a data table. This is laziness: You should extract the data you need. Even if you need all the fields, your data table will inevitably change.

9. No indexes or excessive use of Indexes

The general principle is as follows: indexes should be used for fields represented by any where clause in the select statement.

For example, suppose we have a user table, including numeric ID (primary key) and email address. During login, MySQL must find the correct ID based on an email. If an index is used (email here), MySQL can use a faster search algorithm to locate the email, or even implement it in real time. Otherwise, MySQL can only check each record sequentially until the correct email address is found.
Some people will add indexes to each field. Unfortunately, after performing INSERT or UPDATE, these indexes need to be re-generated, which will affect the performance. Therefore, you only need to add an index as needed.

10. Forget the backup!
 
Although rare, the database is still at risk of crash. The hard disk may be damaged, the server may crash, and the web host provider may go bankrupt! The loss of MySQL Data will be disastrous, so make sure that you have used automatic backup or copied it in place.

11. Bonus mistake-do not consider using other databases

For PHP developers, MySQL may be the most widely used database system, but it is not the only choice. PostgreSQL and Firebird are the strongest competitors: both are open-source and not acquired by companies. Microsoft provides SQL server Express and Oracle provides 10g Express, both of which are free versions of enterprise-level databases. Sometimes, SQLite is a feasible alternative for a small web application or embedded application.

Articles you may be interested in

• 9 important habits that can greatly improve work efficiency and time efficiency
• Why are there always errors between the programmer's estimated time and the actual project completion time?
• Differences between post-70, post-80, and post-90 programmers
• SEO issues that programmers should pay attention to when developing websites
• How php uses programs to implement pseudo-static
• Use php functions in the smarty template and how to use multiple functions for a variable in the smarty Template
• Self-recognition by a dad programmer
• Why can Most startups survive for five years?