• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Learn data types

Log in or subscribe for free to enjoy all this course has to offer!

Remember that huge list of data types that we saw when we set up the tables?

We'll go into that list in a bit more detail soon, but first let's take a quick look at a typical HTML form.

You've seen forms like this a million times.  What do you think the appropriate data types are for each field?

The first name, last name and company name will all be  VARCHAR s.  The length could be 100 characters long; while that is a bit overkill, it's always good to be safe!

The email address will also be a  VARCHAR  with a length of 50 or 100.

The phone number could be made into an integer by cutting out the dashes (-) or whatever is breaking up the number into smaller parts.  An  INT  won't work because the maximum size of an unsigned  INT  is 11, which translates to a maximum value of 4,294,967,295. The max value of a signed INT is 2,147,483,647.  That means that a phone number like 9175555555 is too big for the type  INT . 

The easiest format for telephone numbers is a  VARCHAR .  Validate the format of the phone number in your code before it reaches the database. Date of birth will be a  DATE .  MySQL stores the date like this:  YYYY-MM-DD .

Additionally, the  DATETIME  format  (where the time is also included) saves like this:  YYYY-MM-DD HH:MI:SS .

Gender is ususally saved as  VARCHAR  with a length of 1 which will accommodate an  M  or an  F . 

Finally, Comments will be saved with the type  TEXT .

Let's examine the list of datatypes more sequentially!

Text Types

VARCHAR  usually correspond to an html input box. It can hold up to 255 characters. 

CHAR  can hold up to 30 characters, and you'll almost never use this type.

TEXT  usually corresponds to an HTML  textarea  box and you don't specify the limit of characters.  Store your novel in this format!

Numeric types

For numeric data the choices are:

  • INT  - if this is a signed integer it can hold a value between -2147483648 and 2147483647

  • TINYINT  - an integer between -128 and 127

  • SMALLINT  - an integer between -32768 and 32767

  • BIGINT  - an integer between -9223372036854775808 and 9223372036854775807

  • DECIMAL  - a format for numbers that include decimals

  • FLOAT   - a format good for money

  • DOUBLE  - when you are going to save values returned from sin( ), cos( ), and sqrt( ) - so you want a high degree of precision with lots of fractional decimal places.

Date types

Formatting dates is pretty straight forward in MySQL.  As mentioned, this DBMS saves the DATE format like this:

YYY-MM-DD

DATETIME is saved like this:

YYYY-MM-DD HH:MI:SS

TIMESTAMP is saved using the same format as DATETIME:

YYYY-MM-DD HH:MI:SS

And year is either YYYY or YY.

NULL - the value of no value!

If a tree falls in the forest and no one is around, does it make a noise?

In the case of a database field that has been set to accept NULL, the tree fell and didn't make a noise,  but it did leave a trace.

NULL means that the value has not been set.  This is different from an empty string ("").  The empty string means that the value of "" has been set into this field.

Example of certificate of achievement
Example of certificate of achievement