是否有使用 SQL Lite 交换主键值的最佳实践方法？
作为一点背景知识，我正在使用一个 SQL Lite 数据库，该数据库被一个闭源 UI 所使用，该 UI 不会通过方便的时间戳列对结果进行排序（哎呀，感谢诺基亚！） - 它只使用默认值排序，对应于主键，主键是一个普通的自动递增“id”列。
我很容易就有了当前和所需 id 值的映射，但应用映射是我当前的问题。看来我无法交换这些值，因为更新一次处理一行，这会暂时导致重复值。我尝试使用带有临时无序值的 case 子句的更新语句，但由于每行仅处理一次，这显然不起作用。因此，我已经达到需要 3 个更新语句来交换一对值的程度，这远非理想，因为我希望它能够很好地扩展。
除此之外，设置了许多触发器，这使得在新表中添加/删除行成为一个复杂的问题，除非我可以在表复制和删除导致的任何添加/删除期间禁用这些触发器。删除，这就是为什么我还没有采用这条途径。
我想我的下一行查询将是一个带有新 id 的新列，然后找到一种方法在删除旧列之前将主索引移至该列，但我将其扔在那里，以防有人可以提供更好的解决方案将节省我一些时间:)
As a bit of background, I'm working with a SQL Lite database that is being consumed by a closed-source UI that doesn't order the results by the handy timestamp column (gee, thanks Nokia!) - it just uses the default ordering, which corresponds to the primary key, which is a vanilla auto-incrementing 'id' column.
I easily have a map of the current and desired id values, but applying the mapping is my current problem. It seems I cannot swap the values as an update processes rows one at a time, which would temporarily result in a duplicate value. I've tried using an update statement with case clauses using a temporary out-of-sequence value, but as each row is only processed once this obviously doesn't work. Thus I've reached the point of needing 3 update statements to swap a pair of values, which is far from ideal as I want this to scale well.
Compounded to this, there are a number of triggers set up which makes adding/deleting rows into a new table a complex problem unless I can disable those for the duration of any additions/deletions resulting from table duplication & deletion, which is why I haven't pursued that avenue yet.
I'm thinking my next line of enquiry will be a new column with the new ids then finding a way to move the primary index to it before removing the old column, but I'm throwing this out there in case anyone can offer up a better solution that will save me some time :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论