MySQL数据类型

CREATE TABLE integer_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED,
    score SMALLINT,
    population INT UNSIGNED,
    big_number BIGINT
);

CREATE TABLE float_example (
    id INT,
    price DECIMAL(10, 2),    -- 总10位，2位小数
    percentage FLOAT,
    scientific_value DOUBLE
);

-- 查看当前时区
SELECT @@global.time_zone, @@session.time_zone;

-- 设置会话时区
SET time_zone = '+08:00';
SET time_zone = 'Asia/Shanghai';

-- 转换时区
SELECT CONVERT_TZ('2023-01-01 12:00:00', '+00:00', '+08:00');

CREATE TABLE datetime_example (
    id INT,
    birth_date DATE,
    meeting_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    birth_year YEAR
);

CREATE TABLE string_example (
    id INT,
    country_code CHAR(2),        -- 固定2字符，如'CN'
    username VARCHAR(50),       -- 最多50字符
    email VARCHAR(100),
    description TEXT,           -- 长文本描述
    long_content LONGTEXT      -- 超长文本内容
);

CREATE TABLE zerofill_test (
    id INT(5) ZEROFILL
);
INSERT INTO zerofill_test VALUES (1), (123), (12345);
-- 显示结果：00001, 00123, 12345

CREATE TABLE enum_example (
    id INT,
    status ENUM('active', 'inactive', 'pending'),
    priority ENUM('low', 'medium', 'high')
);

CREATE TABLE set_example (
    id INT,
    tags SET('red', 'green', 'blue', 'yellow')
);

-- 插入数据示例
INSERT INTO set_example (id, tags) VALUES (1, 'red,green');

CREATE TABLE json_example (
    id INT,
    user_data JSON,
    preferences JSON
);

-- 插入JSON数据
INSERT INTO json_example (id, user_data) 
VALUES (1, '{"name": "John", "age": 30, "hobbies": ["reading", "swimming"]}');

-- JSON查询函数
SELECT JSON_EXTRACT(user_data, '$.name') FROM json_example;
SELECT user_data->>'$.name' FROM json_example;

-- 创建包含数组的JSON列
CREATE TABLE products (
    id INT PRIMARY KEY,
    tags JSON
);
-- 创建多值索引
CREATE INDEX idx_tags ON products ((CAST(tags->'$' AS CHAR(32) ARRAY)));

CREATE TABLE people (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    height_cm DECIMAL(5,2),
    height_inches DECIMAL(5,2) GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

-- 隐式转换
SELECT '123' + 456;  -- 结果为579

-- 显式转换
SELECT CAST('123' AS UNSIGNED);
SELECT CONVERT('2023-01-01', DATE);

-- 错误：索引列与比较值类型不匹配，导致索引失效
SELECT * FROM users WHERE phone = 13800138000;  -- phone是VARCHAR，数字是INT

-- 正确：保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

-- 日期比较：字符串与日期类型比较
SELECT * FROM orders WHERE order_date = '2023-01-01';  -- 可隐式转换但建议显式
SELECT * FROM orders WHERE order_date = DATE('2023-01-01');

-- 创建表时指定字符集
CREATE TABLE charset_example (
    id INT,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 不区分大小写
SELECT 'A' = 'a' COLLATE utf8mb4_general_ci;  -- 返回1

-- 区分大小写
SELECT 'A' = 'a' COLLATE utf8mb4_bin;  -- 返回0