CODEWITHSUNDEEP
mysql
hd.
hd.

Reputation: 18306

dumping data from views in mysql

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.

Upvotes: 8

Views: 12669

Answers (3)

jago
jago

Reputation: 497

I have written a bash function to export the "structure" and data of a VIEW without creating a full copy of the data. I tested it with MySQL 5.6 on a CentOS 7 server. It properly takes into account columns with JSON values and strings like "O'Mally", though you may need to tweak it further for other special cases.

For the sake of brevity, I did not make it robust in terms of error checks or anything else.

function export_data_from_view
{
   local DB_HOST=$1
   local SCHEMA=$2
   local VIEW=$3

   local TMP_TABLE_NAME="view_as_table_$RANDOM"

   local SQL1="
      create table $TMP_TABLE_NAME as
      (select * from $VIEW where 1=0);

      show create table $TMP_TABLE_NAME \G
   "

   # Create an empty table with the structure of all columns in the VIEW.
   # Display the structure.  Delete lines not needed.
   local STRUCT=$(
      mysql -h $DB_HOST -BANnq -e "$SQL1" $SCHEMA |
         egrep -v "\*\*\*.* row \*\*\*|^${TMP_TABLE_NAME}$" |
         sed "s/$TMP_TABLE_NAME/$VIEW/"
   )
   echo
   echo "$STRUCT;"
   echo

   local SQL2="
      select concat( 'quote( ', column_name, ' ),' )
      from   information_schema.columns
      where  table_schema = '$SCHEMA'
        and  table_name   = '$VIEW'
      order  by ORDINAL_POSITION
   "
   local COL_LIST=$(mysql -h $DB_HOST -BANnq -e "$SQL2")

   # Remove the last comma from COL_LIST.
   local COL_LIST=${COL_LIST%,}

   local SQL3="select $COL_LIST from $VIEW"
   local INSERT_STR="insert into $VIEW values "

   # Fix quoting issues to produce executeable INSERT statements.
   # \x27 is the single quote.
   # \x5C is the back slash.
   mysql -h $DB_HOST -BANnq -e "$SQL3" $SCHEMA |
      sed '
         s/\t/,/g;                    # Change each TAB to a comma.
         s/\x5C\x5C\x27/\x5C\x27/g;   # Change each back-back-single-quote to a back-single-quote.
         s/\x27NULL\x27/NULL/g;       # Remove quotes from around real NULL values.
         s/\x27\x27{/\x27{/g;         # Remove extra quotes from the beginning of a JSON value.
         s/}\x27\x27/}\x27/g;         # Remove extra quotes from the end of a JSON value.
      ' |
      awk -v insert="$INSERT_STR" '{print insert "( " $0 " );"}'

   local SQL4="drop table if exists $TMP_TABLE_NAME"
   mysql -h $DB_HOST -BANnq -e "$SQL4" $SCHEMA
   echo
}

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146430

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.

Upvotes: 14

Frank Heikens
Frank Heikens

Reputation: 127056

Use SELECT ... INTO OUTFILE to create a dump of the data.

Upvotes: 2

Related Questions