KingbaseES数据类型从基础CHAR到JSON/XML/几何类型完全指南

char [(size [byte | char])]

-- 按字符算：一个中文算1个字符，一个英文也算1个字符
create table test_char (col char(4 char));
insert into test_char values ('一二三四');   -- 没问题，刚好4个字符
insert into test_char values ('一二三四五'); -- 报错！超了1个字符

-- 按字节算：一个中文通常占3个字节，一个英文占1个字节
create table test_byte (col char(4 byte));
insert into test_byte values ('1234');  -- 没问题，4个ascii字符=4字节
insert into test_byte values ('一二三'); -- 报错！3个中文=9字节，远超4字节限制

varchar2(size [byte | char])

create table user_info (
    user_id   int,
    username  varchar2(50 char),    -- 最多50个字符
    email     varchar2(100 byte),   -- 最多100个字节
    bio       varchar2(500 char)    -- 个人简介，最多500个字符
);

insert into user_info values (1, '张三', 'zhangsan@example.com', '全栈开发工程师');

-- varchar2会原样存储，不会补空格
select username, length(username) from user_info;
-- 结果：张三 | 2（实际字符长度）

-- nchar：定长的unicode字符串
-- nvarchar2：变长的unicode字符串
create table multilang (
    label    nchar(20),            -- 固定20个unicode字符位
    content  nvarchar2(500 char)   -- 变长，最多500个unicode字符
);

-- 中英日文混合存储，完全没问题
insert into multilang values ('hello世界こんにちは', '多语言内容存储示例');

select label, content from multilang;

number [(precision [, scale])]

create table financial_records (
    record_id   number(10),        -- 10位整数，足够存id
    unit_price  number(8,2),       -- 8位有效数字、2位小数，如 999999.99
    total       number(12,2),      -- 12位有效数字、2位小数，适合存大额金额
    round_value number(12,-2)      -- 标度为负，自动舍入到百位
);

-- 实际插入数据
insert into financial_records values (100001, 29.90, 2990.00, 123456);
-- round_value 列存入123456，实际会被存为 123500（百位以下舍入）

select * from financial_records;

-- 四舍五入到3位有效数字
select cast(123.89 as number(3));      -- 输出：124

-- 保留2位小数
select cast(123.89 as number(5,2));    -- 输出：123.89

-- 保留1位小数，第2位小数四舍五入
select cast(123.89 as number(6,1));    -- 输出：123.9

-- 小数点后5位，适合存精度很高的数据
select cast(0.000127 as number(4,5));  -- 输出：0.00013

-- 超出精度直接报错
select cast(123.89 as number(3,2));
-- error: 超出精度范围

create table orders (
    order_id   int,            -- 订单编号，21亿以内够用了
    quantity   smallint,       -- 购买数量，3万多个也够了
    status     smallint        -- 状态码：0待付款 1已付款 2已发货 3已完成
);

insert into orders values (100001, 5, 0);
insert into orders values (100002, 120, 1);

select * from orders where quantity > 10;

-- float：precision为1~24时是单精度（real），25~53时是双精度（double precision）
-- binary_float：32位单精度，范围 1.17549e-38 ~ 3.40282e+38
-- binary_double：64位双精度，范围 2.22507e-308 ~ 1.79769e+308

create table sensor_data (
    sensor_id    int,
    temperature  binary_float,    -- 温度传感器读数
    pressure     binary_double,   -- 气压传感器读数
    recorded_at  timestamp
);

-- 浮点数的字面量写法
insert into sensor_data values (1, 36.5f, 101325.0d, current_timestamp);
insert into sensor_data values (2, -0.1f, 101200.5d, current_timestamp);

-- 查询浮点数据
select sensor_id, temperature, pressure from sensor_data;

-- 浮点数支持特殊值
select 'infinity'::float8;   -- 正无穷
select '-infinity'::float8;  -- 负无穷
select 'nan'::float8;        -- 不是数字

create table events (
    event_id   int,
    event_name varchar2(100),
    event_date date
);

-- 用to_date把字符串转成date
insert into events values (1, '项目启动', to_date('2024-12-01', 'yyyy-mm-dd'));
insert into events values (2, '年终总结', to_date('2024-12-31 14:00:00', 'yyyy-mm-dd hh24:mi:ss'));

-- 不指定时间的话，默认是午夜 00:00:00
select event_name, event_date from events;
-- 输出：
-- 项目启动 | 2024-12-01 00:00:00
-- 年终总结 | 2024-12-31 14:00:00

-- date之间可以直接做加减
select event_name, event_date - to_date('2024-12-01','yyyy-mm-dd') as days_diff
from events;
-- 输出：
-- 项目启动 | 0
-- 年终总结 | 30

timestamp [(fractional_seconds_precision)]  -- 小数秒位数，0~6，默认6

create table system_log (
    log_id    int,
    log_time  timestamp(3),     -- 精确到毫秒（3位小数）
    level     varchar2(10),
    message   varchar2(500)
);

-- 插入带毫秒精度的时间
insert into system_log values (
    1,
    to_timestamp('2024-01-01 09:30:53.123', 'yyyy-mm-dd hh24:mi:ss.ff3'),
    'info',
    '系统启动完成'
);

insert into system_log values (
    2,
    to_timestamp('2024-01-01 09:30:53.456', 'yyyy-mm-dd hh24:mi:ss.ff3'),
    'error',
    '数据库连接超时'
);

-- 精确到毫秒的查询
select log_id, log_time, level, message from system_log
where log_time > to_timestamp('2024-01-01 09:30:53.200', 'yyyy-mm-dd hh24:mi:ss.ff3');

-- 场景：跨国会议安排
create table global_meetings (
    meeting_id   int,
    topic        varchar2(200),
    start_time   timestamp with time zone,         -- 保留原始时区
    end_time     timestamp with local time zone     -- 自动转本地时区
);

-- 北京时间下午3点开会
insert into global_meetings values (
    1,
    '全球技术同步会',
    timestamp '2024-06-15 15:00:00 +08:00',
    timestamp '2024-06-15 17:00:00 +08:00'
);

-- 查询时能看到时区信息
select meeting_id, topic, start_time from global_meetings;
-- 输出：1 | 全球技术同步会 | 2024-06-15 15:00:00 +08:00

-- interval year to month：以"年+月"为单位
-- interval day to second：以"天+时+分+秒"为单位

-- 例子：2004年闰年2月29日，加4年后是哪天？
select to_date('29-feb-2004', 'dd-mon-yyyy') + to_yminterval('4-0') as result;
-- 输出：2008-02-29 00:00:00

-- 实际建表使用
create table project_schedule (
    project_name varchar2(100),
    start_time   timestamp,
    buffer_time  interval day(3) to second(0),   -- 缓冲时间，精确到天和秒
    duration     interval year to month            -- 项目周期，以年月为单位
);

insert into project_schedule values (
    'erp系统建设',
    to_timestamp('2024-03-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),
    '0 12:30:00',    -- 半天缓冲
    '1-6'            -- 1年6个月
);

select project_name,
       start_time,
       buffer_time,
       duration,
       start_time + buffer_time as adjusted_start
from project_schedule;

create table media_library (
    file_id     int,
    file_name   varchar2(200),
    file_type   varchar2(20),    -- image/audio/video
    file_size   int,             -- 文件大小（字节）
    file_data   blob             -- 实际文件内容
);

-- 插入一条记录（实际开发中通常通过程序写入blob数据）
insert into media_library (file_id, file_name, file_type, file_size, file_data)
values (1, 'banner.jpg', 'image', 204800, empty_blob());

create table knowledge_base (
    article_id  int primary key,
    title       varchar2(200),
    author      varchar2(50),
    content     clob,            -- 文章正文，几万字不在话下
    summary     nclob,           -- unicode格式的摘要
    created_at  date
);

insert into knowledge_base values (
    1,
    '数据库性能优化实战',
    '技术团队',
    '本文详细介绍了数据库性能优化的各个方面，包括索引设计、查询优化、缓存策略……（此处省略一万字）',
    '从索引到缓存，全方位讲解性能优化方法',
    to_date('2024-06-01', 'yyyy-mm-dd')
);

-- clob也能用like搜索
select title from knowledge_base where content like '%索引设计%';

-- 先创建一个目录对象（相当于给文件路径起个别名）
create directory doc_dir as '/data/documents';

-- 建表
create table external_docs (
    doc_id    int,
    doc_name  varchar2(200),
    doc_ref   bfile           -- 指向外部文件的引用
);

-- 用bfilename函数插入文件引用
insert into external_docs values (1, '年度报告', bfilename('doc_dir', 'annual_report.pdf'));
insert into external_docs values (2, '技术规范', bfilename('doc_dir', 'tech_spec.docx'));

-- 查询时看到的是引用信息，不是文件内容
select doc_id, doc_name from external_docs;

raw [(size)]     -- size范围1~32767字节
long raw         -- 跟raw一样，但不能指定长度

create table raw_data (
    id         int,
    bin_data   raw(1024),       -- 最多1024字节的二进制数据
    signature  raw(256)         -- 256字节的数字签名
);

-- 用hextoraw把十六进制字符串转成raw
insert into raw_data values (1, hextoraw('0a1b2c3d4e5f'), hextoraw('aabbccdd'));

-- 用rawtohex把raw转回十六进制查看
select id, rawtohex(bin_data) as hex_data from raw_data;
-- 输出：1 | 0a1b2c3d4e5f

-- 创建一张存json数据的表
create table api_data (
    id    int,
    jdoc  jsonb
);

-- 插入一条json数据
insert into api_data values (1, '{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "张三",
    "is_active": true,
    "score": 95.5,
    "tags": ["developer", "architect", "leader"]
}');

-- 再插一条
insert into api_data values (2, '{
    "guid": "7f3b2a01-4e9c-4d2f-b8a1-6234567890ab",
    "name": "李四",
    "is_active": false,
    "score": 88.0,
    "tags": ["designer", "leader"]
}');

-- 用 -> 提取json对象（返回json类型）
-- 用 ->> 提取json对象（返回文本）
select
    jdoc->>'name'   as name,
    jdoc->'score'   as score,
    jdoc->'is_active' as active
from api_data;

-- 输出：
--  name | score | active
-- ------+-------+--------
--  张三 | 95.5  | true
--  李四 | 88.0  | false

-- @> 包含查询：tags里有没有"leader"？
select jdoc->>'name' as name from api_data
where jdoc @> '{"tags":["leader"]}'::jsonb;
-- 输出：张三 和 李四 都有leader标签

-- ? 存在查询：有没有某个键？
select jdoc->>'name' as name from api_data
where jdoc ? 'is_active';
-- 输出：两条都有

-- ?| 任一存在：有没有tags或者email？
select jdoc->>'name' as name from api_data
where jdoc ?| array['tags', 'email'];

-- ?& 全部存在：是不是同时有name和score？
select jdoc->>'name' as name from api_data
where jdoc ?& array['name', 'score'];

-- 更复杂的嵌套数据
insert into api_data values (3, '{
    "name": "王五",
    "tags": [
        {"term": "backend", "level": "senior"},
        {"term": "cloud", "level": "expert"}
    ]
}');

-- 查找tags中包含特定term的文档
select jdoc->>'name' from api_data
where jdoc @> '{"tags":[{"term":"backend"}]}';

-- 通用gin索引，支持 @>、?、?&、?| 操作符
create index idx_jsonb on api_data using gin (jdoc);

-- jsonb_path_ops索引：只支持@>操作符，但更小更快
create index idx_jsonb_path on api_data using gin (jdoc jsonb_path_ops);

-- 针对特定键的表达式索引（最精准）
create index idx_jsonb_tags on api_data using gin ((jdoc -> 'tags'));

-- 这个查询会走 idx_jsonb_tags 索引
select jdoc->>'name', jdoc->'score' from api_data
where jdoc -> 'tags' ? 'leader';

-- 用 @@ 操作符配合jsonpath语法
select jdoc->>'name' from api_data
where jdoc @@ '$.tags[*] == "leader"';

-- 带条件的路径查询
select jdoc->>'name' from api_data
where jdoc @@ '$.score ? (@ > 90)';
-- 输出：张三（score=95.5）

create table xml_docs (
    doc_id   int,
    content  xml
);

-- 方式一：用xmlparse创建
insert into xml_docs values (1,
    xmlparse(document '<?xml version="1.0"?>
    <book>
        <title>数据库入门</title>
        <chapter id="1">基础概念</chapter>
        <chapter id="2">进阶技巧</chapter>
    </book>')
);

-- 方式二：直接类型转换
insert into xml_docs values (2, '<note><to>张三</to><body>明天开会</body></note>'::xml);

-- 查询xml内容
select doc_id, content from xml_docs;

-- 把xml转回字符串
select xmlserialize(document content as text) from xml_docs where doc_id = 1;

-- 判断是完整文档还是文档片段
select content is document as is_full_doc from xml_docs;

create table xml_orders (
    order_id int,
    order_data xmltype
);

-- 插入xml格式的订单数据
insert into xml_orders values (1, xmltype('<?xml version="1.0"?>
<order>
    <customer>李四</customer>
    <items>
        <item sku="a001">
            <name>键盘</name>
            <price>299</price>
            <qty>2</qty>
        </item>
        <item sku="a002">
            <name>鼠标</name>
            <price>89</price>
            <qty>1</qty>
        </item>
    </items>
    <total>687</total>
</order>'));

-- 使用xmltype的方法查询
select x.order_data from xml_orders x;

-- 建一张带几何字段的表
create table locations (
    name      varchar2(50),
    center    point,          -- 中心坐标
    area      polygon,        -- 区域多边形
    boundary  circle          -- 圆形边界
);

insert into locations values (
    '总部大楼',
    '(116.397, 39.908)',                                -- 一个点
    '((116.39, 39.90), (116.40, 39.90),
      (116.40, 39.91), (116.39, 39.91))',               -- 四边形
    '<(116.397, 39.908), 0.005>'                        -- 以点为圆心的圆
);

select name, center, area, boundary from locations;

-- 计算两点之间的距离
select point '(1,1)' <-> point '(4,5)' as distance;
-- 输出：5（勾股定理：√(3²+4²) = 5）

-- 判断一个点是否在矩形内
select box '((0,0),(2,2))' @> point '(1,1)' as is_inside;
-- 输出：true

select box '((0,0),(2,2))' @> point '(3,3)' as is_inside;
-- 输出：false

-- 计算圆的面积
select area(circle '<(0,0), 5>') as circle_area;
-- 输出：78.5398163397448（π × 5²）

-- 两个矩形是否重叠
select box '((0,0),(2,2))' && box '((1,1),(3,3))' as overlaps;
-- 输出：true

-- 计算两点之间的线段中点
select center(lseg '((0,0),(4,4))') as midpoint;
-- 输出：(2,2)

create table network_assets (
    asset_name  varchar2(50),
    ip_addr     inet,
    subnet      cidr
);

insert into network_assets values ('web-server-1', '192.168.1.100', '192.168.1.0/24');
insert into network_assets values ('db-server',    '10.0.0.5/16',    '10.0.0.0/16');
insert into network_assets values ('cache-server', '172.16.5.20',    '172.16.0.0/16');

-- 检查ip是否属于某个网段（<< 表示"包含在"）
select asset_name from network_assets where ip_addr << '192.168.1.0/24'::cidr;
-- 输出：web-server-1

-- 获取ip的网络部分
select network('192.168.1.100/24');
-- 输出：192.168.1.0/24

-- 获取主机部分
select host('192.168.1.100/24');
-- 输出：192.168.1.100

-- 判断两个网段是否重叠
select inet '192.168.1.0/24' && inet '192.168.1.128/25' as overlaps;
-- 输出：true

create table devices (
    device_id  int,
    device_name varchar2(50),
    mac        macaddr
);

-- 下面这几种写法都是同一个mac地址
insert into devices values (1, '服务器a', '08:00:2b:01:02:03');
insert into devices values (2, '服务器b', '08-00-2b-01-02-03');
insert into devices values (3, '交换机',  '08002b:010203');
insert into devices values (4, '路由器',  '0800.2b01.0203');
insert into devices values (5, '防火墙',  '08002b010203');

-- 查询时统一输出为冒号分隔格式
select device_id, device_name, mac from devices;
-- 输出全部显示为 08:00:2b:01:02:03

-- eui-64格式的mac地址（8字节）
insert into devices values (6, '无线ap', '08:00:2b:01:02:03:04:05'::macaddr8);

-- 把48位mac转成eui-64格式
select macaddr8_set7bit('08:00:2b:01:02:03');
-- 输出：0a:00:2b:ff:fe:01:02:03

-- 直接转tsvector：自动去重和排序
select 'a fat cat sat on a mat and ate a fat rat'::tsvector;
-- 输出：'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
-- 注意：重复的a和fat只出现一次

-- 用to_tsvector进行语言相关的处理（推荐）
select to_tsvector('english', 'the fat rats');
-- 输出：'fat':2 'rat':3
-- "the"被过滤（停用词），"fat"变成小写"fat"，"rats"变成词干"rat"
-- 冒号后面的数字是词在原文中的位置

-- 简单and查询
select 'fat & rat'::tsquery;
-- 输出：'fat' & 'rat'

-- 组合查询
select 'fat & (rat | cat)'::tsquery;
-- 输出：'fat' & ( 'rat' | 'cat' )

-- 排除查询
select 'fat & rat & !cat'::tsquery;
-- 输出：'fat' & 'rat' & !'cat'

-- 前缀匹配
select 'super:*'::tsquery;
-- 会匹配superman、supernatural等所有以super开头的词

-- 建表
create table articles (
    article_id  int,
    title       varchar2(200),
    body        text
);

insert into articles values (1, '数据库性能优化',
    '数据库性能优化是每个开发者的必修课。索引设计、查询优化、缓存策略都是关键技能。');
insert into articles values (2, 'python入门教程',
    'python是一门非常适合初学者的编程语言。它的语法简洁，社区活跃。');
insert into articles values (3, '分布式数据库架构',
    '分布式数据库通过数据分片和复制来提高系统的可用性和性能。');

-- 创建gin索引加速全文搜索
create index idx_articles_body on articles
using gin (to_tsvector('english', body));

-- 搜索：找出提到"database"和"performance"的文章
select title from articles
where to_tsvector('english', body) @@ to_tsquery('database & performance');

-- 搜索：找出提到"database"或"python"的文章
select title from articles
where to_tsvector('english', body) @@ to_tsquery('database | python');

-- 按相关度排序（使用ts_rank）
select title, ts_rank(to_tsvector('english', body), to_tsquery('database')) as rank
from articles
where to_tsvector('english', body) @@ to_tsquery('database')
order by rank desc;

-- 会议室预约系统
create table room_booking (
    room_id   int,
    booked_by varchar2(50),
    during    tsrange
);

insert into room_booking values
    (101, '张三', '[2025-01-15 09:00, 2025-01-15 10:00)'),   -- 9点到10点
    (101, '李四', '[2025-01-15 10:00, 2025-01-15 11:30)'),   -- 10点到11点半
    (102, '王五', '[2025-01-15 09:30, 2025-01-15 12:00)');   -- 9点半到12点

-- 检查101会议室有没有时间冲突
select booked_by from room_booking
where room_id = 101
  and during && '[2025-01-15 09:30, 2025-01-15 10:30)'::tsrange;
-- 输出：张三（他的9:00-10:00跟9:30-10:30有重叠）

-- 包含检查：15在不在[10,20)范围内？
select int4range(10, 20) @> 15;
-- 输出：true

-- 重叠检查：两个范围有没有交集？
select numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- 输出：true（11.1~22.2 跟 20.0~30.0 在20.0~22.2处重叠）

-- 取交集
select int4range(10, 20) * int4range(15, 25);
-- 输出：[15,20)

-- 取上界和下界
select lower(int4range(10, 20)), upper(int4range(10, 20));
-- 输出：10 | 20

-- 范围是否为空
select isempty(numrange(1, 5));
-- 输出：false

-- 创建一个浮点数范围类型
create type floatrange as range (
    subtype = float8,
    subtype_diff = float8mi
);

-- 直接使用
select '[1.234, 5.678]'::floatrange;
-- 输出：[1.234,5.678)

-- 也可以创建一个时间范围类型
create function time_subtype_diff(x time, y time) returns float8 as
$$ select extract(epoch from (x - y)) $$ language sql strict immutable;

create type timerange as range (
    subtype = time,
    subtype_diff = time_subtype_diff
);

-- 使用自定义时间范围
select '[11:10, 23:00]'::timerange;
-- 输出：[11:10:00,23:00:00)

-- 查看每一行的rowid
select rowid, employee_id, name from employees where employee_id <= 5;

-- 用rowid定位特定行（比用主键还快）
select * from employees where rowid = 'aaaacmaabaaaaeqaaa';

-- rowid支持比较操作符
select rowid, name from employees
where rowid > 'aaaacmaabaaaaeqaaa'
order by rowid;

-- 定义一个对象类型：地址
create type address_type as object (
    province   varchar2(50),
    city       varchar2(50),
    street     varchar2(200),
    post_code  char(6)
);

-- 在表中使用自定义类型
create table customers (
    customer_id  int,
    name         varchar2(50),
    home_addr    address_type
);

-- 可变数组（varray）：一组有序元素，需指定最大数量
-- 嵌套表（nested table）：一组无序元素