How long can varchar in mysql be stored? 1.varchar type changes 4. The maximum length of a version of MySQL database with varchar type below1is limited to 255, and its data range can be 0~255 or 1~255 (depending on different versions of the database). In MySQL5.0 version 5.0 and above, the length of varchar data type is supported to 65535, i.e.

How long can varchar in mysql be stored?

1.varchar type change

4. The maximum length of MySQL database of varchar type below1is limited to 255, and its data range can be 0~255 or 1~255 (depending on different versions of the database). In MySQL5.0 version 5.0 or above, the length of supporting varchar data type is 65535, which means that it can store 65532 bytes of data, and the start bit and the end bit occupy three bytes, which means that the data that needs to be stored in fixed text or BLOB format in version 4. 1 or below can be stored in variable-length varchar, which can effectively reduce the size of database documents.

In the MySQL database version of varchar type below 4. 1, nvarchar (characters storing Unicode data type) is stored as 2 bytes, which is generally used for Chinese or other language input and is not easy to be garbled; Varchar: Chinese characters are 2 bytes, and other characters are stored as 1 byte. Varchar is suitable for inputting English and numbers.

In version 4.0, varchar(20) means 20 bytes. If you store UTF8 Chinese characters, you can only store 6 characters (3 bytes for each Chinese character); After version 5.0, varchar(20) means 20 characters. Whether storing numbers, letters or UTF8 Chinese characters (3 bytes for each Chinese character), you can store 20 characters with a maximum size of 65532 bytes; Varchar(20) is only 20 bytes at most in Mysql4, but Mysql5 has different storage sizes according to different codes. The specific rules are as follows:

A) storage restrictions

The Varchar field stores the actual content independently of the clustered index, and the actual length is indicated by 1 to 2 bytes at the beginning of the content (it takes 2 bytes for the length to exceed 255), so the maximum length cannot exceed 65535.

B) code length limitation

If the character type is gbk, each character takes up at most 2 bytes, and the maximum length cannot exceed 32766;

If the character type is utf8, each character takes up at most 3 bytes, and the maximum length cannot exceed 2 1845.

If the definition exceeds the above limit, the varchar field will be cast to the text type and a warning will be generated.

C) line length limitation

In practical application, it is the length of line definition that limits the length of varchar. MySQL requires that the defined length of a row cannot exceed 65535. If the defined table length exceeds this value, you will be prompted.

Error1118 (42000): The row size is too large. The maximum row size (excluding BLOBs) of the table type used is 65535. You must change some columns to text or spots.

2. the difference between char (m) and varchar (m)

The length of the column defined by CHAR(M) is fixed, and the value of m can be between 0 and 255. When saving CHAR values, spaces are filled to their right to reach the specified length. When retrieving the CHAR value, the trailing space is deleted. No case conversion is performed during storage or retrieval. CHAR is very convenient to store fixed-length data and has high index efficiency for CHAR fields. For example, if you define char( 10), then no matter whether the data you store reaches 10 bytes, it will occupy 10 bytes, and the insufficient data will be automatically filled with spaces.

The length of the column defined by VARCHAR(M) is a string with variable length, and the value of m can be between 0 and 65535. (The maximum effective length of VARCHAR is determined by the maximum line size and the character set used. The total maximum length is 65,532 bytes). When saving a VARCHAR value, only the required number of characters is saved, plus a record length of one byte (if the length of the column declaration exceeds 255, two bytes are used). VARCHAR values were saved without padding. When the value is saved and retrieved, the trailing spaces remain, which conforms to standard SQL. VarCHAR stores variable-length data, but the storage efficiency is not as high as CHAR. If the possible value length of a field is not fixed, we only know that it cannot exceed 10 characters, and it is the most economical to define it as VARCHAR( 10). The actual length of a VARCHAR type is the actual length of its value+1. Why "+1"? This byte is used to store how much length is actually used. Considering the space, varchar is suitable; Considering the efficiency, it is more appropriate to use char, and the key is to find the balance point according to the actual situation.

The biggest difference between CHAR and VARCHAR is that one is fixed length and the other is variable length. Because its length is variable, it is the actual string plus one byte to record the length of the string (if it exceeds 255, it needs two bytes). If the value assigned to a CHAR or VARCHAR column exceeds the maximum length of the column, the value will be cropped to fit the column. If the cut character is not a space, a warning will be generated. If non-white space characters are cropped, it will cause an error (not a warning) and prohibit inserting values using strict SQL mode.

3. the difference between 3.varchar and text and BlOB types

VARCHAR, BLOB and TEXT types are variable-length types, and their storage requirements depend on the actual length of column values (denoted by L in the above table), not the maximum possible size of the types. For example, the VARCHAR( 10) column can store a string with a maximum length of 10 characters. The actual storage needs to be the length of the string, plus 1 byte to record the length of the string. For the string' abcd', l is 4, and the storage requirement is 5 bytes.

BLOB and TEXT types need 1, 2, 3 or 4 bytes to record the length of column values, depending on the maximum possible length of the type. VARCHAR needs to define the size, and the maximum limit is 65535 bytes; No text is required. If a value exceeding the maximum length of a column type is assigned to a BLOB or TEXT column, the value will be truncated to fit it.

BLOB is a large binary object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB differ only in the maximum length of the values they can store.

BLOB can store pictures, but text can't. TEXT can only store plain text files. The four TEXT types TINYTEXT, Text, MEDIUMTEXT and LONGTEXT correspond to the four BLOB types and have the same maximum length and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison of BLOB values are performed in a case-sensitive manner, while TEXT values are case-insensitive. In other words, the text is a case-insensitive BLOB.

4. Summarize the differences among char, varchar and text.

The difference in length, the range of char is 0 ~ 255, and the longest of varchar is 64k, but note that 64k here is the length of the whole line, and other columns should be taken into account. If there is not null, it will occupy one place. For different character sets, the effective length is different, such as utf8, the longest is 2 1845, and other columns should be removed. In general, varchar is enough to store. If you meet Daimonji, you can consider using words, up to 4G.

Efficiency is basically char & gtvarchar & gt text, but if you use Innodb engine, it is recommended to use varchar instead of char.

Char and varchar can have default values, and text cannot specify default values.

Database storage needs to choose the appropriate data type, which has a certain impact on performance. There are two piecemeal records here. For int type, if you don't need to access negative values, it's best to add unsigned; ; For fields that often appear in where statements, you can consider adding indexes, especially those that are shaped.