Lesson 4
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.
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.
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.
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.
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 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
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.
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 |