You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Choosing the right data type for each column is one of the most impactful decisions you make when designing a MySQL schema. The right type saves storage, enforces validity, and improves query performance.
MySQL provides a range of integer types that differ in their storage size and value range:
| Type | Storage | Signed range |
|---|---|---|
| TINYINT | 1 byte | -128 to 127 |
| SMALLINT | 2 bytes | -32,768 to 32,767 |
| INT | 4 bytes | -2,147,483,648 to 2,147,483,647 |
| BIGINT | 8 bytes | -9.2 × 10^18 to 9.2 × 10^18 |
For fractional numbers use DECIMAL(precision, scale) for exact arithmetic (ideal for money) or FLOAT / DOUBLE for approximate floating-point values.
price DECIMAL(10, 2), -- e.g. 99999999.99
latitude DOUBLE,
score TINYINT UNSIGNED
VARCHAR(n) stores variable-length strings up to n characters and is the most common choice for names, emails, and short text. CHAR(n) stores fixed-length strings and is slightly faster for values that are always the same length, such as two-letter country codes.
For longer text use TEXT (up to 65,535 bytes), MEDIUMTEXT (16 MB), or LONGTEXT (4 GB). Binary equivalents BLOB, MEDIUMBLOB, and LONGBLOB store raw binary data.
Always use the utf8mb4 character set for string columns to correctly handle all Unicode characters including emoji:
name VARCHAR(100) NOT NULL,
bio TEXT,
code CHAR(2) NOT NULL
| Type | Format | Use |
|---|---|---|
| DATE | YYYY-MM-DD | Calendar dates |
| TIME | HH:MM:SS | Time of day or duration |
| DATETIME | YYYY-MM-DD HH:MM:SS | Combined date and time, no timezone |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | Stored as UTC, displayed in session timezone |
| YEAR | YYYY | Year values |
TIMESTAMP columns can be given DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to track creation and modification times automatically.
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
MySQL does not have a native boolean type. The convention is TINYINT(1) where 0 represents false and 1 represents true. Many ORMs map this automatically to a boolean in application code.
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.