Using Complete INSERT Statements in mysqldump

The mysqldump command in MySQL is used to export databases or tables into SQL dump files, which can be used to recreate the database structure and data. One useful option is -c or --complete-insert, which generates complete INSERT statements that explicitly list the column names and their values for each row of data.

This option is particularly handy when you want to have more control over how data is inserted into another database or when troubleshooting data issues. Instead of relying on abbreviated INSERT statements that only list values, complete INSERT statements provide clarity and ensure that the data is inserted correctly, especially when dealing with tables with many columns.

Here are some examples of how to use mysqldump with the --complete-insert option:

Example 1: Dumping a single table with complete INSERT statements:

mysqldump -u username -p --complete-insert dbname tablename

This command exports the table tablename from database dbname with complete INSERT statements. Each INSERT statement will explicitly mention all column names followed by their corresponding values.

Example 2: Dumping an entire database with complete INSERT statements:

mysqldump -u username -p --complete-insert dbname

This command exports the entire database dbname with complete INSERT statements for each table within the database. It ensures that all data from all tables is exported with detailed INSERT statements.

Example 3: Exporting and compressing the dump file while using complete INSERT statements:

mysqldump -u username -p --complete-insert dbname | gzip > dbname_backup.sql.gz

This command not only dumps the database dbname with complete INSERT statements but also pipes the output through gzip to compress the SQL dump file dbname_backup.sql.gz.

Example 4: Dumping a table with complete INSERT statements and excluding certain columns:

mysqldump -u username -p --complete-insert --ignore-table=dbname.tablename --ignore-column=columnname dbname tablename

This command dumps the table tablename from database dbname with complete INSERT statements, excluding the specified column columnname from the output.

Example 5: Exporting data with extended INSERT statements (multiple rows per INSERT):

mysqldump -u username -p --extended-insert=FALSE --complete-insert dbname tablename

This command forces mysqldump to use single-row INSERT statements instead of extended INSERT statements, ensuring each row has a complete INSERT statement.

To verify if the mysqldump command executed successfully, follow these steps:

1. Check the command output for any error messages or warnings.
2. Inspect the generated SQL dump file to ensure it contains complete INSERT statements as expected.
3. If applicable, import the SQL dump into another MySQL database and verify that the data is correctly inserted.

Read Also

Generating Compact Output in mysqldump
Setting MySQL Dump Compatibility Mode
Including Comments in mysqldump Output
Adding ANALYZE TABLE Statements in mysqldump Output
Setting Character Sets Directory for mysqldump