MySQL での一括挿入

-- Slow: one round trip and one transaction per row
INSERT INTO products (name, price) VALUES ('Widget A', 9.99);
INSERT INTO products (name, price) VALUES ('Widget B', 14.99);
INSERT INTO products (name, price) VALUES ('Widget C', 19.99);

-- Fast: one round trip, one transaction for all three
INSERT INTO products (name, price) VALUES
    ('Widget A',  9.99),
    ('Widget B', 14.99),
    ('Widget C', 19.99);

START TRANSACTION;
INSERT INTO products (name, price) VALUES ('Widget A',  9.99);
INSERT INTO products (name, price) VALUES ('Widget B', 14.99);
-- ... hundreds more ...
INSERT INTO products (name, price) VALUES ('Widget Z', 99.99);
COMMIT;

LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- skip header row
(name, price, category);

SHOW VARIABLES LIKE 'secure_file_priv';

LOAD DATA LOCAL INFILE '/path/on/client/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Load staging table, validate, then move to production
INSERT INTO products (name, price, category)
SELECT name, price, category
FROM products_staging
WHERE price > 0 AND name IS NOT NULL;

ALTER TABLE products DISABLE KEYS;
-- ... bulk INSERT ...
ALTER TABLE products ENABLE KEYS;  -- rebuilds indexes in one pass

-- Temporarily increase the buffer pool if possible
SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;  -- 4GB example

-- Disable foreign key checks
SET foreign_key_checks = 0;
-- ... bulk load ...
SET foreign_key_checks = 1;

-- Disable unique checks for known-clean data
SET unique_checks = 0;
-- ... bulk load ...
SET unique_checks = 1;