How to export a specific column data in MySQL

user_1476749 0
Viewed 35993

I have a table. I need to export all the rows for 1 column only. How can I achieve this? I know how to export the whole table but have not find a way to export a single columns data.

mysql
7 Answers

try this..Each result data will be in each row with cell by cell

SELECT cu.*, cy.name as country_name, st.name as state_name FROM table_name cu, all_countries cy, all_states st where cu.country = cy.id and cu.state = st.id INTO OUTFILE 'file_info3.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\t\n'

The output file will be located at "C:\wamp\bin\mysql\mysql5.6.17\data\db_name\".

user_103132950

For workbench you can do it this way (I'm using MySQL Workbench version 6.3):

Right-click on the table and click on "Table Data Export Wizard" which will let you select the columns you want to export:

enter image description here

enter image description here

It exports as csv or json.

You can import it back the similar way. (Right-click on the table and click "Table Data Import Wizard"

user_25135230

Solution from https://www.sitepoint.com/community/t/using-mysqldump-to-export-individual-fields/4743 worked for me 

mysql -u USERNAME --password=PASSWORD --database=DATABASE --execute='SELECT FIELD, FIELD FROM TABLE LIMIT 0, 10000 ’ -X > file.xml

I tried to export data from a kubernet pod using following query and it worked for me

kubectl exec  podName -n namespaceName  -- mysql --host=hostName --user=userName --password=password dbName --execute=“SELECT FIELD, FIELD FROM TABLE LIMIT 0, 10000”> a5.sql
user_10534960
Related