如何在现有MYSQL表的两行之间添加一行? [英] How can i add a row between two rows in existing MYSQL table?

问题描述

我创建了一个包含50行的MYSQL表.
现在我需要在5和6之间添加一行.
但是主键应该在那之后更改5，新的第6行，之后是7,8
我尝试插入行-显示重复的主键

如何在现有表格的两行之间添加一行?

在此先感谢
Saranya1388

I created a MYSQL table having 50 rows.
Now I need to add a row between 5 and 6.
But the primary key should change after that 5, new row-6 ,after-wards 7,8
I tried insert row - shows duplicate primary key

How can I add a row between two rows in existing table?

Thanks in advance
Saranya1388

推荐答案

如果我没看错，您的mySql表带有行，因此:

If I read you right, you have a mySql table with rows thus:

1 me
2 you
3 them
4 him
5 her
6 it
7 whom
8 who

您要将其更改为:

You want to change this to:

1 me
2 you
3 them
4 him
5 her
6 my new row
7 it
8 whom
9 who

如果这是正确的，那么就没有自动的方法可以做到这一点.甚至不可能，这取决于您的数据库设计.您使用自动号码了吗?如果是这样，那么不能，您不能这样做.是否在其他表格中使用了此字段?如果是这样，那么不，我不建议这样做.
如果没有，那么您必须:
1)检索表中的所有记录.
2)找到需要更新的最后一个号码.
3)从此倒退，使用SQL更新命令将每个数字加1，直到到达插入点.
4)插入新记录.

但是，我真的不建议这样做.相反，如果您需要按字段排序，则添加一个.并如上所述进行更新.
在现实生活中，对于多用户数据库，这种事情对于数据完整性来说是严重不健康的！

If this is correct then there is no automatic way to do it. It may not even be possible, depending on your DB design. Did you use auto number? If so, then no, you can''t do it. Is this field used in any other tables? If so, then no, I don''t recommend it.
If not, then you have to:
1) Retrieve all records in the table.
2) Find the last number that needs to be updated.
3) Working backwards from this, increment each number by one, using SQL update commands, until you reach your insert point.
4) Insert your new record.

However, I would really not recommend this. Instead, if you need an order by field, then add one. And update it as described above.
In a real life situation, with multi-user database, this kind of thing is seriously unhealthy for data integrity!

这篇关于如何在现有MYSQL表的两行之间添加一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！