Capítulo 1. Utilizar el programa cliente mysql

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: translation-feedback@oreilly.com

1.0 Introducción

El sistema de bases de datos MySQL utiliza una arquitectura cliente-servidor. El servidor, mysqld, es el programa que realmente manipula las bases de datos. Para decirle al servidor lo que debe hacer, utiliza un programa cliente que comunique tu intención mediante sentencias escritas en SQL. Los programas cliente se escriben con fines diversos, pero todos interactúan con el servidor conectándose a él, enviando sentencias SQL para que se realicen operaciones en la base de datos y recibiendo los resultados.

Los clientes se instalan localmente en la máquina desde la que quieres acceder a MySQL, pero el servidor puede instalarse en cualquier lugar, siempre que los clientes puedan conectarse a él. Como MySQL es un sistema de base de datos inherentemente en red, los clientes pueden comunicarse con un servidor que se ejecute localmente en tu propia máquina o en algún lugar al otro lado del planeta.

El programa mysql es uno de los clientes incluidos en las distribuciones de MySQL. Cuando se utiliza de forma interactiva, mysql te pide una sentencia, la envía al servidor MySQL para su ejecución y muestra los resultados. mysql también se puede utilizar de forma no interactiva en modo por lotes para leer sentencias almacenadas en archivos o producidas por programas. Esto permite utilizar mysql desde dentro de scripts o trabajos cron o junto con otras aplicaciones.

Este capítulo describe las capacidades de mysqlpara que puedas utilizarlo con mayor eficacia:

  • Configurar una cuenta MySQL utilizando la base de datos cookbook

  • Especificar parámetros de conexión y utilizar archivos de opciones

  • Ejecutar sentencias SQL de forma interactiva y por lotes

  • Controlar el formato de salida de mysql

  • Utilizar variables definidas por el usuario para guardar información

Para probar los ejemplos que se muestran en este libro, necesitas una cuenta de usuario MySQL y una base de datos. Las dos primeras recetas de este capítulo describen cómo utilizar mysql para configurarlas, partiendo de estas premisas:

  • El servidor MySQL se ejecuta localmente en tu propio sistema

  • Tu nombre de usuario y contraseña de MySQL son cbuser y cbpass

  • Tu base de datos se llama cookbook

Si quieres, puedes violar cualquiera de los supuestos. No es necesario que tu servidor se ejecute localmente, ni que utilices el nombre de usuario, la contraseña o el nombre de la base de datos que se emplean en este libro. Naturalmente, en tales casos, deberás modificar los ejemplos en consecuencia.

Aunque decidas no utilizar cookbook como nombre de tu base de datos, te recomendamos que utilices una base de datos dedicada a los ejemplos que se muestran aquí, no una que también utilices para otros fines. De lo contrario, los nombres de las tablas existentes pueden entrar en conflicto con los utilizados en los ejemplos, y tendrás que hacer modificaciones que serían innecesarias con una base de datos dedicada.

Los scripts que crean las tablas utilizadas en este capítulo se encuentran en el directorio tables de la distribución recipes que acompaña al Libro de cocina MySQL. Los demás scripts se encuentran en el directorio mysql. Para obtener la distribución recipes, consulta el Prefacio.

1.1 Configurar una cuenta de usuario MySQL

Problema

Necesitas una cuenta para conectarte a tu servidor MySQL.

Solución

Utiliza las sentencias CREATE USER y GRANT para configurar la cuenta. A continuación, utiliza el nombre de cuenta y la contraseña para establecer conexiones con el servidor.

Debate

Conectarse a un servidor MySQL requiere un nombre de usuario y una contraseña. Puede que también necesites especificar el nombre del host en el que se ejecuta el servidor. Si no especificas explícitamente los parámetros de conexión, mysql asume valores por defecto. Por ejemplo, si no se especifica explícitamente el nombre del host, mysql asume que el servidor se está ejecutando en el host local.

Si otra persona ya te ha configurado una cuenta y te ha concedido privilegios para crear y modificar la base de datos cookbook, utiliza esa cuenta. De lo contrario, el siguiente ejemplo muestra cómo utilizar el programa mysql para conectar con el servidor y emitir las sentencias que configuran una cuenta de usuario con privilegios para acceder a una base de datos llamada cookbook. Los argumentos para mysql incluyen -hlocalhost para conectar con el servidor MySQL que se ejecuta en el host local, -u root para conectar como el usuario MySQL root, y -p para indicar a mysql que solicite una contraseña:

$ mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54117
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost';
Query OK, 0 rows affected (0.09 sec)
mysql> GRANT PROCESS ON *.* to  `cbuser`@`localhost` ;
Query OK, 0 rows affected (0,01 sec)
mysql> quit
Bye
Consejo

El privilegio PROCESS es necesario si necesitas generar un archivo de volcado de tus datos MySQL. Véase también la Receta 1.4.

Si intentas invocar mysql y recibe un mensaje de error que indica que no se puede encontrar o que es un comando no válido, significa que tu intérprete de comandos no sabe dónde está instalado mysql. Consulta la Receta 1.3 para obtener información sobre cómo configurar la variable de entorno PATH que el intérprete utiliza para encontrar comandos.

En los comandos mostrados, el $representa el prompt mostrado por tu shell o intérprete de comandos, y mysql> es el prompt mostrado por mysql. El texto que escribes se muestra en negrita. El texto que no está en negrita (incluidas las instrucciones) es la salida del programa; no escribas nada de eso.

Cuando mysql imprima el prompt de contraseña, introduce la contraseña de MySQL root donde veas la ******; si el usuario de MySQL root no tiene contraseña, simplemente pulsa la tecla Intro (o Retorno) en el prompt de contraseña. Verás el mensaje de bienvenida de MySQL, que puede ser ligeramente diferente según la versión de MySQL que utilices. A continuación, introduce las sentencias CREATE USER y GRANT como se muestra.

El comando quit finaliza tu sesión mysql. También puedes terminar una sesión utilizando un comando o (en Unix) tecleando Ctrl-D. exit

Para conceder a la cuenta cbuser acceso a una base de datos distinta de cookbook, sustituye el nombre de la base de datos donde veas cookbook en la declaración GRANT. Para conceder acceso a la base de datos cookbook a una cuenta existente, omite la declaración CREATE USER y sustituye esa cuenta por 'cbuser'@'localhost' en la declaración GRANT.

Nota

El registro de cuenta de usuario MySQL contiene dos partes: el nombre de usuario y el host. El nombre de usuario es un identificador del usuario que accede al servidor MySQL. Puedes especificar cualquier cosa para esta parte. El nombre de host es la dirección IP o el nombre del host desde el que este usuario se conectará al servidor MySQL. Hablaremos del modelo de seguridad MySQL y de las cuentas de usuario en la Receta 24.0.

La parte hostname de 'cbuser'@'localhost' indica el host desde el que te conectarás al servidor MySQL. Para configurar una cuenta que se conectará a un servidor que se ejecuta en el host local, utiliza localhost, como se muestra. Si piensas conectarte al servidor desde otro host, sustitúyelo en las sentencias CREATE USER y GRANT. Por ejemplo, si vas a conectarte al servidor desde un host llamado mihost.ejemplo.com, las sentencias tendrán el siguiente aspecto:

mysql> CREATE USER 'cbuser'@'myhost.example.com' IDENTIFIED BY 'cbpass';
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'myhost.example.com';

Puede que se te haya ocurrido que hay una paradoja en el procedimiento que acabamos de describir: para configurar una cuenta cbuser que pueda conectarse al servidor MySQL, primero debes conectarte al servidor para poder ejecutar las sentencias CREATE USER y GRANT. Asumo que ya puedes conectarte como usuario de MySQL rootporque CREATE USER y GRANT sólo pueden ser utilizadas por un usuario como root que tenga los privilegios administrativos necesarios para configurar otras cuentas de usuario. Si no puedes conectarte al servidor como root, pide a tu administrador de MySQL que te cree la cuenta cbuser.

Tras crear la cuenta cbuser, comprueba que puedes utilizarla para conectarte al servidor MySQL. Para ello, desde el host que se nombró en la declaración CREATEUSER , ejecuta el siguiente comando (el host nombrado después de -h debe ser el host donde se está ejecutando el servidor MySQL):

$ mysql -h localhost -u cbuser -p
Enter password: cbpass

Ahora puedes proceder a crear la base de datos cookbook y las tablas que contiene, como se describe en la Receta 1.2. Para que sea más fácil invocar mysql sin especificar cada vez los parámetros de conexión, ponlos en un archivo de opciones (ver Receta 1.4).

Ver también

Para obtener información adicional sobre la administración de cuentas MySQL, consulta el Capítulo 24.

1.2 Crear una base de datos y una tabla de ejemplo

Problema

Quieres crear una base de datos y configurar tablas dentro de ella.

Solución

Utiliza una sentencia CREATE DATABASE para crear la base de datos, una sentencia CREATE TABLE para cada tabla, y sentencias INSERT para añadir filas a las tablas.

Debate

La sentencia GRANT mostrada en la Receta 1.1 establece privilegios para acceder a la base de datos cookbook, pero no crea la base de datos. Esta sección muestra cómo hacerlo y también cómo crear una tabla y cargarla con los datos de muestra utilizados para los ejemplos de las secciones siguientes. Se aplican instrucciones similares para crear otras tablas utilizadas en otras partes de este libro.

Conéctate al servidor MySQL como se muestra al final de la Receta 1.1, y luego crea la base de datos así:

mysql> CREATE DATABASE cookbook;

Ahora que tienes una base de datos, puedes crear tablas en ella. En primer lugar, selecciona cookbook como base de datos por defecto:

mysql> USE cookbook;

A continuación, crea una tabla sencilla:

mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT, PRIMARY KEY(thing));

Y rellénalo con unas cuantas filas:

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('centipede',99,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);
Consejo

Para introducir las sentencias INSERT más fácilmente, después de introducir la primera, pulsa la flecha hacia arriba para recuperarla, pulsa Retroceso (o Suprimir) unas cuantas veces para borrar los caracteres hasta el último paréntesis abierto y, a continuación, escribe los valores de los datos de la siguiente sentencia. O, para evitar teclear las sentencias INSERT, pasa directamente a la Receta 1.6.

La tabla que acabas de crear se llama limbs y contiene tres columnas para registrar el número de piernas y brazos que poseen diversas formas de vida y objetos. La fisiología del alienígena de la última fila es tal que no se pueden determinar los valores adecuados para las columnas arms y legs; NULLindica valor desconocido.

La cláusula PRIMARY KEY define la clave primaria que identifica de forma única la fila de la tabla. Esto evita la inserción de datos ambiguos en la tabla y también ayuda a MySQL a realizar consultas más rápidamente. Hablaremos de los datos ambiguos en el Capítulo 18 y de los problemas de rendimiento en el Capítulo 21.

Comprueba que las filas se han añadido a la tabla limbs ejecutando una sentencia SELECT:

mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing        | legs | arms |
+--------------+------+------+
| human        |    2 |    2 |
| insect       |    6 |    0 |
| squid        |    0 |   10 |
| fish         |    0 |    0 |
| centipede    |   99 |    0 |
| table        |    4 |    0 |
| armchair     |    4 |    2 |
| phonograph   |    0 |    1 |
| tripod       |    3 |    0 |
| Peg Leg Pete |    1 |    2 |
| space alien  | NULL | NULL |
+--------------+------+------+
11 rows in set (0,01 sec)

Llegados a este punto, ya tienes una base de datos y una tabla. Para más información sobre la ejecución de sentencias SQL, consulta las Recetas 1.5 y 1.6 .

Nota

En este libro, las sentencias SQL muestran las palabras clave como SELECT o INSERT en mayúsculas para distinguirlas. Es sólo una convención tipográfica; las palabras clave pueden estar en cualquier caso de letra.

1.3 Encontrar el cliente mysql

Problema

Cuando invocas al cliente mysql desde la línea de comandos, tu intérprete de comandos no puede encontrarlo.

Solución

Añade el directorio donde está instalado mysqla tu configuración de PATH. Entonces podrás ejecutar mysql desde cualquier directorio fácilmente.

Debate

Si tu shell o intérprete de comandos no encuentra mysql cuando lo invoques, verás algún tipo de mensaje de error. Puede tener este aspecto en Unix:

$ mysql
mysql: Command not found.

O así en Windows:

C:\> mysql.exe
'mysql.exe' is not recognized as an internal or external command,↩
operable program or batch file.

Una forma de decirle a tu intérprete de comandos dónde encontrar mysql es escribir su ruta completa cada vez que lo ejecutes. El comando podría tener este aspecto en Unix:

$ /usr/local/mysql/bin/mysql

O así en Windows:

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql"

Teclear nombres de ruta largos se hace pesado muy rápidamente. Puedes evitarlo cambiando la ubicación al directorio donde está instalado mysql antes de ejecutarlo. Pero si lo haces, puedes tener la tentación de poner todos tus archivos de datos y archivos SQL por lotes en el mismo directorio que mysql, saturando así innecesariamente una ubicación destinada sólo a programas.

Una solución mejor es modificar tu variable de entorno PATH search-path, que especifica los directorios donde el intérprete de comandos busca los comandos. Añade al valor PATH el directorio donde está instalado mysql. Entonces podrás invocar mysql desde cualquier ubicación introduciendo sólo su nombre, lo que elimina tener que escribir el nombre de la ruta. Para obtener instrucciones sobre cómo configurar tu variable PATH, lee "Ejecutar programas desde la línea de comandos" en el repositorio GitHub asociado (consulta el Prefacio).

En Windows, otra forma de evitar que escriba el nombre de la ruta o entre en el directorio mysql es crear un acceso directo y colocarlo en un lugar más cómodo, como el escritorio. Esto facilita el inicio de mysql simplemente abriendo el acceso directo. Para especificar las opciones del comando o el directorio de inicio, edita las propiedades del acceso directo. Si no invocas siempre mysql con las mismas opciones, puede ser útil crear un acceso directo para cada conjunto de opciones que necesites. Por ejemplo, crea un acceso directo para conectarte como usuario normal para el trabajo general y otro para conectarte como usuario de MySQL root con fines administrativos.

1.4 Especificar las opciones del comando mysql

Problema

Cuando invocas el programa mysqlsin opciones de comando, sale inmediatamente con un mensaje de error.

Solución

Debes especificar los parámetros de conexión. Hazlo en la línea de comandos, en un archivo de opciones o utilizando una mezcla de ambos.

Debate

Si invocas mysql sin opciones de comando, el resultado puede ser un error de acceso denegado. Para evitarlo, conéctate al servidor MySQL como se muestra en la Receta 1.1, utilizando mysql de la siguiente manera:

$ mysql -h localhost -u cbuser -p
Enter password: cbpass

Cada opción es la forma abreviada de un guión : -h y -u para especificar el nombre de host y el nombre de usuario, y -p para que se te pida la contraseña. También existen las correspondientes formas largas de doble guión: --host --user y --password. Utilízalas así:

$ mysql --host=localhost --user=cbuser --password
Enter password: cbpass

Para ver todas las opciones que admite mysql , utiliza este comando:

$ mysql --help

La forma de especificar las opciones de comando para mysql también se aplica a otros programas MySQL como mysqldump y mysqladmin. Por ejemplo, para generar un archivo de volcado llamado cookbook.sql que contenga una copia de seguridad de las tablas de la base de datos cookbook, ejecuta mysqldump de la siguiente manera:

$ mysqldump -h localhost -u cbuser -p cookbook > cookbook.sql
Enter password: cbpass

Algunas operaciones requieren una cuenta administrativa MySQL. El programa mysqladmin puede realizar operaciones que sólo están disponibles para la cuenta MySQL root. Por ejemplo, para detener el servidor, invoca mysqladmin de la siguiente manera:

$ mysqladmin -h localhost -u root -p shutdown
Enter password:        ← enter MySQL root account password here

Si el valor que utilizas para una opción es el mismo que su valor por defecto, puedes omitir la opción. Sin embargo, no hay contraseña por defecto. Si quieres, puedes especificar la contraseña directamente en la línea de comandos utilizando -ppassword ( sin espacio entre la opción y la contraseña) o --password=password.

Advertencia

No lo recomendamos porque la contraseña es visible para los curiosos y, en sistemas multiusuario, puede ser descubierta por otros usuarios que ejecuten herramientas como ps, que informan sobre los procesos o pueden leer el contenido de tu archivo de historial del intérprete de comandos.

Como el host por defecto es localhost, el mismo valor que hemos estado especificando explícitamente, puedes omitir la opción -h (o --host) de la línea de comandos:

$ mysql -u cbuser -p

Pero supongamos que realmente prefieres no especificar ninguna opción. ¿Cómo puedes hacer que mysql sepa simplemente qué valores utilizar? Es fácil, porque los programas MySQL admiten archivos de opciones:

  • Si colocas una opción en un archivo de opciones, no es necesario que la especifiques en la línea de órdenes cada vez que invoques a un programa determinado.

  • Puedes mezclar opciones de la línea de comandos y del archivo de opciones. Esto te permite almacenar los valores de opción más utilizados en un archivo, pero anularlos como desees en la línea de comandos.

El resto de esta sección describe estas capacidades.

Especificación de los parámetros de conexión mediante archivos de opciones

Para evitar introducir opciones en la línea de comandos cada vez que invoques amysql , ponlas en un archivo de opciones para que mysql las lea automáticamente. Los archivos de opciones son archivos de texto sin formato:

  • En Unix, tu archivo de opciones personal se llama .my.cnfy está en tu directorio personal. También existen archivos de opciones para todo el sitio que los administradores pueden utilizar para especificar parámetros que se aplican globalmente a todos los usuarios. Puedes utilizar el archivo my. cnf en el directorio /etc o /etc/mysql, o en el directorio etc bajo el directorio de instalación de MySQL.

  • En Windows, los archivos que puedes utilizar incluyen el archivo my.ini o my.cnf en tu directorio de instalación de MySQL (por ejemplo, C:\ArchivosPrograma\MySQL\MySQL Server 8.0), tu directorio de Windows (probablemente C:\WINDOWS) o el directorio C:\.

Para ver la lista exacta de ubicaciones de archivos de opciones permitidas, invoca mysql --help .

El siguiente ejemplo ilustra el formato utilizado en los archivos de opciones de MySQL:

# general client program connection options
[client]
host     = localhost
user     = cbuser
password = cbpass

# options specific to the mysql program
[mysql]
skip-auto-rehash
pager="/usr/bin/less -i" # specify pager for interactive mode

Con los parámetros de conexión listados en el grupo [client] como se acaba de mostrar, puedes conectarte como cbuser invocando a mysql sin opciones en la línea de comandos:

$ mysql

Lo mismo ocurre con otros programas cliente de MySQL, como mysqldump.

Advertencia

La opción password se almacena en el archivo de configuración en formato de texto plano, y cualquier usuario que tenga acceso a este archivo puede leerlo. Si quieres proteger las credenciales de conexión, debes utilizar mysql_config_editor para almacenarlas de forma segura.

mysql_config_editor almacena las credenciales de conexión en un archivo, llamado .mylogin.cnf, ubicado en tu directorio personal en Unix y en el directorio %APPDATA%\MySQL en Windows. Sólo admite los parámetros de conexión host, user, password y socket. La opción --login-pathespecifica un grupo en el que se almacenan las credenciales. El valor por defecto es [client].

A continuación se muestra un ejemplo de uso de mysql_config_editor para crear un archivo de inicio de sesión encriptado:

$ mysql_config_editor set --login-path=client \
> --host=localhost --user=cbuser --password
Enter password: cbpass

# print stored credentials
$ mysql_config_editor print --all
[client]
user = cbuser
password = *****
host = localhost

Los archivos de opciones de MySQL tienen estas características:

  • Las líneas se escriben en grupos (o secciones). La primera línea de un grupo especifica el nombre del grupo entre corchetes, y las líneas restantes especifican las opciones asociadas al grupo. El archivo de ejemplo que acabamos de mostrar tiene un grupo [client] y un grupo [mysql]. Para especificar opciones para el servidor, mysqld, ponlas en un grupo [mysqld].

  • El grupo de opciones habitual para especificar los parámetros de conexión del cliente es [client]. En realidad, este grupo lo utilizan todos los clientes estándar de MySQL. Al incluir una opción en este grupo, facilitas la invocación no sólo de mysql sino también de otros programas como mysqldumpy mysqladmin. Sólo asegúrate de que cualquier opción que pongas en este grupo sea entendida por todos los programas cliente. De lo contrario, la invocación de cualquier cliente que no la entienda dará lugar a un error de opción desconocida.

  • Puedes definir varios grupos en un archivo de opciones. Por convención, los clientes MySQL buscan los parámetros en el grupo [client] y en el grupo denominado por el propio programa. Esto proporciona una forma cómoda de enumerar los parámetros generales del cliente que quieres que utilicen todos los programas cliente, pero aún así puedes especificar opciones que sólo se apliquen a un programa concreto. El archivo de opciones de ejemplo anterior ilustra esta convención para el programa mysql, que obtiene los parámetros generales de conexión del grupo [client] y también recoge las opciones skip-auto-rehash y pager del grupo [mysql].

  • Dentro de un grupo, escribe las líneas de opción en name=value donde name corresponde a un nombre de opción (sin guiones a la izquierda) y value es el valor de la opción. Si una opción no tiene valor (como skip-auto-rehash), enumera el nombre solo, sin la parte final =value parte.

  • En los archivos de opciones, sólo se permite la forma larga de una opción, no la forma corta. Por ejemplo, en la línea de comandos, el nombre de host puede indicarse utilizando -hhost_name o --host=host_nameEn un archivo de opciones, sólo se permite host=host_name .

  • Muchos programas, mysql y mysqld incluidos, tienen variables de programa además de opciones de comando. (En el caso del servidor, se denominan variables de sistema; consulta la Receta 22.1.) Las variables de programa pueden especificarse en archivos de opciones, igual que las opciones. Internamente, los nombres de las variables de programa utilizan guiones bajos, pero en los archivos de opciones, puedes escribir opciones y variables utilizando indistintamente guiones o guiones bajos. Por ejemplo, skip-auto-rehash y skip_auto_rehash son equivalentes. Para establecer la variable de sistema sql_mode del servidor en un grupo de opciones [mysqld], sql_mode=value y sql-mode=value son equivalentes. (La intercambiabilidad del guión y el guión bajo también se aplica a las opciones o variables especificadas en la línea de comandos).

  • En los archivos de opciones, se permiten espacios alrededor del = que separa el nombre y el valor de una opción. Esto contrasta con las líneas de comandos, donde no se permiten espacios alrededor de =. Si un valor de opción contiene espacios u otros caracteres especiales, puedes entrecomillarlo utilizando comillas simples o dobles. La opción pager ilustra esto.

  • Es habitual utilizar un archivo de opciones para especificar las opciones de los parámetros de conexión (como host, user y password). Sin embargo, el archivo puede enumerar opciones que tengan otros fines. La opción pager mostrada para el grupo [mysql] especifica el programa de paginación que mysql debe utilizar para mostrar la salida en modo interactivo. No tiene nada que ver con la forma en que el programa se conecta al servidor. No recomendamos poner password en el archivo de opciones, porque se almacena como texto sin formato y podría ser descubierto por usuarios que tengan acceso al sistema de archivos del archivo de configuración sin tener necesariamente acceso a la instalación de MySQL.

  • Si un parámetro aparece varias veces en un archivo de opciones, tiene prioridad el último valor encontrado. Normalmente, debes enumerar los grupos específicos de un programa a continuación del grupo [client], de modo que si hay algún solapamiento en las opciones establecidas por los dos grupos, las opciones más generales queden anuladas por los valores específicos del programa.

  • Las líneas que comienzan con los caracteres # o; se ignoran como comentarios. Las líneas en blanco también se ignoran. # puede utilizarse para escribir comentarios al final de las líneas de opción, como se muestra para la opción pager.

  • Las opciones que especifican rutas de archivos o directorios deben escribirse utilizando / como carácter separador de rutas, incluso en Windows, que utiliza \ como separador de rutas. Como alternativa, escribe \ duplicándolo como \\ (esto es necesario porque \ es el carácter de escape de MySQL en las cadenas).

Para saber qué opciones leerá el programa mysql de los archivos de opciones, utiliza este comando:

$ mysql --print-defaults

También puedes utilizar la utilidad my_print_defaults, que toma como argumentos los nombres de los grupos de archivos de opciones que debe leer. Por ejemplo, mysqldump busca opciones en los grupos [client] y [mysqldump]. Para comprobar qué opciones del archivo de opciones están en esos grupos, utiliza este comando:

$ my_print_defaults client mysqldump

Mezclar parámetros de la línea de comandos y del archivo de opciones

Es posible mezclar opciones de la línea de comandos y opciones de los archivos de opciones. Quizás quieras listar tu nombre de usuario y el host del servidor en un archivo de opciones, pero prefieras no almacenar allí tu contraseña. No pasa nada; los programas MySQL leen primero tu archivo de opciones para ver qué parámetros de conexión aparecen allí, y luego comprueban la línea de comandos en busca de parámetros adicionales. Esto significa que puedes especificar algunas opciones de una forma y otras de otra. Por ejemplo, puedes indicar tu nombre de usuario y nombre de host en un archivo de opciones, pero utilizar una opción de contraseña en la línea de comandos:

$ mysql -p
Enter password:        ← enter your password here

Los parámetros de la línea de comandos tienen prioridad sobre los parámetros que se encuentran en tu archivo de opciones, así que para anular un parámetro del archivo de opciones, sólo tienes que especificarlo en la línea de comandos. Por ejemplo, puedes indicar tu nombre de usuario y contraseña habituales de MySQL en el archivo de opciones para uso general. Luego, si en alguna ocasión debes conectarte como usuario de MySQL root, especifica las opciones de usuario y contraseña en la línea de comandos para anular los valores del archivo de opciones:

$ mysql -u root -p
Enter password:        ← enter MySQL root account password here

Para especificar explícitamente que no haya contraseña cuando exista una contraseña no vacía en el archivo de opciones, utiliza --skip-password en la línea de comandos:

$ mysql --skip-password
Nota

A partir de este punto, normalmente mostraremos comandos para programas MySQL sin opciones de parámetros de conexión. Suponemos que suministrarás los parámetros que necesites, ya sea en la línea de órdenes o en un archivo de opciones.

Proteger los archivos de opciones de otros usuarios

En un sistema operativo multiusuario como Unix, protege el archivo de opciones situado en tu directorio personal para evitar que otros usuarios lo lean y averigüen cómo conectarse a MySQL utilizando tu cuenta. Utiliza chmod para hacer que el archivo sea privado, configurando su modo para que sólo tú puedas acceder a él. Cualquiera de los siguientes comandos lo hace:

$ chmod 600 .my.cnf
$ chmod go-rwx .my.cnf

En Windows, puedes utilizar el Explorador de Windows para establecer los permisos de los archivos.

1.5 Ejecutar sentencias SQL de forma interactiva

Problema

Has iniciado mysql. Ahora quieres enviar sentencias SQL al servidor MySQL para que se ejecuten.

Solución

Sólo tienes que escribirlas, indicando a mysql dónde termina cada una. Como alternativa, especifica las líneas de una sola línea directamente en la línea de comandos.

Debate

Cuando invocas mysql, por defecto, muestra un prompt mysql> para indicarte que está listo para recibir entradas. Para ejecutar una sentencia SQL en el indicador mysql>, escribe , añade un punto y coma (;) al final para indicar el final de la sentencia y pulsa Intro. Es necesario un terminador de sentencia explícito; mysqlno interpreta Intro como un terminador porque puedes introducir una sentencia utilizando varias líneas de entrada. El punto y coma es el terminador más común, pero también puedes utilizar \g(ir) como sinónimo del punto y coma. Así, los siguientes ejemplos son formas equivalentes de emitir la misma sentencia, aunque se introduzcan de forma diferente y terminen de forma diferente:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-06 17:43:52 |
+---------------------+
mysql> SELECT
    -> NOW()\g
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-06 17:43:57 |
+---------------------+

Para la segunda sentencia, mysqlcambia el prompt de mysql> a -> para hacerte saber que sigue esperando a ver el terminador de la sentencia.

Los terminadores de sentencia ; y \g no forman parte de la propia sentencia. Son convenciones utilizadas por el programa mysql, que reconoce estos terminadores y los elimina de la entrada antes de enviar la sentencia al servidor MySQL.

Algunas sentencias generan líneas de salida tan largas que ocupan más de una línea en tu terminal, lo que puede dificultar la lectura de los resultados de la consulta. Para evitar este problema, genera una salida vertical terminando la sentencia con \G en lugar de con ; o \g. La salida muestra los valores de las columnas en líneas separadas:

mysql>  USE cookbook
mysql> SHOW FULL COLUMNS FROM limbs LIKE 'thing'\G
*************************** 1. row ***************************
     Field: thing
      Type: varchar(20)
 Collation: utf8mb4_0900_ai_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:

Para producir una salida vertical para todas las sentencias ejecutadas en una sesión, invoca mysql con la opción -E (o --vertical). Para producir una salida vertical sólo paraaquellos resultados que superen la anchura de tu terminal, utiliza --auto-vertical-output.

Para ejecutar una sentencia directamente desde la línea de comandos, especifícala utilizando la opción -e (o --execute). Esto es útil para las sentencias de una sola línea. Por ejemplo, para contar las filas de la tabla limbs, utiliza este comando:

$ mysql -e "SELECT COUNT(*) FROM limbs" cookbook
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+

Para ejecutar varias sentencias, sepáralas con punto y coma:

$ mysql -e "SELECT COUNT(*) FROM limbs;SELECT NOW()" cookbook
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-06 17:43:57 |
+---------------------+

mysql también puede leer sentencias de un archivo o de otro programa (ver Receta 1.6).

1.6 Ejecutar sentencias SQL leídas de un archivo o programa

Problema

Quieres que mysql lea sentencias almacenadas en un archivo para no tener que introducirlas manualmente. O quieres que mysql lea la salida de otro programa.

Solución

Para leer un archivo, redirige la entrada de mysqlo utiliza el comando source. Para leer desde un programa, utiliza una tubería.

Debate

Por defecto, el programa mysql lee la entrada de forma interactiva desde el terminal, pero puedes alimentarlo con sentencias utilizando otras fuentes de entrada, como un archivo o un programa.

Para ello, MySQL admite el modo por lotes, que es conveniente para ejecutar un conjunto de sentencias en repetidas ocasiones sin introducirlas manualmente cada vez. El modo por lotes facilita la configuración de trabajos cron que se ejecutan sin intervención del usuario.

Para crear un script SQL para que mysql lo ejecute por lotes, pon tus sentencias en un archivo de texto. Luego invoca a mysql y redirige su entrada para que lea de ese archivo:

$ mysql cookbook < file_name

Las sentencias leídas desde un archivo de entrada sustituyen a las que introducirías normalmente a mano de forma interactiva, por lo que deben terminar con ;, \g o \G, igual que si las introdujeras manualmente. Los modos interactivo y por lotes difieren en el formato de salida por defecto . Para el modo interactivo, el formato por defecto es el tabular (en caja). En el modo por lotes, el formato por defecto es el delimitado por tabulaciones. Para anular el valor por defecto, utiliza la opción de comando adecuada (ver Receta 1.7).

Los scripts SQL también son útiles para distribuir conjuntos de sentencias SQL a otras personas. De hecho, así es como distribuimos los ejemplos SQL de este libro. Muchos de los ejemplos que se muestran aquí pueden ejecutarse utilizando archivos de secuencia de comandos disponibles en la distribución recipes adjunta (véase el Prefacio). Introduce estos archivos en mysql en modo batch para evitar teclear las sentencias tú mismo. Por ejemplo, cuando una receta muestra una sentencia CREATE TABLE que define una tabla, normalmente encontrarás un archivo SQL por lotes en la distribución recipes que puedes utilizar para crear (y quizás cargar datos en) la tabla. Recuerda que la Receta 1.2 muestra las sentencias para crear y rellenar la tabla limbs. Esas sentencias se mostraron tal y como las introducirías manualmente, pero el directorio de tablas de la distribución recipes incluye un archivo limbs.sql que contiene sentencias para hacer lo mismo. El archivo tiene el siguiente aspecto:

DROP TABLE IF EXISTS limbs;
CREATE TABLE limbs
(
  thing VARCHAR(20),  # what the thing is
  legs  INT,          # number of legs it has
  arms  INT,          # number of arms it has
  PRIMARY KEY(thing)
);

INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
INSERT INTO limbs (thing,legs,arms) VALUES('centipede',99,0);
INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

Para ejecutar las sentencias de este archivo de secuencia de comandos SQL, cambia la ubicaciónal directorio de tablas de la distribución recipes y ejecuta este comando:

$ mysql cookbook < limbs.sql

Observarás que el script contiene una sentencia para eliminar la tabla, si existe, antes de crearla de nuevo y cargarla con datos. Esto te permite experimentar con la tabla, tal vez introduciendo cambios en ella, con la seguridad de que puedes restaurarla fácilmente a su estado inicial en cualquier momento ejecutando de nuevo el script.

El comando que acabamos de mostrar ilustra cómo especificar un archivo de entrada para mysql en la línea de comandos. Alternativamente, para leer un archivo de sentencias SQL desde dentro de una sesión mysql, utiliza un comando source filename (o \.filenameque es sinónimo):

mysql> source limbs.sql
mysql> \. limbs.sql

Los scripts SQL pueden incluir a su vez comandos source o \. para incluir otros scripts. Esto te da una flexibilidad adicional, pero ten cuidado de evitar los bucles.

Un archivo que vaya a ser leído por mysql no tiene por qué ser escrito a mano; puede ser generado por un programa. Por ejemplo, la utilidad mysqldump genera copias de seguridad de la base de datos escribiendo un conjunto de sentencias SQL que vuelven a crear la base de datos. Para recargar la salida de mysqldump, aliméntalaa mysql. Por ejemplo, puedes copiar una base de datos a través de la red a otro servidor MySQL de esta forma:

$ mysqldump cookbook > dump.sql
$ mysql -h other-host.example.com cookbook < dump.sql

mysql también puede leer una tubería, por lo quepuede tomar la salida de otros programas como entrada. Cualquier comando que produzca una salida consistente en sentencias SQL correctamente terminadas puede utilizarse como fuente de entrada para mysql. El ejemplo de volcado y recarga puede reescribirse para conectar los dos programas directamente con una tubería, evitando la necesidad de un archivo intermedio:

$ mysqldump cookbook | mysql -h other-host.example.com cookbook

El SQL generado por programa también puede ser útil para rellenar una tabla con datos de prueba sin tener que escribir las sentencias INSERT a mano. Crea un programa que genere las sentencias y envía su salida a mysql mediante una tubería:

$ generate-test-data | mysql cookbook

Enla receta 6.6 se habla más de mysqldump.

1.7 Controlar el destino y el formato de salida de mysql

Problema

Quieres que la salida de mysql vaya a otro sitio que no sea tu pantalla. Y no quieres necesariamente el formato de salida por defecto.

Solución

Redirige la salida a un archivo, o utiliza una tubería para enviar la salida a un programa. También puedes controlar otros aspectos de la salida de mysql para producir una salida tabular, delimitada por tabulaciones, HTML o XML; suprimir las cabeceras de las columnas; o hacer que mysql sea más o menos verboso.

Debate

A menos que envíes la salida de mysql a otro sitio, irá a tu pantalla. Para guardar la salida de mysql en un archivo, utiliza la capacidad de redirección de tu shell:

$ mysql cookbook > outputfile

Si ejecutas mysql de forma interactiva con la salida redirigida, no puedes ver lo que escribes, así que en este caso normalmente también lees la entrada desde un archivo (u otro programa):

$ mysql cookbook < inputfile > outputfile

Para enviar la salida a otro programa (por ejemplo, para analizar la salidade la consulta), utiliza una tubería:

$ mysql cookbook < inputfile | sed -e "s/\t/:/g" > outputfile

El resto de esta sección muestra cómo controlar el formato de salida de mysql.

Producir salida tabular o delimitada por tabulaciones

mysql elige su formato de salida por defecto en función de si se ejecuta de forma interactiva o no interactiva. Para uso interactivo, mysql escribe la salida en el terminal utilizando el formato tabular (en recuadro):

$ mysql cookbook
mysql> SELECT * FROM limbs WHERE legs=0;
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
3 rows in set (0.00 sec)

Para un uso no interactivo (cuando se redirige la entrada o la salida), mysql escribe una salida delimitada por tabuladores:

$ echo "SELECT * FROM limbs WHERE legs=0" | mysql cookbook
thing   legs    arms
squid   0       10
fish    0       0
phonograph      0       1

Para anular el formato de salida predeterminado, utiliza la opción de comando adecuada. Considera un comando sed, mostrado anteriormente, y cambia sus parámetros para ofuscar la salida:

$ mysql cookbook < inputfile | sed -e "s/table/XXXXX/g" 
$ mysql cookbook -e "SELECT * FROM limbs where legs=4" | ↩
        sed -e "s/table/XXXXX/g"
 thing legs arms 
 XXXXX 4 0 
 armchair 4 2

Como mysql se ejecuta de forma no interactiva en ese contexto, produce una salida delimitada por tabulaciones, que puede ser más difícil de leer que la salida tabular. Utiliza la opción -t (o--table ) para producir una salida tabular más legible:

$ mysql cookbook -t -e "SELECT * FROM limbs where legs=4" | ↩
        sed -e "s/table/XXXXX/g"

+----------+------+------+
| thing    | legs | arms |
+----------+------+------+
| XXXXX    |    4 |    0 |
| armchair |    4 |    2 |
+----------+------+------+

La operación inversa consiste en producir una salida por lotes (delimitada por tabulaciones)en modo interactivo. Para ello, utiliza -B (o --batch).

Producir salida HTML o XML

mysql genera una tabla HTML a partir de cada conjunto de resultados de consulta si utilizas la opción -H (o --html). Esto te permite producir fácilmente una salida para incluirla en una página web que muestre un resultado de consulta. Aquí tienes un ejemplo (con saltos de línea añadidos para facilitar la lectura de la salida):

$ mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook
<TABLE BORDER=1>
<TR><TH>thing</TH><TH>legs</TH><TH>arms</TH></TR>
<TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR>
<TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
</TABLE>

La primera fila de la tabla contiene encabezados de columna. Si no quieres una fila de encabezamiento, consulta la siguiente sección para obtener instrucciones.

Puedes guardar el resultado en un archivo y luego verlo con un navegador web . Por ejemplo, en Mac OS X, haz esto:

$ mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook > limbs.html
$ open -a safari limbs.html

Para generar un documento XML en lugar de HTML, utiliza la opción -X (o --xml):

$ mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook
<?xml version="1.0"?>

<resultset statement="select * from limbs where legs=0
">
  <row>
    <field name="thing">squid</field>
    <field name="legs">0</field>
    <field name="arms">10</field>
  </row>

  <row>
    <field name="thing">fish</field>
    <field name="legs">0</field>
    <field name="arms">0</field>
  </row>

  <row>
    <field name="thing">phonograph</field>
    <field name="legs">0</field>
    <field name="arms">1</field>
  </row>
</resultset>

Puedes reformatear XML para adaptarlo a diversos fines haciéndolo pasar por transformaciones XSLT. Esto te permite utilizar la misma entrada para producir muchos formatos de salida.

Las opciones -H, --html-X , y --xml producen salida sólo para las sentencias que generan un conjunto de resultados, no para sentencias como INSERT o UPDATE.

Para escribir tus propios programas que generen XML a partir de los resultados de las consultas, consulta la Receta 13.15.

Suprimir los encabezados de columna en la salida de la consulta

El formato delimitado por tabuladores es práctico para generar archivos de datos que se importan a otros programas. Sin embargo, la primera fila de salida de cada consulta enumera por defecto los encabezados de columna, lo que puede no ser siempre lo que deseas. Supongamos que un programa llamado summarize produce estadísticas descriptivas para una columna de números. Si generas una salida de mysql para utilizarla con este programa, una fila de encabezado de columna desvirtuaría los resultados porque summarize la trataría como datos. Para crear una salida que sólo contenga valores de datos, suprime la fila de encabezado con la opción --skip-column-names:

$ mysql --skip-column-names -e "SELECT arms FROM limbs" cookbook | summarize

Especificando dos veces la opción silenciosa (-so --silent) se consigue el mismo efecto:

$ mysql -ss -e "SELECT arms FROM limbs" cookbook | summarize

Especificar el delimitador de la columna de salida

En modo no interactivo, mysqlsepara las columnas de salida mediante tabuladores, y no existe ninguna opción para que especifique el delimitador de salida. Para producir una salida que utilice un delimitador diferente, postprocesa la salida mysql. Supón que quieres crear un archivo de salida para que lo utilice un programa que espera que los valores estén separados por caracteres dos puntos (:) en lugar de tabuladores. En Unix, puedes convertir tabuladores en delimitadores arbitrarios utilizando una utilidad como tr o sed. Cualquiera de los siguientes comandos cambia los tabuladores por dos puntos (TAB indica dónde escribes un carácter de tabulación):

$ mysql cookbook < inputfile  | sed -e "s/TAB/:/g" > outputfile
$ mysql cookbook < inputfile  | tr "TAB" ":" > outputfile
$ mysql cookbook < inputfile  | tr "\011" ":" > outputfile

La sintaxis difiere entre versiones de tr; consulta tu documentación local. Además, algunos shells utilizan el carácter tabulador para fines especiales, como completar nombres de archivo. En tales shells, escribe un tabulador literal en el comando precediéndolo de Ctrl-V.

sed es más potente que tr porque entiende expresiones regulares y permite múltiples sustituciones. Esto es útil para que produce una salida en algo como el formato de valores separados por comas (CSV), que requiere tres sustituciones:

  1. Escapa de los caracteres entrecomillados que aparezcan en los datos duplicándolos para que, cuando utilices el archivo CSV resultante, no se interpreten como delimitadores de columna.

  2. Cambia los tabuladores por comas.

  3. Rodea los valores de las columnas con comillas.

sed permite realizar las tres sustituciones en una sola línea de comandos:

$ mysql cookbook < inputfile \
    | sed -e 's/"/""/g' -e 's/TAB/","/g' -e 's/^/"/' -e 's/$/"/' > outputfile

Eso es, cuando menos, críptico. Puedes conseguir el mismo resultado con otros lenguajes que pueden ser más fáciles de leer. Aquí tienes un breve script en Perl que hace lo mismo que el comando sed (convierte la entrada delimitada por tabuladores en salida CSV) e incluye comentarios para documentar cómo funciona:

#!/usr/bin/perl
# csv.pl: convert tab-delimited input to comma-separated values output
while (<>)        # read next input line
{
  s/"/""/g;       # double quotes within column values
  s/\t/","/g;     # put "," between column values
  s/^/"/;         # add " before the first value
  s/$/"/;         # add " after the last value
  print;          # print the result
}

Si llamas al script csv.pl, utilízalo así:

$ mysql cookbook < inputfile  | perl csv.pl > outputfile

tr y sed normalmente no están disponibles en Windows. Perl puede ser más adecuado como solución multiplataforma porque funciona tanto en Unix como en Windows. (En los sistemas Unix, Perl suele estar preinstalado. En Windows, puedes instalarlo libremente).

Otra forma de producir una salida CSV es utilizar el módulo Perl Text::CSV_XS, diseñado para tal fin. La utilidad cvt_file.pl, disponible en la distribución de recetas, utiliza este módulo para construir un reformateador de archivos de uso general.

Controlar el nivel de verbosidad de mysql

Cuando ejecutas mysqlde forma no interactiva, no sólo cambia el formato de salida por defecto, sino que se vuelve más escueto. Por ejemplo, mysqlno imprime el recuento de filas ni indica cuánto tardaron en ejecutarse las sentencias. Para decirle a mysql que sea más verboso, utiliza -v (o --verbose), especificando la opción varias veces para aumentar la verbosidad. Prueba los siguientes comandos para ver cómo difiere la salida:

$ echo "SELECT NOW()" | mysql
$ echo "SELECT NOW()" | mysql -v
$ echo "SELECT NOW()" | mysql -vv
$ echo "SELECT NOW()" | mysql -vvv

Las contrapartidas de -v y--verbose son -s y --silent, que también pueden utilizarse varias veces para aumentar el efecto.

1.8 Utilizar variables definidas por el usuario en sentencias SQL

Problema

Quieres utilizar en una sentencia un valor producido por una sentencia anterior .

Solución

Guarda el valor en una variable definida por el usuario para almacenarlo para su uso posterior.

Debate

Para guardar un valor devuelto por una sentencia SELECT, asígnalo a una variable definida por el usuario. Esto te permite consultarlo en otras sentencias más adelante en la misma sesión (pero no entre sesiones). Las variables de usuario son una extensión específica de MySQL para SQL estándar. No funcionarán con otros motores de bases de datos.

Para asignar un valor a una variable de usuario dentro de una sentencia SELECT , utiliza @var_name :=value La variable puede utilizarse en sentencias posteriores siempre que se permita una expresión, como en una cláusula WHERE o en una sentencia INSERT.

He aquí un ejemplo que asigna un valor a una variable de usuario, y luego hace referencia a esa variable más adelante. Es una forma sencilla de determinar un valor que caracterice alguna fila de una tabla y, a continuación, seleccionar esa fila concreta:

mysql> SELECT MAX(arms+legs) INTO @max_limbs FROM limbs;
Query OK, 1 row affected (0,01 sec)
mysql> SELECT * FROM limbs WHERE arms+legs = @max_limbs;
+-----------+------+------+
| thing     | legs | arms |
+-----------+------+------+
| centipede |   99 |    0 |
+-----------+------+------+

Otro uso de una variable es guardar el resultado de LAST_INSERT_ID() después de crear una nueva fila en una tabla que tenga una columna AUTO_INCREMENT:

mysql> SELECT @last_id := LAST_INSERT_ID();

LAST_INSERT_ID() devuelve el valor más reciente de AUTO_INCREMENT. Al guardarlo en una variable, puedes referirte al valor varias veces en sentencias posteriores, aunque emitas otras sentencias que creen sus propios valores AUTO_INCREMENT y cambien así el valor devuelto por LAST_INSERT_ID(). En la Receta 15.10 se trata más a fondo esta técnica .

Las variables de usuario contienen valores únicos. Si una sentencia devuelve varias filas , la sentencia fallará con un error, pero se asignará el valor de la primera fila:

mysql> SELECT thing FROM limbs WHERE legs = 0;
+------------+
| thing      |
+------------+
| squid      |
| fish       |
| phonograph |
+------------+
3 rows in set (0,00 sec)

mysql> SELECT thing INTO @name FROM limbs WHERE legs = 0;
ERROR 1172 (42000): Result consisted of more than one row
mysql> SELECT @name;
+-------+
| @name |
+-------+
| squid |
+-------+

Si la sentencia no devuelve ninguna fila, no se produce ninguna asignación, y la variable conserva su valor anterior. Si la variable no se ha utilizado anteriormente, su valor es NULL:

mysql> SELECT thing INTO @name2 FROM limbs WHERE legs < 0;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0,00 sec)

mysql> select @name2;
+--------+
| @name2 |
+--------+
| NULL   |
+--------+
1 row in set (0,00 sec)
Consejo

El comando SQL SHOW WARNINGS devuelve mensajes informativos sobre errores recuperables, como la asignación de un resultado vacío a una variable o el uso de una función obsoleta.

Para asignar explícitamente a una variable un valor determinado, utiliza una sentencia SET . La sintaxis SET puede utilizar := o = como operador de asignación:

mysql> SET @sum = 4 + 7;
mysql> SELECT @sum;
+------+
| @sum |
+------+
|   11 |
+------+

Puedes asignar un resultado de SELECT a una variable, siempre que la escribas como una subconsulta escalar (una consulta entre paréntesis que devuelve un único valor):

mysql> SET @max_limbs = (SELECT MAX(arms+legs) FROM limbs);

Los nombres de las variables de usuario no distinguen entre mayúsculas y minúsculas:

mysql> SET @x = 1, @X = 2; SELECT @x, @X;
+------+------+
| @x   | @X   |
+------+------+
| 2    | 2    |
+------+------+

Las variables de usuario sólo pueden aparecer donde se permitan expresiones,no donde se deban proporcionar constantes o identificadores literales. Resulta tentador intentar utilizar variables para cosas como nombres de tabla, pero no funciona. Por ejemplo, si intentas generar un nombre de tabla temporal utilizando una variable como la siguiente, falla:

mysql> SET @tbl_name = CONCAT('tmp_tbl_', CONNECTION_ID());
mysql> CREATE TABLE @tbl_name (int_col INT);
ERROR 1064 (42000): You have an error in your SQL syntax; ↩
check the manual that corresponds to your MySQL server version for ↩
the right syntax to use near '@tbl_name (int_col INT)' at line 1

Sin embargo, puedes generar una sentencia SQL preparada que incorpore @tbl_name, y luego ejecutar el resultado. La receta 6.4 muestra cómo hacerlo.

SET también se utiliza para asignar valores a parámetros almacenados del programa, variables locales y variables del sistema. Para ver ejemplos, consulta el Capítulo 11 y la Receta 22.1.

1.9 Personalizar una ventana de mysql

Problema

Has abierto varias conexiones en diferentes ventanas de terminal y quieres distinguirlas visualmente.

Solución

Establece un indicador mysql con un valor personalizado.

Debate

Puedes personalizar un aviso de mysql proporcionando la opción --prompt al iniciar: 

$ mysql --prompt="MySQL Cookbook> "
MySQL Cookbook>

Si el cliente ya se ha iniciado, puedes utilizar el comandoprompt para cambiarlo interactivamente: 

mysql> prompt MySQL Cookbook> 
PROMPT set to 'MySQL Cookbook> '
MySQL Cookbook>

El comando prompt, al igual que otros comandos mysql, admite una versión abreviada: \R: 

mysql> \R MySQL Cookbook> 
PROMPT set to 'MySQL Cookbook> '
MySQL Cookbook>

Para especificar el valor del aviso en el archivo de configuración, pon la opciónprompt en la sección [mysql]: 

[mysql]
prompt="MySQL Cookbook> "

Las comillas son opcionales y sólo son necesarias cuando quieras incluir caracteres especiales, como un espacio al final de la cadena de consulta.

Por último, puedes especificar un indicador utilizando la variable de entornoMYSQL_PS1 : 

$ export MYSQL_PS1="MySQL Cookbook> "
$ mysql
MySQL Cookbook>

Para restablecer un indicador a su valor por defecto, ejecuta el comando prompt sin argumentos: 

MySQL Cookbook> prompt
Returning to default PROMPT of mysql> 
mysql>
Consejo

Si utilizaste la variable de entorno MYSQL_PS1, el prompt por defecto será el valor de la variable MYSQL_PS1 en lugar de mysql.

El indicador mysql es altamente personalizable. Puedes configurarlo para que muestre la fecha actual, la hora, la cuenta de usuario, la base de datos predeterminada, el host del servidor y otra información sobre tu base de datos conexión. Encontrarás la lista completa de opciones admitidas en el Manual de referencia del usuario de MySQL.

Para que aparezca una cuenta de usuario en el indicador, utiliza la secuencia especial \u para mostrar sólo un nombre de usuario o \U para mostrar la cuenta de usuario completa: 

mysql> prompt \U> 
PROMPT set to '\U> '
cbuser@localhost>

Si te conectas a servidores MySQL en máquinas diferentes, puede que quieraspara ver el nombre de host del servidor MySQL en el prompt. Existe una secuencia especial, \h, sólo para esto: 

mysql> \R \h> 
PROMPT set to '\h> '
Delly-7390>

Para tener la base de datos actual por defecto en el prompt, utiliza la secuencia especial \d : 

mysql> \R \d> 
PROMPT set to '\d> '
(none)> use cookbook
Database changed
cookbook>

mysql admite varias opcionespara incluir la hora en el aviso. Puedes tener la información completa de fecha y hora o sólo parte de ella: 

mysql> prompt \R:\m:\s> 
PROMPT set to '\R:\m:\s> '
15:30:10>
15:30:10> prompt \D> 
PROMPT set to '\D> '
Sat Sep 19 15:31:19 2020>

Advertencia

No puedes especificar el día actual del mes a menos que utilices la fecha actual completa. De esto se informó en MySQL Bug #72071y aún no está solucionado.

Las secuencias especiales pueden combinarse entre sí y con cualquier otro texto, mysql utiliza el juego de caracteres UTF-8 y, si tu terminal también admite UTF-8, puedes utilizar caracteres sonrientes para que tu prompt sea más impresionante. Por ejemplo, para tener a mano información sobre la cuenta de usuario conectada, el host MySQL, la base de datos por defecto y la hora actual, puedes configurar el prompt como \u@\h [📁\d] (🕑\R:\m:\s)> : 

mysql> prompt \u@\h [📁\d] (🕑\R:\m:\s)> 
PROMPT set to '\u@\h [📁\d] (🕑\R:\m:\s)> '
cbuser@Delly-7390 [📁cookbook] (🕑16:15:41)>

1.10 Utilizar programas externos

Problema

Quieres utilizar un programa externo sin salir del comando de cliente mysql prompt.

Solución

Utiliza el comando system para llamar a un programa.

Debate

Aunque MySQL te permite generar contraseñas aleatorias para sus propias cuentas de usuario internas, aún no dispone de una función interna para generar una contraseña de usuario segura para todos los demás casos. Ejecuta el comando system para utilizar una de las herramientas del sistema operativo : 

mysql> system openssl rand -base64 16
p1+iSG9rveeKc6v0+lFUHA==

\! es una versión corta del comando system: 

mysql> \! pwgen -synBC 16 1
Nu=3dWvrH7o_tWiE

pwgen puede no estar instalado en tu sistema operativo. Necesitas instalar el paquete pwgen antes de ejecutar este ejemplo.

system es un comando del cliente mysql y se ejecuta localmente, utilizando permisos propios del cliente. Por defecto, el servidor MySQL se ejecuta como usuario mysql, aunque puedes conectarte utilizando cualquier cuenta de usuario. En este caso, sólo podrás acceder a los programas y archivos permitidos para tu cuenta del sistema operativo. Así, los usuarios normales no pueden acceder al directorio de datos, que pertenece al usuario especial con el que se está ejecutando el proceso mysqld: 

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0,00 sec)

mysql> system ls /var/lib/mysql/
ls: cannot open directory '/var/lib/mysql/': Permission denied
mysql> \! id
uid=1000(sveta) gid=1000(sveta) groups=1000(sveta)

Por la misma razón, systemno ejecuta ningún comando en el servidor remoto.

Puedes utilizar cualquier programa, especificar opciones, redirigir la salida y canalizarla hacia otros comandos. Unainformación útil que puede darte el sistema operativo es cuántos recursos físicos ocupa el proceso mysqld y compararlos con los datos recogidos internamente por el propio servidor MySQL.

MySQL almacena información sobre el uso de memoria de en el archivo . Performance Schema. Su esquema complementario sys contiene vistas que te permiten acceder fácilmente a esta información. En particular, puedes encontrar la cantidad total de memoria asignada en formato legible por humanos consultando la vista sys.memory_global_total vista: 

mysql> SELECT * FROM sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 253.90 MiB      |
+-----------------+
1 row in set (0.00 sec)

mysql> \! ps -o rss hp `pidof mysqld` | awk '{print $1/1024}'
298.66

La cadena del sistema operativo solicita al sistema operativo estadísticas sobre el uso de la memoria física y las convierte a un formato legible por humanos. Este ejemplo muestra que no toda la memoria asignada está instrumentada dentro del servidor MySQL.

Ten en cuenta que necesitas ejecutar el cliente mysql en la misma máquina que tu servidor MySQL para que esto funcione.

1.11 Filtrar y procesar la salida

Advertencia

¡Esta receta sólo funciona en plataformas Unix!

Problema

Quieres cambiar el formato de salida del cliente MySQL más allá de sus capacidades incorporadas.

Solución

Establece en pager una cadena de comandos, filtrando la salida como tú quieras.

Debate

A veces, las capacidades de formateo del cliente mysql no te permiten trabajar fácilmente con el conjunto de resultados. Por ejemplo, el número de filas devueltas podría ser demasiado grande para caber en la pantalla. O el número de columnas puede hacer que el resultado sea demasiado ancho para leerlo cómodamente en la pantalla. Los paginadores estándar del sistema operativo, como less o more, te permiten trabajar con textos largos y anchos más cómodamente.

Puedes especificar qué buscapersonas utilizar proporcionando la opción --pager al iniciar el cliente mysql o utilizando el comando pager y su versión abreviada, \P. Puedes especificar cualquier argumento para el paginador.

Para indicar a mysql que utilice less como localizador, especifica la opción --pager=less o asigna este valor de forma interactiva. Proporciona parámetros de configuración para el comando de la misma forma que lo haces cuando trabajas en tu shell favorito. En el siguiente ejemplo, especificamos las opciones -F y -X, de modo que less sale si el conjunto de resultados es lo suficientemente pequeño como para caber en la pantalla y funciona normalmente cuando es necesario: 

mysql> pager less -F -X
PAGER set to 'less -F -X'
mysql> SELECT * FROM city;
+----------------+----------------+----------------+
| state          | capital        | largest        |
+----------------+----------------+----------------+
| Alabama        | Montgomery     | Birmingham     |
| Alaska         | Juneau         | Anchorage      |
| Arizona        | Phoenix        | Phoenix        |
| Arkansas       | Little Rock    | Little Rock    |
| California     | Sacramento     | Los Angeles    |
| Colorado       | Denver         | Denver         |
| Connecticut    | Hartford       | Bridgeport     |
| Delaware       | Dover          | Wilmington     |
| Florida        | Tallahassee    | Jacksonville   |
| Georgia        | Atlanta        | Atlanta        |
| Hawaii         | Honolulu       | Honolulu       |
| Idaho          | Boise          | Boise          |
| Illinois       | Springfield    | Chicago        |
| Indiana        | Indianapolis   | Indianapolis   |
| Iowa           | Des Moines     | Des Moines     |
| Kansas         | Topeka         | Wichita        |
| Kentucky       | Frankfort      | Louisville     |
:
mysql> SELECT * FROM movies;
+----+------+----------------------------+
| id | year | movie                      |
+----+------+----------------------------+
|  1 | 1997 | The Fifth Element          |
|  2 | 1999 | The Phantom Menace         |
|  3 | 2001 | The Fellowship of the Ring |
|  4 | 2005 | Kingdom of Heaven          |
|  5 | 2010 | Red                        |
|  6 | 2011 | Unknown                    |
+----+------+----------------------------+
6 rows in set (0,00 sec)

Puedes utilizar pager no sólo para embellecer la salida, sino también para ejecutar cualquier comando que pueda procesar texto. Un uso común es buscar un patrón en los datos, impresos por la sentencia de diagnóstico, utilizando grep. Por ejemplo, para ver sólo History list length en la larga salida SHOW ENGINE INNODB STATUS, utiliza \P grep "History list length." Una vez que hayas terminado con la búsqueda, reinicia el localizador con el comando vacío pagero instruye a mysql para que desactive pager e imprima en STDOUT utilizando nopager o \n: 

mysql> \P grep "History list length"
PAGER set to 'grep "History list length"'
mysql> SHOW ENGINE INNODB STATUS\G
History list length 30
1 row in set (0,00 sec)

mysql> SELECT SLEEP(60);
1 row in set (1 min 0,00 sec)

mysql> SHOW ENGINE INNODB STATUS\G
History list length 37
1 row in set (0,00 sec)

mysql> nopager
PAGER set to stdout

Otra opción útil durante el diagnóstico es enviar la salida a ninguna parte. Por ejemplo, para medir la eficacia de una consulta, puede que quieras examinar la variable de estado de la sesión Handler_*. En este caso, no te interesa el resultado de la consulta, sino sólo la salida del siguiente comando de diagnóstico. Es más, quizá quieras enviar datos de diagnóstico a consultores profesionales de bases de datos, pero no quieres que vean la salida real de la consulta por motivos de seguridad.

En este caso, indica a pager que utilice una función hash o que no envíe la salida a ninguna parte: 

mysql> pager md5sum
PAGER set to 'md5sum'
mysql> SELECT 'Output of this statement is a hash';
8d83fa642dbf6a2b7922bcf83bc1d861  -
1 row in set (0,00 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'
mysql> SELECT 'Output of this statement goes to nowhere';
1 row in set (0,00 sec)

mysql> pager
Default pager wasn't set, using stdout.
mysql> SELECT 'Output of this statement is visible';

+-------------------------------------+
| Output of this statement is visible |
+-------------------------------------+
| Output of this statement is visible |
+-------------------------------------+
1 row in set (0,00 sec)
Consejo

Para redirigir la salida de una consulta, los mensajes de información y todos los comandos que escribas a un archivo, utiliza pager cat > FILENAME. Para redirigir a un archivo y seguir viendo la salida, utiliza el comando tee y su versión abreviada, \T. El comando incorporado tee funciona tanto en plataformas UNIX como Windows.

Puedes encadenar comandos pagerutilizando tuberías. Por ejemplo, para imprimir el contenido de la tabla limbs en diferentes estilos de fuente, establece en pager una cadena de llamadas como en la siguiente lista:

  1. tr -d ' ' para eliminar los espacios sobrantes

  2. awk -F'|' '{print "+"$2"+\033[3m"$3"\033[0m+⁠\033[1m"$4"\033​[0m"$5"+"}' para añadir estilos al texto

  3. column -s '+' -t' para una salida bien formateada

mysql> \P tr -d ' ' | ↩
awk -F'|' '{print "+"$2"+\033[3m"$3"\033[0m+\033[1m"$4"\033[0m"$5"+"}' | ↩
column -s '+' -t
PAGER set to 'tr -d ' ' | ↩
awk -F'|' '{print "+"$2"+\033[3m"$3"\033[0m+\033[1m"$4"\033[0m"$5"+"}' | ↩
column -s '+' -t'
mysql> select * from limbs;
                  
thing       legs  arms
                  
human       2     2
insect      6     0
squid       0     10
fish        0     0
centipede   99    0
table       4     0
armchair    4     2
phonograph  0     1
tripod      3     0
PegLegPete  1     2
spacealien  NULL  NULL
                  
11 rows in set (0,00 sec)

Get Libro de cocina de MySQL, 4ª edición now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial