0

Web dev here, typical scenario at work - I have a list of items that can be reordered using an UI. Upon reordering, I want the database to update.

Using MySQL I've always found this process very awkward. I just give the table an "order" column which is an integer, then I have to programatically make sure that all of the rows in the entire table update their order to match the single item that was reordered.

This always seemed like something that could be built into the language.

Something like:

UPDATE table REORDER order TO 3 WHERE id=1.

And I imagine that an order would be its own type - it would be an integer but it would also have to be sequential.

Does MySQL support this and I'm not aware of it? If it doesn't, what is the rationale for not supporting it? Do any other database solutions support this functionality?

8
  • What version of MySQL are you using?
    – Dai
    Sep 24, 2022 at 15:07
  • If nothing else, you could use a UNIQUE constraint, which would prevent rows sharing the same sort-order, and any gaps between items are inconsequential.
    – Dai
    Sep 24, 2022 at 15:07
  • Finally, have you considered alternative approaches, such as having each row only store a reference to the previous item (i.e. a linked-list) instead of its absolute-sort-order?
    – Dai
    Sep 24, 2022 at 15:08
  • I like the linked-list solution @Dai, thanks for that suggestion! Not sure how performance would be but I'm thinking theoretically and I can always optimize later. And I'm not using any particular version - just thinking about this for the future. I would like to use the latest stable version. Sep 24, 2022 at 15:12
  • 1
    There is no order of rows in a table to reorder, so this question makes no sense. What it sounds like you want is an extra column in your table that will be used by default in your order by clauses in your select statements, this column can be updated like any other column. But you must make sure you write order by my_order to get the rows out in that order. Sep 24, 2022 at 16:24

3 Answers 3

2

To answer your question directly, no, MySQL does not have this feature, and no other relational database should have this feature. The definition of a relation is an unordered set of rows. There is no order implied, just the order you implement by assigning values in some column.

You could make it easier to change the order by using a FLOAT column instead of an integer. Then if you want to move a row in between the current row 3 and 4, you assign the column sortorder = 3.5. Or any other fractional value between 3 and 4. So you only need to change the sortorder value in one row instead of lots of rows.

With FLOAT you can keep subdividing until you run out of precision in the float number.

P.S.: I recommend not to use the column name of order because it's a reserved keyword.

1

As others have noted (and are a little concentrated on) is this isn't really a question of data integrity as it is logical correctness that you were hoping to have the database automatically maintain for you. I understand the problem you're trying to solve, from a maintenance perspective.

Does MySQL support this and I'm not aware of it? Do any other database solutions support this functionality?

I've never heard of any of the modern relational database management systems supporting this concept natively.

If it doesn't, what is the rationale for not supporting it?

Generally things of this nature aren't supported out of the box in a relational database system for at least one of three reasons:

  1. It's computationally heavy, and therefore better off to not offer as a feature out of the box when it's bound to be a fairly common performance bottleneck.

  2. There's no clear determinism to how such a feature should operate in every scenario. Features need to be rather code complete and can't have ambiguity for different scenarios. One ambiguity that comes to mind with an Order data type is how should it handle the scenario when you update a row to an existing value that another row is already set to? Some people would argue it should push down that existing row, others would argue it should be pushed up in order, and others would argue the feature should throw an error and not allow this kind of UPDATE to occur. There's no perfect deterministic solution to that issue, and the third option would quickly make such a feature useless, in my opinion.

  3. Demand for that use case is not common enough to make it worth the time for the maintainers of the database system to focus on solving that problem now. Database systems have very complex engines under the hood, and there's plenty of other more common (yet possibly even boring) issues / features that the developers will yield more positive return on from the end users, by focusing on.

0

A SELECT (without ORDER BY), by definition, delivers an unordered set of rows.

You should use ORDER BY on the SELECT to determine the ordering at runtime.

If you cannot use an ORDER BY, then you cannot expect SQL to be an easy solution. For example, linked lists are not efficiently handled in SQL. (Cf: CONNECT BY in Oracle, CTEs in newer MySQLs, etc)

Furthermore, if you have an ordering column, UPDATEing that column on every row is a costly operation. Think again before barking up that tree.

2
  • Agreed on using ORDER BY - I wouldn't expect the table to have a default ordering just because it had an order column. In theory there could be multiple order columns and you could choose which one to ORDER BY. Out of all of the solutions I've found, CTEs seem to be the one that most resonates with me. Is there a reason you discourage this solution? Sep 24, 2022 at 22:13
  • @NickManning - Performance on large tables may be poor.
    – Rick James
    Sep 25, 2022 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.