1.7. Cumplimiento de los estándares por parte de MySQL

• Organización de los datos en disco

  MySQL Server mapea cada base de datos a un directorio bajo el directorio de datos de MySQL, y las tablas dentro de cada directorio como ficheros. Esto tiene algunas implicaciones:

  • Nombres de bases de datos y nombres de tablas son case sensitive en MySQL Server en sistemas operativos que tienen nombres de ficheros case-sensitive (como la mayoría de sistemas Unix). Consulte Sección 9.2.2, “Sensibilidad a mayúsuclas y minúsculas de identificadores”.

  • Puede usar comandos de sistema estándard para hacer copia de seguridad, renombrar, mover, borrar y copiar tablas de tipo MyISAM o ISAM. Por ejemplo, para renombrar el nombre de una tabla MyISAM renombre los archivos .MYD, .MYI, y .frm que correspondan a la tabla.

  Nombres de bases de datos, tablas, índices, columnas o alias pueden empezar con un dígito (pero no pueden consistir únicamente de dígitos).

• Sintaxis general del lenguaje

  • Cadenas de carácteres deben limitarse por '"' o ''', no sólo por '''.

  • Use '\' como un carácter de escape en cadenas de carácteres.

  • En comandos SQL, puede acceder a tablas de distintas bases de datos con la sintaxis db_name.tbl_name. Algunos servidores SQL proporcionan la misma fucnionalidad, pero lo llaman User space. MySQL Server no soporta espacios de tablas como los usados en comandos como: CREATE TABLE ralph.my_table...IN my_tablespace.

• Sintaxis de comandos SQL

  • Los comandos ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, y REPAIR TABLE.

  • Los comandos CREATE DATABASE y DROP DATABASE. Consulte Sección 13.1.3, “Sintaxis de CREATE DATABASE”.

  • El comando DO.

  • EXPLAIN SELECT para obtener una descripción de cómo las tablas se usan.

  • Los comandos FLUSH y RESET.

  • El comando SET. Consulte Sección 13.5.3, “Sintaxis de SET”.

  • El comando SHOW. Consulte Sección 13.5.4, “Sintaxis de SHOW”.

  • Uso de LOAD DATA INFILE. En muchos casos, esta sintaxis es compatible con el comando de Oracle LOAD DATA INFILE. Consulte Sección 13.2.5, “Sintaxis de LOAD DATA INFILE”.

  • Uso de RENAME TABLE. Consulte Sección 13.1.9, “Sintaxis de RENAME TABLE”.

  • Uso de REPLACE en lugar de DELETE + INSERT. Consulte Sección 13.2.6, “Sintaxis de REPLACE”.

  • Uso de CHANGE col_name, DROP col_name, o DROP INDEX, IGNORE o RENAME en un comando ALTER TABLE. Uso de múltiples ADD, ALTER, DROP, o CHANGE cláusulas en un comando ALTER TABLE. Consulte Sección 13.1.2, “Sintaxis de ALTER TABLE”.

  • Uso de nombres de índices, índices sobre el prefijo de un cambio, y uso de INDEX o KEY en un comando CREATE TABLE. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”.

  • Uso de TEMPORARY o IF NOT EXISTS con CREATE TABLE.

  • Uso de IF EXISTS con DROP TABLE.

  • Puede borrar varias tablas con un único comando DROP TABLE.

  • Las cláusulas ORDER BY y LIMIT de los comandos UPDATE y DELETE.

  • Sintaxis de INSERT INTO ... SET col_name = ....

  • La cláusula DELAYED de los comandos INSERT y REPLACE.

  • La cláusula LOW_PRIORITY de los comandos INSERT, REPLACE, DELETE, y UPDATE.

  • Uso de INTO OUTFILE y STRAIGHT_JOIN en un comando SELECT . Consulte Sección 13.2.7, “Sintaxis de SELECT”.

  • La opción SQL_SMALL_RESULT en un comando SELECT.

  • No necesita nombrar todas las columnas seleccionadas en la parte GROUP BY. Esto proporciona mejor rendimiento en algunas consultas muy específicas pero bastante normales. Consulte Sección 12.10, “Funciones y modificadores para cláusulas GROUP BY”.

  • Puede especificar ASC y DESC con GROUP BY.

  • La habilidad para inicializar variables en un comando con el operador :=:

    mysql> SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg
        -> FROM test_table;
    mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
    

• Tipos de columnas

  • Los tipos de columnas MEDIUMINT, SET, ENUM, y los distintos tipos BLOB y TEXT.

  • Los atributos de columnas AUTO_INCREMENT, BINARY, NULL, UNSIGNED, y ZEROFILL.

• Funciones y operadores

  • Para facilitar a los usuarios que vienen de otros entornos SQL, MySQL Server soporta alias para varias funciones. Por ejemplo, todas las funciones de cadenas de carácteres soportan sintaxis estándard SQL y ODBC.

  • MySQL Server entiende los operadores || y && para OR lógica y AND, como en el lenguaje de programación C. En MySQL Server, || y OR son sinónimos, como lo son && y AND. Debido a esta sintaxis, MySQL Server no soporta el operador estándard SQL || para concatenar cadenas de carácteres; use en su lugar CONCAT(). Como CONCAT() toma cualquier número de argumentos, es fácil adaptarse al uso del operador || a MySQL Server.

  • Uso de COUNT(DISTINCT list) donde list tiene más de un elemento.

  • Todas las comparaciones de cadenas de carácteres son case-insensitive por defecto, con la ordenación determinada por el conjunto de carácteres actual (ISO-8859-1 Latin1 por defecto). Si no quiere que sea así, puede declarar las columnas con el atributo BINARY o usar la conversión BINARY, que hace que las comparaciones se hagan usando el código de carácteres subyacente en lugar del orden léxico.

  • El operador % es sinónimo de MOD(). Esto es que N % M es equivalente a MOD(N,M). % se soporta para programadores C y por compatibilidad con PostgreSQL.

  • Los operadores =, <>, <= ,<, >=,>, <<, >>, <=>, AND, OR, o LIKE se pueden usar en comparaciones de columnas a la izquierda del FROM en comandos SELECT. Por ejemplo:

    mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
    

  • La función LAST_INSERT_ID() retorna el valor AUTO_INCREMENT más reciente. Consulte Sección 12.9.3, “Funciones de información”.

  • LIKE se permite en columnas numéricas.

  • Los operadores de expresiones regulares extendidos REGEXP y NOT REGEXP.

  • CONCAT() o CHAR() con un argumento o más de dos argumentos. (En MySQL Server, estas funciones pueden tomar cualquier número de argumentos.)

  • Las funciones BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), MD5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), y WEEKDAY().

  • Uso de TRIM() para eliminar espacios en substrings. Funciones estándard sólo SQL soportan eliminar carácteres simples.

  • Las funciones GROUP BY STD(), BIT_OR(), BIT_AND(), BIT_XOR(), y GROUP_CONCAT(). Consulte Sección 12.10, “Funciones y modificadores para cláusulas GROUP BY”.

• Para columnas VARCHAR, los espacios finales se eliminan cuando el valor se guarda. (Arreglado en MySQL 5.0.3). Consulte Sección A.8, “Problemas conocidos en MySQL”.

• En algunos casos, las columnas de tipo CHAR se convierten en columnas VARCHAR cuando define una tabla o altera su estructura. (Arreglado en MySQL 5.0.3). Consulte Sección 13.1.5.1, “Cambios tácitos en la especificación de columnas”.

• Los privilegios para una tabla no se eliminan automáticamente cuando se borra una tabla. Debe usar explícitamente un comando REVOKE para quitar los privilegios de una tabla. Consulte Sección 13.5.1.3, “Sintaxis de GRANT y REVOKE”.

• La función CAST() no soporta conversión a REAL o BIGINT. Consulte Sección 12.8, “Funciones y operadores de cast”.

• SQL estándard necesita que las cláusulas HAVING en un comando SELECT puedan referirse a columnas en la cláusula GROUP BY. Esto no se permite antes de la versión MySQL 5.0.2.

MySQL 4.1 soporta sub-consultas y tablas derivadas. Una "sub-consulta" es un comando SELECT anidado en otro comando. Una tabla "derivada" (una vista sin nombre) es una subconsulta en la cláusula FROM de otra consulta. Consulte Sección 13.2.8, “Sintaxis de subconsultas”.

Para versiones MySQL anteriores a la 4.1, la mayoría de subconsultas pueden reescribirse usando joins u otros métodos. Consulte Sección 13.2.8.11, “Re-escribir subconsultas como joins en versiones de MySQL anteriores” para ejemplos que muestren cómo hacerlo.

MySQL Server no soporta la sintaxis de extensiones Sybase SQL: SELECT ... INTO TABLE .... En su lugar, MySQL Server soporta la sintaxis estándard SQL INSERT INTO ... SELECT ..., que básicamente es lo mismo. Consulte Sección 13.2.4.1, “Sintaxis de INSERT ... SELECT”.

INSERT INTO tbl_temp2 (fld_id)
    SELECT tbl_temp1.fld_order_id
    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Alternativamente, puede usar SELECT INTO OUTFILE ... o CREATE TABLE ... SELECT.

Para al versión 5.0, MySQL soporta SELECT ... INTO con variables de usuario. La misma sintaxis puede usarse dentro de procedimientos almacenados usando cursores y variables locales. Consulte Sección 19.2.9.3, “La sentencia SELECT ... INTO”.

MySQL Server (versiones 3.23-max y todas las versiones 4.0 y posteriores) soportan transacciones con los motores trasaccionales InnoDB y BDB . InnoDB proporciona completa compatibilidad ACID. Consulte Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

Los otros motores no transaccionales en MySQL Server (como MyISAM) siguen un paradigma diferente para integridad de datos llamado "operaciones atómicas". En términos transaccionales, tablas MyISAM operan en modo AUTOCOMMIT=1. Operaciones atómicas a menudo ofrecen integridad comparable con mejor rendimiento.

MySQL Server soporta ambos paradigmas, puede decidir si su aplicación necesita la velocidad de operaciones atómicas o el uso de características transaccionales. Esta elección puede hacerse para cada tabla.

Como se ha dicho, el compromiso entre tipos de tablas transaccionales y no transaccionales reside principalmente en el rendimiento. Tablas transaccionales tienen requerimientos significativamente mayores para memoria y espacio de disco, y mayor carga de CPU. Por otra parte, tipos de tablas transaccionales como InnoDB también ofrece muchas características significativas. El diseño modular de MySQL Server permite el uso concurrente de distintos motores de almacenamiento para cumplir distintos requerimientos y mostrarse óptimo en todas las situaciones.

Pero, ¿cómo usar las características de MySQL Server para mantener integridad de forma rigurosa incluso en tablas no transaccionales como MyISAM , y cómo se comparan estas características con los tipos de tablas transaccionales?

El paradigma transaccional tiene sus ventajas y desventajas. Muchos usuarios y desarrolladores de aplicaciones dependen en la facilidad con la que pueden solucionar problemas donde un aborto parece ser o es necesario. Sin embargo, incluso si el paradigma de operaciones atómicas le es desconocido o está más familiarizado con las transacciones, considere el beneficio de la velocidad que pueden ofrecer las tablas no transaccionales, que puede ser de tres a cinco veces más rápido que las más optimizadas tablas transaccionales.

En las situaciones en las que la integridad es de máxima importancia, MySQL Server ofrece integridad a nivel de transacción incluso para tablas no transaccionales. Si bloquea tablas con LOCK TABLES, todas las actualizaciones se bloquean hasta que se hacen las comprobaciones necesarias. Si obtiene un bloqueo READ LOCAL (el contrario a un bloqueo de escritura) para una tabla que permita inserciones concurrentes al final de la tabla, las lecturas están permitidas, así como las inserciones de otros clientes. Los registros insertados no puede verlos el cliente que tenga el bloqueo hasta que lo libere. Con INSERT DELAYED, puede encolar inserciones en una cola local, hasta que los bloqueos se liberan, sin tener que esperar el cliente a que acabe la inserción. Consulte Sección 13.2.4.2, “Sintaxis de INSERT DELAYED”.

"Atómico", en el sentido en que nos referimos, no es nada mágico. Se trata que puede asegurar que mientras cada actualización específica está ejecutándose, ningún otro usuario puede interferir con ellas, y que nunca puede haber un rollback automático (lo que puede ocurrir con tablas transaccionales si no se es muy cuidadoso). MySQL Server garantiza que no hay dirty reads (lecturas sucias).

A continación se presentan algunas técnicas para trabajar con tablas no transaccionales:

Los procedimientos almacenados se implementan desde la versión 5.0. Consulte Capítulo 19, Procedimientos almacenados y funciones.

Funcionalidad básica para disparadores se implementa en MySQL desde la versión 5.0.2, con desarrollo adicional planeado para MySQL 5.1. Consulte Capítulo 20, Disparadores (triggers).

En MySQL Server 3.23.44 y posteriores, el motor InnoDB soporta chequeo para restricciones de claves foráneas, incluyendo CASCADE, ON DELETE, y ON UPDATE. Consulte Sección 15.6.4, “Restricciones (constraints) FOREIGN KEY”.

Para otros motores diferentes a InnoDB, MySQL Server parsea la sintaxis de FOREIGN KEY en comandos CREATE TABLE, pero no lo usa ni almacena. En el futuro, la implemantación se extenderá para almacenar esta información en el fichero de especificaciones de las tablas de forma que puedan obtenerla mysqldump y ODBC. En una etapa posterior, restricciones de claves foráneas se implementarán para tablas MyISAM.

Restricciones de claves foráneas ofrecen distintos beneficios a los diseñadores de bases de datos:

Tenga en cuenta que estos beneficios tienen el coste de un trabajo adicional para el servidor de base de datos para poder realizar todas las comprobaciones necesarias. Chequeos adicionales por parte del servidor afectan al rendimiento, lo que puede ser lo suficientemente malo para algunas aplicaciones como para evitarlo todo lo posible. (Algunas grandes aplicaciones comerciales han codificado la lógica de claves foráneas en el nivel de aplicación por esta razón.)

MySQL proporciona a diseñadores de bases de datos la posibilidad de elegir qué paradigma elegir. Si no necesita claves foráneas y quiere evitar la sobrecarga asociada con la integridad referencial, puede usar otro tipo de tabla como MyISAM. (Por ejemplo, el motor MyISAM ofrece muy buen rendimiento para aplicaciones que sólo realizan operaciones INSERT y SELECT, ya que las inserciones de pueden utilizar de forma concurrente con consultas. Consulte Sección 7.3.2, “Cuestiones relacionadas con el bloqueo (locking) de tablas”.)

Si elige no utilizar integridad referencial, tenga en cuenta las siguientes consideraciones:

Tenga en cuenta que el uso de claves foráneas puede provocar algunos problemas:

Tenga en cuenta que las claves foráneas en SQL se usan para chequear y forzar integridad referencial, no para unir tablas. Si quiere obtener resultados de múltiples tablas a partir de un comando SELECT, debe usar un join entre ellas:

SELECT * FROM t1, t2 WHERE t1.id = t2.id;

Consulte Sección 13.2.7.1, “Sintaxis de JOIN”. Consulte Sección 3.6.6, “Usar claves foráneas (foreign keys)”.

La sintaxis de FOREIGN KEY sin ON DELETE ... se usa a menudo por aplicaciones ODBC para producir cláusulas WHERE automáticamente.

Vistas (incluyendo vistas actualizables) se implementan en la versión 5.0 de MySQL Server. Las vistas están disponibles en las versiones binarias a partir de la 5.0.1. Consulte Capítulo 21, Vistas (Views).

Las vistas son útiles para permitir acceder a los usuarios a un conjunto de relaciones (tablas) como si fueran una sola, y limitar su acceso a las mismas. También se pueden usar las vistas para restringir el acceso a registros (un subconjunto de una tabla particular). Para control de acceso a columnas, puede usar el sofisticado sistema de privilegios de MySQL Server. Consulte Sección 5.6, “El sistema de privilegios de acceso de MySQL”.

Al diseñar la implementación de las vistas, nuestro ambicioso objetivo, dentro de los límites de SQL, ha sido la plena compatibilidad con la regla 6 de Codd para sistemas relacionales de bases de datos: "Todas las vistas que son actualizables en teoría, deben serlo en la práctica".

Algunas otras bases de datos SQL utilizan '--' para comenzar comentarios. MySQL Server utiliza '#' como carácter para comenzar comentarios. Puede utilizar comentarios estilo C /*this is a comment */ con MySQL Server. Consulte Sección 9.5, “Sintaxis de comentarios”.

MySQL Server 3.23.3 y posteriores permiten comentarios del estilo '--', mientras el comentario esté seguido de un carácter (o por un carácter de controlo como una nueva línea). Se necesita dicho espacio para evitar problemas con consultas SQL generadas automáticamente que usen algo parecido al código a continuación, donde se inserta automáticamente el valor de pago para !payment!:

UPDATE account SET credit=credit-!payment!

Piense acerca de lo que ocurre si el valor de payment es un valor negativo como -1:

UPDATE account SET credit=credit--1

credit--1 es una expresión legal en SQL, pero si -- se interpreta como parte de un comentario, una parte de la expresión se descarta. El resultado es un comando que tiene un significado completamente distinto al deseado:

UPDATE account SET credit=credit

Este comando no produce ningún cambio en absoluto! Lo cual ilustra que permitir comentaros que empiecen con '--' puede tener serias consecuencias.

Usando la implementación de este método de comentarios en MySQL Server desde 3.23.3, credit--1 es seguro.

Otra medida de seguridad es que el cliente de líneas de comando mysql elimina todas las líneas que empiecen con '--'.

La siguiente información sólo es relevante si utiliza versiones anteriores a la 3.23.3:

Si tiene un programa SQL en un fichero de texto que contenga comentarios del estilo '--', debería utilizar la utilidad replace para convertir los comentarios antiguos en carácteres '#' de la siguiente forma:

shell> replace " --" " #" < text-file-with-funny-comments.sql \
         | mysql db_name

en lugar del usual:

shell> mysql db_name < text-file-with-funny-comments.sql

También puede editar el fichero "a mano" para cambiar los comentarios '--' a '#':

shell> replace " --" " #" -- text-file-with-funny-comments.sql

Vuelva a cambiarlos con el comando:

shell> replace " #" " --" -- text-file-with-funny-comments.sql

Normalmente, un error ocurre cuando trata de ejecutar un INSERT o UPDATE en un registro que viole la clave primaria, clave única o clave foránea. Si usa un motor transaccional como InnoDB, MySQL automáticamente deshace el comando. Si usa un motor no transaccional, MySQL para de procesar el comando en el registro en el que ocurre el error y deja sin procesar el resto de registros.

Si desea ignorar este tipo de violaciones de claves, MySQL permite la palabra clave IGNORE para INSERT y UPDATE. En este caso, MySQL ignora cualquier violación de clave y continúa procesando el siguiente registro. Consulte Sección 13.2.4, “Sintaxis de INSERT”. See Sección 13.2.10, “Sintaxis de UPDATE”.

Puede obtener información acerca del número de registro insertados o actualizados realmente con la función de la API de C mysql_info(). Consulte Sección 24.3.3.32, “mysql_info()”. A partir de MySQL 4.1 puede usar el comando SHOW WARNINGS. Consulte Sección 13.5.4.22, “Sintaxis de SHOW WARNINGS”.

De momento, sólo las tablas InnoDB soportan claves foráneas. Consulte Sección 15.6.4, “Restricciones (constraints) FOREIGN KEY”. El soporte para claves foráneas para tablas MyISAM está previsto para implementarse en MySQL 5.1.

Antes de la versión 5.0.2 de MySQL, se permitía insertar valores ilegales convirtiéndolos en valores legales. A partir de la versión 5.0.2, sigue este compartimiento por defecto, pero puede elegir un tratamiento para valores incorrectos más tradicional, como no aceptarlos y abortar los comandos que los incluyen. Esta sección describe el comportamiento por defecto de MySQL (permisivo), así como el nuevo modo estricto SQL y en qué se diferencian.

Lo siguiente es cierto si no usa modo estricto. Si inserta un valor "incorrecto" en una columna, como NULL en una columna NOT NULL o una valor numérico demasiado grande en una columna numérica, MySQL cambia el valor al "mejor valor posible" para la columna en lugar de producir un error:

La razón para las reglas anteriores es que no podemos validar esas condiciones hasta que los comandos han empezado a ejecutarse. No podemos deshacer si encontramos un problema tras actualizar algunos registros, ya que el motor de almacenamiento puede no soportar rollback. La opción de terminar el comando no es siempre positiva; en este caso, la actualización quedaría "a medias", lo que posiblemente es la peor opción. En este caso, lo mejor es hacerlo "lo mejor posible" y continuar como si nada hubiera ocurrido.

A partir de MySQL 5.0.2, puede seleccionar un tratamiento de validación de datos de entrada más estricto usando los modos SQL STRICT_TRANS_TABLES o STRICT_ALL_TABLES. Consulte Sección 5.3.2, “El modo SQL del servidor”.

STRICT_TRANS_TABLES funciona así:

Para chequeo estricto, active STRICT_ALL_TABLES. Es equivalente a STRICT_TRANS_TABLES excepto que en motores de almacenamiento no transaccionales, los errores abortan el comando incluso cuando hay datos incorrectos a partir del primer registro. Esto significa que si ocurre un error a medias de una inserción o actualización de varios registros en una tabla no transaccional, se produce una actualización parcial. Los primeros registros se insertan o actualizan, pero aquéllos a partir del punto en que ocurre el error no. Para evitar esto en tablas no transaccionales, use inserciones de un solo registro o use STRICT_TRANS_TABLES para obtener advertencias en lugar de errores. Para evitar problemas, no utilice MySQL para validar contenido de columnas. Es preferible (y a menudo más rápido) dejar que la aplicación se asegure de pasar sólo valores legales a la base de datos.

Con cualquiera de las opciones de modo estricto, puede hacer que se traten los errores como advertencias usando INSERT IGNORE o UPDATE IGNORE en lugar de INSERT o UPDATE sin IGNORE.

Las columnas ENUM y SET proporcionan una manera eficiente de definir columnas que contienen un conjunto dado de valores. Sin embargo, antes de MySQL 5.0.2, ENUM y SET no son restricciones reales. Esto es por la misma razón que NOT NULL tampoco lo es. Consulte Sección 1.7.6.2, “Restricciones (constraints) sobre datos inválidos”.

Las columnas de tipo ENUM siempre tienen un valor por defecto. Si no especifica un valor por defecto, entonces será NULL para las columnas que permitan valores NULL, si no, se utiliza el primer valor de la enumeración como valor por defecto.

Si inserta un valor incorrecto en una columna ENUM o si fuerza insertar un valor en una columna ENUM con IGNORE, se inicializa al valor reservado para enumeraciones 0, el cual se muestra como una cadena vacía en un contexto de cadenas de carácteres. Consulte Sección 11.4.4, “El tipo de columna ENUM”.

Si insertar un valor incorrecto en una columna SET, se ignora el valor incorrecto. Por ejemplo, si la columna puede contener los valores 'a', 'b', y 'c', un intento de insertar 'a,x,b,y' resulta en un valor de 'a,b'. Consulte Sección 11.4.5, “El tipo SET”.

En MySQL 5.0.2, puede configurar el servidor para que use el modo estricto de SQL. Consulte Sección 5.3.2, “El modo SQL del servidor”. Cuando el modo estricto está activado, la definición de columnas ENUM o SET no actúa como una restricción en valores insertados en la columna. Los valores que no satisfacen estas condiciones provocan un error:

Se pueden suprimir los errores derivados de valores inválidos en modo estricto usando INSERT IGNORE o UPDATE IGNORE. En ese caso, se genera una advertencia en lugar de un error. Para tipos ENUM, el valor se inserta como un miembro erróneo (0). Para tipo SET, el valor se inserta igual excepto que se borra cualquier subcadena inválida. Por ejemplo, 'a,x,b,y' se convertiría en 'a,b', como se ha descrito.