Choosing the Right Data Type

You have seen the types one by one. Here is the decision guide you can keep next to your editor.

The questions to ask

1. What kind of thing is it? A number, text, date, true/false, structured data, raw bytes?
2. Do I do arithmetic on it? If not, it may not be a number. A phone number or postal code is text, even though it looks like a number. It can start with 0, contain + or spaces, and you never add two phone numbers.
3. How large can it get? Think about the biggest realistic value, then add room for growth.
4. Can it be unknown? Decide NULL or NOT NULL.
5. Will I search, sort or join on it? Then keep it small. It will be in an index.

Cheat sheet

Mistakes seen again and again

• Using VARCHAR(255) for everything. It hides the rules and makes indexes bigger than needed.
• Storing dates as text, or numbers as text you compute with.
• Using FLOAT for money.
• Using INT for a table that will exceed 2 billion rows (or BIGINT for a lookup table with ten rows).
• A phone number as INT. It loses leading zeros and cannot store +91.
• One huge TEXT column for structured data that you later need to search.
• A table with 100 columns. It often means that several things are mixed together (Normalization).
• Different types for the same fact. users.id as INT and orders.user_id as BIGINT breaks index use in joins and foreign keys. A foreign key column must match the referenced key’s type exactly (and sign).

1
2
3
4
5
6

CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

1

ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'posts_ibfk_1' are incompatible.

The foreign key was refused because BIGINT (signed) does not match INT UNSIGNED. Make the types identical:

1
2
3
4
5
6

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id)
);
SHOW TABLES;

1
2
3
4
5
6
7

+-----------------+
| Tables_in_learn |
+-----------------+
| posts           |
| users           |
+-----------------+
2 rows in set

Changing your mind later

Changing a column’s type later rebuilds the table, which can take a long time on big tables (Online DDL). The earlier you pick the right type, the cheaper it is. Spend ten minutes on types before you create a table that will hold millions of rows.

1
2
3
4
5
6
7
8
9
10

CREATE TABLE members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    date_of_birth DATE NOT NULL,
    monthly_fee DECIMAL(8,2) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DESCRIBE members;

1
2
3
4
5
6
7
8
9
10
11
12

+---------------+--------------+------+-----+-------------------+-------------------+
| Field         | Type         | Null | Key | Default           | Extra             |
+---------------+--------------+------+-----+-------------------+-------------------+
| id            | int unsigned | NO   | PRI | NULL              | auto_increment    |
| full_name     | varchar(100) | NO   |     | NULL              |                   |
| phone         | varchar(20)  | NO   |     | NULL              |                   |
| date_of_birth | date         | NO   |     | NULL              |                   |
| monthly_fee   | decimal(8,2) | NO   |     | NULL              |                   |
| is_active     | tinyint(1)   | NO   |     | 1                 |                   |
| joined_at     | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------------+--------------+------+-----+-------------------+-------------------+
7 rows in set