How to take mysql dump of selected columns of a table
I have a requirement in which I have to take mysql dump of just one column of a table. Since that table has too many columns, I don't want to take dump of the full table. I have to get this dump of the table from one server to another. Any idea how I can do this?
If you would like to take mysql dump including the schema, it can be done by following the below steps:
create a temp table:
create table temp_table like name_of_the_original_table;
duplicating data into temp_table:
insert into temp_table select * from name_of_the_original_table;
dropping unnecessary fields:
alter table temp_table drop column somecolumn;
post this, you could take a mysqldump by running:
mysqldump -u <username> -p <password> databasename temp_table
If the intention is to take a data dump(without the schema), you can run the below command:
select * from sometable into outfile '/tmp/datadump' fields terminated by 't' lines terminated by 'n';
选择列到文件中？
Select col from table into outfile 'fileame'
mysql> CREATE TABLE `tempTable` AS SELECT `columnYouWant` from `table`;
$> mysqldump yourDB tempTable > temp.sql
将temp.sql复制到目标服务器，然后在目标服务器上复制
$> mysql yourDB < temp.sql
mysql> RENAME TABLE `table` TO `tableBackup`, `tempTable` TO `table`;
上一篇: 在意外的T上收到解析错误
下一篇: 如何获取表中选定列的mysql转储