是否可以使用INSERT命令将数据插入到表的中间部分? [英] Is it possible to insert data into the mid section of a table using the INSERT command?

问题描述

示例:

我想做一个INSERT添加---> 2 | some_data2. 是否有SQL命令或执行此操作的某种方式?

I want to do an INSERT to add ---> 2 | some_data2. Is there an SQL command or some way of doing this?

好的.也许如果我向我解释了我想完成的所有事情，您会明白为什么我要尝试做我想做的事情.我在php中创建了一个Web管理页面，该页面仅显示数据库表中的数据.现在这些显示的行是可拖动的".例如，您可以将第2行拖到第4行.现在在"mouseup"事件上，我想将新订单保存在数据库中.

[edit]
OK. Maybe if i explain every thing what i want to accomplish you'll see why i'm trying to do what i want to do. I creating a Web admin page in php that simply displays data from a database table.now these displayed rows are "draggable". example,you can drag row #2 to say, row #4. now on the "mouseup" event, i want to save the new order in the database.

1 | data1 -------------------------> 1 |数据1
2 | data2 -------拖动后-----> 3 | data3
3 | data3 -------------------------> 4 |数据4
4 | data4 -------------------------> 2 |数据2
看到我的问题了吗?我不能只为已拖动的一个值更新INDEX列中的值.我将不得不更新所有拖过的值. 3必须成为2、4、3和2必须成为新的4.多数民众赞成在sql更新过多(尤其是在表较大的情况下).因此，我想删除第2行并将其插入正确的位置.

1| data1 -------------------------> 1| data1
2| data2 -------After Drag-----> 3| data3
3| data3 -------------------------> 4| data4
4| data4 -------------------------> 2| data2
See my problem? i cant merely just update the value in the INDEX column for the one value that was dragged. I'll have to update all the values that were dragged over. 3 has to become a 2, 4 a 3 and 2 has to become the new 4. Thats too many sql updates (expecially if the table is larger). So i want to remove row #2 and insert it in its correct location.

推荐答案

好吧，我再次回答是因为您的更新显示您所提出的问题与每个人都认为的完全不同.

Okay, I'm answering again because your update shows you are asking a completely different question than everyone thought you were.

您描述的方法-更新移动的行的索引及其后列表中的每个索引-正是这里的正确方法.

The method that you described -- update the index of the moved row and every one in the list after it -- is exactly the right method here.

为什么您说"SQL更新太多"?只要您将 MySQL索引应用于index列中，即使对于非常大的列表(它们的数量级为毫秒)，这些更新也将迅速减轻.

Why do you say that "that's too many sql updates"? As long as you have a MySQL index applied to the index column, those updates will be lightening fast, even for very large lists (they will be on the order of milliseconds).

我认为您正在预先优化，或者您没有定义索引.要添加索引(如果还没有索引)，请运行以下命令:

I think you are either pre-optimizing, or you don't have an index defined. To add an index (if you don't already have one), run this command:

CREATE INDEX row_index_index ON `some_table` (`index`);

您还可能需要重新考虑将索引"列重命名为位置"之类，以免将名称与实际的MySQL索引混淆.

You also might want to reconsider renaming your "index" column to something like "position" to avoid confusion about nomenclature with an actual MySQL index.

这篇关于是否可以使用INSERT命令将数据插入到表的中间部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！