LOAD DATA and OUTFILE

Loading Data with LOAD DATA and SELECT INTO OUTFILE

SELECT ... INTO OUTFILE writes rows to a delimited file on the server's disk, and LOAD DATA INFILE reads one back: the fastest bulk path. The caller needs the FILE privilege (never grant it to an application), and secure_file_priv confines both to one directory, /var/lib/mysql-files/ here; INTO OUTFILE '/tmp/p.csv' fails with ERROR 1290. The export wrote lines such as 7,"AC-MUG-01","Semicolon Coffee Mug",12.50, and the load computes a column with SET:

Exporting cheap products as CSV and loading them with a computed columnSQL

SELECT id, sku, title, price FROM products WHERE price < 20
INTO OUTFILE '/var/lib/mysql-files/cheap.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
CREATE TABLE price_updates (sku VARCHAR(20) PRIMARY KEY, new_price DECIMAL(8,2));
LOAD DATA INFILE '/var/lib/mysql-files/cheap.csv' INTO TABLE price_updates
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(@id, sku, @title, @price) SET new_price = ROUND(@price * 1.05, 2);
SELECT * FROM price_updates;
LOAD DATA LOCAL INFILE '/tmp/new.csv' INTO TABLE price_updates FIELDS TERMINATED BY ',';

Output

+-----------+-----------+
| sku       | new_price |
+-----------+-----------+
| AC-MUG-01 |     13.12 |
| AC-STK-01 |      5.24 |
+-----------+-----------+
ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the
  client and server sides

LOCAL sends a file from the client's disk, as a PHP application on another host must. The client ran with mysql --local-infile=1, but the server's local_infile is OFF by default since 8.0; PHP's client switch is Pdo\Mysql::ATTR_LOCAL_INFILE (Databases with PDO). IGNORE 1 LINES skips a header.

MENU

LOAD DATA and OUTFILE