MySQL数据类型 #

MySQL数据类型
├── 数值类型
│   ├── 整数类型
│   │   ├── TINYINT
│   │   ├── SMALLINT
│   │   ├── MEDIUMINT
│   │   ├── INT
│   │   └── BIGINT
│   ├── 浮点类型
│   │   ├── FLOAT
│   │   └── DOUBLE
│   └── 定点类型
│       └── DECIMAL
├── 字符串类型
│   ├── CHAR
│   ├── VARCHAR
│   ├── TEXT系列
│   └── BLOB系列
├── 日期时间类型
│   ├── DATE
│   ├── TIME
│   ├── DATETIME
│   ├── TIMESTAMP
│   └── YEAR
├── JSON类型
│   └── JSON
└── 其他类型
    ├── BOOLEAN
    ├── ENUM
    └── SET

-- 创建表
CREATE TABLE integers (
    tiny_col TINYINT,
    small_col SMALLINT,
    medium_col MEDIUMINT,
    int_col INT,
    bigint_col BIGINT
);

-- 有符号整数（默认）
CREATE TABLE signed_int (
    num INT
);

-- 无符号整数
CREATE TABLE unsigned_int (
    num INT UNSIGNED
);

-- 显示宽度（不影响存储范围）
CREATE TABLE display_width (
    num INT(5) ZEROFILL
);

INSERT INTO display_width VALUES (123);
-- 查询结果：00123

-- FLOAT类型
CREATE TABLE float_table (
    price FLOAT,
    weight FLOAT(5,2)
);

-- FLOAT(M,D)
-- M：总位数，D：小数位数
-- FLOAT(5,2)：最多5位，小数2位，如：123.45

INSERT INTO float_table VALUES (123.456, 123.45);
-- price存储：约123.456（可能有精度损失）
-- weight存储：123.45

-- DOUBLE类型
CREATE TABLE double_table (
    precise_value DOUBLE(10,6)
);

INSERT INTO double_table VALUES (123.456789);
-- 存储：123.456789

-- 浮点数精度问题
SELECT 0.1 + 0.2 = 0.3;  -- 结果：0（false）

-- 原因：浮点数存储有精度损失
SELECT 0.1 + 0.2;  -- 结果：0.30000000000000004

-- 对于精确计算，使用DECIMAL
SELECT CAST(0.1 AS DECIMAL(10,1)) + CAST(0.2 AS DECIMAL(10,1)) = 0.3;
-- 结果：1（true）

-- DECIMAL(M,D)
-- M：精度，总位数（1-65）
-- D：标度，小数位数（0-30）

CREATE TABLE decimal_table (
    amount DECIMAL(10,2),
    rate DECIMAL(5,4)
);

INSERT INTO decimal_table VALUES (12345678.90, 0.1234);

-- 精确计算
SELECT amount * rate FROM decimal_table;
-- 结果：1524156.751926（精确）

-- 推荐方案1：DECIMAL存储
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2)  -- 单位：元
);

-- 推荐方案2：整数存储（分）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount BIGINT  -- 单位：分，12345表示123.45元
);

-- 查询时转换
SELECT amount / 100 AS amount_yuan FROM orders;

-- CHAR类型
CREATE TABLE char_table (
    code CHAR(10),
    status CHAR(1)
);

INSERT INTO char_table VALUES ('ABC', 'A');
-- code存储：'ABC       '（补空格到10位）

-- VARCHAR类型
CREATE TABLE varchar_table (
    name VARCHAR(100),
    description VARCHAR(500)
);

INSERT INTO varchar_table VALUES ('John', 'This is a description');
-- name存储：'John'（4字节）

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    summary TEXT,
    content LONGTEXT
);

-- TEXT类型不能有默认值
-- 错误：content TEXT DEFAULT ''

CREATE TABLE files (
    id INT PRIMARY KEY,
    file_name VARCHAR(100),
    file_data MEDIUMBLOB,
    file_size INT
);

-- 存储图片、文件等二进制数据

CREATE TABLE date_table (
    birth_date DATE,
    event_date DATE
);

INSERT INTO date_table VALUES ('1990-05-15', '2024-12-31');

-- 使用函数
INSERT INTO date_table VALUES (CURRENT_DATE, CURDATE());

-- 日期格式
SELECT DATE_FORMAT(birth_date, '%Y年%m月%d日') FROM date_table;
-- 结果：1990年05月15日

CREATE TABLE time_table (
    start_time TIME,
    duration TIME
);

INSERT INTO time_table VALUES ('09:30:00', '08:00:00');

-- 时间范围可以超过24小时（表示时长）
INSERT INTO time_table VALUES ('100:00:00', '200:30:00');

-- 使用函数
INSERT INTO time_table VALUES (CURRENT_TIME, NOW());

CREATE TABLE datetime_table (
    created_at DATETIME,
    updated_at DATETIME
);

INSERT INTO datetime_table VALUES ('2024-01-15 10:30:00', NOW());

-- 自动初始化
CREATE TABLE auto_datetime (
    id INT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO auto_datetime (id) VALUES (1);
-- created_at和updated_at自动设置为当前时间

UPDATE auto_datetime SET id = 2 WHERE id = 1;
-- updated_at自动更新为当前时间

CREATE TABLE timestamp_table (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- TIMESTAMP特点：
-- 1. 自动转换为UTC存储
-- 2. 查询时转换为当前时区
-- 3. 范围受限（到2038年）

-- 查看时区设置
SHOW VARIABLES LIKE '%time_zone%';

-- 设置时区
SET time_zone = '+8:00';  -- 东八区
SET time_zone = 'SYSTEM'; -- 系统时区

CREATE TABLE year_table (
    birth_year YEAR,
    publish_year YEAR(4)
);

INSERT INTO year_table VALUES (1990, 2024);

-- YEAR支持2位或4位
-- 2位年份：00-69转换为2000-2069，70-99转换为1970-1999
INSERT INTO year_table VALUES (24, 99);
-- 存储：2024, 1999

-- 当前日期时间
SELECT NOW(), CURRENT_TIMESTAMP, SYSDATE();
SELECT CURDATE(), CURRENT_DATE;
SELECT CURTIME(), CURRENT_TIME;

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);     -- 加7天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);   -- 减1个月
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 计算天数差

-- 提取部分
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
SELECT WEEKDAY(NOW());  -- 星期几（0=周一）

CREATE TABLE json_table (
    id INT PRIMARY KEY,
    data JSON
);

-- 插入JSON数据
INSERT INTO json_table VALUES (
    1,
    '{"name": "John", "age": 30, "skills": ["MySQL", "Python"]}'
);

-- 插入JSON数组
INSERT INTO json_table VALUES (
    2,
    '[{"id": 1, "name": "A"}, {"id": 2, "name": "B"}]'
);

-- 提取值
SELECT data->'$.name' FROM json_table WHERE id = 1;
SELECT data->>'$.name' FROM json_table WHERE id = 1;  -- 去掉引号

-- JSON_EXTRACT函数
SELECT JSON_EXTRACT(data, '$.name') FROM json_table;

-- 提取数组元素
SELECT data->'$.skills[0]' FROM json_table WHERE id = 1;

-- 创建JSON
SELECT JSON_OBJECT('name', 'John', 'age', 30);
-- 结果：{"name": "John", "age": 30}

SELECT JSON_ARRAY(1, 2, 3, 'a', 'b');
-- 结果：[1, 2, 3, "a", "b"]

-- 合并JSON
SELECT JSON_MERGE_PRESERVE(
    '{"a": 1}',
    '{"b": 2}'
);
-- 结果：{"a": 1, "b": 2}

-- 设置值
UPDATE json_table 
SET data = JSON_SET(data, '$.email', 'john@example.com')
WHERE id = 1;

-- 删除值
UPDATE json_table 
SET data = JSON_REMOVE(data, '$.age')
WHERE id = 1;

-- 按JSON字段查询
SELECT * FROM json_table WHERE data->'$.name' = '"John"';

-- 使用JSON_CONTAINS
SELECT * FROM json_table 
WHERE JSON_CONTAINS(data->'$.skills', '"MySQL"');

-- 使用JSON_SEARCH
SELECT * FROM json_table 
WHERE JSON_SEARCH(data, 'one', 'John') IS NOT NULL;

CREATE TABLE enum_table (
    id INT PRIMARY KEY,
    status ENUM('active', 'inactive', 'pending'),
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium'
);

INSERT INTO enum_table (id, status) VALUES (1, 'active');

-- ENUM存储为整数索引
-- 'active'=1, 'inactive'=2, 'pending'=3

-- 查询索引值
SELECT status + 0 FROM enum_table;

-- 查询所有可能值
SELECT DISTINCT status FROM enum_table;

-- 使用数字插入
INSERT INTO enum_table (id, status) VALUES (2, 2);  -- 'inactive'

CREATE TABLE set_table (
    id INT PRIMARY KEY,
    tags SET('tag1', 'tag2', 'tag3', 'tag4')
);

-- 可以存储多个值
INSERT INTO set_table VALUES (1, 'tag1,tag2');
INSERT INTO set_table VALUES (2, 'tag1,tag2,tag3');

-- 使用FIND_IN_SET查询
SELECT * FROM set_table WHERE FIND_IN_SET('tag1', tags) > 0;

-- 使用位运算
SELECT * FROM set_table WHERE tags & 1;  -- 包含tag1

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

INSERT INTO bool_table (id, is_active) VALUES (1, TRUE);
INSERT INTO bool_table (id, is_active) VALUES (2, FALSE);

-- 查询
SELECT * FROM bool_table WHERE is_active = TRUE;
SELECT * FROM bool_table WHERE is_active IS TRUE;
SELECT * FROM bool_table WHERE is_active;

-- 字符串转数字
SELECT '123' + 0;  -- 结果：123
SELECT '123abc' + 0;  -- 结果：123

-- 数字转字符串
SELECT CONCAT(123, 'abc');  -- 结果：'123abc'

-- 日期转换
SELECT '2024-01-01' + INTERVAL 1 DAY;

-- CAST函数
SELECT CAST('123' AS SIGNED);
SELECT CAST('123.45' AS DECIMAL(10,2));
SELECT CAST(123 AS CHAR);
SELECT CAST(NOW() AS DATE);

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

-- 常用转换
SELECT 
    CAST('2024-01-15' AS DATE),
    CAST('10:30:00' AS TIME),
    CAST('2024-01-15 10:30:00' AS DATETIME);