MySQL:基于另一个字段添加序列列
我正在处理一些遗留代码/数据库,并且需要向数据库添加一个字段,该字段将记录与该(外国)id 相关的序列号。
示例表数据(当前):
ID ACCOUNT some_other_stuff
1 1 ...
2 1 ...
3 1 ...
4 2 ...
5 2 ...
6 1 ...
我需要添加一个sequenceid列,该列为每个帐户单独递增,实现:
ID ACCOUNT SEQ some_other_stuff
1 1 1 ...
2 1 2 ...
3 1 3 ...
4 2 1 ...
5 2 2 ...
6 1 4 ...
请注意,序列与帐户相关。
有没有办法可以在 SQL 中实现此目的,或者我是否可以使用 PHP 脚本来完成这项工作?
TIA, 凯夫
I'm working on some legacy code/database, and need to add a field to the database which will record a sequence number related to that (foreign) id.
Example table data (current):
ID ACCOUNT some_other_stuff
1 1 ...
2 1 ...
3 1 ...
4 2 ...
5 2 ...
6 1 ...
I need to add a sequenceid column which increments separately for each account, achieving:
ID ACCOUNT SEQ some_other_stuff
1 1 1 ...
2 1 2 ...
3 1 3 ...
4 2 1 ...
5 2 2 ...
6 1 4 ...
Note that the sequence is related to account.
Is there a way I can achieve this in SQL, or do I resort to a PHP script to do the job for me?
TIA,
Kev
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
创建触发器:
Create a trigger:
问题被标记为“mysql”,所以是的,MySQL 的 auto_increment 可以创建分组顺序 id。
参见http://dev.mysql.com/doc/ refman/5.0/en/example-auto-increment.html:
编辑:示例 php 脚本(使用 PDO,但它与 php-mysql 模块)
打印
The question is tagged as "mysql", so yes, MySQL's auto_increment can create groupwise sequential ids.
see http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html:
edit: example php script (using PDO, but it's the same game with the php-mysql module)
prints
这应该可行,但可能很慢:
我已将表称为“测试”;显然这需要正确设置。您必须使用临时表,因为 MySQL 不允许您使用正在更新的同一个表中的子查询。
This should work but is probably slow:
I have called the table 'test'; obviously that needs to be set correctly. You have to use a temporary table because MySQL will not let you use a subselect from the same table you are updating.