SQL Data types

Properly structuring data is crucial for building efficient, accurate database-driven systems. SQL provides various specialized data types beyond just plain text and numbers that enable robust data handling. Understanding the options available helps architect optimal database schemas.

String Data Types

Text-based information is stored in SQL using CHAR and VARCHAR types.

CHAR(n) stores strings at an exact length of n characters. Any unused character slots are padded with spaces:

CREATE TABLE Users (
    Username CHAR(50)
);

INSERT INTO Users VALUES ('user123'); -- Stores as 'user123' plus 32 spaces

VARCHAR(n) stores variable length text up to n characters. No blank padding, so it uses only necessary space:

CREATE TABLE Posts (
    Title VARCHAR(100)
);

INSERT INTO Posts VALUES ('Hello World'); -- Stores just the 11 characters

VARCHAR is generally preferable over CHAR for flexibility. Unicode equivalents like NVARCHAR(n) and NCHAR(n) are also available for international data.

Numeric Data Types

Whole numbers are stored using the INT type. DECIMAL stores exact fractional values, while FLOAT/DOUBLE store scientific precision numbers:

CREATE TABLE Products (
    Price DECIMAL(8,2), -- 999999.99
    Stock INT, -- Up to 2 billion
    Weight FLOAT -- Fractional scientific notation
);

INSERT INTO Products VALUES (5.99, 105, 1.05986);

We specify precision and scale for the exact needed range. This optimizes storage and calculations.

Date and Time Data Types

SQL has dedicated types for dates, times, timestamps and intervals:

CREATE TABLE Events (
   `Date` DATE, -- 2023-12-25
   StartTime TIME, -- 12:30:00
   Finish DATETIME -- 2023-12-25 12:30:00
);

INSERT INTO Events
VALUES
   ('2023-12-25', '12:30', '2023-12-25 12:30');

Standard YYYY-MM-DD formatting avoids ambiguity with different regional date orders.

Boolean and Binary Types

The BOOLEAN type stores simple true/false values. The BLOB and BINARY types contain opaque binary data like images, files etc:

CREATE TABLE Users (
    AccountVerified BOOLEAN,
    ProfilePicture BLOB
);

INSERT INTO Users VALUES (true, LOAD_FILE('profile.png'));

Bitwise flags can also be used but BOOLEAN reads clearer for logic values.

Auto Increment Columns

Auto increment columns generate sequential numbers automatically. This provides unique IDs often used for primary keys:

CREATE TABLE Users (
    ID INT AUTO_INCREMENT,
    Name VARCHAR(100),
    PRIMARY KEY (ID)
);

INSERT INTO Users (Name) VALUES ('John'); -- ID populates as 1
INSERT INTO Users (Name) VALUES ('Mary'); -- ID populates as 2

Special and Custom Types

Many databases support specialized types like XML, JSON, ARRAY and custom types for particular data structures. These handle validation and processing tailored to the data semantics.

Choosing the right data types might seem tedious initially. But the long term benefits of optimized storage, improved data quality, and simplified logic justify the planning required.

common data types available in MySQL:

Data type	Description	Format/Length
INT	Integer numerical value	INT, INT(11)
DECIMAL	Fixed precision decimal number	DECIMAL(M,D) M=digits, D=decimals
FLOAT	Floating point approximate number	FLOAT, FLOAT(p)
DATE	Date value	DATE, DATE(0) YYYY-MM-DD
TIME	Time value	TIME(0) hh:mm:ss
DATETIME	Date and time value	DATETIME YYYY-MM-DD hh:mm:ss
TIMESTAMP	Auto updated datetime	TIMESTAMP DEFAULT CURRENT_TIMESTAMP
CHAR	Fixed length string	CHAR(size) 1-255 chars
VARCHAR	Variable length string	VARCHAR(size) 1-65535 chars
TEXT	Long text string	TEXT, LONGTEXT, etc
BLOB	Binary large object	BLOB, TINYBLOB, etc
JSON	JSON document	JSON
BOOLEAN	Boolean value	BOOLEAN
AUTO INCREMENT	Auto incrementing integer	SERIAL, BIGSERIAL, etc