Hosting guebs

Capítulo 12. Funciones y operadores

Tabla de contenidos

12.1. Operadores
12.1.1. Precedencias de los operadores
12.1.2. Paréntesis
12.1.3. Funciones y operadores de comparación
12.1.4. Operadores lógicos
12.2. Funciones de control de flujo
12.3. Funciones para cadenas de caracteres
12.3.1. Funciones de comparación de cadenas de caracteres
12.4. Funciones numéricas
12.4.1. Operadores aritméticos
12.4.2. Funciones matemáticas
12.5. Funciones de fecha y hora
12.6. Qué calendario utiliza MySQL
12.7. Funciones de búsqueda de texto completo (Full-Text)
12.7.1. Búsquedas booleanas de texto completo (Full-Text)
12.7.2. Búsquedas de texto completo (Full-Text) con expansión de consulta
12.7.3. Limitaciones de las búsquedas de texto completo (Full-Text)
12.7.4. Afinar búsquedas de texto completo (Full-Text) con MySQL
12.7.5. Cosas por hacer en búsquedas de texto completo (Full-Text)
12.8. Funciones y operadores de cast
12.9. Otras funciones
12.9.1. Funciones bit
12.9.2. Funciones de encriptación
12.9.3. Funciones de información
12.9.4. Funciones varias
12.10. Funciones y modificadores para cláusulas GROUP BY
12.10.1. Funciones (de agregación) de GROUP BY
12.10.2. Modificadores de GROUP BY
12.10.3. GROUP BY con campos escondidos

Las expresiones pueden usarse en varios puntos de los comandos SQL, tales como en las cláusulas ORDER BY o HAVING de los comandos SELECT , en la cláusula WHERE de los comandos SELECT, DELETE, o UPDATE o en comandos, SET . Las expresiones pueden escribirse usando valores literales, valores de columnas, NULL, funciones y operadores. Este capítulo describe las funciones y operadores permitidos para escribir expresiones en MySQL.

Una expresión que contiene NULL siempre produce un valor NULL a no ser que se indique de otro modo en la documentación para una función u operador particular.

Nota: Por defecto, no deben haber espacios en blanco entre un nombre de función y los paréntesis que lo siguen. Esto ayuda al parser de MySQL a distinguir entre llamadas a funciones y referencias a tablas o columnas que tengan el mismo nombre que una función. Sin embargo, se permiten espacios antre los argumentos de las funciones.

Puede decirle a MySQL server que acepte espacios tras los nombres de funciones arrancando con la opción --sql-mode=IGNORE_SPACE. Los programas cliente pueden pedir este comportamiento usando la opción CLIENT_IGNORE_SPACE para mysql_real_connect(). En cualquier caso, todos los nombres de función son palabras reservadas. Consulte Sección 5.3.2, “El modo SQL del servidor”.

Para una mayor brevedad, la mayoría de ejemplos de este capítulo muestran la salida del programa mysql de forma abreviada. En lugar de mostrar ejemplos en este formato:

mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+
1 rows in set (0.00 sec)

Se usa este otro:

mysql> SELECT MOD(29,9);
        -> 2

12.1. Operadores

12.1.1. Precedencias de los operadores

La precedencia de operadores se muestra en la siguiente lista, de menor a mayor precedencia. Los operadores que se muestran juntos en una línea tienen la misma precedencia.

:=
||, OR, XOR
&&, AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (resta unaria), ~ (inversión de bit unaria)
!
BINARY, COLLATE

La precedencia mostrada para NOT es desde MySQL 5.0.2. En versiones anteriores, o desde 5.0.2 si el modo HIGH_NOT_PRECEDENCE está activo, la precedencia de NOT es la misma que la del operador ! . Consulte Sección 5.3.2, “El modo SQL del servidor”.

12.1.2. Paréntesis

  • ( ... )

    Use paréntesis para forzar el orden de evaluación en una expresión. Por ejemplo:

    mysql> SELECT 1+2*3;
            -> 7
    mysql> SELECT (1+2)*3;
            -> 9
    

12.1.3. Funciones y operadores de comparación

Las operaciones de comparación dan un valor de 1 (CIERTO), 0 (FALSO), o NULL. Estas operaciones funcionan tanto para números como para cadenas de caracteres. Las cadenas de caracteres se convierten automáticamente en números y los números en cadenas cuando es necesario.

Algunas de las funciones de esta sección (tales como LEAST() y GREATEST()) retornan valores distintos a 1 (CIERTO), 0 (FALSO), o NULL. Sin embargo, el valor que retornan se basa en operaciones de comparación realizadas como describen las siguientes reglas.

MySQL compara valores usando las siguientes reglas:

  • Si uno o ambos argumentos son NULL, el resultado de la comparación es NULL, excepto para el operador de comparación NULL-safe <=> .

  • Si ambos argumentos en una operación de comparación son cadenas, se comparan como cadenas.

  • Si ambos argumentos son enteros, se comparan como enteros.

  • Los valores hexadecimales se tratan como cadenas binarias si no se comparan con un número.

  • Si uno de los argumentos es una columna TIMESTAMP o DATETIME y el otro argumento es una constante, la constante se convierte en timestamp antes de realizar la comparación. Esto se hace para acercarse al comportamiento de ODBC. Esto no se hace para argumentos en IN()! Para estar seguro, siempre use cadenas completas de fechas/horas al hacer comparaciones.

  • En todos los otros casos, los argumentos se comparan como números con punto flotante (reales).

Por defecto, la comparación de cadenas no es sensible a mayúsculas y usa el conjunto de caracteres actual (ISO-8859-1 Latin1 por defecto, que siempre funciona bien para inglés).

Par convertir un valor a un tipo específico para una comparación, puede usar la función CAST() . Los valores de cadenas de caracteres pueden convertirse a un conjunto de caracteres distinto usando CONVERT(). Consulte Sección 12.8, “Funciones y operadores de cast”.

Los siguientes ejemplos ilustran conversión de cadenas a números para operaciones de comparación:

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

Tenga en cuanta que cuando compara una columna de cadenas de caracteres con un número, MySQL no puede usar el índice de la columna para buscar rápidamente le valor. Si str_col es una columna de cadenas indexada, el índice no puede usarse al realizar la búsqueda en el siguiente comando:

SELECT * FROM tbl_name WHERE str_col=1;

La razón es que hay diferentes cadenas que pueden convertirse al valor 1: '1', ' 1', '1a', ...

  • =

    Igual:

    mysql> SELECT 1 = 0;
            -> 0
    mysql> SELECT '0' = 0;
            -> 1
    mysql> SELECT '0.0' = 0;
            -> 1
    mysql> SELECT '0.01' = 0;
            -> 0
    mysql> SELECT '.01' = 0.01;
            -> 1
    

  • <=>

    NULL-safe equal. Este operador realiza una comparación de igualdad como el operador =, pero retorna 1 en lugar de NULL si ambos operandos son NULL, y 0 en lugar de NULL si un operando es NULL.

    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
            -> 1, 1, 0
    mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
            -> 1, NULL, NULL
    

  • <>, !=

    Diferente:

    mysql> SELECT '.01' <> '0.01';
            -> 1
    mysql> SELECT .01 <> '0.01';
            -> 0
    mysql> SELECT 'zapp' <> 'zappp';
            -> 1
    

  • <=

    Menor que o igual:

    mysql> SELECT 0.1 <= 2;
            -> 1
    

  • <

    Menor que:

    mysql> SELECT 2 < 2;
            -> 0
    

  • >=

    Mayor que o igual:

    mysql> SELECT 2 >= 2;
            -> 1
    

  • >

    Mayor que:

    mysql> SELECT 2 > 2;
            -> 0
    

  • IS valor booleano, IS NOT valor booleano

    Comprueba si un valor contra un valor booleano, donde boolean_value puede ser TRUE, FALSE, o UNKNOWN.

    mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
            -> 1, 1, 1
    mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
            -> 1, 1, 0
    

    IS [NOT] valor booleano sintaxis se añadió en MySQL 5.0.2.

  • IS NULL, IS NOT NULL

    Testea si un valor es o no NULL.

    mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
            -> 0, 0, 1
    mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
            -> 1, 1, 0
    

    Para poder trabajar con programas ODBC, MySQL soporta las siguientes características extra al usar IS NULL:

    • Puede encontrar el registro que contiene el valor AUTO_INCREMENT más reciente realizando un comando de la siguiente forma inmediatamente tras generar el valor:

      SELECT * FROM tbl_name WHERE auto_col IS NULL
      

      Este comportamiento puede desactivarse asignando SQL_AUTO_IS_NULL=0. Consulte Sección 13.5.3, “Sintaxis de SET.

    • Para columnas DATE y DATETIME que se declaran como NOT NULL, puede encontrar la fecha especial '0000-00-00' con un comando como este:

      SELECT * FROM tbl_name WHERE date_column IS NULL
      

      Esto es necesario para algunas aplicaciones ODBC, ya que ODBC no soporta un valor de fecha '0000-00-00' .

  • expr BETWEEN min AND max

    Si expr es mayor o igual que min y expr es menor o igual a max, BETWEEN retorna 1, de otro modo retorna 0. Esto es equivalente a la expresión (min <= expr AND expr <= max) si todos los argumentos son del mismo tipo. De otro modo la conversión de tipos tiene lugar según las reglas descritas al principio de la sección, pero aplicadas a todos los argumentos.

    mysql> SELECT 1 BETWEEN 2 AND 3;
            -> 0
    mysql> SELECT 'b' BETWEEN 'a' AND 'c';
            -> 1
    mysql> SELECT 2 BETWEEN 2 AND '3';
            -> 1
    mysql> SELECT 2 BETWEEN 2 AND 'x-3';
            -> 0
    

  • expr NOT BETWEEN min AND max

    Esto es lo mismo que NOT (expr BETWEEN min AND max).

  • COALESCE(value,...)

    Retorna el primer valore no NULL de la lista.

    mysql> SELECT COALESCE(NULL,1);
            -> 1
    mysql> SELECT COALESCE(NULL,NULL,NULL);
            -> NULL
    

  • GREATEST(value1,value2,...)

    Con dos o más argumentos, retorna el argumento mayor (con valor mayor). Los argumentos se comparan usando las mismas reglas que para LEAST().

    mysql> SELECT GREATEST(2,0);
            -> 2
    mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
            -> 767.0
    mysql> SELECT GREATEST('B','A','C');
            -> 'C'
    

  • expr IN (value,...)

    Retorna 1 si expr es uno de los valores en la lista IN , de lo contrario retorna 0. Si todos los valores son constantes, se evalúan según el tipo y ordenación de expr . La búsqueda para el elemento se hace usando búsqueda binaria. Esto significa que IN es muy rápido si la lista IN consiste enteramente en constantes. Si expr es una expresión de cadenas de caracteres sensible a mayúsculas, la comparación de cadenas se realiza sensible a mayúsculas.

    mysql> SELECT 2 IN (0,3,5,'wefwf');
            -> 0
    mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
            -> 1
    

    El número de valores en la lista IN sólo está limitado por el valor max_allowed_packet.

    En MySQL 5.0, para cumplir el estándar SQL IN retorna NULL no sólo si la expresión de la parte izquierda es NULL, también si no encuentra coincidencias en la lista y una de las expresiones en la lista es NULL.

    La sintaxis de IN() puede usarse para escribir algunas subconsultas. Consulte Sección 13.2.8.3, “Subconsultas con ANY, IN y SOME.

  • expr NOT IN (value,...)

    Esto es lo mismo que NOT (expr IN (value,...)).

  • ISNULL(expr)

    Si expr es NULL, ISNULL() retorna 1, sino retorna 0.

    mysql> SELECT ISNULL(1+1);
            -> 0
    mysql> SELECT ISNULL(1/0);
            -> 1
    

    Una comparación de valores NULL usando = siempre es falsa.

    La función ISNULL() comparte algunos comportamientos especiales con el operador de comparación IS NULL, consulte la descripción de IS NULL en Sección 12.1.3, “Funciones y operadores de comparación”.

  • INTERVAL(N,N1,N2,N3,...)

    Retorna 0 if N < N1, 1 si N < N2 y así o -1 si N es NULL. Todos los argumentos se tratan como enteros. Esto requiere que N1 < N2 < N3 < ... < Nn para que la función funcione correctamente. Esto es porque se usa una búsqueda binaria (muy rápida).

    mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
            -> 3
    mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
            -> 2
    mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
            -> 0
    

  • LEAST(value1,value2,...)

    Con dos o más argumentos, retorna el argumento menor (con un valor menor). Los argumentos se comparan usando las siguientes reglas:

    • Si el valor retornado se usan en un contexto INTEGER o todos los argumentos son enteros, se comparan como enteros.

    • Si el valor retornado se usa en un contexto REAL o todos los argumentos son reales, se comparan como reales.

    • Si algún argumento es una cadena de caracteres sensible a mayúsculas, los argumentos se comparan como cadenas sensibles a mayúsculas.

    • En cualquier otro caso, los argumentos se comparan como cadenas no sensibles a mayúsculas.

    mysql> SELECT LEAST(2,0);
            -> 0
    mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
            -> 3.0
    mysql> SELECT LEAST('B','A','C');
            -> 'A'
    

    Tenga en cuenta que las reglas de conversión precedentes pueden producir resultados extraños en algunos casos extremos:

    mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
        -> -9223372036854775808
    

    Esto ocurre porque MySQL lee 9223372036854775808.0 en un contexto entero. La representación entera no es lo bastante buena para tratar el valor, así que lo cambia a entero con signo.

12.1.4. Operadores lógicos

En SQL, todos los operadores lógicos se evalúan a TRUE, FALSE, o NULL (UNKNOWN). En MySQL, se implementan como 1 (TRUE), 0 (FALSE), y NULL. La mayoría de esto es común en diferentes servidores de bases de datos SQL aunque algunos servidores pueden retornar cualquier valor distinto a cero para TRUE.

  • NOT, !

    NOT lógica. Se evalúa a 1 si el operando es 0, a 0 si el operando es diferente a cero, y NOT NULL retorna NULL.

    mysql> SELECT NOT 10;
            -> 0
    mysql> SELECT NOT 0;
            -> 1
    mysql> SELECT NOT NULL;
            -> NULL
    mysql> SELECT ! (1+1);
            -> 0
    mysql> SELECT ! 1+1;
            -> 1
    

    El último ejemplo produce 1 porque la expresión se evalúa igual que (!1)+1.

  • AND, &&

    AND lógica. Se evalúa a 1 si todos los operandos son distintos a cero y no NULL, a 0 si uno o más operandos son 0, de otro modo retorna NULL.

    mysql> SELECT 1 && 1;
            -> 1
    mysql> SELECT 1 && 0;
            -> 0
    mysql> SELECT 1 && NULL;
            -> NULL
    mysql> SELECT 0 && NULL;
            -> 0
    mysql> SELECT NULL && 0;
            -> 0
    

  • OR, ||

    OR lógica. Cuando ambos ooperandos son no NULL, el resultado es 1 si algún operando es diferente a cero, y 0 de otro modo. Con un operando NULL el resultado es 1 si el otro operando no es cero, y NULL de otro modo. Si ambos operandos son NULL, el resultado es NULL.

    mysql> SELECT 1 || 1;
            -> 1
    mysql> SELECT 1 || 0;
            -> 1
    mysql> SELECT 0 || 0;
            -> 0
    mysql> SELECT 0 || NULL;
            -> NULL
    mysql> SELECT 1 || NULL;
            -> 1
    

  • XOR

    XOR lógica. Retorna NULL si algún operando es NULL. Para operandos no NULL , evalúa a 1 si un número par de operandos es distinto a cero, sino retorna 0.

    mysql> SELECT 1 XOR 1;
            -> 0
    mysql> SELECT 1 XOR 0;
            -> 1
    mysql> SELECT 1 XOR NULL;
            -> NULL
    mysql> SELECT 1 XOR 1 XOR 1;
            -> 1
    

    a XOR b es matemáticamente igual a (a AND (NOT b)) OR ((NOT a) and b).

12.2. Funciones de control de flujo

  • CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END, CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

    La primera versión retorna result donde value=compare-value. La segunda versión retorna el resultado para la primera condición que es cierta. Si no hay ningún resultado coincidente, el resultado tras ELSE se retorna, o NULL si no hay parte ELSE.

    mysql> SELECT CASE 1 WHEN 1 THEN 'one'
        ->     WHEN 2 THEN 'two' ELSE 'more' END;
            -> 'one'
    mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
            -> 'true'
    mysql> SELECT CASE BINARY 'B'
        ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
            -> NULL
    

    El tipo de retorno por defecto de una expresión CASE es el tipo agregado compatible de todos los valores de retorno, pero también depende del contexto en el que se usa. Si se usa en un entorno de cadenas de caracteres, el resultado se retorna como cadena de caracteres. Si se usa en un contexto numérico, el resultado se retorna como valor decimal, real o entero.

  • IF(expr1,expr2,expr3)

    Si expr1 es TRUE (expr1 <> 0 and expr1 <> NULL) entonces IF() retorna expr2; de otro modo retorna expr3. IF() retorna un valor numérico o cadena de caracteres, en función del contexto en que se usa.

    mysql> SELECT IF(1>2,2,3);
            -> 3
    mysql> SELECT IF(1<2,'yes','no');
            -> 'yes'
    mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
            -> 'no'
    

    Si sólo una de expr2 o expr3 es explícitamente NULL, el tipo del resultado de la función IF() es el mismo tipo que la expresión no NULL .

    expr1 se evalúa como un valor entero, que significa que si esta testeando valores de punto flotante o cadenas de caracteres, debe hacerlo mediante operaciones de comparación.

    mysql> SELECT IF(0.1,1,0);
            -> 0
    mysql> SELECT IF(0.1<>0,1,0);
            -> 1
    

    En el primer caso mostrado, IF(0.1) retorna 0 ya que 0.1 se convierte a un valor entero, resultando en un test de IF(0). Puede que esto no ea lo que espera. En el segundo caso, la comparación testea el valor de coma flotante para comprobar que no es cero. El resultado de la comparación se usa como entero.

    El tipo de retorno de IF() (que puede ocurrir cuando se almacena en una tabla temporal) se calcula como sigue:

    ExpresiónValor Retornado
    expr2 o expr3 retorna una cadenacadena de caracteres
    expr2 o expr3 retorna un valor de coma flotantecoma flotante
    expr2 o expr3 retorna un enteroentero

    Si expr2 y expr3 son cadenas de caracteres, el resultado es sensible a mayúsculas si alguna de las cadenas lo es.

  • IFNULL(expr1,expr2)

    Si expr1 no es NULL, IFNULL() retorna expr1, de otro modo retorna expr2. IFNULL() retorna un valor numérico o de cadena de caracteres, en función del contexto en que se usa.

    mysql> SELECT IFNULL(1,0);
            -> 1
    mysql> SELECT IFNULL(NULL,10);
            -> 10
    mysql> SELECT IFNULL(1/0,10);
            -> 10
    mysql> SELECT IFNULL(1/0,'yes');
            -> 'yes'
    

    El valor por defecto de retorno de IFNULL(expr1,expr2) es el más “general” de las dos expresiones, en el orden STRING, REAL, o INTEGER. Considere el caso de una tabla basada en expresiones o donde MySQL debe almacenar internamente un valor retornado por IFNULL() en una tabla temporal:

    CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
    

    En este ejemplo, el tipo de la columnatest es CHAR(4).

  • NULLIF(expr1,expr2)

    Retorna NULL si expr1 = expr2 es cierto, de otro modo retorna expr1. Es lo mismo que CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

    mysql> SELECT NULLIF(1,1);
            -> NULL
    mysql> SELECT NULLIF(1,2);
            -> 1
    

    Tenga en cuenta que MySQL evalúa expr1 dos veces si los argumentos no son iguales.

12.3. Funciones para cadenas de caracteres

Las funciones de cadenas de caracteres retornan NULL si la longitud del resultado es mayor que el valor de la variable de sistema max_allowed_packet . Consulte Sección 7.5.2, “Afinar parámetros del servidor”.

Para funciones que operan en posiciones de cadenas de caracteres, la primera posición es la 1.

  • ASCII(str)

    Retorna el valor numérico del carácter más a la izquierda de la cadena de caracteres str. Retorna 0 si str es la cadena vacía. Retorna NULL si str es NULL. ASCII() funciona para caracteres con valores numéricos de 0 a 255.

    mysql> SELECT ASCII('2');
            -> 50
    mysql> SELECT ASCII(2);
            -> 50
    mysql> SELECT ASCII('dx');
            -> 100
    

    Consulte la función ORD() .

  • BIN(N)

    Retorna una representación de cadena de caracteres del valor binario de N, donde N es un número muy largo (BIGINT) . Esto es equivalente a CONV(N,10,2). Retorna NULL si N es NULL.

    mysql> SELECT BIN(12);
            -> '1100'
    

  • BIT_LENGTH(str)

    Retorna la longitud de la cadena de caracteres str en bits.

    mysql> SELECT BIT_LENGTH('text');
            -> 32
    

  • CHAR(N,...)

    CHAR() interpreta los argumentos como enteros y retorna la cadena de caracteres que consiste en los caracteres dados por los códigos de tales enteros. Los valores NULL no se tienen en cuenta.

    mysql> SELECT CHAR(77,121,83,81,'76');
            -> 'MySQL'
    mysql> SELECT CHAR(77,77.3,'77.3');
            -> 'MMM'
    

  • CHAR_LENGTH(str)

    Retorna la longitud de la cadena de caracteres str, medida en caracteres. Un carácter de múltiples bytes cuenta como un sólo carácter. Esto significa que para una cadena de caracteres que contiene cinco caracteres de dos bytes, LENGTH() retorna 10, mientras CHAR_LENGTH() returna 5.

  • CHARACTER_LENGTH(str)

    CHARACTER_LENGTH() es sinónimo de CHAR_LENGTH().

  • COMPRESS(string_to_compress)

    Comprime una cadena de caracteres. Esta función necesita que MySQL se compile con una biblioteca de compresión como zlib. De otro modo, el valor retornado siempre es NULL. La cadena comprimida puede descomprimirse con UNCOMPRESS().

    mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
            -> 21
    mysql> SELECT LENGTH(COMPRESS(''));
            -> 0
    mysql> SELECT LENGTH(COMPRESS('a'));
            -> 13
    mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
            -> 15
    

    Los contenidos comprimidos se almacenan de la siguiente forma:

    • Cadenas vacías se almacenan como cadenas vacías.

    • Cadenas no vacías se almacenan como longitud de cuatros bytes de la cadena descomprimida (los bytes más bajos primero), seguido de la cadena comprimida. Si la cadena acaba con un espacio, se añade un carácter '.' para evitar problemas con eliminación de espacios finales al almacenar en una columna CHAR o VARCHAR . (El uso de CHAR o VARCHAR para almacenar cadenas comprimidas no se recomienda. Es mejor usar una columna BLOB .)

  • CONCAT(str1,str2,...)

    Retorna la cadena resultado de concatenar los argumentos. Retorna NULL si algúna argumento es NULL. Puede tener uno o más argumentos. Si todos los argumentos son cadenas no binarias, el resultado es una cadena no binaria. Si los argumentos incluyen cualquier cadena binaria, el resultado es una cadena binaria. Un argumento numérico se convierte a su forma de cadena binaria equivalente; si quiere evitarlo puede usar conversión de tipos explícita, como en este ejemplo: SELECT CONCAT(CAST(int_col AS CHAR), char_col)

    mysql> SELECT CONCAT('My', 'S', 'QL');
            -> 'MySQL'
    mysql> SELECT CONCAT('My', NULL, 'QL');
            -> NULL
    mysql> SELECT CONCAT(14.3);
            -> '14.3'
    

  • CONCAT_WS(separator,str1,str2,...)

    CONCAT_WS() significa CONCAT With Separator (CONCAT con separador) y es una forma especial de CONCAT(). El primer argumento es el separador para el resto de argumentos. El separador se añade entre las cadenas a concatenar. El separador puede ser una cadena como el resto de argumentos. Si el separador es NULL, el resultado es NULL. La función evita valores NULL tras el argumento separador.

    mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
            -> 'First name,Second name,Last Name'
    mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
            -> 'First name,Last Name'
    

    En MySQL 5.0, CONCAT_WS() no evita cadenas vacías. (Sin embargo, evita NULLs.)

  • CONV(N,from_base,to_base)

    Convierte números entre diferentes bases numéricas. Retorna una representación de cadena de carácters para el número N, convertido de base from_base a base to_base. Retorna NULL si algún argumento es NULL. El argumento N se interpreta como entero, pero puede especificarse como un entero o cadena. La base mínima es 2 y la máxima es 36. Su to_base es un número negativo, N se trata como un número con signo. De otro modo, N se trata como sin signo. CONV() funciona con precisión de 64-bit.

    mysql> SELECT CONV('a',16,2);
            -> '1010'
    mysql> SELECT CONV('6E',18,8);
            -> '172'
    mysql> SELECT CONV(-17,10,-18);
            -> '-H'
    mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);
            -> '40'
    

  • ELT(N,str1,str2,str3,...)

    Retorna str1 si N = 1, str2 if N = 2, y así. Retorna NULL si N es menor que 1 o mayor que el número de argumentos. ELT() es el complemento de FIELD().

    mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
            -> 'ej'
    mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
            -> 'foo'
    

  • EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

    Retorna una cadena en que para cada bit del valor bits, puede obtener una cadena on y para cada bit reasignado obtiene una cadena off . Los bits en bits se examinan de derecha a izquierda (de bits menores a mayores). Las cadenas se añaden al resultado de izquierda a derecha, separados por la cadena separator (siendo el carácter por defecto la coma ','). El número de bits examinados se obtiene por number_of_bits (por defecto 64).

    mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
            -> 'Y,N,Y,N'
    mysql> SELECT EXPORT_SET(6,'1','0',',',10);
            -> '0,1,1,0,0,0,0,0,0,0'
    

  • FIELD(str,str1,str2,str3,...)

    Retorna el índice de str en la lista str1, str2, str3, ... . Retorna 0 si no se encuentra str.

    Si todos los argumentos de FIELD() son cadenas, todos los argumentos se comparan como cadenas. Si todos los argumentos son números, se comparan como números. De otro modo, los argumentos se comparan como números con doble precisión.

    Si str es NULL, el valor retornado es 0 porque NULL falla en comparaciones de comparación con cualquier valor. FIELD() es el complemento de ELT().

    mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
            -> 2
    mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
            -> 0
    

  • FIND_IN_SET(str,strlist)

    Retorna un valor en el rango de 1 a N si la cadena str está en la lista de cadenas strlist consistente de N subcadenas. Una lista de cadenas es una cadena compuesta de subcadenas separadas por caracteres ',' . Si el primer argumento es una cadena constante y el segundo es una columna de tipo SET, la función FIND_IN_SET() está optimizada para usar aritmética de bit. Retorna 0 si str no está en strlist o si strlist es la cadena vacía. Retorna NULL si algún argumento es NULL. Esta función no funciona apropiadamente si el primer argumento contiene un carácter de coma (',') .

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
            -> 2
    

  • HEX(N_or_S)

    Si N_OR_S es un número, retorna una cadena representación del valor hexadecimal de N, donde N es un número muy grande (BIGINT) . Esto es equivalente a CONV(N,10,16).

    Si N_OR_S es una cadena, esta función retorna una cadena hexadecimal de N_OR_S caracteres, donde cada carácter en N_OR_S se convierte a dos dígitos hexadecimales.

    mysql> SELECT HEX(255);
            -> 'FF'
    mysql> SELECT 0x616263;
            -> 'abc'
    mysql> SELECT HEX('abc');
            -> 616263
    

  • INSERT(str,pos,len,newstr)

    Retorna la cadena str, con la subcadena comenzando en la posición pos y len caracteres reemplazados por la cadena newstr. Retorna la cadena original si pos no está entre la longitud de la cadena. Replaza el resto de la cadena a partir de la posición pos si len no está dentro de la longitud del resto de la cadena. Retorna NULL si cualquier argumento es nulo.

    mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
            -> 'QuWhattic'
    mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
            -> 'Quadratic'
    mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
            -> 'QuWhat'
    

    Esta función está preparada para funcionar con múltiples bytes.

  • INSTR(str,substr)

    Retorna la posición de la primera ocurrencia de la subcadena substr en la cadena str. Es lo mismo que la forma de dos argumentos de LOCATE(), excepto que el orden de los argumentos es inverso.

    mysql> SELECT INSTR('foobarbar', 'bar');
            -> 4
    mysql> SELECT INSTR('xbar', 'foobar');
            -> 0
    

    Esta función puede trabajar con múltiples bytes. En MySQL 5.0, sólo es sensible a mayúsculas si uno de los argumentos es una cadena binaria.

  • LCASE(str)

    LCASE() es sinónimo de LOWER().

  • LEFT(str,len)

    Retorna los len caracteres empezando por la izquierda de la cadena str.

    mysql> SELECT LEFT('foobarbar', 5);
            -> 'fooba'
    

  • LENGTH(str)

    Retorna la longitud de la cadena str, medida en bytes.Un carácter multi-byte cuenta como múltiples bytes. Esto significa que para cadenas que contengan cinco caracteres de dos bytes, LENGTH() retorna 10, mientras que CHAR_LENGTH() retorna 5.

    mysql> SELECT LENGTH('text');
            -> 4
    

  • LOAD_FILE(file_name)

    Lee el fichero y retorna el contenido como cadena de caracteres. El fichero debe estar localizado en el servidor, debe especificar la ruta completa al fichero, y debe tener el privilegio FILE . El fichero debe ser legible por todo el mundo y su tamaño menor a max_allowed_packet bytes.

    Si el fichero no existe o no puede ser leído debido a que una de las anteriores condiciones no se cumple, la función retorna NULL.

    mysql> UPDATE tbl_name
               SET blob_column=LOAD_FILE('/tmp/picture')
               WHERE id=1;
    

  • LOCATE(substr,str) , LOCATE(substr,str,pos)

    La primera sintaxis retorna la posición de la primera ocurrencia de la subcadena substr en la cadena str. La segunda sintaxis retorna la posición de la primera ocurrencia de la subcadena substr en la cadena str, comanzando en la posición pos. Retorna 0 si substr no está en str.

    mysql> SELECT LOCATE('bar', 'foobarbar');
            -> 4
    mysql> SELECT LOCATE('xbar', 'foobar');
            -> 0
    mysql> SELECT LOCATE('bar', 'foobarbar',5);
            -> 7
    

    Esta función trabaja con múltiples bytes. En MySQL 5.0, es sensible a mayúsculas sólo si algún argumento es una cadena binaria.

  • LOWER(str)

    Retorna la cadena str con todos los caracteres cambiados a minúsculas según el mapeo del conjunto de caracteres actual (por defecto es ISO-8859-1 Latin1).

    mysql> SELECT LOWER('QUADRATICALLY');
            -> 'quadratically'
    

    Esta función funciona con múltiples bytes.

  • LPAD(str,len,padstr)

    Retorna la cadena str, alineado a la izquierda con la cadena padstr a una longitud de len caracteres. Si str es mayor que len, el valor retornado se acorta a len caracteres.

    mysql> SELECT LPAD('hi',4,'??');
            -> '??hi'
    mysql> SELECT LPAD('hi',1,'??');
            -> 'h'
    

  • LTRIM(str)

    Retorna la cadena str con los caracteres en blanco iniciales eliminados.

    mysql> SELECT LTRIM('  barbar');
            -> 'barbar'
    

    Esta función trabaja con múltiples bytes.

  • MAKE_SET(bits,str1,str2,...)

    Retorna un conjunto de valores (una cadena conteniendo subcadenas separadas por caracteres ',' ) consistiendo en cadenas que tienen el bit correspondiente en bits asignado. str1 se corresponde al bit 0, str2 al bit 1, y así. Los valores NULL en str1, str2, ... no se añaden al resultado.

    mysql> SELECT MAKE_SET(1,'a','b','c');
            -> 'a'
    mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
            -> 'hello,world'
    mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
            -> 'hello'
    mysql> SELECT MAKE_SET(0,'a','b','c');
            -> ''
    

  • MID(str,pos,len)

    MID(str,pos,len) es sinónimo de SUBSTRING(str,pos,len).

  • OCT(N)

    Retorna una representación en cadena del valor octal de N, donde N es un número largo (BIGINT). Es equivalente a CONV(N,10,8). Retorna NULL si N es NULL.

    mysql> SELECT OCT(12);
            -> '14'
    

  • OCTET_LENGTH(str)

    OCTET_LENGTH() es sinónimo de LENGTH().

  • ORD(str)

    Si el carácter más a la izquierda de la cadena str es un carácter multi-byte , retorna el código de ese carácter, calculado a partir del valor numérico de sus bytes usando esta fórmula:

      (1st byte code)
    + (2nd byte code * 256)
    + (3rd byte code * 256^2) ...
    

    Si el caráctar más a la izquierda no es multi-byte , ORD() retorna el mismo valor que la función ASCII() .

    mysql> SELECT ORD('2');
            -> 50
    

  • POSITION(substr IN str)

    POSITION(substr IN str) es sinónimo de LOCATE(substr,str).

  • QUOTE(str)

    Acota una cadena para producir un resultado que puede usarse como un valor con caracteres de escape en un comando SQL. La cadena se retorna rodeado por comillas sencillas y con cada instancia de comilla sencilla ('''), antibarra ('\'), ASCII NUL, y Control-Z predecidos por una antibarra. Si el argumento es NULL, el valor de retorno es la palabra “NULL” sin comillas alrededor.

    mysql> SELECT QUOTE('Don\'t!');
            -> 'Don\'t!'
    mysql> SELECT QUOTE(NULL);
            -> NULL
    

  • REPEAT(str,count)

    Retorna una cadena consistente de la cadena str repetida count veces. Si count <= 0, retorna una cadena vacía. Retorna NULL si str o count son NULL.

    mysql> SELECT REPEAT('MySQL', 3);
            -> 'MySQLMySQLMySQL'
    

  • REPLACE(str,from_str,to_str)

    Retorna la cadena str con todas las ocurrencias de la cadena from_str reemplazadas con la cadena to_str.

    mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
            -> 'WwWwWw.mysql.com'
    

    Esta función trabaja con múltiples bytes.

  • REVERSE(str)

    Retorna la cadena str con el orden de los caracteres invertido.

    mysql> SELECT REVERSE('abc');
            -> 'cba'
    

    Esta función trabaja con múltiples bytes.

  • RIGHT(str,len)

    Retorna los len caracteres de la derecha de la cadena str.

    mysql> SELECT RIGHT('foobarbar', 4);
            -> 'rbar'
    

    Esta función trabaja con múltiples bytes.

  • RPAD(str,len,padstr)

    Retorna la cadena str, alineada a la derecha con la cadena padstr con una longitud de len caracteres. Si str es mayor que len, el valor de retorno se corta a len caracteres.

    mysql> SELECT RPAD('hi',5,'?');
            -> 'hi???'
    mysql> SELECT RPAD('hi',1,'?');
            -> 'h'
    

    Esta función trabaja con múltiples bytes.

  • RTRIM(str)

    Retorna la cadena str con los espacios precedentes eliminados.

    mysql> SELECT RTRIM('barbar   ');
            -> 'barbar'
    

    Esta función trabaja con múltiples bytes.

  • SOUNDEX(str)

    Retorna una cadena soudex de str. Dos cadenas que suenen igual deben tener cadenas soundex idénticas. Una cadena soundex estándar tiene cuatro caracteres de longitud, pero la función SOUNDEX() retorna una cadena arbitráriamente larga. Puede usar SUBSTRING() en el resultado para obtener una cadena soundex estándar. Todos los caracteres no alfabéticos en str se ignoran. Todos los caracteres alfabéticos internacionales fuera del rango A-Z se tratan como vocales.

    mysql> SELECT SOUNDEX('Hello');
            -> 'H400'
    mysql> SELECT SOUNDEX('Quadratically');
            -> 'Q36324'
    

    Nota: Esta función implementa el algoritmo Soundex original, no la versión más popular (descrita por D. Hnuth). La diferencia es que la versión original descarta vocales primero y luego duplicados, mientras que la versión mejorada descarta primero los duplicados y luego las vocales.

  • expr1 SOUNDS LIKE expr2

    Es lo mismo que SOUNDEX(expr1) = SOUNDEX(expr2).

  • SPACE(N)

    Retorna la cadena consistente en N caracteres blancos.

    mysql> SELECT SPACE(6);
            -> '      '
    

  • SUBSTRING(str,pos) , SUBSTRING(str FROM pos), SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

    Las formas sin el arguemnto len retornan una subcadena de la cadena str comenzando en la posición pos. Las formas con el argumento len retornan una subcadena de longitud len a partir de la cadena str, comenzando en la posición pos.Las formas que usan FROM son sintaxis SQL estándard. En MySQL 5.0, es posible usar valores negativos para pos. En este caso, el inicio de la subcadena son pos caracteres a partir del final de la cadena, en lugar del principio. Un valor negativo puede usarse para pos en cualquier de las formas de esta función.

    mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'
    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'
    mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'        
    mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'        
    mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'
    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'
    

    Esta función trabaja con múltiples bytes.

    Tenga en cuenta que si usa un valor menor a 1 para len, el resultado siempre es una cadena vacía.

    SUBSTR() es sinónimo de SUBSTRING().

  • SUBSTRING_INDEX(str,delim,count)

    Retorna la subcadena de la cadena str antes de count ocurrencias del delimitador delim. Si count es positivo, todo a la izquierda del delimitador final (contando desde la izquierda) se retorna. Si count es negativo, todo a la derecha del delimitador final (contando desde la derecha) se retorna.

    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
            -> 'www.mysql'
    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
            -> 'mysql.com'
    

    Esta función trabaja con múltiples bytes.

  • TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM(remstr FROM] str)

    Retorna la cadena str con todos los prefijos y/o sufijos remstr eliminados. Si ninguno de los especificadores BOTH, LEADING, o se daTRAILING, BOTH se asumen. Si remstr es opcional y no se especifica, los espacios se eliminan.

    mysql> SELECT TRIM('  bar   ');
            -> 'bar'
    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'
    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'
    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'
    

    Esta función trabaja con múltiples bytes.

  • UCASE(str)

    UCASE() es sinónimo de UPPER().

  • UNCOMPRESS(string_to_uncompress)

    Descomprime una cadena comprimida con la función COMPRESS() . Si el argumento no es un valor comprimido, el resultado es NULL. Esta función necesita que MySQL se compile con una biblioteca de compresión tal como zlib. De otro modo, el valor de retorno siempre es NULL.

    mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
            -> 'any string'
    mysql> SELECT UNCOMPRESS('any string');
            -> NULL
    

  • UNCOMPRESSED_LENGTH(compressed_string)

    Retorna la longitud de una cadena comprimida antes de la compresión.

    mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
            -> 30
    

  • UNHEX(str)

    Realiza la operación opuesta a HEX(str). Esto es, interpreta cada par de dígitos hexadecimales en el argumento como números y los convierte al carácter representado por el número. El caracteres resultantes se retornan como cadena binaria.

    mysql> SELECT UNHEX('4D7953514C');
            -> 'MySQL'
    mysql> SELECT 0x4D7953514C;
            -> 'MySQL'
    mysql> SELECT UNHEX(HEX('string'));
            -> 'string'
    mysql> SELECT HEX(UNHEX('1267'));
            -> '1267'
    

  • UPPER(str)

    Retorna la cadena str con todos los caracteres cambiados a mayúsculas según el mapeo del conjunto de caracteres actual (por defecto es ISO-8859-1 Latin1).

    mysql> SELECT UPPER('Hej');
            -> 'HEJ'
    

    Esta función trabaja con múltiples bytes.

12.3.1. Funciones de comparación de cadenas de caracteres

MySQL convierte automáticamente números a cadenas según es necesario y viceversa.

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

Si quiere convertir un número a cadena explícitamente, use la función CAST() :

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'

Si una función de cadenas da una cadena binaria como argumento, la cadena resultante también es binaria. Un número convertido a cadena se trata como cadena binaria (esto es, es sensible a mayúsculas en comparaciones). Esto afecta sólo a comparaciones.

Normalmente, si una expresión en una comparación de cadenas es sensible a mayúsculas, la comparación se realiza con sensibilidad a mayúsculas.

  • expr LIKE pat [ESCAPE 'escape-char']

    Coincidencia de patrones usando comparación mediante expresiones regulares SQL. Retorna 1 (TRUE) o 0 (FALSE). Si expr o pat es NULL, el resultado es NULL.

    El patrón no puede ser una cadena literal. Por ejemplo, puede especificarse como expresión de cadena o columna.

    Con LIKE puede usar los siguientes dos caracteres comodín en el patrón:

    CarácterDescrición
    %Coincidencia de cualquier número de caracteres, incluso cero caracteres
    _Coincide exactemente un carácter
    mysql> SELECT 'David!' LIKE 'David_';
            -> 1
    mysql> SELECT 'David!' LIKE '%D%v%';
            -> 1
    

    Para testear instancias literales de un carácter comodín, preceda el carácter con el carácter de escape. Si no especifica el carácter ESCAPE , se asume '\' .

    CadenaDescrición
    \%Coincide un carácter '%'
    \_Coincide un carácter '_'
    mysql> SELECT 'David!' LIKE 'David\_';
            -> 0
    mysql> SELECT 'David_' LIKE 'David\_';
            -> 1
    

    Para especificar un carácter de escape distinto, use la cláusula ESCAPE :

    mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
            -> 1
    

    Los siguientes dos comandos ilustran que la comparación de cadenas no son sensibles a mayúsculas a no ser que uno de los operandos sea una cadena binaria:

    mysql> SELECT 'abc' LIKE 'ABC';
            -> 1
    mysql> SELECT 'abc' LIKE BINARY 'ABC';
            -> 0
    

    En MySQL, LIKE se permite en expresiones numéricas. (Esta es una extensión del SQL estándar LIKE.)

    mysql> SELECT 10 LIKE '1%';
            -> 1
    

    Nota: Debido a que MySQL usa sintaxis de escape C en cadenas (por ejemplo, '\n' para representar carácter de nueva línea), debe doblar cualquier '\' que use en cadenas LIKE . Por ejemplo, para buscar '\n', especifíquelo como '\\n'. Para buscar '\', especifíquelo como '\\\\'; esto es debido a que las antibarras se eliminan una vez por el parser y otra vez cuando la coincidencia con el patrón se realiza, dejando una única antibarra para comparar.

  • expr NOT LIKE pat [ESCAPE 'escape-char']

    Es lo mismo que NOT (expr LIKE pat [ESCAPE 'escape-char']).

  • expr NOT REGEXP pat, expr NOT RLIKE pat

    Es lo mismo que NOT (expr REGEXP pat).

  • expr REGEXP pat, expr RLIKE pat

    Realiza una comparación de patrones de una expresión de cadena de caracteres expr contra un patrón pat. El patrón puede ser una expresión regular extendida. La sintaxis para expresiones regulares se discute en Apéndice F, Expresiones regulares en MySQL. Retorna 1 si expr coincide con pat, de otro modo retorna 0. Si expr o pat es NULL, el resultado es NULL. RLIKE es un sinónimo de REGEXP, debido a compatibilidad con mSQL.

    El patrón no necesita ser una cadena literal. Por ejemplo, puede especificarse como una expresión de cadena o columna.

    Nota: Debido a que MySQL usa la sintaxis de escape de C en cadenas (por ejemplo, '\n' para representar una nueva línea), de doblar cualquier '\' que use en sus cadenas REGEXP .

    REGEXP no es sensible a mayúsculas, excepto cuando se usa con cadenas binarias.

    mysql> SELECT 'Monty!' REGEXP 'm%y%%';
            -> 0
    mysql> SELECT 'Monty!' REGEXP '.*';
            -> 1
    mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
            -> 1
    mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
            -> 1  0
    mysql> SELECT 'a' REGEXP '^[a-d]';
            -> 1
    

    REGEXP y RLIKE usan el conjunto de caracteres actual (ISO-8859-1 Latin1 por defecto) al decidir el tipo de un carácter. Atención: Estos operadores no pueden trabajar con múltiples bytes.

  • STRCMP(expr1,expr2)

    STRCMP() retorna 0 si las cadenas son idénticas, -1 si el primer argumento es menor que el segundo según el orden actual, y 1 en cualquier otro caso.

    mysql> SELECT STRCMP('text', 'text2');
            -> -1
    mysql> SELECT STRCMP('text2', 'text');
            -> 1
    mysql> SELECT STRCMP('text', 'text');
            -> 0
    

    En MySQL 5.0, STRCMP() usa el conjunto de caracteres actual cuando realizac comparaciones. Esto hace el comportamiento de comparaciones por defecto insensible a mayúsculas a no ser que alguno de los operandos sea una cadena binaria.

12.4. Funciones numéricas

12.4.1. Operadores aritméticos

Los operadores aritméticos usuales están disponibles. Tenga en cuenta que en el caso de -, +, y *, el resultado se calcula con precisión BIGINT (64-bit) si ambos argumentos son enteros. Si uno de los argumentos es un entero sin signo, y los otros argumentos son también enteros, el resultado es un entero sin signo. Consulte Sección 12.8, “Funciones y operadores de cast”.

  • +

    Suma:

    mysql> SELECT 3+5;
            -> 8
    

  • -

    Resta:

    mysql> SELECT 3-5;
            -> -2
    

  • -

    Menos unario. Cambia el signo del argumento.

    mysql> SELECT - 2;
            -> -2
    

    Nota: Si este operador se usa con BIGINT, el valor de retorno es también BIGINT. Esto significa que debe eliminar usar - con enteros que pueden ser iguales o menores a -2^63.

  • *

    Multiplicación:

    mysql> SELECT 3*5;
            -> 15
    mysql> SELECT 18014398509481984*18014398509481984.0;
            -> 324518553658426726783156020576256.0
    mysql> SELECT 18014398509481984*18014398509481984;
            -> 0
    

    El resultado de la última expresión es incorrecto ya que el resultado de la multiplicación entera excede el rango de 64-bit de cálculos BIGINT. (Consulte Sección 11.2, “Tipos numéricos”.)

  • /

    División:

    mysql> SELECT 3/5;
            -> 0.60
    

    División por cero produce un resultado NULL:

    mysql> SELECT 102/(1-1);
            -> NULL
    

    Una división se calcula con aritmética BIGINT sólo en un contexto donde el resultado se convierte a entero.

  • DIV

    División entera. Similar aFLOOR() pero funciona con valores BIGINT.

    mysql> SELECT 5 DIV 2;
            -> 2
    

12.4.2. Funciones matemáticas

Todas las funciones matemáticas retornan NULL en caso de error.

  • ABS(X)

    Retorna el valor absoluto de X.

    mysql> SELECT ABS(2);
            -> 2
    mysql> SELECT ABS(-32);
            -> 32
    

    Esta función puede usar valores BIGINT.

  • ACOS(X)

    Retorna el arcocoseno de X, esto es, el valor cuyo coseno es X. Retorna NULL si X no está en el rango -1 a 1.

    mysql> SELECT ACOS(1);
            -> 0
    mysql> SELECT ACOS(1.0001);
            -> NULL
    mysql> SELECT ACOS(0);
            -> 1.5707963267949
    

  • ASIN(X)

    Retorna el arcoseno de X, esto es, el valor cuyo seno es X. Retorna NULL si X no está en el rango de -1 a 1.

    mysql> SELECT ASIN(0.2);
            -> 0.20135792079033
    mysql> SELECT ASIN('foo');
            -> 0
    

  • ATAN(X)

    Retorna la arcotangente de X, esto es, el valor cuya tangente es X.

    mysql> SELECT ATAN(2);
            -> 1.1071487177941
    mysql> SELECT ATAN(-2);
            -> -1.1071487177941
    

  • ATAN(Y,X) , ATAN2(Y,X)

    Retorna la arcotangente de las variables X y Y. Es similar a calcular la arcotangente de Y / X, excepto que los signos de ambos argumentos se usan para determinar el cuadrante del resultado.

    mysql> SELECT ATAN(-2,2);
            -> -0.78539816339745
    mysql> SELECT ATAN2(PI(),0);
            -> 1.5707963267949
    

  • CEILING(X), CEIL(X)

    Retorna el entero más pequeño no menor a X.

    mysql> SELECT CEILING(1.23);
            -> 2
    mysql> SELECT CEIL(-1.23);
            -> -1
    

    Estas dos funciones son sinónimos. Tenga en cuenta que el valor retornado se convierte a BIGINT.

  • COS(X)

    Retorna el coseno de X, donde X se da en radianes.

    mysql> SELECT COS(PI());
            -> -1
    

  • COT(X)

    Retorna la cotangente de X.

    mysql> SELECT COT(12);
            -> -1.5726734063977
    mysql> SELECT COT(0);
            -> NULL
    

  • CRC32(expr)

    Computa un valor de redundancia cíclica y retorna el valor sin signo de 32 bits. El resultado es NULL si el argumento es NULL. Se espera que el argumento sea una cadena y (si es posible) se trata como una si no lo es.

    mysql> SELECT CRC32('MySQL');
            -> 3259397556
    mysql> SELECT CRC32('mysql');
            -> 2501908538
    

  • DEGREES(X)

    Retorna el argumento X, convertido de radianes a grados.

    mysql> SELECT DEGREES(PI());
            -> 180
    mysql> SELECT DEGREES(PI() / 2);
            -> 90
    

  • EXP(X)

    Retorna el valor de e (la base del logaritmo natural) a la potencia de X.

    mysql> SELECT EXP(2);
            -> 7.3890560989307
    mysql> SELECT EXP(-2);
            -> 0.13533528323661
    

  • FLOOR(X)

    Retorna el valor entero más grande pero no mayor a X.

    mysql> SELECT FLOOR(1.23);
            -> 1
    mysql> SELECT FLOOR(-1.23);
            -> -2
    

    Tenga en cuenta que el valor devuelto se convierte a BIGINT.

  • LN(X)

    Retorna el logaritmo natural de X, esto es, el logaritmo de X base e.

    mysql> SELECT LN(2);
            -> 0.69314718055995
    mysql> SELECT LN(-2);
            -> NULL
    

    Esta función es sinónimo a LOG(X).

  • LOG(X), LOG(B,X)

    Si se llama con un parámetro, esta función retorna el logaritmo natural de X.

    mysql> SELECT LOG(2);
            -> 0.69314718055995
    mysql> SELECT LOG(-2);
            -> NULL
    

    Si se llama con dos parámetros, esta función retorna el logaritmo de X para una base arbitrária B.

    mysql> SELECT LOG(2,65536);
            -> 16
    mysql> SELECT LOG(10,100);
            -> 2
    

    LOG(B,X) es equivalente a LOG(X) / LOG(B).

  • LOG2(X)

    Retorna el logaritmo en base 2 de X.

    mysql> SELECT LOG2(65536);
            -> 16
    mysql> SELECT LOG2(-100);
            -> NULL
    

    LOG2() es útil para encontrar cuántos bits necesita un número para almacenamiento. Esta función es equivalente a la expresión LOG(X) / LOG(2).

  • LOG10(X)

    Retorna el logaritmo en base 10 de X.

    mysql> SELECT LOG10(2);
            -> 0.30102999566398
    mysql> SELECT LOG10(100);
            -> 2
    mysql> SELECT LOG10(-100);
            -> NULL
    

    LOG10(X) es equivalente a LOG(10,X).

  • MOD(N,M) , N % M, N MOD M

    Operación de módulo. Retorna el resto de N dividido por M.

    mysql> SELECT MOD(234, 10);
            -> 4
    mysql> SELECT 253 % 7;
            -> 1
    mysql> SELECT MOD(29,9);
            -> 2
    mysql> SELECT 29 MOD 9;
            -> 2
    

    Esta función puede usar valores BIGINT.

    MOD() también funciona con valores con una parte fraccional y retorna el resto exacto tras la división:

    mysql> SELECT MOD(34.5,3);
            -> 1.5
    

  • PI()

    Retorna el valor de π (pi). El número de decimales que se muestra por defecto es siete, pero MySQL usa internamente el valor de doble precisión entero.

    mysql> SELECT PI();
            -> 3.141593
    mysql> SELECT PI()+0.000000000000000000;
            -> 3.141592653589793116
    

  • POW(X,Y) , POWER(X,Y)

    Retorna el valor de X a la potencia de Y.

    mysql> SELECT POW(2,2);
            -> 4
    mysql> SELECT POW(2,-2);
            -> 0.25
    

  • RADIANS(X)

    Retorna el argumento X, convertido de grados a radianes. (Tenga en cuenta que π radianes son 180 grados.)

    mysql> SELECT RADIANS(90);
            -> 1.5707963267949
    

  • RAND(), RAND(N)

    Retorna un valor aleatorio en coma flotante del rango de 0 a 1.0. Si se especifica un argumento entero N, es usa como semilla, que produce una secuencia repetible.

    mysql> SELECT RAND();
            -> 0.9233482386203
    mysql> SELECT RAND(20);
            -> 0.15888261251047
    mysql> SELECT RAND();
            -> 0.63553050033332
    mysql> SELECT RAND();
            -> 0.70100469486881
    mysql> SELECT RAND(20);
            -> 0.15888261251047
    

    Puede usar esta función para recibir registros de forma aleatoria como se muestra aquí:

    mysql> SELECT * FROM tbl_name ORDER BY RAND();
    

    ORDER BY RAND() combinado con LIMIT es útil para seleccionar una muestra aleatoria de una conjunto de registros:

    mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
        -> ORDER BY RAND() LIMIT 1000;
    

    Tenga en cuenta que RAND() en una cláusula WHERE se re-evalúa cada vez que se ejecuta el WHERE.

    RAND() no pretende ser un generador de números aleatorios perfecto, pero es una forma rápida de generar números aleatorios ad hoc portable entre plataformas para la misma versión de MySQL.

  • ROUND(X), ROUND(X,D)

    Retorna el argumento X, redondeado al entero más cercano. Con dos argumentos, retorna X redondeado a D decimales. D puede ser negativo para redondear D dígitos a la izquierda del punto decimal del valor X.

    mysql> SELECT ROUND(-1.23);
            -> -1
    mysql> SELECT ROUND(-1.58);
            -> -2
    mysql> SELECT ROUND(1.58);
            -> 2
    mysql> SELECT ROUND(1.298, 1);
            -> 1.3
    mysql> SELECT ROUND(1.298, 0);
            -> 1
    mysql> SELECT ROUND(23.298, -1);
            -> 20
    

    El tipo de retorno es el mismo tipo que el del primer argumento (asumiendo que sea un entero, doble o decimal). Esto significa que para un argumento entero, el resultado es un entero (sin decimales).

    Antes de MySQL 5.0.3, el comportamiento de ROUND() cuando el argumento se encuentra a medias entre dos enteros depende de la implementación de la biblioteca C. Implementaciones distintas redondean al número par más próximo, siempre arriba, siempre abajo, o siempre hacia cero. Si necesita un tipo de redondeo, debe usar una función bien definida como TRUNCATE() o FLOOR() en su lugar.

    Desde MySQL 5.0.3, ROUND() usa la biblioteca de matemática precisa para valores exactos cuando el primer argumento es un valor con decimales:

    • Para números exactos, ROUND() usa la regla de "redondea la mitad hacia arriba": Un valor con una parte fracional de .5 o mayor se redondea arriba al siguiente entero si es positivo o hacia abajo si el siguiente entero es negativo. (En otras palabras, se redondea en dirección contraria al cero.) Un valor con una parte fraccional menor a .5 se redondea hacia abajo al siguiente entero si es positivo o hacia arriba si el siguiente entero es negativo.

    • Para números aproximados, el resultado depende de la biblioteca C. En muchos sistemas, esto significa que ROUND() usa la regla de "redondeo al número par más cercano": Un valor con una parte fraccional se redondea al entero más cercano.

    El siguiente ejemplo muestra cómo el redondeo difiere para valores exactos y aproximados:

    mysql> SELECT ROUND(2.5), ROUND(25E-1);
    +------------+--------------+
    | ROUND(2.5) | ROUND(25E-1) |
    +------------+--------------+
    | 3          |            2 |
    +------------+--------------+
    

    Para más información, consulte Capítulo 23, Matemáticas de precisión.

  • SIGN(X)

    Retorna el signo del argumento como -1, 0, o 1, en función de si X es negativo, cero o positivo.

    mysql> SELECT SIGN(-32);
            -> -1
    mysql> SELECT SIGN(0);
            -> 0
    mysql> SELECT SIGN(234);
            -> 1
    

  • SIN(X)

    Retorna el seno de X, donde X se da en radianes.

    mysql> SELECT SIN(PI());
            -> 1.2246063538224e-16
    mysql> SELECT ROUND(SIN(PI()));
            -> 0
    

  • SQRT(X)

    Retorna la raíz cuadrada de un número no negativo. X.

    mysql> SELECT SQRT(4);
            -> 2
    mysql> SELECT SQRT(20);
            -> 4.4721359549996
    mysql> SELECT SQRT(-16);
            -> NULL        
    

  • TAN(X)

    Retorna la tangente de X, donde X se da en radianes.

    mysql> SELECT TAN(PI());
            -> -1.2246063538224e-16
    mysql> SELECT TAN(PI()+1);
            -> 1.5574077246549
    

  • TRUNCATE(X,D)

    Retorna el número X, truncado a D decimales. Si D es 0, el resultado no tiene punto decimal o parte fraccional. D puede ser negativo para truncar (hacer cero) D dígitos a la izquierda del punto decimal del valor X.

    mysql> SELECT TRUNCATE(1.223,1);
            -> 1.2
    mysql> SELECT TRUNCATE(1.999,1);
            -> 1.9
    mysql> SELECT TRUNCATE(1.999,0);
            -> 1
    mysql> SELECT TRUNCATE(-1.999,1);
            -> -1.9
    mysql> SELECT TRUNCATE(122,-2);
           -> 100
    

    Todos los números se redondean hacia cero.

12.5. Funciones de fecha y hora

Esta sección describe las funciones que pueden usarse para manipular valores temporales. Consulte Sección 11.3, “Tipos de fecha y hora” para una descripción del rango de los valores que tiene cada fecha y hora y los formatos válidos en que se puedene especificar los valores.

Aquí hay un ejemplo que usa funciones de fecha. La siguiente consulta selecciona todos los registros con un valor date_col dentro de los últimos 30 días:

mysql> SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

Tenga en cuenta que la consulta también selecciona registros con fechas futuras.

Las funciones que esperan valores de fecha usualmente aceptan valores de fecha y hora e ignoran la parte de hora. Las funciones que esperan valores de hora usualmente aceptan valores de fecha y hora e ignoran la parte de fecha.

Las funciones que retornan la fecha u hora actuales se evalúan sólo una vez por consulta al principio de la ejecución de consulta. Esto significa que las referencias múltiples a una función tales como NOW() en una misma consulta siempre producen el mismo resultado. Este principio también se aplica a CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), y a cualquiera de sus sinónimos.

En MySQL 5.0, las funciones CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), y FROM_UNIXTIME() retornan valores en la zona horaria de la conexión, que está disponible como valor de la variable de sistema time_zone . Además, UNIX_TIMESTAMP() asume que su argumento es un valor de fecha y hora en la zona horaria actual. Consulte Sección 5.9.8, “Soporte de zonas horarias en el servidor MySQL”.

Los rango de retorno en las siguientes descripciones de funciones se aplican en fechas completas. Si la fecha es un valor “cero” o una fecha incompleta como '2001-11-00', las funciones que extraen una parte de la fecha pueden retornar 0. Por ejemplo DAYOFMONTH('2001-11-00') retorna 0.

  • ADDDATE(date,INTERVAL expr type), ADDDATE(expr,days)

    Cuando se invoca con la forma INTERVAL del segundo argumento, ADDDATE() es sinónimo de DATE_ADD(). La función relacionada SUBDATE() es sinónimo de DATE_SUB(). Para información del argumento INTERVAL , consulte la discusión de DATE_ADD().

    mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
            -> '1998-02-02'
    mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
            -> '1998-02-02'
    

    Si el argumento days es simplemente un valor entero, entonces MySQL 5.0 lo trata como el número de días a añadir a expr.

    mysql> SELECT ADDDATE('1998-01-02', 31);
            -> '1998-02-02'
    

  • ADDTIME(expr,expr2)

    ADDTIME() añade expr2 a expr y retorna el resultado. expr es una expresión de fecha u hora y fecha, y expr2 es una expresión temporal.

    mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',
        ->                '1 1:1:1.000002');
            -> '1998-01-02 01:01:01.000001'
    mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
            -> '03:00:01.999997'
    

  • CONVERT_TZ(dt,from_tz,to_tz)

    CONVERT_TZ() convierte un valor datetime dt de la zona horaria dada por from_tz a la zona horaria dada por to_tz y retorna el valor resultante. Las zonas horarias pueden especificarse como se describe en Sección 5.9.8, “Soporte de zonas horarias en el servidor MySQL”. Esta función retorna NULL si los argumentos son inválidos.

    Si el valor se sale del rango soportado por el tipo TIMESTAMP al convertirse de from_tz a UTC, no se realiza ninguna conversión. El rango TIMESTAMP se describe en Sección 11.1.2, “Panorámica de tipos de fechas y hora”.

    mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
            -> '2004-01-01 13:00:00'
    mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
            -> '2004-01-01 22:00:00'
    

    Nota: Para usar zonas horarias con nombres tales como 'MET' o 'Europe/Moscow', las tabas de zona horaria deben estar actualizadas correctamente. Consulte Sección 5.9.8, “Soporte de zonas horarias en el servidor MySQL” para instrucciones.

  • CURDATE()

    Retorna la fecha horaria como valor en formato 'YYYY-MM-DD' o YYYYMMDD, dependiendo de si la fucnión se usa en un contexto numérico o de cadena de caracteres.

    mysql> SELECT CURDATE();
            -> '1997-12-15'
    mysql> SELECT CURDATE() + 0;
            -> 19971215
    

  • CURRENT_DATE, CURRENT_DATE()

    CURRENT_DATE y CURRENT_DATE() son sinónimos de CURDATE().

  • CURTIME()

    Retorna la hora actual como valor en formato 'HH:MM:SS' o HHMMSS dependiendo de si la fucnión se usa en un contexto numérico o de cadena de caracteres.

    mysql> SELECT CURTIME();
            -> '23:50:26'
    mysql> SELECT CURTIME() + 0;
            -> 235026
    

  • CURRENT_TIME, CURRENT_TIME()

    CURRENT_TIME y CURRENT_TIME() son sinónimos de CURTIME().

  • CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()

    CURRENT_TIMESTAMP() son sinónimos de NOW().

  • DATE(expr)

    Extrae la parte de fecha de la expresión de fecha o fecha y hora expr.

    mysql> SELECT DATE('2003-12-31 01:02:03');
            -> '2003-12-31'
    

  • DATEDIFF(expr,expr2)

    DATEDIFF() retorna el número de días entre la fecha inicial expr y la fecha final expr2. expr y expr2 son expresiones de fecha o de fecha y hora. Sólo las partes de fecha de los valores se usan en los cálculos.

    mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
            -> 1
    mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
            -> -31
    

  • DATE_ADD(date,INTERVAL expr type), DATE_SUB(date,INTERVAL expr type)

    Estas funciones realizan operaciones aritméticas de fechas. date es un valor DATETIME o DATE especificando la fecha de inicio. expr es una expresión que especifica el intervalo a añadir o borrar de la fecha de inicio. expr es una cadena; puede comenzar con un '-' para intervalos negativos. type es una palabra clave que indica cómo debe interpretarse la expresión.

    La palabra clave INTERVAL y el especificador type no son sensibles a mayúsculas.

    La siguiente tabla muestra cómo se relacionan los argumentos type y expr :

    type ValueExpected expr Format
    MICROSECONDMICROSECONDS
    SECONDSECONDS
    MINUTEMINUTES
    HOURHOURS
    DAYDAYS
    WEEKWEEKS
    MONTHMONTHS
    QUARTERQUARTERS
    YEARYEARS
    SECOND_MICROSECOND'SECONDS.MICROSECONDS'
    MINUTE_MICROSECOND'MINUTES.MICROSECONDS'
    MINUTE_SECOND'MINUTES:SECONDS'
    HOUR_MICROSECOND'HOURS.MICROSECONDS'
    HOUR_SECOND'HOURS:MINUTES:SECONDS'
    HOUR_MINUTE'HOURS:MINUTES'
    DAY_MICROSECOND'DAYS.MICROSECONDS'
    DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'
    DAY_MINUTE'DAYS HOURS:MINUTES'
    DAY_HOUR'DAYS HOURS'
    YEAR_MONTH'YEARS-MONTHS'

    Los valores QUARTER y WEEK están disponibles a partir de MySQL 5.0.0.

    MySQL permite cualquier delimitador en el formato expr . Los mostrados en la tabla son sugerencias. Si el argumento date es un valora DATE y sus cálculos involucarán sólo partes YEAR, MONTH, y DAY (esto es, sin partes de hora), el resultado es un valor DATE . De otro modo, el resultado es un valor DATETIME .

    INTERVAL expr type se permite en cualquier lado del operador + si la expresión en el otro lado es una fecha o fecha y hora. Para el operador - , INTERVAL expr type se permite sólo en la parte derecha, ya que no tiene sentido restar una fecha de un intervalo. (Consulte los ejemplos a continuación.)

    mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
            -> '1998-01-01 00:00:00'
    mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
            -> '1998-01-01'
    mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
            -> '1997-12-31 23:59:59'
    mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
        ->                 INTERVAL 1 SECOND);
            -> '1998-01-01 00:00:00'
    mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
        ->                 INTERVAL 1 DAY);
            -> '1998-01-01 23:59:59'
    mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
        ->                 INTERVAL '1:1' MINUTE_SECOND);
            -> '1998-01-01 00:01:00'
    mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
        ->                 INTERVAL '1 1:1:1' DAY_SECOND);
            -> '1997-12-30 22:58:59'
    mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
        ->                 INTERVAL '-1 10' DAY_HOUR);
            -> '1997-12-30 14:00:00'
    mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
            -> '1997-12-02'
    mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
        ->            INTERVAL '1.999999' SECOND_MICROSECOND);
            -> '1993-01-01 00:00:01.000001'
    

    Si especifica un intervalo demasiado pequeño (no incluye todas las partes de intervalo que se esperarían de la palabra clave type ), MySQL asume que ha dejado la parte más a la izquierda del valor del intervalo. Por ejemplo, si especifica un type de DAY_SECOND, se espera que el valor de expr tenga días, horas, minutos y segundos. Si especifica un valor como '1:10', MySQL asume que las partes de día y hora no se encuentran disponibles y que el valor representa minutos y segundos. En otras palabras, '1:10' DAY_SECOND se interpreta de forma que es equivalente a '1:10' MINUTE_SECOND. Esto es análogo a la forma en que MySQL interpreta valores TIME como representando tiempo transcurrido en lugar de la hora del día.

    Si suma o borra de un valor de fecha algo que contenga una parte de fora, el resultado se convierte automáticamente a valor fecha/hora:

    mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
            -> '1999-01-02'
    mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
            -> '1999-01-01 01:00:00'
    

    Si usa fechas muy mal formadas, el resultado es NULL. Si suma MONTH, YEAR_MONTH, o YEAR y la fecha resultante tiene un día mayor que el día máximo para el nuevo mes, el día se ajusta al número máximo del nuevo mes:

    mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
            -> '1998-02-28'
    

  • DATE_FORMAT(date,format)

    Formatea el valor date según la cadena format . Los siguientes especificadores pueden usarse en la cadena format :

    EspecificadorDescripción
    %aDía de semana abreviado (Sun..Sat)
    %bMes abreviado (Jan..Dec)
    %cMes, numérico (0..12)
    %DDía del mes con sufijo inglés (0th, 1st, 2nd, 3rd, ...)
    %dDía del mes numérico (00..31)
    %eDía del mes numérico (0..31)
    %fMicrosegundos (000000..999999)
    %HHora (00..23)
    %hHora (01..12)
    %IHora (01..12)
    %iMinutos, numérico (00..59)
    %jDía del año (001..366)
    %kHora (0..23)
    %lHora (1..12)
    %MNombre mes (January..December)
    %mMes, numérico (00..12)
    %pAM o PM
    %rHora, 12 horas (hh:mm:ss seguido de AM o PM)
    %SSegundos (00..59)
    %sSegundos (00..59)
    %THora, 24 horas (hh:mm:ss)
    %USemana (00..53), donde domingo es el primer día de la semana
    %uSemana (00..53), donde lunes es el primer día de la semana
    %VSemana (01..53), donde domingo es el primer día de la semana; usado con %X
    %vSemana (01..53), donde lunes es el primer día de la semana; usado con %x
    %WNombre día semana (Sunday..Saturday)
    %wDía de la semana (0=Sunday..6=Saturday)
    %XAño para la semana donde domingo es el primer día de la semana, numérico, cuatro dígitos; usado con %V
    %xAño para la semana, donde lunes es el primer día de la semana, numérico, cuatro dígitos; usado con %v
    %YAño, numérico, cuatro dígitos
    %yAño, numérico (dos dígitos)
    %%Carácter '%' literal

    Todos los otros caracteres se copian al resultado sin interpretación.

    Tenga en cuenta que el carácter '%' se necesita antes de caracteres especificadores de formato.

    Los rangos para los especificadores de mes y día comienzan en cero debido a que MySQL permite almacenar fechas incompletas tales como '2004-00-00'.

    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
            -> 'Saturday October 1997'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
            -> '22:23:00'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                              '%D %y %a %d %m %b %j');
            -> '4th 97 Sat 04 10 Oct 277'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                              '%H %k %I %r %T %S %w');
            -> '22 22 10 10:23:00 PM 22:23:00 00 6'
    mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
            -> '1998 52'
    

  • DAY(date)

    DAY() es sinónimo de DAYOFMONTH().

  • DAYNAME(date)

    Retorna el nombre del día de la semana para date.

    mysql> SELECT DAYNAME('1998-02-05');
            -> 'Thursday'
    

  • DAYOFMONTH(date)

    Retorna el día del mes para date, en el rango 1 a 31.

    mysql> SELECT DAYOFMONTH('1998-02-03');
            -> 3
    

  • DAYOFWEEK(date)

    Retorna el índice del día de la semana para date (1 = domingo, 2 = lunes, ..., 7 = sábado). Estos valores del índice se corresponden con el estándar ODBC.

    mysql> SELECT DAYOFWEEK('1998-02-03');
            -> 3
    

  • DAYOFYEAR(date)

    Retorna el día del año para date, en el rango 1 a 366.

    mysql> SELECT DAYOFYEAR('1998-02-03');
            -> 34
    

  • EXTRACT(type FROM date)

    La función EXTRACT() usa la misma clase de especificadores de tipo que DATE_ADD() o DATE_SUB(), pero extrae partes de la fecha en lugar de realizar aritmética de fecha.

    mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
           -> 1999
    mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
           -> 199907
    mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');
           -> 20102
    mysql> SELECT EXTRACT(MICROSECOND
        ->                FROM '2003-01-02 10:30:00.000123');
            -> 123
    

  • FROM_DAYS(N)

    Dado un número de día N, retorna un valor DATE .

    mysql> SELECT FROM_DAYS(729669);
            -> '1997-10-07'
    

    Use FROM_DAYS() con precaución en fechas viejas. No se pretende que se use con valores que precedan el calendario Gregoriano (1582). Consulte Sección 12.6, “Qué calendario utiliza MySQL”.

  • FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

    Retorna una representación del argumento unix_timestamp como un valor en formato 'YYYY-MM-DD HH:MM:SS' o YYYYMMDDHHMMSS , dependiendo de si la función se usa en un formato numérico o de cadena de caracteres.

    mysql> SELECT FROM_UNIXTIME(875996580);
            -> '1997-10-04 22:23:00'
    mysql> SELECT FROM_UNIXTIME(875996580) + 0;
            -> 19971004222300
    

    Si se da format, el resultado se formatea según la cadena format. format puede contener los mismos especificadores que los listados en la entrada para la función DATE_FORMAT() .

    mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
        ->                      '%Y %D %M %h:%i:%s %x');
            -> '2003 6th August 06:22:58 2003'
    

  • GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

    Retorna una cadena de formato. Esta función es útil en combinación con las funciones DATE_FORMAT() y STR_TO_DATE() .

    Los tres valores posibles para el primer argumento y los cinco posibles valores para el segundo argumento resultan en 15 posibles cadenas de formato (para los especificadores usados, consulte la tabla en la descripción de la función DATE_FORMAT() ).

    LLamad a funciónResultado
    GET_FORMAT(DATE,'USA')'%m.%d.%Y'
    GET_FORMAT(DATE,'JIS')'%Y-%m-%d'
    GET_FORMAT(DATE,'ISO')'%Y-%m-%d'
    GET_FORMAT(DATE,'EUR')'%d.%m.%Y'
    GET_FORMAT(DATE,'INTERNAL')'%Y%m%d'
    GET_FORMAT(DATETIME,'USA')'%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(DATETIME,'JIS')'%Y-%m-%d %H:%i:%s'
    GET_FORMAT(DATETIME,'ISO')'%Y-%m-%d %H:%i:%s'
    GET_FORMAT(DATETIME,'EUR')'%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(DATETIME,'INTERNAL')'%Y%m%d%H%i%s'
    GET_FORMAT(TIME,'USA')'%h:%i:%s %p'
    GET_FORMAT(TIME,'JIS')'%H:%i:%s'
    GET_FORMAT(TIME,'ISO')'%H:%i:%s'
    GET_FORMAT(TIME,'EUR')'%H.%i.%S'
    GET_FORMAT(TIME,'INTERNAL')'%H%i%s'

    El formato ISO es ISO 9075, no ISO 8601.

    En MySQL 5.0, TIMESTAMP puede usarse; GET_FORMAT() retorna los mismos valores que para DATETIME.

    mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
            -> '03.10.2003'
    mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
            -> '2003-10-31'
            
    
    
    

    Consulte Sección 13.5.3, “Sintaxis de SET.

  • HOUR(time)

    Retorna la hora para time. El rango del valor de retorno es 0 a 23 para valores de horas del día.

    mysql> SELECT HOUR('10:05:03');
            -> 10
    

    Además, el rango de los valores TIME es mucho mayor, así que HOUR puede retornar valores mayores que 23.

    mysql> SELECT HOUR('272:59:59');
            -> 272
    

  • LAST_DAY(date)

    Toma una fecha o fecha/hora y retorna el valor correspondiente para el último día del mes. Retorna NULL si el argumento es inválido.

    mysql> SELECT LAST_DAY('2003-02-05');
            -> '2003-02-28'
    mysql> SELECT LAST_DAY('2004-02-05');
            -> '2004-02-29'
    mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
            -> '2004-01-31'
    mysql> SELECT LAST_DAY('2003-03-32');
            -> NULL
    

  • LOCALTIME, LOCALTIME()

    LOCALTIME y LOCALTIME() son sinónimos de NOW().

  • LOCALTIMESTAMP, LOCALTIMESTAMP()

    LOCALTIMESTAMP y LOCALTIMESTAMP() son sinónimos de NOW().

  • MAKEDATE(year,dayofyear)

    Retorna una fecha, dado un año y día del año. dayofyear debe ser mayor a 0 o el resultado es NULL.

    mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
            -> '2001-01-31', '2001-02-01'
    mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
            -> '2001-12-31', '2004-12-30'
    mysql> SELECT MAKEDATE(2001,0);
            -> NULL
    

  • MAKETIME(hour,minute,second)

    Retorna un valor horario calculado a partir de los argumentos hour, minute, y second .

    mysql> SELECT MAKETIME(12,15,30);
            -> '12:15:30'
    

  • MICROSECOND(expr)

    Retorna los microsegundos a partir del a expresión de hora o fecha/hora expr como número en el rango de 0 a 999999.

    mysql> SELECT MICROSECOND('12:00:00.123456');
            -> 123456
    mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');
            -> 10
    

  • MINUTE(time)

    Retorna el minuto de time, en el rango 0 a 59.

    mysql> SELECT MINUTE('98-02-03 10:05:03');
            -> 5
    

  • MONTH(date)

    Retorna el mes para date, en el rango 1 a 12.

    mysql> SELECT MONTH('1998-02-03');
            -> 2
    

  • MONTHNAME(date)

    Retorna el nombre completo del mes para date.

    mysql> SELECT MONTHNAME('1998-02-05');
            -> 'February'
    

  • NOW()

    Retorna la fecha y hora actual como valor en formato 'YYYY-MM-DD HH:MM:SS' o YYYYMMDDHHMMSS , dependiendo de si la función se usa en contexto numérico o de cadena de caracteres.

    mysql> SELECT NOW();
            -> '1997-12-15 23:50:26'
    mysql> SELECT NOW() + 0;
            -> 19971215235026
    

  • PERIOD_ADD(P,N)

    Añade N meses al periodo P (en el formato YYMM o YYYYMM). Retorna un valor en el formato YYYYMM. Tenga en cuenta que el argumento del periodo P no es una fecha.

    mysql> SELECT PERIOD_ADD(9801,2);
            -> 199803
    

  • PERIOD_DIFF(P1,P2)

    Retorna el número de meses entre periodos P1 y P2. P1 y P2 deben estar en el formato YYMM o YYYYMM. Tenga en cuenta que los argumentos del periodo P1 y P2 no son fechas.

    mysql> SELECT PERIOD_DIFF(9802,199703);
            -> 11
    

  • QUARTER(date)

    Retorna el cuarto del año para date, en el rango 1 a 4.

    mysql> SELECT QUARTER('98-04-01');
            -> 2
    

  • SECOND(time)

    Retorna el segundo para time, en el rango 0 a 59.

    mysql> SELECT SECOND('10:05:03');
            -> 3
    

  • SEC_TO_TIME(seconds)

    Retorna el argumento seconds , convertido a horas, minutos y segundos, como un valor en formato 'HH:MM:SS' o HHMMSS, dependiendo de si la función se usa en contexto numérico o de cadena de caracteres.

    mysql> SELECT SEC_TO_TIME(2378);
            -> '00:39:38'
    mysql> SELECT SEC_TO_TIME(2378) + 0;
            -> 3938
    

  • STR_TO_DATE(str,format)

  • Esta es la inversa de la función DATE_FORMAT(). Toma la cadena str y la cadena de formato format. STR_TO_DATE() retorna un valor DATETIME si la cadena de formato contiene parte de fecha y hora, o un valor DATE o TIME si la cadena contiene sólo parte de fecha o hora.

    Los valores fecha, hora o fecha/hora contenidos en str deben ser dados en el formato indicado por format. Para los especificadores que pueden usarse en format, consulte la tabla en la descripción de la función DATE_FORMAT() . Todos los otros caracteres no se interpretan. Si str contiene un valor fecha, hora o fecha/hora ilegal, STR_TO_DATE() retorna NULL. A partir de MySQL 5.0.3, un valor ilegal también produce una advertencia.

    
    
    mysql> SELECT STR_TO_DATE('03.10.2003 09.20','%d.%m.%Y %H.%i');
            -> '2003-10-03 09:20:00'
    mysql> SELECT STR_TO_DATE('10arp', '%carp');
            -> '0000-10-00 00:00:00'
    mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00','%Y-%m-%d %H:%i:%s');
            -> NULL
    

    El chequeo de rango en las partes de los valores de fecha se describe en Sección 11.3.1, “Los tipos de datos DATETIME, DATE y TIMESTAMP. Esto significa, por ejemplo, que una fecha con una parte de día mayor que el número de días en un mes se permite mientras la parte del día esté en el rango de 1 a 31. También, fechas “cero” o fechas con partes de 0 se permiten.

    mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
            -> '0000-00-00'
    mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
            -> '2004-04-31'
    

  • SUBDATE(date,INTERVAL expr type), SUBDATE(expr,days)

    Cuando se invoca con la forma INTERVAL del segundo argumento, SUBDATE() es sinónimo de DATE_SUB(). Para información del argumento INTERVAL , consulte la discusión para DATE_ADD().

    mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
            -> '1997-12-02'
    mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
            -> '1997-12-02'
    

    La siguiente forma permite el uso de un valor entero para days. En tales casos, es el número de días a ser borrados de la expresión fecha o fecha/hora expr.

    mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);
            -> '1997-12-02 12:00:00'
    

    Nota no puede usar formato "%X%V" para convertir una cadena año-semana en fecha ya que la combinación de un año y semana no identific unívocamente un año y semana si la semana atraviesa la forntera de un mes. Para convertir un año-semana a fecha, debe especificar el día de la semana:

    mysql> select str_to_date('200442 Monday', '%X%V %W');
    -> 2004-10-18
    

  • SUBTIME(expr,expr2)

    SUBTIME() resta expr2 de expr y retorna el resultado. expr es una expresión de hora o fecha/hora, y expr2 es una expresión de hora.

    mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
            -> '1997-12-30 22:58:58.999997'
    mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
            -> '-00:59:59.999999'
    

  • SYSDATE()

    SYSDATE() es sinónimo de NOW().

  • TIME(expr)

    Extrae la parte de hora de la expresión hora o fecha/hora expr.

    mysql> SELECT TIME('2003-12-31 01:02:03');
            -> '01:02:03'
    mysql> SELECT TIME('2003-12-31 01:02:03.000123');
            -> '01:02:03.000123'
    

  • TIMEDIFF(expr,expr2)

    TIMEDIFF() retorna el tiempo entre la hora de inicio expr y la hora final expr2. expr y expr2 son expresiones de hora o de fecha/hora, pero ambas deben ser del mismo tipo.

    mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
        ->                 '2000:01:01 00:00:00.000001');
            -> '-00:00:00.000001'
    mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
        ->                 '1997-12-30 01:01:01.000002');
            -> '46:58:57.999999'
    

  • TIMESTAMP(expr) , TIMESTAMP(expr,expr2)

    Con un único argumento, esta función retorna la expresión de fecha o fecha/hora expr como valor fecha/hora. Con dos argumentos, suma la expresión de hora expr2 a la expresión de fecha o de fecha/hora expr y retorna el resultado como valor fecha/hora.

    mysql> SELECT TIMESTAMP('2003-12-31');
            -> '2003-12-31 00:00:00'
    mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
            -> '2004-01-01 00:00:00'
    

  • TIMESTAMPADD(interval,int_expr,datetime_expr)

    Suma la expresión entera int_expr a la expresión de fecha o de fecha/hora datetime_expr. La unidad for int_expr la da el argumento interval , que debe ser uno de los siguientes valores: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, o YEAR.

    El valor interval puede especificarse usando una de las palabras claves que se muestran, o con un prefijo de SQL_TSI_. Por ejemplo, DAY o SQL_TSI_DAY son legales.

    mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
            -> '2003-01-02 00:01:00'
    mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
            -> '2003-01-09'
    

    TIMESTAMPADD() está disponible desde MySQL 5.0.0.

  • TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

    Retorna la diferencia entera entre las expresiones de fecha o de fecha/hora datetime_expr1 y datetime_expr2. La unidad del resultado se da en el argumento interval. Los valores legales para interval son los mismos que los listados en la descripción de la función TIMESTAMPADD() .

    mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
            -> 3
    mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
            -> -1
    

    TIMESTAMPDIFF() está disponible desde MySQL 5.0.0.

  • TIME_FORMAT(time,format)

    Se usa como la función DATE_FORMAT() pero la cadena format puede contener sólo los especificadores de formato que tratan horas, minutos y segundos. Otros especificadores producen un valor NULL o 0.

    Si el valor time contiene una parte horaria mayor que 23, los especificadores de formato horario %H y %k producen un valor mayor que el rango usual de 0..23. Los otros especificadores de hora producen la hora modulo 12.

    mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
            -> '100 100 04 04 4'
    

  • TIME_TO_SEC(time)

    Retorna el argumento time convertido en segundos.

    mysql> SELECT TIME_TO_SEC('22:23:00');
            -> 80580
    mysql> SELECT TIME_TO_SEC('00:39:38');
            -> 2378
    

  • TO_DAYS(date)

    Dada la fecha date, retorna un número de día (el número de dias desde el año 0).

    mysql> SELECT TO_DAYS(950501);
            -> 728779
    mysql> SELECT TO_DAYS('1997-10-07');
            -> 729669
    

    TO_DAYS() no está pensado para usarse con valores anteriores al calendario Gregoriano (1582), ya que no tiene en cuenta los días perdidos cuando se cambió el calendario. Consulte Sección 12.6, “Qué calendario utiliza MySQL”.

    Recuerde que MySQL convierte años de dos dígitos en fechas de cuatro dígitos usando las reglas en Sección 11.3, “Tipos de fecha y hora”. Por ejemplo, '1997-10-07' y '97-10-07' se consideran fechas idénticas:

    mysql> SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07');
            -> 729669, 729669
    

    Para fechas anteriores a 1582 (y posiblemente un año posterior en otras localizaciones), los resultados de esta función no son fiables. Consulte Sección 12.6, “Qué calendario utiliza MySQL” para más detalles.

  • UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

    Si se llama sin argumentos, retorna el timestamp de Unix (segundos desde '1970-01-01 00:00:00' GMT) como entero sin signo. Si se llama a UNIX_TIMESTAMP() con un argumento date , retorna el valor del argumento como segundos desde '1970-01-01 00:00:00' GMT. date puede ser una cadena DATE , una cadena DATETIME , un TIMESTAMP, o un número en el formato YYMMDD o YYYYMMDD en hora local.

    mysql> SELECT UNIX_TIMESTAMP();
            -> 882226357
    mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
            -> 875996580
    

    Cuando se usa UNIX_TIMESTAMP en una columna TIMESTAMP , la función retorna el valor del timestamp interno directamente, sin conversión implícita “string-to-Unix-timestamp” . Si pasa una fecha fuera de rango a UNIX_TIMESTAMP(), retorna 0, pero tenga en cuenta que sólo se hace un chequeo de rango básico (año de 1970 a 2037, mes de 01 a 12, día de 01 a 31).

    Si quiere restar columnas UNIX_TIMESTAMP() puede querer convertir el resultado a enteros sin signo. Consulte Sección 12.8, “Funciones y operadores de cast”.

  • UTC_DATE, UTC_DATE()

    Retorna la fecha UTC actual como valor en formato 'YYYY-MM-DD' o YYYYMMDD, dependiendo si la función se usa en un contexto numérico o de cadenas de caracteres.

    mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
            -> '2003-08-14', 20030814
    

  • UTC_TIME, UTC_TIME()

    Retorna la hora UTC actual como valor en formato 'HH:MM:SS' or HHMMSS dependiendo si la función se usa en un contexto numérico o de cadenas de caracteres.

    mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
            -> '18:07:53', 180753
    

  • UTC_TIMESTAMP, UTC_TIMESTAMP()

    Retorna la fecha y hora UTC actual como valor en formato 'YYYY-MM-DD HH:MM:SS' o YYYYMMDDHHMMSS dependiendo si la función se usa en un contexto numérico o de cadenas de caracteres.

    mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
            -> '2003-08-14 18:08:04', 20030814180804
    

  • WEEK(date[,mode])

    Esta función retorna el número de semana para date. La forma de dos argumentos de WEEK() le permite especificar si la semana comienza en lunes o domingo y si el valor de retorno debe estar en el rango de 0 a 53 o de 1 a 53. Si el argumento mode se omite en MySQL 5.0, el valor de la variable de sistema default_week_format se usa. Consulte Sección 5.3.3, “Variables de sistema del servidor”.

    La siguiente tabla describe cómo funciona el argumento mode :

     Primer día  
    Modode semanaRangoSemana 1 es la primera semana...
    0Domingo0-53con un domingo en este año
    1Lunes0-53con más de 3 días este año
    2Domingo1-53con un domingo este año
    3Lunes1-53con más de 3 días este año
    4Domingo0-53con más de 3 días este año
    5Lunes0-53con un lunes en este año
    6Domingo1-53con más de 3 días este año
    7Lunes1-53con un lunes en este año
    mysql> SELECT WEEK('1998-02-20');
            -> 7
    mysql> SELECT WEEK('1998-02-20',0);
            -> 7
    mysql> SELECT WEEK('1998-02-20',1);
            -> 8
    mysql> SELECT WEEK('1998-12-31',1);
            -> 53
    

    Tenga en cuenta que si una fecha cae en la última semana del año prévio, MySQL retorna 0 si no usa 2, 3, 6, o 7 con el argumento opcional mode :

    mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
            -> 2000, 0
    

    Se podría argumentar que MySQL debería retornar 52 para la función WEEK() , ya que la fecha dada ocurre en la 52a semana de 1999. Decidimos retornar 0 en su lugar porque queríamos que la función devolviera “el número de semana en el año dado.” Esta hace uso de la función WEEK() fiable combinada con otras funciones que extraen una parte de fecha de una fecha.

    Si prefiere que el resultado a ser evaluado respecto al año que contiene el primer día de la semana para la fecha dada, debe usar 0, 2, 5, o 7 como el argumento mode opcional.

    mysql> SELECT WEEK('2000-01-01',2);
            -> 52
    

    Alternativamente, use la función YEARWEEK():

    mysql> SELECT YEARWEEK('2000-01-01');
            -> 199952
    mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
            -> '52'
    

  • WEEKDAY(date)

    Retorna el índice de días de la semana para date (0 = lunes, 1 = martes, ... 6 = domingo).

    mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
            -> 1
    mysql> SELECT WEEKDAY('1997-11-05');
            -> 2
    

  • WEEKOFYEAR(date)

    Retorna la semana de la fecha como número del rango 1 a 53. Esta es una función de compatibilidad equivalente a WEEK(date,3).

    mysql> SELECT WEEKOFYEAR('1998-02-20');
            -> 8
    

  • YEAR(date)

    Retorna el año para date, en el rango 1000 a 9999.

    mysql> SELECT YEAR('98-02-03');
            -> 1998
    

  • YEARWEEK(date), YEARWEEK(date,start)

    Retorna año y semana para una fecha. El argumento start funciona exactamente como el argumento start de WEEK(). El año en el resultado puede ser diferente del año en el argumento fecha para la primera y última semana del año.

    mysql> SELECT YEARWEEK('1987-01-01');
            -> 198653
    

    Tenga en cuenta que el número de semana es diferente de lo que la función WEEK() retornaría (0) para argumentos opcionales 0 o 1, como WEEK() retorna la semana en el contexto del año dado.

12.6. Qué calendario utiliza MySQL

MySQL usa lo que se conoce como calendario Gregoriano proleptico.

Cada país que ha cambiado del calendario Juliano al Gregoriano ha tenido que descartar al menos diez días durante el cambio. Para ver cómo funciona, vamos a mirar el mes de Octubre de 1582, cuando se hizo el primer cambio Juliano-Gregoriano:

LunesMartesMiércolesJuevesViernesSábadoDomingo
1234151617
18192021222324
25262728293031

No hay fechas entre 4 y 15 de Octubre. Esta continuidad se llama el corte. Cualquier fecha antes del corte son Julianas, y cualquier fecha posterior es Gregoriana. Las fechas durante el corte no existen.

Un calendario aplicado a fechas cuando no estaban en uso se llama proleptico. Por lo tanto, si asuminos que nunca huvo un corte y las reglas Gregorianas funcionaron siempre, tenemos un calendario Gregoriano proleptico. Esto es lo que se usa en MySQL, y es requerido por el estándar SQL. Por esta razón, las fechas anteriores al corte almacenadas como valores MySQL DATE o DATETIME deben ajustarse para compensar la diferencia. Es importante tener en cuenta que el corte no ocurrió al mismo tiempo en todos los países, y que donde ocurrió más tarde se perdieron más días. Por ejemplo, en Gran Bretaña, tuvo lugar en 1752, cuando el miércoles 2 de Septiembre fue seguido por el jueves 14 de Septiembre; Rusia siguió con el calendario Juliano hasta 1918, perdiendo 13 días en el proceso, y la que se llama popularmente la "Revolución de Octubre" ocurrió en Noviembre según el calendario Gregoriano.

12.7. Funciones de búsqueda de texto completo (Full-Text)

  • MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

    MySQL soporta indexación y búsqueda full-text. Un índice full-text en MySQL es un índice de tipo FULLTEXT. Los índices FULLTEXT pueden usarse sólo con tablas MyISAM; pueden ser creados desde columnas CHAR, VARCHAR, o TEXT como parte de un comando CREATE TABLE o añadidos posteriormente usando ALTER TABLE o CREATE INDEX. Para conjuntos de datos grandos, es mucho más rápido cargar los datos en una tabla que no tenga índice FULLTEXT y crear el índice posteriormente, que cargar los datos en una tabla que tenga un índice FULLTEXT existente.

    Las restricciones en búsquedas full-text se listan en Sección 12.7.3, “Limitaciones de las búsquedas de texto completo (Full-Text)”.

Las búsquedas full-text se realizan con la función MATCH() .

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

La función MATCH() realiza una búsqueda de lenguaje natural para cadenas contra una colección de textos. Una colección es un conjunto de una o más columnas incluídas en un índice FULLTEXT . La cadena de búsqueda se da como argumento para AGAINST(). Para cada registro en la tabla MATCH() retorna un valor de relevancia, esto es, una medida de similaridad entre la cadena de búsqueda y el texto en el registro en las columnas mencionadas en la lista MATCH().

Por defecto, la búsqueda se realiza de forma insensible a mayúsculas. Sin embargo, puede realizar búsquedas sensibles a mayúsculas usando colaciones binarias para columnas indexadas. Por ejemplo, una columna que usa el conjunto de caracteres latin1 que puede asignarse una colación de latin1_bin para hacerla sensible a mayúsculas para búsquedas full-text .

Cuando se usa MATCH() en una cláusula WHERE , como en el ejemplo precedente, los registros retornados se ordenan automáticamente con la relevancia mayor primero. Los valores relevantes son números en coma flotante no negativos. Relevancia cero significa que no tiene similaridad. La relevancia se computa basada en el número de palabras en el registro, el número de palabras únicas en este registro, el número total de palabras en la colección, y el número de documentos (registros) que contienen una palabra particulas.

Para búsquedas full-text en lenguaje natural, se requiere que las columnas nombradas en la función MATCH() sean las mismas columnas incluídas en algún índice FULLTEXT en su tabla. Para la consulta precedente, tenga en cuenta que las columnas nombradas en la función MATCH() (title y body) son las mismas que las nombradas en la definición del índice FULLTEXT de la tabla article. Si quiere buscar el title o body separadamente, necesitará crear índices FULLTEXT para cada columna.

También es posible realizar una búsqueda boolena o una búsqueda con expansión de consulta. Estos tipos de búsqueda se describen en Sección 12.7.1, “Búsquedas booleanas de texto completo (Full-Text)” y Sección 12.7.2, “Búsquedas de texto completo (Full-Text) con expansión de consulta”.

El ejemplo precedente es una ilustración básica mostrando cómo usar la función MATCH() donde los registros se retornan para decrementar la relevancia. El siguiente ejemplo muestra cómo recibir los valores de relevancia explícitamente. Los registros retornados no se ordenan debido a que el comando SELECT no incluye cláusulas WHERE ni ORDER BY:

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
    -> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.65545833110809 |
|  2 |                                       0 |
|  3 |                        0.66266459226608 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

El siguiente ejemplo es más complejo. La consulta retorna los valores de relevancia y también ordena los registros en orden decrecente de relevancia. Para conseguir este resultado, debe especificar MATCH() dos veces: una vez en la lista SELECT y otra en la cláusula WHERE . Esto hace que no haya sobrecarga adicional, ya que el optimizador de MySQL se da cuenta que hay dos llamadas MATCH() son idénticas y invoca la búsqueda full-text sólo una vez.

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

La implementación MySQL de FULLTEXT trata cualquier secuencia de caracteres de palabras (letras, dígitos, y subrayados) como una palabra. Esa secuencia puede contener apóstrofes ('), poro no más que una en un registro. Esto significa que aaa'bbb se trata como una palabra, pero aaa''bbb se trata como dos palabras. Los apóstrofoes al principio o fin de una palabra se eliminan por el parser FULLTEXT ; 'aaa'bbb' se parsea como aaa'bbb.

El parser FULLTEXT determina dónde empiezan y acaban las palabras buscando algunos delimitadores, por ejemplo ' ' (el espacio), , (coma), y . (punto). Si las palabras no se separan por delmitadores como, por ejemplo, en chino, el parser FULLTEXT no puede determinar dónde empieza y acaba una palabra. Para ser capaz de añadir palabras o índices indexados en tales idioomas en un índice FULLTEXT, debe preprocesarlos para que se eliminen mediante algún delimitador arbitrario tal como ".

Algunas palabras se ignoran en las búsquedas full-text:

  • Cualquier palabra demasiado corta se ignora. La longitud mínima de las palabras que se encuentran en búsquedas full-text es de cuatro caracteres por defecto.

  • Las palabras en la lista de palabras de parada se ignoran. Una palabra de parada es una palabra tal como “el” o “algún” que es tan común que se considera que no tiene valor semántico. Hay una lista de palabras de parada, pero puede reescribirse con una lista de palabras definidas por el usuario. Consulte Sección 12.7.4, “Afinar búsquedas de texto completo (Full-Text) con MySQL”.

La longitud de palabra mínima y lista de palabras de parada puede cambiarse como se describe en Sección 12.7.4, “Afinar búsquedas de texto completo (Full-Text) con MySQL”.

Cada palabra correcta en la colección y en la consulta se pesa según su significado en la colección o consulta. Esta forma, una palabra que está presente en varios documentos tiene un peso menor ( y puede incluso tener peso 0), ya que tiene un valor semántico menor en esta colección particular. De modo similar, si la palabra es rara, recibe un peso mayor. Los pesos de las palabras se combinan para computar la relevancia del registro.

Una técnica de este tipo funciona mejor con colecciones grandes (de hecho, se ajustó con cuidado para funcionar de este modo). Para tablas muy pequeñas, la distribución de palabras no refleja automáticamente su valor semántico, y este modelo puede producir resultados extraños en ocasiones. Por ejemplo, aunque la palabra “MySQL” está presente en cada registro de la tabla articles, una búsqueda de esta palabra no da resultados.

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

El resultado de búsqueda es vacío porque la palabra “MySQL” está presente al menos en el 50% de los registros. Como tal, se trata efectivamente como una palabra de parada. Para conjuntos grandes, este es el comportamiento más deseable--una consulta en lenguaje natural no debe retornar cada segundo registro de una tabla de 1GB. Para conjuntos pequeños, puede ser menos deseable.

Una palabra que coíncide la mitad de registros en una tabla es menos deseable para localizar documentos relevantes. De hecho, es más fácil encontrar muchos documentos irrelevantes. Todos sabemos que esto pasa demasiado frecuentemente cuando tratamos de buscar algo en Internet con un motor de búsqueda. Es con este razonamiento que los registros que contienen la palabra se les asigna un valor semántico bajo para el conjunto particular en que ocurre. Una palabra dada puede exceder el límite del 50% en un conjunto pero no en otro.

El límite de 50% tiene una implicación significante cuando intenta una primera búsqueda full-text para ver cómo funciona: si crea una tabla e inserta sólo uno o dos registros de texto en ella, cada palabra en el texto aparece al menos en el 50% de los registros. Como resultado, no se retorna ningún resultado. Asegúrese de insertar al menos tres registros, y preferiblemente muchos más.

12.7.1. Búsquedas booleanas de texto completo (Full-Text)

MySQL puede realizar búsquedas full-text booleanas usando el modificador IN BOOLEAN MODE :

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

Esta consulta recibe todos los registros que contienen la palabra “MySQL” pero que no contiene la palabra “YourSQL”.

Búsquedas full-text booleanas tienen estas características:

  • No usa el límite del 50%.

  • No ordenan registros automáticamente para ordenarlos por relevancia decrecente. Puede verlo en la consulta precedente: El registro con la relevancia mayor es la que contiene “MySQL” dos veces, pero se lista la última, no la primera.

  • Pueden funcionar incluso sin un índice FULLTEXT, aunque una búsqueda ejecutada de esta forma sería bastante lenta.

  • La longitud mínima y máxima de palabra de parámetro de full-text se aplica.

  • Se aplica la lista de palabras de parada.

La capacidad de búsqued full-text booleana soporta los siguientes operadores:

  • +

    Un signo más indica que esta palabra debe estar presente en cada registro que se retorne.

  • -

    Un signo menos indica que esta palabra debe no estar presente en cualquiera de los registros que se retornan.

  • (sin operador)

    Por defecto (cuando ni + ni - se especifica) la palabra es opcional, pero los registros que la contienen se clasifican mejor. Esto mimetiza el comportamiento de MATCH() ... AGAINST() sin el modificador IN BOOLEAN MODE .

  • > <

    Estos dos operadores se usan para cambiar la contribución de la palabra al valor de relevancia que se asigna a un registro. El operador > incrementa la contribución y el operador < lo decrementa. Consulte el ejemplo a continuación.

  • ( )

    Los paréntesis se usan para agrupara palabras en subexpresiones. Los grupos entre paréntesis se pueden anidar.

  • ~

    Una tilde actúa como operador de negación, causando que la contribución de la palabra a la relevancia del registro sea negativa. Esto es útil para marcar palabras “ruidosas”. Un registro que contenga tales palabras se clasifica peor que otros, pero no se excluye, como se haría con el operador - .

  • *

    El asterisco sirve como operador de truncado. A diferencia de otros operadores, debe ser añadido a la palabra afectada.

  • "

    Una frase entre comillas dobles ('"') coincide sólo con registos que contienen la frase literalmente, como si se hubiera escrito. El motor de full-text divide la frase en palabras, realiza una búsqueda en el índice FULLTEXT de la palabra. Antes de MySQL 5.0.3, el motor realizaba una búsqueda de subcadenas para la frase en el registro en que se encontraban, de forma que la coincidencia debe incluir caracteres no imprimibles en la frase. Desde MySQL 5.0.3, los caracteres no imprimibles no necesitan coincidir exactamente: La búsqueda de frases requiere sólo que las coincidencias contengan exactamente las mismas palabras de la frase y en el mismo orden. Por ejemplo, "test phrase" coincide con "test, phrase" en MySQL 5.0.3, pero no anteriormente.

    Si la frase no contiene palabras que están en el índice, el resultado es vacío. Por ejemplo, si todas las palabras son palabras de parada o con longitud menor que la mínima para palabras indexadas, el resultado es vacío.

Los siguientes ejemplos demuestran algunas cadenas de búsqueda que usan operadores booleanos full-text:

  • 'apple banana'

    Encuentra registros que contengan al menos una de las dos palabras.

  • '+apple +juice'

    Encuentra registros que contengan ambas palabras.

  • '+apple macintosh'

    Encuentra registros que contengan la palabra “apple”, pero claficia mejor las que tambíen contengan “macintosh”.

  • '+apple -macintosh'

    Encuentra registros que contengan la palabra “apple” pero no “macintosh”.

  • '+apple +(>turnover <strudel)'

    Encuentra registros que contengan las palabras “apple” y “turnover”, o “apple” y “strudel” (en cualquier orden), pero clasifican “apple turnover” mejor que “apple strudel”.

  • 'apple*'

    Encuentra registros que contenga palabras tales como “apple”, “apples”, “applesauce”, o “applet”.

  • '"some words"'

    Encuentra registros que contienen la frase exacta “some words” (por ejemplo, registros que contengan “some words of wisdom” pero no “some noise words”). Tenga en cuenta que el carácter '"' que envuelve la frase son caracteres operadores que delimitan la frase. No son los delimitadores que rodean la cadena de búsqueda en sí.

12.7.2. Búsquedas de texto completo (Full-Text) con expansión de consulta

La búsqueda full-text en MySQL 5.0 soporta expansión de consultas( en particular, su variante “expansión de consultas ciega”). Generalmente esto es útil cuando una frase buscada es demasiado corta, lo que a menudo significa que el usuario se fía de conocimiento implícito que normalemente no tiene el motor de búsqueda full-text. Por ejemplo, uun usuario buscando “database” puede referirse a “MySQL”, “Oracle”, “DB2”, y “RDBMS” todas son frases que deberían coincidir con “databases” y deberían retornarse también. Este es conocimiento implícito.

Expansión de consultas ciega (también conocida como feedback de relevancia automático) se activa añadiendo WITH QUERY EXPANSION siguiendo la frase de búsqueda. Funciona realizando la búsqueda dos veces, donde la frase de búsqueda para la segunda búsqueda es la frase de búsqueda original concatenada con los primeros documentos encontrados en la primera búsqueda. Por lo tanto, si uno de estos documentos contiene la palabra “databases” y la palabra “MySQL”, la segunda búsqueda los documentos que contienen la palabra “MySQL” incluso si no contienen la palabra “database”. El siguiente ejemplo muestra esta diferencia:

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

Otro ejemplo puede ser buscar libros de Georges Simenon sobre Maigret, cuando un usuario no está seguro de cómo se escribe “Maigret”. Una búsqueda para “Megre and the reluctant witnesses” encuentra sólo “Maigret and the Reluctant Witnesses” sin expansión de consulta. Una búsqueda con expansión de consulta encuentra todos los libros con la palabra “Maigret” en la segunda pasada.

Nota: Debido a que la expansión de búsqueda ciega tiende a incrementar el ruido significativamente retornando documentos no relevantes, sólo tiene sentido cuando una frase de búsqueda es corta.

12.7.3. Limitaciones de las búsquedas de texto completo (Full-Text)

  • Las búsquedas full-text las soportan sólo las tablas MyISAM .

  • En MySQL 5.0, las búsquedas full-text pueden usarse con la mayoría de conjuntos de caracteres multi-byte. La excepción es para Unicode, el conjunto de caracteres utf8 puede usarse, pero no el conjunto ucs2 .

  • Idiomas ideográficos como Chino y Japonés no tienen delimitadores de palabras. Por lo tanto, el parser FULLTEXT no puede determinar dónde empiezan y acaban las palabras en este y otros idiomas . Las implicaciones y algunas soluciones del problema se describen en Sección 12.7, “Funciones de búsqueda de texto completo (Full-Text)”.

  • Mientras el uso de múltiples conjuntos de caracteres en una misma tabla se soporta, todas las columnas en un índice FULLTEXT deben usar el mismo conjunto de caracteres y colación.

  • La lista de columnas MATCH() debe coincidir exactamente con la lista de columnas en algún índice FULLTEXT definido en la tabla, a no ser que MATCH() estén IN BOOLEAN MODE.

  • El argumento de AGAINST() debe ser una cadena constante.

12.7.4. Afinar búsquedas de texto completo (Full-Text) con MySQL

La capacidad de búsqueda full-text de MySQL tiene algunos parámetros ajustables por el usuario, aunque añadir más está en la lista de temas pendientes. Puede tener un mayor control en el comportamiento de las búsquedas full-text si tiene una distribución fuente de MySQL ya que algunos cambios requieren modificaciones del código. Consulte Sección 2.8, “Instalación de MySQL usando una distribución de código fuente”.

Tenga en cuenta que la búsqueda full-text está ajustada cuidadosamente para una mayor eficiencia. El modificar el comportamiento por defecto puede decrementarla en la mayoría de los casos. No cambie el código MySQL a no ser que sepa lo que hace.

La mayoría de variables full-text que se describen a continuación deben cambiarse en tiempo de arranque del servidor. Se necesita reiniciar el servidor para cambiarlas; no pueden modificarse mientras el servidor está en ejecución.

Algunos cambios de variables requieren que rehaga los índices FULLTEXT en sus tablas. Las instrucciones para hacerlo se dan al final de esta sección.

  • La longitud mínima y máxima de las palabras a indexar se definen con las variables de sistema ft_min_word_len y ft_max_word_len . (Consulte Sección 5.3.3, “Variables de sistema del servidor”.) El valor por defecto mínimo es de cuatro caracteres; el valor máximo por defecto depende de la versión de MySQL que use. Si cambia algún valor, debe rehacer los índices FULLTEXT. Por ejemplo, si quiere que se puedan buscar palabras de tres caracteres, puede cambiar la variable ft_min_word_len poniendo las siguientes líneas en un fichero de opciones:

    [mysqld]
    ft_min_word_len=3
    

    A continuación reinicie el servidor y rehaga los índices FULLTEXT. Tenga en cuenta las particularidades de myisamchk en las instrucciones a continuación.

  • Para sobreescribir la lista de palabras de parada por defecto, cambie la variable de sistema ft_stopword_file. (Consulte Sección 5.3.3, “Variables de sistema del servidor”.) El valor de la variable debe ser la ruta del fichero que contiene la lista de palabras de parada, o la cadena vacía para desactivar el filtro de palabras de parada. Tras cambiar el valor de esta variable o los contenidos del fichero de palabras de parada, rehaga los índices FULLTEXT .

    La lista de palabras de parada es libre, esto es, puede usar cualquier carácter no alfanumérico como el de nueva línea, espacio, o coma para separar las palabras de parada. Las excepción son el subrayado (_) y el apóstrofe sencillo (') que se tratan como parte de una palabra. El conjunto de caracteres de la lista de palabras de parada es el conjunto de caracteres por defecto del servidor; consulte Sección 10.3.1, “Conjunto de caracteres y colación del servidor”.

  • El límite del 50% para búsquedas de lenguaje natural está determinada por el esquema de pesos elegido. Para desactivarlo, consulte la siguiente línea en myisam/ftdefs.h:

    #define GWS_IN_USE GWS_PROB
    

    Cambie esta línea a:

    #define GWS_IN_USE GWS_FREQ
    

    A continuación recompile MySQL. No hay necesidad de rehacer los índices en este caso. Nota: Al hacer esto se decrementa severamente la habilidad de MySQL para proporcionar valores apropiados de relevancia para la función MATCH(). Si realmente necesita buscar para estas palabras comunes, es mejor buscar usando IN BOOLEAN MODE en su lugar, que no observa el límite del 50%.

  • Para cambiar los operadores usados para búsquedas booleanas full-text, cambie la variable de sistema ft_boolean_syntax. Esta variable también puede cambiarse mientras el servidor está en ejecución, pero debe tener el privilegio SUPER para ello. No es necesario rehacer los índices. Variables de sistema del servidor describe las reglas que gobiernan cómo cambiar esta variable.

Si modifica variables full-text que afectan el indexado (ft_min_word_len, ft_max_word_len, ft_stopword_file), o si cambia el fichero de palabras de parada mismo, debe reconstruir los índices FULLTEXT tras hacer los cambios y reiniciar el servidor. Para rehacer los índices en este caso, es suficiente hacer una operación de reparación QUICK :

mysql> REPAIR TABLE tbl_name QUICK;

Tenga en cuenta que si usa myisamchk para realizar una operación que modifica los índices de tablas (tales como reparar o analizar), los índices FULLTEXT se reconstruyen usando los valores por defecto full-text para longitud de palabras mínima y máxima y el fichero de palabras de parada a no ser que especifique otro. Esto puede hacer que las consultas fallen.

El problema ocurre porque estos parametros sólo son conocidos por el servidor. No se almacenan en ficheros índices MyISAM. Para evitar este problema si ha modificado la longitud mínima o máxima de palabra o el fichero de palabras de parada en el servidor, especifique los mismos valores de ft_min_word_len, ft_max_word_len, y ft_stopword_file a myisamchk que usa para mysqld. Por ejemplo, si ha puesto que la longitud de palabra mínima a 3, puede reparar una tabla con un myisamchk como este:

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

Para asegurar que myisamchk y el servidor usan los mismos valores para parámetros full-text, puede poner cada uno en las secciones [mysqld] y [myisamchk] de un fichero de opciones:

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

Una alternativa a usar myisamchk es usar REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, o ALTER TABLE. Estos comandos los realiza el servidor, que conoce los valores apropiados de los parámetros full-text a usa.

12.7.5. Cosas por hacer en búsquedas de texto completo (Full-Text)

  • Rendimiento mejorado para todas las operaciones FULLTEXT.

  • Operadores de proximidad.

  • Soporte de “always-index words.” Pueden ser cualquier cadena de caracteres que quiera el usuario para tratarlas como palabras, tales como “C++”, “AS/400”, o “TCP/IP”.

  • Soporte para búsqueda full-text en tablas MERGE.

  • Soporte para el conjunto de caracteres ucs2 .

  • Hace que la lista de palabras de parada dependente del idioma del conjunto de datos.

  • Stemming

  • Pre-parser genérico proporcionado por el usuario UDF .

  • Hace el modelo más flexible (añadiendo algunos parámetros ajustables a FULLTEXT en los comandos CREATE TABLE y ALTER TABLE).

12.8. Funciones y operadores de cast

  • BINARY

    El operador BINARY convierte la cadena a continuación a una cadena binaria. Esta es una forma fácil de forzar una comparación de columna byte a byte en lugar de carácter a carácter. Esto hace que la comparación sea sensible a mayúsculas incluso si no está definida como BINARY o BLOB. BINARY también hace que los espacios finales sean significativos.

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT BINARY 'a' = 'A';
            -> 0
    mysql> SELECT 'a' = 'a ';
            -> 1
    mysql> SELECT BINARY 'a' = 'a ';
            -> 0
    

    BINARY afecta la comparación; puede darse antes de cualquier operando con el mismo resultado.

    BINARY str es una abreviación de CAST(str AS BINARY).

    Tenga en cuenta que en algunos contextos, si cambia el tipo de una columna BINARY indexada, MySQL no es capaz de usar el índice eficientemente.

    Si quiere comparar un valor BLOB u otra cadena binaria de forma no sensible a mayúsculas, puede hacerlo teniendo en cuenta que las cadenas binarias no tienen conjunto de caracteres, y por lo tanto no tienen concepto de mayúsculas. Para una comparación no sensible a mayúsculas, use la función CONVERT() para convertir el valor de cadena a un conjunto de caracteres no sensible a mayúsculas. El resultado es una cadena no binaria. El resultado es una cadena no binaria, así que la operación LIKE no es sensible a mayúsculas:

    SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name;
    

    Para usar un conjunto de caracteres diferente, substituya su nombre por latin1 en el comando precedente.

    CONVERT() puede usarse más generalmente para comparar cadenas que se representan en distintos conjuntos de caracteres.

  • CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)

    Las funciones CAST() y CONVERT() pueden usarse para tomar un valor de un tipo y producir un valor de otro tipo.

    type puede ser uno de los siguientes valores:

    • BINARY

    • CHAR

    • DATE

    • DATETIME

    • DECIMAL

    • SIGNED [INTEGER]

    • TIME

    • UNSIGNED [INTEGER]

    BINARY produce una cadena binaria. Consulte la entrada para el operador BINARY en esta sección para una descripción de cómo afecta esto a las comparaciones.

    El tipo DECIMAL está disponible desde MySQL 5.0.8.

    CAST() y CONVERT(... USING ...) son sintaxis SQL estándar. La forma no-USING de CONVERT() es sintaxis ODBC .

    CONVERT() con USING se usa para convertir datos entre distintos conjuntos de caracteres. En MySQL, los nombres transcodificados son los mismos que los nombres de los conjuntos de caracteres correspondientes. Por ejemplo, este comando convierte la cadena 'abc' en el conjunto de caracteres por defecto del servidor a la cadena correspondiente en el conjunto de caracteres utf8 :

    SELECT CONVERT('abc' USING utf8);
    

Las funciones de conversión son útiles cuando quiere crear una columna con un tipo específico en un comando CREATE ... SELECT:

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

Las funciones también pueden ser útiles para ordenar columnas ENUM en orden léxico. Normalmente ordenar columnas ENUM se hace usando el valor numérico interno. Convertir los valores en CHAR resulta en orden léxico:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY) es lo mismo que BINARY str. CAST(expr AS CHAR) trata la expresión como una cadena con el conjunto de caracteres por defecto.

CAST() cambia el resultaod si lo usa como parte de una expresión más compleja como CONCAT('Date: ',CAST(NOW() AS DATE)).

No debe usar CAST() para extraer datos en datos en distintos formatos sino usar funciones de cadenas como LEFT() or EXTRACT(). Consulte Sección 12.5, “Funciones de fecha y hora”.

Para convertir una cadena en un valor numérico en un contexto numérico, normalmente no tiene que hacer nada más que usar el valor de la cadena como si fuera un número:

mysql> SELECT 1+'1';
       -> 2

Si usa un número en un contexto de cadenas, el número se convierte automáticamente en una cadena BINARY string.

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

MySQL soporta aritmética con valores con y sin signo de 64-bit. Si usa operadores numéricos (tales como +) y uno de los operandos es un entero sin signo, el resultado no tiene signo. Puede cambiar este comportamiento usando los operadores SIGNED y UNSIGNED para cambiar la operación a un entero con o sin signo de 64-bit , respectivamente.

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

Tenga en cuenta que si un operando es un valor de coma flotante, el resultado es de coma flotante y no está afectado por la regla precedente. (En este contexto, los valores de la columna DECIMAL se tratan como valores con punto flotante.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

Si usa una cadena en una operación aritmética, se convierte en número en coma flotante.

12.9. Otras funciones

12.9.1. Funciones bit

MySQL usa aritmética BIGINT (64-bit) para operaciones de bit, así que estos operadores tienen un rango máximo de 64 bits.

  • |

    OR bit a bit:

    mysql> SELECT 29 | 15;
            -> 31
    

    El resultado es un entero de 64 bits.

  • &

    AND bit a bit:

    mysql> SELECT 29 & 15;
            -> 13
    

    El resultado es un entero de 64 bits.

  • ^

    XOR bit a bit:

    mysql> SELECT 1 ^ 1;
            -> 0
    mysql> SELECT 1 ^ 0;
            -> 1
    mysql> SELECT 11 ^ 3;
            -> 8
    

    El resultado es un entero de 64 bits.

    Desplaza un número largo (BIGINT) a la izquierda.

  • <<

    mysql> SELECT 1 << 2;
            -> 4
    

    El resultado es un entero de 64 bits.

  • >>

    Desplaza un número largo (BIGINT) a la derecha.

    mysql> SELECT 4 >> 2;
            -> 1
    

    El resultado es un entero de 64 bits.

  • ~

    Invierte todos los bits.

    mysql> SELECT 5 & ~1;
            -> 4
    

    El resultado es un entero de 64 bits.

  • BIT_COUNT(N)

    Retorna el número de bits en el argumento N.

    mysql> SELECT BIT_COUNT(29);
            -> 4
    

12.9.2. Funciones de encriptación

Las funciones en esta sección encriptan y desencriptan valores. Si quiere almacenar resultados de una función de encriptación que puede contaner valores arbitrarios de bytes, use una columna BLOB en lugar de CHAR o VARCHAR para evitar problemas potenciales con eliminación de espacios finales que pueden cambiar los valores de datos.

  • AES_ENCRYPT(str,key_str) , AES_DECRYPT(crypt_str,key_str)

    Estas funciones permiten encriptación y desencriptación de datos usando el algoritmo oficial AES (Advanced Encryption Standard), conocido anteriormente como "Rijndael." Se usa una encriptación con una clave de 128-bit , pero puede ampliarlo hasta 256 bits modificando las fuentes. Elegimos 128 porque es mucho más rápido y de momento es suficientemente seguro.

    Los argumentos de entrada pueden ser de cualquier longitud. Si algún argumento es NULL, el resultado de esta función también es NULL.

    Debido a que AES es un algoritmo a nivel de bloques, se usa relleno para cadenas de longitud impar y así la longitud de la cadena resultante puede calcularse como 16 * (trunc(string_length / 16) + 1).

    Si AES_DECRYPT() detecata datos inválidos o relleno incorrecto, retorna NULL. Sin embargo, es posible para AES_DECRYPT() retornar un valor no NULL (posiblemente basura) si los datos de entrada o la clave son inválidos.

    Puede usar la función AES para almacenar datos de forma encriptada modificando sus consultas:

    INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
    

    Puede obtener incluso mejor seguridad si no transfiere la clave a través de la conexión para cada consulta, que puede hacerse almacenando la clave en una variable del servidor al hacer la conexión. Por ejemplo:

    SELECT @password:='my password';
    INSERT INTO t VALUES (1,AES_ENCRYPT('text',@password));
    

    AES_ENCRYPT() y AES_DECRYPT() pueden considerarse las funciones de encriptación criptográficamente más seguras disponibles en MySQL.

  • DECODE(crypt_str,pass_str)

    Desencripta la cadena encriptada crypt_str usando pass_str como contraseña. crypt_str debe ser una cadena retornada de ENCODE().

  • ENCODE(str,pass_str)

    Encripta str usando pass_str como contraseña. Para desencriptar el resultado, use DECODE().

    El resultado es una cadena binaria de la misma longitud que str. Si quiere guardarlo en una columna, use una columna de tipo BLOB .

  • DES_DECRYPT(crypt_str[,key_str])

    Desencripta una cadena encriptada con DES_ENCRYPT(). En caso de error, esta función retorna NULL.

    Tenga en cuenta que esta función funciona sólo si MySQL se configura con soporte SSL. Consulte Sección 5.7.7, “Usar conexiones seguras”.

    Si no se da argumento key_str, DES_DECRYPT() examina el primer byte de la cadena encriptada para determinar el número de clave DES que se usó para encriptar la cadena original, y luego lee la clave del fichero clave DES para desencriptar el mensaje. Para que esto funcione, el usuario debe tener el privilegio SUPER. El fichero clave puede especificarse con la opción del servidor --des-key-file .

    Si le pasa a esta función el argumento key_str , esta cadena se usa como la clave para desencriptar el mensaje.

    Si el argumento crypt_str no parece una cadena encriptada, MySQL retorna crypt_str.

  • DES_ENCRYPT(str[,(key_num|key_str)])

    Encriptas la cadena con la clave dada usando el algoritmo triple-DES. En caso de error, retorna NULL.

    Tenga en cuenta que esta función funciona sólo si MySQL se configura con soporte SSL. Consulte Sección 5.7.7, “Usar conexiones seguras”.

    La clave de encriptación a usar se elige basada en el segundo argumento de DES_ENCRYPT(), si se ha dado uno:

    ArgumentoDescripción
    Sin argumentoSe usa la primera clave del fichero clave DES.
    key_numEl número de clave dado (0-9) del fichero clave DES se usa.
    key_strSe usa la cadena clave dada para encriptar str.

    El fichero clave puede especificarse con la opción de servidor --des-key-file .

    La cadena retornada es una cadena binaria donde el primer carácter es CHAR(128 | key_num).

    Se añade 128 para hacer más sencillo reconocer una clave encriptada. Si usa una cadena clave, key_num es 127.

    La longitud de la cadena para el resultado es new_len = orig_len + (8-(orig_len % 8))+1.

    Cada línea en el fichero clave DES tiene el siguiente formato:

    key_num des_key_str
    

    Cada key_num debe ser un número en el rango de 0 a 9. Las líneas en el fichero pueden estar en cualquier orden. des_key_str es la cadena que se usa para encriptar el mensaje. Entre el número y la clave debe haber un espacio como mínimo. La primera clave es la clave usada por defecto si no especifica ningún argumento clave para DES_ENCRYPT()

    Puede decir a MySQL que lea un nuevo valor de clave del fichero clave con el comando FLUSH DES_KEY_FILE. Esto necesita el privilegio RELOAD .

    Un beneficio de tener un conjunto de claves por defecto es que da a las aplicaciones una forma de chequear la existencia de valores de columna encriptados, sin dar al usuario final el derecho de desencritparlos.

    mysql> SELECT customer_address FROM customer_table 
         > WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
    

  • ENCRYPT(str[,salt])

    Encripta str usando la llamada de sistema Unix crypt() . El argumento salt debe ser una cadena con al menos dos caracteres. Si no se da argumento salt , se usa un valor aleatorio.

    mysql> SELECT ENCRYPT('hello');
            -> 'VxuFAJXVARROc'
    

    ENCRYPT() ignora todo excepto los primeros ochos caracteres de str, al menos en algunos sistemas. Este comportamiento viene determinado por la implementación de la llamada de sistema crypt() subyacente.

    Si crypt() no está disponible en su sistema (como pasa en Windows), ENCRYPT() siempre retorna NULL. Debido a esto, recomandamos que use MD5() o SHA1() en su lugar, y que estas dos funciones existen en todas las plataformas.

  • MD5(str)

    Calcula una checksum MD5 de 128-bit para la cadena. El valor se retorna como una cadena binaria de dígitos 32 hex ,o NULL si el argumento era NULL. El valor de retorno puede usarse como clave hash, por ejemplo.

    mysql> SELECT MD5('testing');
            -> 'ae2b1fca515949e5d54fb22b8ed95575'
    

    Este es el "RSA Data Security, Inc. MD5 Message-Digest Algorithm."

    Si quiere convertir el valor a mayúsculas, consulte la descripción de conversiones de cadenas binarias dada en la entrada del operador BINARY en Sección 12.8, “Funciones y operadores de cast”.

  • OLD_PASSWORD(str)

    OLD_PASSWORD() se añadió en MySQL 4.1,cuando se cambión la implementación de PASSWORD() para mejorar la seguridad. OLD_PASSWORD() retorna el valor de la implementación pre-4.1 de PASSWORD(), y está hecha para permitirle resetear contraseñas para cualquier cliente pre-4.1 que necesite conectar a su versión 4.1 o posterior de MySQL server sin bloquearlo. Consulte Sección 5.6.9, “Hashing de contraseñas en MySQL 4.1”.

  • PASSWORD(str)

    Calcula y retorna una cadena de contraseña de la contraseña en texto plano str, o NULL si el argumento era NULL. Esta es la función que se usa para encriptar contraseñas MySQL para almacenar en la columna Password de la tabla user .

    mysql> SELECT PASSWORD('badpwd');
            -> '7f84554057dd964b'
    

    La encriptación de PASSWORD() es de un sentido (no reversible).

    PASSWORD() no realiza encriptación de contraseña de la misma forma que se encriptan las contraseñas Unix. Consulte ENCRYPT().

    Nota: La función PASSWORD() se usa por el sistema de autenticación en MySQL Server; no no debe usarlo en su propias aplicaciones. Para ese propósito, use MD5() o SHA1() en su lugar. Consulte RFC 2195 para más información acerca de tratar contraseñas y autenticación de forma segura en su aplicación.

  • SHA1(str), SHA(str)

    Calcula una checksum SHA1 de 160-bit para la cadena, como se describe en RFC 3174 (Secure Hash Algorithm). El valor se retorna como cadnea de 40 dítigos hexadecimales, o NULL si el argumento era NULL. Uno de los usos posibles para esta función es una clave hash. También puede usarlo como función criptográficamente segura para almacenar contraseñas

    mysql> SELECT SHA1('abc');
            -> 'a9993e364706816aba3e25717850c26c9cd0d89d'
    

    SHA1() puede considerarse un equivalente criptográficamente más seguro que MD5(). SHA() es sinónimo de SHA1().

12.9.3. Funciones de información

  • BENCHMARK(count,expr)

    La función BENCHMARK() ejecuta la expresión expr repetidamente count veces. Puede usarse para ver lo rápido que MySQL procesa la expresión. El valor resultado siempre es 0. El uso pretendido es desde dentro del cliente mysql, que reporte tiempos de ejecución de consultas:

    mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
    +----------------------------------------------+
    | BENCHMARK(1000000,ENCODE('hello','goodbye')) |
    +----------------------------------------------+
    |                                            0 |
    +----------------------------------------------+
    1 row in set (4.74 sec)
    

    El tiempo reportado es el tiempo transcurrido en el cliente final no el tiempo de CPU en el servidor. Se recomienda ejecutar BENCHMARK() varias veces, y interpretar el resultado teniendo en cuenta la carga de la máquina servidor.

  • CHARSET(str)

    Retorna el conjunto de caracteres el argumento cadena.

    mysql> SELECT CHARSET('abc');
            -> 'latin1'
    mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
            -> 'utf8'
    mysql> SELECT CHARSET(USER());
            -> 'utf8'
    

  • COERCIBILITY(str)

    Retorna la coerzabilidad de la colación del argumento.

    mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
            -> 0
    mysql> SELECT COERCIBILITY(USER());
            -> 3
    mysql> SELECT COERCIBILITY('abc');
            -> 4
    

    El valor de retorno tiene los siguientes significados:

    CoerzabilidadSignificadoEjemplo
    0Colación explícitaValor con la cláusula COLLATE
    1Sin colaciónConcatenación de cadenas con distintas colaciones
    2Colación implícitaValor de columna
    3Constante de sistemaValor de retorno USER()
    4CoercibleCadena literal
    5IgnorableNULL o una expresión derivada de NULL

    Antes de MySQL 5.0.3, los valores de retorno se muestran como sigue, y las funciones tales como USER() tienen una coerzabilidad de 2:

    CoerzabilidadSignificadoEjemplo
    0Colación explícitaValor con la cláusula COLLATE
    1Sin colaciónConcatenación de cadenas con distintas colaciones
    2Colación implícitaValor de columna
    3CoercibleCadena literal

    Los valores menores tienen precedencia mayor.

  • COLLATION(str)

    Retorna la colación para el conjunto de caracteres de la cadena dada.

    mysql> SELECT COLLATION('abc');
            -> 'latin1_swedish_ci'
    mysql> SELECT COLLATION(_utf8'abc');
            -> 'utf8_general_ci'
    

  • CONNECTION_ID()

    Retorna el ID de la conexión (ID del thread) para la conexión. Cada conexión tiene su propio y único ID.

    mysql> SELECT CONNECTION_ID();
            -> 23786
    

  • CURRENT_USER()

    Retorna la combinación de nombre de usuario y de equipo que tiene la sesión actual. Este valor se corresponde con la cuenta MySQL que determina sus privilegios de aceso. Puede ser distinto al valor de USER().

    mysql> SELECT USER();
            -> 'davida@localhost'
    mysql> SELECT * FROM mysql.user;
    ERROR 1044: Access denied for user ''@'localhost' to
    database 'mysql'
    mysql> SELECT CURRENT_USER();
            -> '@localhost'
    

    El ejemplo ilustra que aunque el cliente especica un nombre de usuario de davida (como se indica por el valor de la función USER() ), el sevidor autentica al cliente usando una cuenta de usuario anónimo (como se ve por la parte de usuario vacía del valor CURRENT_USER() ). Una forma en que esto puede ocurrir es que no haya cuenta listada en las cuentas de permisos para davida.

    En MySQL 5.0, la cadena retornada por CURRENT_USER() usa el conjunto de caracteres utf8 .

  • DATABASE()

    Retorna el nombre de base de datos por defecto (actual). En MySQL 5.0, la cadena tiene el conjunto de caracteres utf8 .

    mysql> SELECT DATABASE();
            -> 'test'
    

    No hay base de datos por defecto, DATABASE() retorna NULL.

  • FOUND_ROWS()

    Un comando SELECT puede incluir una cláusula LIMIT para restringir el número de registros que el servidor retorna al cliente. En algunos casos, es deseable saber cuántos registos habría retornado el comando sin LIMIT, pero sin volver a lanzar el comando. Para obtener este conteo de registros, incluya la opción SQL_CALC_FOUND_ROWS en el comando SELECT , luego invoque FOUND_ROWS() :

    mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
        -> WHERE id > 100 LIMIT 10;
    mysql> SELECT FOUND_ROWS();
    

    El segundo SELECT retorna un número indicando cuantos registros habría retornado el primer SELECT sin la cláusula LIMIT . (Si el comando precendente no incluye la opción SQL_CALC_FOUND_ROWS , FOUND_ROWS() puede retornar un resultado distinto cuando se usa LIMIT y cuando no.)

    Tenga en cuenta que si usa SELECT SQL_CALC_FOUND_ROWS, MySQL debe calcular cuántos registros hay en el conjunto de resultdos completo. Sin embargo, esto es más rápido que ejecutar la consulta de nuevo sin LIMIT, ya que el conjunto de resultados no necesita ser enviado al cliente.

    SQL_CALC_FOUND_ROWS y FOUND_ROWS() pueden ser útiles en situaciones donde puede querer restringir el número de registros que retorna una consulta, pero también determinar el número de registros en el conjunto de resultados entero sin ejecutar la consulta de nuevo. Un ejemplo es el script Web que presenta una salida paginada conteniendo enlaces a las páginas que muestran otras secciones de un resultado de búsqueda. Usando FOUND_ROWS() puede determinar cuántas páginas necesita para el resto de resultados.

    El uso deSQL_CALC_FOUND_ROWS y FOUND_ROWS() es más complejo para consultas UNION que para comandos SELECT simples, ya que LIMIT puede ocurrir en varios lugares en una UNION. Puede aplicarse a comandos SELECT individuales en la UNION, o global en el resultado de UNION como conjunto.

    La intención de SQL_CALC_FOUND_ROWS para UNION es que debe retornar el número de registros que se retornarían sin un LIMIT globar. Las condiciones para uso de SQL_CALC_FOUND_ROWS con UNION son:

    • La palabra clave SQL_CALC_FOUND_ROWS debe aparecer en el primer SELECT de la UNION.

    • El valor de FOUND_ROWS() es exacto sólo si se usa UNION ALL . Si se usa UNION sin ALL, se eliminan duplicados y el valor de FOUND_ROWS() es sólo aproximado.

    • Si no hay LIMIT en UNION, se ignora SQL_CALC_FOUND_ROWS y retorna el número de registros en la tabla temporal que se crea para procesar UNION.

  • LAST_INSERT_ID(), LAST_INSERT_ID(expr)

    Retorna el último valor generado automáticamente que se insertó en una columna AUTO_INCREMENT.

    mysql> SELECT LAST_INSERT_ID();
            -> 195
    

    El último ID generado se mantiene en el servidor para cada conexión. Esto significa que el valor de la función retorna a cada cliente el valor AUTO_INCREMENT más reciente generado por ese cliente. Este valor no puede ser afectado por otros clientes, incluso si generan valores AUTO_INCREMENT ellos mismos. Este comportamiento asegura que reciba sus propios IDs sin tener en cuenta la actividad de otros clientes y sin la necesidad de bloqueos o transacciones.

    El valor de LAST_INSERT_ID() no cambia si actualiza la columna AUTO_INCREMENT de un registro con un valor no mágico (esto es, un valor que no es NULL ni 0).

    Si inserta varios registros a la vez con un comando de inserción LAST_INSERT_ID() retorna el valor del primer registro insertado. La razón para esto es hacer posible reproducir fácilmente el mismo comando INSERT contra otro servidor.

    Si usa INSERT IGNORE y el registro se ignora, el contador AUTO_INCREMENT no se incrementa y LAST_INSERT_ID() retorna 0, lo que refleja que no se ha insertado ningún registro. (Antes de MySQL 4.1, AUTO_INCREMENT el contador se incrementa y LAST_INSERT_ID() retorna el nuevo valor.)

    Si se da expr como argumento para LAST_INSERT_ID(), el valor del argumento se retorna por la función y se recuerda como el siguiente valor a ser retornado por LAST_INSERT_ID(). Esto puede usarse para simular secuencias:

    • Cree una tabla para guardar el contador de secuencia y inicializarlo:

      mysql> CREATE TABLE sequence (id INT NOT NULL);
      mysql> INSERT INTO sequence VALUES (0);
      
    • Use la tabla para generar números de secuncia como aquí:

      mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
      mysql> SELECT LAST_INSERT_ID();
      

      El comando UPDATE incrementa el contador de secuencia y causa que la siguiente llamada a LAST_INSERT_ID() retorne el valor actualizado. El comando SELECT recibe ese valor. La función de la API C mysql_insert_id() puede usarse para obtener el valor . Consulte Sección 24.3.3.34, “mysql_insert_id().

    Puede generar secuencias sin llamar a LAST_INSERT_ID(), pero la utilidad de usar esta función de esta forma es que el valor ID se mantiene en el servidor como el último valor generado automáticamente. Es válido para multi usuarios porque varios clientes pueden realizar el comando UPDATE y obtener su propio valor de secuencia con el comando SELECT (o mysql_insert_id()), sin afectar o ser afectado por otros clientes que generen sus propios valores de secuencia.

    Tenga en cuenta que mysql_insert_id() sólo se actualiza tras los comandos INSERT y UPDATE , así que no puede usar la función de la API C para recibir el valor de LAST_INSERT_ID(expr) tras ejecutar otros comandos SQL como SELECT o SET.

  • ROW_COUNT()

    ROW_COUNT() retorna el número de registros actualizados, insertados o borrados por el comando precedente. Esto es lo mismo que el número de registros que muestra el cliente mysql y el valor de la función de la API C mysql_affected_rows() .

    mysql> INSERT INTO t VALUES(1),(2),(3);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> SELECT ROW_COUNT();
    +-------------+
    | ROW_COUNT() |
    +-------------+
    |           3 |
    +-------------+
    1 row in set (0.00 sec)
    
    mysql> DELETE FROM t WHERE i IN(1,2);
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> SELECT ROW_COUNT();
    +-------------+
    | ROW_COUNT() |
    +-------------+
    |           2 |
    +-------------+
    1 row in set (0.00 sec)
    

    ROW_COUNT() se añadió en MySQL 5.0.1.

  • SESSION_USER()

    SESSION_USER() es sinónimo de USER().

  • SYSTEM_USER()

    SYSTEM_USER() es sinónimo de USER().

  • USER()

    Retorna el nombre de usuario y de equipo de MySQL actual.

    mysql> SELECT USER();
            -> 'davida@localhost'
    

    El valor indica el nombre de usuario especificado al conectar con el servidor, y el equipo cliente desde el que se está conectando. El valor puede ser distinto del de CURRENT_USER().

    Puede extraer la parte de nombre de usuario así:

    mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
            -> 'davida'
    

    En MySQL 5.0, USER() retorna un valor en el conjunto de caracteres utf8, así que debe asegurarse que la literal '@' se interpreta en el conjunto de caracteres:

    mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
            -> 'davida'
    

  • VERSION()

    Retorna una cadena que indica la versión del servidor MySQL. La cadena usa el conjunto de caracteres utf8 .

    mysql> SELECT VERSION();
            -> '5.0.9-standard'
    

    Tenga en cuenta que si su cadena de versión acaba con -log significa que el logueo está activado.

12.9.4. Funciones varias

  • DEFAULT(col_name)

    Retorna el valor por defecto para una columna de tabla. A partir de MySQL 5.0.2, retorna un error si la columna no tiene valor por defecto.

    mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
    

  • FORMAT(X,D)

    Formatea el número X a un formato como '#,###,###.##', redondeado a D decimales, y retorna el resultado como una cadena. Si D es 0, el resultado no tiene punto decimar o parte fraccional.

    mysql> SELECT FORMAT(12332.123456, 4);
            -> '12,332.1235'
    mysql> SELECT FORMAT(12332.1,4);
            -> '12,332.1000'
    mysql> SELECT FORMAT(12332.2,0);
            -> '12,332'
    

  • GET_LOCK(str,timeout)

    Intenta obtener un bloqueo con un nombre dado por la cadena str, con un tiempo máximo de timeout segundos. Retorna 1 si el bloqueo se obtiene con éxito, 0 si el intento supera el tiempo máximo (por ejemplo, debido a que otro cliente haya bloqueado el nombre préviamente), o NULL si ocurre un error (tal como quedarse sin memoria o que el flujo acabe con mysqladmin kill). Si tiene un bloqueo obtenido con GET_LOCK(), se libera cuando ejecuta RELEASE_LOCK(), ejecuta un nuevo GET_LOCK(), o su conexión termina (normal o anormalmente).

    Esta función puede usarse para implementar bloqueos de aplicaciones o simular bloqueo de registros. Los nombres se bloquean en el servidor. Si un nombre lo ha bloqueado un cliente, GET_LOCK() bloquea cualquier petición de otro cliente para bloquear el mismo nombre. Esto permite a los clientes que se pongan con un nombre de bloqueo dado a usar el nombre para realizar advertencias de bloqueo cooperativo. Pero tenga en cuenta que esto permite a un cliente que no esté entre el conjunto de clientes cooperativos bloquear un nombre, de forma deliverada o no, y evitar que ninguno de los clientes cooperativos puedan bloquear dicho nombre. Una forma de reducir la probabilidad que esto pase es usar nombres de bloqueo especificos de bases de datos o de aplicación. Por ejemplo, use nombres de bloqueo de la forma db_name.str o app_name.str.

    mysql> SELECT GET_LOCK('lock1',10);
            -> 1
    mysql> SELECT IS_FREE_LOCK('lock2');
            -> 1
    mysql> SELECT GET_LOCK('lock2',10);
            -> 1
    mysql> SELECT RELEASE_LOCK('lock2');
            -> 1
    mysql> SELECT RELEASE_LOCK('lock1');
            -> NULL
    

    Tenga en cuenta que la segunda llamada a RELEASE_LOCK() retorna NULL debido a que el bloqueo 'lock1' se libera automáticamente por la segunda llamada GET_LOCK() .

  • INET_ATON(expr)

    Dada la representación de cuatros números separados por puntos de una dirección de red como cadena de caracteres, retorna un entero que representa el valor numérico de la dirección. Las direcciones pueden ser direcciones de 4 o 8 bytes .

    mysql> SELECT INET_ATON('209.207.224.40');
            -> 3520061480
    

    El número generado siempre tiene orden de bytes de red. Para el ejemplo mostrado anteriormente, el número se calcula como 209*256^3 + 207*256^2 + 224*256 + 40.

    INET_ATON() también entiende direcciones IP de forma corta:

    mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
            -> 2130706433, 2130706433
    

    NOTA: Cuando almacene valores generados por INET_ATON(), se recomiendoa que use una columna INT UNSIGNED. Si usa una columna INT (con signo), los valores correspondientes a direcciones IP para las que el primer octeto es mayor que 127 se truncan a 2147483647 (esto es, el valor retornado por INET_ATON('127.255.255.255')). Consulte Sección 11.2, “Tipos numéricos”.

  • INET_NTOA(expr)

    Dada una dirección de red numérica (4 o 8 bytes), retorna la representación de puntos de la dirección como cadena.

    mysql> SELECT INET_NTOA(3520061480);
            -> '209.207.224.40'
    

  • IS_FREE_LOCK(str)

    Chequea si el nombre de bloqueo str está libre para uso (esto es, no bloqueado). Retorna 1 si el bloqueo está libre (nadie lo usa para bloquear), 0 si el bloqueo está en uso, y NULL en errores (tales como argumentos incorrectos).

  • IS_USED_LOCK(str)

    Chequea si el nombre de bloqueo str está en uso (esto es, bloqueado). Si es así, retorna el identificador de conexión del cliente que tiene el bloqueo. De otro modo, retorna NULL.

  • MASTER_POS_WAIT(log_name,log_pos[,timeout])

    Esta función es útil para control de sincronización de maestro/ esclavo. Bloquea hasta que el esclavo ha leído y aplicado todas las actualizaciones hasta la posición especificada en el log del maestro. El valor retornado es el número de eventos logueados que tiene que esperar para llegar a la posición especificada. La función retorna NULL si el flujo esclavo SQL no está arrancado, la información maestra del esclavo no está inicializada, los argumentos son incorrectos, u ocurre un error. Retorna -1 si se agota el tiempo de espera. Si el flujo SQL esclavo para mientras MASTER_POS_WAIT() está esperando, la función retorna NULL. Si el eslavo pasa la posición especificada, la función retorna inmediatamente.

    Si un valor timeout se especifica, MASTER_POS_WAIT() para de esprar cuando pasan timeout segundos. timeout debe ser superior a 0; un cero o timeout negativo significa que no hay timeout.

  • RELEASE_LOCK(str)

    Libera el bloqueo nombrado por la cadena str obtenida con GET_LOCK(). Retorna 1 si el bloqueo se libera, 0 si el bloqueo no estaba bloqueado por este flujo (en cuyo caso el bloqueo no se libera), y NULL si el bloqueo nombrado no existía. El bloqueo no existe si nunca se obtuvo por una llamada a GET_LOCK() o si había sido liberado préviamente.

    El comando DO es conveniente para usar con RELEASE_LOCK(). Consulte Sección 13.2.2, “Sintaxis de DO.

  • UUID()

    Retorna un Universal Unique Identifier (UUID) (Identificador Único Universal) generado según la “DCE 1.1: Remote Procedure Call” (Apéndice A) CAE (Common Applications Environment) Especificaciones publicadas por The Open Group en Octubre 1997 (Número de Documento C706).

    Se designa un UUID como número que es único globalmente en espacio y tiempo. Dos llamadas a UUID() generan dos valores distintos, incluso si estas llamadas se realizan en dos máquinas separadas y no están conectadas entre ellas.

    Un UUID es un número de 128 bits representado por una cadena de cinco números hexadecimales en formato aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:

    • Los primeros tres números se generan de un valor temporal.

    • El cuarto número preserva unicidad temporal en caso de que el valor temporal pierda moniticidad (por ejemplo, debido al cambio horario).

    • El quinto número es un número IEEE 802 de nodo que proporciona unicidad espacial. Un número aleatorio se sustituye si el último no está disponible (por ejemplo, debido a que la máquina no tenga tarjeta Ethernet, o no sabemos encontrar la dirección hardware de una interfaz en el sistema operativo). En este caso, la unicidad espacial no se puede garantizar. Sin embargo, una colisión debe tener una probabilidad muy baja.

      Actualmente, la dirección MAC de una interfaz se tiene en cuenta sólo en FreeBSD y Linux. En otros sistemas operativos, MySQL usa un número generado aleatoriamente de 48 bits.

    mysql> SELECT UUID();
            -> '6ccd780c-baba-1026-9564-0040f4311e29'
    

    Tenga en cuenta que UUID() no funciona todavía con replicación.

12.10. Funciones y modificadores para cláusulas GROUP BY

12.10.1. Funciones (de agregación) de GROUP BY

Si usa una función de grupo en un comando sin la cláusula GROUP BY , es equivalente a agrupar todos los registros.

  • AVG([DISTINCT] expr)

    Retorna el valor medio de expr. La opción DISTINCT puede usarse desde MySQL 5.0.3 para retornar la media de los valores distintos de expr.

    mysql> SELECT student_name, AVG(test_score)
        ->        FROM student
        ->        GROUP BY student_name;
    

  • BIT_AND(expr)

    Retorna el AND bit a bit de todos los bits en expr. Los cálculos se realizan con precisión de 64 bits (BIGINT) .

    En MySQL 5.0, esta función retoran 18446744073709551615 si no hubieran registros coindicentes. (Este es el valor de un BIGINT sin signo con todos los bits a 1.)

  • BIT_OR(expr)

    Retorna la OR bit a bit de todos los bits en expr. El cálculo se realiza con precisión de 64 bits (BIGINT) .

    Esta función retorna 0 si no hay registros coincidentes.

  • BIT_XOR(expr)

    Retorna el XOR bit a bit de todos los bits en expr. Los cálculos se realizan con precisión de 64 bits (BIGINT) .

    Esta función retorna 0 si no hay registros coincidentes.

  • COUNT(expr)

    Retorna el contador del número de valores no NULL en los registros recibidos por un comando SELECT.

    mysql> SELECT student.student_name,COUNT(*)
        ->        FROM student,course
        ->        WHERE student.student_id=course.student_id
        ->        GROUP BY student_name;
    
    

    COUNT(*) es algo diferente en que retorna un contador del número de registros retornados, si contienen o no valores NULL .

    COUNT(*) está optimizado para retornar muy rápidamente si SELECTretorna de una tabla, no se retornan otras columnas, y no hay cláusula WHERE . Por ejemplo:

    mysql> SELECT COUNT(*) FROM student;
    

    Esta optimización se aplica sólo a tablas MyISAM, ya que un conteo exacto de registros se almacena para estos tipos de tablas y puede ser accedido muy rápidamente. Para motores de almacenamiento transaccionales (InnoDB, BDB), almacenar un contador de registros es más problemático ya que pueden ocurrir múltiples transacciones, cada una de las cuales puede afectar al contador.

  • COUNT(DISTINCT expr,[expr...])

    Retorna un contador del número de valores no NULL distintos.

    mysql> SELECT COUNT(DISTINCT results) FROM student;
    

    En MySQL, puede obtener el número de combinaciones de distintas expresiones que no contiene NULL dada una lista de expresiones. En SQL estándar, tendría que hacer una concatenación de todas las expresiones dentro de COUNT(DISTINCT ...).

  • GROUP_CONCAT(expr)

    Esta función retorna una cadena resultado con los valores no NULL concatenados de un grupo. Retorna NULL si no hay valores no NULL . La sintaxis completa es la siguiente:

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                     [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val])
    
    mysql> SELECT student_name,
        ->     GROUP_CONCAT(test_score)
        ->     FROM student
        ->     GROUP BY student_name;
    

    Or:

    mysql> SELECT student_name,
        ->     GROUP_CONCAT(DISTINCT test_score
        ->               ORDER BY test_score DESC SEPARATOR ' ')
        ->     FROM student
        ->     GROUP BY student_name;
    

    En MySQL, puede obtener los valores concatenados de combinaciones de expresiones. Puede eliminar valores duplicados usando DISTINCT. Si quiere ordenar valores en el resultado, debe usar la cláusula ORDER BY. Para ordenar en orden inverso, añada la palabra clave DESC (descendente) al nombre de la columna que está ordenando con la cláusula ORDER BY . El valor por defecto es orden ascendente; puede especificarse explícitamente usando la palabra clave ASC. SEPARATOR tiene a continuación la cadena que debe insertarse entre los valores del resultado. Por defecto es una coma (','). Puede eliminar el separador especificando SEPARATOR ''.

    Puede especificar la longitud máxima con la variable de sistema group_concat_max_len . La sintaxis para ello en tiempo de ejecución es la siguiente, donde val es un entero sin signo:

    SET [SESSION | GLOBAL] group_concat_max_len = val;
    

    Si se especifica una longitud máxima, el resultado se trunca a su longitudo máxima.

  • MIN([DISTINCT] expr), MAX([DISTINCT] expr)

    Retornas los valores máximos y mínimos de expr. MIN() y MAX() pueden tener un argumento; en tales casos retornan el valor de cadena mínimo y máximo. Consulte Sección 7.4.5, “Cómo utiliza MySQL los índices”. La palabra clave DISTINCT puede usarse en MySQL 5.0 para encontrar el mínimo o máximo de los distintos valores de expr; esto es soportado, pero produce el mismo resultado que omitiendo DISTINCT.

    mysql> SELECT student_name, MIN(test_score), MAX(test_score)
        ->        FROM student
        ->        GROUP BY student_name;
    

    Para MIN(), MAX(), y otras funciones agregadas, MySQL compara columnas ENUM y SET por su valor de cadena de caracteres en lugar que por la posición relativa de la cadena en el conjunto. Esto difiere de cómo los compara ORDER BY. Esto se rectificará en una futura versión de MySQL .

  • STD(expr), STDDEV(expr)

    Retorna la desviación estándard de expr. Esta es una extensión del estándar SQL. La forma STDDEV() de esta función se proporciona para compatibilidad con Oracle. Desde MySQL 5.0.3, la función estándar SQL STDDEV_POP() puede usarse en su lugar.

  • STDDEV_POP(expr)

    Retorna la desviación estándar de expr (la raíz cuadrada de VAR_POP()). Esta función se añadió en MySQL 5.0.3. Antes de 5.0.3, puede usar STD() o STDDEV(), que son equivalentes pero no SQL estándar.

  • STDDEV_SAMP(expr)

    Retorna la muestra de la desviación estándar de expr (la raíz cuadrada de VAR_SAMP(). Esta función se añadió en MySQL 5.0.3.

  • SUM([DISTINCT] expr)

    Retorna la suma de expr. Si el conjunto resultado no tiene registros, SUM() retorna NULL. La palabra clave DISTINCT puede usarse en MySQL 5.0 para sumar sólo los valores distintos de expr.

  • VAR_POP(expr)

    Retorna la varianza estándar de expr. Considera los registros como la población completa, no como una muestra, así que tiene el número de registros como denominador. Esta función se añadió en MySQL 5.0.3. Antes de 5.0.3, puede usar VARIANCE(), que es equivalente pero no SQL estándar.

  • VAR_SAMP(expr)

    Retorna la varianza de muestra de expr. Esto es, el denominador es el número de registros menos uno. Esta función se añadió en MySQL 5.0.3.

  • VARIANCE(expr)

    Retorna la varianza estándar de expr. Esto es una extensión de SQL estándar. Desde MySQL 5.0.3, la función SQL estándar VAR_POP() puede usarse en su lugar.

12.10.2. Modificadores de GROUP BY

La cláusula GROUP BY permite añadir un modificador WITH ROLLUP que provoca añadir registros extra al resumen de la salida. Estos registros representan operaciones de resumen de alto nivel ( o super agregadas ) . ROLLUP por lo tanto le permite responder preguntas en múltiples niveles de análisis con una sola consulta. Puede usarse, por ejemplo, para proporcionar soporte para operaciones OLAP (Online Analytical Processing).

Suponga que una tabla llamada sales tiene las columnas year, country, product, y profit para guardar las ventas productivas:

CREATE TABLE sales
(
    year    INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit  INT
);

Los contenidos de la tabla pueden resumirse por año con un simple GROUP BY como este:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+

Esta salida muestra el beneficio total para cada año, pero si quiere determinar el beneficio total registrado durante todos los años, debe añadir los valores individuales usted mismo o ejecutar una consulta adicional.

O puede usar ROLLUP, que proporciona ambos niveles de análisis con una única consulta. Añadir un modificador WITH ROLLUP a la cláusula GROUP BY provoca que la consulta produzca otro registro que muestra el beneficio total sobre todos los valores de año:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+

La línea super agregada con la suma total se identifica con el valor NULL en la columna year .

ROLLUP tiene un efecto más complejo cuando hay múltiples columnas GROUP BY . En este caso, cada vez que hay un “break” (cambio en el valor) en cualquiera excepto la última columna de agrupación, la consulta produce registros super agregados extra.

Por ejemplo, sin ROLLUP, un resumen de la tabla sales basado en year, country, y product puede tener este aspecto:

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+

La salida indica valores resumen sólo en el nivel de análisis year/country/product . Cuando se añade ROLLUP , la consulta produce registros extra:

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

Para esta consulta, añadir ROLLUP provoca que la salida incluya información resumen en cuatro niveles de análisis, no sólo uno. Aquí se muestra cómo interpretar la salida de ROLLUP :

  • A continuación de cada conjunto de registros producto de un año dado y un país, un registro resume extra se produce mostrando el total para todos los productos. Estos registros tienen la columna product a NULL.

  • A continuación de cada conjunto de registros para un año dado, se produce un registro resumen extra mostrando el total para todos los países y productos. Estos registros tienen las columnas country y products a NULL.

  • Finalmente, a continuación de todos los otros registros, un registro extra resumen se produce mostrando el total para todos los años, paises y productos. Este registro tiene las columnas year, country, y products a NULL.

Otras consideraciones usando ROLLUP

Los siguientes puntos listan algunos comportamientos específicos a la implementación de MySQL de ROLLUP:

Cuando usa ROLLUP, no puede usar una cláusula ORDER BY para ordenar los resultados. En otras palabras, ROLLUP y ORDER BY son mútuamente exclusivas. Sin embargo, puede tener algún control sobre la ordenación. GROUP BY en MySQL ordena los resultados, y puede usar explícitamente ASC y DESC con columnas mostradas en la lista GROUP BY para especificar orden de ordenación para columnas individuales. (Los registros resumen de alto nivel apadidos por ROLLUP todavía aparecen tras los registros para los que son calculados, a pesar del orden de ordenación.)

LIMIT puede usarse para restringir el número de registros retornados al cliente. LIMIT se aplica tras ROLLUP, así que el límite se aplica contra los registros extra añadidos por ROLLUP. Por ejemplo:

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+

Usar LIMIT con ROLLUP puede producir resultados que son más difíciles de interpretar, ya que tiene menos contexto para entender los registros super agregados.

Los indicadores NULL en cada registro super agregado se preducen cuando los registros se envían al cliente. El servidor busca las columnas llamadas en la cláusula GROUP BY siguiendo la que esté más a la izquierda que ha cambiado un valor. Para cualquier columna en el conjunto de resultados con un nombre que sea una coincidencia léxica para cualquiera de estos nombres, su valor se cambia a NULL. (Si especifica columnas para agrupar con número de columna, el servidor identifica tales columnas para cambiar a NULL por el número.)

Debido a que los valores NULL en los registros super agregados se guardan en el conjunto de resultados en una de las últimas etapas del proceso de la consulta, no puede testearlas como valores NULL dentro de la propia consulta. Por ejemplo, no puede añadir HAVING product IS NULL a la consulta para eliminar de la salida todos los valores menos los registros super agregados.

Por otro lado, los valores NULL aparecen como NULL en la parte del cliente y pueden testearse como tales usando cualquier interfaz de programación de cliente MySQL.

12.10.3. GROUP BY con campos escondidos

MySQL extiende el uso de GROUP BY para que pueda usar columnas o cálculos en la lista SELECT que no aparecen en la cláusula GROUP BY. Esto se aplica a cualquier valor posible para este grupo . Puede usarlo para obtener mejor rendimiento al evitar ordenar y agrupar elementos innecesarios. Por ejemplo, no necesita agrupar customer.name en la siguiente consulta:

mysql> SELECT order.custid, customer.name, MAX(payments)
    ->        FROM order,customer
    ->        WHERE order.custid = customer.custid
    ->        GROUP BY order.custid;

En SQL estándar, puede tener que añadir customer.name a la cláusula GROUP BY . En MySQL, el nombre es redundante si no se ejecuta en modo ANSI.

No use esta característica si las columnas que omite de la parte GROUP BY no son únicos en el grupo! Obtendría resultados impredecibles.

En algunos casos, puede usar MIN() y MAX() para obtener valores específicos de columna incluso si no son únicos. La siguiente da el valor de column del registro conteniendo el valor más pequeño en la columna sort :

SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7)

Consulte Sección 3.6.4, “Los registros de un grupo que tienen el máximo valor en alguna columna”.

Tenga en cuenta que si trata de seguir SQL estándar, no puede usar expresiones en clásulas GROUP BY o ORDER BY . Puede solucionar esta limitación usando un alias para la expresión:

mysql> SELECT id,FLOOR(value/100) AS val 
    -> FROM tbl_name
    -> GROUP BY id, val ORDER BY val;

Sin embargo, MySQL le permite usar expresiones en cláusulas GROUP BY y ORDER BY . Por ejemplo:

mysql> SELECT id, FLOOR(value/100) FROM tbl_name ORDER BY RAND();

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.