Introduction
MySQL Datatypes are the programming language, which contain set of values and data. A datatype defines an appropriate kind of data, like integer, floating-point, Boolean etc. A datatype also defines the attainable values for that type, the operations that should be possible on that sort and then stores the data.
Datatypes are classified into
- String datatype
- Numeric datatype
- Date/time
- Large object datatype.
DATATYPES
In MySQL, a string can control anything from plain text to double data such as files and images. The string can be related and examined based on pattern identity by using the like operators.
The following are the String Data types in MySQL:
| Datatype Syntax | Maximun Size |
|---|---|
| CHAR(size) | Max Size 255 Characters |
| VARCHAR(Size) | Max Size 255 Characters |
| TEXT(Size) | Max size of 65,535 characters |
| BINARY(size) | Maximum size of 255 characters |
| VARBINARY(size) | Maximum size of 255 characters |
Examples
The below example describe the sample example for string data types.
[sql]
mysql> Create table employee(emp_id int,ename varchar(255),job varchar(255),salary int);
Query OK, 0 rows affected (0.64 sec)
[/sql]
The above example describes the string data types attributes.
DATATYPES
Numeric datatypes in MySQL combines number data types, including fixed-point, whole numbers and drifting point. In expansion, MySQL additionally bolsters BIT data sort for putting away field values. Numeric sorts are signed or unsigned with the exception the BIT sort.
The following are the Numeric Datatypes in MySQL
| Datatype Syntax | Maximum Size |
|---|---|
| BIT | Very small integer value. Signed values range from -128 to 127. Unsigned values range from 0 to 255. |
| FLOAT(p) | Floating point number |
| BOOLEAN | Synonym for TINYINT |
| INT(m) | Standard integer value. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295. |
| INTEGER(m) | Standard integer value. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295. |
| NUMERIC(m,d) | Unpacked fixed-point number. m defaults to 10, if not specified. d defaults to 0, if not specified. |
| DOUBLE(m,d) | Double precision floating point number. |
| DOUBLE PRECISION(m,d) | Double precision floating point number |
Examples
The below example describe the sample example for numeric data types.
[sql]
mysql> Create table test(id decimal,name varchar(100),col1 decimal(5,2),col2 integer,col3 bigint);
[/sql]
The below example describes the numeric data types with numeric function.
DATATYPES
MySQL produces the sequence for date and time. Beside this, MySQL holds timestamps datatype for capturing the adjustments made in the row of table. To store the year, past date & month, then utilize
YEAR data sort.
The following are the Date/Time Datatypes in MySQL.
| Datatype Syntax | Maximumn Size | Explanation |
|---|---|---|
| DATE | Values range from '1000-01-01' to '9999-12-31' | Displayed as 'YYYY-MM-DD' |
| TIME | Values range from '-838:59:59' to '838:59:59' | Displayed as 'HH:MM:SS' |
| YEAR[(2|4)] | Year value as 2 digits or 4 digits | Default is 4 digits |
| DATETIME | Values range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. | Displayed as 'YYYY-MM-DD HH:MM:SS' |
Examples
The below example describe the sample example for date/time data types.
[sql]
mysql> select * from employee;
+--------+-------+-------+-------------------+
| emp_id | ename |salary | Orderdate |
+--------+-------+-------+-------------------+
| 1001 | maddi | 12000 | 2008-11-11 |
| 1002 | jack | 13100 | 2008-11-11 |
+--------+-------+-------+-------------------+
2 rows in set (0.00 sec)
mysql> select * from employee where orderdate = '2008-11-11'
[/sql]
Here in the above example it will retrieve the date and time data types.
DATATYPES
The following are the LOB Datatypes in MySQL.
Substantial ordinarily signifies "around 4kb or more", albeit a few databases can cheerfully handle up to 32kb preceding information turns out to be "extensive". Huge articles can be either literary or paired in nature. PDO permits to work with the expansive information sort by utilizing the pram_lob code.
| Datatype Syntax | Maximumn Size |
|---|---|
| TINYBLOB | Maximum size of 255 bytes |
| BLOB(size) | Maximum size of 65,535 bytes |
| LONGTEXT | Maximum size of 4GB or 4,294,967,295 characters |
Key Points
A Data type is a programming language, which contain set of values and data.
- String Datatype uses char and varchar.
- Number Datatype uses integer and floating values.
- Date and Time data type uses 'YYYY-MM-DD' and 'HH:MM:SS'.