MySQL has an auto-increment column feature that allows a unique value to be automatically generated for each new row inserted into a table. However, Oracle does not have this feature and requires a different approach to achieve the same functionality.

In Oracle, to simulate an auto-increment column, you need to create a sequence and a before insert trigger. The sequence generates a unique sequence number, and the trigger assigns this number to the column before inserting the new row into the table.

Here is an example of how to implement this in Oracle:

CREATE SEQUENCE seq_name
START WITH 1
INCREMENT BY 1
NOCACHE;
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    :NEW.column_name := seq_name.NEXTVAL;
END;

Let's break down the code:

- First, we create a sequence named "seq_name" using the CREATE SEQUENCE statement. The START WITH clause specifies the initial value of the sequence, and the INCREMENT BY clause defines how much the sequence should increment by for each new value.

- Next, we create a before insert trigger named "trigger_name" using the CREATE OR REPLACE TRIGGER statement. The trigger is associated with the table "table_name" and will be executed for each row before it is inserted.

- Inside the trigger, we use the :NEW pseudorecord to access the new row being inserted. We assign the next value from the sequence to the "column_name" column of the new row using the seq_name.NEXTVAL function.

By using this sequence and trigger combination, we can achieve the same functionality as an auto-increment column in MySQL.

Overall, the differences in SQL syntax between Oracle and MySQL regarding auto-increment columns arise due to the different approaches each database system takes to handle this feature. While MySQL provides a built-in auto-increment column functionality, Oracle requires the creation of a sequence and trigger to simulate the same behavior.

在Oracle和MySQL中，不是所有的SQL语句都是相同的。两者都不支持实际的SQL标准IDENTITY。

Oracle不使用反引号，你实际上不需要引用你的标识符。最好不要这样做，这样你就不会无意中在标识符中使用无效的字符。

Oracle的数字称为NUMBER，可以带有可选的精度和比例。

为了实现自动增量，可以创建一个序列：

CREATE SEQUENCE seq_category_id START WITH 1 INCREMENT BY 1;

然后在插入表时，可以这样做：

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

要自动执行此操作，类似于AUTO_INCREMENT，可以使用before insert触发器：

-- 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 identity"或"oracle auto_increment"之类的东西，以了解Oracle是如何实现这一点的。

感谢Scott A的解释。