查詢結果帶行號

-- 表名為??mi_user， 假設包含列?id ，address ?

SELECT ?ROW_NUMBER() OVER (ORDER BY id) AS row_num, t.id, t.address ?FROM mi_user t ;
?
?SELECT ?ROW_NUMBER() OVER ( ) AS row_num, t.id, t.address ?FROM mi_user t ;

更新某列數據為行號

-- 表名：mi_user ， 排序的列id,保存行數的列row_num
SET @row_nu := 0;

UPDATE mi_user?
JOIN ( SELECT ?*, ?@row_nu := @row_nu + 1 AS rn FROM mi_user ORDER BY id ) AS ranked
USING (id)
SET mi_user.row_num = ranked.rn ?;
?