mysql不能 读取自动递增的值,MySQL更新递增（不自动递增）列值

I have a table with 4 columns, one of which is called "order". "Order" is not auto-incremented, but another column, "mapping_id" is auto-incremented. The other columns include "quiz_id" and "question_id".

I want to change all the values of "order" to go in the same ascending order as the question_id field. My application only sorts based on this "order" column, so the only way I can re-sort is to edit the values in this column.

What I want to know is how to write an update query to change all the values of "order" to go in an ascending number sequence. I thought of just matching the value of "order" to that of "question_id", but question_id starts at 2 instead of 1, and "order" must begin at 1. Thanks for any help you can provide!

解决方案

Something like the following might do what you are looking for. It will assign incrementing values to order based on the question_id ordering:

select @ordval := 0;

update thetable set `order` =

(select @ordval := @ordval + 1) order by question_id;