Etiqueta: MariaDB

MySQL y MariaDB: Consulta para saber en qué tabla está una columna

en por Donnie Rocken Informática, SQL1 comentario

El otro día lo vimos en SQL-Server, ayer en Oracle, vamos hoy con MySQL y su fork MariaDB, seguramente los gestores de bases de datos SQL libres más populares de la actualidad ¿Cómo puedo saber a qué tabla pertenece una columna sabiendo sólo el nombre de columna? Como en los casos anteriores, basta una consulta:

SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'Nombre_Columna'
        AND TABLE_SCHEMA='Nombre_BaseDatos';

Ya sabéis, os toca cambiar el nombre de columna y el nombre del esquema por los que correspondan en vuestro caso.

Añadir columna a una tabla en MySQL (y MariaDB)

en por Donnie Rocken Informática, SQL3 comentarios

Una pequeña entrada sobre SQL básico, porque me lo han preguntado hace un rato, así que aprovecho para contestar por aquí.

Muy bien, tienes una tabla y quieres añadir una columna o campo que se te olvidó al crearla, o que por cambios en la aplicación necesitas añadir. Si te vas a la guía de referencia de MySQL puedes leer todas las posibilidades que tienes, pero como se puede hacer largo y pesado mejor unos ejemplillos de código rápidos aquí:

La sintaxis básica es ALTER TABLE nombreDeLaTabla add nombreDeLaColumna tipodedatos(longitud)

ALTER TABLE tabla1 add colNueva varchar(2);

Vamos con más ejemplos. Por ejemplo, queremos que la columna nueva no permita valores nulos (ojo, esto puede dar problemas si la tabla ya tiene valores añadidos):

ALTER TABLE tabla1 add colNueva varchar(2) NOT NULL;

O por ejemplo queremos colocarlo específicamente después de una columna que ya existe (si no especificamos nada, añadirá la nueva al final):

ALTER TABLE tabla1 add colNueva varchar(2) NOT NULL AFTER col1;

O al revés, queremos insertarla antes de una columna concreta:

ALTER TABLE tabla1 add colNueva varchar(2) NOT NULL BEFORE col2;

También puede venir con un valor por defecto:

ALTER TABLE tabla1 add colNueva varchar(35) DEFAULT 'Ni de ajo';

¿Y si en vez de una quieres insertar varias? Sin problema:

ALTER TABLE tabla1 add (
colNueva varchar(2) NOT NULL,
colMásNueva tinyint(1) default 0
)

Como ves, es sencillo. SQL básico. Como siempre, todo lo aplicable en MySQL lo es también en MariaDB.

Encriptación AES en MySQL (y MaríaDB)

en por Donnie Rocken SQL23 comentarios

El uso de la encriptación AES en MySQL (la más segura de la que dispone el famoso SGBD) es sencillo, y se realiza mediante dos funciones: AES_ENCRYPT y AES_DECRYPT. Permite usar AES_128 o AES_256 dependiendo de la fuente que usemos.

Para insertar o modificar los datos de una columna basta con hacer uso de AES_ENCRYPT, que recibe dos parámetros: el dato a insertar (string) y la clave (string también). A diferencia de SQL-Server, en este caso el dato a encriptar siempre es una cadena. Si quieres conocer de antemano la longitud de la cadena resultado (para ajustar el tamaño del campo en la tabla) lo puedes hacer recurriendo a la fórmula 16 * (trunc(logintud_de_la_cadena / 16) + 1). Si recibe un argumento null el resultado será null también.

Para el caso de AES_DECRYPT los parámetros serán la cadena encriptada a descifrar y la contraseña. Si hay algún error en la encriptación (sea por una modificación maliciosa o por datos de entrada incorrectos) esta función devolverá NULL, aunque es posible que en algún caso pueda dar un valor no nulo erróneo si la cadena encriptada o la contraseña son erróneas.

Una inserción de un registro encriptado se ralizaría de la siguiente forma:

INSERT INTO tb_Ejemplo VALUES(AES_ENCRYPT('valor a encriptar como cadena', 'contraseña'));

En este caso si realizamos una select normal nos devolvería los datos encriptados, para poder verlos tendríamos que hacer

SELECT AES_DECRYPT(COLUMNA_ENCRIPTADA, 'contraseña') FROM tb_Ejemplo;

Finalmente, por seguridad podemos guardar la contraseña en una variable del servidor para no enviarla en cada comunicación con la base de datos, tal que así:

SELECT @pss:='password';
INSERT INTO tb_Ejemplo VALUES (AES_ENCRYPT('texto',@pss));

Sobre cifrado tanto MySQL como MariaDB ofrecen varias opciones aparte de AES, que en este caso es la comentada por ser la más segura y robusta. Pero también existe la posibilidad de usar DES (DES_ENCRYPT y DES_DECRYPT), las funciones DECODE y ENCODE (que no se qué algoritmo usan, la verdad, pero que nos obligan a almacenar los datos en una columna tipo BLOB) y para hashes las funciones MD5, SHA1 y ENCRYPT (que llama a la función crypt() de UNIX, por lo que no puede usarse en Windows). También existe la función PASSWORD, pero es la que usa el sistema de autenticación de MySQL, por lo que se recomienda no usarla en aplicaciones propias.

La función IFNULL de MySQL

en por Donnie Rocken SQL1 comentario

Si el otro día hablaba de la función IF() no es menos interesante la función IFNULL() de MySQL.

En muchos casos te encuentras bases de datos donde en una columna, por falta de restricciones, hay gente que ha usado el nulo en lugar del cero y están todos mezclados (eso me ha pasado a mi, y luego no había forma de cuadrar los balances). O simplemente quieres sacar un listado de producto y deseas que en el nulo ponga un mensaje tipo «no aplicable» (para un descuento) o «sin unidades» (para un registro de almacén). La función IFNULL facilita este trabajo.

Vamos a ver el primer ejemplo citado. Tenemos una columna donde se han usado nulos en valores que realmente deberían llevar cero, y ahora necesitamos exportarlo a Excel y nos va a hacer el lío tenerlos así. Cierto es que una opción sería modificar toda la columna cambiando nulos por ceros, pero supongamos que no es posible (por falta de permisos de edición sobre la tabla, mismamente). En este caso con una línea tal que esta:

SELECT producto, IFNULL(precio, 0) from tbProductos

La función evalúa el primer parámetro y si este es nulo devuelve el valor indicado en el segundo, mientras que si no lo es devuelve el primero. Es decir, en el ejemplo devolvería el precio del producto si no es nulo, y si lo es devolvería cero.

Veamos otro ejemplo, donde probaríamos la función IFNULL con el ejemplo del descuento:

SELECT producto, IFNULL(descuento, 'NO APLICABLE') from tbProductos

La función IFNULL puede devolver valores del tipo INTEGER, REAL o STRING.

Comentar que en MaríaDB también existe, como la mayoría de las funciones de MySQL. Y que en Microsoft SQL-Server existe la función equivalente ISNULL.

La función IF de MySQL

en por Donnie Rocken Informática, SQLDeja un comentario

En el SGBD MySQL existe una función bastante útil llamada IF. Obviamente no estoy hablando de una sentencia condicional if para el control de flujo como las que que tenemos en cualquier lenguaje de programación (Visual Basic, C, Java, PHP… y que en MySQL también existe), sino de una función que recibe tres argumentos: una condición a evaluar, un valor a devolver si se cumple y un valor a devolver si no.

Veámoslo ilustrado con el siguiente ejemplo. Tenemos una lista de productos con sus respectivos precios, y nos interesa saber simplemente si el precio es mayor de 60 euros, para separarlos así entre «caros» y «baratos». Aunque podríamos hacerlo con una par de consultas unidas imprimiendo una cadena, ahorraremos código haciéndolo de la siguiente forma:

Select IdProducto, NompreProducto, if(Precio>=60, 'caro','barato')
from tbArtículos

Incluso podemos usar la función IF con funciones de agregado, combinando. En el siguiente ejemplo supongamos que, con la misma tabla de productos, queremos saber si los proveedores nos sirven sólo un producto o si nos sirven más de uno, no nos importa la cantidad, sólo discriminaremos entre uno y más de uno.

Select Proveedor, if(count(*)>1, 'Más de uno','Sólo uno')
from Productos group by Proveedor

La idea es que la función evalúa si el primer parámetro recibido es verdadero y distinto de nulo, y en base a eso devuelve uno de los dos valores, el primero para TRUE y el segundo para FALSE.

Por cierto, la función IF también existe en el fork libre de MySQL MaríaDB, como otras muchas funciones del popular gestor de bases de datos. No está mal recordarlo, por si algún día Oracle se encabrona y nos deja tirados, ya que puede que algún día MaríaDB sea la mejor opción.