MariaDB数据类型 #

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

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

INSERT INTO integers VALUES (127, 32767, 8388607, 2147483647, 9223372036854775807);

-- 无符号整数
CREATE TABLE unsigned_integers (
    tiny_unsigned TINYINT UNSIGNED,
    int_unsigned INT UNSIGNED
);

INSERT INTO unsigned_integers VALUES (255, 4294967295);

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

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

-- MariaDB 10.5+ 支持 BOOLEAN 类型别名
CREATE TABLE bool_example (
    is_active BOOLEAN  -- 等同于 TINYINT(1)
);

-- 序列类型（MariaDB 10.3+）
CREATE TABLE sequence_example (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- 使用序列引擎
CREATE TABLE seq ENGINE=SEQUENCE;
SELECT * FROM seq WHERE seq=1 FOR UPDATE;

CREATE TABLE float_table (
    price FLOAT,
    weight FLOAT(5,2),
    precise_value DOUBLE(10,6)
);

INSERT INTO float_table VALUES (123.456, 123.45, 123.456789);

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

-- 浮点数精度问题
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;
-- 结果：精确值

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

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

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

-- 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'; -- 系统时区

-- MariaDB支持微秒精度
CREATE TABLE microsecond_table (
    id INT PRIMARY KEY,
    created_at DATETIME(6)  -- 6位微秒精度
);

INSERT INTO microsecond_table VALUES (1, '2024-01-15 10:30:00.123456');

SELECT created_at FROM microsecond_table;
-- 结果：2024-01-15 10:30:00.123456

-- 当前日期时间
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=周一）

-- MariaDB特有函数
SELECT TO_SECONDS('2024-01-15');  -- 转换为秒数
SELECT SECONDS_TO_TIME(3600);      -- 秒数转时间

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;

-- MariaDB 10.5+ JSON函数增强

-- JSON_VALUE（提取标量值）
SELECT JSON_VALUE('{"name": "John"}', '$.name');
-- 结果：John

-- JSON_TABLE（将JSON转为表）
SELECT * FROM JSON_TABLE(
    '[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]',
    '$[*]' COLUMNS(
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) AS jt;

-- JSON_EXISTS（检查路径是否存在）
SELECT JSON_EXISTS('{"a": 1}', '$.a');  -- 1
SELECT JSON_EXISTS('{"a": 1}', '$.b');  -- 0

-- JSON_NORMALIZE（规范化JSON）
SELECT JSON_NORMALIZE('{"b":2,"a":1}');
-- 结果：{"a":1,"b":2}（按键排序）

-- 按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 json_indexed (
    id INT PRIMARY KEY,
    data JSON,
    name VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) STORED,
    INDEX idx_name (name)
);

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;

CREATE TABLE spatial_table (
    id INT PRIMARY KEY,
    location POINT,
    route LINESTRING,
    area POLYGON,
    geometry GEOMETRY
);

-- 插入点
INSERT INTO spatial_table (id, location) 
VALUES (1, POINT(116.404, 39.915));

-- 插入线
INSERT INTO spatial_table (id, route) 
VALUES (2, LINESTRING(POINT(0,0), POINT(1,1), POINT(2,2)));

-- 插入多边形
INSERT INTO spatial_table (id, area) 
VALUES (3, POLYGON(LINESTRING(POINT(0,0), POINT(0,1), POINT(1,1), POINT(1,0), POINT(0,0))));

-- 计算距离
SELECT ST_Distance(
    POINT(116.404, 39.915),
    POINT(121.474, 31.230)
);

-- 判断包含关系
SELECT ST_Contains(
    POLYGON(LINESTRING(POINT(0,0), POINT(0,2), POINT(2,2), POINT(2,0), POINT(0,0))),
    POINT(1,1)
);

-- 获取边界
SELECT ST_Boundary(
    POLYGON(LINESTRING(POINT(0,0), POINT(0,1), POINT(1,1), POINT(1,0), POINT(0,0)))
);

-- MariaDB 10.7+ 原生UUID类型
CREATE TABLE uuid_table (
    id UUID DEFAULT UUID(),
    name VARCHAR(100)
);

INSERT INTO uuid_table (name) VALUES ('John');

SELECT * FROM uuid_table;
-- id: 550e8400-e29b-41d4-a716-446655440000

-- UUID函数
SELECT UUID();
SELECT UUID_SHORT();
SELECT SYS_GUID();  -- Oracle兼容

-- MariaDB 10.6及以下版本
CREATE TABLE uuid_compat (
    id CHAR(36) DEFAULT UUID(),
    name VARCHAR(100)
);

-- 或使用BINARY(16)存储
CREATE TABLE uuid_binary (
    id BINARY(16) DEFAULT UNHEX(REPLACE(UUID(), '-', '')),
    name VARCHAR(100)
);

-- 查询时转换
SELECT HEX(id) FROM uuid_binary;
SELECT UUID_FROM_BIN(id) FROM uuid_binary;  -- MariaDB 10.8+

-- 字符串转数字
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);