MySQL - 自动递减值

问题描述

假设我有一个表格（id是自动递增）：

  id | col1 | col2 
 1 | 'msg'| 'msg'
 2 | 'lol'| 'lol2'
 3 | 'xxx'| 'x'
  

现在，我想删除第2行， >

  id | col1 | col2 
 1 | 'msg'| 'msg'
 3 | 'xxx'| 'x'
  

事情是，我想得到的是：

  id | col1 | col2 
 1 | 'msg'| 'msg'
 2 | 'xxx'| 'x'
  

我如何以最简单的方式？

解决方案

您不应该这样做。

不要自动递增唯一标识符作为序号。

单词 表示标识符应该固定在永远 / p>

这些数字和枚举之间没有连接。

想象您想要按字母顺序选择记录。你珍贵的数字在哪里？
数据库不像一个有序列表，你可能认为。它不是一个具有以预定义顺序存储行的平面文件。它有完全不同的意识形态。数据库中的行没有任何顺序。并且只有在选择的时间，如果它是由 ORDER BY 子句显式设置。

此外，数据库应该做一个搜索您。所以你可以知道，对于过滤的行或不同的顺序，这个自动递增的数字将与真正的行位置无关。

如果你想枚举输出 - 这是一个表示层的工作。只需在PHP端添加一个计数器。

再次：这些数字应该标识一个特定的记录。如果您更改此号码，您将永远不会再找到您的记录。

以这个网站为例。 Stack Overflow用这样的数字标识了它的问题：

因此，假设您将此网页地址保存到书签。现在Jeff来和整理数据库。你按你的书签和土地在不同的问题。整个网站将成为一个可怕的混乱。

请记住：重新编号唯一标识符是邪恶的！

Let's say that I've got a table, like that (id is auto-increment):

id | col1 | col2
1  | 'msg'| 'msg'
2  | 'lol'| 'lol2'
3  | 'xxx'| 'x'

Now, I want to delete row number 2 and I get something like this

id | col1 | col2
1  | 'msg'| 'msg'
3  | 'xxx'| 'x'

The thing is, what I want to get is that:

id | col1 | col2
1  | 'msg'| 'msg'
2  | 'xxx'| 'x'

How can I do that in the EASIEST way (my knowledge about MySQL is very poor)?

解决方案

You shouldn't do that.
Do not take an auto-incremented unique identifier as an ordinal number.
The word "unique" means that the identifier should be stuck to its row forever.

There is no connection between these numbers and enumerating.
Imagine you want to select records in alphabetical order. Where would your precious numbers go?A database is not like an ordered list, as you probably think. It is not a flat file with rows stored in a predefined order. It has totally different ideology. Rows in the database do not have any order. And will be ordered only at select time, if it was explicitly set by ORDER BY clause.
Also, a database is supposed to do a search for you. So you can tell that with filtered rows or different ordering this auto-increment number will have absolutely nothing to do with the real rows positions.

If you want to enumerate the output - it's a presentation layer's job. Just add a counter on the PHP side.

And again: these numbers supposed to identify a certain record. If you change this number, you'd never find your record again.

Take this very site for example. Stack Overflow identifies its questions with such a number:

So, imagine you saved this page address to a bookmark. Now Jeff comes along and renumbers the whole database. You press your bookmark and land on the different question. Whole site would become a terrible mess.

Remember: Renumbering unique identifiers is evil!

这篇关于MySQL - 自动递减值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！