问mysql中order字段的默认值

EN

-- 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 ;

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

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');

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

-- 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;

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

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