自动增量列:Oracle和MySQL之间的SQL语法差异 [英] Auto-increment column: differences in SQL syntax between Oracle and MySQL

问题描述

我是一名大学生，需要使用Oracle的iSQL * Plus提交课程.

I am a university student and need to submit a coursework using iSQL* Plus by Oracle.

我正在尝试使用以下SQL语句创建表:

I am trying to create a table with the following SQL Statement:

    CREATE  TABLE  Category 
( `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR (45) NULL ,
  PRIMARY KEY (`id`) );

这将导致以下消息:

ORA-00911:无效字符

ORA-00911: invalid character

它是指`"号.因此，我尝试使用单引号代替以下内容:

It's referring to the tick ` sign. So I tried the following, using a single quote instead:

    CREATE  TABLE  Category 
( 'id' INT(11) NOT NULL AUTO_INCREMENT ,
  'title' VARCHAR (45) NULL ,
  PRIMARY KEY ('id') );

错误:

ORA-00904::无效的标识符

ORA-00904: : invalid identifier

因此，请尝试使用-错误:

So one more try with " - The Error:

( "id" INT(11) NOT NULL AUTO_INCREMENT ,
          *

ORA-00907:缺少右括号

ORA-00907: missing right parenthesis

如果我删除了INT后面的(11)，它将抱怨AUTO_INCREMENT属性.

If I remove the (11) behind the INT it will complaint about the AUTO_INCREMENT attribute.

    CREATE  TABLE  Category 
( "id" INT NOT NULL AUTO_INCREMENT ,
  "title" VARCHAR (45) NULL ,
  PRIMARY KEY ("id") );

我认为SQL是SQL，在这些非常基本的级别上并没有真正的区别.我以为事情在更深层次上正在变得不同?

I thought SQL is SQL and there are not really differences on these very basic levels. I thought that things are getting different on deeper levels?

• 我如何使我的陈述生效?
• 您会推荐给熟悉MySQL的人学习Oracle吗?

推荐答案

并非所有SQL都是相同的. Oracle和MySQL都不支持IDENTITY的实际SQL标准.

Not all SQL is the same. Neither Oracle nor MySQL support the actual SQL standard of IDENTITY.

Oracle不使用反引号...您实际上不需要引用标识符.最好不要这样做，以免您最终不会无意间在标识符中使用无效字符.

Oracle does not use backticks... you don't actually need to quote your identifiers. Better not to so you don't end up inadvertently using an invalid character in an identifier.

Oracle数字称为NUMBER，可以采用可选的精度和小数位数.

Oracle numerics are called NUMBER, and can take an optional precision and scale.

CREATE TABLE Category
(
  id    NUMBER(11)   NOT NULL,
  title VARCHAR2(45) NULL,
  PRIMARY KEY (id)
)

要执行AUTO_INCREMENT，请创建一个序列:

To do an AUTO_INCREMENT, create a sequence:

CREATE SEQUENCE seq_category_id START WITH 1 INCREMENT BY 1;

然后，当您插入表中时，请执行以下操作:

Then when you insert into the table, do this:

INSERT INTO category
VALUES (seq_category_id.nextval, 'some title');

要自动执行此操作(例如AUTO_INCREMENT)，请使用插入前触发器:

To do this automatically, like AUTO_INCREMENT, use a before insert trigger:

-- Automatically create the incremented ID for every row:
CREATE OR REPLACE trigger bi_category_id
BEFORE INSERT ON category
FOR EACH ROW
BEGIN
    SELECT seq_category_id.nextval INTO :new.id FROM dual;
END;

或者:

-- Allow the user to pass in an ID to be used instead
CREATE OR REPLACE TRIGGER bi_category_id
BEFORE INSERT ON category
FOR EACH ROW
DECLARE
    v_max_cur_id NUMBER;
    v_current_seq NUMBER;
BEGIN
    IF :new.id IS NULL THEN
        SELECT seq_category_id.nextval INTO :new.id FROM dual;
    ELSE
        SELECT greatest(nvl(max(id),0), :new.id) INTO v_max_cur_id FROM category;
        SELECT seq_category_id.nextval INTO v_current_seq FROM dual;
        WHILE v_current_seq < v_max_cur_id
        LOOP
            SELECT seq_category_id.nextval INTO v_current_seq FROM dual;
        END LOOP;
    END IF;
END;

现在，就发现这些差异而言，您通常可以只搜索"oracle身份"或"oracle auto_increment"之类的内容，以了解Oracle如何做到这一点.

Now, as far as discovering these differences, you can often just search for something like "oracle identity" or "oracle auto_increment" to see how Oracle does this.

这篇关于自动增量列:Oracle和MySQL之间的SQL语法差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！