Default value for order field in mysql

Question

In a given table I have a field (field_order) that will serve as way to define a custom order for showing the rows of the table. When inserting a new record I would like to set that particular field with the numbers of rows in that table plus one

So if the table has 3 rows, at the time of inserting a new one, the default value for field_order should be 4.

What would be the best approach to set that value?

A simple select count inside the insert statement?

Is there a constant like CURRENT_TIMESTAMP for TIMESTAMP datatype that returns that value?

EDIT: The reason behind this is is to be able to sort the table by that particular field; and that field would be manipulated by a user in client side using jQuery's sortable

Answer 1

Okay, so the solutions surrounding this question actually involve a bit of nuance. Went ahead and decided to answer, but also wanted to address some of the nuance/details that the comments aren't addressing yet.

First off, I would very strongly advise you against using auto_increment on the primary key, if for no other reason that that it's very easy for those auto increment ids to get thrown off (for example, rolled back transactions will interfere with them MySQL AUTO_INCREMENT does not ROLLBACK. So will deletes, as @Sebas mentioned).

Second, you have to consider your storage engine. If you are using MyISAM, you can very quickly obtain a COUNT(*) of the table (because MyISAM always knows how many rows are in each table). If you're using INNODB, that's not the case. Depending on what you need this table for, you may be able to get away with MyISAM. It's not the default engine, but it is certainly possible that you could encounter a requirement for which MyISAM would be a better choice.

The third thing you should ask yourself is, "Why?" Why do you need to store your data that way at all? What does that actually give you? Do you in fact need that information in SQL? In the same table of the same SQL table?

And if the "Why" has an answer that justifies its use, then the last thing I'd ask is "how?" In particular, how are you going to deal with concurrent inserts? How are you going to deal with deletes or rollbacks?

Given the requirement that you have, doing a count star of the table is basically necessary... but even then, there's some nuance involved (deletes, rollbacks, concurrency) and also some decisions to be made (which storage engine do you use; can you get away with using MyISAM, which will be faster for count stars?).

More than anything, though, I'd be question why I needed this in the first place. Maybe you really do... but that's an awfully strange requirement.

IN LIGHT OF YOUR EDIT:

EDIT: The reason behind this is is to be able to sort the table by that particular field; and that field would be manipulated by a user in client side using jQuery's sortable

Essentially what you are asking for is metadata about your tables. And I would recommend storing those metadata in a separate table, or in a separate service altogether (Elastic Search, Redis, etc). You would need to periodically update that separate table (or Key value store). If you were doing this in SQL, you could use a trigger. Or you used something like Elastic Search, you could insert your data into SQL and ES at the same time. Either way, you have some tricky issues you need to contend with (for example, eventual consistency, concurrency, all the glorious things that can backfire when you are using triggers in MySQL).

If it were me, I'd note two things. One, not even Google delivers an always up to date COUNT(*). "Showing rows 1-10 out of approximately XYZ." They do that in part because they have more data that I imagine you do, and in part because it actually is impractical (and very quickly becomes infeasible and prohibitive) to calculate an exact COUNT(*) of a table and keep it up to date at all times.

So, either I'd change my requirement entirely and leverage a statistic I can obtain quickly (if you are using MyISAM for storage, go ahead and use count( * )... it will be very fast) or I would consider maintaining an index of the count stars of my tables that periodically updates via some process (cron job, trigger, whatever) every couple of hours, or every day, or something along those lines.

Inre the bounty on this question... there will never be a single, canonical answer to this question. There are tradeoffs to be made no matter how you decide to manage it. They may be tradeoffs in terms of consistency, latency, scalability, precise vs approximate solutions, losing INNODB in exchange for MyISAM... but there will be tradeoffs. And ultimately the decision comes down to what you are willing to trade in order to get your requirement.

If it were me, I'd probably flex my requirement. And if I did, I'd probably end up indexing it in Elastic Search and make sure it was up to date every couple of hours or so. Is that what you should do? That depends. It certainly isn't a "right answer" as much as it is one answer (out of many) that would work if I could live with my count(*) getting a bit out of date.

Should you use Elastic Search for this? That depends. But you will be dealing with tradeoffs which ever way you go. That does not depend. And you will need to decide what you're willing to give up in order to get what you want. If it's not critical, flex the requirement.

Answer 2

There may be a better approach, but all I can think of right now is to create a second table that holds the value you need, and use triggers to make the appropriate inserts / deletes:

Here's an example:

-- Let's say this is your table
create table tbl_test(
    id int unsigned not null auto_increment primary key,
    text varchar(50)
);

-- Now, here's the table I propose.
-- It will be related to your original table using 'Id'
-- (If you're using InnoDB you can add the appropriate constraint
create table tbl_incremental_values(
    id int unsigned not null primary key,
    incremental_value int unsigned not null default 0
);

-- The triggers that make this work:
delimiter $$
create trigger trig_add_one after insert on tbl_test for each row
begin
    declare n int unsigned default 0;
    set n = (select count(*) from tbl_test);
    insert into tbl_incremental_values
        values (NEW.id, (n));
end $$

-- If you're using InnoDB tables and you've created a constraint that cascades
-- delete operations, skip this trigger
create trigger trig_remove before delete on tbl_test for each row
begin
    delete from tbl_incremental_values where id = OLD.id;
end $$
delimiter ;

Now, let's test it:

insert into tbl_test(text) values ('a'), ('b');

select a.*, b.incremental_value 
from tbl_test as a inner join tbl_incremental_values as b using (id);
-- Result:
--    id | text | incremental_value
--    ---+------+------------------
--    1  | a    | 1
--    2  | b    | 2

delete from tbl_test where text = 'b';
select a.*, b.incremental_value 
from tbl_test as a inner join tbl_incremental_values as b using (id);
-- Result:
--    id | text | incremental_value
--    ---+------+------------------
--    1  | a    | 1

insert into tbl_test(text) values ('c'), ('d');
select a.*, b.incremental_value 
from tbl_test as a inner join tbl_incremental_values as b using (id);
-- Result:
--    id | text | incremental_value
--    ---+------+------------------
--    1  | a    | 1
--    3  | c    | 2
--    4  | d    | 3

This will work fine for small datasets, but as evanv says in his answer:

Why?" Why do you need to store your data that way at all? What does that actually give you? Do you in fact need that information in SQL? In the same table of the same SQL table?

If all you need is to output that result, there's a much easier way to make this work: user variables.

Let's now say that your table is something like this:

create table tbl_test(
    id int unsigned not null auto_increment primary key,
    ts timestamp,
    text varchar(50)
);

insert into tbl_test(text) values('a');
insert into tbl_test(text) values('b');
insert into tbl_test(text) values('c');
insert into tbl_test(text) values('d');
delete from tbl_test where text = 'b';
insert into tbl_test(text) values('e');

The ts column will take the value of the date and time on which each row was inserted, so if you sort it by that column, you'll get the rows in the order they were inserted. But now: how to add that "incremental value"? Using a little trick with user variables it is possible:

select a.*
     , @n := @n + 1 as incremental_value
--     ^^^^^^^^^^^^ This will update the value of @n on each row
from (select @n := 0) as init -- <-- you need to initialize @n to zero
   , tbl_test as a
order by a.ts;
-- Result:
--   id | ts                  | text | incremental_value
--   ---+---------------------+------+----------------------
--   1  | xxxx-xx-xx xx:xx:xx | a    | 1
--   3  | xxxx-xx-xx xx:xx:xx | c    | 2
--   4  | xxxx-xx-xx xx:xx:xx | d    | 3
--   5  | xxxx-xx-xx xx-xx-xx | e    | 4

But now... how to deal with big datasets, where it's likely you'll use LIMIT? Simply by initializing @n to the start value of limit:

-- A dull example:

prepare stmt from 
    "select a.*, @n := @n + 1 as incremental_value
    from (select @n := ?) as init, tbl_test as a
    order by a.ts
    limit ?, ?";
-- The question marks work as "place holders" for values. If you're working
-- directly on MySQL CLI or MySQL workbench, you'll need to create user variables
-- to hold the values you want to use.
set @first_row = 2, @nrows = 2;
execute stmt using @first_row,   @first_row,   @nrows;
--                 ^^^^^^^^^^    ^^^^^^^^^^    ^^^^^^
--                 Initalizes    The "floor"   The number
--                 the @n        of the        of rows
--                 value         LIMIT         you want
--
-- Set @first_row to zero if you want to get the first @nrows rows
--
-- Result:
--   id | ts                  | text | incremental_value
--   ---+---------------------+------+----------------------
--   4  | xxxx-xx-xx xx:xx:xx | d    | 3
--   5  | xxxx-xx-xx xx-xx-xx | e    | 4
deallocate prepare stmt;

Answer 3

It seems like the original question was asking for an easy way to set a default sort order on a new record. Later on the user may adjust that "order field" value. Seems like DELETES and ROLLBACKS have nothing to do with this.

Here's a simple solution. For the sort order field, set your default value as 0, and use the primary key as your secondary sort. Simply change your sort order in the query to be DESC. If you want the default functionality to be "display most recently added first", then use:

SELECT * from my_table
WHERE user_id = :uid
ORDER BY field_order, primary_id DESC

If you want to "display most recently added last" use:

SELECT * from my_table
WHERE user_id = :uid
ORDER BY field_order DESC, primary_id

Answer 4

What I have done to avoid the SELECT COUNT(*) ... in the insert query is to have an unsorted state of the field_order column, let's say a default value of 0.

The select-query looks like:

SELECT * FROM my_table ... ORDER BY id_primary, field_order

As long as you don't apply a custom order, your query will result in chronological order.

When you want to apply custom sorting field_order should be re-setted by counting them from -X to 0:

id | sort
---+-----
1  | -2   
2  | -1   
3  |  0   

When altering occurs the custom sort remains, and new rows will always be sorted chronoligicaly at end of the custom sorting already in place:

id | sort
---+-----
1  | -2   
3  |  0   
4  |  0