For many beginners, using PHP can easily write code with specific functionality within a few hours. However, building a stable and reliable database will take some time and related skills. Here's a list of the worst 11 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 what you most likely encounter is MyISAM and InnoDB.
MySQL defaults to using MyISAM. However, in many cases this is a bad choice unless you are creating a very simple or experimental database. FOREIGN key constraints or transactions are important for data integrity, but MyISAM does not support these. In addition, when a record is inserted or updated, the entire datasheet is locked, which can produce very poor operating efficiency when the volume of usage increases.
The conclusion is simple: use InnoDB.
2, the use of PHP 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 a MySQL v4.1.3 or newer version, it is strongly recommended that you use the mysqli extension.
Mysqli (MySQL's enhanced version extension) has the following advantages:
Optional object-oriented interface
Prepared expression, which helps prevent SQL injection attacks and improves performance
Support for more expressions and transaction processing
In addition, if you want to support a variety of database systems, you can also consider PDO.
3. No processing user input
This can be said: Never trust the user's input. Use server-side PHP to validate each string, and do not send a wish with JavaScript. The simplest SQL injection attack will take advantage of the following code:
Copy CodeThe 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, this will be hacked and the corresponding SQL statement is as follows:
Copy CodeThe code is as follows:
SELECT userid from usertable WHERE username= ' admin ';
Cunning. HackersYou can log in as admin and they don't need to know the password because the password segment is commented out.
4. No use of UTF-8
We rarely consider languages other than English in the United States, Britain and Australia. We proudly completed our "masterpiece" and found that they were not working anywhere else.
UTF-8 solves a lot of internationalization 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 touch with MySQL soon, you will prefer to use the language you already have to solve the problem, which will result in writing some redundant, inefficient code. For example, you wouldn't use the AVG () function from MySQL, but you would sum the values in the recordset and then use the PHP loop to calculate the average.
Also, note the SQL query in the PHP loop. Generally, it is more efficient to execute a query than to iterate over the results.
Therefore, when analyzing the data, please take advantage of the database system, understand some of the knowledge of SQL will be helpful.
6, did not optimize the database query
99% of PHP performance problems are caused by the database, and just a bad SQL query can completely paralyze your Web application. MySQL's explain statement, Query Profiler, and many other tools will help you find these heinous select.
7, not the correct use of data types
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 an integer or string at this time, it will make the SQL query very complex, provided 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. In this way, database management may be simpler, but it will make MySQL a bad data store and will likely cause a failure later.
8, in the query use *
Never use * to return data from all columns in a datasheet. This is laziness: you should extract the data you need. Even if you need all the fields, your data tables will inevitably change.
9, do not use the index or excessive use of the index
The general principle is that any field in a SELECT statement that is represented by a WHERE clause should use an index.
For example, suppose we have a user table, including a numeric ID (primary key) and an email address. When you log in, MySQL must find the correct ID based on an email. If you use an index (email here), then MySQL can use a faster search algorithm to locate email, or even instant implementation. Otherwise, MySQL can only check each record sequentially until it finds the correct email address.
Some people add indexes to 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 backup!
Although rare, the database is in danger of collapsing. The hard drive may be corrupted, the server may crash, and the web host provider may go bankrupt! Losing MySQL data will be disastrous, so make sure you've used an automated backup or have replicated it in place.
11, Bonus mistake-do not consider the use of other databases
For PHP developers, MySQL may be the most widely used database system, but not the only option. PostgreSQL and Firebird are the most powerful contenders: both are open source and have not been acquired by the company. Microsoft offers SQL Server Express, Oracle offers 10g Express, both of which are free versions of enterprise-class databases. Sometimes, for a smaller web application or embedded application, SQLite is a viable alternative.
Limited level, welcome to shoot Bricks!!
Original link: Top MySQL mistakes Made by PHP developers
Ps: The original title is written 10, in fact, the author listed 11, so I made a small change
