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 data types can be further divided into following categories
The following in the range of values you can enter in various INTEGER data types.
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.
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
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
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 |
| MEDIUMTEXT | 16 MB |
| LONGTEXT | 4GB |
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
Where
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'.
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 |
| MEDIUMBLOB | 16 MB |
| LONGBLOB | 4GB |
Interface Computers Academy © 2007-2017 All Rights Reserved