Difference Between CHAR and NCHAR in MySQL

In MySQL, both CHAR and NCHAR are ASCII character data types used for storing text data, but they differ significantly in terms of storage, data representation, and performance.

CHAR and NCHAR columns can have different collations, determining how strings are compared and sorted. The CHAR type typically uses the collation associated with its specified character set. On the other hand, NCHAR is intended for Unicode data and typically uses a collation that can handle Unicode characters, ensuring proper sorting and comparison.

Understanding 'CHAR' in MySQL

The CHAR data type is primarily used to store ASCII character data. It is a fixed-length data type, which means it reserves a specified number of bytes, and if the actual data is smaller, it will pad the remaining space with spaces.

Due to its fixed-length nature, CHAR can be slightly faster for operations where the length is always the same, making it efficient for indexing and retrieval.

Syntax

column_name char(number_of_bytes);

Example

In the following example, every entry in the `name` column will occupy 10 bytes. For the name "John", MySQL will store it as "John " (with 6 spaces for padding).

CREATE TABLE example_char (
   name CHAR(10)
);

Understanding 'NCHAR' in MySQL

The NCHAR data type is used to store Unicode character data, making it capable of holding a wide variety of characters from different languages and scripts. It requires more storage space since it encodes each character using two bytes.

It may be less efficient in some cases because it handles varying lengths, it is generally more adaptable and saves space. Unicode is a universal encoding standard that assigns a unique numeric value to each character, allowing for the representation of letters and symbols from different languages and scripts.

Syntax

column_name nchar(number_of_bytes);

Example

In the following example, each entry in the `name` column will use 10 bytes as well, but it can store characters from various languages, such as Chinese or Arabic.

CREATE TABLE example_nchar (
   name NCHAR(10)
);

Key Differences Between CHAR and NCHAR

Following are some common differences between CHAR and NCHAR in MySQL.