使用SQLAlchemy创建后自动增加非唯一ID [英] Auto incrementing a non-unique id upon creation using SQLAlchemy

问题描述

我的主要目标是简化修订历史记录和日记的实现.

我发现自己想知道是否有可能使用Flask-SQLAlchemy(或直接使用SQL)为mysql获取一个自动递增的非唯一整数. 我发现了此堆栈溢出帖子，该帖子与我想要做的很接近，但是问题集中在主键上.例如，如果我的表有这些列，

revision_id = db.Column(db.Integer, nullable=False)
post_id = db.Column(db.Integer, nullable=False)

__table_args__ = (
    PrimaryKeyConstraint('post_id', 'revision_id'),
)

是否有可能创建一个新的post，其revification_id为1，post_id为max(post_id)+ 1，而不会出现两个用户试图同时创建一个帖子并创建相同的post_id的问题? /p>

此系统的优点是，它使发布历史记录(和区别记录)非常简单.每当有人要修改帖子时，我都会使用与原始帖子相同的post_id并增加version_id(现在，我将其键入，存在相同的问题).

更新:

Sylvain Leroux 使我走上了正确的道路，以解决我的问题.我需要将两个表都设置为sqlalchemy中的主键.如果sqlalchemy中有一个以上的主键，则不会假定它们是唯一的.这是我目前的定义，

revision_id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=False, default=1)
post_id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
__table_args__ = (
    PrimaryKeyConstraint('post_id', 'revision_id'),
)

哪个生成此SQL

CREATE TABLE `post` (
    revision_id INTEGER NOT NULL, 
    post_id INTEGER NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (post_id, revision_id), 
)

这使我可以插入带有和不带有post_id的内容.

所有归功于 Sylvain Leroux ，因为我只是将他的答案翻译为SQLAlchemy.

解决方案

引用经典的MySQL错误消息:

只能有一个自动列，并且必须将其定义为键:

auto_increment 列必须为(在主键中)或在键中(在MySQL中也称为索引，可能是唯一的，也可能是唯一的).

---

至:

SELECT MAX(id) FROM tbl INTO @new_id;
INSERT INTO tbl (id, ...) VALUES (@new_id, ....);

您清楚地了解到，如果两个并发请求执行相同的操作，则最终将出现两个新的，完全不相关且具有相同ID的行.您可能不想使用表锁来避免这种陷阱.

就我自己而言，我会说不要那样做".也许这会使您的应用程序中的某些事情变得更容易，但是我敢打赌，这会使其他的 t 吨更复杂或很多的鲁棒性

---

但是...如果真正的问题是保持"post id"不变，请记住，auto_increment可能是键的 part (

说id2，rev1项有错误.您可以插入一个新修订版:

insert into tbl (id, rev, content) values (2,2,"beta");

如果要查看项目id2的各种修订版本:

select * from tbl where id=2 order by rev;

您将必须找到使用SQLAlchemy(:D)的方法，但是使用MySQL绝对可以做到这一点.

My primary goal with this is to make implementing revision histories and journaling easier.

I found myself wondering if it's possible, using Flask-SQLAlchemy (or just straight up SQL), to get an auto incremented non-unique integer for mysql. I found this stack overflow post which is close to what I want to do but the question is focused on a primary key. For example, if my table had these columns,

revision_id = db.Column(db.Integer, nullable=False)
post_id = db.Column(db.Integer, nullable=False)

__table_args__ = (
    PrimaryKeyConstraint('post_id', 'revision_id'),
)

Would it be possible to create a new post that has a revision_id of 1 and a post_id of max(post_id) + 1 without the problem of two users attempting to create a post at the same time and creating the same post_id?

The advantage of this system is that it makes post history (and differencing) very simple. Whenever someone wants to modify a post I'd use the same post_id as the original and increment the revision_id (which, now that I'm typing this out, has the same problem).

Update:

Sylvain Leroux put me on the right track for how to solve my problem. I needed to set both tables as a primary key in sqlalchemy. If there's more then one primary key in sqlalchemy it doesn't assume that they're unique. Here's my current definition,

revision_id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=False, default=1)
post_id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
__table_args__ = (
    PrimaryKeyConstraint('post_id', 'revision_id'),
)

Which produces this SQL

CREATE TABLE `post` (
    revision_id INTEGER NOT NULL, 
    post_id INTEGER NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (post_id, revision_id), 
)

which allowed me to insert with and without a post_id.

All credit to Sylvain Leroux since I merely translated his answer to SQLAlchemy.

解决方案

To quote a classic MySQL error message:

there can be only one auto column and it must be defined as a key:

auto_increment column must be either (in) the primary key or (in) a key (also known as index in MySQL, which may or may be unique).

---

As for:

SELECT MAX(id) FROM tbl INTO @new_id;
INSERT INTO tbl (id, ...) VALUES (@new_id, ....);

You clearly understand that if two concurrent requests do the same thing, you will end up having two new rows, totally unrelated, with the same ID. An you probably don't want to use table locks to avoid that pitfall.

As of myself, I would say "don't do that". Maybe that would make some things more easy in your application, but I bet that would made tons of other things far more complicated or much less robust.

---

But ... if the real problem if to keep the "post id" constant, remember that auto_increment could be part of a key (http://sqlfiddle.com/#!2/9b4b45/1):

create table tbl(id int auto_increment not null, 
                 rev int not null default 1,
                 content TEXT,
                 primary key(id,rev));

-- insert 3 new contents with "auto id" and default revision 1
insert into tbl (content) values ("alpha"), ("bta"), ("gamma");

Say there is an error in the item id2,rev1. You could insert a new revision:

insert into tbl (id, rev, content) values (2,2,"beta");

If you want to see the various revision of item id2:

select * from tbl where id=2 order by rev;

You will have to find how to do that with SQLAlchemy (:D), but this is definitively possible with MySQL.

这篇关于使用SQLAlchemy创建后自动增加非唯一ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！