Overview

In this tutorial, you will learn how to use the best data types when creating a new table in your database for your applications. Most programmers simply put Integer and Text for all their columns but choosing the smallest type for your application is important.

Why would you use a 65000 characters memory space when you only need 10? This concept is even more important when developing desktop applications such as Visual Basic, C, etc. MySQL offers a wide range of possible data types and many of them are not even available in most programming language.



General Data Types

There are 3 main categories of data types:

- Numeric
- Date and Time
- String

All of these categories contains multiple types depending of the size of the data you'll use. You'll also learn in this tutorial that you can use Numeric values for Date and Time in various situations.

It is usually a good idea to store to most optimized version of your data and then re-arrange it for processing in your script. For example, while you might want to have "January 25th, 2007 10:41 AM" as the value in your database, it is definitely not the best choice. This value should instead be a single number.

MySQL is mostly used with web programming language like PHP which is probably the most important. PHP as the ability to convert from one format to another on the fly using prebuilt function. In fact, you can generate any date and time format from a number.

Table of Contents

- phpMyAdmin - Introduction to creating tables
- Using Numeric Types
- Using Date and Time Types
- Using String Types
- Miscellaneous Data Types

On the next page, you will learn how to create table using phpMyAdmin...

phpMyAdmin - Introduction to creating tables

phpMyAdmin is a powerful tool widely installed on most web servers. It offers a graphical interface to manage efficiently your table. Ask your server administrator if you're not sure but any cPanel server have it installed already.

To access phpMyAdmin:

Under cPanel:  simply click the Manage Databases tool and click phpMyAdmin at the very bottom of the page.

Most Web Servers: simply add the "phpMyAdmin/" sub-directory to your domain to access it. This is case-sensitive so depending on your installation, you will have to check which letter are uppercase, otherwise you'll get a nice 404 error.

Using Numeric Types

Numeric types are obviously used for numbers but also for many other formats. For example, the most optimized way to store a date/time value is with a single number. Booleans (True or False) does not exist outside PHP, Visual Basic and some others languages. In fact, booleans are integers displayed as 0 and 1. You will use the same method to store a boolean in a database.

When creating a new key in your table structure, you have a big list of possible numeric data types. While most users simply use the Integer type, it is recommended to use the smallest type as possible. So if you only need to store values under 100, there's no need to use an Integer.

There's another parameter which is important to set correctly. You have the abilty to declare a type as SIGNED or UNSIGNED. By default, all numeric types are signed. When a type is UNSIGNED, you can store a larger value but can't use negative values. The SIGNED types are smaller for the maximum but you can store negative values. Most of the time, you won't need to use negative values so UNSIGNED is the best choice.

This is the table of the types and their corresponding SIGNED/UNSIGNED maximums:

TINYINT (1 BYTE)
signed: min(-128) max(127)
unsigned: min(0) max(255)
 
SMALLINT (2 BYTES)
signed: min(-32768) max(32767)
unsigned: min(0) max(65535)
 
MEDIUMINT (3 BYTES)
signed: min(-8388608) max(8388607)
unsigned: min(0) max(16777215)
 
INT (4 BYTES)
signed: min(-2147483648) max(2147483647)
unsigned: min(0) max(4294967295)
 
BIGINT (8 BYTES)
signed: min(-9223372036854775808) max(9223372036854775807)
unsigned: min(0) max(18446744073709551615)

You can use the BOOL type which will set the key as TINYINT with a length of 1.

Using Date and Time Types

Date and Time formats can be stored in various ways inside a database. All have their pros and cons, but I will insist on one which is the most optimized way to store a value.

The Date and Time types will be stored and displayed using a readable format. You can choose to use only the YEAR type, TIME, DATE, DATETIME and TIMESTAMP. They use a fixed string format. For example, the TIMESTAMP has a fixed 19 characters length (YYYY-MM-DD HH:MM:SS). You should use these types only if you need to perform relative dates queries in your database. For example, if you need to select rows in a range of days or relatively to a previous date.

While most users use these types, I recommend using an integer to store the date and time values. They are easier and faster to work with. In various language, you can convert to numeric and reconvert it back to a readable date on the fly using prebuilt functions.

Did you know 1167652800 is in fact January 1st, 2007 at 12 PM?
It is much smaller to use than 2007-01-01 12:00:00

But what is that number you might ask? It is the number of seconds since the Unix epoch (1/1/1970). Obviously, this value is always second accurate. So while you might want to just use the month, you can also retrieve the minutes or seconds at any time in the future.

In PHP, you can use the time() function to retrieve the current UNIX timestamp in seconds. You can then reconvert it back into a readable format using the date() function.

As a reference, these are the Date and Time types:

DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYY-MM-DD HH:MM:SS
TIME HH:MM:SS
YEAR YYYY

You might notice that the DATETIME type use the same format as TIMESTAMP. While they return the exact same format, they worked differently. During an INSERT or UPDATE query, the TIMESTAMP will automatically set itself to the current time. The TIMESTAMP also use 4 Bytes to store its values (like the INT) and can accept various format like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.


Using String Types

Strings come in various flavors and colors. In this tutorial, I will only cover the important ones (5 types) because the others are mostly in really specific applications.

The most important string type is VARCHAR. It has a variable length up to 255 characters. You only need to store a fixed 16 characters string? No problem, use VARCHAR with a length of 16. If you don't know the length, you should then use 255 which is the limit for that type. You are allowed to put a greater value than 255 but it will then converted to a TEXT type.

The second type is the TEXT. It can support values up to 65,535 characters. This is usually more than enough for most projects. You don't need to specify a length for this type.

In some rare cases, you will need even more space. You should then use the LONGTEXT type which can support values up to 4,294,967,295 characters. In theory, you should never be able to get to that limit...

There're 2 other types which are less used but still worth mentioning. The TINYTEXT can support 255 characters (fixed unlike VARCHAR) and MEDIUMTEXT can support 16,777,215 characters.


Miscellaneous Data Types

There is 1 other important type which is really useful when you need to specify pre-determined values. For example, you could want to store values from "Google", "Microsoft" and "Yahoo". Of course you could use a standard VARCHAR but the better choice would be to use an ENUM var type.

The ENUM lets you enter a list of possible values. Only values corresponding to the ones you entered when creating the key can be inserted. The possible values are also sorted in the order you enter them.
You enter the possible values in this format:

'Google','Microsoft','Yahoo'