update inner join mysql where 链表更新

哎,考虑到自增id不容易用在分布复制中,手欠用UUID做了主键。而mysql好像没有uuid类型只能用字符串,感觉好不方便,后来看到tidb是支持自增id的,而且是分布式,于是决定换回自增id,这就有更新已有数据的guid对应的自增id的要求,记录一下语句。

update tags2topic 
inner join tags on tags2topic.tagguid = tags.aguid
set tags2topic.tagid = tags.id 
where tags2topic.tagguid = tags.aguid
tags数据表
# id, aguid, label, icon
'1', '4dfe0be9-6b9f-4b68-b67b-10fa72671de7', '街拍美腿', NULL
'2', '7083bf97-25f4-4e5a-a7a6-c18e693eae3f', '高清街拍', NULL
'3', 'bdfbb4f9-0c53-4fc0-ae48-478d0d39a1da', '丝足诱惑', NULL
'4', 'a80e3e50-25a2-4db6-849d-ea880558dc30', '丝袜高清', NULL
'5', '5e572a7a-e9d3-48fe-ae72-63a01595668a', '高挑美女', NULL
'6', '6bfffed3-cdb3-4c70-908a-80239eb94870', '角色COS', NULL
'7', 'a8ce5edc-abe2-4ad9-9d57-3947ebbe31db', '职业正装', NULL
'8', 'f1b9e628-177b-4199-b3d6-8dd771dab2ee', '修长美腿', NULL
'9', '512c8a73-5bd5-4ac2-8dfb-10c7b4eaffac', '养眼牛仔', NULL
'10', '72507b15-bdf2-4a59-9637-94b58848e475', '丝袜高跟', NULL
'11', '96f5e0b1-94f5-497e-b894-364c84a0e8d3', '自然风景', NULL
'12', '652f9685-721b-41c0-97be-60f908ffed4b', '人文建筑', NULL
'13', '008cf9bd-6d7e-49d8-8c52-617f829a865a', '浩瀚星空', NULL
'14', 'fcddbfeb-758c-4a17-86b7-254df67e47c5', '微观世界', NULL
'15', 'a76ae46c-eccc-43c6-9e47-80406897c489', '奇趣动物', NULL
tags2topic数据表 部分数据。
# aguid, tagguid, topicguid, createtime, tagid
'48789a37-0f80-4c74-a5ea-2a676f0d1d43', '5e572a7a-e9d3-48fe-ae72-63a01595668a', 'b0816f43-ee5f-4242-b400-de46594a68a2', '2019-02-22 10:48:37', '5'
'a41d05aa-2365-4f56-869f-1659ab81bd41', '6bfffed3-cdb3-4c70-908a-80239eb94870', 'efe99bcf-bcf8-47b4-8ee7-ba66d628d16f', '2019-02-22 10:56:25', '6'
'4b64d139-c8d8-43c9-ae8c-bbcc28c30861', '5e572a7a-e9d3-48fe-ae72-63a01595668a', 'efe99bcf-bcf8-47b4-8ee7-ba66d628d16f', '2019-02-22 10:56:25', '5'
'6bf2a2d8-43f4-4ca6-a1e7-e66e1c4a9a34', '6bfffed3-cdb3-4c70-908a-80239eb94870', 'f65aac0d-df09-4167-a72b-09faf526b42f', '2019-02-22 11:05:51', '6'
'16f0b761-6180-4bf9-803e-0bdf60c291c4', '5e572a7a-e9d3-48fe-ae72-63a01595668a', 'f65aac0d-df09-4167-a72b-09faf526b42f', '2019-02-22 11:05:51', '5'
'99b5c959-1380-4fc4-bc5c-9274a6a7f740', '6bfffed3-cdb3-4c70-908a-80239eb94870', 'ee1a4eb0-5e98-457d-99bd-7ae7e26d113d', '2019-02-22 14:41:18', '6'
'63a52b59-2a47-4a69-84de-d8d90e01e33e', '5e572a7a-e9d3-48fe-ae72-63a01595668a', 'ee1a4eb0-5e98-457d-99bd-7ae7e26d113d', '2019-02-22 14:41:18', '5'
'ed41f2e8-2249-41e9-a596-c150d90ed9cd', '6bfffed3-cdb3-4c70-908a-80239eb94870', '0d22bba7-b448-4418-b6ec-4f1e7e834078', '2019-02-22 14:45:26', '6'
'951ae854-e3ab-418e-bdb4-db574c3cfc88', '5e572a7a-e9d3-48fe-ae72-63a01595668a', '0d22bba7-b448-4418-b6ec-4f1e7e834078', '2019-02-22 14:45:26', '5'
'815124b4-deef-4e8c-bc8e-db691ad51be9', '6bfffed3-cdb3-4c70-908a-80239eb94870', '2c67e02a-8698-49a9-b8d1-4e61e4296935', '2019-02-22 14:47:27', '6'

```

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券