• ADADADADAD

    如何用pt-online-schema-change在线修改表字段长度[ mysql数据库 ]

    mysql数据库 时间:2024-12-03 12:13:21

    作者:文/会员上传

    简介:

    pt-online-schema-change依赖条件:
    操作的表必须有主键,否则执行会报错

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

    MySQL

    以下为本文的正文内容,内容仅供参考!本站为公益性网站,复制本文以及下载DOC文档全部免费。

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

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

    将本文的Word文档下载到电脑

    推荐度:

    下载