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.
我正在研究一些遗留代码/数据库,需要在数据库中添加一个字段,该字段将记录与该(外部)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:
我需要添加一个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 ...
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?
有没有办法在SQL中实现这一点,或者我是否可以使用PHP脚本为我完成这项工作?
TIA, Kev
TIA,Kev
6
This should work but is probably slow:
这应该工作,但可能很慢:
CREATE temporary table seq ( id int, seq int);
INSERT INTO seq ( id, seq )
SELECT id,
(SELECT count(*) + 1 FROM test c
WHERE c.id < test.id AND c.account = test.account) as seq
FROM test;
UPDATE test INNER join seq ON test.id = seq.id SET test.seq = seq.seq;
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.
我把表称为“测试”;显然需要正确设置。您必须使用临时表,因为MySQL不允许您使用您正在更新的同一个表中的子选择。
5
Create a trigger:
创建一个触发器:
CREATE TRIGGER trg_mytable_bi
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
DECLARE nseq INT;
SELECT COALESCE(MAX(seq), 0) + 1
INTO nseq
FROM mytable
WHERE account = NEW.account;
SET NEW.seq = nseq;
END;
5
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:
这个问题被标记为“mysql”,所以是的,MySQL的auto_increment可以创建groupwise顺序id。请参阅http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html:
For
MyISAM and
BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as
MAX(auto_increment_column) + 1 WHERE prefix=given-prefix
. This is useful when you want to put data into ordered groups.
edit: example php script (using PDO, but it's the same game with the php-mysql module)
编辑:示例php脚本(使用PDO,但它与php-mysql模块的游戏相同)
$pdo = new PDO('mysql:host=...;dbname=...', '...', '...');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
// example table
$pdo->exec(
'CREATE TEMPORARY TABLE Foo (
id int auto_increment,
account int,
someotherstuff varchar(32),
primary key(account,id)
) engine=MyIsam'
);
// insert example data
$stmt = $pdo->prepare('INSERT INTO Foo (account,someotherstuff) VALUES (?,?)');
$stmt->execute(array(1, '1a'));
$stmt->execute(array(1, '1b'));
$stmt->execute(array(1, '1c'));
$stmt->execute(array(2, '2a'));
$stmt->execute(array(2, '2b'));
$stmt->execute(array(1, '1d'));
unset($stmt);
// query data
foreach( $pdo->query('SELECT account,id,someotherstuff FROM Foo') as $row ) {
echo $row['account'], ' ', $row['id'], ' ', $row['someotherstuff'], "\n";
}
prints
版画
1 1 1a
1 2 1b
1 3 1c
2 1 2a
2 2 2b
1 4 1d
本站翻译的文章,版权归属于本站,未经许可禁止转摘,转摘请注明本文地址:http://www.silva-art.net/blog/2009/10/21/ba85a52963c15d67f369eecb57185fc6.html。