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.
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.
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.
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.
represented in the form of
YYYY-MM-DDfor example 2014-08-25
represented in the form of
HH:MM:SSfor example 07:33:29
represented in the form of
YYYYfor example 14 or 2014
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.
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.
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.
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.
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.
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.
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