What are Data types in MYSQL? How to create a MySQL table with multiple data types. We can store any type of data Like String, Text, Integer, Image or more in MySQL Database.
What is Data type?
Data type generally consists of the details, like it keeps which type of data is going to be used, length of the data. When you use data type in any programming language it makes your program easy, because which type of data you can use is already fixed by a programmer.
For example, if you are about to use 2 characters, and there is space for 10 characters, then this is the loss of memory. To make the program more flexible you can use the short length data types.
What are the MySql Data types
There is a total of four categories of data types in MySql, Numeric, strings, Date/Time and large object data types.
Numeric Data Types in MYSQL
One major benefit of moving to MySql is that it permits all ANSI SQL numeric data types so that if you are new to MySql, and you have knowledge of another programming you can work well.
The major numeric data types in MySql are INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, DECIMAL, DOUBLE, etc.
Every numeric data types in MySql has a different range, you can use it based on your requirements. If you need large data types, you can use BIGINT, if your range is very less then INT, then you can use TINYINT.
INT Data type in MYSQL
Its length is up to 11 digits and there are totally two types of integer in this, signed and unsigned. Every integer has a different range.
Signed Integer ranges from -2147483648 to 2147483647.
Unsigned integer ranges from 0 to 4294967295.
Integer Data types table list
|INT Data Types||TINYINT||BIGINT||INT|
|Range (Signed)||-128 to 127||-9223372036854775808 to 9223372036854775807||-2147483648 to 2147483647|
|Range (Unsigned)||0 to 255||0 to 18446744073709551615||0 to 4294967295|
DECIMAL Data Type in MySql
It is used to define the floating-point number, and each decimal number consists of 1 byte. Decimal data types used to be unsigned in nature. It is denoted by (M, D), where M denotes the display length and D denotes the number of decimals.
Date and Time Data Type in MySql
It is used in MySql to store the data in a format. The format used by MySql is YYYY-MM-DD, and its range is 1000-01-01 and 9999-12-31.
For example, if you give them today’s date, 11th Aug 2016 it will be stored and displayed in the system as 2016-08-11.
Time Data Type in MYSQL
Every system or program uses a format for storing time, and MySql uses HH:MM: SS and store and display the time in the same format.
MySql Date/Time Data Type
When the user wants to enter date and time together then this needs a format to store. MySql uses YYYY-MM-DD HH:MM: SS. It will store the date and time between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.
For example, if you want to enter the current date and time, it will show details like this. 2016-08-11 04:59:00.