MySQL is often misunderstood by us

MySQL, as a major force in databases, exists everywhere in our various systems. I believe everyone is familiar with it! However, just because you know you can use it doesn't mean you know the details, so let's take stock of some of the things we usually don't pay much attention to, firstly for fun, and secondly to prevent ourselves from stepping into pitfalls.

Disclaimer: If you want to know the details, then read the source code. I really don’t have that ability, so I can only talk about some things from a one-sided perspective!

1. Case-insensitive (field name)

MySQL query field names are not case-sensitive, but the query content is case-sensitive. , so if you can use underscores to name, don’t use case distinction in naming, such as:

1. SELECT uid, v_state FROM all_user WHERE username = 'sunyue';

2. SELECT uid,v_state FROM all_user WHERE USERNAME = 'sunyue'; //Same result as the previous statement, fields are not case-sensitive

3. 
   

4. ##SELECT uid,v_state FROM all_user WHERE USERname = 'SUNYUE'; //No information found, the content must be size-sensitive Write

Copy code

2. Fuzzy matching ([_] matches any character)

Although fuzzy matching has a bit of a regular meaning, you cannot match it in the database as freely as a regular match, so just try to simplify the matching as much as possible! (Additional noun: full-text search sphinx)

1. SELECT uid,v_state FROM all_user WHERE USERNAME LIKE 'su_yue'; //Underscore [_] matches any single character

2. SELECT uid,v_state FROM all_user WHERE USERNAME LIKE 'su%yue'; //% matches any number of characters

3. SELECT uid,v_state FROM all_user WHERE USERNAME NOT LIKE 'su%'; /t likeGet the information not included

##Copy code

3. How to guess a function

As a relational database, mysql is aimed at ordinary developers, so everything should be as humane as possible. So, if you don’t know what a certain function is, then maybe you can guess it, because if a function makes it impossible for others to guess, then maybe it is a failed design. For mysql, as long as your English level is slightly better, you can easily think of how to write a relational query. Therefore, the ability to guess depends on your English level, hahaha! ! !

4. replace into syntax

replace into is a very crude insertion method, but it is also necessary to use it in appropriate scenarios. For example, some data that needs to be overwritten at any time should be replaced, but for some auto-increment tables, You should try to avoid using it, because it will be a waste of ID, and there may be no need to use replace.

5. insert into on dumplicate key update syntax

This writing method is relatively gentle, but the speed is definitely slower. It is said that only mysql has this feature. If you don’t want to check whether the data already exists before inserting, maybe this is a good choice!

6. explain syntax

This is an effective way to check the query effect. In fact, I rarely use this function, but if a developer often uses this Function, then, I believe that he must be an awesome person. Through this command, you can determine whether the statement you wrote is optimized, and if not, what is the direction. It should be said that the more you use it, the more you will know the results, so just use it more!

7. Index length

The maximum index length of myisam is 1000 bytes, and the maximum index length of innodb is 767 bytes! Be careful when using it. If it exceeds the range, it will exceed expectations!

8. Copying the library

1) Directly data the database backup and recovery command that comes with mysql;

2) Directly copy the file to the destination server (Pay attention to the process occupation problem);

3) Use tools for synchronization; I personally feel that the second method is the easiest!

9. Partitioning

For some tables that may be very large, partitioning operations are required. If there is a need for this, you need to add at least one at the beginning. Partition, otherwise the partition will not be able to be operated later. (At least I think so)

10. Slow query

Slow query, this is a performance monitoring problem. If the server performance is too poor, when troubleshooting, there will be records of such slow queries, and the troubleshooting will be more effective!

11. Binlog

binlog, this function is generally used. It is very necessary for data recovery, data operation recording, etc.!

12. Other views, stored procedures, events, etc. that you should know...

Views, events, etc. These are some advanced applications. I found that many projects This kind of application does not exist at all, and at least the projects I have encountered are almost never involved, or if they are, it is done casually by others! But you still need to understand the advanced functions, whether they are useful or not!

13. Available tools

phpMyAdmin, as a remote and safe way to operate the database, is still very good, but as a local operation, it is a bit clumsy; sqlyog , although the interface is old-fashioned, it is still relatively convenient; navicat is more professional, but you have to adapt to its various forms of expression; command line, I think no one will use the command line directly for operations, but the command line is It’s fundamental!

There are many things that need to be deepened. If you have the time and ability, you should delve into the source code!

The above is the content of MySQL that is often misunderstood by us. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!