{"id":321,"date":"2018-11-05T15:41:42","date_gmt":"2018-11-05T15:41:42","guid":{"rendered":"https:\/\/blogs.ua.es\/jpm33\/?p=321"},"modified":"2018-11-05T15:41:42","modified_gmt":"2018-11-05T15:41:42","slug":"importar-exportar-datos-en-mysql","status":"publish","type":"post","link":"https:\/\/blogs.ua.es\/jpm33\/2018\/11\/05\/importar-exportar-datos-en-mysql\/","title":{"rendered":"Importar\/exportar datos en MySQL"},"content":{"rendered":"

Introducci\u00f3n<\/h1>\n

Tenemos una tabla marcas como la siguiente, \u00bfc\u00f3mo podemos exportarla?<\/p>\n

SELECT * FROM `marcas`\r\n\r\nid_marca nombre       url  avatar \r\n1        Zara         NULL NULL \r\n2        Woman Secret NULL NULL \r\n3        New Look     NULL NULL<\/pre>\n
Soluciones<\/h1>\n
Exportar con mysqldump<\/h2>\n
$ mysqldump -ubasedatos -pcontrase\u00f1a --extended-insert=false --hex-blob nombrebasededatos > volcado.sql<\/pre>\n
Exportar con SELECT … INTO OUTFILE<\/h2>\n
Opciones<\/p>\n
[FIELDS\r\n[TERMINATED BY '\\t']\r\n[[OPTIONALLY] ENCLOSED BY '']\r\n[ESCAPED BY '\\\\' ]\r\n]\r\n[LINES\r\n[STARTING BY '']\r\n[TERMINATED BY '\\n']\r\n]<\/pre>\n
Ejemplo:<\/p>\n
SELECT * INTO OUTFILE '\/path\/to\/result.txt'\r\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\r\nLINES TERMINATED BY '\\n'\r\nFROM marcas;<\/pre>\n
Importar fichero SQL<\/h2>\n
$ mysql -ubasedatos -pcontrase\u00f1a nombrebasededatos < volcado.sql<\/pre>\n
Importar datos con LOAD DATA INFILE<\/h2>\n
LOAD DATA sirve para tomar cualquier archivo \u201ccomma-separated\u201d (separado por comas, aunque no necesariamente son comas) y cargarlo como datos en alguna tabla de MySQL.Un ejemplo para recuperar los datos anteriores:<\/p>\n
LOAD DATA LOCAL INFILE '\/path\/to\/result.txt'\r\nINTO TABLE marcas\r\nFIELDS TERMINATED BY ','\r\nLINES TERMINATED BY '\\n' (id_marca, nombre, url, avatar );<\/pre>\n
Referencias<\/h1>\n
    \n
  1. https:\/\/phenobarbital.wordpress.com\/2007\/08\/03\/cargar-datos-en-mysql-con-load-data-infile\/<\/a><\/li>\n
  2. https:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/load-data.html<\/a><\/li>\n
  3. https:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/mysqldump.html<\/a><\/li>\n
  4. https:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/select-into.html<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    Introducci\u00f3n Tenemos una tabla marcas como la siguiente, \u00bfc\u00f3mo podemos exportarla? SELECT * FROM `marcas` id_marca nombre url avatar 1 Zara NULL NULL 2 Woman Secret NULL NULL 3 New Look NULL NULL Soluciones Exportar con mysqldump $ mysqldump -ubasedatos … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":3080,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[676,236732],"tags":[],"class_list":["post-321","post","type-post","status-publish","format-standard","hentry","category-sin-categoria","category-sql"],"_links":{"self":[{"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/posts\/321","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/users\/3080"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/comments?post=321"}],"version-history":[{"count":5,"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/posts\/321\/revisions"}],"predecessor-version":[{"id":432,"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/posts\/321\/revisions\/432"}],"wp:attachment":[{"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/media?parent=321"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/categories?post=321"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.ua.es\/jpm33\/wp-json\/wp\/v2\/tags?post=321"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}