Default value for order field in mysql

Question

In this table, I have a field ( field_order ), which will serve as a way to determine the user-defined display order of the table rows. When inserting a new record, I would like to set this particular field with the row numbers in this table plus one

So, if the table has 3 rows, when entering a new default value for field_order should be 4.

What is the best way to set this value?

Simple counting of numbers inside insert statement?

Is there a constant of type CURRENT_TIMESTAMP for TIMESTAMP that returns this value?

EDIT . The reason for this is to sort the table by this particular field; and this field will be processed by the client-side user using jQuery sorting

Answer 1

Well, that’s why the decisions related to this issue are actually a bit nuanced. I went ahead and decided to answer, but also wanted to consider some of the nuances / details that are not yet considered.

Firstly, I would strongly advise you not to use auto_increment for the primary key, if not for some other reason, which is very easy for these auto increment identifiers to be discarded (for example, rollback transactions will prevent them from MySQL AUTO_INCREMENT not ROLLBACK . This will be deleted as mentioned by @Sebas).

Secondly, you should consider your storage engine. If you use MyISAM, you can get COUNT (*) tables very quickly (since MyISAM always knows how many rows are in each table). If you use INNODB, it is not. Depending on what you need this table for, you may leave with MyISAM. This is not the default mechanism, but, of course, you may encounter a requirement for which MyISAM will be the best choice.

The third thing you should ask yourself is: "Why?" Why do you need to store data this way? What does this actually give you? Do you really need this information in SQL? In the same SQL table table?

And if “Why” has an answer that justifies its use, then the last thing I ask is “how?” In particular, how are you going to do parallel inserts? How are you going to deal with deletion or kickbacks?

Given what you have, you actually need a table counting table ... but even so, there are some nuances (deletes, rollbacks, concurrency), as well as some decisions that need to be made (which storage engine you are using, can you avoid using MyISAM, which will be faster for counting stars?).

Moreover, I would ask why I need this in the first place. Maybe you really ... but it's a terribly strange claim.

IN LIGHT FROM YOUR IMAGE:

EDIT: the reason for this is to sort the table by this particular area; and this field will be controlled by the user on the client side using jQuery sorting

Essentially, you are requesting metadata about your tables. And I would recommend storing this metadata in a separate table or in a separate service in general (Elastic Search, Redis, etc.). You need to periodically update this separate table (or storage of key values). If you do this in SQL, you can use a trigger. Or you used something like Elastic Search, you can embed your data in SQL and ES at the same time. In any case, you have some difficult problems that you need to deal with (for example, possible consistency, concurrency, all nice things that can have unpleasant consequences when using triggers in MySQL).

If it were me, I would point out two things. Firstly, even Google does not always update COUNT(*) . "Showing lines 1-10 of approximately XYZ." They do this partly because they have more data that I believe you are doing, and partly because it is actually impractical (and very quickly becoming impracticable and not permissible) to calculate the exact COUNT(*) table and constant updates all the time.

So, either I completely changed my requirement, or used statistics that I can get quickly (if you use MyISAM for storage, continue to use count( * ) ... it will be very fast), or I would consider supporting the index of counting stars of my tables, which is periodically updated through some process (cron job, trigger, whatever) every couple of hours or every day or something like that.

In gratitude for this question ... there will never be a single canonical answer to this question. There are trade-offs that need to be made no matter how you decide to manage it. They can be a compromise in terms of consistency, latency, scalability, exact and approximate solutions, loss of INNODB in exchange for MyISAM ... but there will be compromises. And ultimately, the decision comes down to the fact that you are ready to trade in order to get your demand.

If it were me, I would probably have knocked down my claim. And if I did, I would most likely index it in Elastic Search and make sure it is updated every couple of hours or so. Is that what you should do? It depends. This is definitely not the “right answer”, as it is one answer (out of many) that will work if I can live with the fact that my COUNT(*) bit outdated.

Should you use Elastic Search for this? It depends. But you will have to deal with the tradeoffs that you ever go. It does not depend. And you will need to decide that you are ready to give up in order to get what you want. If this is not critical, bend the requirement.

Answer 2

There may be a better approach, but now I can only think about creating a second table containing the desired value and use triggers to create the corresponding inserts / deletes:

Here is an example:

 -- Let say this is your table create table tbl_test( id int unsigned not null auto_increment primary key, text varchar(50) ); -- Now, here 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 data this way at all? What does this actually give you? Actually, do you need this information in SQL? In the same table of the same SQL table?

If you only need to output this result, it is much easier to do this job: user variables.

Now let's say your table looks 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 indicate the value of the date and time at which each row was inserted, so if you sort it by this column, you will get the rows in the order in which they were inserted. But now: how to add this "incremental value"? Using a little trick with custom variables 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 large data sets, where, probably, you will use LIMIT ? Just initializing @n initial limit value:

 -- 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 that the initial question was to ask for an easy way to set the default sort order in the new record. Later, the user can configure this value "order field". DELETES and ROLLBACKS seem to have nothing to do with this.

Here is a simple solution. For the sort order field, set the default value to 0 and use the primary key as a secondary sort. Just change the sort order in the query as DESC. If you want the default functionality to be "first displayed last", use:

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

If you want to "display the last added last", use:

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

Answer 4

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

The selection request looks like this:

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

Until you apply the order, your request will lead to a chronological order.

If you want to apply custom field_order sorting, you must set it again by counting them from -X to 0 :

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

When changing, normal sorting occurs, and new lines will always be sorted chronologically at the end of the user sorting already in place:

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