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.

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

Smoking near an Apple computer is not covered by warranty including AppleCare. This would probably cover all Apple devices because as per the company statement, "Nicotine is on OSHA's list of hazardous substances and Apple would not require an employee to repair anything deemed hazardous to their health." [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy