mysql8常用sql语句

查询结果带行号

-- 表名为  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  ;