WebDevelopersNotes logo

home-icon Home / MySQL / MySQL database Column Types

MySQL database Column Types

MySQL database Column Types

The three major types of column types used in MySQL are

  • Integer
  • Text
  • Date

Choosing a column data type is very important in order to achieve speed, effective storage and retrieval. Hence, I’ve dedicated two sessions to this topic. Now, I’ll be touching only the surface; for a thorough explanation refer the resources in What Next? session.

MySQL Numeric Column Types

In addition to int (Integer data type), MySQL also has provision for floating-point and double precision numbers. Each integer type can take also be UNSIGNED and/or AUTO_INCREMENT.

  • TINYINT: very small numbers; suitable for ages. Actually, we should have used this data type for employee ages and number of children. Can store numbers between 0 to 255 if UNSIGNED clause is applied, else the range is between -128 to 127.
  • SMALLINT: Suitable for numbers between 0 to 65535 (UNSIGNED) or -32768 to 32767.
  • MEDIUMINT: 0 to 16777215 with UNSIGNED clause or -8388608 to 8388607.
  • INT: UNSIGNED integers fall between 0 to 4294967295 or -2147683648 to 2147683647.
  • BIGINT: Huge numbers. (-9223372036854775808 to 9223372036854775807)
  • FLOAT: Floating point numbers (single precision)
  • DOUBLE: Floating point numbers (double precision)
  • DECIMAL:Floating point numbers represented as strings.

Date and time column types

  • DATE: YYYY-MM-DD (Four digit year followed by two digit month and date)
  • TIME: hh:mm:ss (Hours:Minutes:Seconds)
  • DATETIME: YYYY-MM-DD hh:mm:ss (Date and time separated by a space character)
  • TIMESTAMP: YYYYMMDDhhmmss
  • YEAR: YYYY (4 digit year)

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

Sometimes i just need a break

Sponsored Links

Tips

How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]

Fact

At one time, Coca-Cola had an online music store - myCokeMusic.com. It ceased operation on 31st July 2006. Even though they had myCokeMusic.com music store, Coca-Cola still did a promotion that involved Apple iTunes. In July 2005, a free iTunes song was given away with every 32 US fl oz Slurpee bought from 7-Eleven. [more...]