Professional Web Applications Themes

Happy medium for extended inserts - MySQL

The default for mysqldump is to enable --opt, which in turn enables --extended-insert. This results in a smaller dump file that is faster to reload. However, it's extremely difficult to view a large dump in a text editor. Disabling the feature with --skip-extended-insert creates individual INSERT statements for each record, producing a larger file that is slower to reload. Is there any way to keep --extended-insert, but have each record dumped on a single line? I'm thinking of something like this: INSERT INTO `foo` VALUES ('1', 'Doe', 'John'), ('2', 'Doe', 'Jane'); instead the default: INSERT INTO `foo` VALUES ('1', 'Doe', ...

  1. #1

    Default Happy medium for extended inserts

    The default for mysqldump is to enable --opt, which in turn enables
    --extended-insert. This results in a smaller dump file that is faster to
    reload. However, it's extremely difficult to view a large dump in a text
    editor. Disabling the feature with --skip-extended-insert creates
    individual INSERT statements for each record, producing a larger file that
    is slower to reload. Is there any way to keep --extended-insert, but have
    each record dumped on a single line?

    I'm thinking of something like this:

    INSERT INTO `foo` VALUES
    ('1', 'Doe', 'John'),
    ('2', 'Doe', 'Jane');

    instead the default:

    INSERT INTO `foo` VALUES ('1', 'Doe', 'John'),('2', 'Doe', 'Jane');

    Jorey Guest

  2. #2

    Default Re: Happy medium for extended inserts

    On Sun, 28 Jan 2007 16:32:55 -0500, Jorey Bump
    <com> wrote:
     

    Pipe it through sed replacing "),(" by "),newline("
    --
    ( Kees
    )
    c[_] You don't win wars by dying for your country. You win
    by making the other poor die for his country. (#226)
    Kees Guest

  3. #3

    Default Re: Happy medium for extended inserts

    On Wed, 31 Jan 2007 00:02:28 +0100, Kees Nuyt wrote:
     

    Thanks! Works great:

    mysqldump -u bob -p foo | sed "s/),(/),\n(/g" > foo.sql
    Jorey Guest

Similar Threads

  1. Double SQL Inserts
    By rcapra in forum Coldfusion Database Access
    Replies: 8
    Last Post: May 5th, 04:06 AM
  2. 1-to-Many Inserts
    By Fandomlife in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 29th, 09:15 PM
  3. need loop that INSERTS into same row
    By Greggg in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 21st, 01:21 PM
  4. Batch Inserts
    By Ron in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 31st, 02:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139