ing old table because --no-drop-old-table was specified.
2019-03-25T13:35:31 Dropping triggers...
2019-03-25T13:35:31 Dropped triggers OK.
Successfully altered `employees`.`employees`.
将表employees的comment字段的字符集修改为utf8mb4:
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column comment varchar(50) character set utf8mb4" --alter-foreign-keys-method=auto --charset=utf8 --execute
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "drop column comment" --alter-foreign-keys-method=auto --charset=utf8 --execute
为表dept_emp的字段from_date和to_date创建复合索引idx_fr_to_date:
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add index idx_fr_to_date(from_date,to_date)" --alter-foreign-keys-method=auto --charset=utf8 --execute
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop index idx_fr_to_date" --alter-foreign-keys-method=auto --charset=utf8 --execute
将表dept_emp的字段to_date指定为允许NULL:
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "modify column to_date date null" --alter-foreign-keys-method=auto --charset=utf8 --execute
为表employees添加字段ptosc_num并允许NULL,字段类型为int,没有指定默认值。
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add ptosc_num int null" --alter-foreign-keys-method=auto --charset=utf8 --execute
修改字段ptosc_num为不允许NULL(NOT NULL),需要通过指定选项--null-to-not-null
,否则会报错。
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column ptosc_num int not null" --alter-foreign-keys-method=auto --null-to-not-null --charset=utf8 --execute
-- 因为字段ptosc_num没有指定默认值,字段类型为int,所以默认值为0
mysql root@localhost:employees> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | ptosc_num |
+--------+------------+------------+-----------+--------+------------+-----------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 0 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 0 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 0 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 0 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 0 |
+--------+------------+------------+-----------+--------+------------+-----------+
5 rows in set
Time: 0.022s
需要为外键指定名称为_forigen_key
,因为在创建新表时候默认为新表上的外键创建这样的名称,如果没这样指定则无法删除。
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop foreign key _dept_emp_ibfk_1" --alter-foreign-keys-method=auto --charset=utf8 --execute
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "engine=InnoDB" --alter-foreign-keys-method=auto --charset=utf8 --execute