Introduction to MySQL Export Database
In MySQL, we store the data in the database in table format. Manier times, there is a necessity to get the data stored in the tables of MySQL database in some other format such as CSV, JSON, or SQL format. This is often the requirement when we do data transfer and sharing between multiple companies and individuals.
If we have the exported data of the database and its table contents in CSV format then this data can be further used in other platforms such as google docs, open office, or Microsoft Excel for further analysis of resultset and concluding the necessary statistics by performing various operations on the exported data to get certain conclusions. When MySQL data is exported in JSON format then we can transfer this data to other applications as JSON is by far the most preferred method for data transfer. Further, MySQL data can also be exported of dumped to SQL files that can be used as the backup files and the commands that are exported for the specific database or table contents can be run again on some different or same MySQL server to achieve the state of the database similar to the status when the data was exported.
In this article, we will learn how we can export the data of MySQL to various formats when we are using the MySQL Workbench editor. Note that mysqldump command can also be used through the command-line to export data to SQL format. Other than this, we can make the use of the INTO OUTFILE statement to export the data of MySQL database and its contents to any other format like .csv or .txt using the command-line MySQL shell.
Exporting Database and tables in Workbench in SQL format
You can choose the option “Data Export” provided in the Management wizard in the first screen on MySQL workbench or go to the Server menu in the upper portion and then choose the option Data Export to open the Export wizard of MySQL as shown below
Here, you can select the database whose contents or whole database itself you want to export. After clicking the database name you want, you will see all the tables inside it being auto-selected in the right-side window as shown in the below image. After clicking on the educba database all the tables inside it are seen selected in the right window. You can keep it as it is in case you want to export whole database contents or choose the particular table or tables that you want to export and click on the start export button to initialize the process of export.
After clicking on start export the MySQL dump file in .sql format will be created on the specifies export options path.
Exporting data to any other format in Workbench
You can export the table contents and structure by going to the schemas tab mentioned in the left-sidebar of the Workbench tool. Then you can right-click on the table of the database you wish to export and then click on “Table Data Export Wizard” which will lead to the opening of the export wizard as shown in the below image. Where you will see the list of all the columns of that table that are auto-selected for exporting. You can further choose the columns that you want to export by changing the selected columns and then click on the Next button in the bottom button as shown below
If you click on the advanced button below, you can type the equivalent query whose resultant data and the content will be exported. After clicking on next button you can choose the path where the file needs to be exported and type of format in which you want to export either CSV or JSON and in case of CSV, you can select the options associated with data to be exported and specifying different properties that need to be applied while exporting data such as field separator to be considered and NULL word interpretation, etc as shown below
After clicking on Next, if the same file with same name and format exists on the specified path it asks that whether you want to overwrite the file or else just mentions the steps that will be followed and executed while exporting as shown below
After clicking on next you will see a message saying that the file has been exported successfully as shown below
16 Online Courses | 11 Hands-on Projects| 70+ Hours| Verifiable Certificate of Completion
4.5
View Course
You can observe that the exported file in the desired format will be created in the specified path after running the export process successfully.
Exporting the Query Resultset
There is also the provision to export the resultset of any select query that is executed in Workbench by simply clicking on the export button and then specifying the path and name of the file. Note that in MySQL using the command-line, we can export the results of the query by using the INTO OUTFILE statement. Let us look at how we can export the resultset of the query in workbench with the help of an example. Consider the following select query that retrieves the names of all developers that begin with letter R as shown below –
select * from developers where name like "R%";
that gives the following output after the execution of the query –
Now, we can click on the export button that turns into opening the files and we can choose the path and name of the file by typing in it as shown below
You can choose the format of the report from CSV, HTML, XML, JSON, SQL, or Excel whichever you want from the choice list provided below in the left-hand corner. Simply, click on the save and your file will be exported with the retrieved result set.
Conclusion
We can export the database, table contents, and structure or the output resultset of the MySQL query in MySQL using the client tools of MySQL like Workbench or even using the command-line mysqldump command to create .sql exported files or using INTO OUTFILE statement.
Recommended Articles
This is a guide to MySQL Export Database. Here we discuss the introduction, Exporting Database, and tables in Workbench in SQL format respectively. You may also have a look at the following articles to learn more –
