PostgreSQL

Appearance

PostgreSQL 标识符与关键字：数据库中的命名规则 ​

NOTE

标识符和关键字是 SQL 语言的基础构件，就像建筑中的砖块和钢筋，它们共同构成了数据库的操作指令和数据结构。理解它们的规则能避免许多常见错误！

🧩 什么是标识符和关键字？ ​

想象你正在建造一个数据库"城市"：

  • 标识符 = 建筑名称（学校, 医院, 公园）
  • 关键字 = 建筑规则（建造, 拆除, 扩建）
类型作用示例
标识符命名数据库对象users, order_date, product_price
关键字SQL 操作指令SELECT, CREATE, WHERE

快速识别技巧

当看到一个单词时问自己：它是描述数据（标识符）还是操作数据（关键字）？

sql
SELECT name FROM employees;  -- SELECT/FROM是关键字，name/employees是标识符

📜 标识符的核心规则 ​

基本命名规范 ​

  1. 开头字符：字母（a-z）或下划线（_）
  2. 后续字符：字母/数字/下划线(_)/美元符号($)
  3. 长度限制：≤63字节（默认）
sql
-- 合法标识符示例
valid_name
_user123
price_in_usd$

-- 非法标识符示例
123user     -- ✗ 数字开头
user-name   -- ✗ 包含连字符
order date  -- ✗ 包含空格

两种标识符类型对比 ​

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100)
sql
CREATE TABLE "Product-List" (
    "ID" SERIAL PRIMARY KEY,
    "产品名称" VARCHAR(100))

大小写处理规则 ​

写法实际存储是否区分大小写
table_nametable_name否
Table_Nametable_name否
"Table_Name"Table_Name是 ✅

WARNING

跨数据库警告：MySQL 的 Table_Name 会被存储为 Table_Name，而 PostgreSQL 会转为小写！保持风格统一可避免迁移问题。

🌍 Unicode 特殊字符处理 ​

当需要多语言支持时，使用 Unicode 转义：

sql
-- 创建包含中文的列名
CREATE TABLE U&"用户信息" (
    U&"用户\ID" SERIAL PRIMARY KEY,  -- 转义序列
    U&"注册日期" DATE
);

⚠️ 关键字使用注意事项 ​

关键字是 SQL 的保留指令，如误用会导致语法错误：

sql
-- 错误示例：使用关键字作为列名
CREATE TABLE orders (
    select INTEGER,  -- 'select'是关键字
    from TIMESTAMP   -- 'from'是关键字
);

-- 正确解决方案
CREATE TABLE orders (
    selection INTEGER,    -- 改用近义词
    order_from TIMESTAMP  -- 添加前缀
);

高危操作

虽然可以这样使用关键字，但极度不推荐：

sql
CREATE TABLE "select" (  
    "where" TEXT
);

这会导致后续查询必须始终加引号：
SELECT "where" FROM "select";

🚀 最佳实践指南 ​

命名三原则 ​

  1. 一致性：全项目统一命名风格（如蛇形命名user_id）
  2. 可读性：使用有意义的名称（order_date而非od）
  3. 避关键字：永远不要用关键字命名

大小写推荐方案 ​

sql
-- 推荐风格：关键字大写，标识符小写
SELECT user_name, order_date 
FROM customer_orders
WHERE total_price > 1000;

-- 不推荐：全大写或全小写
select username, orderdate from customerorders where totalprice>1000;

💼 实际业务场景案例 ​

场景1：电商平台商品表设计 ​

背景：需要创建包含特殊字符的商品分类表
问题：分类名含空格和连字符（如"Home & Garden"）

sql
-- 错误方案（不加引号）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    category name VARCHAR(50)  -- 语法错误
);

-- 正确方案（带引号标识符）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    "category name" VARCHAR(50)  
);

-- 查询示例
SELECT "category name" FROM categories WHERE id = 1;

效果：✅ 支持特殊字符命名 ✅ 保持数据可读性

场景2：国际化用户管理系统 ​

背景：需要支持多语言列名（中英文混合）
问题：直接使用中文列名导致迁移错误

sql
-- 创建表（使用Unicode转义）
CREATE TABLE U&"用户表" (
    U&"用户\ID" SERIAL PRIMARY KEY,
    U&"姓名" VARCHAR(50),
    U&"注册\日期" DATE
);

-- 等效于
CREATE TABLE "用户表" (
    "用户ID" SERIAL PRIMARY KEY,
    "姓名" VARCHAR(50),
    "注册日期" DATE
);

效果：✅ 支持多语言命名 ✅ 跨平台兼容

场景3：金融系统迁移陷阱 ​

背景：从Oracle迁移到PostgreSQL
问题：Oracle中大小写不敏感的Transaction表在PG中失效

sql
-- Oracle原始建表语句
CREATE TABLE Transaction (...)  -- Oracle存储为TRANSACTION

-- PostgreSQL错误迁移
CREATE TABLE Transaction (...)  -- PG存储为transaction!

-- 正确迁移方案
CREATE TABLE "Transaction" (...)  -- 保持原名称

效果：✅ 避免应用层修改 ✅ 无缝迁移

✅ 核心要点总结 ​

  1. 普通标识符：小写字母+下划线，无特殊字符（推荐✅）
  2. 带引号标识符：需区分大小写/含特殊字符时使用（谨慎使用⚠️）
  3. 关键字：永远不要用作标识符（危险❌）
  4. 最佳实践：关键字大写 + 标识符小写蛇形命名

TIP

忘记规则时记住这个黄金法则：
"当怀疑，引号保平安；关键字，避用最安全"