Archivo de la categoría: MySQL

Calcular la edad con MySQL – MySQL Tips

Muchas veces este tipo de calculos recurrimos ha hacerlos por medio de alguna función, y no nos paramos a pensar que podemos realizarlo en la misma consulta y ahorrar asi tiempo, creación de funciones y llamadas a estas. Con la siguiente consulta, tenemos un campo llamado birthDay el cual almacena la fecha de nacimiento dentro de nuestra tabla myTable y la consulta nos devuelve las edades a dia de hoy.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthDay)), '%Y')+0 AS age FROM myTable
Enhanced by Zemanta

Recuperar site offline Drupal

Si por algún motivo se nos queda nuestro sitio de Drupal en modo offline, y no podemos acceder para recuperar el estado normal, ejecutando la siguiente consulta SQL conseguiremos que nuestro sitio vuelva a estar disponible.

 

UPDATE variable SET value = 's:1:"0";' WHERE name= 'site_offline';
DELETE FROM cache WHERE cid = 'variables';

 

LOAD DATA INFILE desde un terminal remoto – MySQL Tips

Siguiendo con las entradas relacionadas con mis notas mentales, hoy me he vuelto a cruzar con una situación que se me plantea de vez en cuando y siempre tengo que tirar de la documentación.

El escenario es el siguiente, tengo un programa en php que se ejecuta en terminal que me realiza una consulta e extracción de datos de una base de datos paradox que esta en un servidor, si he dicho paradox, y me genera un fichero csv el cual a continuación por medio de una consulta de load data infile se me vuelca a una base de datos mysql que tengo en otro servidor. Resulta que la consulta load data infile no he conseguido que se me ejecute, en cuando lo solucione publicare la solución así que lo que hago es una vez terminado el proceso de volcado me conecto al servidor vía terminal y ejecuto la consulta load data infile desde la terminal. Si esto lo hago de esta manera y no ejecutando la consulta directamente es debido a lo lento que funciona el paradox. El proceso de volcado de los datos de paradox al fichero oscila entre los 8-10 minutos, realizando la operación directa el proceso duraba entre 45 minutos a 1 hora.

Primero me conecto al servidor remoto mysql con un usuario y contraseña que tenga permisos para acceso remoto, añadiendo al final la opción –local-infile=1 para poder subir ficheros


#mysql -u [usuario] -p -h [host remoto] --local-infile=1

A continuación ejecuto la consulta para que me vuelque el fichero


LOAD DATA LOCAL INFILE [fichero] REPLACE INTO TABLE [db.table]     CHARACTER SET [tipo caracteres]    FIELDS TERMINATED BY  [terminación campos]     ([columna1], [columna2], ..);

En [fichero] ponemos entre comillas la ruta completa del fichero en nuestro ordenador,  en [db.table] el nombre de la base de datos y la tabla en la que vamos a volcar los datos, en tipo carácter ponemos entre comillas el tipo de codificación p.e cp1250, utf8, en [terminación campos] como están delimitados los campos, p.e ; y finalmente entre paréntesis las columnas del la tabla.

Una vez ejecutado puedo decir que en mi caso se volcaron 42673 registros en 0.53 segundos, esta misma operación la he realizado generando un fichero sql para la inserción de los datos y el tiempo era infinitamente superior a este.

 

Crear una clave foranea con MySQL – Notas Mentales – MySQL Tips

MySQLCon esta entrada voy a inaugurar una serie de minientradas que iré creando, que voy a llamar notas mentales, serán cosas muy cortas y que si no las usamos con frecuencia, siempre que tenemos que tirar de ellas nos toca googlear.

La primera es la consulta para crear una clave foránea en MySQL en una tabla ya creada

 

ALTER TABLE `miTabla` ADD FOREIGN KEY (campodemiTabla) REFERENCES `otraTabla` (`Id`) ;

Fechas PHP a MySQL y vuelta – MySQL Tips

PHP Programming languageUna de las cosas con las que toca lidiar practicamente siempre que trabajamos con fechas contra una base de datos MySQL es que la base va MySQLa almacenar las fechas de la manera ISO “año-mes-dia” y nosotros a la hora de trabajar trabajos con el formato Europeo, ya sea “dia-mes-año” o “dia/mes/año”.

Durante muchos años trabajaba con la tecnica de la función bidireccional, es decir, me creaba una función la cual cuando le llegaba la fecha la convertia en un sentido u otro.

function cambiaFormatoFecha($fechaOriginal)
{
 //Dividimos la fecha de la hora si existe
 $partesStamp = explode(" ", $fechaOriginal);
 // La primera parte es la fecha, la segunda la hora
 $partesFecha = explode ( "-", $partesStamp[0] );
 // La fecha final
 return $partesFecha[2] ."-".$partesFecha[1]."-".$partesFecha[0];
}

MySQL guarda las fechas separadas por guiones, así que si nosotros definimos el formato de fecha separado con guiones, pues eso que tenemos ganado y esta función nos convierte tanto nuestras fechas cuando las introducimos como las fechas que devuelve el MySQL.

Trabajar con esto puede parecer que es una gran ventaja, pero la verdad que a nivel de rendimiento no lo es ya que supone ejecutar la función cada vez que aparezca un registro de fecha.

Una solución bastante mas eficiente es la de procesar las fechas en la consulta. Para eso utilizaremos las funciones incorporadas de MySQL DATE_FORMAT y STR_TO_DATE.

Si queremos unos registros de una determinada fecha podemos hacer:


SELECT * FROM `tabla` WHERE fecha LIKE STR_TO_DATE('mifecha','%d-%m-%Y);

En esta consulta mifecha esta en el formato dia-mes-año

Si queremos ver las fechas en el formato dia-mes-año


SELECT DATE_FORMAT(fecha,'%d-%m-%Y) FROM `tabla`;

Para mas información

STR_TO_DATE http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

DATE_FORMAT http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

AutoMySQLBackup – Copias Automaticas de nuestras bases de datos

Todos nos acordamos de las copias de seguridad o bien cuando no la hemos hecho, y sobre todo cuando el problema es grande. Una manera de automatizar las copias de nuestras bases de datos es por medio de este script el cual es muy fácil de configurar y muy fácil de instalar, y seguro que de alguna nos salvara.

Si tenemos un sistema basado en debian ( como ubuntu )  con el apt-get install se nos bajara y se nos instalara en el sistema.


sudo apt-get install automysqlbackup

Una vez instalado vamos a configurar los 4 parámetros básicos para que funcione en el fichero automysqlbackup que generalmente esta en /etc/default


USERNAME=myUser #Usuario con permisos en la base de datos

PASSWORD=myPass #Contraseña del usuario

DBHOST=localhost #En la mayoría de los casos estará en localhost

DBNAMES="myDb" #Esto si queremos una en particular si son mas bases las ponemos separadas por coma

Ahora ya solo queda añadir la tarea al crontab y ya estarán programadas las copias de seguridad


crontab -e

Con esto abrimos la edición de la crontab


# m h    dom  mon  dow         command

0 0      *         *        *              automysqlbackup > /dev/null 2>&1

La parte del /dev/null 2 >&1 la pongo por si se producen errores en el comando estos no salgan por pantalla.

+Info: AutoMySQLBackup | Free software downloads at SourceForge.net.

vía AutoMySQLBackup | Free software downloads at SourceForge.net.