Search code examples
mysqlviewexport

Dump MySQL view as a table with data

Say I have a view in my database, and I want to send a file to someone to create that view's output as a table in their database.

mysqldump of course only exports the 'create view...' statement (well, okay, it includes the create table, but no data).

What I have done is simply duplicate the view as a real table and dump that. But for a big table it's slow and wasteful:

create table tmptable select * from myview

Short of creating a script that mimics the behaviour of mysqldump and does this, is there a better way?

Solution

  • Same problem here my problem is that I want to export view definition (84 fields and millions of records) as a "create table" statement, because view can variate along time and I want an automatic process. So that's what I did:

    1. Create table from view but with no records

    mysql -uxxxx -pxxxxxx my_db -e "create table if not exists my_view_def as select * from my_view limit 0;"

    1. Export new table definition. I'm adding a sed command to change table name my_view_def to match original view name ("my_view")

    mysqldump -uxxxx -pxxxxxx my_db my_view_def | sed s/my_view_def/my_view/g > /tmp/my_view.sql

    1. drop temporary table

    mysql -uxxxx -pxxxxxx my_db -e "drop table my_view_def;"

    1. Export data as a CSV file

    SELECT * from my_view into outfile "/tmp/my_view.csv" fields terminated BY ";" ENCLOSED BY '"' LINES TERMINATED BY '\n';

    Then you'll have two files, one with the definition and another with the data in CSV format.