Introduction to MySQL CHAR

In MySQL, CHAR is a fixed-length string type.

In this article, we’ll take you through MySQL CHAR data type.

In MySQL, CHAR is a fixed-length string type, which is different from VARCHAR data type. If a column is defined as the CHAR type, the length (number of characters) of the content in the column is the length defined in the column.

MySQL CHAR syntax

To use the CHAR data type, we need to specify a length. Its syntax is as follows:

CHAR(length)

here:

  • length is a numeric value that indicates the number of characters in this column.
  • length Can be any value from 0 to 255.
  • If we don’t specify this value, the default value is 1. That is to say, CHAR is equivalent to CHAR(1).

If the length of the string inserted to the CHAR column is less than the specified character length, MySQL pads the source string with spaces up to the specified length. When you read the value from the CHAR column , MySQL removes trailing spaces.

Because the CHAR data type is stored with a fixed length, the performance of CHAR is better than VARCHAR.

MySQL CHAR Examples

Let’s create a table test_char as a demo table with the following statement:

CREATE TABLE test_char (name char(5) NOT NULL);

Insert value into CHAR column

Now, let’s insert two rows into the test_char table using the following SQL statement:

INSERT INTO test_char VALUES ('Harve'), ('Tim');

Then let’s query the rows from the table test_char:

SELECT name, LENGTH(name)
FROM test_char;

+-------+--------------+
| name  | LENGTH(name) |
+-------+--------------+
| Harve |            5 |
| Tim   |            3 |
+-------+--------------+

Here, the length of Tim is 3 because CHAR MySQL automatically removes trailing spaces when reading the value in the column.

Suffix spaces problem

Now, let’s insert a value ' Tom ' with leading and trailing spaces into the column:

INSERT INTO test_char VALUES(' Tom ');

Let’s look at the results again:

+-------+--------------+
| name  | LENGTH(name) |
+-------+--------------+
| Harve |            5 |
| Tim   |            3 |
|  Tom  |            4 |
+-------+--------------+

Here, ' Tom ' becomeed ' Tom'. This is because MySQL removes trailing spaces from the CHAR value, regardless of whether the spaces were original or not.

That is, MySQL ignores trailing spaces in strings. For example 'Tom' is equivalent to 'Tom ' or 'Tom '.

This can be problematic if a CHAR column is a unique column.

Insert value that exceeds length

Let’s insert a CHAR value whose length exceeds the length of definition:

INSERT INTO test_char VALUES('Steven');

MySQL does not automatically truncate strings, it will return the following error:

ERROR 1406 (22001): Data too long for column 'name' at row 1

Conclusion

In this article, we learned MySQL CHAR data type and how to handle MySQL CHAR data types:

  • CHAR value is stored in a fixed length, so it is more efficient than VARCHAR.
  • You should specify a length for CHAR type. It can be any value between 0 and 255. If not specified, the default is 1.
  • For CHAR values, MySQL ignores trailing spaces. When reading the value of a CHAR column, MySQL removes trailing spaces automatically.