Tratamiento de mayúsculas y minúsculas en MySQL

Según Wikipedia, la sensibilidad a mayúsculas y minúsculas, se explica de la siguiente manera:

Que algo sea sensible a las mayúsculas y minúsculas, abreviado a veces como sensible a mayúsculas,​ y dicho también directamente en inglés case sensitive, es una expresión usada en informática que se aplica a los textos en los que tiene alguna relevancia escribir un carácter en mayúsculas o minúsculas.

Lo contrario se denomina insensible a las mayúsculas y minúsculas, abreviado a veces como insensible a mayúsculas​ y dicho también directamente en inglés case insensitive.

 

Por defecto, MySQL hace un tratamiento diferente para las mayúsculas en los nombres de las bases de datos y tablas según el sistema operativo como veremos a continuación.

Lo más sencillo y recomendable para facilitar el uso, la portabilidad y evitar problemas es adoptar una convención consistente: crear y consultar bases de datos y tablas usando nombres en minúsculas.

Muchas veces no se puede aplicar esta recomendación “de palabra”, por lo que se debe recurrir a una variable de configuración.

 

Variable lower_case_table_names

Esta variable se define dentro del archivo de configuración my.cnf (Linux) o my.ini (Windows). Determina cómo MySQL almacena en disco y consulta los nombres de bases de datos y tablas. El almacenamiento en disco se refiere al proceso de creación, con las instrucciones CREATE DATABASE y CREATE TABLE, respectivamente.

  • lower_case_table_names=0 (case sensitive en la creación y consulta)
    • los nombres se guardarán en disco tal y como se han especificado en la creación
    • los nombres en las consultas deben respetar las mayúsculas
    • NO de se debería establecer en sistemas operativos que sean case-insensitive! (Windows u OS X)
  • lower_case_table_names=1 (case insensitive en la creación y consulta)
    • los nombres se guardarán en disco siempre en minúsculas, independientemente de como se escriban
    • los nombres en las consultas son convertidos a minúsculas (también aplica a los aliases)
  • lower_case_table_names=2 (case sensitive en la creación – case insensitive en la consulta)
    • los nombres se guardarán en disco tal y como se han especificado en la creación
    • los nombres en las consultas son convertidos a minúsculas (también aplica a los aliases)
    • Solo funciona en sistemas operativos que no sean case-sensitive!
    • Los nombres de tablas InnoDB son una excepción y se almacenan en minúsculas, como en el caso de lower_case_table_names=1

A continuación, los valores por defecto para cada sistema operativo:

  • Linux: lower_case_table_names=0
  • Windows: lower_case_table_names=1
  • OS X: lower_case_table_names=2

 

¿Qué valor de lower_case_table_names usar?

En el caso de usar MySQL en un solo tipo de sistema operativo, se puede mantener el valor por defecto de lower_case_table_names.

Los conflictos aparecen al empezar a transferir tablas hacia otros sistemas que difieran en la sensibilidad a mayúsculas:

En Linux podrían existir las siguientes tablas: usuarios, USUARIOS y Usuarios.

Al importar un dump que las contuviera en Windows, la primera se crea sin problemas, pero la siguiente genera un error indicando que la tabla ya existe. Esto sucede porque para Windows las 3 tienen el mismo nombre.

A fin de evitar este tipo de problemas se puede optar por:

  1. Usar lower_case_table_names=1 en todos los sistemas (mi recomendación)
    • La “desventaja” es que no se verán listados con sus nombres originales, si los mismos estaban especificados con mayúsculas en la creación
  2. Usar lower_case_table_names=0 en Linux y lower_case_table_names=2 en Windows.
    • Esta alternativa es propensa a fallar cuando se hagan consultas que funcionan en Windows porque los nombres se pasan a minúsculas automáticamente, pero en Linux generan errores porque no se utilizaron las mayúsculas tal cual se especificaron en la creación.
    • Si se usan tablas InnoDB no queda más remedio que usar la opción 1.

 

Basado en:

http://rm-rf.es/mayusculas-y-minusculas-en-mysql/

Anuncios

Concatenar cadenas de caracteres en MySQL

La función más común para juntar un conjunto de cadenas de caracteres en MySQL es CONCAT(cadena1, cadena2, …)

Algunas consideraciones:

  • Si los argumentos que recibe son “no binarios”, la cadena resultante es no binaria
  • Si al menos uno de los argumentos es una cadena binaria, la salida es binaria.
  • Un número se convierte a su forma de texto no binaria
  • Si alguno de los argumentos es NULL, retorna NULL

mysql> SELECT CONCAT('Lalo', ' ' , 'Landa');
-> 'Lalo Landa'

mysql> SELECT CONCAT('Lalo', NULL , 'Landa');
-> NULL

El uso más común es para unir nombre de campos de las tablas incluidas en la consulta:

mysql> SELECT CONCAT(apellido, ', ' , nombre) FROM usuarios;
-> 'Gómez, Mario'

Cuando es necesario indicar un separador para unir más de 2 cadenas, lo recomendable es usar la función CONCAT_WS(separador, cadena1, cadena2, …)

Es una forma especial de CONCAT(). El WS al final proviene de  “With Separator”, lo que en conjunto sería “Conctatenar con Separador”. El primer argumento que recibe es el separador que se agregará entre cada una de las cadenas indicadas a continuación.

Algunas consideraciones:

  • Si el separador es NULL, el resultado es NULL
  • Si alguno de las cadenas es NULL, la misma se omite

mysql> SELECT CONCAT_WS(' ', 'Lalo' , 'Landa');
-> 'Lalo Landa'

mysql> SELECT CONCAT_WS(' ', 'Lalo', NULL , 'Landa');
-> 'Lalo Landa'

Concatenando múltiples campos:

mysql> SELECT CONCAT(' ', titulo, nombre, apellido) FROM usuarios;
-> 'Ing. Sergio Morales'

Tratar los registros duplicados en un INSERT de MySQL

Es un sentimiento desagradable el hacer una sentencia INSERT y recibir una respuesta del estilo:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

Los registros se consideran duplicados cuando se intenta insertar un registro que tiene un valor igual a uno existente en un campo que posee una PRIMARY KEY o un índice UNIQUE.

Existen 3 alternativas para controlar la forma en que se resolverá la inserción de registros ante la existencia de duplicados. La elección depende de las reglas de negocio principalmente.

INSERT IGNORE

Todos los errores que ocurran durante la inserción serán ignorados y se presentarán como warnings. Se puede decir que se “mantiene el registro existente”.

IGNORE tiene mayor precedencia que el modo estricto, por lo que si ambos aplican y ocurriera un error, se ignoraría.

Ejemplo

Si la tabla de usuarios tiene un índice UNIQUE por el campo login y ya existe el usuario con login jstock:

mysql> INSERT IGNORE INTO usuarios (login, nombre, apellido, estado) VALUES('jstock', 'Juan', 'Stock', 1);
-> Query OK, 0 rows affected (0.00 sec)

se ignora el duplicado, manteniendo los valores previos del registro.

INSERT ON DUPLICATE KEY UPDATE

Si una fila a insertar fuera a causar un error de duplicación, en su lugar se realiza un UPDATE del registro que existía previamente, modificando uno o más campos a la vez (separando con coma cada asignación) según se indique.

La cantidad de filas afectadas que devuelve Mysql es:

  • 1 si se inserta la nueva fila
  • 2 si la fila existente se modifica
  • 0 si la fila existente se modifica a los valores que ya poseía

Ejemplo

Suponiendo que la tabla hits posee la cantidad de veces que se accedió a una página y tiene un índice UNIQUE por el campo pagina, al agregar un registro más para index.html:

mysql> INSERT INTO paginas (pagina,hits) VALUES ('index.hml',1) ON DUPLICATE KEY UPDATE hits=hits+1;

como ya existe un registro previo identificado, lo que sucede es que se actualiza incrementando en uno la cantidad de hits.

Si existe un campo con AUTO_INCREMENT…

al hacer este tipo de INSERT si efectivamente se crea un registro, se genera un nuevo valor para el campo en cuestión. Este se puede obtener a partir de la función LAST_INSERT_ID().

Si el resultado de la sentencia es hacer una actualización del registro, entonces no se puede obtener el id correspondiente con LAST_INSERT_ID(). Para lograr que esta función tenga sentido en este caso, agregamos en el UPDATE para el campo con AUTO_INCREMENT (supongamos que sea id):

id=LAST_INSERT_ID(id)

Así se logra que la función devuelva el mismo valor que se le pasa como argumento y sea recordado y devuelto por la siguiente invocación de LAST_INSERT_ID().

Ejemplo

mysql> INSERT INTO paginas (pagina,hits) VALUES ('index.hml',1) ON DUPLICATE KEY UPDATE id_pagina=LAST_INSERT_ID(id_pagina), hits=hits+1;

REPLACE

Funciona como INSERT, pero si se detecta un duplicado, se borra el registro existente en la tabla y se realiza la nueva inserción. Se puede decir que se “establece el registro nuevo”, quitando el previo. Al realizar un borrado se desencadenan los triggers asociados al DELETE y los registros vinculados por FOREIGN KEYS podrían borrarse también!

Los pasos que sigue esta sentencia al ejecutarse son:

  1. Intenta insertar el registro
  2. Si falla por duplicate entry de una key
    1. Borra el registro conflictivo de la tabla
    2. Intentar insertar el registro nuevamente

entonces, en caso de fallo por duplicado realiza la secuencia INSERT – DELETE – INSERT.

La cantidad de filas afectadas que devuelve Mysql es:

  • 1 si se inserta la nueva fila (no existía duplicado)
  • 2 si se detecta un duplicado: un registro borrado y uno insertado en su lugar
  • N>2 si hay duplicados debido a múltiples índices UNIQUE por ejemplo

Ejemplo

Si la tabla de usuarios tiene un índice UNIQUE por el campo login y ya existe el usuario con login jstock:

mysql> REPLACE INTO usuarios (login, nombre, apellido, estado) VALUES('jstock', 'Juan', 'Stock', 1);
-> Query OK, 2 rows affected (0.00 sec)

se borra el registro previo y se inserta el nuevo, con los valores indicados.

Si existe un campo con AUTO_INCREMENT…

al hacer el REPLACE en caso de que se detecte un duplicado, se genera un nuevo valor para el campo en cuestión en el registro insertado. Este se puede obtener a partir de la función LAST_INSERT_ID().

Palabras reservadas en MySQL

Particulamente cuando deseamos utilizar ciertas palabras para nombrar tablas y campos, recibimos un mensaje de error del estilo

ERROR 1064 (42000): You have an error in your SQL syntax

esto se debe a que esas palabras son reservadas para Mysql por lo que no se pueden utilizar directamente y deben ser indicadas entre comillas del tipo backticks `.

Las palabras reservadas van cambiando con las versiones de Mysql por lo que deberán investigar cuales aplican a la versión que poseen.

Ejemplo:

mysql > CREATE TABLE schemas (id INT, name VARCHAR(50) );

producirá un error de sintaxis.

Para evitarlo debemos indicar el nombre de tabla entre `

mysql > CREATE TABLE `schemas` (id INT, name VARCHAR(50) );

 

Cuando se trata de campos también es viable utilizar nombres calificados, indicando:

nombre_tabla.nombre_campo

Tipos de join e información extra en EXPLAIN de MySQL

Cuando analizamos consultas a la base de datos con EXPLAIN, ciertas columnas que nos devuelve proveen mucha información, pero hay que saber leerlas. En este caso voy a hablar de los tipos de join entre las tablas y algunos de los extras.

Tipos de Join

Se muestran en la columna “Type”. El orden en que se detallan corresponde a la preferencia relativa entre los tipos de join, de mejor (system) a peor (ALL). Ej: es preferible que el tipo de join sea eq_ref en lugar de ref_or_null, que a su vez es mejor que range, el cual se prefiere antes que ALL.

  1. system: la tabla posee una sola fila
  2. const: la tabla matchea a lo sumo con 1 fila
  3. eq_ref: una fila se lee de esta tabla por cada combinación de filas de las tablas previas
  4. ref: toda las filas que matchean un valor del índice de esta tabla se leen de esta tabla por cada combinación de filas de las tablas previas
  5. fulltext: se usa un índice fulltext
  6. ref_or_null: similar a ref pero se buscan valores NULL también
  7. index_merge: es una optimización que utiliza varios índices y luego une los resultados
  8. unique_subquery: similar a ref en subqueries dentro de un IN cuando tiene índices unique
  9. index_subquery: similar a unique_subquery cuando tiene índices NO unique
  10. range: se obtienen las filas en un rango, usando un índice
  11. index: similar a ALL pero escanea el índice (puede estar con el Extra “using index”)
  12. ALL: consulta todos los registros de la tabla (full table scan) por cada combinación de filas de las tablas previas

Extras

Se muestran en la columna “Extra”. Pueden aparecer solos o combinados entre sí o con otros extras.

  • Using filesort: se hace una pasada adicional para determinar como obtener las filas ordenadas
    • es común cuando se utiliza un GROUP BY u ORDER BY, y no se puede ordenar con el índice
  • Using where: se usa el WHERE para restringir las filas que matchean contra la siguiente tabla
    • se podría decir que es casi necesario que aparezca para tipos de join: ALL e index
  • Using index: la información es obtenida solamente consultando el índice
    • con Using where: el índice se usa además para buscar valores de los campos en el índice
    • sin Using where: el índice se usa solo para evitar leer filas de la tabla
  • Using temporary: se crea una tabla temporal para generar el resultado
    • es común cuando se utiliza un GROUP BY

Basado en:

http://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Tablas fragmentadas MyISAM en MySQL

Las tablas fragmentadas, si bien no ocasionan problemas graves, pueden disminuir la velocidad de la lectura de los datos.

A continuación haremos una serie de pruebas para ver cómo responden las tablas MyISAM a la fragmentación.

En las consultas mostraremos solamente las columnas del estado de la tabla que necesitamos, sabiendo que no podemos especificar columnas en el comando SHOW TABLE STATUS.

Crear base de datos, tabla y datos de prueba

mysql> CREATE DATABASE frag_test;
USE frag_test;
CREATE TABLE frag_test (c1 VARCHAR(64)) ENGINE=MYISAM;
INSERT INTO frag_test VALUES ('fila 1');
INSERT INTO frag_test VALUES ('fila 2');
INSERT INTO frag_test VALUES ('fila 3');
INSERT INTO frag_test VALUES ('fila 4');

Verificar estado de la tabla

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          4          0

Presten atención a la columna Data_free que indica 0 bytes.

Borrar un registro y verificar estado de la tabla

mysql> DELETE FROM frag_test WHERE c1 = 'fila 2';

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          3         20

Ahora vemos que hay 20 bytes asignados pero sin uso en la tabla.

Realizar la inserción de un registro, verificar estado de la tabla y los datos

mysql> INSERT INTO frag_test VALUES ('fila 5');

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          4          0

Nuevamente volvemos a tener 0 bytes libres. Solo se utiliza el espacio fragmentado (hueco) si el formato de fila es fixed o el tamaño del registro a ingresar es menor o igual al hueco existente.

Al consultar el contenido de la tabla:

mysql> SELECT c1 FROM frag_test;

c1
------
fila 1
fila 5
fila 3
fila 4

la fila 5 se agregó entre la 1 y la 3, donde estaba antes la 2 (que fue borrada).

 

Ahora vamos a ver cómo podemos desfragmentar una tabla.

Borrar un registro y verificar estado de la tabla

mysql> DELETE FROM frag_test WHERE c1 = 'fila 3';

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          3         20

Tal como sucedió cuando borramos la fila 2, vemos que hay 20 bytes asignados pero sin uso en la tabla.

Optimizar la tabla y verificar el estado

Este proceso realiza un bloqueo (lock) de la tabla y puede demorar mucho tiempo si tenemos mucha fragmentación y datos!

mysql> OPTIMIZE TABLE frag_test;

Table                Op        Msg_type  Msg_text
-------------------  --------  --------  --------
frag_test.frag_test  optimize  status    OK

Si lo intentamos de nuevo (lo mismo que hacer este proceso sobre una tabla ya optimizada que no se haya modificado) obtendremos:

Table                Op        Msg_type  Msg_text
-------------------  --------  --------  ---------------------------
frag_test.frag_test  optimize  status    Table is already up to date

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          3          0

Como podemos ver, se ha quitado el espacio sin uso de la tabla.

Con una versión de Mysql posterior a 5.0.2 podemos obtener todas las tablas fragmentadas mediante la siguiente consulta

mysql> SELECT table_schema, table_name, data_free, engine
FROM information_schema.tables WHERE table_schema
NOT IN ('information_schema', 'mysql') AND data_free > 0;

Basado en:

http://www.databasejournal.com/features/mysql/article.php/3927871/MySQL-Data-Fragmentation—What-When-and-How.htm

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