Data Types

MySQL's data types cover the same broad categories as any SQL database — numbers, text, dates — but with MySQL-specific sizes and behavior worth knowing before you design a real table.

Integer types and their ranges

MySQL gives you four signed integer sizes, each trading storage space for range — picking the smallest one that fits your data avoids wasting disk space across millions of rows:

-- TINYINT:   -128 to 127                                  (1 byte)
-- SMALLINT:  -32,768 to 32,767                            (2 bytes)
-- INT:       about -2.1 billion to 2.1 billion            (4 bytes)
-- BIGINT:    about -9.2 quintillion to 9.2 quintillion    (8 bytes)

VARCHAR vs TEXT

VARCHAR(n) stores a variable-length string up to n characters, inline in the row itself. TEXT has no length you specify (up to 65,535 bytes) and MySQL may store it separately from the row — VARCHAR is the right default for names, emails, and short fields; TEXT is for genuinely long content like an article body:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT
);
DESCRIBE posts;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| title | varchar(200) | YES  |     | NULL    |                |
| body  | text         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

DATETIME vs TIMESTAMP

Both store a date and time down to the second, but TIMESTAMP is stored internally in UTC and converted to your session's time zone whenever you read it back, while DATETIME stores exactly what you gave it with no conversion at all. TIMESTAMP also has a much narrower range: 1970-01-01 to 2038-01-19, versus DATETIME's 1000-01-01 to 9999-12-31.

ENUM for a fixed set of values

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'shipped', 'delivered', 'cancelled')
);
INSERT INTO orders (status) VALUES ('pending');
SELECT * FROM orders;

+----+---------+
| id | status  |
+----+---------+
|  1 | pending |
+----+---------+
1 row in set (0.00 sec)

Internally, MySQL stores each ENUM value as a small integer index into the list you defined, which is compact and fast — but adding a new allowed value later means altering the table's definition, so ENUM fits best for values that genuinely won't change often.