将MySQL视图转储为具有数据的表 [英] Dump MySQL view as a table with data

问题描述

说我在我的数据库中有一个视图，我想发送一个文件给某人，以将该视图的输出创建为数据库中的一个表。

mysqldump of当然只出口'创建视图...'声明（好的，它包括创建表，但没有数据）。

我所做的只是重复该视图作为一个真正的表和转储。但是对于一张大桌子来说，这是缓慢而浪费的：

 创建表tmptable select * from myview 
  

创建一个模仿mysqldump行为的脚本，是否有更好的方法？

<可以，根据你的CSV失败评论，从Paul的答案开始，div class =h2_lin>解决方案

对其进行以下更改：

   - 被终止的字段'''
 +终止的字幕'，'ESCAPED BY'\'
  

完成后，在进口端你会做load data infile，并使用相同的终止/封闭/转义语句。

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?

解决方案

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.

这篇关于将MySQL视图转储为具有数据的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！