In-depth study of mysql varchar and limit (easy to ignore), mysqlvarchar

In-depth study of mysql varchar and limit (easy to ignore), mysqlvarchar

Why is the title named? Commen sence refers to things that everyone should know, but often they will know something or know something about it, today I will summarize some commen sense type problems I encountered in mysql.

　　1. How many Chinese characters and numbers can be stored in varchar (5?

I believe many people should be familiar with this, just like me. Based on experience, we can quickly make a decision, such as using varchar (200) to store URLs. However, even if you have used it many times, you may make incorrect answers to the above questions.

I have checked a lot of information about this problem. Some people say that they can store 5 Characters and 2.5 Chinese characters (each Chinese Character occupies two bytes), and some people say that they need to differentiate versions, 5.0 is a dividing line. 5.0 is the same as previously mentioned. After 5.0, five "Words" can be stored without being distinguished by numbers, English letters, and Chinese characters. Is that true, let's make an experiment:
Copy codeThe Code is as follows:
Create table 'test '(
'Name' varchar (5) not null default '',
'Info' char (5) not null default '',
Primary key ('name ')
) ENGINE = MyISAM default charset = utf8;

It can be seen that 5 in varchar (5) represents five "characters" instead of five bytes (bytes ), when the storage length exceeds the specified length, the excess part will be "click". My mysql version is 5.6, and the character set utf8 and gbk are the same.

I don't have any other versions on my computer. I went to the official documents to see if there are any instructions. I finally found some differences in the official documents for a long time:

The following section describes mysq4.1in http://dev.mysql.com/doc/refman/4.1/en/char.html:

Copy codeThe Code is as follows:
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. for example, CHAR (30) can hold up to 30 characters. (Before MySQL 4.1, the length is interpreted as number of bytes .)

Let's take a look at similar descriptions of other versions:

Copy codeThe Code is as follows:
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR (30) can hold up to 30 characters.

Obviously, the official document says that when the mysql version is earlier than 4.1, the statement is true: varchar (5) saves 5 bytes, and five English numbers or 2.5 Chinese characters (assuming one Chinese character is 2 bytes );

When the mysql version is greater than or equal to 4.1, the value 5 in varchar (5) is no longer the number of bytes, it should be understood as "word". The word here means that a Chinese character is "Same as an English word or number"

　　2. Will you use limit in mysql?

How do you use limit in a project? Limit num? Or limit num1, num2? Or others? It is important to know that the performance of limit varies greatly.

I tested it myself and used limit in an innodb table. The table contains 10000 data records, four fields: id (int), time (int), and title (varchar) body (mediumtext), the size is about mb. First, turn off the query cache, so that the query cache will not affect the query time. Note that the time field is indexed,

Copy codeThe Code is as follows:
SET @ query_cache_type = ON;
Set global query_cache_size = 0;

Open Query profiler to view the statement execution time
Copy codeThe Code is as follows:
Set profiling = 1;

Next, execute the following statements:

Copy codeThe Code is as follows:
A. SELECT id, TIME, title FROM cnblogs where time> = 1315646940 order by time asc limit, 10

B. SELECT id, TIME, title FROM cnblogs WHERE TIME >=1315646940 ORDER BY TIME ASC LIMIT 10

C. SELECT id, TIME, title FROM cnblogs order by time asc limit, 10

Execution sequence a, B, c, a, B, c, c, a, and a (note that although I disabled the cache, the last query will still be cached, this can be seen from the Query profiler, so cross-execution is performed), use the following statement to view the results

Copy codeThe Code is as follows:
SHOW profiles;

From the preceding statement execution time analysis, we can see that the cache factor is not taken into account. When using limit, the "limit begin, num" format is much less efficient than the "limit num" format, therefore, try to use the second form when using it. For example, to obtain the data in a table cyclically, the data in the table cannot be retrieved at a time. In this case, the data is sorted by id (or other sorting fields) after performing the limit operation, we can obtain the critical value of the last field as the minimum value of the next Data fetch. Using limit num is much more efficient.