修改主键的类型是否锁表

Hacker_Yv76YjBL · 2025 年8 月 26 日 04:08

【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.6
问题一：
表的主键信息为：id int(11) primary key NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’；
想将表的主键类型调整为bigint，执行语句是否会锁表，是否可以秒加；
执行语句：alter table xxx modify column id bigint NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’；
问题二：INFORMATION_SCHEMA.tables系统表的AUTO_INCREMENT信息不准
执行语句：
select a.table_schema,a.table_name,b.column_name,b.data_type,b.column_type ,a.AUTO_INCREMENT ,b.extra
from INFORMATION_SCHEMA.tables a left outer join INFORMATION_SCHEMA.columns b
on a.table_schema=b.table_schema and a.table_name = b.table_name
where b.column_key=‘PRI’ and a.table_schema not in (‘information_schema’ , ‘performance_schema’, ‘sys’, ‘mysql’,‘test’,‘METRICS_SCHEMA’,‘INFORMATION_SCHEMA’)
and b.data_type !=‘bigint’
order by a.AUTO_INCREMENT desc;

但表的自增id已经为19360583

对表进行了统计信息分析后仍然不准确。

托马斯滑板鞋 · 2025 年8 月 26 日 05:49

问题一我试了下
autocommit 设置为OFF，显示打开事务好像没用，DDL会直接提交

create table t1 (id int primary key NOT NULL AUTO_INCREMENT);
set autocommit =OFF;
begin ; 
alter table t1 modify column id bigint NOT NULL AUTO_INCREMENT ; （到这一步，从另一个窗口查看表结构已被更改）
rollback; （没用）

按TiDB文档的说法，应该不会长时间锁表

Miracle · 2025 年8 月 26 日 05:51

tidb的ddl都是online ddl，不会锁表。你提供的这个ddl如果没有dml阻塞，应该是秒加的

WalterWj · 2025 年8 月 26 日 05:57

(root@127.0.0.1) [test]>CREATE TABLE `subscriptions` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `createdat` datetime(3) NOT NULL,
    ->   `updatedat` datetime(3) NOT NULL,
    ->   `deletedat` datetime(3) DEFAULT NULL,
    ->   `userid` bigint(20) NOT NULL,
    ->   `topic` char(25) COLLATE utf8mb4_unicode_ci NOT NULL,
    ->   `delseqid` bigint(20) DEFAULT '0' COMMENT '删除seqid',
    ->   `recvseqid` int(11) DEFAULT '0',
    ->   `readseqid` int(11) DEFAULT '0',
    ->   `modegwant` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `modegwin` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `domain` tinyint(1) NOT NULL DEFAULT '0',
    ->   `crvseqid` int(11) NOT NULL DEFAULT '0',
    ->   `delseqid` bigint(20) NOT NULL DEFAULT '0' ,
    ->   `isdisplay` tinyint(1) NOT NULL DEFAULT '1' ,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `subscriptions_topic_userid` (`topic`,`userid`),
    ->   KEY `subscriptions_deletedat` (`deletedat`),
    ->   KEY `userid` (`userid`),
    ->   KEY `idx_user_deletedat` (`userid`,`deletedat`),
    ->   KEY `idx_userid_domain_deletedat` (`userid`,`domain`,`deletedat`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1;
ERROR 1060 (42S21): Duplicate column name 'delseqid'
(root@127.0.0.1) [test]>
(root@127.0.0.1) [test]>CREATE TABLE `subscriptions` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `createdat` datetime(3) NOT NULL,
    ->   `updatedat` datetime(3) NOT NULL,
    ->   `deletedat` datetime(3) DEFAULT NULL,
    ->   `userid` bigint(20) NOT NULL,
    ->   `topic` char(25) COLLATE utf8mb4_unicode_ci NOT NULL,
    ->   `delseqid` bigint(20) DEFAULT '0' COMMENT '删除seqid',
    ->   `recvseqid` int(11) DEFAULT '0',
    ->   `readseqid` int(11) DEFAULT '0',
    ->   `modegwant` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `modegwin` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `domain` tinyint(1) NOT NULL DEFAULT '0',
    ->   `csrecvseqid` int(11) NOT NULL DEFAULT '0',
    ->   `delseqid` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户清除聊天记录的最大seqid',
    ->   `isdisplay` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否展示会话信息 0 显示 1 不显示',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `subscriptions_topic_userid` (`topic`,`userid`),
    ->   KEY `subscriptions_deletedat` (`deletedat`),
    ->   KEY `userid` (`userid`),
    ->   KEY `idx_user_deletedat` (`userid`,`deletedat`),
    ->   KEY `idx_userid_domain_deletedat` (`userid`,`domain`,`deletedat`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1;
ERROR 1060 (42S21): Duplicate column name 'delseqid'
(root@127.0.0.1) [test]>
(root@127.0.0.1) [test]>
(root@127.0.0.1) [test]>CREATE TABLE `subscriptions` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `createdat` datetime(3) NOT NULL,
    ->   `updatedat` datetime(3) NOT NULL,
    ->   `deletedat` datetime(3) DEFAULT NULL,
    ->   `userid` bigint(20) NOT NULL,
    ->   `topic` char(25) COLLATE utf8mb4_unicode_ci NOT NULL,
    ->   `delseqid` bigint(20) DEFAULT '0' COMMENT '删除seqid',
    ->   `recvseqid` int(11) DEFAULT '0',
    ->   `readseqid` int(11) DEFAULT '0',
    ->   `modegwant` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `modegwin` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `domain` tinyint(1) NOT NULL DEFAULT '0',
    ->   `csrecvseqid` int(11) NOT NULL DEFAULT '0',
    ->   `isdisplay` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否展示会话信息 0 显示 1 不显示',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `subscriptions_topic_userid` (`topic`,`userid`),
    ->   KEY `subscriptions_deletedat` (`deletedat`),
    ->   KEY `userid` (`userid`),
    ->   KEY `idx_user_deletedat` (`userid`,`deletedat`),
    ->   KEY `idx_userid_domain_deletedat` (`userid`,`domain`,`deletedat`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1;
LL, 1003, 'topicC', 0, 30, 25, 'wantC', 'winC', 0, 300, 0),
('2024-08-01 10:03:00.000', '2024-08-01 10:13:00.000', NULL, 1004, 'topicD', 0, 40, 35, NULL, NULL, 1, 400, 1),
('2024-08-01 10:04:00.000', '2024-08-01 10:14:00.000', NULL, 1005, 'topicE', 0, 50, 45, 'wantE', NULL, 0, 500, 1),
('2024-08-01 10:05:00.000', '2024-08-01 10:15:00.000', NULL, 1006, 'topicF', 0, 60, 55, NULL, 'winF', 1, 600, 0),
('2024-08-01 10:06:00.000', '2024-08-01 10:16:00.000', NULL, 1007, 'topicG', 0, 70, 65, 'wantG', 'winG', 0, 700, 1),
('2024-08-01 10:07:00.000', '2024-08-01 10:17:00.000', NULL, 1008, 'topicH', 0, 80, 75, NULL, NULL, 1, 800, 1),
('2024-08-01 10:08:00.000', '2024-08-01 10:18:00.000', NULL, 1009, 'topicI', 0, 90, 85, 'wantI', 'winI', 0, 900, 0),
('2024-08-01 10:09:00.000', '2024-08-01 10:19:00.000', NULL, 1010, 'topicJ', 0, 100, 95, 'wantJ', NULL, 1, 1000, 1);

ALTER TABLE `subscriptions`
MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected, 6 warnings (0.10 sec)

(root@127.0.0.1) [test]>
(root@127.0.0.1) [test]>INSERT INTO `subscriptions`
    -> (`createdat`, `updatedat`, `deletedat`, `userid`, `topic`, `delseqid`, `recvseqid`, `readseqid`, `modegwant`, `modegwin`, `domain`, `csrecvseqid`, `isdisplay`)
    -> VALUES
    -> ('2024-08-01 10:00:00.000', '2024-08-01 10:10:00.000', NULL, 1001, 'topicA', 0, 10, 5, 'wantA', 'winA', 0, 100, 1),
    -> ('2024-08-01 10:01:00.000', '2024-08-01 10:11:00.000', NULL, 1002, 'topicB', 0, 20, 15, 'wantB', 'winB', 1, 200, 1),
    -> ('2024-08-01 10:02:00.000', '2024-08-01 10:12:00.000', NULL, 1003, 'topicC', 0, 30, 25, 'wantC', 'winC', 0, 300, 0),
    -> ('2024-08-01 10:03:00.000', '2024-08-01 10:13:00.000', NULL, 1004, 'topicD', 0, 40, 35, NULL, NULL, 1, 400, 1),
    -> ('2024-08-01 10:04:00.000', '2024-08-01 10:14:00.000', NULL, 1005, 'topicE', 0, 50, 45, 'wantE', NULL, 0, 500, 1),
    -> ('2024-08-01 10:05:00.000', '2024-08-01 10:15:00.000', NULL, 1006, 'topicF', 0, 60, 55, NULL, 'winF', 1, 600, 0),
    -> ('2024-08-01 10:06:00.000', '2024-08-01 10:16:00.000', NULL, 1007, 'topicG', 0, 70, 65, 'wantG', 'winG', 0, 700, 1),
    -> ('2024-08-01 10:07:00.000', '2024-08-01 10:17:00.000', NULL, 1008, 'topicH', 0, 80, 75, NULL, NULL, 1, 800, 1),
    -> ('2024-08-01 10:08:00.000', '2024-08-01 10:18:00.000', NULL, 1009, 'topicI', 0, 90, 85, 'wantI', 'winI', 0, 900, 0),
    -> ('2024-08-01 10:09:00.000', '2024-08-01 10:19:00.000', NULL, 1010, 'topicJ', 0, 100, 95, 'wantJ', NULL, 1, 1000, 1);
Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [test]>
(root@127.0.0.1) [test]>ALTER TABLE `subscriptions`
    -> MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.08 sec)

(root@127.0.0.1) [test]>admin show ddl jobs;
+--------+---------------------+----------------------+-------------------------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+-------------+
| JOB_ID | DB_NAME             | TABLE_NAME           | JOB_TYPE                      | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME                | START_TIME                 | END_TIME                   | STATE  | COMMENTS    |
+--------+---------------------+----------------------+-------------------------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+-------------+
|    932 | test                | subscriptions        | modify column                 | public       |       876 |      930 |         0 | 2025-08-26 13:54:34.372000 | 2025-08-26 13:54:34.372000 | 2025-08-26 13:54:34.422000 | synced |             |
|    931 | test                | subscriptions        | create table                  | public       |       876 |      930 |         0 | 2025-08-26 13:54:34.271000 | 2025-08-26 13:54:34.271000 | 2025-08-26 13:54:34.321000 | synced |             |
|    929 | test                | wa_data              | create table                  | public       |       876 |      928 |         0 | 2025-08-19 14:59:23.521000 | 2025-08-19 14:59:23.521000 | 2025-08-19 14:59:23.570000 | synced |             |
|    927 | test                | abc                  | add index                     | public       |       876 |      925 |         0 | 2025-08-19 09:59:18.621000 | 2025-08-19 09:59:18.621000 | 2025-08-19 09:59:18.821000 | synced | ingest, DXF |
|    926 | test                | abc                  | create table                  | public       |       876 |      925 |         0 | 2025-08-19 09:57:00.471000 | 2025-08-19 09:57:00.521000 | 2025-08-19 09:57:00.570000 | synced |             |
|    924 | spd_shsy            | base_his_info        | create table                  | public       |       919 |      923 |         0 | 2025-08-14 15:33:51.587000 | 2025-08-14 15:33:51.637000 | 2025-08-14 15:33:51.787000 | synced |             |
|    922 | spd_shsy            | base_producer        | create table                  | public       |       919 |      921 |         0 | 2025-08-14 15:33:51.487000 | 2025-08-14 15:33:51.537000 | 2025-08-14 15:33:51.587000 | synced |             |
|    920 | spd_shsy            |                      | create schema                 | public       |       919 |        0 |         0 | 2025-08-14 15:33:51.437000 | 2025-08-14 15:33:51.437000 | 2025-08-14 15:33:51.487000 | synced |             |
|    918 | test                | t                    | truncate table                | public       |       876 |      886 |         0 | 2025-08-14 09:30:23.537000 | 2025-08-14 09:30:23.589000 | 2025-08-14 09:30:23.688000 | synced |             |
|    916 | finance_transaction | hs_accounts_main_tmp | update tiflash replica status | public       |       911 |      913 |         0 | 2025-08-13 14:27:40.087000 | 2025-08-13 14:27:40.087000 | 2025-08-13 14:27:40.187000 | synced |             |
+--------+---------------------+----------------------+-------------------------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+-------------+
10 rows in set (0.06 sec)

测试下来是秒加的

托马斯滑板鞋 · 2025 年8 月 26 日 05:58

问题二，表是lightning迁移/复制过来的吗？我试了下，复制的表这里是1

TiDBer_xTvoCh2f · 2025 年8 月 26 日 06:01

应该是小bug，这种情况还在自增吗？

WalterWj · 2025 年8 月 26 日 06:05

select auto_increment from information_schema.tables where table_name='subscriptions';

这个结果是啥？我本地没复现，是准的。测试版本是 8.5.3

WalterWj · 2025 年8 月 26 日 06:17

为啥我复现不出来，你是物理写入么？还是逻辑写入的。

托马斯滑板鞋 · 2025 年8 月 26 日 06:23

物理，版本7.5.6

sd4544763 · 2025 年8 月 26 日 06:24

针对问题一：在 TiDB 中执行 ALTER TABLE 修改主键类型为 bigint 的操作会触发表结构变更，通常需要重建表，因此会锁表且无法秒级完成（具体时间取决于数据量）。建议在业务低峰期操作，或使用 TiDB 的在线 DDL 功能（如 GH-OST/PT-OSC 工具）减少影响。

关于问题二：INFORMATION_SCHEMA.TABLES 中的 AUTO_INCREMENT 值可能因缓存或元信息更新延迟导致不准。TiDB 的统计信息分析（ANALYZE）主要优化查询计划，不会直接更新系统表的自增ID值。可通过手动执行 SHOW CREATE TABLE <表名> 确认当前自增值，或检查是否有未提交的事务影响自增ID分配。

WalterWj · 2025 年8 月 26 日 06:25

AI 结论，自己测试下再发。。。。

TiDBer_xTvoCh2f · 2025 年8 月 26 日 06:27

逻辑，8.1 ticdc没问题

托马斯滑板鞋 · 2025 年8 月 26 日 06:29

WalterWj · 2025 年8 月 26 日 06:37

手动刷下，看看行不行。alter table subscriptions auto_increment 1;

就执行类似这个 sql 即可，不会改成功的，但是会触发刷新 auto 值。

TiDBer_sheldon · 2025 年8 月 26 日 06:47

我也认为会锁表。例如从int型改成varchar(max),类型变了，位数变了

托马斯滑板鞋 · 2025 年8 月 26 日 07:16

不会，还是1

托马斯滑板鞋 · 2025 年8 月 26 日 07:18

有意思的是，show create table 中显示的AUTO_INCREMENT= 是有值的；
不过使用 alter table subscriptions auto_increment 1; 刷新值没有变

WalterWj · 2025 年8 月 26 日 07:24

神奇。

TiDBer_HErMeXDz · 2025 年8 月 26 日 07:30

https://docs.pingcap.com/zh/tidb/stable/ddl-introduction/ 请参考官方文档。

llsong · 2025 年8 月 26 日 14:29

感觉肯定要锁表的，生产环境中这种操作应该用更稳妥的方案来搞