Modo SQL del servidor

MySQL server puede operar en distintos modos SQL, y puede aplicar estos modos de forma distinta a diferentes clientes. Esto permite que cada aplicación ajuste el modo de operación del servidor a sus propios requerimientos.

Los modos definen qué sintaxis SQL debe soportar MySQL y que clase de chequeos de validación de datos debe realizar. Esto hace más fácil de usar MySQL en distintos entornos y usar MySQL junto con otros servidores de bases de datos.

Puede especificar el modo SQL por defecto arrancando mysqld con la opción --sql-mode="modes". El valor puede dejarse en blanco (--sql-mode="") si desea resetearlo.

En MySQL 5.0, también puede cambiar el modo SQL tras el tiempo de arranque cambiando la varialbe sql_modeusando el comando SET [SESSION|GLOBAL] sql_mode='modes' . Asignar la variable GLOBAL requiere el privilegio SUPER y afecta las operaciones de todos los clientes que conecten a partir de entonces. Asignar la variable SESSION afecta sólo al cliente actual. Cualquier cliente puede cambiar el valor de sql_mode en su sesión en cualquier momento.

modes es una lista de los diferentes modos separados por comas (',') . Puede consultar el modo actual mediante el comando SELECT @@sql_mode . El valor por defecto es vacío (sin modo seleccionado).

Los valores de los modos sql_mode más importantes probablemente son los siguientes:

•  ANSI
  Cambia el comportamiento y la sintaxis para cumplir mejor el SQL.
•  STRICT_TRANS_TABLES
  Si un valor no puede insertarse tal y como se da en una tabla transaccional, se aborta el comando. Para tablas no transaccionales, aborta el comando si el valor se encuentra en un comando que implique un sólo registro o el primer registro de un comando de varios registros. Más detalles a continuación en esta sección. (Implementado en MySQL 5.0.2)
•  TRADITIONAL
  Hace que MySQL se comporte como un sistema de bases de datos SQL ``tradicional''. Una simple descripción de este modo es `` da un error en lugar de una alerta'' cuando se inserta un valor incorrecto en la columna. Nota:INSERT/UPDATE aborta así que se detecta un error. Puede que no sea lo que quiera si está usando un motor de almacenamiento no transaccional, ya que los cambios en los datos anteriores al error no se deshacen, resultando en una actualización ``parcial'' . (Añadido en MySQL 5.0.2)

Cuando este manual se refiere al ``modo estricto,'' implica un modo donde al menos STRICT_TRANS_TABLES oSTRICT_ALL_TABLES está permitido.

La siguiente lista describe todos los modos soportados:

•  ALLOW_INVALID_DATES
  No hace un chequeo total de los datos en modo estricto. Chequea sólo que los meses se encuentran en el rango de 1 a 12 y que los días están en el rango de 1 a 31. Esto es muy conveniente para aplicaciones Web donde obtiene un año, mes y día en tres campos distintos y quiere guardar exactamente lo que inserta el usuario (sin validación de datos). Este modo se aplica a columnas DATE y DATETIME . No se aplica a columnas TIMESTAMP , que siempre requieren una fecha válida.
  Este modo se implementó en MySQL 5.0.2. Antes de 5.0.2, este era el modo por defecto de MySQL para tratar datos. Desde 5.0.2, el permitir el modo estricto provoca que el servidor requiera que el mes y día se evalúen como valores legales y no simplemente en los rangos de 1 a 12 y de 1 a 31, respectivamente. Por ejemplo,'2004-04-31' es legal con el modo estricto desactivado, pero ilegal con el modo estricto activado. Para permitir tales fechas en modo estricto, habilite ALLOW_INVALID_DATES también.
•  ANSI_QUOTES
  Trata '"' como un identificador delimitador de carácter (como '`' ) y no como un delimitador de cadenas de caracteres. Puede usar '`' para delimitar identificadores en modo ANSI. Con ANSI_QUOTES activado, puede usar doble delimitadores para delimitar una cadena de caracteres literales, ya que se interpreta como un identificador.
•  ERROR_FOR_DIVISION_BY_ZERO
  Produce un error en modo estricto (de otra forma una advertencia) cuando encuentra una división por cero (oMOD(X,0)) durante un INSERT o UPDATE, o en cualquier expresión (por ejemplo, en una lista de select o cláusulaWHERE ) que implica datos de tablas y una divisón por cero. Si este modo no se da, MySQL retorna NULL para una división por cero. Si se usa INSERT IGNORE o UPDATE IGNORE, MySQL genera una advertencia de división por cero, pero el resultado de la operación es NULL. (Implmentado en MySQL 5.0.2)
•  HIGH_NOT_PRECEDENCE
  Desde MySQL 5.0.2 , la precedencia del operador NOT se trata tal que expresiones como NOT a BETWEEN b AND c se parsean como NOT (a BETWEEN b AND c). Antes de MySQL 5.0.2, la expresión se parseaba como (NOT a) BETWEEN b AND c. El antiguo comportamiento de mayor-precedencia puede obtenerse permitiendo el modo SQL HIGH_NOT_PRECEDENCE . (Añadido en MySQL 5.0.2)

  mysql> SET sql_mode = '';
  mysql> SELECT NOT 1 BETWEEN -5 AND 5;
          -> 0
  mysql> SET sql_mode = 'broken_not';
  mysql> SELECT NOT 1 BETWEEN -5 AND 5;
          -> 1
  

•  IGNORE_SPACE
  Permite nombres entre el nombre de función y el carácter '(' . Esto fuerza que todos los nombres de función se traten como palabras reservadas. Como resultado, si quiere acceder a cualquier base de datos, tabla, o nombre de columna que sea una palabra reservada, debe delimitarla. Por ejemplo, y como hay una función USER() , el nombre de la tabla user en la base de datos mysql y la columna User en esa table se reseva, así que debe delimitarla:

  SELECT "User" FROM mysql."user";
  

•  NO_AUTO_CREATE_USER
  Previene que GRANT cree automáticamente nuevos usuarios si de otra forma se haría, a no ser que se especifique un usuario. (Añadido en MySQL 5.0.2)
•  NO_AUTO_VALUE_ON_ZERO
  NO_AUTO_VALUE_ON_ZERO afecta el tratamiento de las columnas AUTO_INCREMENT . Normalmente, genera el siguiente número de secuencia para la columna insertando NULL o 0 en ella. NO_AUTO_VALUE_ON_ZERO suprime este comportamiento para 0 de forma que sólo NULL genera el siguiente número de secuencia.
  Este modo puede ser útil si 0 se ha almacenado en una tabla con columnas AUTO_INCREMENT . (Esta no es una práctica recomendada, de todos modos.) Por ejemplo, si vuelca la tabla con mysqldump y posteriormente la recarga, normalmente MySQL genera un nuevo número de secuencia cuando encuentra los valores 0 , resultando en una tabla con distinto contenido que la que fue volcada. Activar NO_AUTO_VALUE_ON_ZERO antes de recargar el fichero con el volcado resuelve el problema. En MySQL 5.0, mysqldump incluye automáticamente en su salida un comando permitiendo NO_AUTO_VALUE_ON_ZERO.
•  NO_BACKSLASH_ESCAPES
  Desactiva el uso del carácter de barra invertida ('\') como carácter de escape en cadenas de caracteres. Con este modo activado, la barra invertida se convierte en un carácter ordinario como cualquier otro. (Implementado en MySQL 5.0.1)
•  NO_DIR_IN_CREATE
  Cuando crea una tabla, ignora todas las directivas INDEX DIRECTORY y DATA DIRECTORY. Este opción es útil en servidores de replicación esclavos.
• NO_ENGINE_SUBSTITUTION
  Evita la substitución automática de motor de almacenamiento cuando el motor deseado no está disponible o compilado.
•  NO_FIELD_OPTIONS
  No muestra opciones específicas para columnas de MySQL en la salida de SHOW CREATE TABLE. Este modo se usa con mysqldump en modo de portabilidad.
•  NO_KEY_OPTIONS
  No muestra opciones específicas para índices de MySQL en la salida de SHOW CREATE TABLE. Este modo se usa con mysqldump en modo de portabilidad.
•  NO_TABLE_OPTIONS
  No muestra opciones específicas para tablas (tales como ENGINE) en la salida de SHOW CREATE TABLE. Este modo se usa con mysqldump en modo de portabilidad.
•  NO_UNSIGNED_SUBTRACTION
  En operaciones de resta, no marca el resultado como UNSIGNED si uno de los operandos no tiene signo. Note que esto hace que UNSIGNED BIGINT no sea 100% usable en todos los contextos. Consulte Sección 12.8, “Funciones y operadores de cast”.
•  NO_ZERO_DATE
  En modo estricto, no permite '0000-00-00' como fecha válida. Puede insertar fechas 0 con la opción IGNORE . Cuando no está en modo estricto, la fecha se acepta pero se genera una advertencia. (Añadido en MySQL 5.0.2)
•  NO_ZERO_IN_DATE
  En modo estricto, no acepta fechas la parte del mes o día es 0. Se usa con la opción IGNORE , inserta una fecha'0000-00-00' para cualquiera de estas fechas. Cuando no está en modo estricto, la fecha se acepta pero se genera una advertencia. (Añadido en MySQL 5.0.2)
•  ONLY_FULL_GROUP_BY
  No permite consultas que en la parte del GROUP BY se refieran a una columna que no se seleccione.
•  PIPES_AS_CONCAT
  Trata || como un concatenador de columnas de caracteres (lo mismo que CONCAT()) en lugar de como sinónimo de OR.
•  REAL_AS_FLOAT
  Trata REAL como un sinónimo de FLOAT en lugar de sinónimo de DOUBLE.
•  STRICT_ALL_TABLES
  Activa el modo estricto para todos los motores de almacenamiento. Rechaza los datos inválidos. Detalles adicionales a continuación. (Añadido en MySQL 5.0.2)
•  STRICT_TRANS_TABLES
  Habilita el modo estricto para motores de almacenamiento transaccionales, y cuando sea posible también para los no transaccionales. Detalles adicionales a continuación. (Implementado en MySQL 5.0.2)

El modo estricto controla cómo MySQL trata los valores de entrada invalidos o no presentes. Un valor puede ser inválido por distintas razones. Por ejemplo, puede tener un tipo de datos incorrecto para la columna, o puede estar fuera de rango. Un valor no está presente cuando el registro a insertarse no tiene un valor para una columna que no tiene la cláusua DEFAULT explícita en su definición.

Para tablas transaccionales, se genera un error para valores inválidos o no presentes en un comando con los modos STRICT_ALL_TABLES o STRICT_TRANS_TABLES habilitados. El comando se aborta y deshace.

Para tablas no transaccionales, el comportamiento es el mismo para cualquier modo, si un valor incorrecto se encuentra en el primer registro a insertar o actualizar. El comando se aborta y la tabla continúa igual. Si el comando inserta o modifica varios registros y el valor incorrecto aparece en el segundo o posterior registro, el resultado depende de qué modo estricto esté habilitado:

• Para STRICT_ALL_TABLES, MySQL devuelve un error e ignora el resto de los registros. Sin embargo, en este caso, los primeros registros se insertan o actualizan. Esto significa que puede producirse una actualización parcial, que puede no ser lo que desea. Para evitarlo, es mejor usar comandos de un único registro ya que pueden abortarse sin cambiar la tabla.
• Para STRICT_TRANS_TABLES, MySQL convierte los valores inválidos en el valor válido más próximo para la columna e inserta el nuevo valor. Si un valor no está presente, MySQL inserta el valor por defecto implícito para el tipo de la columna. En ese caso, MySQL genera una advertencia en lugar de un error y continúa procesando el comando. Los valores implícitos se describen en Sección 13.1.5, “Sintaxis de CREATE TABLE”.

El modo estricto no permite fechas inválides como '2004-04-31'. Esto sigue permitiendo fechas con partes con ceros, como 2004-04-00' o fechas ``cero''. Para no permitirlas tampoco, active los modos SQLNO_ZERO_IN_DATE y NO_ZERO_DATE además del modo estricto.

Si no usa el modo estricto (esto es, ni STRICT_TRANS_TABLES ni STRICT_ALL_TABLES están activados), MySQL inserta valores ajustados para valores inválidos o no presentes y produce advertencias. En modo estricto, puede producir este comportamiento usando INSERT IGNORE o UPDATE IGNORE. Consulte Sección 13.5.4.22, “Sintaxis de SHOW WARNINGS”.

Los siguientes modos especiales se proporcionan como abreviaciones de combinaciones de modos de la lista precedente. Todos están disponibles en MySQL 5.0 empezando en la versión 5.0.0, excepto para TRADITIONAL, que se implementó en MySQL 5.0.2.

La descripción incluye todos los modos que están disponibles en la reción más reciente de MySQL. Para versiones anteriores, un modo de combinación no incluye todos los modos individuales que sólo están disponibles en las versiones más recientes.

•  ANSI
  Equivalente a REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. Antes de MySQL 5.0.3, ANSItambién incluye ONLY_FULL_GROUP_BY. Consulte Sección 1.7.3, “Ejecutar MySQL en modo ANSI”.
•  DB2
  Equivalente a PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.
•  MAXDB
  Equivalente a PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
•  MSSQL
  Equivalente a PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.
•  MYSQL323
  Equivalente a NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.
•  MYSQL40
  Equivalente a NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.
•  ORACLE
  Equivalente a PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
•  POSTGRESQL
  Equivalente a PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.
•  TRADITIONAL
  Equivalente a STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.

Fuente:enlace