When working with MySQL dumps using --extended-insert, you'll notice the default behavior outputs all rows in a single line:

INSERT INTO table VALUES (1,'data'),(2,'more'),(3,'values');

This becomes problematic for:

• Version control diffs
• Human readability
• Selective data manipulation

Interestingly, PHPMyAdmin achieves the desired formatting by using a custom implementation. Here's their approach:

INSERT INTO table VALUES
(1,'first'),
(2,'second'),
(3,'third');

Here are three ways to achieve this without post-processing:

1. Using sed Replacement

mysqldump --extended-insert dbname | sed 's$),($),\\n($g' > output.sql

2. Custom Formatting with MySQL Client

mysql --batch --skip-column-names -e "SELECT * FROM table" dbname | \
awk -F'\t' '{printf "(%d,\"%s\")%s\n", $1,$2,NR==count?";":","}' count=$(mysql -N -e "SELECT COUNT(*) FROM table" dbname)

3. mysqldump with Custom Delimiters

mysqldump --extended-insert --complete-insert \
--skip-comments --skip-add-drop-table dbname | \
perl -pe 's/VALUES \$/VALUES \\n\\(/g' | \
perl -pe 's/\$,\$/\$,\\n\\(/g' > formatted_dump.sql

While formatted dumps are larger (5-15% typically), the benefits often outweigh costs:

For Git repositories, consider adding this to your .gitattributes:

*.sql diff=sql

And in .gitconfig:

[diff "sql"]
    xfuncname = "^INSERT INTO ?([^ ]*)? .*VALUES"

When working with MySQL dumps using the --extended-insert option, a common pain point emerges: all table rows get compressed into a single line. This creates readability and version control challenges, especially when comparing diffs between database versions.

-- Typical extended insert output
INSERT INTO table VALUES (1,'data'),(2,'more'),(3,'values'),...;

Interestingly, phpMyAdmin implements a cleaner output format while still using extended inserts:

-- phpMyAdmin's format
INSERT INTO table VALUES 
(1,'data'),
(2,'more'),
(3,'values');

While MySQL doesn't provide a direct command-line flag for this formatting, these approaches work:

# Option 1: Use sed post-processing
mysqldump --extended-insert db_name | sed 's/),/),\n/g' > output.sql

# Option 2: mysqldump-combined with Perl
mysqldump --extended-insert db_name | perl -pe 's/,(?=\\()/,\n/g' > formatted.sql

For Python users processing dumps:

import re

with open('dump.sql') as f:
    content = f.read()

# Split extended inserts into multiple lines
formatted = re.sub(r'(?<=\)),(?=\()', ',\n', content)

with open('formatted.sql', 'w') as f:
    f.write(formatted)

Proper formatting enables:

• Easier code reviews of database changes
• Better git diff visualization
• Simpler manual editing of dump files
• Improved compatibility with version control systems

While formatted dumps are larger, the tradeoff in maintainability often justifies it. For very large databases, consider:

# Compress formatted dumps
mysqldump --extended-insert db_name | sed 's/),/),\n/g' | gzip > dump.sql.gz