MySQL Tutorial

Learn MySQL step by step


Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step

Oracle SQL

Tutorial for Oracle SQL

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions
Unix For Oracle DBA 20 Questions










Data Types in MySQL

MySQL supports all the SQL standard data types. In addition to this it comes with several other types also. These data types can be classified into following  categories

Numeric Datatypes

Numeric data types can be further divided into following categories


Integer Data Types

The following in the range of values you can enter in various INTEGER data types.

integer datatype range

For example, if you designate a column as  INT or INTEGER then you can enter values between -2147483648 to 2147483648 if SIGNED and values between 0 to 4294967295 for UNSIGNED

What is the difference between SIGNED and UNSIGNED

The signed columns can contain both negative values as well as positive values, whereas, unsigned columns can contain only positive values. If you want to designate a column as unsigned then you have to explicitly  mention it in the CREATE TABLE command because by default the data types are assumed as signed.

Fixed Point Data Types

The DECIMAL and NUMERIC types store exact numeric data values and usually used when we need to maintain exact precision. DECIMAL and NUMERIC data types are identical.

The syntax of declaration is

<column_name> DECIMAL(P,S)

Where P stands for precision and S stands for scale. Precision means the total digits a column can have and scale means the position of decimal point from right.

For example, if we declare a column as decimal(6,2) then this column can contain a number with max 4 digits and 2 decimals, so it can store a value in the range -9999.99 to 9999.99

BIT Data Type

The BIT data type is used to store bit values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64

The BIT data type is commonly used to store Boolean values like 0 for False and 1 for True

String Data Types

The String data type is used to store character values. MySQL comes with different string data types. Below is the list of commonly used String data types


The CHAR data type is fixed width data type i.e. if you specify a column as CHAR(50) then MySQL will store 50 bytes for this column values irrespective of how many characters you enter in that column. For example if you enter a string of just 10 chars in this column MySQL will occupy 50 bytes only and not 10 bytes. That's why this data type is not recommended because of inefficient storage management.

The CHAR datatype width can be between 0 to 255.


The VARCHAR data type is variable width data type,  in contrast to CHAR data type, the VARCHAR datatype will occupy only that many bytes as the number of characters you entered in that column. For example if you specify a column as VARCHAR(50) and then you entered a string of 10 characters then this column will occupy 10 bytes only. This data type is recommended because of it's efficient storage management.

The VARCHAR data type width can be between 0 to 65, 535


What If, you want to store text of more than 65,535 characters, in this case, you can use the TEXT data type. The TEXT data type can hold unlimited number of characters. Depending upon your requirements of different length of TEXT data types are available, they are

These are all same types only distinguished with the length of the data they can contain.

The following is the minimum and maximum length of these types.

Data Type Maximum data it can hold
TINYTEXT 255 bytes
TEXT 65,535 bytes


DATE & TIME Data Types

To store date and time values you can use date type available in MySQL. MySQL supports the following date types


A DATE data type can store only date part, it will not store time.

MySQL supports dates from 1000-00-01 to 9999-12-31 i.e. you can enter dates only between this range.

If you enter a date with only last two digits of year then, MySQL will interpret the year as 2000 to 2069 if the year is between 00-69 and it will assume it to be 1970 to 1999, if the year is
between 70-99.

The default format of DATE values are YYYY-MM-DD



A DATETIME data type can store both dates as well as time. The default format of datetime values is 'YYYY-MM-DD HH:MM:SS'. The range of values you can store is
 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'


The TIMESTAMP data type can also store both date and time parts. But it's range is limited. A TIMESTAMP type can have values in the  range of '1970-01-01 00:00:01' UTC to '2038-01-1903:14:07' UTC.

Both DATETIME and TIMESTAMP data types can also store Fractional seconds upto 6 digits also. The default format for storing fractional seconds is 'YYYY-MM-DD HH:MM:SS[.fraction].

You can store values in the range

For DATETIME from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

For TIMESTAMP from '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

Binary Data Types (BLOB)

A BLOB is a binary large object that can hold a variable amount of data. This data type is usually used for storing images or binary files. There are different BLOB types available in MySQL they are

These are all same types only distinguished with the length of the data they can contain.

The following is the minimum and maximum length of these types.

Data Type Maximum data it can hold
TINYBLOB 255 bytes
BLOB 65,535 bytes





HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved