
建库建表不是敲命令,是提前排雷
导语 很多人学 MySQL 是从 CREATE TABLE 开始的,但真正决定一个库后面好不好运维的,恰恰是建库建表那几分钟做的几个决策:字符集选哪个、主键用什么类型、存储引擎显不
在人大金仓(KingbaseES)数据库日常运维过程中,表的增删改查是最基础也是最常用的操作。
与 MySQL 不同,人大金仓采用 Schema(模式)组织数据库对象。同一个数据库中可以创建多个 Schema,不同 Schema 下可以存在相同名称的表。
例如:
数据库:TEST
│
├── public
│ ├── user_info
│ └── order_info
│
├── app
│ ├── user_info
│ └── product_info
│
└── backup
├── user_info
└── order_info
上面的结构中,public、app、backup 是三个不同的 Schema,它们都可以拥有一个名为 user_info 的表。
因此,在操作人大金仓数据库时,除了掌握普通的 INSERT、UPDATE、DELETE、SELECT 操作,还需要了解 Schema 的使用方式,以及如何跨 Schema 访问表。
本文使用以下环境作为演示:
| 配置项 | 示例 | | ---------- | ------ | | 数据库名称 | TEST | | Schema 1 | public | | Schema 2 | app | | Schema 3 | backup | | 用户 | system |
SELECT current_database();
查看当前用户:
SELECT current_user;
查看当前 Schema:
SELECT current_schema();
查看当前搜索路径:
SHOW search_path;
通过 KSQL 命令查看:
\dn
也可以通过 SQL 查询:
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;
创建一个名为 app 的 Schema:
CREATE SCHEMA app;
指定 Schema 所有者:
CREATE SCHEMA app AUTHORIZATION system;
如果 Schema 已经存在,可以使用:
CREATE SCHEMA IF NOT EXISTS app;
删除空 Schema:
DROP SCHEMA app;
如果 Schema 下存在表、视图等对象,直接删除可能会失败。
如果确定需要连同内部对象一起删除:
DROP SCHEMA app CASCADE;
注意:CASCADE 会同时删除该 Schema 内的对象,以及依赖这些对象的其他数据库对象。生产环境执行前必须确认影响范围。
可以通过设置 search_path 来调整当前会话默认搜索的 Schema:
SET search_path TO app, public;
查看:
SHOW search_path;
之后执行:
SELECT * FROM user_info;
数据库会按照 search_path 的顺序查找 user_info。
也可以直接使用完整名称:
SELECT * FROM app.user_info;
推荐在生产环境中操作时尽量使用 Schema.表名 的完整形式,避免操作到其他 Schema 下的同名表。
在 public Schema 下创建 user_info 表:
CREATE TABLE public.user_info (
id INTEGER,
username VARCHAR(100),
age INTEGER,
email VARCHAR(200),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
查看表结构:
\d public.user_info
查看表数据:
SELECT * FROM public.user_info;
CREATE TABLE public.user_info (
id INTEGER PRIMARY KEY,
username VARCHAR(100) NOT NULL,
age INTEGER,
email VARCHAR(200),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
其中:
使用 GENERATED AS IDENTITY:
CREATE TABLE public.order_info (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no VARCHAR(100),
amount NUMERIC(12,2),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
如果需要允许显式插入 ID,可以使用:
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
创建表时直接指定 Schema:
CREATE TABLE app.user_info (
id INTEGER PRIMARY KEY,
username VARCHAR(100),
age INTEGER
);
即使当前 search_path 是 public,也可以明确指定将表创建到 app Schema。
只复制表结构,不复制数据:
CREATE TABLE backup.user_info
(LIKE public.user_info INCLUDING ALL);
如果目标 Schema 不存在,需要先创建:
CREATE SCHEMA IF NOT EXISTS backup;
复制表结构和数据:
CREATE TABLE backup.user_info AS
SELECT *
FROM public.user_info;
需要注意,CREATE TABLE AS 主要复制查询结果及列定义,不等同于完整复制原表的索引、主键、约束和其他对象定义。
SELECT * FROM public.user_info;
只查询指定字段:
SELECT id, username, age
FROM public.user_info;
SELECT *
FROM public.user_info
WHERE id = 1;
查询年龄大于 25 岁的用户:
SELECT *
FROM public.user_info
WHERE age > 25;
多个条件:
SELECT *
FROM public.user_info
WHERE age > 25
AND username = 'zhangsan';
使用 IN 查询:
SELECT *
FROM public.user_info
WHERE id IN (1, 2, 3);
使用 LIKE 模糊查询:
SELECT *
FROM public.user_info
WHERE username LIKE '%zhang%';
按照 ID 升序:
SELECT *
FROM public.user_info
ORDER BY id ASC;
按照 ID 降序:
SELECT *
FROM public.user_info
ORDER BY id DESC;
查询前 10 条:
SELECT *
FROM public.user_info
LIMIT 10;
查询第 11 到第 20 条:
SELECT *
FROM public.user_info
ORDER BY id
LIMIT 10 OFFSET 10;
这是人大金仓中非常常用的操作。
假设 public 和 app 两个 Schema 中都存在 user_info 表。
查询 public 中的数据:
SELECT * FROM public.user_info;
查询 app 中的数据:
SELECT * FROM app.user_info;
查询 backup 中的数据:
SELECT * FROM backup.user_info;
跨 Schema 查询的语法:
SELECT 字段
FROM Schema名称.表名称;
这里的 Schema 名称和表名称之间使用英文句点 . 连接。
假设 public.user_info 保存用户信息,app.order_info 保存订单信息。
可以直接进行关联查询:
SELECT
u.id,
u.username,
o.order_no,
o.amount
FROM public.user_info u
LEFT JOIN app.order_info o
ON u.id = o.user_id;
不需要切换 Schema,也不需要额外建立数据库连接。
前提是两个表位于同一个数据库中,并且当前用户拥有对应对象的访问权限。
如果多个 Schema 中的表结构一致,可以使用 UNION ALL:
SELECT id, username, age
FROM public.user_info
UNION ALL
SELECT id, username, age
FROM app.user_info
UNION ALL
SELECT id, username, age
FROM backup.user_info;
UNION ALL 会保留重复记录,而 UNION 会对结果去重。
修改表结构和修改表数据是两种不同的操作。
ALTER TABLE 主要用于增加字段、删除字段、修改字段类型、修改字段名称等。
ALTER TABLE public.user_info
ADD COLUMN phone VARCHAR(20);
增加多个字段:
ALTER TABLE public.user_info
ADD COLUMN address VARCHAR(200),
ADD COLUMN status INTEGER DEFAULT 1;
ALTER TABLE public.user_info
DROP COLUMN phone;
删除字段之前,需要确认业务程序是否还在使用该字段。
ALTER TABLE public.user_info
ALTER COLUMN age TYPE BIGINT;
如果新旧数据类型之间不能直接转换,需要使用 USING:
ALTER TABLE public.user_info
ALTER COLUMN age TYPE VARCHAR(20)
USING age::VARCHAR;
ALTER TABLE public.user_info
RENAME COLUMN username TO user_name;
ALTER TABLE public.user_info
RENAME TO user_info_old;
注意:RENAME TO 只修改表名,不会修改 Schema。
例如,将 public.user_info 移动到 app Schema:
ALTER TABLE public.user_info
SET SCHEMA app;
执行后,表的完整名称变成:
app.user_info
注意:目标 Schema 必须存在,当前用户需要具备相应权限。移动表不会自动把相关视图、函数等对象全部迁移到新 Schema。
INSERT INTO public.user_info
(id, username, age, email)
VALUES
(1, 'zhangsan', 25, 'zhangsan@example.com');
INSERT INTO public.user_info
(id, username, age, email)
VALUES
(2, 'lisi', 28, 'lisi@example.com'),
(3, 'wangwu', 30, 'wangwu@example.com'),
(4, 'zhaoliu', 32, 'zhaoliu@example.com');
将 public.user_info 中的数据插入到 app.user_info:
INSERT INTO app.user_info
(id, username, age, email)
SELECT
id,
username,
age,
email
FROM public.user_info;
这是一种常见的数据迁移方式。
只迁移年龄大于 25 岁的用户:
INSERT INTO app.user_info
(id, username, age, email)
SELECT
id,
username,
age,
email
FROM public.user_info
WHERE age > 25;
如果目标表中可能已经存在相同 ID,可以使用 ON CONFLICT:
INSERT INTO app.user_info
(id, username, age, email)
SELECT
id,
username,
age,
email
FROM public.user_info
ON CONFLICT (id) DO NOTHING;
这要求目标表的 id 字段存在主键或唯一约束。
如果需要重复数据时更新原记录:
INSERT INTO app.user_info
(id, username, age, email)
SELECT
id,
username,
age,
email
FROM public.user_info
ON CONFLICT (id)
DO UPDATE SET
username = EXCLUDED.username,
age = EXCLUDED.age,
email = EXCLUDED.email;
UPDATE public.user_info
SET age = 30
WHERE id = 1;
UPDATE public.user_info
SET
age = 31,
email = 'new@example.com'
WHERE id = 1;
UPDATE public.user_info
SET status = 1
WHERE age >= 18;
可以利用另一个 Schema 中的表作为更新数据来源。
例如,根据 app.user_info 中的用户信息,更新 public.user_info:
UPDATE public.user_info p
SET age = a.age
FROM app.user_info a
WHERE p.id = a.id;
这里的逻辑是:
生产环境中,建议执行 UPDATE 之前先确认影响范围:
SELECT *
FROM public.user_info
WHERE id = 1;
确认无误后再执行:
UPDATE public.user_info
SET age = 30
WHERE id = 1;
不要随意省略 WHERE 条件,否则可能修改整张表的数据。
DELETE FROM public.user_info
WHERE id = 1;
DELETE FROM public.user_info
WHERE age < 18;
DELETE FROM public.user_info;
也可以使用:
TRUNCATE TABLE public.user_info;
两种方式都可以删除表中的全部记录,但 TRUNCATE 通常更适合整表清空。
如果需要删除 public.user_info 中存在、但 app.user_info 中不存在的记录,可以使用 NOT EXISTS:
DELETE FROM public.user_info p
WHERE NOT EXISTS (
SELECT 1
FROM app.user_info a
WHERE a.id = p.id
);
执行前建议先使用相同条件进行 SELECT 检查:
SELECT *
FROM public.user_info p
WHERE NOT EXISTS (
SELECT 1
FROM app.user_info a
WHERE a.id = p.id
);
确认记录范围后再执行 DELETE。
下面以实际运维中经常遇到的情况为例。
假设现在需要将 public Schema 中的业务表迁移到 app Schema。
\dt public.*
\dt app.*
如果目标表不存在,可以通过 LIKE 创建:
CREATE TABLE app.user_info
(LIKE public.user_info INCLUDING ALL);
注意,LIKE INCLUDING ALL 会复制支持的表结构属性,但不代表所有外部依赖、触发器、权限和其他关联对象都会自动完整复制。
INSERT INTO app.user_info
SELECT *
FROM public.user_info;
如果两个表字段顺序不一致,建议明确指定字段:
INSERT INTO app.user_info
(id, username, age, email, create_time)
SELECT
id,
username,
age,
email,
create_time
FROM public.user_info;
查询源表:
SELECT COUNT(*) FROM public.user_info;
查询目标表:
SELECT COUNT(*) FROM app.user_info;
两边数量一致,只能说明记录数量相同,并不能证明数据内容完全一致。
还需要进一步检查关键字段和业务数据。
可以使用 EXCEPT 检查源表中存在、目标表中不存在的记录:
SELECT id, username, age, email
FROM public.user_info
EXCEPT
SELECT id, username, age, email
FROM app.user_info;
反过来检查目标表中多出来的记录:
SELECT id, username, age, email
FROM app.user_info
EXCEPT
SELECT id, username, age, email
FROM public.user_info;
如果两个查询都没有返回记录,说明指定字段的数据集合一致。
注意:EXCEPT 默认会去重。如果表中允许重复记录,需要使用 EXCEPT ALL 或其他方式检查重复数据差异。
如果只是复制数据,不需要移动原表,可以在确认业务切换完成后执行:
DROP TABLE public.user_info;
如果只是想清空源表数据,保留表结构:
TRUNCATE TABLE public.user_info;
生产环境执行前,应确认应用连接、视图、外键、触发器和其他依赖关系,必要时安排业务停写或切换窗口。
跨 Schema 操作除了要求用户具备表权限,还需要具备访问 Schema 的权限。
例如,允许用户 app_user 访问 app Schema:
GRANT USAGE ON SCHEMA app TO app_user;
允许查询表:
GRANT SELECT ON app.user_info TO app_user;
允许插入数据:
GRANT INSERT ON app.user_info TO app_user;
允许修改数据:
GRANT UPDATE ON app.user_info TO app_user;
允许删除数据:
GRANT DELETE ON app.user_info TO app_user;
一次授予多种权限:
GRANT SELECT, INSERT, UPDATE, DELETE
ON app.user_info
TO app_user;
如果需要操作该 Schema 下已有的所有表,可以使用:
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_user;
注意:这条命令只作用于执行时已经存在的表,不会自动授权未来新创建的表。需要对未来创建的表设置默认权限:
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO app_user;
默认权限还与执行命令的对象创建角色有关,需要根据实际建表用户配置。
人大金仓中进行表操作时,下面几个问题需要特别注意:
1. 尽量使用 Schema.表名
推荐:
SELECT * FROM public.user_info;
不推荐在多个 Schema 存在同名表时直接使用:
SELECT * FROM user_info;
后者会受到 search_path 影响。
2. UPDATE 和 DELETE 必须确认 WHERE 条件
特别是生产环境,不要直接执行没有 WHERE 的更新和删除语句。
3. DROP 和 TRUNCATE 要谨慎
DROP 会删除表对象,TRUNCATE 会清空整张表的数据,两者都不是普通的单条记录删除操作。
4. 跨 Schema 不代表跨数据库
public.user_info 和 app.user_info 可以直接进行关联查询,是因为它们位于同一个数据库中。
如果两个表位于不同数据库,不能直接使用普通的 Schema.表名 进行关联,需要考虑数据库链接、外部表或数据迁移等方案。
5. 生产环境建议使用事务
对于支持事务的普通表操作,可以先开启事务:
BEGIN;
UPDATE public.user_info
SET age = 30
WHERE id = 1;
SELECT *
FROM public.user_info
WHERE id = 1;
确认修改结果后:
COMMIT;
如果发现操作错误:
ROLLBACK;
注意,事务中的修改在 COMMIT 之前不会正式提交。TRUNCATE 在支持事务的普通表上也可以回滚,但涉及外部表、特殊对象或其他非事务性操作时,需要单独确认。
| 操作 | 命令 |
| -------------------- | ---------------------------------------------------- |
| 查看 Schema | \dn |
| 查看表 | \dt |
| 查看指定 Schema 的表 | \dt public.* |
| 查看表结构 | \d public.user_info |
| 创建 Schema | CREATE SCHEMA app; |
| 删除 Schema | DROP SCHEMA app; |
| 创建表 | CREATE TABLE public.test (...); |
| 删除表 | DROP TABLE public.test; |
| 查询数据 | SELECT * FROM public.test; |
| 插入数据 | INSERT INTO public.test ...; |
| 修改数据 | UPDATE public.test SET ... WHERE ...; |
| 删除数据 | DELETE FROM public.test WHERE ...; |
| 清空表 | TRUNCATE TABLE public.test; |
| 增加字段 | ALTER TABLE public.test ADD COLUMN ...; |
| 删除字段 | ALTER TABLE public.test DROP COLUMN ...; |
| 修改字段类型 | ALTER TABLE public.test ALTER COLUMN ... TYPE ...; |
| 修改表名 | ALTER TABLE public.test RENAME TO test_new; |
| 移动 Schema | ALTER TABLE public.test SET SCHEMA app; |
| 跨 Schema 查询 | SELECT * FROM app.test; |
| 跨 Schema 插入 | INSERT INTO app.test SELECT * FROM public.test; |
| 跨 Schema 更新 | UPDATE ... FROM ...; |
| 跨 Schema 删除 | DELETE ... WHERE NOT EXISTS (...); |
人大金仓的表操作与 PostgreSQL 风格的关系型数据库有很多相似之处,日常使用中需要重点掌握 CREATE、SELECT、INSERT、UPDATE、DELETE 和 ALTER TABLE 等 SQL 命令。
而 Schema 是人大金仓数据库对象管理中的重要组成部分。
在同一个数据库中,可以创建多个 Schema,不同 Schema 可以拥有相同名称的表。通过 Schema.表名 的方式,可以直接完成跨 Schema 查询、数据复制、关联查询、更新和删除。
对于数据库运维人员来说,熟悉 Schema 的管理方式和跨 Schema 操作,能够更方便地完成业务数据迁移、表结构调整和数据库日常维护。
评论 0