Not all data is created equal! We know from working with variables in PHP and JavaScript that variables can hold all kinds of data. MySQL is not different in this sense. The various data types in MySQL will have an impact on how much space on disk the database will use. They will also have an impact on performance. If the data types in use for your fields are not optimized, you could run into a slow running application, with your database being the bottleneck. Now that we have a good Introduction to MySQL under our belt, let’s take a closer look at data types in MySQL and wrap our arms around the nuts and bolts of how they work.

---

We can start out by taking a look at the schemata table from the information_schema database.

mysql> show columns from information_schema.schemata;

+----------------------------+--------------+------+-----+---------+-------+
| Field                      | Type         | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME               | varchar(512) | NO   |     |         |       |
| SCHEMA_NAME                | varchar(64)  | NO   |     |         |       |
| DEFAULT_CHARACTER_SET_NAME | varchar(32)  | NO   |     |         |       |
| DEFAULT_COLLATION_NAME     | varchar(32)  | NO   |     |         |       |
| SQL_PATH                   | varchar(512) | YES  |     | NULL    |       |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

What we want to do here is focus on that second column named type. This is what tells us what data type will be stored here. The results here are all of the varchar data type. In addition, we can see that each field has a specific number associated with the varchar. This is telling us how many characters can be stored. Let’s quickly define a few data types.

Varcar

This is a very flexible data type for strings, especially when you are not sure exactly how long the length will be. Names and addresses are common uses for the varchar data type. Varchar has a maximum of 255 characters per field.

Char

When you know how many characters you need, you can make use of the char data type. This is a fixed width data type. Therefore, you don’t want to allocate a char data type with a 56 character size if you are only going to use 3 characters. It is best to use char only when the data to be stored will have the same length. Accuracy counts here.

Text

The text data type is a character string just like varchar and char. It is a variable length data type which can go up to four gigabytes per field! Use this data type carefully.

Character Sets and Bytes

This leads us to an interesting paradox. When defining database fields, we get to specify the length in characters that a field will hold. We are not able to specify the number of bytes however. This is where character sets come in.

latin1 is the default character set in MySQL and it uses 1 byte of data per character to be stored in the database. Note: If you end up with data that requires more than the 1 byte offered by latin1, the data might appear as question marks in the database – not fun!

utf8 on the other hand used up to 4 bytes per character. If you have an application that might be using foreign languages, or any type of special characters, you might want to consider changing your character set to utf8.

What are MySQL Text Sizes?

There are four different ways to define a text field. These are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. What type you choose depends on how much you want to store. In general, it is a best practice to use the smallest data type possible to store your data while still allowing your application to run properly. This is because as capacity goes up, so does overhead. The MySQL docmentation has a great section dedicated to storage requirements.

Dates and Times

Dates, times, and timestamps are actually strings in the most generic sense, but they do have their own data types.

• DATE

  represented in the form of YYYY-MM-DD for example 2014-08-25

• TIME

  represented in the form of HH:MM:SS for example 07:33:29

• YEAR

  represented in the form of YY or YYYY for example 14 or 2014

• TIMESTAMP

  This is a very useful data type, although it does have a limited range of 1970-01-01 through 2038-01-19. What makes this data type great however, is it’s ability to automatically populate on record creation or when it is updated.

• DATETIME

  This is similar to the TIMESTAMP data type, however it is *not* dynamic. You must calculate the value you want to enter and manually insert yourself. That’s a drawback. The benefit however, is that the range is greater.

MySQL Numbers

There are several ways to store numbers depending on what you would like to store in the database. Like text data, you want to choose the most appropriate type for the data to be stored.

DECIMAL

You can store very specific numeric values with the DECIMAL data type. Let’s say you want to store the cost of a product, and you know that all products are going to be less than 100. We could specify DECIMAL(4,2). This would instruct MySQL to store four total digits with 2 digits after the decimal point. This would allow for -99.99 up to 99.99. Note the four total digits and two digits after the decimal point.

INTEGER

Integers are typically used for a unique id or some similar type of representation. The idea of integers brings into the concept of unsigned values. Most times, when you’re using integers, you won’t have a need for negative values. In this case, your integer will be unsigned.

Another interesting feature of this data type is automatic number assignment. You may be familiar with the idea of an auto incrementing id. AUTOINCREMENT is usually used in combination with the UNSIGNED data type. This way, you have an automatically updating, non negative, integer for use in your tables. This is unique to MySQL, it is not a part of standard SQL.

ENUM

The ENUM is a fancy looking datatype and may have confused you in the past. Be confused no more! ENUM simply stands for Enumerated list, and it is perfect for Yes / No type values to be stored. Instead of storing actual text to represent something in your application, it stores a number. Behind the scenes however, it is in fact associating that number to a specific text value. When you query the database, you will see the actual text. Think of options like free, paid, premium, or admin. With ENUM, you can easily allow for those 4 options, and do so within a small footprint of memory. Be advised, you can only choose one possible value. Think of it like a select tag in html.

SET

What if you want to have an array of choices so to speak, yet you would like to have more than one option selected? This is what the SET data type is for. A SET field can store up to 64 values. You are allowed to store none, some, or all of the available options.

MySQL Data Types Summary

While not the most exciting of things to have to know, data types in MySQL are one of those bread and butter topics that you just have to be aware of. In this episode, we covered several data types and what they can be used for. Let’s check out a table here:

MySQL Data Types
Strings	VARCHAR, CHAR, TEXT
Date/time	DATE, TIME, YEAR, TIMESTAMP, DATETIME
Numbers	DECIMAL, INT
Lists	ENUM, SET