如何用pt-online-schema-change在线修改表字段长度
发布时间：2022-03-31 11:57:19 所属栏目：MySql教程 来源：互联网
导读：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]
|
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 testG *************************** 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）即为我们想要的结果。 （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号