Choosing MySQL Data Types and Constraints for Integrity and Performance

1. Data Types

1.1 Integer Types

-- Signed range
TINYINT      (-128 ~ 127)
SMALLINT     (-32768 ~ 32767)
MEDIUMINT    (-8388608 ~ 8388607)
INT/INTEGER  (-2147483648 ~ 2147483647)
BIGINT       (-2^63 ~ 2^63-1)

-- Unsigned (add UNSIGNED)
TINYINT UNSIGNED      (0 ~ 255)
INT UNSIGNED          (0 ~ 4294967295)
BIGINT UNSIGNED       (0 ~ 2^64-1)

-- Example
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT,
    age TINYINT UNSIGNED,      -- suitable for age
    status TINYINT,            -- -128~127, suitable for status code
    big_count BIGINT UNSIGNED  -- large counters
);

Fixed‑point and Floating‑point Types

-- Floating (approximate values)
FLOAT(M, D)   -- single precision, M total digits, D decimal places
DOUBLE(M, D)  -- double precision

-- Fixed‑point (exact values)
DECIMAL(M, D) -- exact arithmetic, suitable for finance
NUMERIC(M, D) -- synonym for DECIMAL

-- Example
CREATE TABLE products (
    price DECIMAL(10, 2),      -- precise to cents, 10 digits total
    rating FLOAT(3, 1),        -- e.g., 4.5
    scientific DOUBLE           -- scientific calculations
);

-- Always use DECIMAL for monetary values to avoid precision loss

1.2 String Types

Fixed‑length vs Variable‑length

-- Fixed‑length (padded with spaces)
CHAR(n)      -- n=0~255, fast queries, wastes space
    • ID card: CHAR(18)
    • MD5 hash: CHAR(32)
    • Status code: CHAR(2)

-- Variable‑length
VARCHAR(n)   -- n=0~65535, saves space
    • Username: VARCHAR(50)
    • Address: VARCHAR(255)
    • Email: VARCHAR(100)

-- Comparison example
CREATE TABLE test_chars (
    code CHAR(10),          -- always occupies 10 bytes
    desc VARCHAR(10)        -- actual length + 1/2 byte
);

Text Types

TINYTEXT    -- up to 255 bytes
TEXT        -- up to 64KB (article content)
MEDIUMTEXT  -- up to 16MB (book content)
LONGTEXT    -- up to 4GB (large documents)

-- Example
CREATE TABLE articles (
    title VARCHAR(200),
    content TEXT,            -- article body
    summary TINYTEXT,
    book_content MEDIUMTEXT
);

Binary Types

BINARY(n)    -- fixed‑length binary
VARBINARY(n) -- variable‑length binary
BLOB         -- binary large object (images, files)

1.3 Temporal Types

DATE        -- 'YYYY‑MM‑DD' (birthdays, dates)
TIME        -- 'HH:MM:SS[.microseconds]' (time intervals)
DATETIME    -- 'YYYY‑MM‑DD HH:MM:SS[.microseconds]' (local time)
TIMESTAMP   -- UTC epoch, '1970‑01‑01 00:00:01' to '2038‑01‑19 03:14:07'
YEAR        -- YEAR(4): 1901‑2155, YEAR(2): 70‑69 (1970‑2069)

-- Example
CREATE TABLE events (
    event_date DATE,
    event_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    birth_year YEAR(4)
);

-- DATETIME vs TIMESTAMP comparison
/*
DATETIME:
• Range: '1000‑01‑01 00:00:00' ~ '9999‑12‑31 23:59:59'
• Storage: 8 bytes, stored as‑is
• Timezone: independent

TIMESTAMP:
• Range: '1970‑01‑01 00:00:01' UTC ~ '2038‑01‑19 03:14:07' UTC
• Storage: 4 bytes, stored as UTC
• Timezone: automatically converted to connection timezone
• Auto‑update: supports DEFAULT and ON UPDATE
*/

Data Type Selection Principles

Minimum sufficient : use the smallest type that meets the requirement.

Avoid NULL : prefer NOT NULL to improve performance.

Exact arithmetic : use DECIMAL for monetary values, not FLOAT/DOUBLE.

Temporal types :

Use DATE for dates only.

Use TIMESTAMP for automatic update timestamps.

Use DATETIME for specific moments.

String storage :

Use CHAR for fixed‑length values (e.g., MD5, status codes).

Use VARCHAR for variable‑length text.

Use TEXT family for large text blobs.

Unified character set : set the whole database to utf8mb4.

2. Constraints

2.1 NOT NULL

-- Basic usage
CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- Combined usage
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL DEFAULT 'guest';

-- Note: NULL differs from an empty string ''

2.2 UNIQUE

-- Column‑level unique constraint
CREATE TABLE users (
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

-- Table‑level unique constraint (composite)
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY unique_user_role (user_id, role_id)  -- composite unique
);

-- Adding a unique constraint
ALTER TABLE users ADD UNIQUE (username);
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- View unique indexes
SHOW INDEX FROM users WHERE Non_unique = 0;

2.3 CHECK (MySQL 8.0.16+)

-- Basic check constraint
CREATE TABLE employees (
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65),
    gender CHAR(1) CHECK (gender IN ('M','F'))
);

-- Named check constraints
CREATE TABLE products (
    price DECIMAL(10,2),
    stock INT,
    CONSTRAINT chk_price CHECK (price >= 0),
    CONSTRAINT chk_stock CHECK (stock >= 0),
    CONSTRAINT chk_valid CHECK (price > 0 OR stock = 0)
);

-- Table‑level check constraint
CREATE TABLE orders (
    order_date DATE,
    delivery_date DATE,
    CONSTRAINT chk_dates CHECK (delivery_date >= order_date)
);

-- Adding a check constraint later
ALTER TABLE employees ADD CONSTRAINT chk_salary_range CHECK (salary BETWEEN 3000 AND 100000);

2.4 Primary Key and Auto‑Increment

-- Single‑column primary key
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Auto‑increment characteristics
/*
1. Must be a primary key or unique key
2. Only one AUTO_INCREMENT column per table
3. Starts at 1 by default
4. Starting value can be changed
*/

-- Change auto‑increment start value
ALTER TABLE users AUTO_INCREMENT = 1000;

-- Insert and retrieve auto‑generated ID
INSERT INTO users (username) VALUES ('john');
SELECT LAST_INSERT_ID();  -- returns the newly inserted ID

3. Character Set and Collation

3.1 Character Set

-- List supported character sets
SHOW CHARACTER SET;

-- Common character sets
/*
utf8mb4: 4‑byte Unicode, supports all characters (recommended)
utf8: 3‑byte Unicode, MySQL's "utf8" is a truncated version
latin1: Western European characters
gbk/gb2312: Simplified Chinese
*/

-- Set character set at table level
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4;

-- Set at database level
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Set at connection level
SET NAMES 'utf8mb4';

3.2 Collation

-- List collations
SHOW COLLATION LIKE 'utf8mb4%';

-- Common collations
/*
utf8mb4_unicode_ci: Unicode‑based, accurate but slightly slower (recommended)
utf8mb4_general_ci: General, faster but less accurate
utf8mb4_bin: Binary comparison, case‑sensitive
utf8mb4_0900_ai_ci: MySQL 8.0 default, Unicode 9.0
*/

-- Collation suffix meanings
-- _ci : case‑insensitive
-- _cs : case‑sensitive
-- _bin: binary comparison
-- _ai : accent‑insensitive
-- _as : accent‑sensitive

-- Set collation at table level
CREATE TABLE products (
    name VARCHAR(100) COLLATE utf8mb4_bin,   -- case‑sensitive
    description TEXT COLLATE utf8mb4_unicode_ci
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;