当前位置：文江博客话题详情

反转sql表中的id

发布于 2024-10-07 08:41:58 字数 204 浏览 3 评论 0原文

我有一个带有主 ID 的表
1 西默
2 帕万
3深
4只羊
5人
我想颠倒这个顺序
5 西默尔
4 帕万
3深
2只羊
1 man

我如何通过编程或 SQL 来做到这一点？我不想在 sql 中使用 order by

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷你仙 2024-10-14 08:41:58

要么：

1）选择按 id 降序排列的所有行，然后将它们按升序插入到新表中，该表的 AUTO_INCRMENT 将以相反的顺序分配标识符。现在，您可以将这些行及其新标识符复制回原始表。

2) 选择按 id 降序排列的所有行到程序中，删除它们，然后使用新的 ID 重新插入它们：

$sql = "SELECT id, name FROM table ORDER BY id DESC";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}

//empty the table, to avoid primary key conflicts
mysql_query("TRUNCATE TABLE table");    

$i = 1;
foreach ($rows as $row) {
  mysql_query("INSERT INTO table (id, name) VALUES ($i, '" . mysql_real_escape_string($row['name']) . ")");
  $i++;
}

Either:

1) Select all the rows ordered by id in descending order, then insert them into a new table in ascending order, and that table's AUTO_INCREMENT will assign the identifiers in reverse order. Now you can copy those rows back to the original table with their new identifiers.

2) Select all the rows ordered by id in descending order into a program, delete them, and reinsert them with the new IDs:

$sql = "SELECT id, name FROM table ORDER BY id DESC";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}

//empty the table, to avoid primary key conflicts
mysql_query("TRUNCATE TABLE table");    

$i = 1;
foreach ($rows as $row) {
  mysql_query("INSERT INTO table (id, name) VALUES ($i, '" . mysql_real_escape_string($row['name']) . ")");
  $i++;
}

回复收藏 0 原文

请帮我爱他 2024-10-14 08:41:58

我不想在 sql 中使用 order by

但这正是 SQL 提供 ORDER BY 子句的原因。

您只是想反转当前数据的顺序还是希望系统为新记录分配递减的序列号？

前者将是后者的先决条件，所以......

UPDATE yourtable SET id=(4294967295-id);

请注意，虽然您可以控制每个新自动增量值与前一个值的差异量（通过指定 auto_increment_increment），但简单地将其设置为 -1 并不能解决问题问题是，这个增量所应用的基值是 SELECT MAX(id) FROM yourtable - 所以即使 mysql 允许你指定 auto_increment_increment 为 -1，你最终也会得到重复的行，因此你需要复制架构中所有对 auto_increment 类型的引用都使用直整数，并使用序列生成器。然后您需要重写所有代码以使用序列生成器而不是 INSERT_ID() / mysql_insert_id()。

使用 ORDER BY 不是更简单吗？

i dont want to use order by in sql

But this is exactly the reason that SQL provides the ORDER BY clause.

Do you simply want to reverse the order of the current data or do you want your system ro assign decreasing sequence numbers to new records?

The former would be a pre-requisite for the latter so....

UPDATE yourtable SET id=(4294967295-id);

Note that while you can control the amount by which each new autoincrement value differs from the previous (by specifying the auto_increment_increment), simply setting this to -1 would not solve the problem as the base value this delta is applied to is SELECT MAX(id) FROM yourtable - so even if mysql allowed you to specify an auto_increment_increment of -1, you'd end up with duplicate rows, therefore you'd need to repliace all references to auto_increment types in your schema with straight integers and use a sequence generator instead. Then you'd need to rewrite all your code to use the sequence generator instead of INSERT_ID() / mysql_insert_id().

Wouldn't it be simpler to just use ORDER BY?

回复收藏 0 原文

~没有更多了~