Setting the field terminator in MySQL dump output can be crucial when exporting data, especially for formatting it correctly in subsequent data processing tasks. The mysqldump command offers the option --fields-terminated-by=name to specify a custom delimiter for separating fields in the output file.
Let’s explore several examples of how this option can be used:
Example 1: Using a comma (,) as the field terminator:
mysqldump --fields-terminated-by=',' database_name > output.csv
This command exports data from database_name to output.csv with each field separated by a comma.
Verification: Open output.csv in a text editor and check that fields are indeed separated by commas.
Example 2: Using a tab character (\t) as the field terminator:
mysqldump --fields-terminated-by='\t' database_name > output.tsv
This command exports data to output.tsv with fields separated by tabs, commonly used for TSV (Tab-Separated Values) files.
Verification: Open output.tsv in a text editor with a tab-aware viewer to confirm proper field separation.
Example 3: Using a pipe (|) as the field terminator:
mysqldump --fields-terminated-by='|' database_name > output.pipe
This command exports data to output.pipe with fields separated by pipes, suitable for use in pipelines or delimited data scenarios.
Verification: Open output.pipe and ensure fields are separated by pipes.
Example 4: Using a semicolon (;) as the field terminator:
mysqldump --fields-terminated-by=';' database_name > output_semicolon.csv
This command exports data to output_semicolon.csv with fields separated by semicolons, useful for specific CSV formats requiring this delimiter.
Verification: Check output_semicolon.csv to confirm semicolon separation between fields.
Example 5: Using a double quote (“) as the field terminator:
mysqldump --fields-terminated-by='"' database_name > output_quotes.csv
This command exports data to output_quotes.csv with fields enclosed in double quotes, suitable for CSV formats requiring quoted fields.
Verification: Inspect output_quotes.csv to ensure proper handling of quoted fields.
Example 6: Using a space (‘ ‘) as the field terminator:
mysqldump --fields-terminated-by=' ' database_name > output_space.txt
This command exports data to output_space.txt with fields separated by spaces, applicable for space-delimited formats.
Verification: Open output_space.txt and confirm fields are properly separated by spaces.
Example 7: Using a hash (#) as the field terminator:
mysqldump --fields-terminated-by='#' database_name > output_hash.txt
This command exports data to output_hash.txt with fields separated by hashes, useful in certain delimited text formats.
Verification: Check output_hash.txt to ensure fields are separated as expected by hashes.
Example 8: Using a colon (:) as the field terminator:
mysqldump --fields-terminated-by=':' database_name > output_colon.txt
This command exports data to output_colon.txt with fields separated by colons, suitable for specific delimited data requirements.
Verification: Open output_colon.txt and verify that fields are correctly separated by colons.
Example 9: Using a backslash (\) as the field terminator:
mysqldump --fields-terminated-by='\\' database_name > output_backslash.txt
This command exports data to output_backslash.txt with fields separated by backslashes, useful in certain data transfer scenarios.
Verification: Inspect output_backslash.txt to confirm proper field separation using backslashes.
Example 10: Using a dollar sign ($) as the field terminator:
mysqldump --fields-terminated-by='$' database_name > output_dollar.txt
This command exports data to output_dollar.txt with fields separated by dollar signs, suitable for specific data formats requiring this delimiter.
Verification: Open output_dollar.txt and ensure fields are separated by dollar signs as intended.
Also check similar articles.
Using Extended INSERT Syntax in mysqldump
Dumping Events Using mysqldump
Dumping Slave Position in mysqldump Output (Deprecated)
Dumping Replica Position in mysqldump Output
Disabling Keys in mysqldump Output
Discussion about this post