SQL详解之DDL - 数据库定义语言完整指南

引言

在日常的数据库设计或重构中，你有没有遇到过这种情况？MySQL敲惯了AUTO_INCREMENT，换PostgreSQL直接报错；updated_at想自动更新，MySQL一行搞定，PostgreSQL还得写触发器？今天一篇讲透DDL核心命令，对比两大主流关系型库的实用差异，附开发中常用的代码示例。

1. DDL核心基础

DDL（Data Definition Language）是SQL中定义/修改数据库结构的子集，操作对象包括数据库、表、索引、视图等，执行后自动提交无法回滚（TRUNCATE在部分库支持例外）。

语言类型主要命令核心用途事务特性
DDLCREATE/ALTER/DROP/TRUNCATE建/改/删结构自动提交
DMLINSERT/UPDATE/DELETE操作数据需手动提交

2. 数据库操作

2.1 创建数据库

-- 创建数据库（指定字符集，避免中文乱码必加！）
CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 查看/切换数据库
SHOW DATABASES;
USE company_db;
-- 创建数据库（需指定所有者和编码）
CREATE DATABASE company_db
WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

-- 查看/切换数据库（psql客户端命令）
\l
\c company_db;

2.2 删除数据库

-- 直接删除（安全起见加IF EXISTS）
DROP DATABASE IF EXISTS old_database;
-- 【敲黑板！】需先终止所有连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'old_database' AND pid <> pg_backend_pid();

-- 再删除
DROP DATABASE IF EXISTS old_database;

3. 表操作

3.1 创建表（核心差异对比）

自增主键 & 自动更新时间戳

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
    first_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 自动更新
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB; -- 必须指定InnoDB（支持事务、外键）
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY, -- 自增主键（等价int自增）
    first_name VARCHAR(50) NOT NULL,
    salary NUMERIC(10,2) CHECK (salary > 0), -- NUMERIC和DECIMAL通用
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- 带时区更安全
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    department_id INTEGER,
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL
);

-- PostgreSQL需单独写触发器实现自动更新
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_employees_updated_at 
    BEFORE UPDATE ON employees 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

3.2 修改表结构

-- 1. 添加列
ALTER TABLE employees ADD COLUMN mobile VARCHAR(20);
-- 2. 修改列类型 + 重命名列（MODIFY改类型，CHANGE改类型+重命名）
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2), CHANGE COLUMN mobile phone VARCHAR(20);
-- 3. 删除列/约束
ALTER TABLE employees DROP COLUMN phone, DROP INDEX idx_name;
-- 4. 重命名表
ALTER TABLE employees RENAME TO staff;
-- 1. 添加列
ALTER TABLE employees ADD COLUMN mobile VARCHAR(20);
-- 2. 修改列类型 + 重命名列（分开操作！）
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);
ALTER TABLE employees RENAME COLUMN mobile TO phone;
-- 3. 删除列/约束（约束需通过名称DROP）
ALTER TABLE employees DROP COLUMN phone, DROP CONSTRAINT fk_department;
-- 4. 重命名表
ALTER TABLE employees RENAME TO staff;

3.3 删除表

DROP TABLE IF EXISTS employees;
-- RESTRICT：有依赖则不删（默认）；CASCADE：连依赖对象一起删
DROP TABLE IF EXISTS employees CASCADE;

4. 索引操作（PostgreSQL特色更突出）

4.1 基础索引

-- 普通/唯一索引
CREATE INDEX idx_last_name ON employees(last_name);
CREATE UNIQUE INDEX idx_employee_email ON employees(email);
-- 普通/唯一索引
CREATE INDEX idx_last_name ON employees(last_name);
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- 【特色1】部分索引：只索引满足条件的行（节省空间+提升速度）
CREATE INDEX idx_active_employees ON employees(employee_id) WHERE status = 'active';
-- 【特色2】表达式索引：索引函数处理后的值
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));
-- 【特色3】并发创建索引：不锁定大表
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column_name);

4.2 删除索引

DROP INDEX idx_last_name ON employees;
DROP INDEX IF EXISTS idx_last_name;

5. 视图操作（PostgreSQL物化视图是亮点）

5.1 普通视图（两者通用）

-- 创建简单视图（简化常用查询）
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE status = 'active';

-- 修改视图
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE status = 'active';

5.2 PostgreSQL特色：物化视图

普通视图是“虚拟表”，每次查询都重新执行底层SQL；物化视图是“物理表”，会提前存储结果，适合读多写少的统计场景。

-- 创建物化视图
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as total_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- 刷新物化视图（数据更新后手动/定时执行）
REFRESH MATERIALIZED VIEW monthly_sales_summary;

6. 约束操作

6.1 核心约束对比

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending' -- ENUM：但修改选项麻烦
);
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'cancelled')) -- CHECK更灵活
);

7. DDL最佳实践

7.1 命名规范

-- 好的命名：小写下划线、约束前缀清晰
CREATE TABLE user_accounts (
    account_id SERIAL PRIMARY KEY,
    CONSTRAINT fk_user_orders FOREIGN KEY (user_id) REFERENCES users(user_id),
    CONSTRAINT chk_positive_amount CHECK (amount > 0)
);

7.2 数据类型避坑

  • 金额：用DECIMAL/NUMERIC（别用FLOAT/DOUBLE，会有精度丢失）
  • 时间：PostgreSQL用TIMESTAMP WITH TIME ZONE，MySQL用TIMESTAMP（DATETIME不存时区）
  • 文本：固定长度用VARCHAR(n)，长度不定用TEXT

7.3 安全+性能

  • 生产环境DDL前务必先备份
  • 大表修改结构选维护窗口
  • PostgreSQL用CREATE INDEX CONCURRENTLY避免锁表
  • 定期清理无用索引

8. MySQL与PostgreSQL DDL差异速查

特性MySQLPostgreSQL
自增主键AUTO_INCREMENTSERIAL
自动更新时间戳ON UPDATE CURRENT_TIMESTAMP需写触发器
分区表直接PARTITION BYPARTITION BY + 子表PARTITION OF
物化视图不支持支持（需手动刷新）
并发DDL有限CREATE INDEX CONCURRENTLY
枚举类型ENUM（修改麻烦）CHECK + VARCHAR（更灵活）
时间类型带时区TIMESTAMP（存，但默认本地）TIMESTAMP WITH TIME ZONE

相关教程

执行DDL前，先在测试环境1:1还原验证，同时备份核心数据；大表变更选凌晨低峰期。

总结

DDL是数据库设计的核心，MySQL语法简单、适合快速开发，PostgreSQL功能更丰富、标准兼容性更好。掌握两大库的差异，能帮你在不同场景下高效设计表结构、避免踩坑。