query - the mysql server is running with the--secure-file-priv option so it cannot execute this statement




¿Cómo generar resultados de consulta MySQL en formato CSV? (20)

mysql - lote, -B

Imprima los resultados utilizando la pestaña como separador de columna, con cada fila en una nueva línea. Con esta opción, mysql no usa el archivo histórico. El modo por lotes da como resultado un formato de salida no tabular y el escape de caracteres especiales. El escape se puede deshabilitar usando el modo sin procesar; vea la descripción para la opción --raw.

Esto le dará un archivo separado por pestañas. Dado que las comas (o cadenas que contienen comas) no se escapan, no es sencillo cambiar el delimitador a comas.

¿Existe una manera fácil de ejecutar una consulta MySQL desde la línea de comandos de Linux y generar los resultados en formato CSV ?

Esto es lo que estoy haciendo ahora:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

Se ensucia cuando hay muchas columnas que deben estar entre comillas, o si hay comillas en los resultados que deben escaparse.


  1. lógica:

CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;

Cuando crea una tabla CSV, el servidor crea un archivo de formato de tabla en el directorio de la base de datos. El archivo comienza con el nombre de la tabla y tiene una extensión .frm. El motor de almacenamiento también crea un archivo de datos. Su nombre comienza con el nombre de la tabla y tiene una extensión .CSV. El archivo de datos es un archivo de texto plano. Cuando almacena datos en la tabla, el motor de almacenamiento los guarda en el archivo de datos en formato de valores separados por comas.


Aquí hay una manera bastante retorcida de hacerlo. Lo encontré en algún lugar, no puedo tomar ningún crédito

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

Funciona bastante bien Una vez más, aunque una expresión regular prueba solo escribe.

Explicación Regex:

  • s /// significa sustituir lo que está entre el primer // con lo que hay entre el segundo //
  • la "g" al final es un modificador que significa "todas las instancias, no solo las primeras"
  • ^ (en este contexto) significa principio de línea
  • $ (en este contexto) significa fin de línea

Entonces, juntándolo todo:

s/'/\'/          replace ' with \'
s/\t/\",\"/g     replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          at the end of the line place a "
s/\n//g          replace all \n (newline) with nothing

Como alternativa a la respuesta anterior, puede tener una tabla MySQL que utiliza el motor CSV.

Luego, tendrá un archivo en su disco duro que siempre estará en un formato CSV que podría copiar sin procesarlo.


Desde tu línea de comando, puedes hacer esto:

mysql -h *hostname* -P *port number* --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv*

Créditos: Exportación de tablas de Amazon RDS a un archivo csv


El siguiente guión de bash funciona para mí. Opcionalmente también obtiene el esquema para las tablas solicitadas.

#!/bin/bash
#
# export mysql data to CSV
#https://.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
#

#ansi colors
#http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
blue='\033[0;34m'
red='\033[0;31m'
green='\033[0;32m' # '\e[1;32m' is too bright for white bg.
endColor='\033[0m'

#
# a colored message 
#   params:
#     1: l_color - the color of the message
#     2: l_msg - the message to display
#
color_msg() {
  local l_color="$1"
  local l_msg="$2"
  echo -e "${l_color}$l_msg${endColor}"
}


#
# error
#
# show the given error message on stderr and exit
#
#   params:
#     1: l_msg - the error message to display
#
error() {
  local l_msg="$1"
  # use ansi red for error
  color_msg $red "Error:" 1>&2
  color_msg $red "\t$l_msg" 1>&2
  usage
}

#
# display usage 
#
usage() {
  echo "usage: $0 [-h|--help]" 1>&2
  echo "               -o  | --output      csvdirectory"    1>&2
  echo "               -d  | --database    database"   1>&2
  echo "               -t  | --tables      tables"     1>&2
  echo "               -p  | --password    password"   1>&2
  echo "               -u  | --user        user"       1>&2
  echo "               -hs | --host        host"       1>&2
  echo "               -gs | --get-schema"             1>&2
  echo "" 1>&2
  echo "     output: output csv directory to export mysql data into" 1>&2
  echo "" 1>&2
  echo "         user: mysql user" 1>&2
  echo "     password: mysql password" 1>&2
  echo "" 1>&2
  echo "     database: target database" 1>&2
  echo "       tables: tables to export" 1>&2
  echo "         host: host of target database" 1>&2
  echo "" 1>&2
  echo "  -h|--help: show help" 1>&2
  exit 1
}

#
# show help 
#
help() {
  echo "$0 Help" 1>&2
  echo "===========" 1>&2
  echo "$0 exports a csv file from a mysql database optionally limiting to a list of tables" 1>&2
  echo "   example: $0 --database=cms --user=scott --password=tiger  --tables=person --output person.csv" 1>&2
  echo "" 1>&2
  usage
}

domysql() {
  mysql --host $host -u$user --password=$password $database
}

getcolumns() {
  local l_table="$1"
  echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null
}

host="localhost"
mysqlfiles="/var/lib/mysql-files/"

# parse command line options
while true; do
  #echo "option $1"
  case "$1" in
    # options without arguments
    -h|--help) usage;;
    -d|--database)     database="$2" ; shift ;;
    -t|--tables)       tables="$2" ; shift ;;
    -o|--output)       csvoutput="$2" ; shift ;;
    -u|--user)         user="$2" ; shift ;;
    -hs|--host)        host="$2" ; shift ;;
    -p|--password)     password="$2" ; shift ;;
    -gs|--get-schema)  option="getschema";; 
    (--) shift; break;;
    (-*) echo "$0: error - unrecognized option $1" 1>&2; usage;;
    (*) break;;
  esac
  shift
done

# checks
if [ "$csvoutput" == "" ]
then
  error "ouput csv directory not set"
fi
if [ "$database" == "" ]
then
  error "mysql database not set"
fi
if [ "$user" == "" ]
then
  error "mysql user not set"
fi
if [ "$password" == "" ]
then
  error "mysql password not set"
fi

color_msg $blue "exporting tables of database $database"
if [ "$tables" = "" ]
then
tables=$(echo "show tables" | domysql)
fi

case $option in
  getschema) 
   rm $csvoutput$database.schema
   for table in $tables
   do
     color_msg $blue "getting schema for $table"
     echo -n "$table:" >> $csvoutput$database.schema
     getcolumns $table >> $csvoutput$database.schema
   done  
   ;;
  *)
for table in $tables
do
  color_msg $blue "exporting table $table"
  cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:)
  if [  "$cols" = "" ]
  then
    cols=$(getcolumns $table)
  fi
  ssh $host rm $mysqlfiles/$table.csv
cat <<EOF | mysql --host $host -u$user --password=$password $database 
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
  scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
  (echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
  rm $csvoutput$table.csv.raw
done
  ;;
esac

Esto es lo que hago:

echo $QUERY | \
  mysql -B  $MYSQL_OPTS | \
  perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
  mail -s 'report' [email protected]

El script de Perl (cortado desde otro lugar) hace un buen trabajo al convertir los campos espaciados por tabuladores a CSV.


Esto es simple, y funciona en cualquier cosa sin necesidad de modo por lotes o archivos de salida:

select concat_ws(',',
    concat('"', replace(field1, '"', '""'), '"'),
    concat('"', replace(field2, '"', '""'), '"'),
    concat('"', replace(field3, '"', '""'), '"'))

from your_table where etc;

Explicación:

  1. Reemplace "con" en cada campo -> reemplazar (campo1, '"', '" "')
  2. Rodee cada resultado entre comillas -> concat ('"', resultado1, '"')
  3. Coloque una coma entre cada resultado citado -> concat_ws (',', quoted1, quoted2, ...)

¡Eso es!


La solución OUTFILE proporcionada por Paul Tomblin hace que se escriba un archivo en el propio servidor MySQL, por lo que esto funcionará solo si tiene acceso a FILE , así como acceso de inicio de sesión u otros medios para recuperar el archivo desde ese cuadro.

Si no tiene dicho acceso, y la salida delimitada por tabuladores es un sustituto razonable de CSV (por ejemplo, si su objetivo final es importar a Excel), entonces la solución de Serbaut (con mysql --batch y opcionalmente --raw ) es el camino a seguir.


Muchas de las respuestas en esta página son débiles porque no manejan el caso general de lo que puede ocurrir en formato CSV. por ejemplo, comas y comillas incrustadas en campos y otras condiciones que siempre surgen con el tiempo. Necesitamos una solución general que funcione para todos los datos de entrada CSV válidos.

Aquí hay una solución simple y fuerte en Python:

#!/usr/bin/env python

import csv
import sys

tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)

for row in tab_in:
    comma_out.writerow(row)

Asigne un nombre a ese archivo tab2csv , póngalo en su ruta, déle permisos de ejecución y, a continuación, tab2csv este listado

mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv >outfile.csv

Las funciones de manejo de CSV de Python cubren casos de esquina para formatos de entrada de CSV.

Esto podría mejorarse para manejar archivos muy grandes a través de un enfoque de transmisión.


Para ampliar las respuestas anteriores, la siguiente línea de una línea exporta una sola tabla como un archivo separado por tabulaciones. Es adecuado para la automatización, exportando la base de datos todos los días más o menos.

mysql -B -D mydatabase -e 'select * from mytable'

Convenientemente, podemos usar la misma técnica para enumerar las tablas de MySQL y para describir los campos en una sola tabla:

mysql -B -D mydatabase -e 'show tables'

mysql -B -D mydatabase -e 'desc users'

Field   Type    Null    Key Default Extra
id  int(11) NO  PRI NULL    auto_increment
email   varchar(128)    NO  UNI NULL    
lastName    varchar(100)    YES     NULL    
title   varchar(128)    YES UNI NULL    
userName    varchar(128)    YES UNI NULL    
firstName   varchar(100)    YES     NULL    

Prueba este código:

SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Para obtener más información: http://dev.mysql.com/doc/refman/5.1/en/select-into.html


Si hay PHP instalado en la máquina que está utilizando, puede escribir un script PHP para hacerlo. Requiere que la instalación de PHP tenga instalada la extensión MySQL.

Puede llamar al intérprete de PHP desde la línea de comando así:

php --php-ini path/to/php.ini your-script.php

Estoy incluyendo el --php-ini , porque es posible que necesites usar tu propia configuración de PHP que habilita la extensión MySQL. En PHP 5.3.0+, esa extensión está habilitada de forma predeterminada, por lo que ya no es necesario usar la configuración para habilitarla.

Luego puedes escribir tu script de exportación como cualquier script PHP normal:

<?php
    #mysql_connect("localhost", "username", "password") or die(mysql_error());
    mysql_select_db("mydb") or die(mysql_error());

    $result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");

    $result || die(mysql_error());

    while($row = mysql_fetch_row($result)) {
      $comma = false;
      foreach ($row as $item) {

        # Make it comma separated
        if ($comma) {
          echo ',';
        } else {
          $comma = true;
        }

        # Quote the quotes
        $quoted = str_replace("\"", "\"\"", $item);

        # Quote the string
        echo "\"$quoted\"";
      }
        echo "\n";
    }
?>

La ventaja de este método es que no tiene problemas con los campos de texto y varchar, que tienen texto que contiene nuevas líneas. Esos campos se citan correctamente y esas líneas nuevas en ellos serán interpretadas por el lector CSV como parte del texto, no como separadores de registros. Eso es algo que es difícil de corregir después con sed o algo así.


Si tiene PHP configurado en el servidor, puede usar mysql2csv para exportar un archivo CSV (realmente válido) para una consulta mysql absoluta. Vea mi respuesta en MySQL: ¿SELECCIONAR * EN PERFIL LOCAL? Para un poco más de contexto / información.

Traté de mantener los nombres de las opciones desde mysql por lo que debería ser suficiente para proporcionar las opciones --file y --query :

./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

"Instalar" mysql2csv través de

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

(descargue el contenido de la esencia, verifique la suma de comprobación y hágalo ejecutable).


Solo Unix / Cygwin , canalícelo a través de 'tr':

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

NB: Esto no maneja ni las comas incrustadas, ni las pestañas incrustadas.


Tiny bash script para realizar consultas simples a volcados CSV, inspirado en https://.com/a/5395421/2841607 .

#!/bin/bash

# $1 = query to execute
# $2 = outfile
# $3 = mysql database name
# $4 = mysql username

if [ -z "$1" ]; then
    echo "Query not given"
    exit 1
fi

if [ -z "$2" ]; then
    echo "Outfile not given"
    exit 1
fi

MYSQL_DB=""
MYSQL_USER="root"

if [ ! -z "$3" ]; then
    MYSQL_DB=$3
fi

if [ ! -z "$4" ]; then
    MYSQL_USER=$4
fi

if [ -z "$MYSQL_DB" ]; then
    echo "Database name not given"
    exit 1
fi

if [ -z "$MYSQL_USER" ]; then
    echo "Database user not given"
    exit 1
fi

mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "$1" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $2
echo "Written to $2"

Usando la solución publicada por Tim, creé este script bash para facilitar el proceso (se solicita la contraseña de root, pero puede modificar el script fácilmente para pedir a cualquier otro usuario):

#!/bin/bash

if [ "$1" == "" ];then
    echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
    exit
fi

DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3

echo "MySQL password:"
stty -echo
read PASS
stty echo

mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME

Creará un archivo llamado: database.table.csv


MySQL Workbench puede exportar conjuntos de registros a CSV, y parece manejar muy bien las comas en los campos. El CSV se abre en multa de OpenOffice.


Puedes usar el siguiente comando desde tu ubicación SQL:

" mysql -h (nombre de host / IP>) -u (nombre de usuario) -p (contraseña) databasename <(query.sql)> outputFILE (.txt / .xls) "

ej. nombre de host -xxxx

uname - nombre de usuario

contraseña - contraseña

DBName - employeeDB

queryFile - employee.sql

outputFile - outputFile.xls

mysql -hx.xxx -uusername -ppassword employeeDB <employee.sql> outputFile.xls

Asegúrese de que está ejecutando el comando desde el directorio donde se encuentra SQL o mencione la ruta completa de la ubicación sql en el comando anterior.


$ mysql your_database --password=foo < my_requests.sql > out.csv

Que es pestaña separada. Pipelo así para obtener un verdadero CSV (gracias @therefromhere):

... .sql | sed 's/\t/,/g' > out.csv






quotes