MySQL's Char,varchar,text,blob

MySQL Char,varchar,text,blob is a few links but there are a lot of different types of fields, which is the basis of MySQL, but the foundation did not learn, bad to fill.

Let's summarize briefly:

Char: fixed length , maximum 255 characters

VARCHAR: variable length , maximum 65,535 characters (both single-column and full-line limits)

Text: variable length, large object with character set, and sort and check according to character set, case insensitive

BLOB: variable length, non-character set binary large object, case sensitive

The following is only my personal understanding, Caishuxueqian, look at the road expert pointing.

The engine I use is MyISAM, so the following discussion is focused on the MyISAM.

The first explanation is that Char,char is one of the commonly used field types in the project, which means storing the data in fixed lengths, in other words, the data is initialized to allocate a fixed-length storage space for that type of field, even if the length of the storage space is not reached, the actual amount of storage space used is defined as the length. For example, a char (50) of a field and a specified length of 50 characters, then when you deposit a character: "ABC", the character length is actually 3 characters, but the hard disk space is 50 characters. Obviously, the disadvantage of Char comes out: waste storage space ! However, the advantages of char are also shown: fixed length, very high efficiency (index), no fragmentation .

Here we look at how char is stored, although Char wastes a great deal of storage space, but have you ever thought about how to handle a string in front and back space char? when the stored string does not reach the maximum length of char, the string is not populated with spaces after it, and char filters the space at the end of the string and stores it, while the string is compared with an automatic space padding to the end of the string.

Well, with the knowledge of char, the understanding of varchar is easy.

VarChar is a string that stores variable lengths, which simply means that when we define a table, the length of the specified field is the maximum length, and when the string does not reach its maximum length , it is stored as the actual length of the string, without consuming extra storage space. Thus, in general, varchar saves storage space compared to char, but there are often exceptions, followed by a discussion of the problem.

A special case is when creating a table with the row_format=fixed option (the default is row_format=dynamic), then MySQL allocates a fixed-length amount of storage for each row of data, which is, of course, an exception. I wonder if you ever thought: Why would an administrator want to do this? It makes sense to allocate a fixed-length storage space to varchar. As a common example, assuming that the dynamic default option is used, we create a field B varchar (100), and now we insert a 10-character data: Abcdefghij, very good, takes up only 10 characters of space, saving a lot of storage space compared to char. But did you think of the problem? One day later you find that this field needs to be updated and updated to 20 characters of data: Abcdefghijklmnopqrst, do you know how to store the database? The original storage location allocated only 10 characters of space, now to store 20 characters, less than the length limit (maximum length is 100 characters), the problem is, how does MySQL handle it? The next step might be to discuss the paging mechanism or the split mechanism at the MySQL storage level, and no further further. Anyway, no matter how MySQL takes, it will certainly be fragmented on the disk, the time of the formation of disk fragmentation on the system efficiency is a fatal blow, so we often see an administrator to export MySQL and then import, is to solve this problem, improve efficiency.

Here's a question: varchar saves storage space than char, but there are often exceptions! How do we understand this question? To dig deeper into this problem, we need to dig deeper into the storage mechanism of varchar. Commonly used in Chinese storage is generally used GBK or utf-8 two character sets, gbk each character accounted for 2 bytes, utf-8 each character accounted for 3 bytes, so: GBK character Set maximum storage length is

(65535-1-1)/2= 32766 or (65535-1-2)/2= 32766, the meaning of this algorithm is: 65535 is the maximum length of varchar, the first 1 means that the actual storage location starts from the second bit, The second-1 or 2 means that the varchar field stores the actual content separately from the clustered index, with 1 to 2 bytes at the beginning of the content representing the actual length (255 bytes for a length of less than 1 and 255 bytes for a length of more than 2). The reason for dividing by 2 is that the character of a GBK character set is 2 bytes long, so the maximum storage length of the GBK character set is 32766, depending on the situation.

So what about the UTF-8 character set? The algorithm is as follows:

(65535-1-1)/3= 21844 or (65535-1-2)/3= 21844 minus 1 minus 2 meaning ibid., divided by 3 means that a utf-8 character set character occupies an actual length of 3 bytes.

After reading this section, you can understand the storage space used by char (1) and varchar (1)? In this extreme case, it is clear that Char saves more storage space because Char does not have the additional overhead of managing the data.

Next, let's focus on what the real meaning of varchar's 65535 storage length represents? Because the previous said a word: both single-column restrictions, but also the whole line of restrictions! Let us explain in detail. For a single-column limit, let's give an example: CREATE TABLE TableName (c varchar (N)) CHARSET=GBK; The maximum value of N is (65535-1-2) = 32766. At the same time, if there are other fields in the same field, then the length of all fields cannot exceed 65535, for example: CREATE TABLE tablename (c1 int (4), C2 char (x), C3 varchar (N)) Charset=utf8, So the maximum value of n is (65535-1-2-4-30*3)/3=21812, perhaps you understand that int (4) needs 4 characters, here may need a more in-depth understanding of the storage mechanism of int, I do not know, the book has not yet read it. In short, the int type occupies 4 characters in length, in other words CREATE TABLE tablename (c1 int), C2 char (), C3 varchar (N)) Charset=utf8, The maximum value of n is also (65535-1-2-4-30*3)/3=21812. I did a simple experiment to see.

The length of the first varchar was greater than 21812, so it failed.

The second varchar length equals 21812 and the Save succeeds

Reprint please indicate source: www.blogguy.cn

Nonsense so much, in short, a word: char in the waste of storage space under the disadvantage of obtaining a higher efficiency, varchar opposite. Next we will also summarize the circumstances under which char is used and under what circumstances use varchar.

principle One: Depending on the length of the string, all fixed-length strings or strings similar to fixed lengths are used char. such as ID card number, mobile phone number, bank card number, MD5, hash value, etc. this is the string is fixed length, no doubt with Char, there is a kind of basic fixed length but slightly out, such as the name of the Chinese, etc., the general length may be 2~5 a Chinese character, This kind of information is also very suitable for storing with char, as long as the allocation is slightly larger than the usual length.

principle Two: whether the data changes frequently leads to fragmentation, small strings that can change frequently and produce storage fragments are all used in char. we know that data of type char is a one-time allocation of storage space, no matter how you modify it in the future, the data is always within that storage space and does not produce fragmentation. varchar is different, the data length of varchar is variable, when the modified data is larger than the current storage length, it will produce fragments, if the application is repeatedly modified data application, then over time is to produce countless fragments, efficiency can be imagined.

principle Three: Understand the difference between the storage space and memory space of varchar, and reasonably specify the length of varchar. we know that varchar's storage length is based on the length of the string, but the memory space occupied by the runtime allocates memory space by the defined length (I understand that I don't know if it's correct). This phenomenon causes the storage of a string, such as a communication address, unobstructed in 100 characters characters can be stored, so varchar (100) is a reasonable choice, but because of the earlier, may be a diagram of the convenience of varchar (500), anyway, the storage space is the same, But the effect is really different. In the memory model, varchar (100) and varchar (500) are different, the latter occupies 5 times times more memory space than the former, in the temporal table and sorting when the difference is almost an order of magnitude, so efficiency can be imagined.

Reprint Annotated Source: www.blogguy.cn

Basically explain char and varchar, here by the passing see what are nchar and nvarchar?

nvarchar represents variable-length Unicode data with a maximum length of 4,000 characters and nchar for fixed-length Unicode data with a maximum length of 4,000 characters.

Under what circumstances should the nchar and nvarchar be used? We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. Web sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues. The same explanation also follows the ntext we are going to discuss.

Now let's look at the text and blob

The text is divided into 4 types: Tinytext, text, Mediumtext, and Longtext, each corresponding to a different length. Text is a non-binary string and requires the specified character set and is checked and sorted by that character set. Only plain text can be stored and is considered to be a varchar extension when the length is insufficient.

BLOBs are also divided into 4 types: Tinyblob,blob,mediumblob and Longblob, respectively, corresponding to different lengths, BLOBs store binary data, so no character set check is required, and blobs, in addition to storing text information, because of the binary storage format, So you can also save information such as pictures, blobs can be seen as varbinary in the length of the extension.

The various types of storage lengths for text and blobs, as shown in the following table:

Tinyblob Maximum length 255 characters (2^8-1)
Tinytext Maximum length 255 characters (2^8-1)
Blob Maximum length 65,535 characters (2^16-1)
Text max length 65,535 characters (2^16-1)
Mediumblob Maximum length 16,777,215 characters (2^24-1)
Mediumtext Maximum length 16,777,215 characters (2^24-1
Longblob Maximum length 4,294,967,295 characters (2^32-1)
Longtext Maximum length 4,294,967,295 characters (2^32-1)

Well, to this Char,varchar,text,blob content discussion basically completed, here is I again review about the data of the int type, listed here for easy comparison.

xml/html Code

2. · tinyint--a tiny integer that supports 128 to 127 (signed), 0 to 255 (UNSIGNED), requires 1 bytes of storage
4. · bit--with tinyint (1)
6. · bool--with tinyint (1)
8. · smallint--a small integer that supports 32768 to 32767 (signed), 0 to 65535 (UNSIGNED), requires 2 bytes of storage mediumint--A medium integer, supports 8388608 to 8388607 (signed), 0 to 16777215 (UNSIGNED), requires 3 bytes of storage
10. · int--an integer that supports 2147493648 to 2147493647 (signed), 0 to 4294967295 (UNSIGNED) and requires 4 bytes of storage
12. · integer--with int
14. · bigint--a large integer, supported-9223372036854775808 to 9223372036854775807 (signed), 0 to 18446744073709551615 (UNSIGNED), 8 bytes storage required
16. · Float (precision)--a floating-point number. The precision<=24 is used for single-precision floating-point numbers, and precision between 25 and 53 for precision floating-point numbers. Float (X) has the same range as the float and double types of the mockers, but does not define the display dimensions and decimal digits. Before MySQL3.23, this is not a true floating-point value, and there are always two decimal places. All calculations in MySQL are double-precision, so this brings up some unexpected problems.
18. · float--a small menu-precision floating-point number. Supports -3.402823466E+38 to -1.175494351e-38,0 and 1.175494351E-38 to 3.402823466E+38, requiring 4 bytes of storage. If it is unsigned, the range of positive numbers remains the same, but negative numbers are not allowed.
20. · double--a double-precision floating-point number. Supports -1.7976931348623157E+308 to -2.2250738585072014e-308,0 and 2.2250738585072014E-308 to 1.7976931348623157E+308. If the float,unsigned does not change the positive range, but negative numbers are not allowed.
22. · Double precision--with double
24. · real--with double
26. · decimal--stores a number like a string, with each character occupying one byte
28. · dec--with decimal
30. · numeric--with decimal

MySQL's Char,varchar,text,blob