Check what is easy to be misunderstood in mysql, check mysql misunderstanding

Check what is easy to be misunderstood in mysql, check mysql misunderstanding

Quote: mysql, as a major force in the database, is everywhere in our various systems. I believe everyone is familiar with it! However, if you know that you can use it, it does not mean that you know the details. Then, let's take a look at some of the places that we don't usually pay much attention to. First, let's make it interesting, and then let ourselves step on it.

Statement: If you want to know the details, read the source code. I really don't have the skills, but I can only talk about some things on one side!

1. case-insensitive (field name)

The names of mysql query fields are case-insensitive, but the query content must be case-sensitive. Therefore, if you can name them with underscores, do not use case-sensitive names. For example:

SELECT uid, v_state FROM all_user WHERE username = 'sunue'; SELECT uid, v_state FROM all_user where username = 'sunue'; // same as the result of the previous statement, the field is case insensitive SELECT uid, v_state FROM all_user WHERE USERname = 'sunue'; // No information is found. The content must be case sensitive.

　2. Fuzzy match ([_] match any character)

Fuzzy match is a bit of regular expression, but you cannot match in the database as freely as the regular expression, to simplify matching as much as possible! (Glossary: full-text search sphtasks)

SELECT uid, v_state FROM all_user where username like 'su _ yue '; // underline [_] matches any single character SELECT uid, v_state FROM all_user where username like 'su % yue '; // % match any number of characters SELECT uid, v_state FROM all_user where username not like 'su % '; // not like to retrieve excluded Information

3. How to guess a function

As a relational database, mysql is intended for common developers, so everything is as user-friendly as possible. Therefore, if you do not know what a function is, you may be able to guess it, because if a function cannot be guessed by others, then maybe, he is the failed design. For mysql, as long as your English level is a little better, you can easily think of how to write relational queries. So the ability to guess lies in your level of English. Hahaha !!!

4. replace into syntax

Replace into is a very rough insert method, but it is also necessary to use it in appropriate scenarios. For example, replace should be used for data that needs to be overwritten at any time, however, avoid using auto-incrementing tables as much as possible, because this will be a waste of IDs and may not be necessary to use replace.

5. insert into on dumplicate key update syntax

This method is more gentle, but the speed must be slower. It is said that only mysql has this feature. If you do not want to determine whether the data already exists before insertion, maybe this is a good choice!

6. explain syntax

This is an effective way to check query results. In fact, I seldom use this function. However, if a developer often uses this function, I believe that, he must be a cool man himself. With this command, you can determine whether the statements you write are optimized, and if not, what is the direction. It should be said that if you use more, you will naturally know the results. You should use more!

7. Index Length

The maximum index length of myisam is 1000 bytes, and that of innodb is 767 bytes! Pay attention to usage. exceeding the range is beyond expectation!

8. Database Replication

1. mysql database backup and recovery command; 2. directly copy the file to the target server (pay attention to the process occupation problem); 3. use tools for synchronization. I personally think the second method is the easiest!

9. Partition

For some tables that may be very large, you need to perform partitioning. If you need to add at least one partition at the beginning, otherwise you will not be able to operate the partition later. (At least I think so)

10. Slow Query

Slow query: This is a performance monitoring problem. If the server performance is too bad, when troubleshooting the problem, there are such slow query records, troubleshooting will get twice the result!

11. binlog

This function is generally used in binlog, which is essential for data recovery and data operation records!

12. Other views, stored procedures, and events that should be known...

Views, events, and so on. These are some advanced applications. I found that many projects do not have such applications at all. In a few projects that I have encountered, they are almost not involved or involved! However, you still need to know about advanced functions, whether or not they are used!

13. Available tools

PhpMyAdmin is a good way to operate databases remotely and securely, but it is a bit clumsy as a local operation; sqlyog, although the interface is ancient, it is still relatively convenient; navicat, more professional, but you have to adapt to his various forms of expression; command line, I think no one will directly use command line operations, however, command line is fundamental!

There are many things to be deepened. If you have the time capability, you should go deep into the source code !!!

 

Yo, it took so long to complete the previous article !!