MYSQL 添加行号&将行号写入到主键的列_mysql 加行号做主键

MYSQL 添加行号

SELECT 
  @rownum:= @rownum + 1 AS rownum, a.*
FROM
  (SELECT @rownum := 0) t,is_afxt.hk_vehicle a
 
--或者(假设CREATED_TIME日期列数据不重复)
select 
(select count(1)+1 from is_afxt.hk_vehicle b where b.CREATED_TIME < a.CREATED_TIME) rownum ,a.*
from  is_afxt.hk_vehicle a
order by a.CREATED_TIME asc

 将行号写入到主键的列

 
--①获取行号
with hf 
as(
	select 
	(select count(1)+1 from is_afxt.hk_vehicle b where b.CREATED_TIME < a.CREATED_TIME) rownum ,a.*
	from  is_afxt.hk_vehicle a
	order by a.CREATED_TIME asc
)
--②将行号写入主键列
update is_afxt.hk_vehicle a left join hf b on a.CREATED_TIME=b.CREATED_TIME
set a.SID =b.rownum