SQL LINES

What is the SQL LINES clause?

Specifies the line prefix and line terminator when importing or exporting text data.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LINES: Supported: MySQL, MariaDB. Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake.

SQL LINES Full Explanation

The LINES clause appears in MySQL and MariaDB within LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. It tells the SQL engine how to recognize the start and end of each logical record in a text file.• STARTING BY lets you define a string that must appear at the beginning of every line. Records that do not match the prefix are ignored. This is handy for skipping headers or comments.• TERMINATED BY defines the sequence that marks the end of a line. The default is a Unix newline ('\n'), but you can set Windows style ('\r\n') or any custom delimiter.The clause only affects text—binary files ignore it. LINES works together with the FIELDS clause, which controls column delimiters inside each line. Using incorrect or mismatched settings will cause data misalignment or import errors. LINES is not part of the ANSI SQL standard and is unsupported by most other databases.

SQL LINES Syntax

LINES [STARTING BY 'start_string'] [TERMINATED BY 'terminator_string']

SQL LINES Parameters

STARTING BY (string) - Optional prefix that each line must begin with.
TERMINATED BY (string) - Optional sequence that ends each line.

Example Queries Using SQL LINES

-- Basic CSV import with Unix newlines
LOAD DATA INFILE '/tmp/sales.csv'
INTO TABLE sales
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Skip header lines that start with '#'
LOAD DATA INFILE '/tmp/logs.txt'
INTO TABLE app_logs
FIELDS TERMINATED BY '\t'
LINES STARTING BY '#' TERMINATED BY '\r\n';

-- Export data with Windows line endings
SELECT * FROM users
INTO OUTFILE '/tmp/users.tsv'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';

Expected Output Using SQL LINES

The server reads or writes each row using the specified start prefix and line terminator
Rows that do not match the STARTING BY string are skipped
Successful execution returns OK and the number of rows affected

Use Cases with SQL LINES

Importing CSV or TSV files that use Windows CRLF endings.
Ignoring comment or header lines in log files during LOAD DATA.
Exporting query results for systems that require a specific line delimiter.
Handling mixed line endings across operating systems.

Common Mistakes with SQL LINES

Omitting quotes around the terminator string.
Using LINES in databases that do not support it.
Forgetting to escape the newline character (use '\n', '\r\n').
Assuming LINES controls column delimiters – that is handled by FIELDS.
Mismatching STARTING BY with actual file contents, resulting in zero rows imported.

First Introduced In

MySQL 3.23

Frequently Asked Questions

What does LINES TERMINATED BY do?

It defines the character sequence that signals the end of each row when importing or exporting text files.

How can I ignore header rows?

Combine IGNORE 1 LINES or use LINES STARTING BY with a prefix that only real data rows contain.

Does LINES work in PostgreSQL or SQL Server?

No. LINES is exclusive to MySQL and MariaDB. Other systems use COPY or BULK INSERT with different options.

What is the default delimiter if I omit LINES TERMINATED BY?

MySQL defaults to the Unix newline character (\n).