OceanBase 表创建与修改

表创建语法

基本表创建

-- 创建基本表
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
    columnN datatype constraint
);

-- 示例：创建用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

带分区的表创建

-- 创建范围分区表
CREATE TABLE sales (
    id BIGINT PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region VARCHAR(20)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
    PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
    PARTITION p202303 VALUES LESS THAN ('2023-04-01')
);

-- 创建哈希分区表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
)
PARTITION BY HASH (customer_id) PARTITIONS 8;

带索引的表创建

-- 创建表时同时创建索引
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    INDEX idx_category (category),
    INDEX idx_price (price),
    UNIQUE INDEX idx_name (name)
);

表修改语法

添加列

-- 向表中添加列
ALTER TABLE table_name ADD COLUMN column_name datatype constraint;

-- 示例：向用户表添加年龄列
ALTER TABLE users ADD COLUMN age INT;

-- 向表中添加多个列
ALTER TABLE users ADD COLUMN (
    phone VARCHAR(20),
    address VARCHAR(255)
);

修改列

-- 修改列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype constraint;

-- 示例：修改用户表中年龄列的数据类型
ALTER TABLE users MODIFY COLUMN age TINYINT;

-- 修改列的默认值
ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;

-- 删除列的默认值
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;

删除列

-- 删除表中的列
ALTER TABLE table_name DROP COLUMN column_name;

-- 示例：删除用户表中的地址列
ALTER TABLE users DROP COLUMN address;

-- 删除多个列
ALTER TABLE users DROP COLUMN phone, DROP COLUMN age;

修改表名

-- 修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 示例：将用户表重命名为 customers
ALTER TABLE users RENAME TO customers;

添加约束

-- 添加主键约束
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 添加唯一约束
ALTER TABLE table_name ADD UNIQUE (column_name);

-- 添加外键约束
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 添加检查约束
ALTER TABLE users ADD CHECK (age >= 18);

删除约束

-- 删除主键约束
ALTER TABLE table_name DROP PRIMARY KEY;

-- 删除唯一约束
ALTER TABLE table_name DROP INDEX index_name;

-- 删除外键约束
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

-- 删除检查约束
ALTER TABLE table_name DROP CHECK constraint_name;

表创建与修改最佳实践

表设计原则

1. 合理选择数据类型：根据实际数据长度和范围选择合适的数据类型
2. 设计合适的主键：使用自增列或业务主键，避免复合主键过复杂
3. 合理设计索引：根据查询需求设计索引，避免过多索引
4. 考虑分区策略：对于大数据量的表，合理使用分区表
5. 使用合适的字符集：根据业务需求选择合适的字符集，如 UTF8MB4

表创建建议

1. 先设计后创建：在创建表之前，先进行详细的表结构设计
2. 使用命名规范：表名和列名使用清晰的命名规范，便于理解和维护
3. 添加必要的约束：添加主键、唯一约束、非空约束等，确保数据完整性
4. 考虑未来扩展性：预留必要的扩展字段，便于未来业务发展
5. 测试表结构：在正式环境创建表之前，先在测试环境进行测试

表修改建议

1. 避开业务高峰期：在业务低峰期进行表结构修改，减少对业务的影响
2. 备份数据：在修改表结构之前，先备份相关数据
3. 分步骤修改：对于复杂的表结构修改，分多个步骤进行
4. 监控修改过程：在修改表结构过程中，密切监控数据库性能
5. 测试修改结果：修改完成后，测试表结构和相关功能是否正常

表信息查询

查看表结构

-- 查看表结构
DESCRIBE table_name;

-- 查看表的详细信息
SHOW CREATE TABLE table_name;

-- 查看表的列信息
SELECT * FROM information_schema.columns WHERE table_name = 'table_name';

查看表索引

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看表的索引信息
SELECT * FROM information_schema.statistics WHERE table_name = 'table_name';

查看表分区

-- 查看表的分区信息
SHOW CREATE TABLE table_name;

-- 查看表的分区情况
SELECT * FROM information_schema.partitions WHERE table_name = 'table_name';

常见问题（FAQ）

Q1: 如何快速复制一张表的结构？

A1: 可以使用以下语法快速复制一张表的结构：

-- 复制表结构（不包括数据）
CREATE TABLE new_table LIKE old_table;

-- 复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM old_table;

-- 复制表结构和部分数据
CREATE TABLE new_table AS SELECT * FROM old_table WHERE condition;

Q2: 表结构修改会锁表吗？

A2: 是的，表结构修改会锁定表，影响表的读写操作。OceanBase 支持在线 DDL，但某些修改操作仍然会锁定表。建议在业务低峰期进行表结构修改，并合理设置 DDL 相关参数。

Q3: 如何查看表结构修改的进度？

A3: 可以通过以下方式查看表结构修改的进度：

-- 查看当前执行的 DDL 任务
SELECT * FROM oceanbase.GV$OB_DDL_PROGRESS;

-- 查看 DDL 历史记录
SELECT * FROM oceanbase.DBA_OB_DDL_HISTORY ORDER BY gmt_create DESC;

Q4: 如何优化大表的表结构修改？

A4: 对于大表的表结构修改，可以采取以下优化措施：

1. 使用 ONLINE DDL 方式
2. 设置合理的 DDL 并行度
3. 避开业务高峰期
4. 考虑使用影子表迁移
5. 合理设计修改步骤

-- 使用 ONLINE DDL 修改表结构
ALTER TABLE table_name ADD COLUMN column_name datatype constraint ONLINE;

Q5: 如何处理表结构修改失败？

A5: 表结构修改失败时，可以尝试以下操作：

1. 查看错误信息，分析失败原因
2. 检查表的状态和锁情况
3. 重试修改操作
4. 如果是大表，可以考虑分批次修改
5. 必要时联系 OceanBase 技术支持

-- 查看表的状态
SELECT * FROM oceanbase.DBA_OB_TABLES WHERE table_name = 'table_name';

-- 查看表的锁情况
SELECT * FROM oceanbase.GV$OB_LOCK_WAITSTAT WHERE table_name = 'table_name';