Дамп в CSV и обратно

load.sql

LOAD DATA INFILE '/path/file.csv' INTO TABLE `table` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

/etc/mysql/my.cnf

[mysqld]
secure_file_priv=""

Далее перезагрузить мускул.

sudo chown mysql:mysql /path/file.csv

При ошибке с кодировкой следует добавить CHARACTER SET UTF8 перед FIELDS TERMINATED BY.

В случае, когда идет NULL или опущено значение data1,,data3, импорт возможно сделать путем замены на реальный NULL через SET:

load-null.sql

LOAD DATA INFILE '/home/user/data/pillbox_engine_20150511.tab'
INTO TABLE `pillbox_engine`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID, Enabled, created_at, updated_at, spp, setid, splsize, pillbox_size, splshape, splshape_text, pillbox_shape_text, splscore, pillbox_score, splimprint, pillbox_imprint, splcolor, splcolor_text, pillbox_color_text, spl_strength, spl_ingredients, spl_inactive_ing, SOURCE, rxtty, rxstring, rxcui, product_code, part_num, part_medicine_name, ndc9, medicine_name, marketing_act_code, effective_time, file_name, equal_product_code, dosage_form, document_type, dea_schedule_code, dea_schedule_name, author_type, author, approval_code, image_source, splimage, has_image, epc_match, version_number, laberer_code, application_number, updated, stale, NEW, has_image_new, @vfrom_sis, @vno_rxcui, @vspl_id, pri_key, rxstring_new, ingredients_new, spl_inactive_ing_new, splcolor_new, splshape_new, splsize_new, splscore_new, splimprint_new)
SET
from_sis = NULLIF(@vfrom_sis,'NULL'),
no_rxcui = NULLIF(@vno_rxcui,'NULL'),
spl_id = NULLIF(@vspl_id,'NULL');

load-emply.sql

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(one, two, three, @vfour, five)
SET four = NULLIF(@vfour,'')
;

to-csv.sql

SELECT * FROM `your_table` INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';