Differences between char, varchar, and nvarchar

Char is a fixed-length type, and varchar is a variable-length type. Their differences are as follows:

In a char (m) data column, each value occupies M bytes. If the length of a data column is smaller than m, MySQL uses space characters on the right side of the data column. (The space characters filled in the search operation will be removed.) In the varchar (m) data column, each value takes only enough bytes and adds a byte to record its length (that is, the total length is L + 1 bytes ).

Rules Used in MySQL to determine whether data column type conversion is required

1. In a data table, if the length of each data column is fixed, the length of each data row will also be fixed.
2. As long as there is a variable length of a data column in the data table, the length of each data row is variable.
3. If the length of data rows in a data table is variable, in order to save storage space, mySQL converts a fixed-length data column in the data table to a variable-length data column.
Exception: Char data columns with a length less than 4 characters will not be converted to varchar type
 

Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes.

If you are still struggling with this issue, let's take a look at the explanation below and make a decision.

Generally, if you use Chinese characters or other special characters, I will use the type starting with N. Otherwise, I will directly start with var.

 

What is the difference between varchar and nvarchar in SQL Server?

A:
Varchar (N)
Variable-length and non-UNICODE character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes.

Nvarchar (N)
Unicode data with a variable length of n characters. The value of N must be between 1 and 4,000. The storage size of bytes is twice the number of input characters.

The two fields have Field Values: Me and coffee.
The varchar field occupies 2 × 2 + 6 = 10 bytes of storage space, while the nvarchar field occupies 8 × 2 = 16 bytes of storage space.

If the field value is only in English, you can select varchar. If the field value contains many double-byte (Chinese, Korean, etc.) characters, use nvarchar.Differences between char varchar nvarcharOnline help:

Char (N)	Fixed Length	High indexing EfficiencyProgramUse trim to remove unnecessary spaces.	NIt must be a value between 1 and 8,000. The storage size isNBytes
Varchar (N)	Variable Length	Efficiency not as high as char flexibility	NIt must be a number between 1 and 8,000. The storage size is the actual length of the input data bytes, insteadNBytes
Text (N)	Variable Length	Non-Unicode data
Nchar (N)	Fixed Length	Unicode data type processing (all characters are expressed in two bytes)	NMust be between 1 and 4,000. The storage size isNDouble the byte
Nvarchar (N)	Variable Length	Unicode data type processing (all characters are expressed in two bytes)	NMust be between 1 and 4,000. The storage size of bytes is twice the number of input characters. The entered data character length can be zero.
Ntext (N)	Variable Length	Unicode data type processing (all characters are expressed in two bytes)

Source: http://blog.sina.com.cn/s/blog_44e571d70100cagf.html

In general, if it contains Chinese characters, use nchar/nvarchar. If it contains English letters and numbers, use Char/varchar.

The summary here is good.
Http://www.cnblogs.com/ebaidu/archive/2007/08/14/854778.htmlMany developers often do not consider the char and varchar types when designing databases. Some developers do not pay attention to them because the storage price is getting cheaper and cheaper, I forgot some of the basic design theories and principles at the beginning. This reminds me of the young people who have moved away from their hands with a wave of RMB. In fact, I want to be a human or a human, development is good, and the grasp of details directly determines a lot of things. Of course, there are still some people who just don't figure out their differences, so they just choose one. Here I want to make a simple analysis on them. Of course, if there is something wrong with them, I hope you can give me some advice.

1. Char.It is very convenient for Char to store fixed-length data, and the indexing efficiency of char fields is high,For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. If not, it is automatically filled with spaces, therefore, trim () may be used multiple times during reading ().

2. varchar.Store variable-length dataBut the storage efficiency is not as high as char.If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as varchar (10. The actual length of the varchar type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length.In terms of space, it is appropriate to use varchar;Considering the efficiency, the key to the proper char is to find the trade-off based on the actual situation.

3. Text. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

4. nchar, nvarchar, and ntext. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist,Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes.The length of nchar and nvarchar is between 1 and 4000. Compared with Char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains English letters and numbers, use Char/varchar.

I will summarize their differences:
Char, Nchar fixed length, fast speed, large space, need to be processed
Varchar, nvarchar, and text cannot be long, the space is small, and the speed is slow. No processing is required.
Nchar, nvarchar, and ntext process Unicode codes Find a piece of information below, source http://wenku.baidu.com/view/eee97bf5f61fb7360b4c652b.html In SQL Server, varchar stores data in a single byte. nvarchar uses Unicode to store data. when a Chinese character is stored in SQL Server, it is saved as two bytes (generally Unico encoding) and an English character is saved to the database. If the field type is varchar, it only occupies One byte If the field type is nvarchar Two bytes .
Normally, we use varchar You can also store Chinese characters. However, if the operating system is an English operating system and the Chinese font is Incomplete support In SQL Server, if the Chinese character is varchar, garbled characters (displayed as?) are displayed ??). In addition, the host normally supports Chinese environments. Therefore, if varchar is used to store data, it cannot be found in the development stage. In most cases Deployment
Of course, using nvarchar to store English characters will increase by a factor of storage space. However, given the low storage cost, compatibility will bring you more benefits.
Therefore, you should try to use nvarchar to store data during design. varchar is used only when you ensure that this field does not save Chinese characters. There will be no problems.
But! If the deployed host is an English operating system and does not support the Chinese environment, the problem arises. All varchar fields are garbled when they are stored in Chinese ??). In general, you do not know that this is because you use the wrong data type to store the data. You will try to install Chinese fonts, try to set the language environment of the operating system... these cannot solve the problem. The only solution is to set the database field type to nvarchar (or nchar ). anyone familiar with project management should know that it is terrible to modify the database at the deployment stage.
Another advantage of using nvarchar is that you do not need to consider the differences between Chinese and English characters when judging strings.

Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes.

If you are still struggling with this issue, let's take a look at the explanation below and make a decision.

Generally, if you use Chinese characters or other special characters, I will use the type starting with N. Otherwise, I will directly start with var.

 

What is the difference between varchar and nvarchar in SQL Server?

A:
Varchar (N)
Variable-length and non-UNICODE character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes.

Nvarchar (N)
Unicode data with a variable length of n characters. The value of N must be between 1 and 4,000. The storage size of bytes is twice the number of input characters.

The two fields have Field Values: Me and coffee.
The varchar field occupies 2 × 2 + 6 = 10 bytes of storage space, while the nvarchar field occupies 8 × 2 = 16 bytes of storage space.

If the field value is only in English, you can select varchar. If the field value contains many double-byte (Chinese, Korean, etc.) characters, use nvarchar.Differences between char varchar nvarcharOnline help:

Char (N)	Fixed Length	High indexing efficiency using TRIM in programs to remove unnecessary blank space	NIt must be a value between 1 and 8,000. The storage size isNBytes
Varchar (N)	Variable Length	Efficiency not as high as char flexibility	NIt must be a number between 1 and 8,000. The storage size is the actual length of the input data bytes, insteadNBytes
Text (N)	Variable Length	Non-Unicode data
Nchar (N)	Fixed Length	Unicode data type processing (all characters are expressed in two bytes)	NMust be between 1 and 4,000. The storage size isNDouble the byte
Nvarchar (N)	Variable Length	Unicode data type processing (all characters are expressed in two bytes)	NMust be between 1 and 4,000. The storage size of bytes is twice the number of input characters. The entered data character length can be zero.
Ntext (N)	Variable Length	Unicode data type processing (all characters are expressed in two bytes)

Source: http://blog.sina.com.cn/s/blog_44e571d70100cagf.html

In general, if it contains Chinese characters, use nchar/nvarchar. If it contains English letters and numbers, use Char/varchar.

The summary here is good.
Http://www.cnblogs.com/ebaidu/archive/2007/08/14/854778.htmlMany developers often do not consider the char and varchar types when designing databases. Some developers do not pay attention to them because the storage price is getting cheaper and cheaper, I forgot some of the basic design theories and principles at the beginning. This reminds me of the young people who have moved away from their hands with a wave of RMB. In fact, I want to be a human or a human, development is good, and the grasp of details directly determines a lot of things. Of course, there are still some people who just don't figure out their differences, so they just choose one. Here I want to make a simple analysis on them. Of course, if there is something wrong with them, I hope you can give me some advice.

1. Char.It is very convenient for Char to store fixed-length data, and the indexing efficiency of char fields is high,For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. If not, it is automatically filled with spaces, therefore, trim () may be used multiple times during reading ().

2. varchar.Store variable-length dataBut the storage efficiency is not as high as char.If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as varchar (10. The actual length of the varchar type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length.In terms of space, it is appropriate to use varchar;Considering the efficiency, the key to the proper char is to find the trade-off based on the actual situation.

3. Text. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

4. nchar, nvarchar, and ntext. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist,Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes.The length of nchar and nvarchar is between 1 and 4000. Compared with Char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains English letters and numbers, use Char/varchar.

I will summarize their differences:
Char, Nchar fixed length, fast speed, large space, need to be processed
Varchar, nvarchar, and text cannot be long, the space is small, and the speed is slow. No processing is required.
Nchar, nvarchar, and ntext process Unicode codes Find a piece of information below, source http://wenku.baidu.com/view/eee97bf5f61fb7360b4c652b.html In SQL Server, varchar stores data in a single byte. nvarchar uses Unicode to store data. when a Chinese character is stored in SQL Server, it is saved as two bytes (generally Unico encoding) and an English character is saved to the database. If the field type is varchar, it only occupies One byte If the field type is nvarchar Two bytes .
Normally, we use varchar You can also store Chinese characters. However, if the operating system is an English operating system and the Chinese font is Incomplete support In SQL Server, if the Chinese character is varchar, garbled characters (displayed as?) are displayed ??). In addition, the host normally supports Chinese environments. Therefore, if varchar is used to store data, it cannot be found in the development stage. In most cases Deployment
Of course, using nvarchar to store English characters will increase by a factor of storage space. However, given the low storage cost, compatibility will bring you more benefits.
Therefore, you should try to use nvarchar to store data during design. varchar is used only when you ensure that this field does not save Chinese characters. There will be no problems.
But! If the deployed host is an English operating system and does not support the Chinese environment, the problem arises. All varchar fields are garbled when they are stored in Chinese ??). In general, you do not know that this is because you use the wrong data type to store the data. You will try to install Chinese fonts, try to set the language environment of the operating system... these cannot solve the problem. The only solution is to set the database field type to nvarchar (or nchar ). anyone familiar with project management should know that it is terrible to modify the database at the deployment stage.
Another advantage of using nvarchar is that you do not need to consider the differences between Chinese and English characters when judging strings.