In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >
Share
Shulou(Shulou.com)06/03 Report--
PHP developers often make 10 MySQL mistakes, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.
1. Use MyISAM instead of InnoDB
MySQL has a lot of database engines, and generally uses MyISAM and InnoDB.
MyISAM is used by default. But unless you are building a very simple database or just experimental, then most of the time this choice is wrong.
MyISAM does not support foreign key constraints, which is the essence of ensuring data integrity. In addition, MyISAM locks the entire table when adding or updating data, which is found in the
There will be big problems with extended performance in the future.
The solution is simple: use InnoDB. But I usually try MyISAM here.
2. Use the mysql method of PHP
PHP has provided MySQL's function library from the very beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but PHP hands
It is recommended in the book:
If you are using a version of MySQL after 4.1.3, it is strongly recommended that you use the mysqli extension.
Mysqli, or the advanced extension of MySQL, has some advantages:
There are object-oriented interfaces
Prepared statements (preprocessing statements, which can effectively prevent SQL- injection attacks and improve performance)
Support for multiple statements and transactions
In addition, if you want to support multiple databases, you should consider PDO.
3. Do not filter user input
Never trust the user's input. Use the back-end PHP to verify and filter every piece of input, and don't trust Javascript. SQL statements like this are easily attacked:
$username= $_ POST ["name"]; $password= $_ POST ["password"]; $sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; / / run query...
Such code, if the user enters "admin';", is equivalent to the following:
SELECT userid FROM usertable WHERE username='admin'
In this way, the intruder can log in through admin without entering a password.
4. Do not use UTF-8
Those users in Britain and the United States seldom consider the problem of language, so many products cannot be used in other places. There are also some GBK codes, and there will be a lot of trouble.
UTF-8 solves a lot of internationalization problems. Although PHP6 can solve this problem perfectly, it doesn't prevent you from setting the character set of MySQL to UTF-8.
5. Use PHP where you should use SQL
If you are new to MySQL, sometimes you may consider using a language you are familiar with when solving a problem. This may lead to some waste and poor performance.
The situation. For example, instead of using MySQL's native AVG () method when calculating averages, PHP loops through all the values and then accumulates the averages.
Also note the PHP loop in the SQL query. In general, it is more efficient to loop with PHP after all the results have been achieved.
Generally, when dealing with a large amount of data, a powerful database method is used to improve the efficiency.
6. Do not optimize the query
99% of PHP performance problems are caused by the database, and a bad SQL statement can make your entire program very slow. EXPLAIN statement,Query of MySQL
Profiler,many other tools's tools can help you find those naughty SELECT.
7. Use the wrong data type
MySQL provides a range of data types for numbers, strings, times, and so on. If you want to store dates, use DATE or DATETIME types, plastic shapes or words
Strings make things more complicated.
Sometimes you want to use your own defined data types, for example, to use strings to store serialized PHP objects. It may be easy to add a database, but in that case, MySQL
It will become bulky and may cause some problems in the future.
8. Use * in SELECT query
Do not use * to return all fields in the table, which can be very slow. You just need to take out the data fields you need. If you need to take out all the fields, then maybe your table needs to
It's going to change.
9. Insufficient or excessive indexing
In general, the index should appear in all fields after WHERE in the SELECT statement.
For example, suppose our user table has a numeric ID (primary key) and email address. After logging in, MySQL should find the appropriate ID through email. By indexing, MySQL can
To quickly locate the email through the search algorithm. If there is no index, MySQL needs to check each record until it is found.
In that case, you may want to add an index to every field, but the consequence is that when you update or add, the index will be done again, when the amount of data is large.
There will be performance problems. So, index only the fields you need.
10. No backup
It may not happen often, but database corruption, hard drive failure, service outage, and so on, can cause catastrophic damage to the data. So you must make sure that the data is backed up automatically or
Save a copy of the.
After reading the above, have you mastered the 10 MySQL mistakes that PHP developers often make? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.