MySQL与Oracle SQL语法差异：ORA-00907错误排查求助

错误原因 #

Oracle数据库不支持MySQL专属的ENUM数据类型，你在创建Expressions表时使用了ENUM('>', '<', '=')语法，Oracle无法识别该类型，解析时会判定语法结构异常，从而抛出ORA-00907: missing right parenthesis错误。

修正方案 #

将ENUM替换为Oracle支持的VARCHAR2类型，同时可以通过CHECK约束限定operator列的取值范围，实现和MySQL ENUM类似的效果：

CREATE TABLE Variables1 (name VARCHAR2(3), value1 INT);
CREATE TABLE Expressions (
    left_operand VARCHAR2(3),
    operator VARCHAR2(1),
    right_operand VARCHAR2(3),
    CONSTRAINT chk_operator CHECK (operator IN ('>', '<', '='))
);

INSERT INTO Variables1 (name, value1) VALUES ('x', 66);
INSERT INTO Variables1 (name, value1) VALUES ('y', 77);

INSERT INTO Expressions (left_operand, operator, right_operand) VALUES ('x', '>', 'y');
INSERT INTO Expressions (left_operand, operator, right_operand) VALUES ('x', '<', 'y');
INSERT INTO Expressions (left_operand, operator, right_operand) VALUES ('x', '=', 'y');
INSERT INTO Expressions (left_operand, operator, right_operand) VALUES ('y', '>', 'x');
INSERT INTO Expressions (left_operand, operator, right_operand) VALUES ('y', '<', 'x');
INSERT INTO Expressions (left_operand, operator, right_operand) VALUES ('x', '=', 'x');

这段代码在Oracle环境下可正常执行，同时通过CHECK约束保证operator列只能取指定的三个运算符值，和原MySQL代码的逻辑一致。

内容的提问来源于stack exchange，提问作者ananya