Archivo de la etiqueta: MySQL

Creando consultas Select con Zend_Db_Select – PHP Tips

Uno de los frameworks con los cuales mas me gusta trabajar es con ZendFramework, sobre todo con las funciones de conexión y consulta MySQL, ya que el uso de estas me simplifica el trabajo. Hoy voy a explicar un poco el uso del Zend_Db_Select en la versión de ZendFramework 1.12. Vamos a suponer que la conexión a la base de datos ya la tenemos configurada.


// $db es la conexión a la base de datos

$select = $db->select();

$select->from('mitable');

$select->where('id LIKE ?' => $id);

$stmt = $select->query();

$result = $stmt->fetchAll();

Este seria un ejemplo básico de selección de un dato de una tabla y devolver todos los datos con ese Id.

Para mas información y mas métodos http://framework.zend.com/manual/1.12/en/zend.db.select.html

 

 

 

Como cambiar de MyISAM a INNODB – MySQL Tips

Antes en MySQL el mecanismo de almacenamiento predeterminado esta MyISAM,  hasta que cambio a INNODB. ¿Cual es mejor? ¿Debería migrar mis bases de datos a INNODB? a la primera pregunta diría que es bastante mejor INNODB y si deberíamos o no migrar las bases de datos, pues en casos será conveniente y en otros no, pero si creamos bases de datos desde cero es conveniente establecer el mecanismo como INNODB.

Como cambiar de MyISAM a INNODB


ALTER TABLE nombre_tabla ENGINE=INNODB;

No existe un comando que nos permita cambiar todas las tablas de una base de datos de MyISAM a INNODB, y para hacerlo tenemos que cambiar una a una.

Para facilitar las cosas siempre podemos crearnos un script que acceda a nuestra base de datos, tenga permisos ALTER y hacerlo todo de una vez.

He desarrollado un script en PHP que lo hace

<?php
/**
* Created by Ruben Lacasa Mas
* User: Ruben Lacasa Mas <ruben@rubenlacasa.es>
* Date: 18/10/13
* Time: 12:18
*/
$dbhost = 'hostname';
$dbname = 'dbname';
$dbuser = 'dbuser';
$dbpass = 'dbpassword';
$dsn = 'mysql:dbname='.$dbname.';host='.$dbhost;
try {
$dbh = new PDO($dsn, $dbuser, $dbpass);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$sql = "SHOW TABLES";
$query = $dbh->prepare($sql);
$query->execute();
$tables = $query->fetchAll(PDO::FETCH_CLASS);
foreach ($tables as $table) {
$sqlAlter = "ALTER TABLE ".$table->Tables_in_dbname." ENGINE=INNODB;";
$dbh->exec($sqlAlter);
}

view raw
alterEngine.php
hosted with ❤ by GitHub

Mas información: http://dev.mysql.com/doc/refman/5.0/es/converting-tables-to-innodb.html http://es.wikipedia.org/wiki/InnoDB

Regla de la suma en bases de datos – MySQL Tips

Esta entrada no es específicamente para bases de datos MySQL, y se puede aplicar a cualquier otra base de datos.

Este es un pequeño truco que utilizo a la hora de explicar a alguien como decidir que tipo de dato a establecer a la hora de crear campos en una tabla en una base de datos, y es hacernos la siguiente pregunta ¿vas a sumarlo?, si la respuesta es no el tipo de campo puede establecerse de tipo varchar y si vamos a sumarlo sera de tipo numérico. Por ejemplo, los códigos postales o los teléfonos, ¿vamos a sumar códigos postales o teléfonos? la respuesta es no, por lo tanto los pondremos como tipo varchar, además en el tema de los códigos postales no hay tanto problema pero en el tema de los teléfonos… cada usuario te lo puede poner de cualquier manera, y si ponemos controles de validación del teléfono, pero al final lo que queremos es tener el teléfono del usuario.

Mejorar la velocidad de nuestro sitio Moodle

Uno de los sitios que gestiono es una plataforma de formación basada en Moodle, y la verdad que es bastante completa y tiene todo lo que una plataforma de formación puede necesitar, aunque algunas cosas, en mi opinión son mejorables, y se podrían presentar de una manera mas clara.

Pero dentro de las cosas que mas me preocupaban era lo lento que funcionaba el sitio, y la verdad no comprendía como una plataforma con tanta comunidad detrás de ella y tan usada funcionara tan lenta. Primero pensé que podría ser del servidor,  pero la única web que tenia problemas de rendimiento era esta. Hasta que un día googleando descubrí esta pagina http://opensourceelearning.blogspot.com.es/2012/10/why-your-moodle-site-is-slow-five.html y la verdad, realizando los 5 pasos, y algún otro mas, la velocidad y el rendimiento de la plataforma se ha visto muy mejorado.

Los pasos que nos indica en la web son los siguientes:

  1. Deshabilitar las Copias de seguridad automáticas.  Esto se hace desde Administración del sitio > Cursos > Copias de seguridad > Copia de seguridad programada. Si esta opción no note diferencia, supongo que el problema puede venir cuando se realiza la copia de seguridad que baja el rendimiento.
  2. Deshabilitar las Estadísticas.  Esto se hace desde Administración del sitio > Características avanzadas. Esta opción me mejoro mucho el rendimiento.
  3. Deshabilitar el modo de diseño de temas. Esto se hace desde Administración del sitio > Apariencia > Temas > Ajustes de temas. Esta opción me mejoro mucho el rendimiento.
  4. Establecer el Mantener registros para igual o menor a 365 días. Esto lo establecemos desde Administración del sitio > Servidor > Limpieza. Esta opción no la cambie ya que necesito tener registros de muchos años atrás.
  5. Deshabilitar Usar la base de datos para información de la sesión. Esto se hace desde Administración del sitio > Servidor > Gestión de la sesión. Esta también mejoro mucho el rendimiento del sitio, y como apunte decir que no os asustéis si una vez deshabilitada se cierra la sesión, es normal, se vuelve a iniciar listo.

A parte de estos 5 puntos que nos recomiendan en esa web, hay otra cosa que hay que tener en cuenta y que a mi me ha servido para mejorar el rendimiento del sitio, y es eliminar paneles innecesarios en las paginas, es decir, usar solo los necesarios, y si tenéis que mostrar listados de cursos por pantalla intentar limitar el tamaño a mostrar a 10.

Espero que os sirvan estos consejos.

 

 

Como extraer un valor numerico de una cadena en una consulta MySQL – MySQL Tips

MySQLPuede ser que alguna vez nos veamos en la necesidad que extraer los números que hay almacenados dentro de campo en el cual están combinados los números y texto, por ejemplo ( 1 litro, 1 hora, 25 euros, $4,  horas), etc…)

Si el numero esta al principio de la cadena

En estas situación la solución es muy sencilla, y es la que nos encontraremos la mayoría de las veces, simplemente utilizamos la función CONVERT().

CONVERT() es una función que nos convierte datos entre distintos tipos de caracteres. A esta función le pasaremos como parámetros la cadena o el campo en el cual este el dato que queramos extraer y SIGNED, el cual indicara que queremos que nos lo convierta a un entero, la función quedaría así:


SELECT CONVERT('25 euros', SIGNED)

Esta consulta devolverá 25, pero que pasaría si el numero no estuviera al principio de la cadena de texto,  pues que nos devolverá 0. Por lo tanto solo nos sirve la función CONVERT() cuando la cadena empieza con numero si no no sirve.

Si el numero esta en el segundo lugar

Esta situación es un poco mas complicada, y tendremos que utilizar REGEXP() e IF.  Hace unos días ya hice una entrada referente a RLIKE y REGEXP así que decir que nos permite realizar búsquedas y comparaciones con patrones, y el IF generalmente se usa a la hora de crear procedimientos o funciones, pero también se puede usar dentro de una consulta, basicamente el IF se usa de la siguiente manera:

IF (condicion, si verdadero, si falso)

Imaginemos que nos han introducido los importes de esta manera ‘€ 25’, y queremos con una consulta sacar la suma de todos los importes.


SELECT SUM(
IF (
CONVERT(campo, SIGNED) != 0,
campo,
IF (
campo REGEXP('[0-9]'),
CONVERT(SUBSTR(campo FROM 2) , SIGNED),
0
)
)
)
FROM tabla;

Básicamente lo que hago es primero comprobar si hay un numero en el principio, en caso que que lo detecte lo devuelve, si no lo detecta, o devuelve 0, comprobamos con REGEXP() a ver si hay un numero en la cadena, en el caso que lo encuentre hago un CONVERT() desde la posición 2 de la cadena por medio de un SUBSTR(), si no devuelvo 0.

El numero puede estar en cualquier parte de la cadena

Para esta situación ya tenemos que recurrir a crear una función, no es recomendable ponernos a anidar IF. Partiendo de la combinación de REGEXP(), CONVERT() y SUBSTR() he realizado la siguiente función la cual nos devolverá el primer numero que encuentre en la cadena, este donde este.

DROP FUNCTION IF EXISTS extractNumber;
DELIMITER //
CREATE FUNCTION extractNumber (cadena1 VARCHAR(255))
RETURNS INT(11)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE posicion, resultado, longitud INT(11) DEFAULT 0;
DECLARE cadena2 VARCHAR(255);
SET longitud = LENGTH(cadena1);
SET resultado = CONVERT(cadena1, SIGNED);
IF resultado = 0 THEN
IF cadena1 REGEXP('[0-9]') THEN
SET posicion = 2;
checkString:WHILE posicion <= longitud DO
SET cadena2 = SUBSTR(cadena1 FROM posicion);
IF CONVERT(cadena2, SIGNED) != 0 THEN
SET resultado = CONVERT(cadena2, SIGNED);
LEAVE checkString;
END IF;
SET posicion = posicion + 1;
END WHILE;
END IF;
END IF;
RETURN resultado;
END //
DELIMITER ;

view raw
extractNumber.sql
hosted with ❤ by GitHub

Limitaciones de la función extractNumber()

La mayoría de las pruebas las he realizado con números enteros, en plan “Un camión con 500 kilos de patatas” o en el plan “tortilla de 12 huevos”, en todos esos casos funciona perfectamente, al igual que si ponemos “1€” o “€1” o “Total Euros: 20”, pero si hay decimales solo devuelve la parte entera de estos.

Uso de expresiones regulares en MySQL – MySQL Tips

Una de las características poco conocidas en en el uso de sentencias en MySQL es el uso de expresiones regulares.

Imaginemos la siguiente situación:

Tenemos una tabla de paises y uno de los campos es nombre, y queremos crear una consulta que nos devuelva todos los paises que empiezan por A, C y R. Si lo realizamos sin expresiones regulares lo realizaríamos de la siguiente forma:


SELECT * FROM paises WHERE nombre LIKE 'A%' OR 'C%' OR 'R%'

Imaginaros esta consulta para mas iniciales, todo seria una secuencia de OR inicial. En cambio si usamos expresiones regulares quedaria de la siguiente manera


SELECT * FROM PAISES WHERE nombre RLIKE '^[A|C|R]'

Como podéis ver la consulta se reduce bastante, el RLIKE indica que se va a usar una expresión regular, el símbolo ^ indica que es el principio de cadena y entre [] ponemos las distintas variaciones separadas por |.

Si por ejemplo quisieramos todos los paises

http://dev.mysql.com/doc/refman/5.0/es/regexp.html

Formato Fecha en MySQL – MySQL Tips

Una de las “pegas” que tiene MySQL es que por defecto gestiona las fechas en modo ’00-00-0000′ es decir año-mes-dia. Y siempre nos toca crear una función para transformar estas fechas tanto al pasarlas a la base de datos, como para recuperarlas. A la hora de la recuperación algo que nos puede ahorrar trabajo es, a la hora de realizar la consulta, devolver la fecha formateada, para ello utilizaremos DATE_FORMAT.

Nuestra consulta podría quedar de la siguiente manera


SELECT id, name, DATE_FORMAT(date, '%d-%m-%Y) as date

Como primer parámetro de DATE_FORMAT especificamos el campo que contiene la fecha, y luego como segundo parámetro especificamos el formato en el cual queremos que nos devuelva la fecha en este caso sera ’00-00-0000′

 

Mas información en http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Enhanced by Zemanta

Caracteres de escape en MySQL – MySQL tips

Para los castellano parlantes quiza esta entrada no sea de mucha utilidad, pero si nos encontramos en el caso de tener que desarrollar una web por ejemplo en ingles quiza sirva de utilidad. El caso es el siguiente: i agi emos que tenemos que insertar textos en ingles dentro en nuestra base de datos, y nos encontramos que tenemos que insertar por ejemplo la palabra don’t . Como norma general para escribir una cadena de texto en una sentencia MySQL la encerramos entre comillas simples (‘), asi que si queremos utilizar una cadena de texto que contenga esa comilla, o cualquier otro caracter especial que queramos que sea interpretado como tal, tendremos que utilizar el caracter de escape \. Nuestra consulta quedara de la siguiente manera

Insert into table Set field ='don\'t'

Publicado desde WordPress

Selección de datos entre fechas – MySQL Tips

MySQLUna de las consultas mas frecuentes que solemos realizar es la de devolver datos entre fechas. La manera mas sencilla de realizar es utilizar el operador de comparación ‘BETWEEN’. Por ejemplo si queremos ver los datos entre dos fechas lo haremos de la siguiente manera. En este ejemplo la fecha mas baja es ‘fechaMin’ y la alta ‘fechaMax’, y hay que tener en cuenta que todos los argumentos tienen que ser del mismo tipo.


SELECT * FROM `miTabla` WHERE `miCampoFecha` BETWEEN 'fechaMin'  AND 'fechaMax';

Si el valor de `micampoFecha` es mayor o igual que ‘fechaMin’ y menor o igual que ‘fechaMax’  ‘BETWEEN’ devolvera 1 si no devolvera 0. Esto es lo mismo que hacer lo siguiente.


SELECT * FROM `miTabla` WHERE ('fechaMin' <= `miCampoFecha` AND `miCampoFecha` <= 'fechaMax')

Si lo que queremos es mostrar las fechas que no esten comprendidas dentro de este rango añadiremos ‘NOT’  delante de ‘BETWEEN’


SELECT * FROM `miTabla` WHERE `miCampoFecha` NOT BETWEEN 'fechaMin'  AND 'fechaMax';

Para mas información acerca de los operadores de comparación en MySQL hacer clic aquí

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