Data types in MySQL
MySQL supports below types of data types.
- Numeric Data Type
- Date and Time
Numeric data type
bit – single bit
bool and boolean and TINYINT = 1 byte. So you can store signed numbers in the range from -128 to 127. You can store the unsigned numbers in from 0 to 255. If you store 0, it is treated as false. If you store 1, it is treated as true.
SMALLINT = 2 bytes. So you can store signed numbers in the range from -32768 to 32767. You can store the unsigned numbers in from 0 to 65535
MEDIUMINT = 3 bytes. So you can store signed numbers in the range from -8388608 to 8388607. You can store the unsigned numbers in from 0 to 16777215
INT = 4 bytes. So you can store signed numbers in the range from -2147483648 to 2147483647. You can store the unsigned numbers in from 0 to 4294967295
BIGINT = 8 bytes. So you can store signed numbers in the range from -9223372036854775808 to 9223372036854775807. You can store the unsigned numbers in from 0 to 18446744073709551615.
When declaring the Integer data type, we can specify how many characters should be displayed using below syntax.
INT(2) – Number 2 indicates that we need display the value with 2 characters. Application may or may not use this attribute to control the display
Here is a Simple table to understand the data types.
CREATE TABLE DATATYPES(
DataId INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Age INT(2) zerofill
Insert into DATATYPES (Age) values(4);
If we display the record, Age values will be padded with 0.
FLOAT – 4 bytes
DOUBLE – 8 bytes
Floating point values are approximate.
DECIMAL – Decimal type can be used in scenarios where you need to preserve exact precision. It is generally used to store financial data as we need a lot of accuracy.
For example DECIMAL(6,2) would be used to store values from -9999.99 to 9999.99.
Here 6 is the precision and 2 is the scale.
Precision means the total number of digits in a number. Scale means the number of digits to the right of the decimal point in a number.
Float uses 7 digits for precision. Double use 15 digits for precision. Decimal used 28 digits for precision.
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
String data type
CHAR – CHAR data type is used when you know that column value will be of fixed size. For example – Usually states are displayed in 2 character format. So we can use CHAR(2) as a datatype. Also we can store maximum 255 characters with this data type. CHAR is much faster than VARCHAR.
VARCHAR – Main difference between CHAR and VARCHAR is that size is not fixed in VARCHAR. Let us say you want to store the name of the person. We know that length of the name is not fixed. But we also know that Maximum length could be say 100 characters. Then We can use VARCHAR data type like VARCHAR(100). Note that 100 is the max size. If the actual length of the name is say 20 characters then only 20 bytes are used to store the data + 1 or 2 bytes as a prefix to hold length of the actual value. Also we can store upto 65,535 characters with this data type.
BINARY and VARBINARY – These data types are similar to the char and varchar. Only difference is that data is stored in binary format.
TINYTEXT – 255 bytes
TEXT – 65535 bytes. VARCHAR has a variable max size of 65535 bytes.
MEDIUMTEXT – 16,777,215 bytes
LONGTEXT – 4,294,967,295 bytes
BLOB stands for binary large objects. We use this data type to store images, audio, video etc.
TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB are similar to the TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. Only difference is that they store the binary data.
ENUM – used to store constant values
Date and Time Data types
DATE – This data type stores only date and displays it in the YYYY-MM-DD format
DATETIME – This data type stores both date and time and displays it in the YYYY-MM-DD HH:MM:SS format
TIME – This data type stores only time and displays it in the format – HH:MM:SS
YEAR – This data type stores only year and display it in the format YYYY
TIMESTAMP – This data type is useful when we want to view data in different time zones. For example, let us say we inserted the record with current timestamp in X time zone. Now if someone tries to view the record in different time zone, he will see the timestamp updated for his time zone.
CREATE TABLE TESTTIMESTAMP(
SET time_zone=’+00:00′;INSERT INTO TESTTIMESTAMP VALUES(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
Now change the time zone and view records.
SET time_zone =’+05:00′; select * from TESTTIMESTAMP;
You will notie that timestamp value changes as we change the time zone but date time value does not change even if we change the time zone.
Convert the data type of variables
We can use CAST and CONVERT functions to convert the data type of variables.
SELECT (4.5 + CAST(‘2’ AS decimal));
SELECT CONCAT(‘Converting 4 into string ‘,CAST(4 AS CHAR));
SELECT (5 + CONVERT(‘6’, signed int));
SELECT CONCAT(‘Converting number to string’,CONVERT(1.2,CHAR));
Converting charcter sets
SELECT CONVERT(_latin1’str1′ USING ascii);
SELECT CAST(_latin1’str1′ AS CHAR CHARACTER SET ascii);
Converting string to date
SELECT STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’);