Tìm hiểu về Data Types trong MySQL: Hướng dẫn đầy đủ từ cơ bản đến nâng cao

CREATE TABLE product (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quantity SMALLINT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    is_available TINYINT(1) NOT NULL DEFAULT 1 -- Thường được sử dụng làm boolean
);

CREATE TABLE measurement (
    id INT AUTO_INCREMENT PRIMARY KEY,
    temperature FLOAT NOT NULL, -- Độ chính xác thấp hơn
    precise_value DOUBLE NOT NULL, -- Độ chính xác cao hơn
    money DECIMAL(10,2) NOT NULL -- Lưu trữ dữ liệu tiền tệ chính xác
);

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE, -- Độ dài thay đổi, tiết kiệm không gian
    country_code CHAR(2) NOT NULL, -- Độ dài cố định (ví dụ: VN, US, UK)
    password_hash CHAR(64) NOT NULL -- SHA-256 hash luôn có độ dài cố định
);

CREATE TABLE blog_post (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    summary TEXT NOT NULL, -- Cho đoạn văn ngắn
    content LONGTEXT NOT NULL, -- Cho nội dung bài viết dài
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE file_storage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    file_data MEDIUMBLOB NOT NULL, -- Lưu trữ tệp tin nhị phân như ảnh, PDF
    file_hash BINARY(32) NOT NULL -- Lưu trữ hash MD5/SHA-256
);

CREATE TABLE event (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL, -- Chỉ lưu ngày
    start_time TIME NOT NULL, -- Chỉ lưu giờ
    end_time TIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- Lưu cả ngày và giờ
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- Tự động cập nhật khi record thay đổi
);

CREATE TABLE store_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_name VARCHAR(100) NOT NULL,
    location POINT NOT NULL, -- Lưu trữ tọa độ vị trí cửa hàng
    delivery_area POLYGON -- Lưu trữ khu vực giao hàng
);

-- Chèn dữ liệu không gian
INSERT INTO store_locations (store_name, location)
VALUES ('Cửa hàng Trung tâm', ST_GeomFromText('POINT(105.85 21.03)')); -- Tọa độ Hà Nội

CREATE TABLE product_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    attributes JSON NOT NULL, -- Lưu trữ các thuộc tính động của sản phẩm
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Chèn dữ liệu JSON
INSERT INTO product_details (product_id, attributes)
VALUES (1, '{"color": "black", "size": "M", "materials": ["cotton", "polyester"], "dimensions": {"width": 20, "height": 30}}');

-- Truy vấn dữ liệu JSON
SELECT product_id, JSON_EXTRACT(attributes, '$.color') AS color
FROM product_details
WHERE JSON_EXTRACT(attributes, '$.size') = 'M';

-- Hoặc sử dụng toán tử ->
SELECT product_id, attributes->'$.color' AS color
FROM product_details
WHERE attributes->'$.size' = 'M';

-- Không tối ưu
CREATE TABLE product_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_id INT, -- Thừa không gian nếu chỉ có vài cửa hàng
    quantity INT, -- Thừa không gian nếu số lượng không lớn
    sku VARCHAR(255) -- Thừa không gian nếu mã SKU luôn có độ dài 10
);

-- Tối ưu hơn
CREATE TABLE product_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_id SMALLINT UNSIGNED, -- Đủ cho 65,535 cửa hàng
    quantity MEDIUMINT UNSIGNED, -- Đủ cho 16,777,215 sản phẩm
    sku CHAR(10) -- Cố định 10 ký tự
);

-- Không tối ưu
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NULL, -- Cho phép NULL
    delivery_date DATETIME NULL, -- Cho phép NULL
    status VARCHAR(20) NULL -- Cho phép NULL
);

-- Tối ưu hơn
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    delivery_date DATETIME NOT NULL DEFAULT '9999-12-31', -- Giá trị mặc định xa trong tương lai
    status VARCHAR(20) NOT NULL DEFAULT 'pending' -- Giá trị mặc định có ý nghĩa
);

-- Không tối ưu
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL -- Có thể nhập sai giá trị
);

-- Tối ưu hơn
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    status ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open'
    -- Chỉ cho phép 4 giá trị này và tiết kiệm không gian
);

-- Không tối ưu
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 8 bytes
    -- Các cột khác
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT, -- 4 bytes, không khớp với kiểu của products.id
    -- Các cột khác
    FOREIGN KEY (product_id) REFERENCES products(id) -- Sẽ gây chuyển đổi kiểu ngầm
);

-- Tối ưu hơn
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 4 bytes
    -- Các cột khác
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED, -- Cùng kiểu với products.id
    -- Các cột khác
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Sử dụng đúng kiểu
CREATE TABLE user_address (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    address VARCHAR(200) NOT NULL, -- Độ dài thay đổi
    city VARCHAR(50) NOT NULL, -- Độ dài thay đổi
    postal_code CHAR(5) NOT NULL, -- Độ dài cố định (giả sử mã bưu điện luôn có 5 ký tự)
    country_code CHAR(2) NOT NULL -- Độ dài cố định (ISO 3166-1 alpha-2)
);

-- Schema chưa tối ưu
CREATE TABLE users_unoptimized (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL, -- Thừa không gian, username hiếm khi > 50 ký tự
    password VARCHAR(255) NOT NULL, -- Nên dùng CHAR vì hash thường có độ dài cố định
    email VARCHAR(255) NOT NULL,
    is_active INT NOT NULL, -- Không hiệu quả cho giá trị boolean
    registration_date DATETIME,
    last_login DATETIME
);

-- Schema đã tối ưu
CREATE TABLE users_optimized (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- UNSIGNED tăng phạm vi dương
    username VARCHAR(50) NOT NULL UNIQUE, -- Giảm độ dài tối đa
    password CHAR(60) NOT NULL, -- Độ dài cố định cho bcrypt hash
    email VARCHAR(100) NOT NULL, -- Email hiếm khi > 100 ký tự
    is_active TINYINT(1) NOT NULL DEFAULT 1, -- Sử dụng 1 byte cho giá trị boolean
    registration_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Không cho phép NULL
    last_login TIMESTAMP NULL DEFAULT NULL -- NULL có ý nghĩa = chưa đăng nhập
);

-- Schema chưa tối ưu
CREATE TABLE products_unoptimized (
    id BIGINT AUTO_INCREMENT PRIMARY KEY, -- Quá lớn cho hầu hết các cửa hàng
    name VARCHAR(255), -- Cho phép NULL không cần thiết
    description TEXT, -- Không giới hạn độ dài
    price FLOAT, -- Không chính xác cho tiền tệ
    category VARCHAR(100), -- Các danh mục thường lặp lại
    status VARCHAR(20), -- Các trạng thái từ một tập hữu hạn
    created_at DATETIME,
    updated_at DATETIME
);

-- Schema đã tối ưu
CREATE TABLE categories (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE products_optimized (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Đủ cho hàng triệu sản phẩm
    name VARCHAR(100) NOT NULL, -- Không cho phép NULL
    description TEXT NOT NULL, -- Không cho phép NULL
    price DECIMAL(10,2) NOT NULL, -- Chính xác cho tiền tệ
    category_id SMALLINT UNSIGNED NOT NULL, -- Sử dụng bảng tra cứu
    status ENUM('active', 'inactive', 'discontinued') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Chuyển đổi từ VARCHAR sang CHAR
ALTER TABLE users MODIFY username CHAR(50) NOT NULL;

-- Chuyển đổi từ INT sang BIGINT
ALTER TABLE products MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;

-- Chuyển đổi từ FLOAT sang DECIMAL
ALTER TABLE orders MODIFY total_amount DECIMAL(10,2) NOT NULL;