Doctrine2 没有将序列设置为 id 列的默认值(postgres)

2022-01-03 00:00:00 postgresql sequence php doctrine-orm

只是一个简单的例子:如果我想在 postgres 中创建一个带有自动填充 ID 的表,我运行这个 sql:

Just a simple example: If I want create a table with auto fill id in postgres I run this sql:

CREATE SEQUENCE person_id_seq  START 1;

CREATE TABLE person (
    id         integer PRIMARY KEY DEFAULT nextval('person_id_seq'),
    name       varchar(100) NOT NULL
);

并且在原则上我设置了所有属性

and in doctrine I set all property

class Person {

/**
 * @Id
 * @Column(type="integer", nullable=false)
 * @GeneratedValue(strategy="SEQUENCE")
 * @SequenceGenerator(sequenceName="person_id_seq", initialValue=1, allocationSize=100)
 */
private $id;

但是当我生成 sql (php science orm:schema-tool:create --dump-sql) 时,我明白了:

but when I generated sql (php doctrine orm:schema-tool:create --dump-sql) I got it:

CREATE TABLE person (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL
);
CREATE SEQUENCE person_id_seq INCREMENT BY 100 MINVALUE 1 START 1

但不要将其设置为默认值

but don't set it to default

d 人

      Column       |              Type              | Modifiers
-------------------+--------------------------------+-----------
 id                | integer                        | not null
...
..
.

推荐答案

来自 精美手册:

4.8.1.标识符生成策略
...
AUTO(默认):告诉 Doctrine 选择所用数据库平台首选的策略.首选策略是 MySQL、SQLite 和 MsSQL 的 IDENTITY 以及 Oracle 和 PostgreSQL 的 SEQUENCE.这种策略提供了完全的可移植性.
...
IDENTITY:告诉 Doctrine 使用数据库中的特殊标识列,这些列在插入行时生成一个值.此策略目前不提供完整的可移植性,并受以下平台支持:MySQL/SQLite (AUTO_INCREMENT)、MSSQL (IDENTITY) 和 PostgreSQL (SERIAL).

4.8.1. Identifier Generation Strategies
...
AUTO (default): Tells Doctrine to pick the strategy that is preferred by the used database platform. The preferred strategies are IDENTITY for MySQL, SQLite and MsSQL and SEQUENCE for Oracle and PostgreSQL. This strategy provides full portability.
...
IDENTITY: Tells Doctrine to use special identity columns in the database that generate a value on insertion of a row. This strategy does currently not provide full portability and is supported by the following platforms: MySQL/SQLite (AUTO_INCREMENT), MSSQL (IDENTITY) and PostgreSQL (SERIAL).

他们建议 AUTO 以获得最大的便携性:

They suggest AUTO for maximum portability:

/**
 * @Id
 * @Column(type="integer", nullable=false)
 * @GeneratedValue
 */

这应该会为您创建并连接一个序列.另一种方法是使用 IDENTITY 策略请求 serial 列:

That should create and wire up a sequence for you. An alternative would be to ask for a serial column using the IDENTITY strategy:

/**
 * @Id
 * @Column(type="integer", nullable=false)
 * @GeneratedValue(strategy="IDENTITY")
 */

这应该创建您的 id 列作为类型​​ serial 并且 PostgreSQL 将创建序列并为您设置默认值.

This one should create your id column as type serial and PostgreSQL will create the sequence and set up the default value for you.

文档表明您正在做的事情应该可行,但文档通常只提供简化版本的现实.

The documentation indicates that what you're doing should work but the documentation usually only provides a simplified version of reality.

尝试使用 strategy="AUTO".如果这不起作用,请尝试 strategy="IDENTITY".

Try using strategy="AUTO". If that doesn't work, try strategy="IDENTITY".

相关文章