TiDB3.0.0 GA
建表语句如下:
CREATE TABLE `topic` (
`platformid` varchar(255) DEFAULT NULL,
`appid` varchar(255) DEFAULT NULL,
`topicid` varchar(255) DEFAULT NULL,
`content_type` int(11) DEFAULT NULL,
`video_type` int(11) DEFAULT NULL,
`title` varchar(2000) DEFAULT NULL,
`url` varchar(2000) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`create_time` timestamp NULL DEFAULT NULL,
`pub_time` timestamp NULL DEFAULT NULL,
`summary` varchar(5000) DEFAULT NULL,
`content` varchar(5000) DEFAULT NULL,
`category` varchar(5000) DEFAULT NULL,
`tags` varchar(5000) DEFAULT NULL,
`keywords` varchar(5000) DEFAULT NULL,
`topic_contentids` varchar(2000) DEFAULT NULL,
`style_card_source_channelid` varchar(255) DEFAULT NULL,
`style_card_type` int(11) DEFAULT NULL,
`source_type` int(11) DEFAULT NULL,
`source_id` varchar(255) DEFAULT NULL,
`fingerprint` bigint(20) DEFAULT NULL,
`m_create_time` timestamp NULL DEFAULT NULL,
`m_update_time` timestamp NULL DEFAULT NULL,
UNIQUE KEY `u_pat` (`platformid`,`appid`,`topicid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
执行SQL语句如下:
set @rowidex := 1;
set @last_fingerprint := -1;
select
platformid ,
appid ,
topicid ,
content_type ,
title ,
url ,
author ,
create_time ,
pub_time ,
summary ,
category ,
tags ,
keywords ,
topic_contentids ,
style_card_source_channelid ,
style_card_type ,
source_type ,
source_id ,
fingerprint ,
if(@last_fingerprint = fingerprint , @rowidex := @rowidex + 1 , @rowidex := 1) row_num ,
@last_fingerprint := fingerprint last_fingerprint ,
now() m_create_time ,
now() m_update_time
from topic
order by fingerprint
;
这条sql加上limit语句row_num返回正常,去掉limit的话不准确了
下图没有limit的sql执行结果:
下图是带limit的sql执行结果