Mysql easy to be overlooked in detail _mysql

MySQL as a database of the main force, everywhere in our various systems, I believe that everyone is not unfamiliar! But, you know you can use does not mean you know the details, then we will take stock count some of the places we usually do not pay attention to, one for the fun, and then to not let themselves trample on the pit.

Statement: To want to know the details, then go to read the source code, I really do not have the ability, only from the one-sided to say something!

1, case-insensitive (field name)
MySQL's query field name is case-insensitive, but the query's content is case-sensitive, so you can name it with an underscore instead of case-sensitive naming, such as:

SELECT uid,v_state from All_user WHERE username = ' Sunyue ';
SELECT uid,v_state from all_user WHERE USERNAME = ' Sunyue ';      

 As with the previous statement, fields are case-insensitive

SELECT uid,v_state from all_user WHERE USERname = ' Sunyue ';      

 Information not found, content to be case-sensitive

2, Fuzzy matching ([_] match any character)
Although fuzzy matching is a bit of a regular meaning, but you do not like the same as the same free in the database to match, so as to simplify the match! (Attached noun: full-text search sphinx)

SELECT uid,v_state from All_user WHERE USERNAME like ' Su_yue ';  

 Underscore [_] matches any single character
SELECT uid,v_state from All_user WHERE USERNAME like ' Su%yue ';  

 % matches any number of characters
SELECT uid,v_state from All_user WHERE USERNAME isn't like ' su% ';    

 /T like fetching information that is not contained

3. How to guess a function
MySQL as a relational database, facing the ordinary developers, so everything possible to be humane. So, if you don't know what a feature is, then maybe you can guess, because if a feature makes it impossible for someone to guess a ballpark, then maybe he's the loser design. For MySQL, as long as your English level a little better, you can easily think of the relationship query should be how to write. So, the ability to guess lies in your level of English, HAHAHA!!!

4. Replace into grammar
Replace into is a very rude way of inserting, however, it is also necessary to use the appropriate scenarios, such as some of the data that needs to be overwritten at any time, should use replace, but for some self-added tables, should try to avoid, because this would be a waste of ID, You may not need to use replace.

5, insert into on dumplicate key update syntax
This writing is relatively gentle, but the speed is certainly slow, it is said that only MySQL has this feature oh, if you do not want to be in the insertion to determine whether the data already exist, perhaps this is a good choice!

6. Explain grammar
This is an effective way to check the effect of the query, in fact, I rarely use this function, but, if a developer often use this function, then, I believe that he himself must be a cow to break the person. By this command, you can determine whether the statement you write is optimized, and if not, what direction it is. It should be said that you use more, you naturally know the results, or more use it!

7, Index length
MyISAM has a maximum index length of 1000 bytes and a maximum InnoDB index length of 767 bytes! Attention to use Oh, out of range, then exceeded expectations!

8, the library copy
1 Direct data mysql from the database backup Restore command;
2 directly copy files to the destination server (note process occupancy issues);
3 Use tools to synchronize; personal feeling the second way is easiest!

9, zoning
For some potentially large tables, partitioning is required, and if so, it is necessary to add at least one partition at the very beginning, otherwise the partition cannot be manipulated later. (at least that's what I think)

10, slow query
Slow query, this is a performance monitoring problem, if the server performance is too poor, to troubleshoot problems, there is such a slow query record, the investigation will be more than twice!

11, Binlog
Binlog, generally use this function, for data recovery, data operation records and so have a very necessary function!

12, other should know the view, stored procedures, events ...
Views, events, and so on, these are some advanced applications, I found that many projects do not exist in this application, to the less I encountered projects are almost not involved or involved is also someone else casually do! But advanced features are still to be understood, whether or not to use!

13, can use the tool
phpMyAdmin, as a remote security operation of the database, is still very good, but as a local operation, it is a bit clumsy; SQLyog, although the interface is old-fashioned, but still more convenient; Navicat, more professional, but you have to adapt to his various forms; command line, I don't think anyone should be using the command line directly, but the command line is the root!

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.