SQL Data Types

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

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.

 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

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.

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.

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.

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.

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;