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

发布时间：2022-03-31 10:08 所属栏目：115 来源：互联网

导读：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] c

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）即为我们想要的结果。

（编辑：ASP站长网）