YugabyteDB YSQL数据操作全面指南-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_00310/article/details/148440716

YugabyteDB YSQL数据操作全面指南

yugabyte-db yugabyte/yugabyte-db: 是 YugaByte DB 的官方仓库，一个高性能、高可扩展、分布式的 SQL 数据库，支持 PostgreSQL 兼容性。适合对分布式数据库、SQL 数据库和云原生应用的开发者。项目地址: https://gitcode.com/gh_mirrors/yu/yugabyte-db

概述

在分布式数据库YugabyteDB中，数据操作是日常开发中最基础也最重要的功能之一。本文将深入讲解如何使用YSQL（YugabyteDB的PostgreSQL兼容层）进行高效的数据操作，包括数据插入、更新、删除等核心操作，以及一些高级特性。

基础数据操作

数据插入操作

在YSQL中，插入数据主要通过INSERT语句实现。假设我们有一个员工表：

CREATE TABLE employees (
    employee_no integer,
    name text,
    department text
);

基本插入方式有两种：

按列顺序插入（需了解表结构）：

INSERT INTO employees VALUES (1, 'John Smith', 'Marketing');

明确指定列名插入（更安全可靠）：

INSERT INTO employees (employee_no, name, department)
VALUES (1, 'John Smith', 'Marketing');

批量插入可以显著提高效率：

INSERT INTO employees VALUES
(1, 'John Smith', 'Marketing'),
(2, 'Bette Davis', 'Sales'),
(3, 'Lucille Ball', 'Operations');

默认值处理

当某些列值未知时，可以使用默认值：

-- 方式1：省略列
INSERT INTO employees (employee_no, name) VALUES (1, 'John Smith');

-- 方式2：显式使用DEFAULT
INSERT INTO employees (employee_no, name, department)
VALUES (1, 'John Smith', DEFAULT);

高级数据操作

Upsert操作

Upsert是"update or insert"的缩写，在YSQL中通过INSERT ON CONFLICT实现：

-- 基础表结构
CREATE TABLE employees (
    employee_no integer PRIMARY KEY,
    name text UNIQUE,
    department text NOT NULL
);

-- Upsert示例：当name冲突时更新department
INSERT INTO employees (employee_no, name, department)
VALUES (1, 'John Smith', 'Sales')
ON CONFLICT (name)
DO UPDATE SET department = EXCLUDED.department || ';' || employees.department;

自动递增列

使用序列(sequence)实现自动递增主键：

-- 简单方式（使用serial类型）
CREATE TABLE employees2 (employee_no serial, name text, department text);

-- 高级方式（自定义序列）
CREATE SEQUENCE sec_employees_employee_no
START 1
CACHE 1000;

CREATE TABLE employees (
    employee_no integer DEFAULT nextval('sec_employees_employee_no') NOT NULL,
    name text,
    department text
);

数据更新与删除

更新数据

基本更新语法：

-- 更新所有行
UPDATE employees SET department = 'Sales';

-- 条件更新
UPDATE employees SET employee_no = 7 WHERE employee_no = 3;

-- 多列更新
UPDATE employees SET employee_no = 2, name = 'Lee Warren' WHERE employee_no = 5;

删除数据

删除操作同样支持条件筛选：

-- 条件删除
DELETE FROM employees WHERE department = 'Sales';

-- 清空表
DELETE FROM employees;

数据导入导出

从文件导入数据

使用COPY FROM命令：

COPY employees FROM '/path/to/employees.txt' DELIMITER ',' CSV HEADER;

导出数据到文件

使用COPY TO命令：

COPY employees TO '/path/to/employees.txt' DELIMITER ',';

数据库备份与恢复

备份单个数据库

ysql_dump mydb > mydb.sql

恢复数据库

\i mydb.sql

高级特性

自动时间戳

记录数据创建和修改时间：

-- 创建时间
CREATE TABLE employees (
    employee_no integer NOT NULL,
    name text,
    department text,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 更新时间（需要触发器）
CREATE OR REPLACE FUNCTION trigger_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE trigger_timestamp();

获取修改后的数据

使用RETURNING子句获取操作后的数据：

-- 插入后返回
INSERT INTO employees (name, department) 
VALUES ('John Smith', 'Sales')
RETURNING *;

-- 更新后返回
UPDATE employees SET employee_no = employee_no + 1
WHERE employee_no = 1
RETURNING name, employee_no AS new_employee_no;

-- 删除后返回
DELETE FROM employees WHERE department = 'Sales' RETURNING *;

约束管理

NOT NULL约束

-- 创建表时添加
CREATE TABLE employees (
    employee_no integer NOT NULL,
    name text NOT NULL,
    department text
);

-- 后期添加
ALTER TABLE employees
ALTER COLUMN department SET NOT NULL;

延迟约束检查

BEGIN;
SET CONSTRAINTS name DEFERRED;
...
COMMIT;

-- 或者创建时指定
CREATE TABLE employees (
    employee_no integer,
    name text UNIQUE
    DEFERRABLE INITIALLY DEFERRED
);