创建一个相同表结构的 新表,在新表上加字段;
将原表数据导出再导入到新表;
mysql> CREATE TABLE orders_new LIKE orders;
Query OK, 0 rows affected (0.11 sec)
mysql> ALTER TABLE orders_new ADD new_field TINYINT(1) not null default 0;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
导出旧表数据
mysql> select * into outfile '/var/lib/mysql-files/wqd_orders.dat'
-> fields terminated by '|' optionally enclosed by '"'
-> lines terminated by '\n'
-> from orders order by id;
Query OK, 12176657 rows affected (1 min 20.93 sec)
导入新表
truncate table customers_new;
set autocommit = 0;
load data infile 'c:\\customers.dat'
into table customers_new
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\n';
commit;
重命名表
rename table customers_new to customers;
Query OK, 0 rows affected (0.05 sec)