SQL stores data into columns, and each column must have some type. These data types will be defined at the table creation time. Data type also indicates the range of data that can be stored in it. Each column can have only 1 data type.
Correct Data types make retrieval of data fast and increase performance
The syntax for defining data type at the time of creating a table is
CREATE TABLE table_name (column1_name data type(length));MySQL supports SQL data types of several categories:
- numeric types
- date and time types
- string (character and byte) types
- spatial types
- JSON data type
Numeric Data Types
| Type | Storage (Bytes) | Minimum Value Signed | Minimum Value Unsigned | Maximum Value Signed | Maximum Value Unsigned |
|---|---|---|---|---|---|
| TINYINT | 1 | -128 | 0 | 127 | 255 |
| SMALLINT | 2 | -32768 | 0 | 32767 | 65535 |
| MEDIUMINT | 3 | -8388608 | 0 | 8388607 | 16777215 |
| INT | 4 | -2147483648 | 0 | 2147483647 | 4294967295 |
| BIGINT | 8 | -9223372036854775808 | 0 | 9223372036854775807 | 18446744073709551615 |
by default, data types are considered signed, meaning both positive and negative values can be inserted.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (tinyint_col TINYINT, smallint_col SMALLINT, mediumint_col MEDIUMINT, int_col INT, bigint_col BIGINT);
INSERT INTO apple (tinyint_col, smallint_col, mediumint_col, int_col, bigint_col) VALUES (127,32767, 8388607, 2147483647, 9223372036854775807);when unsigned is mentioned while declaring the table, it doesn't allow negative values to be inserted and hence double the overall limit of the column, making the minimum value 0.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (tinyint_col TINYINT UNSIGNED, smallint_col SMALLINT UNSIGNED, mediumint_col MEDIUMINT UNSIGNED, int_col INT UNSIGNED, bigint_col BIGINT UNSIGNED);
INSERT INTO apple (tinyint_col, smallint_col, mediumint_col, int_col, bigint_col) VALUES (255, 65535, 16777215, 4294967295, 18446744073709551615);Approximate Numeric Data Types
Approx numeric data type can't be unsigned.
| Type | From | TO |
|---|---|---|
| Bit | 1 | 255 |
| Decimal | -10^38 +1 | 10^38 -1 |
| Numeric | -10^38 +1 | 10^38 -1 |
| Float | -1.79E + 308 | 1.79E + 308 |
| Real | -3.40E + 38 | 3.40E + 38 |
FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here values can be stored up to M digits in total where D represents the decimal point. For example, a column defined as FLOAT(8,5) will look like -999.99999.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (decimal_col DECIMAL, numeric_col NUMERIC, float_col FLOAT, double_col DOUBLE, real_col REAL);
INSERT INTO apple (decimal_col, numeric_col, float_col, double_col, real_col) VALUES (1, 1, 1, 1, 1);Date and Time Data Type
The date and time data type is used to store date, time, DateTime, timestamp, and year
| Data Type | Format | Value |
|---|---|---|
| DATE | YYYY-MM-DD | 2000-01-01 |
| TIME | HH:MI:SS | 01:01:01 |
| DATETIME | YYYY-MM-DD HH:MI:SS | 2000-01-01 01:01:01 |
| TIMESTAMP | YYYY-MM-DD HH:MI:SS | 2000-01-01 01:01:01 |
| YEAR | YYYY | 2000 |
SQL query to create Date and Time Data Type tables are below
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (date_col DATE, time_col TIME, datetime_col DATETIME, timestamp_col TIMESTAMP, year_col YEAR);
INSERT INTO apple (date_col, time_col, datetime_col, timestamp_col, year_col) VALUES (now(), now(), now(), now(), now());Character and Strings Data Types
Long text is stored as String Data Type, it can store description, feedback, URLs and binary data. String Data Type contains the below types.
- text
- blob
- char and varchar
- binary and varbinary
- enum
- set
Text Data Type
Text data types are used to store TEXT data. its size range from 1 kb to 4GB. Mysql Supports the below text data types.
| Types | Description | Range in characters |
|---|---|---|
| TINYTEXT | it's used to store short-length text Strings | 255 |
| TEXT | it's used to store medium-length text Strings such as article | 65535 |
| MEDIUMTEXT | It's used to store large-length text Strings such as webpage | 16777215 |
| LONGTEXT | It's used to store very large length text Strings such as books | 4294967295 |
SQL query to create Date and Time Data Type tables are below
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (tiny_text_col TINYTEXT, text_col TEXT, medium_text_col MEDIUMTEXT, long_text_col LONGTEXT);
INSERT INTO apple (tiny_text_col, text_col, medium_text_col, long_text_col) VALUES ('Macintosh','Fuji','Gala','Jonagold');Blob Data Type
BLOB stands for Binary Large Object. The blob data type is used to store BInary Strings such as audio, video or image in binary format.
| Types | Description | Range in characters |
|---|---|---|
| TINYBLOB | it's used to store short-length BLOB | 255 |
| BLOB | it's used to store medium-length BLOB | 65535 |
| MEDIUMTEXT | It's used to store large-length BLOB | 16777215 |
| LONGTEXT | It's used to store very large BLOB | 4294967295 |
SQL query to create Blob Type tables are below
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (tiny_blob_col TINYBLOB, blob_col BLOB, medium_blob_col MEDIUMBLOB, long_blob_col LONGBLOB);
INSERT INTO apple (tiny_blob_col, blob_col, medium_blob_col, long_blob_col) VALUES ('Macintosh','Fuji','Gala','Jonagold');
INSERT INTO apple (tiny_blob_col, blob_col, medium_blob_col, long_blob_col) VALUES (x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082', x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082', x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082', x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082');
-- To save file in binary format
-- Copy file to /tmp
-- Change ownership to mysql user chown mysql:mysql /tmp/file_name
-- Log into mysql as mysql root user to make sure have FILE privilege
INSERT INTO apple (tiny_blob_col, blob_col, medium_blob_col, long_blob_col) VALUES (LOAD_FILE('/tmp/Macintosh.png'), LOAD_FILE('/tmp/Fuji.png'), LOAD_FILE('/tmp/Gala.png'), LOAD_FILE('/tmp/Jonagold.png'));Char and Varchar
Non-binary String up to length 255 are stored in CHAR and strings up to 65535 lengths are stored in Varchar. Char and Varchar both require size parameters at the time of table creation.
Char appends additional spaces in value, where as Varchar dosent
The main difference between CHAR and VARCHAR is the way they store the data. for example, CHAR(10) needs to fill the row with 10 characters, so if wesome is stored having 6 characters only, CHAR will add 4 spaces on the right to make it 10 characters.
whereas VARCHAR will not add any additional spaces and display value as it is.
| Types | Description | Range in characters | |
|---|---|---|---|
| CHAR | Char uses fixed memory, if the inserted value is less then it will add space to the right to make its size equal to column length. it wastes memory and is only preferred if data length is fixed. | 255. | |
| VARCHAR | Varchar uses dynamic memory allocation and will only use memory as per the data length. | 0 to 65,535 in 5.0.3 and later versions. |
SQL query to create Blob Type tables are below
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
create table apple (char_col CHAR(10), varchar_col VARCHAR(10));
INSERT INTO apple (char_col, varchar_col) VALUES ('Macintosh','Fuji');Binary Strings Data Types
Char and Varchar are almost similar to Binary and Varbinary Strings data types. Binary and Varbinary Strings stores Binary Data and its length is measured in bytes.
| Types | Description | Range in bytes |
|---|---|---|
| BINARY | Contains binary strings. | 0 to 255 |
| VARBINARY | also known as Variable-length Binary, it Contains binary strings. | A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. |
Json Data Type
MySQL 5.7.6 supports JSON data type and provides automatic validation of JSON documents. invalid JSON files will not be stored and will produce errors.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE table_name ( id INT NOT NULL AUTO_INCREMENT, json_col JSON, PRIMARY KEY(id));
CREATE TABLE apple (apple_id BIGINT, apple_json JSON, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_json) VALUE (1, '[{"appleId":1,"appleName":"Macintosh","price":1.1,"available":true,"taste":["sweet","sour"],"vendors":[{"vendorId":100,"vendorName":"Vendor_A"},{"vendorId":200,"vendorName":"Vendor_B"}]}]');
INSERT INTO apple (apple_id, apple_json) VALUE (2, '[{"appleId":2,"appleName":"Fuji","price":2.2,"available":false,"taste":["bitter","salty"],"vendors":[{"vendorId":200,"vendorName":"Vendor_B"},{"vendorId":300,"vendorName":"Vendor_C"}]}]');MYSQL has the JSON_OBJECT function which allows existing columns to be formatted as a JSON Object
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), apple_price DECIMAL(10,2), apple_type VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (1, "Macintosh", 1.1);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (2, "Fuji", 2.2);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (3, "Gala", 3.3);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (4, "Jonagold", 4.4);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (5, "GrannySmith", 5.5);
SELECT JSON_OBJECT('apple_id',apple_id , 'apple_name',apple_name , 'apple_price', apple_price) as APPLE_JSON_OBJECT from apple;MYSQL has a JSON_ARRAY function which allows existing columns to be formatted as JSON Array.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), apple_price DECIMAL(10,2), apple_type VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (1, "Macintosh", 1.1);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (2, "Fuji", 2.2);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (3, "Gala", 3.3);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (4, "Jonagold", 4.4);
INSERT INTO apple (apple_id, apple_name, apple_price) VALUES (5, "GrannySmith", 5.5);
SELECT JSON_ARRAY(apple_id, apple_name, apple_price) as APPLE_JSON_ARRAY from apple;
