Data types

Why types matter

Every column has a data type. The type decides which values are allowed, how much disk the column uses, how it sorts and compares, and how fast queries run. A bad choice causes subtle bugs, such as prices that drift by a cent or dates that sort alphabetically. A good choice also documents your intent.

Integers

Add UNSIGNED when a value can never be negative, such as a quantity or an age. It doubles the positive range. Use INT for most ids, and BIGINT for tables that may grow past a few billion rows. BOOLEAN is an alias for TINYINT(1): 0 is false, 1 is true.

Let us see what strict mode does with an out-of-range value:

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS ages;
CREATE TABLE ages (person VARCHAR(20), age TINYINT UNSIGNED, is_member BOOLEAN DEFAULT FALSE);
INSERT INTO ages (person, age) VALUES ('Mia', 34);
SELECT * FROM ages;

+--------+------+-----------+
| person | age  | is_member |
+--------+------+-----------+
| Mia    |   34 |         0 |
+--------+------+-----------+

INSERT INTO ages (person, age) VALUES ('Old Tree', 300);

ERROR 1264 (22003): Out of range value for column 'age' at row 1

The default SQL mode of MySQL 8 is strict, so the bad value is rejected with an error. In a non-strict mode the value would be silently clamped to 255, which is the kind of quiet corruption you want to avoid. Keep strict mode on.

Exact and approximate numbers

• DECIMAL(p, s) (also NUMERIC) stores exact decimals. DECIMAL(8,2) means 8 digits in total, 2 after the point, so the largest is 999999.99.
• FLOAT (4 bytes) and DOUBLE (8 bytes) store approximations. They are right for measurements and scientific values, wrong for money.

DROP TABLE IF EXISTS prices;
CREATE TABLE prices (item VARCHAR(20), price DECIMAL(6,2));
INSERT INTO prices VALUES ('tea', 3.456);
SELECT * FROM prices;

+------+-------+
| item | price |
+------+-------+
| tea  |  3.46 |
+------+-------+

The value was rounded to two decimals. Inserting a number that needs more digits before the point than p - s allows (for example 99999.99 into DECIMAL(6,2)) gives an out-of-range error.

Text

Choose a realistic VARCHAR length. A limit of 100 for a name is plenty; 255 is a common upper bound for emails and URLs. Over-long values are rejected in strict mode:

DROP TABLE IF EXISTS codes;
CREATE TABLE codes (country_code CHAR(2), label VARCHAR(5), size ENUM('S','M','L'));
INSERT INTO codes VALUES ('IN', 'India', 'M');
SELECT * FROM codes;

+--------------+-------+------+
| country_code | label | size |
+--------------+-------+------+
| IN           | India | M    |
+--------------+-------+------+

INSERT INTO codes VALUES ('IN', 'Hindustan', 'M');

ERROR 1406 (22001): Data too long for column 'label' at row 1

INSERT INTO codes VALUES ('IN', 'India', 'XL');

ERROR 1265 (01000): Data truncated for column 'size' at row 1

The first error is "Data too long for column"; the second is "Data truncated" because 'XL' is not one of the allowed ENUM values. ENUM is convenient for a short, stable list such as an order status. If the list may change often, a separate lookup table is more flexible, as you will see in the design lesson.

Dates and times

Never store dates as text. Real date types validate, sort and calculate correctly:

SELECT CAST('2024-02-30' AS DATE) AS impossible_date;

+-----------------+
| impossible_date |
+-----------------+
| NULL            |
+-----------------+

SELECT DATE('2024-02-29') + INTERVAL 1 YEAR AS next_year;

+------------+
| next_year  |
+------------+
| 2025-02-28 |
+------------+

The first query returns NULL, because 30 February does not exist. The second shows how MySQL steps from a leap day to the end of February.

JSON and binary

JSON stores a document and validates that it is well-formed; a later lesson covers it. BLOB types store raw bytes such as images, but most applications keep files on disk or in object storage and store only the path in the database.

Choosing quickly

Common mistakes

• Using FLOAT for money.
• Using VARCHAR(255) for everything without thinking about meaning or size.
• Storing dates or numbers as text.
• Using INT(11) to mean "11 digits". The number in brackets was only a display hint and is deprecated; INT always holds the same range.
• Turning off strict mode to make errors go away.

Exercise

Design a table products_demo for a product with an id, a name (up to 80 characters), a price in rupees with paise, an in_stock flag, a weight_kg measurement (approximate is fine) and a created_at moment. Insert one row, then try inserting a price of 123456789.00 and read the error.

DROP TABLE IF EXISTS products_demo;
CREATE TABLE products_demo (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(80) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  in_stock   BOOLEAN NOT NULL DEFAULT TRUE,
  weight_kg  FLOAT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO products_demo (name, price, weight_kg) VALUES ('Notebook', 49.50, 0.35);
SELECT id, name, price, in_stock FROM products_demo;

+----+----------+-------+----------+
| id | name     | price | in_stock |
+----+----------+-------+----------+
|  1 | Notebook | 49.50 |        1 |
+----+----------+-------+----------+

INSERT INTO products_demo (name, price) VALUES ('Too expensive', 123456789.00);

ERROR 1264 (22003): Out of range value for column 'price' at row 1