PHP Programmers most frequently commit 11 MySQL errors

For most Web applications, a database is a very basic part. If you're using PHP, you're probably also using the Mysql-lamp series as an important part.

For many beginners, using PHP can easily write code with specific features in just a few hours. However, building a stable and reliable database can take some time and related skills. Here are some of the 11 most serious mysql-related errors I've ever made (some are also reflected in the use of other languages/databases).

1. Use MyISAM instead of InnoDB

MySQL has a lot of database engines, but the most likely thing you'll encounter is MyISAM and InnoDB.

MySQL is using 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 transactional processing are important for data integrity, but MyISAM does not support these. In addition, when a record is inserted or updated, the entire data table is locked, which results in very poor operational efficiency when the usage increases.

The conclusion is simple: use InnoDB.

2. Using PHP's MySQL function

PHP provides MySQL library functions (or near as makes no difference) from the date of generation. Many applications still use functions like Mysql_connect, mysql_query, MYSQL_FETCH_ASSOC, and so on, although the PHP manual says:

If you are using MySQL v4.1.3 or later, it is highly recommended that you use the mysqli extension.

Mysqli (MySQL's enhanced extensions) has several advantages:

Optional object-oriented interface

Prepared expression, which helps to prevent SQL injection attacks and improves performance

Support for more expressions and transactional processing

In addition, if you want to support a variety of database systems, you can also consider PDO.

3. User input not processed

This can be said in this way: Never trust the user's input. Verify each string with server-side PHP, and do not send hope to JavaScript. The simplest SQL injection attack takes advantage of the following code:

$username = $_post["name"];

$password = $_post["password"];

$sql = "Select UserID from usertable WHERE username= ' $username ' and password= ' $password ';";

Run Query ...

Just enter "admin" in the Username field, – "So it will be black, the corresponding SQL statement is as follows:

SELECT userid from usertable WHERE username= ' admin ';

Cunning hackers can log in with the admin, they don't need to know the password because the password snippet is commented out.

4. No use of UTF-8

We seldom consider languages other than English in the United States, Britain and Australia. We are proud to have finished our "masterpiece" and found that they do not work in other places.

UTF-8 has solved many international problems. Although it is not well supported before PHP v6.0, it does not affect your MySQL character set to UTF-8.

5. Relative to SQL, prefer PHP

If you are in contact with MySQL soon, then you will be inclined to use the language you already mastered to solve the problem, which will lead to write some redundant, inefficient code. For example, if you don't use the AVG () function that comes with MySQL, you will first sum the values in the recordset and then use the PHP loop to calculate the average.

Also, be aware of SQL queries in the PHP loop. In general, executing a query is more efficient than iterating over the results.

Therefore, when analyzing the data, please take advantage of the database system, understand some of the knowledge of SQL will be beneficial.

6. Database query is not optimized

99% of the PHP performance problems are caused by the database, just a bad SQL query can make your Web application completely paralyzed. MySQL's explain statement, Query Profiler, and many other tools will help you find these evil select.

7. Data types are not used correctly

MySQL provides data types such as numeric, string, and date. If you want to store a time, use the date or datetime type. If this is the case with an integer or string type, then the SQL query will be very complex, if you can use an integer or string to define that type.

Many people tend to arbitrarily customize some data formats, such as using string to store serialized PHP objects. This may make it easier to manage the database, but it makes MySQL a bad data store and is likely to cause a malfunction later.

8. Use in Queries *

Never use * to return data for 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. Do not use indexes or over-use indexes

The general principle is this: the fields represented by any of the WHERE clauses in the SELECT statement should use the index.

For example, suppose we have a user table that includes the numeric ID (primary key) and the email address. When logging in, MySQL must find the correct ID based on an email. If you use an index (in this case, email), MySQL will be able to use a faster search algorithm to locate email, or even instant implementations. Otherwise, MySQL will only be able to check each record sequentially until it finds the correct email address.

Some people add indexes on each field, but unfortunately, these indexes need to be regenerated after the insert or update is executed, which can affect performance. So, just add the index when you need it.

10. Forget to back up

Although rare, the database still has a risk of crashing. The hard disk may be damaged, the server may crash, and the web hosting provider may go bankrupt! Losing MySQL data will be disastrous, so make sure you have used automatic backups or have replicated them 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 option. PostgreSQL and Firebird are the strongest contenders: Both are open source and not bought by the company. Microsoft provides SQL Server Express, Oracle offers 10g Express, both of which are free versions of enterprise databases. Sometimes, for a smaller web application or embedded application, SQLite is a viable alternative.

PHP Programmers most frequently commit 11 MySQL errors