SQL示例：分别使用 MySQL 和 Oracle 创建表（自增主键、指定主键的区别，VARCHAR，VARCHAR2）

本文介绍了创建actor表的SQL语句实现方案，重点对比了MySQL和Oracle两种数据库的语法差异。

---

MySQL版本使用SMALLINT主键、VARCHAR字段和CURDATE()默认值，而Oracle使用NUMBER类型、VARCHAR2字段和SYSDATE默认值。

---

文章详细说明了主键约束、默认值设置和字段注释的实现方式，并提供了低版本MySQL的替代方案（使用触发器）。

---

同时对比了自增主键和指定主键的特点，包括定义方式、唯一性保证、插入语法等维度，最后给出了不同场景下的主键选择建议。

---

对于需要兼容多版本或特殊需求的场景，还提供了混合方案的实现示例。

---

Oracle 特有VARCHAR2，其他数据库使用 VARCHAR。

题目：创建表

SQL232 创建一个actor表，包含如下列

---

描述

创建一个actor表，包含如下列信息

列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	date	not null	最后更新时间，默认是系统的当前时间

解法

创建 actor 表的 MySQL 语句如下：

sql

CREATE TABLE actor ( actor_id SMALLINT(5) NOT NULL COMMENT '主键id', first_name VARCHAR(45) NOT NULL COMMENT '名字', last_name VARCHAR(45) NOT NULL COMMENT '姓氏', last_update DATE NOT NULL DEFAULT (CURDATE()) COMMENT '最后更新时间，默认是系统的当前时间', PRIMARY KEY (actor_id) );

说明：

1. 主键约束：actor_id被设置为主键，确保每行数据的唯一性

2. 默认值：last_update字段使用DEFAULT (CURDATE())设置默认值为当前系统日期（年月日部分）

3. 注释：使用COMMENT为每个字段添加了中文说明

注意：

• DEFAULT (CURDATE())语法要求MySQL 8.0.13及以上版本

• 如果使用更低版本的 MySQL，需要使用触发器来实现默认当前日期，或者改用DEFAULT CURRENT_DATE（但 MySQL 的 DATE 类型字段不支持直接使用CURRENT_DATE作为默认值）

兼容低版本 MySQL 的替代方案：

sql

CREATE TABLE actor ( actor_id SMALLINT(5) NOT NULL COMMENT '主键id', first_name VARCHAR(45) NOT NULL COMMENT '名字', last_name VARCHAR(45) NOT NULL COMMENT '姓氏', last_update DATE NOT NULL COMMENT '最后更新时间', PRIMARY KEY (actor_id) ); -- 创建触发器自动设置 last_update 的默认值 DELIMITER $$ CREATE TRIGGER actor_default_last_update BEFORE INSERT ON actor FOR EACH ROW BEGIN IF NEW.last_update IS NULL THEN SET NEW.last_update = CURDATE(); END IF; END$$ DELIMITER ;

完整建表+插入数据

• 用 INSERT INTO ... VALUES 语法
• 多条数据用逗号分隔，每条数据用括号包裹
• 字段顺序与 VALUES 中的值顺序对应
• 字符串和日期时间值使用单引号包裹

错误示例

--建表 CREATE TABLE IF NOT EXISTS actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')) ); --批量插入数据 INSERT INTO actor (actor_id, first_name, last_name, last_update) VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'); --查询验证 SELECT * FROM actor;

注意

上例在 MySQL 中使用了 SQLite 的语法datetime('now','localtime')，MySQL 不支持这种写法。

---

-- ❌ 错误：这是 SQLite 的语法，MySQL 不支持 last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')) -- ✅ MySQL 正确写法 last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

正确的 MySQL 代码

-- 建表 CREATE TABLE IF NOT EXISTS actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- 批量插入数据 INSERT INTO actor (actor_id, first_name, last_name, last_update) VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'); -- 查询验证 SELECT * FROM actor;

如果需要 DATETIME 类型

-- MySQL 5.6.5 之后版本支持 last_update DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP -- 或使用函数 last_update DATETIME NOT NULL DEFAULT (NOW())

使用INSERT IGNORE语法来实现"如果数据已经存在，请忽略"

不使用replace操作

INSERT IGNORE INTO actor (actor_id, first_name, last_name, last_update) VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33');

说明

关键字	作用
INSERT IGNORE	插入时如果主键冲突，忽略该条插入，不报错

执行逻辑

1. 表中已存在actor_id = 3的记录（WD, GUINESS）

2. 执行INSERT IGNORE尝试插入相同actor_id的记录

3. 由于主键冲突，MySQL忽略新数据的插入

4. 表中数据保持不变，仍为：3|WD|GUINESS|2006-02-15 12:34:33

其他替代方案（MySQL 语法对比）

语法	行为	适用场景
INSERT IGNORE	冲突时忽略，不报错	✅ 本题要求
REPLACE	冲突时删除旧记录再插入新记录	❌ 题目不支持
ON DUPLICATE KEY UPDATE	冲突时执行更新操作	需要更新时使用

在 Oracle 中实现相同的 actor 表，语法如下：

方法一：使用 DEFAULT 子句（推荐）

sql

CREATE TABLE actor ( actor_id NUMBER(5) NOT NULL, first_name VARCHAR2(45) NOT NULL, last_name VARCHAR2(45) NOT NULL, last_update DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT pk_actor PRIMARY KEY (actor_id) ); COMMENT ON TABLE actor IS '演员表'; COMMENT ON COLUMN actor.actor_id IS '主键id'; COMMENT ON COLUMN actor.first_name IS '名字'; COMMENT ON COLUMN actor.last_name IS '姓氏'; COMMENT ON COLUMN actor.last_update IS '最后更新时间，默认是系统的当前时间';

方法二：使用触发器（更灵活的默认值）

如果需要只存储日期部分（不包含时间），可以使用触发器：

sql

-- 创建表 CREATE TABLE actor ( actor_id NUMBER(5) NOT NULL, first_name VARCHAR2(45) NOT NULL, last_name VARCHAR2(45) NOT NULL, last_update DATE NOT NULL, CONSTRAINT pk_actor PRIMARY KEY (actor_id) ); COMMENT ON TABLE actor IS '演员表'; COMMENT ON COLUMN actor.actor_id IS '主键id'; COMMENT ON COLUMN actor.first_name IS '名字'; COMMENT ON COLUMN actor.last_name IS '姓氏'; COMMENT ON COLUMN actor.last_update IS '最后更新时间，默认是系统的当前时间'; -- 创建触发器设置默认值 CREATE OR REPLACE TRIGGER trg_actor_last_update BEFORE INSERT ON actor FOR EACH ROW BEGIN IF :NEW.last_update IS NULL THEN :NEW.last_update := TRUNC(SYSDATE); -- TRUNC 去除时间部分，只保留日期 END IF; END; /

方法三：使用 identity 列（Oracle 12c+）

如果需要自增主键，可以使用 identity 列：

sql

CREATE TABLE actor ( actor_id NUMBER(5) GENERATED BY DEFAULT AS IDENTITY NOT NULL, first_name VARCHAR2(45) NOT NULL, last_name VARCHAR2(45) NOT NULL, last_update DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT pk_actor PRIMARY KEY (actor_id) );

Oracle 与 MySQL 的主要差异：

建议：

• 使用DEFAULT SYSDATE是最简单的方式，但会包含时间部分（00:00:00）

• 如果严格只需要日期，使用TRUNC(SYSDATE)配合触发器

• 如果需要自增主键，Oracle 12c 及以上版本使用IDENTITY列

自增主键 vs 指定主键的区别

示例对比

混合方案：默认自增 + 允许手动指定

某些数据库支持混合模式，设置默认自增但也可手动指定：

sql

-- MySQL: 允许手动指定覆盖自增值 INSERT INTO t (id, name) VALUES (100, 'Special'); -- 指定100 INSERT INTO t (name) VALUES ('Normal'); -- 自增为101 -- Oracle (12c+): CREATE TABLE t (id NUMBER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR2(50)); -- 可手动指定或自动生成

选择建议

VARCHAR vs VARCHAR2 区别

核心结论

Oracle 中两者功能完全相同，但官方强制要求使用 VARCHAR2

Oracle 官方文档明确说明：

• VARCHAR计划在未来版本中改变用途或废弃

• 始终使用VARCHAR2确保向后兼容

跨数据库建议