CrateDB数据操作指南：增删改查与导入导出详解-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_00118/article/details/148548598

CrateDB数据操作指南：增删改查与导入导出详解

crate CrateDB is a distributed and scalable SQL database for storing and analyzing massive amounts of data in near real-time, even with complex queries. It is PostgreSQL-compatible, and based on Lucene. 项目地址: https://gitcode.com/gh_mirrors/cr/crate

概述

本文全面介绍如何在CrateDB数据库中进行数据操作(Data Manipulation Language, DML)，包括数据插入、更新、删除以及导入导出等核心操作。CrateDB作为一款分布式SQL数据库，其DML操作既保留了传统SQL的易用性，又针对分布式环境进行了优化。

数据插入操作

基础插入语句

在CrateDB中使用标准SQL的INSERT语句插入数据：

INSERT INTO locations (id, date, description, kind, name, position) 
VALUES (
  '14',
  '2013-09-12T21:43:59.000Z',
  'Blagulon Kappa是警察的原生星球',
  'Planet',
  'Blagulon Kappa',
  7
);

注意事项：

列顺序应与表定义时的顺序一致
所有数据都会进行类型校验和约束检查
单条语句失败会整体回滚

批量插入

CrateDB支持高效的批量插入，显著减少网络往返：

INSERT INTO locations (id, date, description, kind, name, position) VALUES
('16', '2013-09-14T21:43:59.000Z', '描述1', 'Planet', '星球1', 19),
('17', '2013-09-13T16:43:59.000Z', '描述2', 'Planet', '星球2', 10);

特殊列处理

对于生成列(GENERATED)和默认值列(DEFAULT)，插入时可省略：

CREATE TABLE debit_card (
  owner text,
  num_part1 integer,
  num_part2 integer,
  check_sum integer GENERATED ALWAYS AS ((num_part1 + num_part2) * 42),
  "user" text DEFAULT 'crate'
);

-- 省略生成列和默认列
INSERT INTO debit_card (owner, num_part1, num_part2) 
VALUES ('Zaphod Beeblebrox', 1234, 5678);

查询插入数据

CrateDB支持将查询结果直接插入到表中，适用于数据迁移和表结构调整：

-- 创建新表
CREATE TABLE locations2 (
    id text primary key,
    name text,
    date timestamp with time zone,
    kind text,
    position smallint,  -- 修改了数据类型
    description text
);

-- 从查询插入
INSERT INTO locations2 (id, name, date, kind, position, description)
(SELECT id, name, date, kind, position, description
 FROM locations
 WHERE position < 10);

重要提示：

查询插入时失败的行会被静默跳过
不支持在子查询中使用LIMIT/OFFSET/ORDER BY

更新插入(Upsert)

CrateDB通过ON CONFLICT DO UPDATE实现原子化的"存在则更新，不存在则插入"：

-- 基础用法
INSERT INTO uservisits (id, name, visits, last_visit) 
VALUES (0, 'Ford', 1, '2015-01-12') 
ON CONFLICT (id) DO UPDATE SET visits = visits + 1;

-- 使用excluded引用新值
INSERT INTO uservisits (id, name, visits, last_visit) VALUES
(0, 'Ford', 2, '2016-01-13'),
(1, 'Trillian', 5, '2016-01-15')
ON CONFLICT (id) DO UPDATE SET
    visits = visits + excluded.visits,
    last_visit = excluded.last_visit;

数据更新

使用标准SQL的UPDATE语句更新数据：

-- 基础更新
UPDATE locations SET description = '更新后的描述' WHERE name = 'Bartledan';

-- 更新嵌套对象
UPDATE locations SET inhabitants['name'] = 'Human' WHERE name = 'Bartledan';

-- 引用原值计算
UPDATE locations SET position = position + 1 WHERE position < 3;

并发更新：CrateDB内置了版本冲突检测和自动重试机制，确保并发更新时的数据一致性。

数据删除

使用DELETE语句删除数据：

DELETE FROM locations WHERE position > 3;

数据导入导出

数据导入

CrateDB支持通过COPY FROM从文件导入数据，支持JSON和CSV格式：

JSON格式示例：

{"id": 1, "quote": "不要恐慌"}
{"id": 2, "quote": "Ford，你正在变成一只企鹅。快停下。"}

CSV格式示例：

id,quote
1,"不要恐慌"
2,"Ford，你正在变成一只企鹅。快停下。"

导入命令示例：

-- 导入单个文件
COPY quotes FROM 'file:///tmp/import_data/quotes.json';

-- 使用通配符导入多个文件
COPY quotes FROM '/tmp/import_data/*' WITH (bulk_size = 4);

-- 获取导入摘要
COPY locations FROM '/tmp/import_data/locations*.json' RETURN SUMMARY;

重要注意事项：