自增列：Oracle 和 MySQL 的 SQL 语法差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8116209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto-increment column: differences in SQL syntax between Oracle and MySQL
提问by Richard
I am a university student and need to submit a coursework using iSQL* Plus by Oracle.
我是一名大学生，需要使用 Oracle 的 iSQL* Plus 提交课程作业。
I am trying to create a table with the following SQL Statement:
我正在尝试使用以下 SQL 语句创建一个表：
CREATE TABLE Category
( `id` INT(11) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR (45) NULL ,
PRIMARY KEY (`id`) );
This results in the following message:
这会导致以下消息：
ORA-00911: invalid character
ORA-00911: 无效字符
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') );
The error:
错误：
ORA-00904: : invalid identifier
ORA-00904: : 无效标识符
So one more try with " - The Error:
所以再试一次“ - 错误：
( "id" INT(11) NOT NULL AUTO_INCREMENT ,
*
ORA-00907: missing right parenthesis
ORA-00907: 缺少右括号
If I remove the (11)behind the INT it will complaint about the AUTO_INCREMENTattribute.
如果我删除(11)INT 后面的内容，它会抱怨该AUTO_INCREMENT属性。
CREATE TABLE Category
( "id" INT NOT NULL AUTO_INCREMENT ,
"title" VARCHAR (45) NULL ,
PRIMARY KEY ("id") );
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?
我认为 SQL 就是 SQL，在这些非常基本的级别上并没有真正的区别。我认为事情在更深层次上变得越来越不同？
- how I get my statement working?
- what would you recommend for someone familiar with MySQL to learn Oracle?
- 我如何让我的陈述生效？
- 对于熟悉 MySQL 的人学习 Oracle，您有什么建议？
回答by Scott A
Not all SQL is the same. Neither Oracle nor MySQL support the actual SQL standard of IDENTITY.
并非所有 SQL 都是相同的。Oracle 和 MySQL 都不支持 IDENTITY 的实际 SQL 标准。
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 不使用反引号……您实际上不需要引用您的标识符。最好不要这样做，这样您就不会无意中在标识符中使用无效字符。
Oracle numerics are called NUMBER, and can take an optional precision and scale.
Oracle 数字称为 NUMBER，可以采用可选的精度和小数位数。
CREATE TABLE Category
(
id NUMBER(11) NOT NULL,
title VARCHAR2(45) NULL,
PRIMARY KEY (id)
)
To do an AUTO_INCREMENT, create a sequence:
要执行 AUTO_INCREMENT，请创建一个序列：
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');
To do this automatically, like AUTO_INCREMENT, use a before insert trigger:
要自动执行此操作，如 AUTO_INCREMENT，请使用插入前触发器：
-- 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;
Or:
或者：
-- 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;
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 identity”或“oracle auto_increment”之类的内容，即可了解 Oracle 如何做到这一点。
回答by a1ex07
Oracle doesn't have auto increment columns. You need to create a sequenceand before inserttrigger that reads NEXTVALfrom the sequence and sets the value for the column
Oracle 没有自动递增列。您需要创建sequence并before insert触发读取NEXTVAL从序列，并将其值的列
