如何用pt-online-schema-change在线修改表字段长度

千家信息网最后更新 2025年02月02日如何用pt-online-schema-change在线修改表字段长度pt-online-schema-change依赖条件:
操作的表必须有主键，否则执行会报错

实验如下:
MySQL [mysql]> create database chenfeng;
Query OK, 1 row affected (0.00 sec)

MySQL [mysql]> use chenfeng;
Database changed

创建带有主键的表test:
MySQL [chenfeng]> create table test
-> (id int(10) not null auto_increment,
-> k int(10) not null default '0',
-> c char(120) not null default '',
-> primary key(id))
-> engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)


MySQL [chenfeng]> desc test;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| k | int(10) | NO | | 0 | |
| c | char(120) | NO | | | |
+-------+-----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


MySQL [chenfeng]>

MySQL [chenfeng]> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`k` int(10) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


[root@chenfeng ~]# pt-online-schema-change --alter="modify c varchar(150) not null default ''" --user=root --password=123456 D=chenfeng,t=test --charset=utf8 --execute
No slaves found. See --recursion-method if host chenfeng has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `chenfeng`.`test`...
Creating new table...
Created new table chenfeng._test_new OK.
Altering new table...
Altered `chenfeng`.`_test_new` OK.
2016-10-07T18:57:36 Creating triggers...
2016-10-07T18:57:36 Created triggers OK.
2016-10-07T18:57:36 Copying approximately 1 rows...
2016-10-07T18:57:36 Copied rows OK.
2016-10-07T18:57:36 Analyzing new table...
2016-10-07T18:57:36 Swapping tables...
2016-10-07T18:57:36 Swapped original and new tables OK.
2016-10-07T18:57:36 Dropping old table...
2016-10-07T18:57:36 Dropped old table `chenfeng`.`_test_old` OK.
2016-10-07T18:57:36 Dropping triggers...
2016-10-07T18:57:36 Dropped triggers OK.
Successfully altered `chenfeng`.`test`.
[root@chenfeng ~]#


MySQL [(none)]> use chenfeng
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
MySQL [chenfeng]> show tables;
+-----------------+
| Tables_in_chenfeng |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)


MySQL [chenfeng]> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| k | int(10) | NO | | 0 | |
| c | varchar(150) | NO | | | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

varchar(150）即为我们想要的结果。