TiDB数据类型 #

TiDB 数据类型
├── 数值类型
│   ├── 整数类型
│   │   ├── TINYINT
│   │   ├── SMALLINT
│   │   ├── MEDIUMINT
│   │   ├── INT / INTEGER
│   │   └── BIGINT
│   ├── 浮点类型
│   │   ├── FLOAT
│   │   └── DOUBLE
│   └── 定点类型
│       └── DECIMAL
│
├── 字符串类型
│   ├── CHAR
│   ├── VARCHAR
│   ├── TEXT
│   │   ├── TINYTEXT
│   │   ├── TEXT
│   │   ├── MEDIUMTEXT
│   │   └── LONGTEXT
│   └── BLOB
│       ├── TINYBLOB
│       ├── BLOB
│       ├── MEDIUMBLOB
│       └── LONGBLOB
│
├── 日期时间类型
│   ├── DATE
│   ├── TIME
│   ├── DATETIME
│   ├── TIMESTAMP
│   └── YEAR
│
├── JSON类型
│   └── JSON
│
└── 其他类型
    ├── BOOLEAN / BOOL
    ├── ENUM
    ├── SET
    └── BIT

-- 创建表
CREATE TABLE integers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tiny_col TINYINT,
    tiny_unsigned TINYINT UNSIGNED,
    small_col SMALLINT,
    medium_col MEDIUMINT,
    int_col INT,
    int_unsigned INT UNSIGNED,
    big_col BIGINT
);

-- 插入数据
INSERT INTO integers (tiny_col, tiny_unsigned, small_col, medium_col, int_col, int_unsigned, big_col)
VALUES 
    (127, 255, 32767, 8388607, 2147483647, 4294967295, 9223372036854775807);

-- 查询
SELECT * FROM integers;

-- 显示宽度不影响存储范围
-- INT(11) 和 INT 存储范围相同
CREATE TABLE display_width (
    id INT(11) ZEROFILL,    
    age INT(3) ZEROFILL     
);

INSERT INTO display_width VALUES (1, 5);

-- 查询结果: id = 00000000001, age = 005
SELECT * FROM display_width;

CREATE TABLE floats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    float_col FLOAT,
    float_precision FLOAT(10, 2),
    double_col DOUBLE,
    double_precision DOUBLE(20, 8)
);

INSERT INTO floats (float_col, float_precision, double_col, double_precision)
VALUES 
    (3.14159, 12345.67, 3.14159265358979, 1234567890.12345678);

SELECT * FROM floats;

-- 浮点数精度问题
SELECT 0.1 + 0.2;  
-- 结果可能不是精确的 0.3

-- 金融计算建议使用 DECIMAL
SELECT CAST(0.1 AS DECIMAL(10, 2)) + CAST(0.2 AS DECIMAL(10, 2));
-- 结果: 0.30

-- DECIMAL(M, D)
-- M: 总位数 (精度)
-- D: 小数位数 (标度)

CREATE TABLE decimals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),      
    rate DECIMAL(5, 4),        
    amount DECIMAL(20, 6)      
);

INSERT INTO decimals (price, rate, amount)
VALUES 
    (99999999.99, 1.2345, 12345678901234.567890);

SELECT * FROM decimals;

-- 账户余额表
CREATE TABLE accounts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    balance DECIMAL(20, 2) NOT NULL DEFAULT 0.00,
    frozen_amount DECIMAL(20, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 转账事务
BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE user_id = 2;
COMMIT;

-- 查询余额
SELECT user_id, balance FROM accounts;

CREATE TABLE strings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    char_col CHAR(10),        
    varchar_col VARCHAR(255), 
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

INSERT INTO strings (char_col, varchar_col, name, email)
VALUES 
    ('hello', 'This is a variable length string', 'Alice', 'alice@example.com'),
    ('world', 'Another string', 'Bob', 'bob@example.com');

SELECT * FROM strings;

-- CHAR 定长存储，不足补空格
-- CHAR(10) 存储 'hello' 实际存储 'hello     '

-- VARCHAR 变长存储，按实际长度
-- VARCHAR(10) 存储 'hello' 实际存储 'hello' (5字节 + 长度前缀)

-- 查询时 CHAR 会去掉尾部空格
SELECT char_col, LENGTH(char_col), CHAR_LENGTH(char_col) FROM strings;

CREATE TABLE articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    summary TEXT,
    content MEDIUMTEXT,
    attachments LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO articles (title, summary, content)
VALUES 
    ('TiDB入门教程', '这是一篇TiDB入门教程', '详细内容...'),
    ('分布式数据库设计', '分布式数据库设计原理', '详细内容...');

SELECT id, title, summary FROM articles;

-- VARCHAR 适合较短的字符串
-- TEXT 适合大文本

-- VARCHAR 可以有默认值
CREATE TABLE test1 (
    name VARCHAR(100) DEFAULT 'unknown'
);

-- TEXT 不能有默认值
CREATE TABLE test2 (
    content TEXT DEFAULT NULL  
);

CREATE TABLE files (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100),
    file_size INT,
    file_data LONGBLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入二进制数据 (通常通过应用程序)
-- INSERT INTO files (filename, mime_type, file_size, file_data)
-- VALUES ('image.png', 'image/png', 1024, ?);

-- 查看支持的字符集
SHOW CHARACTER SET;

-- 查看排序规则
SHOW COLLATION;

-- 指定字符集
CREATE TABLE charset_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) DEFAULT CHARSET=utf8mb4;

-- 常用字符集
-- utf8mb4: 完整 UTF-8，支持 emoji
-- utf8: MySQL 的 utf8 是 utf8mb3，不支持 emoji
-- latin1: 西欧字符集

-- 常用排序规则
-- utf8mb4_general_ci: 快速，但不精确
-- utf8mb4_unicode_ci: 精确，推荐使用
-- utf8mb4_bin: 二进制比较，区分大小写

CREATE TABLE dates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    birth_date DATE NOT NULL,
    hire_date DATE,
    event_date DATE
);

INSERT INTO dates (birth_date, hire_date, event_date)
VALUES 
    ('1990-05-15', '2020-01-01', '2024-12-25'),
    ('1985-08-20', '2018-06-15', '2024-01-01');

-- 日期函数
SELECT 
    birth_date,
    YEAR(birth_date) AS year,
    MONTH(birth_date) AS month,
    DAY(birth_date) AS day,
    DAYOFWEEK(birth_date) AS weekday,
    DAYNAME(birth_date) AS weekday_name
FROM dates;

CREATE TABLE times (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_time TIME,
    end_time TIME,
    duration TIME
);

INSERT INTO times (start_time, end_time, duration)
VALUES 
    ('09:00:00', '18:00:00', '09:00:00'),
    ('08:30:00', '17:30:00', '09:00:00');

-- 时间函数
SELECT 
    start_time,
    end_time,
    TIMEDIFF(end_time, start_time) AS diff,
    HOUR(start_time) AS hour,
    MINUTE(start_time) AS minute
FROM times;

CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (event_name, start_time, end_time)
VALUES 
    ('会议', '2024-03-27 10:00:00', '2024-03-27 12:00:00'),
    ('培训', '2024-03-28 14:00:00', '2024-03-28 17:00:00');

-- 日期时间函数
SELECT 
    event_name,
    start_time,
    DATE_FORMAT(start_time, '%Y年%m月%d日 %H:%i') AS formatted,
    TIMESTAMPDIFF(HOUR, start_time, end_time) AS hours
FROM events;

CREATE TABLE timestamps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO timestamps (name) VALUES ('test1');

-- 等待一秒
-- UPDATE timestamps SET name = 'test1_updated' WHERE id = 1;

SELECT * FROM timestamps;

-- TIMESTAMP 特点
-- 1. 自动更新
-- 2. 时区转换
-- 3. 范围限制到 2038 年

-- 区别对比
CREATE TABLE datetime_vs_timestamp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dt_col DATETIME,
    ts_col TIMESTAMP
);

INSERT INTO datetime_vs_timestamp (dt_col, ts_col)
VALUES (NOW(), NOW());

SELECT 
    dt_col,
    ts_col,
    FROM_UNIXTIME(UNIX_TIMESTAMP(dt_col)) AS dt_to_unix
FROM datetime_vs_timestamp;

-- 主要区别:
-- 1. 范围: DATETIME 更大
-- 2. 时区: TIMESTAMP 受时区影响
-- 3. 存储: TIMESTAMP 4字节, DATETIME 8字节
-- 4. 默认值: TIMESTAMP 可自动更新

CREATE TABLE years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_col YEAR,
    description VARCHAR(100)
);

INSERT INTO years (year_col, description)
VALUES 
    (2024, '今年'),
    (2025, '明年'),
    (1999, '上世纪');

SELECT * FROM years;

CREATE TABLE json_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON,
    config JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入 JSON 数据
INSERT INTO json_data (data, config)
VALUES 
    ('{"name": "Alice", "age": 25, "skills": ["MySQL", "TiDB"]}',
     '{"theme": "dark", "language": "zh-CN"}'),
    ('{"name": "Bob", "age": 30, "address": {"city": "Beijing", "zip": "100000"}}',
     '{"theme": "light", "language": "en-US"}');

SELECT * FROM json_data;

-- 提取 JSON 值
SELECT 
    id,
    JSON_EXTRACT(data, '$.name') AS name,
    JSON_EXTRACT(data, '$.age') AS age,
    data->>'$.name' AS name_text,  
    data->'$.skills' AS skills
FROM json_data;

-- 提取嵌套数据
SELECT 
    id,
    data->>'$.address.city' AS city,
    data->>'$.address.zip' AS zip
FROM json_data
WHERE data->>'$.address.city' IS NOT NULL;

-- 提取数组元素
SELECT 
    id,
    data->'$.skills[0]' AS first_skill,
    JSON_LENGTH(data, '$.skills') AS skills_count
FROM json_data
WHERE JSON_TYPE(data->'$.skills') = 'ARRAY';

-- 设置 JSON 值
UPDATE json_data 
SET data = JSON_SET(data, '$.age', 26)
WHERE id = 1;

-- 添加新字段
UPDATE json_data 
SET data = JSON_SET(data, '$.email', 'alice@example.com')
WHERE id = 1;

-- 删除字段
UPDATE json_data 
SET data = JSON_REMOVE(data, '$.email')
WHERE id = 1;

-- 追加数组元素
UPDATE json_data 
SET data = JSON_ARRAY_APPEND(data, '$.skills', 'PostgreSQL')
WHERE id = 1;

-- JSON 包含
SELECT * FROM json_data 
WHERE JSON_CONTAINS(data, '"Alice"', '$.name');

-- JSON 路径存在
SELECT * FROM json_data 
WHERE JSON_CONTAINS_PATH(data, 'one', '$.address');

-- JSON 搜索
SELECT * FROM json_data 
WHERE JSON_SEARCH(data, 'one', 'Beijing') IS NOT NULL;

-- TiDB 支持对 JSON 字段创建虚拟列索引
CREATE TABLE json_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON,
    name VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) VIRTUAL,
    INDEX idx_name (name)
);

INSERT INTO json_index (data)
VALUES 
    ('{"name": "Alice", "age": 25}'),
    ('{"name": "Bob", "age": 30}');

-- 使用索引查询
SELECT * FROM json_index WHERE name = 'Alice';

-- BOOLEAN 是 TINYINT(1) 的别名
CREATE TABLE booleans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE
);

INSERT INTO booleans (is_active, is_deleted)
VALUES (TRUE, FALSE), (FALSE, TRUE), (1, 0);

SELECT 
    id,
    is_active,
    is_deleted,
    is_active IS TRUE AS active_check
FROM booleans;

CREATE TABLE enums (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'active', 'inactive', 'deleted') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high') NOT NULL
);

INSERT INTO enums (status, priority)
VALUES 
    ('active', 'high'),
    ('inactive', 'low'),
    ('pending', 'medium');

-- 查询
SELECT 
    id,
    status,
    priority,
    status + 0 AS status_index  
FROM enums;

-- 严格模式
SET sql_mode = 'strict_trans_tables';
INSERT INTO enums (status, priority) VALUES ('invalid', 'high');  

CREATE TABLE sets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    permissions SET('read', 'write', 'delete', 'admin')
);

INSERT INTO sets (permissions)
VALUES 
    ('read,write'),
    ('read,write,delete'),
    ('admin');

-- 查询
SELECT 
    id,
    permissions,
    FIND_IN_SET('write', permissions) AS has_write
FROM sets;

-- 包含特定权限
SELECT * FROM sets 
WHERE FIND_IN_SET('write', permissions) > 0;

CREATE TABLE bits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    flags BIT(8),
    options BIT(16)
);

INSERT INTO bits (flags, options)
VALUES (b'00001010', b'1111000011110000');

-- 查询
SELECT 
    id,
    flags,
    flags + 0 AS flags_decimal,
    BIN(flags + 0) AS flags_binary
FROM bits;

-- 位操作
SELECT 
    flags,
    flags & b'00000010' AS bit_1_check,
    flags | b'00010000' AS set_bit_4
FROM bits;

-- CAST 函数
SELECT 
    CAST('123' AS SIGNED) AS int_val,
    CAST('123.45' AS DECIMAL(10, 2)) AS decimal_val,
    CAST(123 AS CHAR) AS char_val,
    CAST('2024-03-27' AS DATE) AS date_val;

-- CONVERT 函数
SELECT 
    CONVERT('123', SIGNED) AS int_val,
    CONVERT('abc' USING utf8mb4) AS utf8_val;

-- 数值与字符串转换
SELECT '123' + 456;  
SELECT CONCAT(123, 'abc');  

-- 日期转换
SELECT DATE('2024-03-27 10:30:00');
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');