Я работаю над некоторым устаревшим кодом / базой данных и должен добавить поле в базу данных, которое будет записывать порядковый номер, связанный с этим (чужим) идентификатором.
Пример данных таблицы (текущий):
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, Kev
Это должно работать, но, вероятно, медленно:
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;
Я назвал таблицу «test»; очевидно, что нужно правильно установить. Вы должны использовать временную таблицу, потому что MySQL не позволит вам использовать подзаголовок из той же таблицы, которую вы обновляете.
Создать триггер:
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;
Вопрос помечен как «mysql», так что да, auto_increment MySQL может создавать групповые последовательные идентификаторы.
см. http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html :
Для таблиц MyISAM и BDB вы можете указать AUTO_INCREMENT на вторичном столбце в индексе с несколькими столбцами. В этом случае генерируемое значение для столбца AUTO_INCREMENT вычисляется как
MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. Это полезно, если вы хотите поместить данные в упорядоченные группы.
edit: пример 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"; }с$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"; }
печать
1 1 1a 1 2 1b 1 3 1c 2 1 2a 2 2 2b 1 4 1d