Spanner表操作 #

Spanner表结构
├── 表名
├── 列定义
│   ├── 列名
│   ├── 数据类型
│   ├── 是否允许NULL
│   └── 默认值
│
├── 主键
│   ├── 必须定义
│   ├── 可包含多列
│   └── 决定数据分布
│
└── 约束条件
    ├── 外键约束
    ├── 检查约束
    └── 交错关系

CREATE TABLE table_name (
    column1 data_type [NOT NULL] [DEFAULT default_value],
    column2 data_type [NOT NULL] [DEFAULT default_value],
    ...
) PRIMARY KEY (key_columns);

-- 用户表
CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    email STRING(255),
    age INT64,
    is_active BOOL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (user_id);

-- 产品表
CREATE TABLE products (
    product_id INT64 NOT NULL,
    name STRING(200) NOT NULL,
    description STRING(MAX),
    price FLOAT64 NOT NULL,
    stock INT64 DEFAULT 0,
    category STRING(50)
) PRIMARY KEY (product_id);

-- 订单表(复合主键)
CREATE TABLE orders (
    user_id INT64 NOT NULL,
    order_id INT64 NOT NULL,
    order_date DATE NOT NULL,
    total_amount FLOAT64,
    status STRING(20) DEFAULT 'pending'
) PRIMARY KEY (user_id, order_id);

-- 访问日志(时间戳作为主键一部分)
CREATE TABLE access_logs (
    user_id INT64 NOT NULL,
    access_time TIMESTAMP NOT NULL,
    action STRING(50),
    ip_address STRING(45)
) PRIMARY KEY (user_id, access_time);

CREATE TABLE posts (
    post_id INT64 NOT NULL,
    title STRING(200) NOT NULL,
    content STRING(MAX),
    status STRING(20) DEFAULT 'draft',
    view_count INT64 DEFAULT 0,
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP()),
    updated_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (post_id);

主键设计原则:
├── 唯一性: 每行必须有唯一主键
├── 不可变: 主键值不应被修改
├── 简洁性: 主键应尽量简短
├── 有序性: 有利于范围查询
└── 分散性: 避免热点问题

-- 不推荐: 单调递增主键会产生热点
CREATE TABLE bad_design (
    id INT64 NOT NULL,  -- 单调递增
    name STRING(100)
) PRIMARY KEY (id);

-- 推荐: 使用位反转或UUID分散写入
CREATE TABLE good_design (
    id INT64 NOT NULL,  -- 使用位反转
    name STRING(100)
) PRIMARY KEY (id);

-- 推荐: 使用复合主键分散
CREATE TABLE distributed_design (
    shard_id INT64 NOT NULL,  -- 分片ID
    id INT64 NOT NULL,
    name STRING(100)
) PRIMARY KEY (shard_id, id);

-- 模式1: 简单整数主键
CREATE TABLE simple (
    id INT64 NOT NULL,
    data STRING(100)
) PRIMARY KEY (id);

-- 模式2: 字符串主键
CREATE TABLE by_name (
    name STRING(100) NOT NULL,
    data STRING(100)
) PRIMARY KEY (name);

-- 模式3: 复合主键(层级关系)
CREATE TABLE hierarchical (
    parent_id INT64 NOT NULL,
    child_id INT64 NOT NULL,
    data STRING(100)
) PRIMARY KEY (parent_id, child_id);

-- 模式4: 时间戳主键(时序数据)
CREATE TABLE time_series (
    device_id INT64 NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    value FLOAT64
) PRIMARY KEY (device_id, timestamp);

交错表(Interleaved Table):
├── 子表数据物理存储在父表数据旁边
├── 提高父子表JOIN性能
├── 支持级联删除
└── 主键必须包含父表主键

-- 父表: 用户
CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    email STRING(255)
) PRIMARY KEY (user_id);

-- 子表: 订单(交错在users下)
CREATE TABLE orders (
    user_id INT64 NOT NULL,      -- 父表主键
    order_id INT64 NOT NULL,     -- 子表主键
    order_date DATE NOT NULL,
    total_amount FLOAT64
) PRIMARY KEY (user_id, order_id)
  INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- 孙表: 订单项(交错在orders下)
CREATE TABLE order_items (
    user_id INT64 NOT NULL,      -- 祖父表主键
    order_id INT64 NOT NULL,     -- 父表主键
    item_id INT64 NOT NULL,      -- 本表主键
    product_id INT64 NOT NULL,
    quantity INT64,
    price FLOAT64
) PRIMARY KEY (user_id, order_id, item_id)
  INTERLEAVE IN PARENT orders ON DELETE CASCADE;

交错层级结构:
users (Level 1)
├── user_id: 1
│   ├── orders (Level 2)
│   │   ├── order_id: 100
│   │   │   ├── order_items (Level 3)
│   │   │   │   ├── item_id: 1
│   │   │   │   └── item_id: 2
│   │   └── order_id: 101
│   └── orders (Level 2)
│       └── order_id: 102
└── user_id: 2
    └── orders (Level 2)
        └── order_id: 200

最大交错深度: 7层

-- ON DELETE CASCADE: 删除父行时自动删除子行
CREATE TABLE orders_cascade (
    user_id INT64 NOT NULL,
    order_id INT64 NOT NULL
) PRIMARY KEY (user_id, order_id)
  INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- ON DELETE NO ACTION: 删除父行时检查子行(默认)
CREATE TABLE orders_no_action (
    user_id INT64 NOT NULL,
    order_id INT64 NOT NULL
) PRIMARY KEY (user_id, order_id)
  INTERLEAVE IN PARENT users ON DELETE NO ACTION;

CREATE TABLE products (
    product_id INT64 NOT NULL,      -- 必须有值
    name STRING(100) NOT NULL,      -- 必须有值
    description STRING(MAX),        -- 可以为NULL
    price FLOAT64                   -- 可以为NULL
) PRIMARY KEY (product_id);

CREATE TABLE products (
    product_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    price FLOAT64 NOT NULL,
    stock INT64 DEFAULT 0,
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_stock_non_negative CHECK (stock >= 0)
) PRIMARY KEY (product_id);

-- 添加CHECK约束
ALTER TABLE products ADD CONSTRAINT chk_name_length 
    CHECK (LENGTH(name) >= 2);

-- 删除CHECK约束
ALTER TABLE products DROP CONSTRAINT chk_name_length;

-- 创建主表
CREATE TABLE departments (
    dept_id INT64 NOT NULL,
    dept_name STRING(100) NOT NULL
) PRIMARY KEY (dept_id);

-- 创建从表(带外键)
CREATE TABLE employees (
    emp_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    dept_id INT64 NOT NULL,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) 
        REFERENCES departments (dept_id)
) PRIMARY KEY (emp_id);

-- 外键选项
CREATE TABLE employees_full (
    emp_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    dept_id INT64,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) 
        REFERENCES departments (dept_id)
        ON DELETE SET NULL  -- 删除时设为NULL
) PRIMARY KEY (emp_id);

-- 通过唯一索引实现唯一约束
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 复合唯一约束
CREATE UNIQUE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 添加单列
ALTER TABLE users ADD COLUMN phone STRING(20);

-- 添加多列
ALTER TABLE users 
    ADD COLUMN address STRING(200),
    ADD COLUMN city STRING(50);

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN country STRING(50) DEFAULT 'USA';

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 注意: 删除列是异步操作
-- 删除后列数据仍占用存储空间，直到垃圾回收

-- Spanner不支持直接修改列类型
-- 需要创建新列、迁移数据、删除旧列

-- 步骤1: 添加新列
ALTER TABLE users ADD COLUMN name_new STRING(200);

-- 步骤2: 迁移数据
UPDATE users SET name_new = name;

-- 步骤3: 删除旧列
ALTER TABLE users DROP COLUMN name;

-- 步骤4: 重命名(通过创建新列)
ALTER TABLE users ADD COLUMN name STRING(200);
UPDATE users SET name = name_new;
ALTER TABLE users DROP COLUMN name_new;

-- 添加CHECK约束
ALTER TABLE products ADD CONSTRAINT chk_price 
    CHECK (price >= 0);

-- 添加外键约束
ALTER TABLE orders ADD CONSTRAINT fk_user 
    FOREIGN KEY (user_id) REFERENCES users(user_id);

-- 删除约束
ALTER TABLE orders DROP CONSTRAINT fk_user;

-- 删除表
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

删除表注意事项:
├── 删除操作不可逆
├── 有交错子表时需要先删除子表
├── 有外键引用时需要先删除约束
├── 删除后数据仍占用存储空间(垃圾回收)
└── 建议先备份重要数据

-- 错误顺序: 先删除父表会失败
DROP TABLE users;  -- ERROR: 有交错子表

-- 正确顺序: 先删除子表
DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE users;

CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100)
) PRIMARY KEY (user_id)
OPTIONS (
    allow_commit_timestamp = true  -- 允许提交时间戳
);

-- 启用提交时间戳
CREATE TABLE logs (
    log_id INT64 NOT NULL,
    message STRING(MAX),
    commit_ts TIMESTAMP OPTIONS (allow_commit_timestamp = true)
) PRIMARY KEY (log_id);

-- 插入时使用提交时间戳
INSERT INTO logs (log_id, message, commit_ts)
VALUES (1, 'Test message', PENDING_COMMIT_TIMESTAMP());

-- 创建临时表(会话级别)
CREATE TEMP TABLE temp_results (
    id INT64 NOT NULL,
    value STRING(100)
) PRIMARY KEY (id);

-- 临时表在会话结束后自动删除

-- 在存储过程中使用临时表
CREATE TEMP TABLE temp_orders AS
SELECT * FROM orders WHERE order_date = CURRENT_DATE();

-- 对临时表进行操作
SELECT * FROM temp_orders WHERE total_amount > 100;

-- 查看所有表
SELECT table_name, table_type
FROM INFORMATION_SCHEMA.TABLES;

-- 查看列信息
SELECT column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'users';

-- 查看索引信息
SELECT index_name, table_name
FROM INFORMATION_SCHEMA.INDEXES;

-- 查看外键信息
SELECT constraint_name, table_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = 'FOREIGN KEY';

-- 查看表统计
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATS;

-- 查看列统计
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATS;

-- 查看索引统计
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATS;

主键设计建议:
├── 使用有意义的业务键
├── 避免单调递增(防止热点)
├── 考虑查询模式
├── 保持主键简短
└── 考虑使用位反转

交错表使用场景:
├── 父子关系明确
├── 经常一起查询
├── 需要级联删除
└── 层级不超过7层

不适合交错表:
├── 多父关系
├── 独立查询为主
└── 需要灵活关联

列设计建议:
├── 选择合适的数据类型
├── 合理设置字符串长度
├── 使用NOT NULL约束
├── 设置合理的默认值
└── 避免过多列(建议<100)

1. 唯一性
   └── 每行必须有唯一标识

2. 稳定性
   └── 主键值不应改变

3. 分散性
   └── 避免写入热点

4. 简洁性
   └── 主键应尽量简短

5. 有序性
   └── 支持范围查询