Especificar columnas en el comando SHOW TABLE STATUS

El comando SHOW TABLE STATUS muestra para cada tabla de la base de datos en uso los siguientes datos:

Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment

Podemos limitar mediante:

  • FROM <BASE_DE_DATOS>: la base de datos sobre la cual obtener la información
  • LIKE '<PATRON>‘: aquellas tablas que coincidan con el patrón (utilizando caracteres y comodines)

El problema es que, en realidad, no hay forma de poder especificar el subconjunto de campos que realmente necesitamos.

A partir de Mysql 5.0.2 tenemos las posibilidad de hacer algo similar a partir de la base de datos information_schema mediante la siguiente consulta:

mysql> SELECT Table_name, Engine, Version, Row_format, Table_rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Table_collation, Checksum, Create_options, Table_comment
FROM information_schema.tables
WHERE table_schema = DATABASE();

Obs: La función DATABASE() retorna la base de datos actual (en uso). Si no hay ninguna base de datos seleccionada, devuelve NULL.

Más aún, esta forma es mucho más potente dado que permite aplicar y combinar ciertas restricciones mediante:

  • table_schema = <BASE_DE_DATOS>: la base de datos sobre la cual obtener la información
    • se pueden indicar varias uniendo esta sintaxis mediante un OR
    • es más podemos usar table_schema LIKE '<PATRON>' para considerar las bases que coincidan con el patrón (utilizando caracteres y comodines)
  • table_name = <TABLA>: la tabla sobre la cual obtener la información
    • se pueden indicar varias uniendo esta sintaxis mediante un OR
    • es más podemos usar table_name LIKE '<PATRON>' para considerar las tablas que coincidan con el patrón (utilizando caracteres y comodines)

De esta manera podemos especificar cada uno de los campos deseados (o incluso aplicar funciones sobre ellos), las bases de datos y las tablas a consultar.

Ejemplo:

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql' AND table_name LIKE 'help%';

Name           Engine  Row_format    Rows  Data_free
-------------  ------  ----------  ------  ---------
help_category  MyISAM  Dynamic         40          0
help_keyword   MyISAM  Fixed          485          0
help_relation  MyISAM  Fixed         1090          0
help_topic     MyISAM  Dynamic        534          0
Anuncios

Usar GROUP_CONCAT ordenando los registros

La función GROUP_CONCAT es muy útil para juntar los valores de otros campos u expresiones habiendo realizado previamente un agrupamiento por ciertos campos. Entonces vamos a presentar un ejemplo que iremos refinando basado en ciertas necesidades:

Suponiendo que tenemos una tabla que contiene las  etiquetas de los usuario para que marquen sus mails, y deseamos obtener el listado de los nombres de etiquetas que cada uno posee:

SELECT usuario, GROUP_CONCAT( etiqueta ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Obs: recuerden que implícitamente se está realizando un orden por GROUP BY.

Ahora necesitamos que si algún usuario hubiera definido más de una etiqueta con el mismo nombre, aparezca en el listado solo una vez:

SELECT usuario, GROUP_CONCAT( DISTINCT etiqueta ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Para ser más prolijos vamos a ordenar los nombres de etiquetas alfabéticamente:

SELECT usuario, GROUP_CONCAT( DISTINCT etiqueta ORDER BY etiqueta ASC ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Obs: podemos omitir ASC, dado que es el sentido de ordenamiento por defecto. Podemos utilizar en su lugar DESC, y hasta ordenar por otras columnas o expresiones!

Por último, vamos a separar con un guíon medio las distintas etiquetas en el listado  (dado que por defecto se utiliza una coma):

SELECT usuario, GROUP_CONCAT( DISTINCT etiqueta ORDER BY etiqueta ASC SEPARATOR ' - ' ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Para tener en cuenta:

  • los valores NULL, no se concatenan
  • se puede omitir el separador indicando SEPARATOR ''
  • el resultado de la concatenación se trunca al valor máximo indicado por la variable group_concat_max (pueden consultar el valor de esta variable en su Mysql)

Consideraciones para limitar los resultados de una consulta en MySQL

Les comento brevemente algunas consideraciones para el uso de LIMIT en un SELECT en Mysql. El formato es el siguiente:

... LIMIT <NRO-FILAS>|<POSICION>,<CANTIDAD>

Si se utiliza un único parámetro NRO-FILAS, entonces se obtiene esa cantidad de registros como máximo en el resultado.

Si se utiliza la forma con los 2 parámetros, POSICION se refiere a un offset en los resultados obtenidos y CANTIDAD al número de registros a considerar desde POSICION. Muy importante: la primer POSICION es 0!

Por compatibilidad con PostgreSQL, existe también la forma alternativa:

... LIMIT <CANTIDAD> OFFSET <POSICION>

Por lo general, cuando se limite la cantidad de resultados, los mismos se habrán solicitado en un orden, por lo que la cláusula ORDER BY se indica siempre antes que LIMIT.

Ejemplo:

En todas las sentencias indicadas a contiuación, se obtienen las 5 notas más recientes:

SELECT id, texto, fecha FROM notas ORDER BY fecha DESC LIMIT 5;

SELECT id, texto, fecha FROM notas ORDER BY fecha DESC LIMIT 0,5; #Empieza en 0!

SELECT id, texto, fecha FROM notas ORDER BY fecha DESC LIMIT 4 OFFSET 0; #Compatibilidad con PostgreSQL

Orden por defecto de un select sobre una tabla en MySQL

En la mayoría de los casos, al hacer una consulta sobre una tabla sin especificar la cláusula ORDER BY ni GROUP BY, el orden por defecto aparenta estar dado por el almacenamiento interno, por lo general:

  • en MyISAM: el orden en que fueron insertados en la tabla (si no hubo cambios en los registros)
  • en InnoDB: el orden de la clave primaria de la tabla

Más allá de eso, no se puede asegurar que esto se cumpla en todos los casos ya que los engines pueden variar estas definiciones aún entre versiones.

La recomendación es: si se necesitan obtener los registros ordenados entonces utilizar ORDER BY en la consulta.

ADICIONAL:

Si se utiliza GROUP BY implícitamente se ordena por estos campos. Si este ordenamiento no fuera necesario, se puede omitir agregando a la consulta ORDER BY NULL.

Obtener la fecha y hora actuales en MySQL

Es importante saber que la fecha actual se puede obtener de 2 formas diferentes en Mysql:

  • NOW(): Devuelve  el instante de tiempo en que la consulta comenzó a ejecutarse en formato ‘YYYY-MM-DD HH:MM:SS’ expresado en la zona horaria establecida.
  • SYSDATE(): Devuelve  el instante de tiempo en que esta función se ejecuta en formato ‘YYYY-MM-DD HH:MM:SS’ expresado en la zona horaria establecida.

mysql> SELECT NOW(), SLEEP(1), NOW(); #Ambos resultados son iguales

+---------------------+----------+---------------------+
| NOW()               | SLEEP(1) | NOW()               |
+---------------------+----------+---------------------+
| 2014-07-16 23:46:55 |        0 | 2014-07-16 23:46:55 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(1), SYSDATE(); #Los resultados difieren en 1 segundo

+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(1) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2014-07-16 23:47:38 |        0 | 2014-07-16 23:47:39 |
+---------------------+----------+---------------------+

Consultas de textos case sensitive en MySQL

Mysql es por defecto case insensitive (no sensible a las mayúsculas): no diferencia las mayúsculas de las minúsculas. Esto se debe a que tiene configurado inicialmente el collation ‘latin1_swedish_ci’.

mysql> SELECT nombre_usuario FROM usuarios WHERE nombre_usuario LIKE 'al%'; # retorna usuarios con nombres como 'Alberto', 'Álvaro', 'alejo'

Por lo general, cuando hacemos una comparación es muy conveniente este comportamiento dado que no importa la capitalización que usemos, encontraremos los resultados.

 

Tengamos en cuenta que para los campos con caracteres no binarios: CHAR, VARCHAR y TEXT, la comparación depende del collation de los mismos.

En cambio, para los campos binarios: BINARY, VARBINARY y BLOB se utiliza el valor numérico de los bytes en la comparación, por lo que serán case sensitive en el caso de los caracteres.

Si se compara una cadena binaria y una no-binaria, la comparación se realiza como si fueran ambas binarias.

 

Pero en ciertos casos, cuando deseamos comparar los caracteres contemplando su capitalización, podemos optar por alguna de las siguientes alternativas:

  • Usar el operador COLLATE para indicar un collation que sea case sensitive (termina con _cs o _bin)  que pertenezca al juego de caracteres correspondiente a un campo, un valor o a ciertas partes de la consulta.
    • Disponible a partir de Mysql 4.1
  • Usar el operador BINARY sobre el valor o columna en la consulta
  • Cambiar  el collation de la columna CHAR(N), VARCHAR(N) o TEXT mediante un ALTER TABLE por uno que sea case sensitive:
    • agregando BINARY al final, lo cual especifica el collation _bin para el juego de caracteres correspondiente
    • indicando el CHARSET y COLLATION específicos
  • Cambiar el tipo de dato de la columna mediante un ALTER TABLE por uno que sea case sensitive:
    • BINARY, VARBINARY o BLOB

Ejemplos de uso de COLLATE

mysql> SELECT nombre_usuario FROM usuarios WHERE nombre_usuario LIKE 'Al%' COLLATE latin1_spanish_cs; # retorna usuarios con nombres como 'Alberto' o 'Álvaro'. NO 'alejo'

mysql> SELECT nombre_usuario FROM usuarios WHERE nombre_usuario COLLATE latin1_bin LIKE 'al%'; # retorna usuarios con nombres como 'alejo'. NO 'Alberto' ni 'Álvaro'

 

Sabiendo también cómo cambiar el tipo de dato a un valor o a un campo, podemos notar que todas las expresiones son equivalentes cuando se utilizan en una comparación:

CAST(nombre AS BINARY) = CONVERT(nombre,BINARY) = BINARY nombre = nombre COLLATE latin1_bin

COLLATE no cambia el tipo de dato, solo aplica un collation al momento de usar el campo en una comparación

Generar valor aleatorio en MySQL

Para obtener un número aleatorio entre MIN y MAX en un select de Mysql debemos utilizar la siguiente fórmula:

FLOOR(MIN + RAND() * (MAX – MIN))

Por ejemplo, para obtener un número entre 10 y 50 se debería hacer:

mysql> SELECT FLOOR(10+ RAND() * 40) AS random;

 

Basado en:

http://anothermysqldba.blogspot.com.ar/2014/06/mysql-random-integers.html