Learn › MySQL › Priya explains the data types developers get wrong

MySQL Ch 14 / 18 Advanced

📐

Priya explains the data types developers get wrong

DECIMAL vs FLOAT, ENUM, TIMESTAMP vs DATETIME, JSON, timezone traps

⏱ 13 min 6 commands 5 takeaways

📐

In this chapter

Priya

DBA, running a team training session

The story

A developer at Priya's company asked: what is the difference between all the string types? When should I use ENUM? What does CHARSET mean? My date queries are returning wrong results -- is it a timezone issue?

Priya ran a training session. Here is everything about MySQL data types that developers get wrong.

CHOOSING THE RIGHT DATA TYPE

Wrong data types waste space and cause bugs. Right data types save space and prevent errors.

INTEGERS

TINYINT    -- 1 byte, -128 to 127 (or 0-255 unsigned)
SMALLINT   -- 2 bytes, -32768 to 32767
MEDIUMINT  -- 3 bytes
INT        -- 4 bytes, -2.1 billion to 2.1 billion
BIGINT     -- 8 bytes, very large numbers

-- For user IDs that might grow large: BIGINT
-- For boolean flags: TINYINT(1) or BIT(1)
-- For age (0-150): TINYINT UNSIGNED

-- UNSIGNED doubles the positive range:
INT UNSIGNED    -- 0 to 4.2 billion (good for auto-increment IDs at scale)

DECIMALS AND MONEY

-- NEVER store money as FLOAT or DOUBLE:
FLOAT  -- approximate, 1.99 might be stored as 1.98999999...
DOUBLE -- same problem, just more precision

-- ALWAYS store money as DECIMAL:
DECIMAL(10,2)   -- 10 total digits, 2 after decimal: max 99999999.99
DECIMAL(15,2)   -- for larger amounts: max 9999999999999.99

SELECT 0.1 + 0.2;   -- returns 0.30000000000000004 with FLOAT
-- With DECIMAL: returns exactly 0.30

STRINGS

CHAR(10)        -- fixed 10 characters, always uses 10 bytes
                -- good for fixed-length values like country codes, phone area codes
VARCHAR(255)    -- variable length, uses only as many bytes as the content
                -- good for names, emails, most text

TEXT            -- up to 65,535 bytes
MEDIUMTEXT      -- up to 16MB
LONGTEXT        -- up to 4GB

-- Use VARCHAR for most strings up to ~1000 chars
-- Use TEXT for longer content (descriptions, articles)
-- Do not index TEXT columns (use FULLTEXT index instead if needed)

ENUM -- PREDEFINED LIST OF VALUES

status ENUM('pending', 'paid', 'cancelled', 'refunded')

-- MySQL stores ENUM as a 1-2 byte integer internally (very efficient)
-- Automatically validates: cannot insert 'shipped' if it is not in the list
-- Problem: adding new values requires ALTER TABLE

-- Good use: status fields with stable options
-- Bad use: anything that might grow (use a separate lookup table instead)

DATES AND TIMES

DATE        -- date only: '2026-03-16'
TIME        -- time only: '14:23:45'
DATETIME    -- date and time, no timezone: '2026-03-16 14:23:45'
TIMESTAMP   -- date and time, stored in UTC, displayed in server timezone

-- DATETIME vs TIMESTAMP:
-- DATETIME stores exactly what you put in, no conversion
-- TIMESTAMP converts to UTC on storage, converts back to server timezone on retrieval
-- For created_at / updated_at: use TIMESTAMP (handles timezone correctly)
-- For event dates without timezone meaning: use DATETIME

TIMEZONE GOTCHAS

-- Check server timezone:
SELECT @@global.time_zone, @@session.time_zone;

-- If your app is in IST (+5:30) but server is in UTC, TIMESTAMP queries may seem wrong:
-- You inserted '2026-03-16 14:00:00' (IST)
-- MySQL stored '2026-03-16 08:30:00' (UTC)
-- You query WHERE created_at = '2026-03-16 14:00:00' -- finds nothing!

-- Fix: set the session timezone when connecting from your app:
SET time_zone = '+05:30';

-- Or set globally in my.cnf:
default-time-zone = '+05:30'

JSON DATA TYPE (MYSQL 5.7+)

ALTER TABLE users ADD COLUMN preferences JSON;

-- Insert JSON:
UPDATE users SET preferences = '{"theme":"dark","language":"en","notifications":true}' WHERE id = 1;

-- Query inside JSON:
SELECT JSON_EXTRACT(preferences, '$.theme') FROM users WHERE id = 1;
-- Shorthand:
SELECT preferences->>'$.theme' FROM users WHERE id = 1;

-- Update one key without replacing the whole JSON:
UPDATE users SET preferences = JSON_SET(preferences, '$.theme', 'light') WHERE id = 1;

-- Index a JSON field:
ALTER TABLE users ADD COLUMN theme VARCHAR(20) GENERATED ALWAYS AS (preferences->>'$.theme') VIRTUAL;
CREATE INDEX idx_theme ON users(theme);

Priya's rule: use the smallest type that fits your data. TINYINT instead of INT for flags saves 75% of space. DECIMAL instead of FLOAT for money prevents subtle billing bugs.

Key takeaways

Never store money in FLOAT or DOUBLE — they are approximate and will cause subtle rounding errors in billing

DECIMAL(10,2) stores money exactly — 10 total digits, 2 after the decimal point

TIMESTAMP stores in UTC and converts to server timezone on retrieval — DATETIME stores exactly what you put in with no conversion

ENUM is stored as a 1-2 byte integer internally — efficient for status fields, but adding values requires ALTER TABLE

JSON columns let you query individual keys with ->> operator and create virtual indexed columns from JSON fields

Commands from this chapter

$ SELECT 0.1 + 0.2;

See floating-point imprecision — use DECIMAL for money

$ ALTER TABLE orders MODIFY COLUMN amount DECIMAL(12,2);

Fix a money column from FLOAT to DECIMAL

$ SELECT @@global.time_zone, @@session.time_zone;

Check server and session timezone

$ SET time_zone = '+05:30';

Set IST timezone for the current session

$ SELECT preferences->>'$.theme' FROM users WHERE id = 1;

Extract a field from a JSON column

$ UPDATE users SET preferences = JSON_SET(preferences, '$.theme', 'light') WHERE id = 1;

Update one JSON field without replacing the whole object