In here I explain the types of MYSQL data types. There are many possible types of datas are available in php with which the PHP variable may be initialized. Similarly MYSQL support several datatypes.
The database table contains multiple columns with attributes or with data types such as string or numbers or numberic. MYSQL provides different data types. Each daya type in MYSQL can be determined buy the following characteristics:
- The space that takes up and whether the values is a fixed-length or variable length.
- The kind of values it represents.
- How MySQL compares the values of a specific data type.
- The values of the data type can be indexed or not.
A datatype specifies particular types of data such as integer, boolean, float etc. A data types also specifies the possible values for that types, the operations can be performed on that type and MySQL supported data types that are huge in number, it separated into three category. They are,
- Numeric Data Type
- String Data Type
- Date and Time Data Type
Numeric Data Type
All standard SQL numeric data types supports in MYSQL. Createing MYSQL table with numeric data attribute, then this attibutes accepts only unsigned and signed numbers. The following list shows the common numeric data types and their descriptions:
| Numeric Types | Description |
|---|---|
| INT | A standard integer data type it can be signed or unsigned. Basically it’s a normal sized integer . If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. |
| TINYINT | It’s a very small integer data type in numeric. It can be signed or unsigned. If signed, the allowable range between -128 to 127. If unsigned, the allowable range between 0 to 255. |
| SMALLINT | It’s a small integer data types in numeric. Signed values range from -32768 to 32767. Unsigned values range from 0 to 65535. |
| MEDIUMINT | It’s a medium sized integer data type in numeric. It can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. |
| BIGINT | A large integer. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. |
| FLOAT(M,D) | A single-precision floating point number. A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). |
| DOUBLE(M,D) | It’s a double-precision floating point number in numeric data types. It support only signed numbers. |
| DECIMAL(M,D) | It’s a fixed-point number in numeric data types. And it’s unpacked floating-point number that cannot be unsigned. NUMERIC is a synonym for DECIMAL. |
String Data Type
In MYSQL, it support several string data type like CHAR, TEXT, VARCHAR etc.
| Data Type | Description |
|---|---|
| CHAR | CHAR holds fixed length string betweeen 1 and 255 characters in length (for example CHAR(6)). |
| VARCHAR | A variable-length string between 1 and 255 characters in length; for example VARCHAR(25). |
| BLOB / TEXT | A field with a maximum length of 65535 characters. BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT. |
| BINARY / VARBINARY | Both are differed with the fixed and variable length order of binary strings instead of the character string and has no character set like BLOB. |
| ENUM / SET | NUM typed attribute will accept values which are limited with the given set of values. And, SET can have an entire set of values as a whole. |
Date and Time Types
The data and time mysql data types are provided for representing temporal data that are used for MySQL date/time manipulation.
| Data Types | Description |
|---|---|
| DATE | This displayed as ‘YYYY-MM-DD’. It’s between 1000-01-01 and 9999-12-31. It accept the data value. |
| DATETIME | Displayed as ‘YYYY-MM-DD HH:MM:SS’. It’s between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. |
| TIMESTAMP(m) | Displayed as ‘YYYY-MM-DD HH:MM:SS’. |
| TIME | Displayed as ‘HH:MM:SS’. |
| YEAR[(m] | Default is 4 digits. Stores a year in 4-digit or 2-digit format. |
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.