MySQL: el gran tutorial para pri­n­ci­pia­n­tes

El fu­n­da­me­n­to de nuestro universo digital es una acu­mu­la­ción de datos que aumenta sin cesar. Como co­n­se­cue­n­cia, los sistemas de gestión de bases de datos como MySQL son los elementos centrales de Internet y del mundo in­te­r­co­ne­c­ta­do. Estos permiten procesar grandes ca­n­ti­da­des de datos de manera ele­c­tró­ni­ca, ar­chi­var­los con co­he­re­n­cia y gua­r­dar­los pe­r­ma­ne­n­te­me­n­te. Para ello, los datos complejos se de­s­co­m­po­nen en ca­n­ti­da­des ma­ne­ja­bles y, en función de las ne­ce­si­da­des, se re­la­cio­nan entre sí. En nuestro manual de MySQL para pri­n­ci­pia­n­tes te pre­se­n­ta­mos los pri­n­ci­pios básicos de la gestión de bases de datos y te mostramos a través de ejemplos cómo puedes optimizar la gestión de los datos de tu proyecto web con MySQL.

MySQL se cuenta junto a Oracle y Microsoft SQL Server entre los sistemas de gestión de bases de datos re­la­cio­na­les más populares a escala mundial (en­cue­n­tras un listado actual en https://db-engines.com/en/ranking). El software de­sa­rro­lla­do en 1994 por la empresa MySQL AB cuenta hoy en día con el pa­tro­ci­nio de Oracle Co­r­po­ra­tion y se co­me­r­cia­li­za bajo un sistema de licencias dual. Asimismo, además de la edición En­te­r­pri­se pro­pie­ta­ria, Oracle ofrece una versión de código abierto con una licencia GPL.  Esta licencia dual ofrece a las empresas la po­si­bi­li­dad de de­sa­rro­llar apli­ca­cio­nes propias basándose en MySQL sin tener que so­me­te­r­las a la licencia de código abierto. En la comunidad open source, sin embargo, la absorción de MySQL por parte de Oracle es objeto de críticas. MySQL está escrito en C y C++ y, además, está provisto de un ana­li­za­dor si­n­tá­c­ti­co de SQL basado en Yacc con un to­ke­ni­za­dor (escáner léxico) propio. Asimismo, el sistema de gestión de bases de datos se destaca por su amplio soporte de sistemas ope­ra­ti­vos.

En el ámbito del de­sa­rro­llo web, la in­te­gra­ción del proyecto MySQL en el catálogo de productos de Oracle es objeto de de­s­co­n­fia­n­za y crítica debido, pri­n­ci­pa­l­me­n­te, al aumento constante de las di­fe­re­n­cias entre la versión MySQL con licencia GPL y el producto En­te­r­pri­se de pago. Las nuevas funciones del sistema de gestión de bases de datos son cada vez más fre­cue­n­tes, aunque solo en la versión pro­pie­ta­ria. Las bases de datos de errores no públicas y los tests in­su­fi­cie­n­tes hacen suponer a la comunidad que, bajo las alas del software Oracle, el proyecto open source no disfruta de igualdad de co­n­di­cio­nes, lo que des­en­ca­de­na un apoyo cada vez menor por parte de esta.

Ya en el año 2009, el equipo de de­sa­rro­llo principal en torno a Michael “Monty” Widenius, inventor de la base de datos MySQL, le dio la espalda al popular sistema de bases de datos y puso en marcha con MariaDB un fork de código abierto de MySQL. A finales de 2012, di­s­tri­bu­cio­nes Linux como Fedora, OpenSUSE, Slackware y Arch Linux fueron las primeras en re­em­pla­zar MySQL por MariaDB como in­s­ta­la­ción estándar. Un gran número de proyectos open source, de empresas de software y pla­ta­fo­r­mas web conocidas siguieron el mismo ejemplo, entre ellas Mozilla, Ubuntu, Google, Red Hat En­te­r­pri­se Linux, Web of Trust, Team Speak, la Fundación Wikimedia y XAMPP.  

Aún hoy queda patente que, en co­m­pa­ra­ción con la versión de MySQL de código abierto, MariaDB sigue de­sa­rro­llá­n­do­se. Por lo tanto, es de suponer que el fork vaya a superar pronto a su proyecto madre.

Tolkien ha sido cla­si­fi­ca­do en la tabla autores con la clave primaria id_autores 1. Para consultar todas las obras de dicho autor, este se presenta en la tabla obras como clave foránea. De esta manera se solicitan todos los rows enlazados con el id_autores 1.

En la práctica, todas las ope­ra­cio­nes de bases de datos en MySQL se llevan a cabo con comandos SQL es­ta­n­da­ri­za­dos como SELECT, INSERT, UPDATE y DELETE, pero ha­bla­re­mos de ellos en capítulos po­s­te­rio­res de nuestro manual de MySQL.

Como es lógico, también se pueden guardar todos los datos re­fe­re­n­tes a los autores y a sus obras en una única tabla. Un al­ma­ce­na­mie­n­to de datos de estas ca­ra­c­te­rí­s­ti­cas implica que una base de datos contenga un sinnúmero de entradas re­du­n­da­n­tes, ya que, por ejemplo, los datos en las columnas nombre y apellido se es­pe­ci­fi­can de forma in­di­vi­dual para cada obra. Una re­du­n­da­n­cia de este tipo no solo supone una carga para la memoria, sino que también da lugar a que se tengan que efectuar ac­tua­li­za­cio­nes en di­fe­re­n­tes puntos de la base de datos. Es por eso que cuando se trabaja con bases de datos re­la­cio­na­les se limite su contenido a un asunto por tabla. Se habla, en este caso, de una no­r­ma­li­za­ción de los datos.

El campo de apli­ca­ción principal de MySQL es el al­ma­ce­na­mie­n­to de datos en el contexto de las páginas web dinámicas. La co­m­bi­na­ción de MySQL con el software de se­r­vi­do­res web Apache y los lenguajes de script PHP o Perl se ha co­n­so­li­da­do como es­tru­c­tu­ra clásica de software en el de­sa­rro­llo web. El stack web puede llevarse a cabo como LAMP (Linux), MAMP (macOS) o WAMP (Windows) con los sistemas ope­ra­ti­vos de se­r­vi­do­res ha­bi­tua­les.

Para los que empiezan a trabajar con MySQL pueden ser re­co­me­n­da­bles los entornos locales XAMPP para recabar las primeras ex­pe­rie­n­cias con el sistema de gestión de bases de datos, el cual se basa en  MariaDB en la versión actual.

A co­n­ti­nua­ción, te pre­se­n­ta­mos algunos ejemplos prácticos para arrojar más luz sobre los fu­n­da­me­n­tos de MySQL. Nuestro tutorial para MySQL tiene como base el entorno de prueba de XAMPP y los códigos de snippets y las capturas de pantalla se centran en las ope­ra­cio­nes de bases de datos que se llevan a cabo a través de PHP con ayuda de un servidor Apache HTTP en un ordenador Windows. En lugar de la clásica base de datos MySQL, en este caso, se aplica el fork MariaDB. Ac­tua­l­me­n­te, ambos sistemas de gestión de bases de datos son tan co­m­pa­ti­bles que todas las ope­ra­cio­nes son exac­ta­me­n­te iguales. En el marco de un tutorial para pri­n­ci­pia­n­tes es in­di­fe­re­n­te si se trabaja con MySQL o con MariaDB. Para saber cómo puedes instalar un entorno de prueba de manera local en tu ordenador Windows, visita nuestro tutorial de XAMPP. Si quieres aprender a trabajar con bases de datos re­la­cio­na­les desde el principio lo re­co­me­n­da­ble es que te centres di­re­c­ta­me­n­te en MariaDB. Un entorno al­te­r­na­ti­vo y gratuito que se basa en MySQL es AMPPS. También tienes la po­si­bi­li­dad de integrar un stack web pe­r­so­na­li­za­do. MySQL y MariaDB se pueden combinar, según sea necesario, con diversos sistemas ope­ra­ti­vos, se­r­vi­do­res web y lenguajes de scripts. Los paquetes de descarga gratuitos y con licencia GPL están di­s­po­ni­bles en la página mysql.com y mariadb.com. Puedes obtener in­s­tru­c­cio­nes de­ta­lla­das de in­s­ta­la­ción para di­fe­re­n­tes pla­ta­fo­r­mas en la do­cu­me­n­ta­ción en inglés de MySQL y MariaDB.

Para la gestión de MySQL nos basamos en la apli­ca­ción web libre ph­p­M­yA­d­min, contenido en el paquete de in­s­ta­la­ción de XAMPP, pero también se ofrece por separado en la página web oficial del proyecto como paquete de descarga ph­p­M­yA­d­min es co­n­si­de­ra­do como el software estándar para la ad­mi­ni­s­tra­ción de bases de datos MySQL en la World Wide Web. La apli­ca­ción web escrita en PHP y Ja­va­S­cri­pt facilita las ope­ra­cio­nes de bases de datos a través de in­te­r­fa­ces gráficas de usuario. De esta manera se pueden crear y gestionar las tablas de la base de datos re­la­cio­nal có­mo­da­me­n­te en el navegador web. En este sentido, no es necesario en principio conocer los co­rre­s­po­n­die­n­tes comandos SQL.

En el extremo izquierdo de la interfaz de usuario aparece un panel de na­ve­ga­ción en el que se indican todas las tablas en la base de datos a las que tienes acceso con ph­p­M­yA­d­min. Bajo el logo del programa en la esquina superior izquierda, hay una función con la que puedes acceder tanto a la página de inicio del software como a la do­cu­me­n­ta­ción oficial. Además, también existe la po­si­bi­li­dad de co­n­fi­gu­rar el panel de na­ve­ga­ción y ac­tua­li­zar su vista.

A co­n­ti­nua­ción em­pe­za­re­mos nuestro curso intensivo de MySQL para que puedas crear tu primera base de datos.

Confirma la entrada haciendo clic en “Create” (crear). La base de datos creada aparece en el panel de na­ve­ga­ción en el lado izquierdo de la pantalla. Las nuevas bases de datos no incluyen ningún contenido. Para depositar datos es necesario que, a co­n­ti­nua­ción, crees una tabla.

La tabla que aparece a co­n­ti­nua­ción incluye una de­s­cri­p­ción de la es­tru­c­tu­ra de las tablas así como de los posibles ajustes.

Todas estas opciones co­m­pre­n­den los ajustes más im­po­r­ta­n­tes de las columnas de las tablas. Si te mueves hacia la derecha con ayuda de la barra de de­s­pla­za­mie­n­to en­co­n­tra­rás otras po­si­bi­li­da­des de co­n­fi­gu­ra­ción —no incluidas en este tutorial de MySQL para pri­n­ci­pia­n­tes.

La siguiente tabla expone di­fe­re­n­tes tipos de datos que se pueden procesar tanto con MySQL como con MariaDB, así como su campo de valores y sus re­qui­si­tos de al­ma­ce­na­mie­n­to.

Para la tabla de ejemplo users se han co­n­fi­gu­ra­do los si­guie­n­tes ajustes:

Los posibles valores para la columna id se definen como números enteros (íntegro, INT) y llevan el atributo UNSIGNED. El id solo puede adoptar valores numéricos positivos. En “Index” hemos se­le­c­cio­na­do el ajuste PRIMARY para id. A este respecto, el número de ide­n­ti­fi­ca­ción hace de clave primaria para la tabla users y la marca de ve­ri­fi­ca­ción en “A_I” (Auto_Increment) indica al sistema gestor de bases de datos que los ide­n­ti­fi­ca­do­res de cada entrada tienen que generarse como números co­n­se­cu­ti­vos.

Los valores para las columnas forename, surname, email y password se definen como un tipo de datos VARCHAR. Se trata, en este caso, de cadenas de ca­ra­c­te­res variables cuya longitud (M) está limitada a 50 ca­ra­c­te­res mediante la opción “Length/Value”. Para la columna email se activa el índice UNIQUE. De este modo se garantiza que cada dirección de correo ele­c­tró­ni­co de la tabla se guarde una única vez.  

Para las columnas created_at y updated_at hemos se­le­c­cio­na­do el tipo de datos TIMESTAMP. El sistema de gestión de bases de datos almacena datos te­m­po­ra­les para crear y ac­tua­li­zar las entradas en formato YYYY-MM-DD HH:MM:DD. Puesto que el sistema tiene que generar au­to­má­ti­ca­me­n­te una marca temporal para cada entrada, se­le­c­cio­na­mos el valor estándar CURRENT_TIMESTAMP para la columna created_at. La columna updated_at adquiere im­po­r­ta­n­cia cuando se actualiza una entrada. En este sentido, pe­r­mi­ti­mos los valores Null para esta columna y se establece NULL como valor estándar.

En el capítulo sobre las consultas a las bases de datos te ofrecemos una de­s­cri­p­ción detallada sobre la sintaxis de SQL.

Los ajustes pueden guardarse haciendo clic en “Save” (guardar). La tabla users se muestra en el panel de na­ve­ga­ción de la base de datos bajo la opción test.

La barra del menú cuenta con di­fe­re­n­tes pestañas para gestionar las tablas de datos. Si quieres modificar la es­tru­c­tu­ra de una tabla de datos, haz clic en la etiqueta “Structure”; para agregar datos se­le­c­cio­na la opción “Insert” (añadir). ph­p­M­yA­d­min también permite buscar en las tablas, ad­mi­ni­s­trar derechos y exportar conjuntos de datos o im­po­r­tar­los de otras tablas.

En algunas ci­r­cu­n­s­ta­n­cias, los cambios rea­li­za­dos en la es­tru­c­tu­ra de las tablas pueden conllevar una pérdida de in­fo­r­ma­ción. Por eso, antes de editar o eliminar las tablas pre­via­me­n­te creadas es re­co­me­n­da­ble que hagas una copia de seguridad de tu base de datos: haz clic en la pestaña “Export” (exportar), se­le­c­cio­na el formato de datos deseado para el backup y confirma el cambio mediante la opción “Go”. A co­n­ti­nua­ción, se abre una ventana de diálogo en la que el navegador web te pide el destino de al­ma­ce­na­mie­n­to para la descarga. El programa gratuito My­S­Q­L­Du­m­per es una al­te­r­na­ti­va a la creación de copias de seguridad de bases de datos con ph­p­M­yA­d­min.

Tras haber in­tro­du­ci­do las entradas en la tabla de ejemplo users, en los si­guie­n­tes capítulos mostramos cómo se pueden solicitar estos datos vía PHP a través del servidor web Apache.

Para ello, el primer paso consiste en crear la conexión con la base de datos. En PHP se puede recurrir a tres in­te­r­fa­ces: MySQL Extension, MySQL Improved Extension (MySQLi) y PHP Data Objects (PDO).

• MySQL Extension: la extensión MySQL consiste en una interfaz de MySQL hoy obsoleta que fue muy popular en su momento. Frente a MySQLi y PDO, esta versión tiene la de­s­ve­n­ta­ja de que no soporta ni se­n­te­n­cias pre­pa­ra­das ni pa­rá­me­tros con nombre.

• MySQLi: MySQLi es una versión mejorada de la clásica extensión de PHP para acceder a las bases de datos MySQL. La interfaz trabaja de un modo pro­ce­di­me­n­tal, pero también está orientada a objetos. Sin embargo, su uti­li­za­ción es algo limitada en las bases de datos MySQL y Maria DB.

• PDO: los PHP Data Objects (PDO) son in­te­r­fa­ces orie­n­ta­das a objetos que dispone una capa de ab­s­tra­c­ción para acceder a los datos. Así, los PDO no solo permiten la in­te­gra­ción de bases de datos MySQL en PHP, sino también de otros sistemas de bases de datos como Po­s­t­gre­S­QL, Oracle, MSSQL o SQLite.

A partir de aquí nos co­n­ce­n­tra­mos en las co­ne­xio­nes de bases de datos vía PDO.

Para poder hacer pe­ti­cio­nes a una base de datos con un script de PHP, la base de datos se ha de au­te­n­ti­fi­car en primer lugar. Con la siguiente línea de código se crea una conexión con la base de datos a través de un PDO:

Se re­co­mie­n­da in­te­grar­la al principio de cada script que contenga ope­ra­cio­nes de bases de datos.

Se utiliza la palabra clave de PHP new para crear una instancia de la clase base de PDO, cuyo co­n­s­tru­c­tor espera tres pa­rá­me­tros: el Data Source Name (DSN), un nombre de usuario y la co­n­tra­se­ña para la base de datos, si la hubiera. En este caso, el DSN está formado por los si­guie­n­tes pa­rá­me­tros:

• co­n­tro­la­dor PDO para bases de datos: mysql
• servidor de la base de datos (host=): localhost
• nombre de la base de datos (dbname=): test
• colación (charset=): utf8

Si todavía no has definido los datos de acceso para tu base de datos, utiliza el nombre de usuario root y una co­n­tra­se­ña vacía:

La conexión con la base de datos se almacena en la variable $pdo. Esto permite remitir a ella po­s­te­rio­r­me­n­te en el script.

Una vez creada la conexión con la base de datos, se le pueden realizar distintas consultas a medida que se va es­cri­bie­n­do el código en el script. Cuando se finaliza, también se in­te­rru­m­pe la conexión de la base de datos.

Para extraer in­fo­r­ma­ción de un banco de datos es necesario recurrir al lenguaje de bases de datos SQL, que se basa se­má­n­ti­ca­me­n­te en el inglés y tiene un ma­n­te­ni­mie­n­to sencillo. Su sintaxis es, en gran medida, au­toe­x­pli­ca­ti­va.

En SQL se trabaja con se­n­te­n­cias, que también se conocen como consultas o preguntas.

Una consulta SELECT sencilla está formada, por ejemplo, por los si­guie­n­tes co­m­po­ne­n­tes:

Primero se define el comando de SQL SELECT y a co­n­ti­nua­ción las columnas y tablas co­rre­s­po­n­die­n­tes a las que hace re­fe­re­n­cia el comando. Se utiliza un punto y coma para cerrar la sentencia.

Asimismo, tienes la po­si­bi­li­dad de ampliar la sentencia para una condición opcional o para una función de cla­si­fi­ca­ción o de agru­pa­ción:

En este sentido se aplica la co­n­ve­n­ción de que, para facilitar la lectura, los comandos de SQL se escriben en mayúscula y los nombres de las bases de datos, de las tablas y de los campos en minúscula. SQL es fu­n­da­me­n­ta­l­me­n­te un lenguaje sin formato y no hace di­s­ti­n­cio­nes entre ma­yú­s­cu­las y mi­nú­s­cu­las.

En caso de que recurras a nombres de tabla y de columna que se co­rre­s­po­n­dan con palabras clave de SQL pre­de­fi­ni­das (no re­co­me­n­da­ble), estos deben ir entre comillas simples (' ').

A co­n­ti­nua­ción, ilu­s­tra­mos la sintaxis de se­n­te­n­cias SQL sencillas con ejemplos de los comandos SELECT, INSERT, UPDATE y DELETE.

Se utiliza el comando SELECT para consultar series de datos (rows) de un número de­te­r­mi­na­do de tablas. Si se quieren mostrar en el navegador tanto el nombre y los apellidos como las di­re­c­cio­nes de correo ele­c­tró­ni­co de todos los usuarios en la tabla de ejemplo creada con an­te­rio­ri­dad, tienes que crear en el di­re­c­to­rio htdocs de tu entorno de XAMPP un nuevo archivo PHP text.php e incluir el siguiente script:

Este ejemplo de código se lee de la siguiente manera: en primer lugar se inicia el script con la etiqueta de inicio de PHP <?php. En la segunda línea se establece la conexión con la base de datos test en localhost y se guarda en la variable $pdo. La sentencia SQL con el comando SELECT se encuentra en la línea 3. En este punto se indica al sistema de gestión de bases de datos que consulte las columnas forename, surname y email de la tabla users. Esta sentencia se guarda en la variable $sql.

Las líneas de la 4 a la 7 muestran el co­n­s­tru­c­tor foreach, que permite la iteración sobre arrays, lo que significa que una es­tru­c­tu­ra de datos se repasa de forma gradual. Tanto los arrays que se quieren iterar como el modo en que se deben guardar los datos co­n­su­l­ta­dos se definen entre comillas detrás del co­n­s­tru­c­tor foreach:

La variable $pdo se comunica con la base de datos deseada a través de la conexión definida en la línea 2. Con la función query()y enviamos al banco de datos la sentencia SQL al­ma­ce­na­da en la variable $sql.

El servidor web solicita las columnas forename, surname e email de la tabla users de la base de datos test y recorre todas y cada una de las filas de la tabla en el marco del co­n­s­tru­c­tor foreach. La palabra clave de PHP as en la variable $row define dónde se tienen que guardar los datos se­le­c­cio­na­dos.

En la primera ronda del co­n­s­tru­c­tor foreach, el array tendría el siguiente aspecto:

Si se tienen que leer todas las columnas de una tabla de datos, se debe utilizar el asterisco (*) como es­pa­cia­dor en la secuencia SQL.

En los dos ejemplos an­te­rio­res, el servidor web emite datos de usuario en el orden en el que se han in­tro­du­ci­do en la tabla users (conforme al ide­n­ti­fi­ca­dor). Si quieres emitir los datos en otro orden, defínelo con ayuda de la palabra clave de SQL ORDER BY. En el siguiente ejemplo se ofrecen los datos en orden al­fa­bé­ti­co según el nombre:

Rara vez se crean ma­nua­l­me­n­te con ph­p­M­yA­d­min las entradas de una base de datos. Por lo general, es el servidor web es el que escribe los datos durante la ejecución del script, como ocurre, por ejemplo, cuando un usuario de Internet cu­m­pli­me­n­ta un fo­r­mu­la­rio online en una página web o deja un co­me­n­ta­rio en una tienda online. En ambos casos se utiliza el comando de SQL INSERT en un segundo plano.

Las se­n­te­n­cias SQL con el comando INSERT se crean siguiendo el siguiente esquema:

Esto se lee de la siguiente manera: abre la tabla co­rre­s­po­n­die­n­te e introduce los valores 1, 2 y 3 en las columnas 1, 2 y 3.

Un script PHP sencillo con el que se pueda añadir otra entrada en la tabla de ejemplo users puede tener la siguiente apa­rie­n­cia:

Para ac­tua­li­zar conjuntos de datos ya exi­s­te­n­tes, se puede recurrir al comando SQL UPDATE tal y como se muestra en el siguiente ejemplo:

Esta sentencia SQL tiene el siguiente si­g­ni­fi­ca­do: se­le­c­cio­na la tabla indicada y reemplaza el valor en la columna1 por el valor1 y el valor en la columna2 por el valor2 siempre y cuando la columna3 tenga el valor3. Si te olvidas de incluir la condición, MySQL so­bree­s­cri­be estos campos en todos los conjuntos de datos.

En este caso estamos tra­ba­ja­n­do con una sentencia SQL que vincula una operación a una condición. Si tra­s­la­da­mos esto a la tabla de nuestro ejemplo, la dirección de correo ele­c­tró­ni­co del usuario John Doe se actualiza por medio del siguiente script de PHP:

Para tra­n­s­fe­rir los valores se utiliza la sentencia UPDATE siguiente:

Debido a que se quieren ac­tua­li­zar todos los registros, no hace falta formular ninguna condición para la ac­tua­li­za­ción.

Si trabajas con ide­n­ti­fi­ca­do­res en tu base de datos, es adecuado ide­n­ti­fi­car con ellos las entradas que vas a borrar. Si, por ejemplo, quieres eliminar la entrada 5 en la tabla del ejemplo, debes proceder de la siguiente manera:

El comando SQL DELETE elimina siempre una fila entera. Si quieres eliminar valores en columnas de­te­r­mi­na­das de una base de datos, puedes recurrir a la sentencia UPDATE. Con UPDATE tabla SET columna = NULL WHERE  se le puede asignar el valor NULL a una columna, pero siempre que se haya au­to­ri­za­do dicho valor para la columna co­rre­s­po­n­die­n­te.

Con PDO se pueden realizar ope­ra­cio­nes de bases de datos en calidad de se­n­te­n­cias pre­pa­ra­das (prepared sta­te­me­nts). En la ac­tua­li­dad, estas “consultas pre­fa­bri­ca­das” son una práctica habitual en el de­sa­rro­llo web y están re­s­pa­l­da­das, sobre todo, por todos los sistemas de gestión de bases de datos modernos.  

• En los ejemplos expuestos con an­te­rio­ri­dad hemos tra­n­s­fe­ri­do di­re­c­ta­me­n­te valores de parámetro a la sentencia SQL. Los prepared sta­te­me­nts, por el contrario, trabajan con ma­r­ca­do­res que solo po­s­te­rio­r­me­n­te se llenan con valores. Esto hace posible que el SGBD pueda comprobar la validez de los pa­rá­me­tros antes de uti­li­zar­los. Las se­n­te­n­cias pre­pa­ra­das ofrecen, siempre y cuando estas se incluyan en el texto fuente, una pro­te­c­ción efectiva contra la inyección SQL. En este patrón de ataque, los hackers crean o modifican comandos SQL para acceder a in­fo­r­ma­ción sensible, tra­n­s­fe­rir datos o aportar los comandos en un sistema.

La inyección SQL se basa en un conocido fallo de seguridad en el entorno de las bases de datos SQL: si se entregan datos de usuario, por ejemplo, vía $_GET con pa­rá­me­tros estáticos, esto permite a los atacantes en­ri­que­cer el input con me­ta­ca­ra­c­te­res que pueden conducir a efectos in­de­sea­dos en caso de que estos alcancen al in­té­r­pre­te de SQL sin en­ma­s­ca­ra­mie­n­to. Este método de in­fi­l­tra­ción puede pre­ve­ni­r­se con efe­c­ti­vi­dad por medio de consultas pa­ra­me­tri­za­das. En este sentido, los prepared sta­te­me­nts hacen las veces de pla­n­ti­llas para los comandos SQL que se tra­n­s­mi­ten a la base de datos separados de los ve­r­da­de­ros pa­rá­me­tros. Esta valida los datos en­tre­ga­dos, enmascara los me­ta­ca­ra­c­te­res au­to­má­ti­ca­me­n­te e inserta los pa­rá­me­tros en lugar de los ma­r­ca­do­res en la secuencia SQL. 

Las se­n­te­n­cias pre­pa­ra­das ofrecen, además de seguridad, una ventaja en cuanto a re­n­di­mie­n­to. Esta se hace visible cuando el mismo comando SQL se tiene que ejecutar en bucle con di­fe­re­n­tes pa­rá­me­tros. Tras ser analizado si­n­tá­c­ti­ca­me­n­te, la sentencia preparada se introduce en el sistema de base de datos y tiene que eje­cu­tar­se con nuevos pa­rá­me­tros. Así, las consultas complejas pueden agi­li­zar­se de forma co­n­si­de­ra­ble.

En PDO, los prepared sta­te­me­nts pueden eje­cu­tar­se con ayuda de la función prepare(). Esta prepara una sentencia para la ejecución y devuelve un objeto de secuencia. Como marcador para los valores co­rre­s­po­n­die­n­tes se utiliza o el símbolo de in­te­rro­ga­ción (?) o un parámetro con nombre (named parameter).

El siguiente ejemplo de código muestra la operación INSERT como sentencia preparada con pa­rá­me­tros sin nombre:

Ini­cia­l­me­n­te se genera un objeto sentencia de la consulta deseada con ayuda de la función prepare() y se guarda en el array $statement. En lugar de valores de parámetro concretos, se utiliza el signo de in­te­rro­ga­ción como marcador.

Si una sentencia SQL solo contiene ma­r­ca­do­res, los valores en­tre­ga­dos por separado en el código se han de vincular a la sentencia. En PHP se emplea para ello la función bindParam(). Uti­li­za­mos el operador flecha (->) para acceder al método bindParam() del objeto $statement y asignarle variables (1 hace re­fe­re­n­cia al primer signo de in­te­rro­ga­ción, 2 al segundo, etc.).

Ahora, la plantilla SQL re­su­l­ta­n­te puede eje­cu­tar­se tan a menudo como se desea con los pa­rá­me­tros deseados. En el ejemplo actual se definen los valores de las variables para dos registros. La ejecución de las se­n­te­n­cias SQL pre­fa­bri­ca­das tiene lugar para cada entrada mediante execute().

Los pa­rá­me­tros con nombre resultan algo más claros que los ma­r­ca­do­res con el signo de in­te­rro­ga­ción. Se trata, en este caso, de ma­r­ca­do­res pe­r­so­na­li­za­dos de­sig­na­dos, según las ne­ce­si­da­des, por el siguiente esquema:

Los pa­rá­me­tros con nombre no pueden contener espacios en blanco o guiones, por lo que en su lugar se tiene que utilizar el guion bajo (_).

El siguiente ejemplo muestra la operación INSERT como sentencia preparada con pa­rá­me­tros con nombre:

En la sentencia preparada se en­cue­n­tran los pa­rá­me­tros con nombre :forename, :surname, :email y :password. Estos se vinculan a las variables $forename, $surname, $email y $password por medio de bindParam(). En el ejemplo actual hemos designado tanto los pa­rá­me­tros como las variables en co­rre­s­po­n­de­n­cia con los nombres de las columnas de la tabla del ejemplo, ya que no es la sintaxis la que lo pre­de­te­r­mi­na. Sí se re­co­mie­n­da utilliar una de­no­mi­na­ción coherente a favor de un código fuente legible. Tanto la asi­g­na­ción de los valores de las variables como la ejecución de la sentencia SQL se lleva a cabo de forma análoga al ejemplo anterior.

MySQL y MariaDB soportan diversas funciones para trabajar con in­fo­r­ma­ción sobre la fecha y la hora.  Puedes consultar aquí la lista completa de funciones de fecha y hora. En nuestro tutorial de MySQL para pri­n­ci­pia­n­tes te pre­se­n­ta­mos algunas de ellas.

Un posible escenario de apli­ca­ción para las funciones de fecha y hora son, por ejemplo, las consultas a las bases de datos en las que se tienen que leer todos los conjuntos de datos creados en un día de­te­r­mi­na­do.

El siguiente script emite todos los registros de la tabla users del ejemplo creados hoy:

Para mostrar ex­clu­si­va­me­n­te las entradas de hoy se utiliza la siguiente condición en la sentencia SQL:

En primer lugar, se extrae la fecha de la marca temporal guardada en la columna created_at con ayuda de la función DATE() y se compara en el siguiente paso con la fecha actual. El comando SELECT solo se­le­c­cio­na las entradas cuya marca temporal coincida con la fecha actual. 

Como al­te­r­na­ti­va puedes se­le­c­cio­nar la entrada ac­tua­li­za­da el 17.05.2017, a cuyo efecto hay que adaptar la condición de la sentencia SQL:

En este caso se compara la fecha de re­fe­re­n­cia con una concreta. Asimismo, se pueden limitar las consultas a un año, mes o día de­te­r­mi­na­dos.

La siguiente sentencia hace re­fe­re­n­cia a todas las entradas de la tabla users creadas en mayo:

Además de los signos de igualdad, SQL también soporta los si­guie­n­tes ope­ra­do­res de co­m­pa­ra­ción en las co­n­di­cio­nes:

Asimismo, también se pueden vincular varias co­n­di­cio­nes con ope­ra­do­res lógicos:

La siguiente sentencia permite, por ejemplo, se­le­c­cio­nar todas las entradas creadas después de febrero y antes de abril:

Hasta ahora se han guardado los datos relativos a la fecha y hora en nuestra base de datos en el formato an­te­rio­r­me­n­te indicado. Con MySQL y MariaDB estos no están es­ta­ble­ci­dos. La función DATE_FORMAT() ofrece la po­si­bi­li­dad de ajustar la fecha y la hora con pa­rá­me­tros op­cio­na­les.

La tabla que aparece a co­n­ti­nua­ción muestra posibles pa­rá­me­tros para la función DATE_FORMAT(), tal y como aparece es­pe­ci­fi­ca­do en la do­cu­me­n­ta­ción.

Si un script no se ejecuta como estaba previsto, se pueden presentar errores si­n­tá­c­ti­cos en el código fuente o surgir tablas con nombres, columnas o variables in­co­rre­c­tos. El servidor de bases de datos no emite au­to­má­ti­ca­me­n­te estos mensajes de error, sino que, a menudo, el resultado deseado no se ma­ni­fie­s­ta sin hacer re­fe­re­n­cia alguna a la operación fallida.

Con errorInfo(), PDO ofrece una función con la que se pueden recuperar otros datos sobre errores en la última operación realizada en la base de datos, por ejemplo, para emitirlos a través del navegador web.

En el siguiente script de ac­tua­li­za­ción de la dirección de correo ele­c­tró­ni­co se emplea la función errorInfo() combinada con el co­n­s­tru­c­tor if . La condición es la correcta ejecución de la sentencia SQL. Si esta se lleva a cabo como es debido, el servidor web emite el string Update su­c­ce­s­s­ful. De lo contrario, se ejecuta el código es­pe­ci­fi­ca­do en else.

En el ejemplo, se informa al usuario de que se ha producido un error en SQL y se emite tanto la sentencia SQL co­rre­s­po­n­die­n­te como in­fo­r­ma­ción adicional sobre errores por medio de errorInfo():

Si el script se ejecuta a través del servidor web, se obtiene la siguiente in­fo­r­ma­ción:

En la práctica, no es muy común que el navegador web entregue in­fo­r­ma­ción detallada sobre los errores, pues no sirven de nada a los usuarios. Por el contrario, los atacantes po­te­n­cia­les pueden utilizar estos mensajes de error para seguir a las consultas SQL y encontrar las de­bi­li­da­des de las apli­ca­cio­nes. Por esto, es re­co­me­n­da­ble que la in­fo­r­ma­ción general dada a los usuarios acerca de los fallos sea muy general y que la in­fo­r­ma­ción referente a los mismos se almacene de manera interna.

Esto podría lograrse como se muestra a co­n­ti­nua­ción:

La tabla muestra una selección de algunas de las canciones más famosas de los años 60 y puede servir como ejemplo negativo de un mal diseño de bases de datos. 

A primera vista llama la atención el hecho de que la tabla muestre gran cantidad de datos re­du­n­da­n­tes. Estos se eliminan y  di­s­tri­bu­yen en el marco de la no­r­ma­li­za­ción en tablas separadas y se combinan entre sí con ayuda de claves foráneas.

Un buen diseño de bases de datos se distingue por tener un mínimo de re­du­n­da­n­cia. Sin embargo, deben evitarse las entradas dobles a través de la no­r­ma­li­za­ción de las tablas de datos. En el marco del modelo de bases de datos re­la­cio­nal se han es­ta­ble­ci­do tres formas normales co­m­ple­me­n­ta­rias que es­ta­ble­cen reglas fijas para una es­tru­c­tu­ra­ción de datos óptima.

Una tabla se co­rre­s­po­n­de con la primera forma normal cuando todos los valores de los atributos son atómicos. Se entiende por valores de atributo atómicos aquellos que solo contienen in­fo­r­ma­ción, lo que se puede observar cla­ra­me­n­te en nuestro ejemplo negativo.

Ob­se­r­ve­mos, por ejemplo, las columnas album_title e interpret de la tabla album. En lugar de incluir cada dato de la tabla en una columna diferente, lo hemos hecho más fácil y hemos in­tro­du­ci­do datos relativos al año de pu­bli­ca­ción del álbum, así como de la exi­s­te­n­cia de la banda entre pa­ré­n­te­sis detrás del título del álbum o de los datos del in­té­r­pre­te. Este descuido se hace evidente cuando, por ejemplo, se quieren consultar todos los títulos pu­bli­ca­dos en un año de­te­r­mi­na­do.

En este caso, es re­co­me­n­da­ble crear tablas de datos siguiendo las reglas de la primera forma normal. En la tabla de nuestro ejemplo esto tendría el siguiente aspecto:

Si se quiere tra­n­s­mi­tir un título de­te­r­mi­na­do en nuestra base de datos, además de la in­fo­r­ma­ción sobre el álbum y el in­té­r­pre­te, estas deben estar co­m­bi­na­das entre sí en tres tablas separadas con ayuda del comando JOIN y de la clave foránea co­rre­s­po­n­die­n­te.

Las re­la­cio­nes de claves foráneas te permiten acceder a los datos de di­fe­re­n­tes tablas con una única sentencia SQL. Para ello, puedes recurrir a cuatro tipos de JOIN en MySQL y MariaDB:

• INNER JOIN: con INNER JOIN, el sistema de bases de datos busca entradas comunes en tablas enlazadas con JOIN. Solo se leen los conjuntos de datos que guardan coin­ci­de­n­cias entre sí, es decir, en los que los valores de las columnas enlazadas (de clave primaria y clave foránea) de ambas tablas coinciden.

• OUTER JOIN: con OUTER JOIN se pueden es­ta­ble­cer di­fe­re­n­cias entre tablas de datos situadas a la izquierda o a la derecha. A di­fe­re­n­cia de INNER JOIN, no solo se leen los conjuntos de datos que coinciden entre sí en ambas tablas, sino también todos los conjuntos de datos restantes de las tablas de la derecha y de la izquierda.

• LEFT JOIN: se leen todos los conjuntos de datos de la tabla izquierda y de la derecha en los que se en­cue­n­tran coin­ci­de­n­cias.

• RIGHT JOIN: se leen todos los conjuntos de datos de la tabla derecha y de la izquierda en los que se en­cue­n­tran coin­ci­de­n­cias.

En este tutorial de MySQL nos centramos en INNER JOIN.

La sintaxis de INNER JOIN sigue el siguiente esquema:

El comando SELECT combinado con el marcador * indica al sistema de gestión de bases de datos que lea los valores de todas las columnas para los que son de apli­ca­ción la co­n­di­cio­nes de las cláusulas ON y WHERE.

Puesto que en este caso se habla de INNER JOIN, solo se extraen de la base de datos los conjuntos de datos en los que existan coin­ci­de­n­cias entre la clave foránea de la tabla1 y la clave primaria de la tabla2. Además, se puede definir una función de filtro opcional con ayuda de la cláusula WHERE.

Te aclaramos esto a co­n­ti­nua­ción con un ejemplo basado en nuestras tablas no­r­ma­li­za­das album, interpret y title:

El script del ejemplo muestra una operación INNER JOIN en la que la tabla album está re­la­cio­na­da con la tabla interpret y se se­le­c­cio­nan los conjuntos de datos en los que se da una coin­ci­de­n­cia entre la clave primaria y la foránea.

En el marco de una sentencia SQL, se extraen los datos del in­té­r­pre­te de la tabla interpret y los relativos al título del álbum y al año de pu­bli­ca­ción del disco que aparecen en la tabla album.

La decisión de los conjuntos de datos del join que deben vi­sua­li­zar­se viene de­li­mi­ta­da con una condición en la cláusula WHERE. Si, por ejemplo, se quiere acceder a los álbumes pu­bli­ca­dos en el año 1968, se puede proceder de la siguiente manera:

Con ayuda del comando JOIN se pueden unir varias tablas en una red de datos. En el ejemplo siguiente se combina la tabla album en un INNER JOIN con las tablas interpret y title para arrojar in­fo­r­ma­ción completa sobre los títulos musicales al­ma­ce­na­dos en la base de datos.

Este manual de MySQL para pri­n­ci­pia­n­tes está concebido como un curso intensivo que explica los fu­n­da­me­n­tos sobre los sistemas de bases de datos basados en SQL y presenta ejemplos de interés práctico de sencillas ope­ra­cio­nes de bases de datos. Si la in­fo­r­ma­ción expuesta sobre las po­si­bi­li­da­des de la apli­ca­ción objeto de la presente guía es de tu interés, también puedes consultar la do­cu­me­n­ta­ción enlazada en el capítulo in­tro­du­c­to­rio sobre los sistemas de gestión de bases de datos MySQL y MariaDB. Asimismo, Internet también alberga gran cantidad de páginas web que ofrecen tu­to­ria­les y ejemplos de uso sobre el popular sistema de gestión de bases de datos. Es re­co­me­n­da­ble, asimismo, visitar la pla­ta­fo­r­ma online Stack Overflow, en la que una amplia comunidad de usuarios formada por más de 6,5 millones de de­sa­rro­lla­do­res in­te­r­ca­m­bian in­fo­r­ma­ción y hablan de los problemas relativos al de­sa­rro­llo del software. La Digital Guide de IONOS, asimismo, contiene otros muchos artículos sobre bases de datos, a los que puedes acceder haciendo clic sobre las etiquetas que aparecen más abajo.