This tutorial will show you different MySQL Data Types in mysql database. Understanding of mysql data types help us to optimize database. The MySQL uses 3 categories of data type for optimization of database. Developers are expected to be realistic in using data types and don’t overuse anything inside the MySQL database or fields within it. Follow given Various Data Types in MySQL and corresponding uses.
The MySQL uses 3 categories of data type for optimization of database. Developers are expected to be realistic in using data types and don’t overuse anything inside the MySQL database or fields within it. Follow given Various Data Types in MySQL and corresponding uses.
Numeric Data Types in MySQL
MySQL practices ANSI SQL standard for numeric data types. If you are new to MySQL or want to learn basics these definitions look friendly to you. Following given common numeric data types with descriptions:
INT – This integer is normal-sized. When singed the permissible range is from -2147483648 to 2147483647. When unsigned the permissible range is from 0 to 4294967295. You can denote a width of up to 11 digits.
TINYINT – This is very small integer. When signed the permissible range is from -128 to 127. When unsigned the permissible range is from 0 to 255. You can denote a width of up to 4 digits.
SMALLINT – This is a small integer which can be signed or unsigned. The permissible range when signed is from -32768 to 32767. When unsigned, the permissible range is 0 to 65535. You can denote a width of up to 5 digits.
MEDIUMINT – This is a medium sized integer for which you can specify a width of up to 9 digits. In signed condition the permissible range is from -8388608 to 8388607. When unsigned the permissible range is 0 to 16777215.
BIGINT – This is a large integer which can be signed or unsigned. The permissible range when signed is 9223372036854775808 to 9223372036854775807. When unsigned the permissible range is from 0 to 18446744073709551615. You can denote a width of maximum 20 digits.
FLOAT(M,D) – This is a floating point number which can be unsigned. Developer can denote the display length M and the number of decimals D. By default this is 10, 2; here 2 is the number of decimals and 10 is total number of digits together with decimals. For a FLOAT decimal precision can go to 24 places.
DOUBLE(M,D) – Also called DOUBLE REAL, this represent to double precision of floating point numbers. M is display length and D is decimals. The default value for this is 16, 4. For this decimal precision can go to 53 places.
DECIMAL(M,D) – This is floating point number which can’t be unsigned. Each decimal is equivalent to 1 byte in unpacked decimals. In this, defining M and D is essential.
Date and Time Types Data Types in MySQL
Following given the Date and Time Types Data Types in MySQL:
DATE – In MySQL Data Type date is represented in format YYYY-MM-DD and ranges from 1000-01-01 and 9999-12-31. For example January 31, 1981 in a MySQL database could be stored as 1981-01-31.
DATETIME – The format of date and time is YYYY-MM-DD HH:MM:SS. It ranges from 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 7 PM on January 31, 1981 could be stored as 1981-01-31 19:00:00.
TIMESTAMP – This appears like DATE TIME format but without syntax between numbers. For example January 31, 1981 & 7 PM could be restored as 19810131070000 i.e. YYYYMMDDHHMMSS.
TIME – This feature stores the time in HH:MM:SS format. For example 7 Hours 30 Minutes and 15 Seconds of morning stores as 07:30:15.
YEAR (M) – This feature stores the year in 2 or 4 digit format. For example 1981 in 2 digit format stores as 81 but in 4 digit format stores as 1981. The default length is 4 digits unless specified.
Various String Types in MySQL Data Types
In a MySQL most of the data is stored in string format. Here given different types of String Types
CHAR(M) – This is fixed length string of 1 and 255 characters. Default is 1, specifying length is not essential. For example CHAR(4).
VARCHAR(M) – This is string of variable length between 1 and 255 characters. While creating a VARCHAR field specification of character field is a must. For example VARCHAR(17).
BLOB or TEXT – "Binary Large Objects", this field has maximum character length of 65535. This field is used for storing large amount of binary data for example media files, images and text etc. Field denoted as TEXT also carry large amount of data. The difference between the two is, stored data in BLOBs is not case sensitive however, in TEXT stored data is case sensitive.
TINYBLOB or TINYTEXT – This is a BLOB or TEXT column of 255 characters length With TINYBLOB or TINYTEXT length can’t be specified.
MEDIUMBLOB or MEDIUMTEXT – The maximum character length of BLOB or TEXT is 16777215. The length is fixed and can not be specified other way round.
LONGBLOB or LONGTEXT – The maximum character of BLOB or TEXT column is 4294967295 and fixed. You can not specify otherwise.
ENUM – This is term for list in MySQL. While defining and ENUM, you create a list of objects from which the value must be chosen or it can set for NULL.
So those are the basics of Data Types in MySQL. What you think? Do you know some more basics? Share below through comment box.