11 MySQL errors most often made by PHP programmers

Source: Internet
Author: User
Tags date character set integer mysql php mysql sql injection sql injection attack sql server express

For most Web applications, databases are a very basic part. If you're using PHP, you're probably also using a significant part of the Mysql-lamp series.

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:

$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:

SELECT userid from usertable WHERE username= ' admin ';

Cunning hackers can log in with admin, they do not need to know the password, because the password section 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! It would be disastrous to lose MySQL data, so make sure you have used automatic backups or have replicated 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.

Turn from: http://boobooke.com/bbs/thread-102535-1-1.html




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.