dumping data from views in mysql

Question

i have a view and want to extract its data into a file that has create table statement as well data. i know that mysqldump doesn't work on views.

Answer 1

Obviously, there isn't an automated way to generate the CREATE TABLE statement of a table that does not exist. So you basically have two options:

1. Create an actual table, dump it and remove it afterwards.
2. Write a lot of code to analyse the view and underlying tables and generate the appropriate SQL.

First option is not optimal at all but it's easy to implement:

CREATE TABLE my_table AS
SELECT *
FROM my_view

You can now dump the table with mysqldump. When you're done:

DROP TABLE my_table

Second option can be as optimal as you need but it can get pretty complicate and it depends a lot on your actual needs and tool availability. However, if performance is an issue you can combine both approaches in a quick and dirty trick:

CREATE TABLE my_table AS
SELECT *
FROM my_view
LIMIT 1;

SHOW CREATE TABLE my_table;

Now, you use your favourite language to read values from my_view and build the appropriate INSERT INTO code. Finally:

DROP TABLE my_table;

In any case, feel free to explain why you need to obtain SQL code from views and we may be able to find better solutions.