MySQL String Data Type Dive: CHAR and VARCHAR – Differences.


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

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 and VARCHAR 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., A CHAR(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 the PAD_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, on INSERT.
  • CHAR column values containing trailing spaces in excess of set length are truncated silently on INSERT 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:

mysql> DESC compar_2;
+---------+------------+------+-----+---------+-------+
| 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:

mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('five','five');
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:

mysql> SHOW warnings;
+-------+------+----------------------------------------------+
| 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:

mysql> SELECT LENGTH(vc_name),LENGTH(c_name)
    -> 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:

mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('five11','five11');
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?

mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('five1','five11');
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:

mysql> SELECT @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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:

mysql> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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.

mysql> SET @@session.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:

mysql> SELECT LENGTH(vc_name), LENGTH(c_name) FROM compar_2;
+-----------------+----------------+
| 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.

mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('four11','four11');
Query OK, 1 row affected, 2 warnings (0.03 sec)

2 warnings for that INSERT.
Let’s see them:

mysql> SHOW warnings;
+---------+------+----------------------------------------------+
| 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.

mysql> SELECT * FROM compar_2;
+---------+--------+
| 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:

mysql> INSERT INTO compar_2(vc_name,c_name) VALUES('to','to');
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:

mysql> SELECT LENGTH(vc_name), LENGTH(c_name) FROM compar_2;
+-----------------+----------------+
| 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.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.