Summary of the 11 most common MySQL mistakes made by PHP programmers_PHP Tutorial

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. Listed below are the 11 worst MySQL-related mistakes I have ever made (some of which are also reflected in the use of other languages/databases). . .
1. Use MyISAM instead of InnoDB
MySQL has many database engines, but the ones you are most likely to encounter are MyISAM and InnoDB.
MySQL uses MyISAM by default. However, this is a poor choice in many cases unless you are creating a very simple or experimental database. Foreign key constraints or transactions are very 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 will produce very poor operating efficiency when usage increases.
The conclusion is simple: use InnoDB.

2. Use PHP’s mysql function
PHP has provided MySQL library functions since the day it was created (or near as makes no difference). Many applications still use functions like mysql_connect, mysql_query, mysql_fetch_assoc, etc., although the PHP manual says:
If you are using MySQL v4.1.3 or newer, it is strongly recommended that you use the mysqli extension.
mysqli (an enhanced extension of MySQL) has the following advantages:
Optional object-oriented interface
prepared expressions, which helps prevent SQL injection attacks and improve performance
Supports more Multiple expressions and transaction processing
In addition, if you want to support multiple database systems, you can also consider PDO.

3. Not handling user input
This can be said like this #1: Never trust user input. Validate each string with server-side PHP, don't rely on JavaScript. The simplest SQL injection attack will use the following code:

Copy code The code is as follows:

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

As long as you enter "admin';--" in the username field, you will be hacked. The corresponding SQL statement is as follows:

Copy code The code is as follows:

SELECT userid FROM usertable WHERE username='admin';

A crafty hacker can log in as admin and they don't need to know the password because the password field is commented out.

4. UTF-8 is not used
We in the United States, the United Kingdom and Australia rarely consider languages ​​other than English. We proudly complete our "masterpieces" only to find that they don't work well elsewhere.
UTF-8 solves many internationalization problems. Although it is not well supported before PHP v6.0, this does not prevent you from setting the MySQL character set to UTF-8.

5. Prefer PHP over SQL
If you have been exposed to MySQL for a short time, you will tend to use the language you already master to solve problems, which will lead to writing some Redundant, inefficient code. For example, you wouldn't use the AVG() function that comes with MySQL, but you would first sum the values ​​in the recordset and then use a PHP loop to calculate the average.
Also, please pay attention to the SQL query in the PHP loop. Generally speaking, executing a query is more efficient than iterating through the results.
So, please take advantage of the database system when analyzing data. Knowing some SQL will be of great benefit.

6. Failure to optimize database queries
99% of PHP performance problems are caused by the database. Just one bad SQL query can completely paralyze your web application. MySQL's EXPLAIN statement, Query Profiler, and many other tools will help you find these evil SELECTs.

7. Inability to use data types 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 you use the INTEGER or STRING type at this time, it will make the SQL query very complicated, provided that you can use INTEGER or STRING to define that type.
Many people tend to customize some data formats without authorization, for example, using string to store serialized PHP objects. This may make the database easier to manage, but it makes MySQL a poor data store and is likely to cause failures 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. Not using indexes or overusing indexes
The general principle is this: any field represented by the where clause in the select statement should use an index.
For example, suppose we have a user table, including numeric ID (primary key) and email address. When logging in, MySQL must look up the correct ID based on an email. If an index is used (here, email), then MySQL can use a faster search algorithm to locate email, even instantaneously. Otherwise, MySQL can only check each record sequentially until it finds the correct email address.
Some people add indexes to each field. Unfortunately, these indexes need to be regenerated after executing INSERT or UPDATE, which will affect performance. So, add indexes only when needed.

10. Forgot to back up!
Although rare, there is still a risk of database crash. Hard drives can get damaged, servers can crash, and web hosting providers can go bankrupt! Losing your MySQL data would be catastrophic, so make sure you have automated backups in place or have copies in place.

11. Bonus mistake-not considering 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 contenders: both are open source and neither has been acquired by a company. Microsoft offers SQL server Express and Oracle offers 10g Express, both of which are free versions of enterprise-class databases. Sometimes, for a smaller web application or embedded application, SQLite can be a viable alternative.

The level is limited, welcome to bid! !
Original link: Top 10 MySQL Mistakes Made by PHP Developers
Ps: The original title was written as 10, but the author actually listed 11, so I made a small change

http://www.bkjia.com/PHPjc/322653.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/322653.htmlTechArticleFor many novices, using PHP can easily write specific features in just a few hours. function code. However, building a stable and reliable database takes some time...