Oracle 扫盲

最近撸了个用于管理数据库的VSCode插件，在测试Oracle类型数据库功能时，发现Oracle跟MySQL有非常的的差异，记录下来方便日后再用到。

一、先纠正几个概念

MySQL 里习惯说”连上服务器，use 一个库”。Oracle 这边，一个实例基本就对应一个数据库，平时理解的”库”，在 Oracle 里其实是”用户（schema）”。

每个用户就是一个 schema，用户名底下的表就是他的表
没有”跨库 join”这种事，要访问别的用户的表，写 用户名.表名，而且得有权限
连接串里不再是简单的 host:port，而是 service_name 或 SID，这块下一小节单独说

表空间（tablespace）也是 Oracle 特有的，简单理解：表的数据最终落在数据文件上，数据文件归属表空间，用户建表时默认放进自己的默认表空间。日常开发不太需要操心，知道有这个东西就行。

二、连接数据库：我第一次就被 SID 搞懵了

工具不缺：官方的 SQL Developer，老牌的 PL/SQL Developer，Navicat、DBeaver 也都支持。我第一次连的时候，IP、端口、账号密码都填好了，发现还剩一个必填项：要在 SID 和服务名之间二选一。当时完全不知道这俩是什么关系，随手选了一个，然后喜提 ORA-12505。

MySQL 的连接很直接，TCP 连上 mysqld 就完事了。Oracle 中间多了一层叫 listener（监听器） 的进程：客户端先连到它（默认端口 1521），再由它把你转交给数据库实例。

然后是 SID 和 service_name 的区别：

SID 是实例的名字，一个实例一个，相当于”身份证号”
service_name 是实例对外注册的”服务名”，一个实例可以注册多个；RAC 集群里一个服务名还能同时飘在多个实例上

官方推荐客户端一律连 service_name，但现实是很多老环境、老文档、老交接文档里只给你 SID。连不上就换另一个选项再试，不用纠结原理，先连上再说。

sqlplus 的两种常见连接姿势：

# 简易连接（EZConnect），//host:port/ 后面跟的是 service_name
sqlplus app_user/password@//192.168.1.100:1521/orcl

# TNS 别名：先在 tnsnames.ora 里配好，之后只写别名
sqlplus app_user/password@ORCL_DB

tnsnames.ora 长这样，文件放在 $ORACLE_HOME/network/admin（也可以用环境变量 TNS_ADMIN 指到别处）：

ORCL_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

图形工具里对应的就是那个二选一：SQL Developer 新建连接时，主机名/端口下面有”服务名”和”SID”两个单选；Navicat、DBeaver 也都有同样的选择，或者直接选 TNS 别名连接。拿不准就先选服务名，报 12505 再换 SID。

连接报错速查，这几个我全遇到过：

报错	大概率原因
ORA-12541	连不上监听器，端口不对或 listener 没起
ORA-12505	你按 SID 连的，但监听器那儿没注册这个 SID
ORA-12514	你按服务名连的，但没注册这个服务名，跟 12505 反着来
ORA-01017	用户名或密码错（偶尔其实是连错了库）
ORA-28000	账号被锁了，找 DBA 解锁

实在搞不清该填什么，最快的办法是找 DBA 要一份 tnsnames.ora，或者让他跑一下 lsnrctl status，监听器上注册了哪些服务名一目了然。

还有个工具层面的坎：Navicat 和 PL/SQL Developer 走的是 Oracle 客户端（OCI），第一次用会提示你指定 instant client 的位置（比如那个 oci.dll），得先去官网下一个 instant client 解压好。SQL Developer 和 DBeaver 走 JDBC，自带驱动，装上就能连，图省事可以先从这俩下手。

最后提一句工具里的自动提交开关：DBeaver 工具栏上有个 auto-commit 按钮，Navicat 的设置里也有对应项。第一次连上先看一眼这个开关，它关系到第六节要讲的坑。

三、数据类型对照

MySQL	Oracle	备注
TINYINT / SMALLINT / INT / BIGINT	NUMBER	全归 NUMBER，要自己定精度
DECIMAL(p,s)	NUMBER(p,s)	基本一样
VARCHAR(n)	VARCHAR2(n)	就用 VARCHAR2，别用 VARCHAR
TEXT	CLOB
BLOB	BLOB	名字相同
DATE	无纯日期类型	Oracle 的 DATE 带时分秒
DATETIME / TIMESTAMP	DATE / TIMESTAMP	要毫秒用 TIMESTAMP
JSON	21c 之前用 CLOB 凑合	21c 起才有原生 JSON 类型
无对应	NUMBER(1) / CHAR(1)	Oracle 没有布尔类型

几个容易懵的点：

Oracle 的 DATE 是带时分秒的，精度到秒；MySQL 的 DATE 才是纯日期。查出来显示成什么样还跟会话的 NLS_DATE_FORMAT 有关，想稳就用 TO_CHAR 自己格式化。
NUMBER 不指定精度默认 38 位，随手用问题不大。

四、自增主键：没有 AUTO_INCREMENT

这是从 MySQL 过来最不适应的地方之一。

12c 之前的标准做法是建一个 SEQUENCE，插入时自己取下一个值：

CREATE SEQUENCE seq_user START WITH 1 INCREMENT BY 1;

INSERT INTO t_user (id, name) VALUES (seq_user.NEXTVAL, '张三');

老项目里还有种”全自动”变体：序列配触发器，插入前自动填 id，SQL 里就不用管它了：

CREATE OR REPLACE TRIGGER trg_user_id
BEFORE INSERT ON t_user
FOR EACH ROW
BEGIN
  IF :new.id IS NULL THEN
    SELECT seq_user.NEXTVAL INTO :new.id FROM dual;
  END IF;
END;
/

12c 之后可以用 IDENTITY 列，体验接近 MySQL：

CREATE TABLE t_user (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(50)
);

GENERATED ALWAYS 不允许手动插 id；想保留手动插值的余地就用 GENERATED BY DEFAULT AS IDENTITY。

两个小提醒：seq.CURRVAL 只有在同一会话里取过 NEXTVAL 之后才有效，直接查会报错；序列默认带缓存（一般缓存 20 个号），实例重启会跳号——不过 MySQL 的自增值重启后照样跳，所以不用大惊小怪。

五、最大的坑：空字符串就是 NULL

Oracle 里 '' 就是 NULL，不存在”空串”和 NULL 的区别：

INSERT INTO t (name) VALUES ('');    -- 这两句效果完全一样
INSERT INTO t (name) VALUES (NULL);

SELECT * FROM t WHERE name = '';      -- 永远查不到，等价于 name = NULL
SELECT * FROM t WHERE name IS NULL;   -- 空串也能被这句查出来

所以在 MySQL 里用 WHERE col <> '' 过滤空串的习惯，到 Oracle 直接失效（NULL 参与比较，结果还是 NULL），IN ('') 也永远匹配不到。这个坑第一天准踩，提前打好预防针。

新表设计时建议想清楚：字段”没有值”就统一用 NULL 表达，别像 MySQL 那样有时存空串有时存 NULL，到 Oracle 里这俩已经合并了。

六、事务：默认不自动提交

第二节提过，执行完 INSERT 会提示 “1 row created”，但没提交。MySQL 默认 autocommit，一条 SQL 一个事务；Oracle 反过来，DML 执行完必须自己 COMMIT，否则改动只在你自己的会话里可见，别人看不到，你断开连接还会被回滚。

更刺激的是：DDL 会自动提交。表结构改到一半，前面没提交的 DML 会跟着一起提交掉。习惯了 MySQL 的人很容易在这里翻车。

好在事务控制的零件两边基本同名：SAVEPOINT sp1 打标记，ROLLBACK TO sp1 回到标记处；SELECT ... FOR UPDATE 锁行，两边写法一样，Oracle 里也是查数改数的老搭档。

顺带说一句，Oracle 的读一致性做得很硬，写不阻塞读，这块体感上比 MySQL 更”严”。

七、SQL 写法对照

直接上表，都是高频写法：

需求	MySQL	Oracle
当前时间	NOW()	SYSDATE / SYSTIMESTAMP
分页	LIMIT 10 OFFSET 20	OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY（12c+），老版本玩 ROWNUM
判空	IFNULL(a, b)	NVL(a, b)
条件取值	IF(c, a, b) / CASE	DECODE(c, 1, ‘a’, 2, ‘b’) 或 CASE
字符串转日期	STR_TO_DATE	TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’)
日期转字符串	DATE_FORMAT	TO_CHAR(sysdate, ‘YYYY-MM-DD’)
日期加减	DATE_ADD(NOW(), INTERVAL 1 DAY)	SYSDATE + 1
加减月份	INTERVAL 1 MONTH	ADD_MONTHS(SYSDATE, 1)
日期差几个月	TIMESTAMPDIFF(MONTH, a, b)	MONTHS_BETWEEN(b, a)
取零点（去掉时间）	DATE(NOW())	TRUNC(SYSDATE)
分组拼接	GROUP_CONCAT(name)	LISTAGG(name, ‘,’) WITHIN GROUP (ORDER BY id)
拼接	CONCAT(a, b)	a \|\| b

日期运算值得单独夸一下：Oracle 里日期本质上就是个数，直接加减。SYSDATE + 1 是明天，SYSDATE + 3/24 是三小时后，SYSDATE - 1/1440 是一分钟前（一天 1440 分钟）。写顺手了比 DATE_ADD 省事。

老版本的分页，ROWNUM 是这么个套娃写法（取第 21~30 条）：

SELECT * FROM (
  SELECT t.*, ROWNUM rn
    FROM (SELECT * FROM t_user ORDER BY id) t
   WHERE ROWNUM <= 30
)
WHERE rn > 20;

多表关联更新也变了：MySQL 里顺手的 UPDATE a JOIN b SET ...，Oracle 不支持，要么写相关子查询，要么用 MERGE：

MERGE INTO t_target a
USING (SELECT id, qty FROM t_source) b
   ON (a.id = b.id)
 WHEN MATCHED THEN UPDATE SET a.qty = b.qty
 WHEN NOT MATCHED THEN INSERT (a.id, a.qty) VALUES (b.id, b.qty);

顺便，MERGE 就是 upsert，对应 MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE。

零散的几个：

查询必须有 FROM。SELECT 1 不行，要写 SELECT 1 FROM DUAL。DUAL 是系统自带的单行表，新版本才允许省略，老环境别指望。
GROUP BY 很严格：SELECT 里的非聚合列必须全部出现在 GROUP BY 里，没有 MySQL 那种宽松模式。
多行 VALUES 插入是 23ai 才有的新特性，之前要写 INSERT ALL ... SELECT 1 FROM DUAL，老代码里很常见。
老代码里可能见到 WHERE a.id = b.id(+)，这是 Oracle 自家的老式外连接写法，等价于 LEFT JOIN，认一下就行。
TO_DATE 一定要显式给格式串，不然走会话参数，换个环境就报 ORA-01861。
MySQL 里 UPDATE/DELETE 可以接 ORDER BY 和 LIMIT，Oracle 没这待遇，要限量只能拿 ROWNUM 包一层。

八、大小写：都按大写存

Oracle 的标识符（表名、列名）默认全按大写存储，不带双引号的名字都会被转成大写。想建小写表名得用双引号包起来，然后从此每次查询都得带双引号——千万别这么干，新表老老实实用大写或者干脆不写引号。

MySQL 的表名大小写则跟操作系统和 lower_case_table_names 配置有关，两边的习惯不能直接搬。

九、看表结构、查元数据

MySQL 里 show tables、show create table、information_schema 一把梭。Oracle 换成查数据字典视图，常用命令对号入座：

SHOW TABLES → SELECT table_name FROM user_tables;
SHOW DATABASES → 没有对应物，凑合用 SELECT username FROM all_users;
SHOW CREATE TABLE → SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_USER') FROM dual;（sqlplus 里先 SET LONG 200000，不然输出被截断）
DESC t_user → sqlplus 里原样能用

注释也要单独说：MySQL 的列注释写在建表语句里，Oracle 的注释是独立的 DDL，用 COMMENT ON 加，查注释去 comments 表：

COMMENT ON COLUMN t_user.name IS '姓名';

SELECT column_name, comments
  FROM user_col_comments
 WHERE table_name = 'T_USER';

其他的按需查：

SELECT index_name FROM user_indexes WHERE table_name = 'T_USER';      -- 索引
SELECT column_name, comments FROM user_tab_comments;                  -- 表注释
SELECT num_rows FROM user_tables WHERE table_name = 'T_USER';         -- 行数(靠统计信息,不是实时值)
SELECT user FROM dual;                                                -- 当前登录用户

注意 NUM_ROWS 来自统计信息，刚插入完的数据它不知道，要精确就老老实实 COUNT(*)。

前缀分三档：USER_ 是自己的，ALL_ 是有权限看的，DBA_ 是全库的（要 DBA 权限）。

十、查锁、杀会话

排障场景对号入座：MySQL 里 SHOW PROCESSLIST、翻 information_schema 的锁表。Oracle 对应的是 v$ 系列视图：

-- 谁连着、在干嘛（对应 SHOW PROCESSLIST）
SELECT sid, serial#, username, status, machine, sql_id
  FROM v$session
 WHERE username = 'APP_USER';

-- 谁锁着对象
SELECT * FROM v$locked_object;

干掉某个会话：

ALTER SYSTEM KILL SESSION '1234, 5678';   -- 前面查到的 sid, serial#

v$ 视图要权限，开发账号多半得找 DBA 代劳。图形工具的话，SQL Developer 和 Navicat 都有”会话”面板，能看到的东西一样，还更直观。

十一、执行计划

MySQL 的 EXPLAIN SELECT ... 在 Oracle 里是两步：

EXPLAIN PLAN FOR SELECT * FROM t_user WHERE id = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

sqlplus 里还有个偷懒开关：SET AUTOTRACE ON，之后每条 SQL 顺手把计划和执行统计打出来。

想看真实执行过的计划和实际行数（相当于看真正的执行过程而不是预估）：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

统计信息要自己动手时用这个：

EXEC DBMS_STATS.GATHER_TABLE_STATS('APP_USER', 'T_USER');

另外，MySQL 里 hint 多半是应急手段，Oracle 里 /*+ ... */ 是正统优化手段，老系统的 SQL 里到处都是，看到别当成垃圾注释给删了。

十二、导数据：mysqldump 的平替没那么顺手

MySQL 一条 mysqldump 走天下。Oracle 对应的是数据泵 expdp / impdp：

expdp app_user/password@orcl schemas=app_user directory=DATA_PUMP_DIR dumpfile=app.dmp logfile=app.log
impdp app_user/password@orcl schemas=app_user directory=DATA_PUMP_DIR dumpfile=app.dmp

体感差异很大：数据泵是在数据库服务端跑的，那个 directory 是服务端的一个目录对象（一般要 DBA 帮忙建），导出的文件落在数据库服务器上而不是你本机。

想把数据弄到本机，就用 SQL Developer / Navicat 的导出功能（INSERT 脚本或 CSV），小数据量完全够用。第一次找 expdp 的 dump 文件找半天的人不止我一个。

十三、PL/SQL 随手记

Oracle 的 PL/SQL 比 MySQL 的存储过程强一大截，也复杂一大截：DECLARE - BEGIN - END 块结构、强类型、游标、异常处理，还有包（package）这一整套。业务逻辑下沉到数据库的团队，PL/SQL 是绕不过去的。

一个匿名块感受下画风：

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM t_user;
  DBMS_OUTPUT.PUT_LINE('count = ' || v_count);
END;
/

（sqlplus 里要先 SET SERVEROUTPUT ON，不然 PUT_LINE 的输出被吞掉，这个也是必踩项。）

最后碎碎念

中文乱码：sqlplus 里查出来中文是问号或乱码，先查服务端字符集 SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';，然后把客户端的 NLS_LANG 环境变量设成配套的值（比如 AMERICAN_AMERICA.AL32UTF8）。
密码 180 天过期：Oracle 账号默认 profile 180 天过期，到期连接报 ORA-28001/28002，别以为是密码错了瞎折腾。
RAC：Oracle 的高可用是多实例挂同一份存储，跟 MySQL 主从复制的思路完全不一样，属于运维的活儿，知道有这么个东西就行。

总体感受：Oracle 比 MySQL 严格得多，也”重”得多。SQL 写得不规范直接报错不给过，好处是模糊地带少，坏处是刚上手天天被报错教育。

先记这么多，后面踩到新坑再来补。

Oracle 扫盲

Oracle 扫盲

一、先纠正几个概念

二、连接数据库：我第一次就被 SID 搞懵了

三、数据类型对照

四、自增主键：没有 AUTO_INCREMENT

五、最大的坑：空字符串就是 NULL

六、事务：默认不自动提交

七、SQL 写法对照

八、大小写：都按大写存

九、看表结构、查元数据

十、查锁、杀会话

十一、执行计划

十二、导数据：mysqldump 的平替没那么顺手

十三、PL/SQL 随手记

最后碎碎念

向量数据库入门：从向量基础到 Chroma 实战

小枫