PHP developers often make 10 MySQL errors

Database is the foundation of most web application development. If you're using PHP, then most databases use MySQL as an important part of the lamp architecture.





PHP looks very simple, and a beginner can start writing functions in a few hours. But it takes time and experience to build a stable, reliable database. Here are some of these experiences, not only MySQL, but also other databases can be referenced.





1. Use MyISAM instead of InnoDB





MySQL has a lot of database engine, single general also use MyISAM and InnoDB.




The
MyISAM is used by default. But unless you're building a very simple database or just experimenting, then most of the time the choice is wrong. MyISAM does not support foreign key constraints, which is the essence of ensuring data integrity. In addition, MyISAM will lock the entire table when adding or updating data, which can have a big problem with future extended performance.




The
solution is simple: use InnoDB.





2. Using the MySQL method of PHP





PHP provides MySQL's library of functions from the outset. Many programs rely on mysql_connect, mysql_query, MYSQL_FETCH_ASSOC, and so on, but the PHP manual suggests:





If you use the MySQL version after 4.1.3, then it is strongly recommended to use mysqli extensions.





mysqli, or MySQL's advanced extensions, has some advantages:





has object-oriented interface





prepared statements (preprocessing statements, can effectively prevent sql-injection attacks, but also improve performance)





supports multiple statements and transactions





In addition, if you want to support multiple databases then you should consider PDO.





3. Do not filter user input





should be: Never trust a user's input. Use back-end PHP to verify that every input is filtered, and do not trust JavaScript. SQL statements like the following can easily be attacked:





$username = $_post["name"]; $password = $_post["password"]; $sql = "Select UserID from usertable WHERE username= ' $username ' and password= ' $password ';"; Run Query ...





code like this if the user enters "admin"; Well, it's the same as the following:





SELECT userid from usertable WHERE username= ' admin ';





so the intruder can not enter the password, the admin identity log in.





4. Do not use UTF-8





users in the UK and the United States rarely consider language issues, which makes it impossible for many products to be common elsewhere. There are some GBK coding, there will be a lot of trouble.





UTF-8 solves a lot of internationalization problems. Although PHP6 can solve this problem perfectly, it does not prevent you from setting MySQL's character set to UTF-8.





5. Use PHP
where SQL is used




If you're new to MySQL, sometimes you might want to solve a problem by thinking about the language you're familiar with. This can result in some wasteful and poor performance situations. For example: When the average is calculated, the MySQL native avg () method is not applied, but it is used in PHP to loop through all the values and then calculate the average.





also pay attention to the PHP loops in SQL queries. In general, it is more efficient to loop with PHP after all the results have been achieved.





generally use a strong database method when handling large amounts of data, which can improve efficiency.





6. Not optimized query





99% PHP Performance problems are caused by the database, a bad SQL statement may make your entire program very slow. MySQL's explain statement,query profiler,many other tools can help you find those naughty select.





7. Using the wrong data type





MySQL provides a range of data types such as numbers, strings, time, and so on. If you want to store dates, it's more complicated to use a date or datetime type, or a shape or string.





Sometimes you want to use your own defined data type, for example, to store a serialized PHP object with a string. Adding a database can be easy, but in this case MySQL becomes unwieldy and can cause problems later.





8. Use *
in a select query




do not use * to return all fields in the table, which is very slow. You just need to take out the data fields you need. If you need to remove all the fields, then your table may need to be changed.





9. Insufficient indexes or excessive indexing





in general, you should index all the fields that appear in the SELECT statement after the where.





For example, if our user table has a number ID (primary key) and an email address. After logging in, MySQL should find the appropriate ID by email. By indexing, MySQL can quickly locate email via the search algorithm. If there is no index, MySQL needs to check every record until it is found.





in this case, you may want to add an index to each field, but the consequence is that when you update or add, the index will do it again, and when the data volume is large, there will be performance problems. So, only the fields you want are indexed.





10. Do not back up





may not happen often, but database corruption, hard drives, service stops, and so on, can cause catastrophic damage to the data. So you have to make sure that you automatically back up your data or save your copy.





11. In addition: Do not consider other databases





MySQL may be the most used database in PHP, but it's not the only option. PostgreSQL and Firebird are also competitors, and they are open source and are not controlled by some companies. Microsoft offers SQL Server express,oracle with 10g Express, and these enterprise-class also have free edition. SQLite is also a good choice for some small or embedded applications.