MySQL: شرح مفصل ودليل اختيار لأنواع بيانات MySQL

-- Demonstration of Floating-Point Precision Loss
SELECT 0.1 + 0.2;  -- Results: 0.300000004（approximate value，Inaccurate）

-- It becomes more apparent after it is entered into the table
CREATE TABLE float_demo (price FLOAT);
INSERT INTO float_demo VALUES (199.99);
SELECT price FROM float_demo;  -- Results: 199.99（The display is correct, but there is a slight error in the internal storage.）
SELECT price + 0.01 FROM float_demo;  -- Cumulative errors may become apparent during calculations

-- DECIMAL Precise Storage，No error
CREATE TABLE decimal_demo (price DECIMAL(10,2));
INSERT INTO decimal_demo VALUES (199.99);
SELECT price + 0.01 FROM decimal_demo;  -- Results: 200.00（Accurate）

CREATE TABLE user_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,              -- Age 0-255 Enough
    view_count INT UNSIGNED,           -- Number of views
    total_orders BIGINT UNSIGNED       -- Total Number of Orders（It's very likely）
);

-- UNSIGNED Indicates unsigned（Only positive numbers）
INSERT INTO user_stats (age, view_count, total_orders) 
VALUES (25, 1000000, 9999999999);

CREATE TABLE financials (
    id INT PRIMARY KEY,
    amount DECIMAL(12,2),       -- Amount，Maximum 9999999999.99
    rate DECIMAL(5,4),          -- Ratio，Maximum 9.9999
    quantity DECIMAL(8,0)       -- Number of integers
);

INSERT INTO financials VALUES (1, 1234567.89, 0.0850, 1000);
SELECT * FROM financials;

+----+-------------+--------+----------+
| id | amount      | rate   | quantity |
+----+-------------+--------+----------+
|  1 | 1234567.89  | 0.0850 |     1000 |
+----+-------------+--------+----------+

-- CHAR(10) Fixed length, storing 'abc' uses 10 bytes
-- VARCHAR(10) Variable length, storing 'abc' uses 4 bytes (3 + 1 length prefix)

CREATE TABLE phones (
    -- Mobile Number (Fixed 11 digits), use CHAR for faster lookup
    mobile CHAR(11),
    -- Username length varies, use VARCHAR to save space
    username VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending'
);

INSERT INTO orders (id, status) VALUES (1, 'paid');
INSERT INTO orders (id) VALUES (2);  -- Default 'pending'

-- ENUM stores numerical indexes (1,2,3...), not strings
SELECT status, status + 0 AS index_num FROM orders;

+---------+-----------+
| status  | index_num |
+---------+-----------+
| paid    |         2 |
| pending |         1 |
+---------+-----------+

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO events (event_name, event_date, start_time) 
VALUES ('Tech Conference', '2026-09-15', '09:00:00');

SELECT * FROM events;

+----+----------------+------------+------------+---------------------+---------------------+
| id | event_name     | event_date | start_time | created_at          | updated_at          |
+----+----------------+------------+------------+---------------------+---------------------+
|  1 | Tech Conference| 2026-09-15 | 09:00:00   | 2026-07-03 10:00:00 | 2026-07-03 10:00:00 |
+----+----------------+------------+------------+---------------------+---------------------+

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES 
('iPhone', '{"color": "black", "storage": 128, "tags": ["phone", "apple"]}');

-- Extract JSON value
SELECT 
    name,
    attributes->>'$.color' AS color,
    attributes->>'$.storage' AS storage
FROM products;

-- JSON Array Lookup
SELECT * FROM products WHERE JSON_CONTAINS(attributes->'$.tags', '"apple"');

+--------+-------+---------+
| name   | color | storage |
+--------+-------+---------+
| iPhone | black | 128     |
+--------+-------+---------+