MySQL — Data Types

Numeric Types

-- Integer types
TINYINT     -- 1 byte (-128 to 127)
SMALLINT    -- 2 bytes (-32768 to 32767)
MEDIUMINT   -- 3 bytes
INT         -- 4 bytes
BIGINT      -- 8 bytes

-- Floating point
FLOAT       -- 4 bytes
DOUBLE      -- 8 bytes

-- Fixed point
DECIMAL(10,2)  -- 10 digits, 2 decimal places

String Types

-- Character types
CHAR(10)        -- Fixed length (10 chars)
VARCHAR(100)    -- Variable length (up to 100 chars)
TEXT            -- Up to 65535 chars
MEDIUMTEXT      -- Up to 16777215 chars
LONGTEXT        -- Up to 4294967295 chars

-- Binary types
BINARY(16)      -- Fixed length binary
VARBINARY(100)  -- Variable length binary
BLOB            -- Binary large object

Date and Time Types

DATE            -- YYYY-MM-DD
TIME            -- HH:MM:SS
DATETIME        -- YYYY-MM-DD HH:MM:SS
TIMESTAMP       -- Auto-updating timestamp
YEAR            -- YYYY

Using Data Types

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age TINYINT,
    salary DECIMAL(10,2),
    birth_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data Type Examples

-- Numeric
CREATE TABLE products (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock INT DEFAULT 0
);

-- String
CREATE TABLE articles (
    id INT AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    slug VARCHAR(100) UNIQUE
);

-- Date
CREATE TABLE events (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Mini Practice

Write MySQL code that:

1. Creates a table with numeric types
2. Uses string types
3. Creates a table with date types
4. Chooses appropriate data types

Up Next

Next: Learn about MySQL Databases.