Data types… They are oh so important when designing your database tables. In this blog post, I look at two similar string data types: CHAR
and VARCHAR
. Chances are you will likely use one, the other, or both in MySQL. But what is the difference in them? I want to know myself. So let’s learn together…
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- MySQL 5.7.22

Photo by Jelleke Vanooteghem on Unsplash
CHAR and VARCHAR
Both of these character data types share some commonalities, but the differences are all their own.
Facts
Here is a summary of facts I deduced from the on-line documentation (see link in closing section):
CHAR
andVARCHAR
both, are declared with a maximum length of characters available for storage.CHAR
columns are fixed to the length declared- When
CHAR
column values are stored, they are stored with ‘right-padded’ spaces up to the specified length. i.e., ACHAR(10)
column value of ‘Carl’ would be stored with those four characters, in addition to, 6 spaces to make up a total of 10. (e.g.,'Carl '
) - The
CHAR
type retrieval and display, hinges on thePAD_CHAR_TO_FULL_LENGTH
SQL mode flag. (We will see an example below) VARCHAR
column values are not stored with padding.VARCHAR
column values with trailing spaces in excess of the specified length are truncated, also generating a warning regardless of the SQL mode, onINSERT
.CHAR
column values containing trailing spaces in excess of set length are truncated silently onINSERT
regardless of the SQL mode.
I’ll work with an example similar to that found in the official documentation, but tailor it to this blog post.
There are columns for both CHAR
and VARCHAR
data types with set lengths of 5
in this test table:
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| vc_name | varchar(5) | YES | | NULL | |
| c_name | char(5) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
I’ll run 3 INSERT
statements, with various string lengths on both columns:
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('five1','five1');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('five1 ','five1 ');
Query OK, 1 row affected, 1 warning (0.03 sec)
Appears that last INSERT
produced a warning.
Let’s look at it:
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Note | 1265 | Data truncated for column 'vc_name' at row 1 |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)
The truncation warning is for the VARCHAR
column due to the extra space counting as 1 character beyond the allowable 5 set for specification during table creation.
But, the CHAR
column did not render any warning.
Let’s verify the length of both columns’ current set of values:
-> FROM compar_2;
+-----------------+----------------+
| LENGTH(vc_name) | LENGTH(c_name) |
+-----------------+----------------+
| 4 | 4 |
| 5 | 5 |
| 5 | 5 |
+-----------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT CHAR_LENGTH(vc_name),CHAR_LENGTH(c_name)
-> FROM compar_2;
+----------------------+---------------------+
| CHAR_LENGTH(vc_name) | CHAR_LENGTH(c_name) |
+----------------------+---------------------+
| 4 | 4 |
| 5 | 5 |
| 5 | 5 |
+----------------------+---------------------+
3 rows in set (0.00 sec)
That makes sense.
Almost.
We inserted 3 sets of values across the table with lengths of 4, 5, and 6.
However, row 3, shows a length of only 5 even though five1
is 6 characters long counting the space.
Space is significant here as the ‘extended’ character beyond the set limit of 5, as we will see moving forward.
To know the difference between a space and an actual character beyond the allowable 5, we can INSERT
values 6 characters in length, not using spaces as the ‘offending’ character:
ERROR 1406 (22001): Data too long for column 'vc_name' at row 1
That INSERT
outright failed on the VARCHAR
column.
What about the CHAR
column?
ERROR 1406 (22001): Data too long for column 'c_name' at row 1
That failed as well.
Keep this in mind as we move forward in the blog post.
Want to really get water in the oil?
We can, by tweaking the SQL mode.
These SQL mode flags are currently enabled at the global and session levels on my system:
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
And at the session level:
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
We can alter behavior for these data types by changing the SQL mode.
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET @@session.sql_mode = PAD_CHAR_TO_FULL_LENGTH;
Query OK, 0 rows affected (0.00 sec)
Before moving on, I want to look at the PAD_CHAR_TO_FULL_LENGTH
flag and what it accomplishes, by visiting the detailing documentation
Here is the gist of it, provided from the documentation section:
By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.
Okay, let’s see what that looks like upon retrieval for the column values:
+-----------------+----------------+
| LENGTH(vc_name) | LENGTH(c_name) |
+-----------------+----------------+
| 4 | 5 |
| 5 | 5 |
| 5 | 5 |
+-----------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT CHAR_LENGTH(vc_name), CHAR_LENGTH(c_name) FROM compar_2;
+----------------------+---------------------+
| CHAR_LENGTH(vc_name) | CHAR_LENGTH(c_name) |
+----------------------+---------------------+
| 4 | 5 |
| 5 | 5 |
| 5 | 5 |
+----------------------+---------------------+
3 rows in set (0.00 sec)
Note here, CHAR
column c_name
is now showing a length of 5 for the first row that was previously showing 4.
This is due to the PAD_CHAR_TO_FULL_LENGTH
flag being enabled. Reminder, this is at the session level (@@session.sql_mode
).
Does PAD_CHAR_TO_FULL_LENGTH
have any effect on INSERT
‘s for these columns?
Let’s test and see.
Query OK, 1 row affected, 2 warnings (0.03 sec)
2 warnings for that INSERT
.
Let’s see them:
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'vc_name' at row 1 |
| Warning | 1265 | Data truncated for column 'c_name' at row 1 |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)
* Note: This INSERT
previously failed prior to changing the SQL mode. The data is inserted, and truncated, but not failing. Producing only a warning now.
Let’s see the values in the table as of now.
+---------+--------+
| vc_name | c_name |
+---------+--------+
| five | five |
| five1 | five1 |
| five1 | five1 |
| four1 | four1 |
+---------+--------+
4 rows in set (0.00 sec)
One more test to solidify this section prior to closing out this blog post. I will INSERT
values 2 characters in length:
Query OK, 1 row affected (0.03 sec)
Again, notice the CHAR
column value returned below shows a length of 5 and not 2 as the VARCHAR
column does:
+-----------------+----------------+
| LENGTH(vc_name) | LENGTH(c_name) |
+-----------------+----------------+
| 4 | 5 |
| 5 | 5 |
| 5 | 5 |
| 5 | 5 |
| 2 | 5 |
+-----------------+----------------+
5 rows in set (0.01 sec)
This initial venture into the depths of MySQL’s CHAR
and VARCHAR
data types surely won’t be my last. I plan to continue learning and exploring them as I progress and needs dictate.
My hope with this blog post is for readers with more understanding and experience, will provide me corrective guidance to any areas I may have erred on, with supportive pointers and advice.
Even better, additional resources/examples for continued learning, study, and development is much appreciated as well.
So by all means, leave any relevant comments below.
Explore the official MySQL 5.7 On-line Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.