CODEWITHSUNDEEP
mysqlviewexport
barryhunter
barryhunter

Reputation: 21091

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?

Upvotes: 12

Views: 30326

Answers (3)

Gonzalo Cao
Gonzalo Cao

Reputation: 2424

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.

Upvotes: 1

Jeff Ferland
Jeff Ferland

Reputation: 18292

OK, so based on your CSV failure comment, start with Paul's answer. Make the following change to it:

 - FIELDS TERMINATED BY ','
 + FIELDS TERMINATED BY ',' ESCAPED BY '\'

When you're done with that, on the import side you'll do a "load data infile" and use the same terminated / enclosed / escaped statements.

Upvotes: 3

Paul Tomblin
Paul Tomblin

Reputation: 182782

One option would be to do a query into a CSV file and import that. To select into a CSV file:

From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Upvotes: 4

Related Questions