mysql sorts according to time and updates a field value

mysqlsql database

Now there is a table with 4000 pieces of data. One of the fields is order_id, and the other field is pub_time. It turns out that order_id is automatically +1 based on the order of data insertion. Now I want to increment it based on pub_time. ,
Is there any good way to update the value of order_id to increase? ? ? It is not updating the newly added data, it is updating the existing 4000 data in the table

Reply content:

mysql library, right? Just upload the script:

update table t
inner join
(SELECT @rownum := @rownum + 1 AS rank, t.order_id
FROM Table t, (SELECT @rownum := 5000) r
order by pub_time asc) c on c.order_id = t.order_id
set t.order_id = c.rank;

The basic logic is to sort and generate serial numbers based on pub_time, and then update to the original table.

One thing to note here is that in order to avoid order_id primary key conflicts during the update process, it is best to set the initial value of rank larger. The initial value of rowrum here is 5000.

I’m not very familiar with the sql command. I don’t know if it’s possible. If so, I think writing a script in PHP will do the trick

That is to say, your order_id was set to auto-increment when inserting data. Now you want to rearrange the data according to the value of pub_time and re-update the value of order_id, right? . ?

If this is what I mean, you should use code to solve the problem instead of using sql statements. . At least I haven't figured out how to solve it with an update statement.

I don’t understand what you mean. Do you want to update the value of order_id based on the incrementing value of pub_time?