MySQL 迁 Oracle 踩坑实录：DATETIME、TINYINT、AUTO_INCREMENT 的类型映射陷阱

去年做了一次 MySQL 到 Oracle 的数据库迁移，表结构转换阶段踩了三个类型映射的坑：DATETIME 精度丢失、TINYINT 被映射成 CHAR(1)、AUTO_INCREMENT 自增主键在 Oracle 里根本没有对应概念。每个坑都是数据跑起来才发现，返工成本极高。

这篇文章把三个坑的完整排查过程和最终映射方案记下来，如果你也在做 MySQL → Oracle 迁移，可以直接对照检查。

坑 1：DATETIME 精度丢失

MySQL 的 DATETIME 支持到微秒（DATETIME(6)），Oracle 的 DATE 只到秒。直接映射：

-- MySQL
CREATE TABLE order_log (
    id BIGINT PRIMARY KEY,
    create_time DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
);

-- Oracle（错误映射）
CREATE TABLE order_log (
    id NUMBER(19) PRIMARY KEY,
    create_time DATE DEFAULT SYSDATE
);

后果：MySQL 里 2024-01-15 14:30:25.123456 的数据，迁到 Oracle 变成 2024-01-15 14:30:25，毫秒部分直接截断。如果业务依赖毫秒级时间排序或幂等判断，数据就乱了。

正确映射：Oracle 用 TIMESTAMP 替代 DATE：

-- Oracle（正确映射）
CREATE TABLE order_log (
    id NUMBER(19) PRIMARY KEY,
    create_time TIMESTAMP(6) DEFAULT SYSTIMESTAMP
);

TIMESTAMP(6) 支持微秒，与 MySQL DATETIME(6) 对齐。如果业务只需要秒级，用 DATE 没问题，但要确认上下游没有依赖毫秒的逻辑。

坑 2：TINYINT 被映射成 CHAR(1)

MySQL 的 TINYINT(1) 常被当作布尔类型用（0/1），Oracle 没有 TINYINT，某些转换工具会把它映射成 CHAR(1)：

-- MySQL
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    is_active TINYINT(1) DEFAULT 1
);

-- Oracle（错误映射）
CREATE TABLE user (
    id NUMBER(19) PRIMARY KEY,
    is_active CHAR(1) DEFAULT '1'
);

后果：Java 代码里 user.getIsActive() 返回 String 类型 "1"/"0"，而原来 MySQL 驱动返回的是 Integer。MyBatis 映射时类型不匹配，查询报 TypeMismatchException 或静默转成 null。

正确映射：Oracle 用 NUMBER(1)：

-- Oracle（正确映射）
CREATE TABLE user (
    id NUMBER(19) PRIMARY KEY,
    is_active NUMBER(1) DEFAULT 1
);

Java 侧用 Integer 或 Boolean（MyBatis 里配 typeHandler）接收。如果业务确实只需要 Y/N，CHAR(1) 也能用，但要统一全项目的类型约定。

坑 3：AUTO_INCREMENT 在 Oracle 里不存在

MySQL 的自增主键：

-- MySQL
CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

Oracle 11g 及以前没有自增列，必须用序列 + 触发器模拟：

-- Oracle 11g 方案
CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER product_bir
BEFORE INSERT ON product
FOR EACH ROW
BEGIN
    SELECT product_seq.NEXTVAL INTO :new.id FROM dual;
END;
/

后果：如果直接迁表结构不迁序列和触发器，插入时报 ORA-01400: cannot insert NULL into ID。

Oracle 12c+ 支持 IDENTITY 列，语法接近 MySQL：

-- Oracle 12c+ 方案（推荐）
CREATE TABLE product (
    id NUMBER(19) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100)
);

但注意：MySQL 的 AUTO_INCREMENT 允许显式插入指定值（INSERT INTO product(id, name) VALUES(100, 'x')），Oracle 的 GENERATED ALWAYS AS IDENTITY 禁止显式插入，要用 GENERATED BY DEFAULT AS IDENTITY 才能兼容。

完整类型映射对照表

迁移前的检查清单

1. 全量扫描表结构：用 information_schema 导出所有表、列、类型、默认值、注释，不要漏掉任何一张表。

-- MySQL 导出表结构
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

2. 逐表确认类型映射：重点检查 DATETIME/TIMESTAMP 精度、TINYINT 语义、AUTO_INCREMENT 处理方式。

3. 生成 Oracle DDL 后人工 review：用 SQL 方言互转工具 把 MySQL DDL 一键转成 Oracle 方言，得到初稿后再逐行核对类型、默认值、约束。转换工具能覆盖 80% 的机械工作，剩下 20%（精度、约束、注释）必须人工把关。

4. 数据迁移后验证：抽样对比关键表的数据，特别是时间字段和布尔字段。

-- 验证时间精度
SELECT create_time FROM order_log WHERE id = 1;
-- MySQL: 2024-01-15 14:30:25.123456
-- Oracle: 15-JAN-24 02.30.25.123456 PM（TIMESTAMP）或 15-JAN-24（DATE）

5. Java 实体类同步调整：Oracle 的 NUMBER 映射到 Java 的类型与 MySQL 不同：

用 SQL 转 Java 实体工具 生成 Oracle 方言的实体类，避免手动映射类型时出错。如果只想看 DDL 的方言转换结果，用 SQL 方言互转工具 更直接。

常见坑

• 「工具生成的 DDL 直接执行」：类型映射、默认值、约束都可能出错，必须人工 review。
• 「Oracle DATE 和 MySQL DATETIME 一样」：Oracle DATE 只到秒，MySQL DATETIME 可到微秒，迁移后毫秒丢失。
• 「TINYINT(1) 映射成 CHAR(1) 也能用」：Java 侧类型不匹配，MyBatis 映射报错或 null。
• 「AUTO_INCREMENT 直接删掉就行」：Oracle 需要序列+触发器或 IDENTITY，直接删掉插入就报错。
• 「迁移完不验证数据」：类型映射错了，数据迁过去也是错的，抽样验证必须做。

相关工具与速查

• SQL 方言互转工具：粘贴 MySQL 建表语句一键转成 Oracle / PostgreSQL / SQL Server / SQLite 方言，覆盖类型映射、自增主键、分页语法
• SQL 转 Java 实体工具：转换完成后基于 Oracle DDL 生成 Java 实体类
• Oracle 命令速查：Oracle 专有语法、序列、触发器、分页等
• MySQL 命令速查：DDL、DML、索引、事务等 MySQL 高频命令