如何对表的选定列进行mysql转储

时间:2022-08-24 20:08:16

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?

我有一个要求,我必须采取只有一列表的mysql转储。由于该表有太多列,我不想转储整个表。我必须将表的转储从一个服务器转移到另一个服务器。知道我怎么能这样做吗?

3 个解决方案

#1


5  

If you would like to take mysql dump including the schema, it can be done by following the below steps:

如果您想采用包含架构的mysql转储,可以按照以下步骤完成:

create a temp table:

创建临时表:

create table temp_table like name_of_the_original_table;

duplicating data into temp_table:

将数据复制到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:

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';

#2


2  

Select the column into a file ?

选择列到文件中?

Select col from table into outfile 'fileame'

#3


1  

mysql> CREATE TABLE `tempTable` AS SELECT `columnYouWant` from `table`;
$> mysqldump yourDB tempTable > temp.sql

copy temp.sql to target server, then on target server

将temp.sql复制到目标服务器,然后复制到目标服务器上

$> mysql yourDB < temp.sql
mysql> RENAME TABLE `table` TO `tableBackup`, `tempTable` TO `table`;

#1


5  

If you would like to take mysql dump including the schema, it can be done by following the below steps:

如果您想采用包含架构的mysql转储,可以按照以下步骤完成:

create a temp table:

创建临时表:

create table temp_table like name_of_the_original_table;

duplicating data into temp_table:

将数据复制到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:

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';

#2


2  

Select the column into a file ?

选择列到文件中?

Select col from table into outfile 'fileame'

#3


1  

mysql> CREATE TABLE `tempTable` AS SELECT `columnYouWant` from `table`;
$> mysqldump yourDB tempTable > temp.sql

copy temp.sql to target server, then on target server

将temp.sql复制到目标服务器,然后复制到目标服务器上

$> mysql yourDB < temp.sql
mysql> RENAME TABLE `table` TO `tableBackup`, `tempTable` TO `table`;