MySQL 5.0 Reference Manual
MySQL 5.0 Reference Manual
É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
[email protected]. Copyright © 1997, 2011, Oracle and/or its affiliates. All rights reserved. This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited. The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing. Si este software o la documentación relacionada se entrega al Gobierno de EE.UU. o a cualquier entidad que adquiera licencias en nombre del Gobierno de EE.UU. se aplicará la siguiente disposición: U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065. This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark of Oracle Corporation and/or its affiliates, and shall not be used without Oracle's express written authorization. Other names may be trademarks of their respective owners. This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services. This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this material is subject to the terms and conditions of your Oracle Software License and Service Agreement, which has been executed and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle or as specifically provided below. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates. This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms: You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above. For more information on the terms of this license, for details on how the MySQL documentation is built and produced, or if you are interested in doing a translation, please visit MySQL Contact & Questions. For additional licensing information, including licenses for libraries used by MySQL products, see Prefacio. If you want help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL users. For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.
Resumen Documento generado el: 2014-05-24 (revisión: 572)
Prefacio Éste es el manual de referencia para el sistema de base de datos MySQL, en su versión 5.0, hasta la versión 5.0.9-beta. No debería utilizarse con ediciones más antiguas del software MySQL, por las muchas diferencias funcionales y de otro tipo entre MySQL 5.0 y versiones anteriores. Si se está utilizando una versión anterior del software MySQL, es preferible hacer referencia al Manual de referencia de MySQL 4.1, que cubre las versiones 3.22, 3.23, 4.0 y 4.1 de MySQL. En este texto se señalan las diferencias entre las diversas versiones de MySQL 5.0, indicando la entrega (5.0.x). La traducción al español de este manual se debe a Vespito, empresa de Barcelona especializada en la gestión de bases de datos MySQL y partner de MySQL AB desde 2001. Ha colaborado en la traducción Claudio Alberto Nipotti, de San Lorenzo (Santa Fe), Argentina.
xix
Capítulo 9. Estructura de lenguaje Tabla de contenidos 9.1 Valores literales ........................................................................................................................ 533 9.1.1 Cadenas de caracteres .................................................................................................. 533 9.1.2 Números ........................................................................................................................ 535 9.1.3 Valores hexadecimales ................................................................................................... 536 9.1.4 Valores booleanos ......................................................................................................... 536 9.1.5 Valores de bits ............................................................................................................... 536 9.1.6 Valores NULL ................................................................................................................. 537 9.2 Nombres de bases de datos, tablas, índices, columnas y alias .................................................... 537 9.2.1 Cualificadores de los identificadores ............................................................................... 538 9.2.2 Sensibilidad a mayúsuclas y minúsculas de identificadores .............................................. 539 9.3 Variables de usuario ................................................................................................................. 541 9.4 Variables de sistema ................................................................................................................. 542 9.4.1 Variables estructuradas de sistema ................................................................................. 543 9.5 Sintaxis de comentarios ............................................................................................................ 545 9.6 Tratamiento de palabras reservadas en MySQL ......................................................................... 546 Este capítulo trata sobre las reglas a seguir cuando se escriban los siguientes elementos de sentencias SQL durante el uso de MySQL: • Valores literales, como cadenas y números. • Identificadores, como nombres de tablas y columans. • Variables de usuario y de sistema • Comentarios • Palabras reservadas
9.1. Valores literales En esta sección se trata la escritura de valores literales en MySQL. Estos incluyen a las cadenas, números, valores hexadeciales, valores booleanos y NULL. También se ocupa de las distintas situaciones (algunas muy propensas a error) en las que se puede incurrir al manejar estos tipos básicos de MySQL.
9.1.1. Cadenas de caracteres Una cadena (string) es una secuencia de caracteres, encerrada por comillas simples (''') o dobles ('"'). Ejemplos: 'una cadena' "otra cadena"
Si el modo de servidor tiene habilitado ANSI_QUOTES, las cadenas solamente pueden delimitarse con comillas simples. Una cadena delimitada por comillas dobles será interpretada como un identificador. A partir de MySQL 4.1.1, las cadenas pueden tener una parte indicativa del conjunto de caracteres y una cláusula COLLATE: 533
Cadenas de caracteres
[_conjunto_caracteres]'cadena' [COLLATE tipo_ordenación]
Ejemplos: SELECT _latin1'cadena'; SELECT _latin1'cadena' COLLATE latin1_danish_ci;
Para más información sobre esta sintaxis consulte Sección 10.3.7, “Conjunto de caracteres y colación de columnas “carácter””. Dentro de una cadena, ciertas secuencias de caracteres tienen un significado especial. Cada una de estas secuencias comienza con una barra diagonal invertida ('\'), conocida como caracter de escape. MySQL reconoce las siguientes secuencias de escape: \0
Un caracter ASCII 0 (NUL).
\'
Un caracter de comilla simple (''').
\"
Un carácter de comilla doble ('"').
\b
Un carácter de retroceso.
\n
Un carácter de salto de línea.
\r
Un carácter de retorno de carro.
\t
Un carácter de tabulación.
\Z
ASCII 26 (Control-Z). Este carácter puede codificarse como '\Z' para solventar el problema de que el ASCII 26 se interpreta en Windows como fin de fichero. (El ASCII 26 causará problemas si se intenta emplear mysql nombre_bd < nombre_fichero.)
\\
Un carácter de barra invertida ('\').
\%
Un carácter '%'. Consulte la nota a continuación.
\_
Un carácter '_'. Consulte la nota a continuación.
Estas secuencias son sensibles a mayúsculas. Por ejemplo, '\b' se interpreta como carácter de retroceso, pero '\B' se interpreta como 'B'. Las secuencias '\%' y '\_' se emplean para buscar apariciones literales de '%' y '_' en un contexto de búsqueda por patrones, donde en otro caso se debieran interpretar como caracteres comodines. Consulte Sección 12.3.1, “Funciones de comparación de cadenas de caracteres”. Hay que advertir que si se emplean '\%' o '\_' en otra situación, devolverán las cadenas '\%' y '\_' y no '%' y '_'. En toda otra secuencia de escape, la barra invertida se ignora. Esto es, el carácter al que se aplica se interpreta como si no tuviera delante un carácter de escape. Hay varias formas de incluir comillas dentro de una cadena: • Un ''' dentro de una cadena que está delimitada por ''' debe escribirse como ''''. • Un '"' dentro de una cadena que está delimitada por '"' debe escribirse como '""'. • Se puede preceder el carácter de comillas con un carácter de escape. ('\'). • Un ''' dentro de una cadena delimitada con '"' no necesita ningún tratamiento especial, ni colocarla en forma doble ni precederla con un carácter de escape. Lo mismo se cumple para una '"' colocada en una cadena delimitada con '''. 534
Números Las siguientes sentencias SELECT demuestran cómo actúan las comillas y los caracteres de escape: mysql> SELECT 'hola', '"hola"', '""hola""', 'hol''a', '\'hola'; +------+--------+----------+-------+-------+ | hola | "hola" | ""hola"" | hol'a | 'hola | +------+--------+----------+-------+-------+ mysql> SELECT "hola", "'hola'", "''hola''", "hol""a", "\"hola"; +------+--------+----------+-------+-------+ | hola | 'hola' | ''hola'' | hol"a | "hola | +------+--------+----------+-------+-------+ mysql> SELECT 'Estas\nSon\nCuatro\nLíneas'; +--------------------+ | Estas Son Cuatro Líneas | +--------------------+ mysql> SELECT 'barra\ desaparece'; +------------------+ | barra desaparece | +------------------+
Si se pretende insertar datos binarios en una columna de tipo cadena (por ejemplo un BLOB), los siguientes caracteres deberán representarse con secuencias de escape: NUL
Byte NUL (ASCII 0). Este carácter se representará con '\0' (una barra invertida seguida de un carácter ASCII '0').
\
Barra invertida (ASCII 92). Este carácter se representará con '\\'.
'
Comilla simple (ASCII 39). Este carácter se representará con '\''.
"
Comilla doble (ASCII 34). Este carácter se representará con '\"'.
Al escribir programas de aplicación, cualquier cadena que pudiese contener cualquiera de estos caracteres especiales deberá ser preparada antes de utilizarse como valor en una sentencia SQL que se enviará al servidor MySQL. Esto puede hacerse de dos maneras: • Procesando la cadena con una función que reemplace los caracteres especiales con una secuencia de escape. Por ejemplo, en un programa C, se puede emplear la función de la API de C mysql_real_escape_string(). Consulte Sección 24.2.3.48, “mysql_real_escape_string()”. La interfaz DBI de Perl proporciona un método quote para convertir caracteres especiales a las secuencias de escape equivalentes. Consulte Sección 24.4, “La API Perl de MySQL”. • Como alternativa al reemplazo explícito de caracteres especiales, varias APIs de MySQL proporcionan la parametrización de consultas, lo que permite insertar marcadores especiales en una consulta y luego asociarles valores al momento de emitirla. En este caso, la API toma a su cargo el reemplazo de caracteres especiales en los valores.
9.1.2. Números Los enteros se representan como secuencias de dígitos. Los flotantes utilizan '.' como separador decimal. Cada tipo de número puede estar precedido con '-' para indicar un valor negativo. Ejemplos de enteros válidos: 535
Valores hexadecimales
1221 0 -32
Ejemplos de números de punto flotante válidos: 294.42 -32032.6809e+10 148.00
Un entero puede usarse en un contexto de punto flotante; se intepretará como el número de punto flotante equivalente.
9.1.3. Valores hexadecimales MySQL soporta valores hexadecimales. En contextos numéricos, éstos actuarán como enteros (con precisión de 64 bits). En contextos de cadena, actuarán como cadenas binarias, donde cada par de dígitos hexadecimales es convertido a un carácter: mysql> SELECT x'4D7953514C'; -> 'MySQL' mysql> SELECT 0xa+0; -> 10 mysql> SELECT 0x5061756c; -> 'Paul'
En MySQL 5.0, el tipo predeterminado para un valor hexadecimal es una cadena. Si se desea estar seguro de que el valor se tratará como un número, puede emplearse CAST(... AS UNSIGNED): mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED); -> 'A', 65
La sintaxis 0x se basa en ODBC. Las cadenas hexadecimales son utilizadas a menudo por ODBC para proveer valores para columnas BLOB. La sintaxis x'hexstring' se basa en SQL standard. Se puede convertir una cadena o un número en una cadena en formato hexadecimal con la función HEX(): mysql> SELECT HEX('cat'); -> '636174' mysql> SELECT 0x636174; -> 'cat'
9.1.4. Valores booleanos En MySQL 5.0, la constante TRUE se evalúa como 1 y la constante FALSE, como 0. Los nombres de constantes pueden escribirse en cualquier combinación de mayúsculas y minúsculas. mysql> SELECT TRUE, true, FALSE, false; -> 1, 1, 0, 0
9.1.5. Valores de bits A partir de MySQL 5.0.3, los valores de bits pueden escribirse utilizando la notación b'valor'. value es un valor binario escrito empleando ceros y unos. 536
Valores NULL La notación de bits es conveniente para especificar valores que se asignarán a columnas BIT: mysql> CREATE TABLE t (b BIT(8)); mysql> INSERT INTO t SET b = b'11111111'; mysql> INSERT INTO t SET b = b'1010'; +------+----------+----------+----------+ | b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) | +------+----------+----------+----------+ | 255 | 11111111 | 377 | FF | | 10 | 1010 | 12 | A | +------+----------+----------+----------+
9.1.6. Valores NULL El valor NULL significa “no hay dato.” NULL puede escribirse en cualquier combinación de mayúsculas y minúsculas. Debe tenerse en cuenta que el valor NULL no es lo mismo que 0 para tipos numéricos o la cadena vacía para tipos de cadena. Consulte Sección A.5.3, “Problemas con valores NULL”. Para operaciones de exportación o importación de texto utilizando LOAD DATA INFILE o SELECT ... INTO OUTFILE, NULL se representa con la secuencia \N. Consulte Sección 13.2.5, “Sintaxis de LOAD DATA INFILE”.
9.2. Nombres de bases de datos, tablas, índices, columnas y alias Los nombres de bases de datos, tablas, índices, columnas y alias son identificadores. Esta sección describe la sintaxis permitida para los identificadores de MySQL. La siguiente tabla describe la longitud máxima y los caracteres permitidos para cada tipo de identificador. Identificador
Longitud máxima (en bytes)
Caracteres permitidos
Base de datos 64
Cualquier carácter permitido en un nombre de directorio, excepto '/', '\', o '.'
Tabla
64
Cualquier carácter permitido en un nombre de fichero, excepto '/', '\', o '.'
Columna
64
Todos los caracteres
Índice
64
Todos los caracteres
Alias
255
Todos los caracteres
Adicionalmente a las restricciones detalladas en la tabla, ningún identificador puede contener un carácter ASCII 0 o un byte con un valor de 255. Los nombres de bases de datos, tablas y columnas no deberían terminar con caracteres de espacio. MySQL 5.0 permite el uso de comillas en identificadores, aunque es mejor evitarlos tanto como sea posible. En MySQL 5.0, los identificadores se almacenan empleando Unicode (UTF8). Esto se aplica a identificadores en las definiciones de tabla que se almacenan en ficheros .frm y a identificadores almacenados en las tablas de permisos en la base de datos mysql. El tamaño de las columnas de tipo cadena en las tablas de permisos (y en cualquier otra tabla) de MySQL 5.0 equivale al número de caracteres, esto significa que (al contrario que en algunas versiones anteriores de MySQL) se pueden 537
Cualificadores de los identificadores utilizar caracteres multibyte sin reducir el número de caracteres permitidos para los valores almacenados en estas columnas. Un identificador puede estar encerrado entre comillas o no. Si un identificador es una palabra reservada o contiene caracteres especiales, se debe encerrar entre comillas cada vez que se haga referencia a él. Para una lista de palabras reservadas, consulte Sección 9.6, “Tratamiento de palabras reservadas en MySQL”. Los caracteres especiales son aquellos que están fuera del grupo de caracteres alfanuméricos del conjunto de caracteres en uso, de '_', y de '$'. El carácter de encomillado de identificador es el acento grave: ('`'): mysql> SELECT * FROM `select` WHERE `select`.id > 100;
Si el modo de servidor SQL incluye la opción ANSI_QUOTES, también está permitido delimitar los identificadores con comillas dobles: mysql> CREATE TABLE "test" (col INT); ERROR 1064: You have an error in your SQL syntax. (...) mysql> SET sql_mode='ANSI_QUOTES'; mysql> CREATE TABLE "test" (col INT); Query OK, 0 rows affected (0.00 sec)
Consulte Sección 5.3.2, “El modo SQL del servidor”. En MySQL 5.0, los caracteres delimitadores de identificador pueden incluírse dentro del identificador si se delimita el identificador. Si el carácter que se incluirá dentro del identificador es el mismo que se utiliza para delimitarlo, habrá que colocarlo en forma doble. Las siguientes sentencias crean una tabla llamada a`b que contiene una columna llamada c"d: mysql> CREATE TABLE `a``b` (`c"d` INT);
Se recomienda que no se utilicen nombres con el esquema XeX, tal como 1e o 2e2, porque una expresión como 1e+1 es ambigua. Podría interpretarse como la expresión 1e + 1 o como el número 1e+1, dependiendo del contexto. Hay que ser cuidadoso al utilizar MD5 para producir nombres de tablas, porque puede producir nombres ilegales como los listados anteriormente.
9.2.1. Cualificadores de los identificadores MySQL acepta nombres que pueden consistir en un solo identificador o múltiples identificadores. Los componentes de un nombre múltiple deben separarse con un punto ('.'). Las partes iniciales de un identificador múltiple actúan como calificadores que afectan el contexto en el cual se interpreta la parte final. En MySQL es posible referirse a una columna empleando cualquiera de las siguientes formas: Referencia de columna
Significado
col_name
La columna col_name de cualquier tabla empleada en la consulta que contenga una columna con ese nombre
tbl_name.col_name
La columna col_name de la tabla tbl_name en la base de datos predeterminada.
538
Sensibilidad a mayúsuclas y minúsculas de identificadores
db_name.tbl_name.col_name La columna col_name en la tabla tbl_name en la base de datos db_name. Si cualquier componente de un nombre múltiple requiere delimitarlo, hay que hacerlo individualmente en lugar de delimitar el nombre como un todo. Por ejemplo, `mi-tabla`.`mi-columna` es legal, pero `mi-tabla.mi-columna` no lo es. No es necesario especificar un prefijo de nombre_tabla o nombre_bd.nombre_tabla para referenciar una columna en una sentencia a menos que la referencia sea ambigua. Supóngase el caso de las tablas t1 y t2 cada una conteniendo una columna c, donde se recupera c en una sentencia SELECT que emplea ambas tablas t1 y t2. En este caso, c es ambiguo porque no es único entre las tablas utilizadas en la sentencia. Se lo debe calificar con un nombre de tabla como t1.c o t2.c para indicar a cuál tabla se refiere la consulta. Del mismo modo, para recuperar desde una tabla t en la base de datos db1 y desde la tabla t en la base de datos db2 en la misma sentencia, hay que referirse a las columnas en aquellas tablas como db1.t.col_name y db2.t.col_name. Una palabra a continuación de un punto en un nombre calificado debe ser un identificador, por lo que no es necesario delimitarlo, aun si es una palabra reservada. La sintaxis .tbl_name hace referencia a la tabla tbl_name en la base de datos actual. Esta sintaxis se acepta por compatibilidad con ODBC, ya que algunos programas ODBC anteceden los nombres de tabla con un carácter '.'.
9.2.2. Sensibilidad a mayúsuclas y minúsculas de identificadores En MySQL, las bases de datos se corresponden con directorios dentro del directorio de datos. Cada tabla dentro de una base de datos corresponde a por lo menos un fichero dentro del directorio de la base de datos (y posiblemente más, dependiendo del motor de almacenamiento). Por lo tanto, es la sensibilidad a mayúsculas del sistema operativo subyacente la que determina la sensibilidad a mayúsculas en los nombres de tablas y bases de datos. Esto significa que los nombres de las tablas y las bases de datos son sensibles a mayúsculas en la mayoría de las variedades de Unix, pero no lo son en Windows. Una notable excepción es Mac OS X, el cual se basa en Unix pero utiliza en forma predeterminada un sistema de ficheros (HFS+) que no es sensible a mayúsculas. No obstante, Mac OS X también soporta volúmenes UFS, los cuales son sensibles a mayúsculas tal como cualquier Unix. Consulte Sección 1.7.4, “Extensiones MySQL al estándar SQL”. Nota: Si bien los nombres de bases de datos y tablas no son sensibles a mayúsculas en algunas plataformas, no habría que referirse a una tabla o base de datos con diferentes combinaciones de mayúsculas y minúsculas dentro de la misma consulta. La siguiente consulta podría fallar porque se refiere a una tabla como my_table y MY_TABLE: mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Los nombres de columnas, índices, procedimientos almacenados y triggers no son sensibles a mayúsculas en ninguna plataforma, ni tampoco lo son los alias de columnas. En forma predeterminada, los alias de tabla en MySQL 5.0 son sensibles a mayúsculas en Unix, pero no en Windows o Mac OS X. La siguiente consulta no funcionaría en Unix, porque se refiere al alias en ambas formas a y A: mysql> SELECT nombre_col FROM nombre_tabla AS a -> WHERE a.nombre_col = 1 OR A.nombre_col = 2;
Sin embargo, la misma consulta está permitida en Windows. Para evitar estar pendiente de tales diferencias, lo mejor es adoptar una convención consistente, como crear y referirse a bases de datos y 539
Sensibilidad a mayúsuclas y minúsculas de identificadores tablas usando siempre minúsculas. Esto es lo recomendado para lograr máxima portabilidad y facilidad de uso. La forma en que los nombres de tablas y bases de datos se almacenan en el disco y se usan en MySQL se define mediante la variable de sistema lower_case_table_names, a la cual se le puede establecer un valor al iniciar mysqld. lower_case_table_names puede tomar uno de los siguientes valores: Valor
Significado
0
Los nombres de tablas y bases de datos se almacenan en disco usando el esquema de mayúsculas y minúsculas especificado en las sentencias CREATE TABLE o CREATE DATABASE. Las comparaciones de nombres son sensibles a mayúsculas. Esto es lo predeterminado en sistemas Unix. Nótese que si se fuerza un valor 0 con --lower-casetable-names=0 en un sistema de ficheros insensible a mayúsculas y se accede a tablas MyISAM empleando distintos esquemas de mayúsculas y minúsculas para el nombre, esto puede conducir a la corrupción de los índices.
1
Los nombres de tablas se almacenan en minúsculas en el disco y las comparaciones de nombre no son sensibles a mayúsculas. MySQL convierte todos los nombres de tablas a minúsculas para almacenamiento y búsquedas. En MySQL 5.0, este comportamiento también se aplica a nombres de bases de datos y alias de tablas. Este valor es el predeterminado en Windows y Mac OS X.
2
Los nombres de tablas y bases de datos se almacenan en disco usando el esquema de mayúsculas y minúsculas especificado en las sentencias CREATE TABLE o CREATE DATABASE, pero MySQL las convierte a minúsculas en búsquedas. Las comparaciones de nombres no son sensibles a mayúsculas. Nota: Esto funciona solamente en sistemas de ficheros que no son sensibles a mayúsculas. Los nombres de las tablas InnoDB se almacenan en minúsculas, como cuando lower_case_table_names vale 1.
En MySQL 5.0 para Windows y Mac OS X, el valor predeterminado de lower_case_table_names es 1. Si se utiliza MySQL en una sola plataforma, normalmente no habrá que cambiar la variable lower_case_table_names. Sin embargo, se pueden encontrar dificultades si se desea transferir tablas entre plataformas cuyos sistemas de ficheros tengan diferente sensibilidad a mayúsculas. Por ejemplo, en Unix, se pueden tener dos tablas diferentes llamadas mi_tabla y MI_TABLA, pero en Windows, estos dos nombres se consideran idénticos. Para evitar problemas de transferencia de datos originados en la combinación de mayúsculas y minúsculas de los nombres de bases de datos y tablas, se tienen dos opciones: • Emplear lower_case_table_names=1 en todos los sistemas. La principal desventaja de esto es que al emplear SHOW TABLES o SHOW DATABASES no se verán los nombres en su combinación original de minúsculas y mayúsculas. • Emplear lower_case_table_names=0 en Unix y lower_case_table_names=2 en Windows. Esto preserva la combinación de mayúsculas y minúsculas en los nombres de bases de datos y tablas. La desventaja es que hay que tener la precaución de que las consultas siempre se refieran a las bases de datos y tablas en Windows respetando la combinación correcta de mayúsculas y minúsculas. Si se transfirieran las consultas a Unix, donde las mayúsculas y minúsculas son significativas, no funcionarán si no se utiliza la combinación correcta. Excepción: Si se utilizan tablas InnoDB, se debería establecer lower_case_table_names en 1 en todas las plataformas para forzar a que los nombres sean convertidos a minúsculas. Notar que antes de establecer lower_case_table_names en 1 en Unix, se deberán convertir a minúsculas los nombres de bases de datos y tablas existentes antes de reiniciar mysqld. 540
Variables de usuario
9.3. Variables de usuario MySQL 5.0 soporta variables de usuario, las cuales permiten almacenar un valor y hacer referencia a él más tarde; esto posibilita pasar valores de una sentencia a otra. Las variables de usuario son específicas de la conexión. Esto significa que una variable definida por un cliente no puede ser vista o utilizada por otros clientes. Todas las variables de un cliente son automáticamente liberadas cuando ese cliente abandona la conexión. Las variables de usuario se escriben como @nombre_var, donde el nombre de variable nombre_var puede consistir de caracteres alfanuméricos tomados del conjunto de caracteres actual, '.', '_', y '$'. El conjunto de caracteres predeterminado es ISO-8859-1 (Latin1). Esto puede cambiarse con la opción de mysqld --default-character-set. Consulte Sección 5.9.1, “El conjunto de caracteres utilizado para datos y ordenación”. Los nombres de variables de usuario no son sensibles a mayúsculas en MySQL 5.0. Una forma de establecer una variable de usuario es empleando una sentencia SET: SET @nombre_var = expr [, @nombre_var = expr] ...
Con SET, tanto = como := pueden usarse como operadores de asignación. La expr asignada a cada variable puede evaluarse a un valor entero, real, cadena, o NULL. Una variable de usuario también puede recibir valores en otras sentencias que no sean SET. En este caso, el operador de asignación debe ser := y no = porque = se considera operador de comparación en otras sentencias que no sean SET: mysql> SET @t1=0, @t2=0, @t3=0; mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +----------------------+------+------+------+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +----------------------+------+------+------+ | 5 | 5 | 1 | 4 | +----------------------+------+------+------+
Las variables de usuario pueden emplearse en cualquier parte donde se permitan expresiones. Generalmente esto no incluye situaciones donde explícitamente se requiere un valor literal, como en la cláusula LIMIT de una sentencia SELECT, o la cláusula IGNORE número LINES de una sentencia LOAD DATA. Si se hace referencia a una variable aún sin inicializar, su valor será NULL. En MySQL 5.0, si a una variable se le asigna un valor de cadena, el conjunto de caracteres y la forma de comparación (collation) de la variable serán forzados para ser iguales a los de la cadena. Este comportamiento es implícito desde MySQL 5.0.3 y lo mismo sucede con las columnas de una tabla. Nota: en una sentencia SELECT, cada expresión se evalúa solamente cuando se envía al cliente. Esto significa que en una cláusula HAVING, GROUP BY, u ORDER BY, no es posible hacer referencia a una expresión que comprenda variables que reciben su valor en la lista del SELECT. Por ejemplo, la siguiente sentencia no funcionará como se espera: mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;
La referencia a b en la cláusula HAVING hace referencia al alias de una expresión de la lista SELECT que hace uso de la variable @aa. Esto no funciona como se esperaría: @aa no contiene el valor de la fila actual, sino el valor del id de la fila anteriormente seleccionada. 541
Variables de sistema La regla general es que nunca se asigne un valor a una variable de usuario en una parte de una sentencia y se use la misma variable en otra parte de la misma sentencia. Se podrían obtener los resultados esperados, pero esto no está garantizado. Otro problema asociado a asignar el valor de una variable y emplearla en la misma sentencia es que el tipo de dato resultante estará basado en el tipo que tenía la variable al comienzo de la sentencia. El siguiente ejemplo ilustra esto: mysql> SET @a='test'; mysql> SELECT @a,(@a:=20) FROM tbl_name;
En esta sentencia SELECT, MySQL informa al cliente que la primer columna es una cadena, y convierte todos los accesos a @a en cadenas, aún cuando @a recibe un valor numérico en la segunda línea. Luego de que la sentencia SELECT se ejecuta, @a se considera un número para la siguiente sentencia. Para evitar problemas con este comportamiento, no se debe inicializar y utilizar la misma variable en la misma sentencia, o, de lo contrario, hay que establecer su valor en 0, 0.0, o '' para definir su tipo antes de utilizarla. Una variable sin asignación tiene el valor NULL con un tipo cadena.
9.4. Variables de sistema MySQL proporciona acceso a muchas variables de sistema y de conexión. Muchas variables pueden modificarse dinámicamente mientras el servidor se está ejecutando. Esto a menudo permite variar la operación del servidor sin tener que detenerlo y reiniciarlo. El servidor mysqld mantiene dos clases de variables. Las variables globales afectan la operación general del servidor. Las variables de sesión actúan sobre la operación en conexiones de clientes individuales. Cuando el servidor arranca, inicializa todas las variables globales a sus valores predeterminados. Estos valores pueden ser modificados por opciones especificadas en ficheros de opciones o en la línea de comandos. Luego de que el servidor se inicia, las variables globales pueden ser modificadas dinámicamente conectándose y emitiendo una sentencia SET GLOBAL var_name. Para cambiar una variable global debe tenerse el privilegio SUPER. El servidor también mantiene un conjunto de variables de sesión para cada cliente que se conecta. Las variables de sesión de cliente se inicializan al momento de conectarse, empleando el valor actual de la correspondiente variable global. Las variables de sesión dinámicas pueden ser modificadas por el cliente mediante una sentencia SET SESSION var_name. No se requieren privilegios especiales para establecer el valor una variable de sesión, pero un cliente puede modificar solamente sus propias variables, no las de otros clientes. Un cambio en una variable global es visible para cualquier cliente que acceda esa variable. Sin embargo, afectará solamente a las correspondientes variables de sesión de las conexiones que se realicen luego del cambio. No afectará las variables de sesión de los clientes actualmente conectados (ni siquiera las del cliente que emitió la sentencia SET GLOBAL). Los valores de las variables globales y de sesión pueden establecerse y recuperarse usando varias sintaxis diferentes. Los siguientes ejemplos están basados en la variable sort_buffer_size. Para establecer el valor de una variable GLOBAL, debe emplearse una de las siguientes sintaxis: mysql> SET GLOBAL sort_buffer_size=valor; mysql> SET @@global.sort_buffer_size=valor;
542
Variables estructuradas de sistema Para establecer el valor de una variable SESSION, debe emplearse una de las siguientes sintaxis: mysql> SET SESSION sort_buffer_size=valor; mysql> SET @@session.sort_buffer_size=valor; mysql> SET sort_buffer_size=valor;
LOCAL es un sinónimo de SESSION. Si al establecer el valor de una variable no se utiliza GLOBAL, SESSION, o LOCAL, por defecto se asume SESSION. Consulte Sección 13.5.3, “Sintaxis de SET”. Para recuperar el valor de una variable GLOBAL debe utilizarse una de las siguientes sentencias: mysql> SELECT @@global.sort_buffer_size; mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';
Para recuperar el valor de una variable SESSION debe utilizarse una de las siguientes sentencias: mysql> SELECT @@sort_buffer_size; mysql> SELECT @@session.sort_buffer_size; mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
Aquí, también, LOCAL es un sinónimo de SESSION. Cuando se recupera una variable con SELECT @@nombre_var (o sea, no se especifica global., session., o local.), MySQL devuelve el valor de SESSION si existe y el valor GLOBAL en otro caso. En el caso de SHOW VARIABLES, si no se especifica GLOBAL, SESSION, o LOCAL, MySQL devuelve los valores de SESSION. La razón por la que la palabra clave GLOBAL se requiere para establecer el valor de variables que solamente existen como GLOBAL pero no para recuperar dicho valor, es para prevenir futuros problemas. Si se elimina una variable SESSION con el mismo nombre que una variable GLOBAL, un cliente con el privilegio SUPER podría cambiar accidentalmente la variable GLOBAL en lugar de hacerlo solamente sobre la variable SESSION de su propia conexión. Si se agrega una variable SESSION con el mismo nombre que una GLOBAL, un cliente que intentase modificar la variable GLOBAL podría encontrarse con que sólo se ha modificado su propia variable SESSION. Puede encontrarse mayor información acerca de las opciones de inicio del sistema y de las variables de sistema en Sección 5.3.1, “Opciones del comando mysqld” y Sección 5.3.3, “Variables de sistema del servidor”. Una lista de las variables que pueden establecerse en tiempo de ejecución se brinda en Sección 5.3.3.1, “Variables de sistema dinámicas”.
9.4.1. Variables estructuradas de sistema MySQL 5.0 también soporta variables de sistema estructuradas. Una variable estructurada difiere de una variable de sistema convencional en dos aspectos: • Su valor es una estructura con componentes que especifican parámetros de servidor que se consideran estrechamente relacionados. • Pueden existir varias instancias de un determinado tipo de variable estructurada. Cada una tiene un nombre diferente y se refiere a un recurso mantenido por el servidor. Actualmente, MySQL soporta un solo tipo de variable estructurada. Éste especifica parámetros que regulan el funcionamiento de los cachés de claves (key caches). Una variable estructurada de caché de claves tiene estos componentes: 543
Variables estructuradas de sistema • key_buffer_size • key_cache_block_size • key_cache_division_limit • key_cache_age_threshold Esta sección describe la sintaxis para referirse a variables estructuradas. Para los ejemplos de sintaxis se emplean variables de caché de claves, pero los detalles específicos sobre cómo funcionan los cachés de claves se encuentran en Sección 7.4.6, “La caché de claves de MyISAM”. Para referirse a un componente de una instancia de una variable estructurada, se emplea un nombre compuesto con el formato nombre_instancia.nombre_componente. Ejemplos: hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size
Siempre hay predefinida una instancia con el nombre default para cada variable de sistema estructurada. Si se hace referencia a un componente de una variable estructurada sin mencionar el nombre de instancia, se utiliza default. Por lo tanto, default.key_buffer_size y key_buffer_size se refieren a la misma variable de sistema. Las reglas para la denominación de instancias y componentes pertenecientes a variables estructuradas son las siguientes: • Para un determinado tipo de variable estructurada, cada instancia debe tener un nombre que sea único dentro de ese tipo de variable. Sin embargo, los nombres de instancia no necesitan ser únicos a través de distintos tipos de variable estructurada. Por ejemplo, cada variable estructurada tiene una instancia llamada default, así que default no es único a través de distintos tipos de variable. • Los nombres de los componentes de cada tipo de variable estructurada deben ser únicos a través de todos los nombres de variables de sistema. Si esto no fuese así (o sea, si dos tipos diferentes de variable estructurada compartiesen nombres de miembros), no sería posible determinar la variable estructurada por defecto a emplear cuando un nombre de miembro no estuviese precedido por un nombre de instancia. • Si un nombre de instancia de variable estructurada no fuese legal al usarlo como identificador sin delimitar, habrá que referirse a él delimitándolo con acentos graves (ASCII 96). Por ejemplo, hotcache no es un nombre legal, pero `hot-cache` lo es. • global, session, y local no son nombres legales de instancia. Esto evita conflictos con notaciones del tipo @@global.nombre_var, que se utilizan para hacer referencias a variables de sistema no estructuradas. Actualmente, las primeras dos reglas no tienen posibilidad de ser infringidas, porque el único tipo de variable estructurada es el empleado para cachés de claves. Estas reglas cobrarán mayor significado si en el futuro se crean otros tipos de variable estructurada. Con una excepción, se puede hacer referencia a los componentes de una variable estructurada utilizando nombres compuestos en cualquier contexto en que puedan aparecer nombres simples de variable. Por ejemplo, se puede asignar un valor a una variable estructurada empleando una opción de línea de comandos: shell> mysqld --hot_cache.key_buffer_size=64K
544
Sintaxis de comentarios En un fichero de opciones, se utilizaría: [mysqld] hot_cache.key_buffer_size=64K
Si se inicia el servidor con esta opción, crea un caché de claves llamado hot_cache con un tamaño de 64KB adicionalmente al caché por defecto, que tiene un tamaño predeterminado de 8MB. Suponiendo que se inicie el servidor de esta manera: shell> mysqld --key_buffer_size=256K \ --extra_cache.key_buffer_size=128K \ --extra_cache.key_cache_block_size=2048
En este caso, el servidor establece el tamaño del caché de claves pedeterminado a 256KB. (También se podría haber escrito --default.key_buffer_size=256K.) Adicionalmente, el servidor crea un segundo caché llamado extra_cache con un tamaño de 128KB, y fija un tamaño de 2048 bytes para los buffers de bloque destinados al caché de bloques de índice de tablas. El siguiente ejemplo inicia el servidor con tres diferentes cachés de claves, manteniendo sus tamaños en una proporción de 3:1:1: shell> mysqld --key_buffer_size=6M \ --hot_cache.key_buffer_size=2M \ --cold_cache.key_buffer_size=2M
Los valores de las variables estructuradas también pueden establecerse y leerse en tiempo de ejecución. Por ejemplo, para establecer a 10MB el tamaño de un caché de claves llamado hot_cache, pueden emplearse cualquiera de estas sentencias: mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024; mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;
Para obtener el tamaño del caché, se realiza lo siguiente: mysql> SELECT @@global.hot_cache.key_buffer_size;
Sin embargo, las siguientes sentencias no funcionarán. La variable no es interpretada como un nombre compuesto, sino como una cadena proporcionada a LIKE para buscar coincidencias con un patrón. mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';
Esta es la excepción mencionada sobre la posibilidad de utilizar en cualquier sitio un nombre de variable estructurada del mismo modo que se hace con una variable simple.
9.5. Sintaxis de comentarios El servidor MySQL soporta tres estilos de comentario: • Desde un carácter '#' hasta el fin de la línea. • Desde una secuencia '-- ' hasta el final de la línea. Nótese que el estilo '-- ' (doble guión) requiere que luego del último guión haya por lo menos un espacio en blanco (espacio, tabulación nueva línea, etc.). Esta sintaxis difiere ligeramente de la sintaxis de comentarios SQL estándar, como se trata en Sección 1.7.5.7, “Empezar un comentario con '--'”. 545
Tratamiento de palabras reservadas en MySQL • Desde una secuencia '/*' hasta la próxima secuencia '*/'. La secuencia de cierre no necesita estar en la misma línea, lo que permite tener comentarios que abarquen múltiples lineas. El siguiente ejemplo muestra los tres estilos de comentario: mysql> SELECT mysql> SELECT mysql> SELECT mysql> SELECT /* Este es un comentario en */ 1;
1+1; # Este comentario llega hasta el final de la línea 1+1; -- Este comentario llega hasta el final de la línea 1 /* este es un comentario en línea (in-line) */ + 1; 1+
múltiples líneas
La sintaxis de comentarios descrita se aplica a la forma en que el servidor mysqld procesa las sentencias SQL. El programa cliente mysql también realiza algún tipo de proceso de sentencias antes de enviarlas al servidor. (Por ejemplo, para determinar donde acaba cada sentencia en una línea que contiene varias de ellas). En MySQL 5.0, la única limitación en la forma en que mysql procesa los comentarios /* ... */ es que un signo admirativo o exclamativo utilizado con este estilo de comentario marca porciones de sentencias SQL de ejecución condicional. Esto se aplica cuando se ejecuta mysql interactivamente y cuando se colocan comandos en un fichero y se emplea mysql para procesar el fichero con mysql < nom_fich. Para más información y ejemplos, consulte Sección 1.7.4, “Extensiones MySQL al estándar SQL”.
9.6. Tratamiento de palabras reservadas en MySQL Un problema común se origina cuando se intenta utilizar en un identificador tal como un nombre de tabla o columna el nombre de un tipo de dato o una función incorporados en MySQL, como TIMESTAMP o GROUP. Es posible hacer esto (por ejemplo, ABS es un nombre de columna válido). Sin embargo, por defecto, al invocar una función no se permiten espacios entre su nombre y el carácter '(' que le sigue. Este requisito permite distinguir entre una llamada a una función y una referencia a una columna con el mismo nombre. Un efecto secundario de este comportamiento es que omitir un espacio en ciertos contextos provoca que un identificador sea interpretado como un nombre de función. Por ejemplo, esta sentencia es legal: mysql> CREATE TABLE abs (val INT);
Pero al omitirse el espacio luego de abs, se produce un error de sintaxis porque la sentencia pasa a invocar la función ABS(): mysql> CREATE TABLE abs(val INT);
Si el modo de servidor SQL incluye el valor IGNORE_SPACE, el servidor permite que las invocaciones a funciones tengan espacios en blanco entre el nombre de la función y el carácter '(' que le sigue. Esto convierte a los nombres de funciones en palabras reservadas. Como resultado, los identificadores que tienen similar nombre a una función deben ser delimitados por comillas como se describe en Sección 9.2, “Nombres de bases de datos, tablas, índices, columnas y alias”. Para controlar el modo de servidor SQL consulte Sección 5.3.2, “El modo SQL del servidor”. Una palabra a continuación de un punto en un nombre calificado (por ejemplo nom_bd.nom_tabla) debe ser un identificador, por lo que no es necesario delimitarlo, incluso si es una palabra reservada. Las palabras en la siguiente tabla están explícitamente reservadas en MySQL. La mayoría de ellas están prohibidas por el estándar SQL para ser empleadas como nombres de columnas y/o tablas (por ejemplo, 546
Tratamiento de palabras reservadas en MySQL GROUP). Unas pocas son reservadas porque MySQL las necesita y (actualmente) utiliza un intérprete (parser) yacc. Una palabra reservada puede emplearse como identificador si se la delimita con comillas. ADD
ALL
ALTER
ANALYZE
AND
AS
ASC
ASENSITIVE
BEFORE
BETWEEN
BIGINT
BINARY
BLOB
BOTH
BY
CALL
CASCADE
CASE
CHANGE
CHAR
CHARACTER
CHECK
COLLATE
COLUMN
CONDITION
CONSTRAINT
CONTINUE
CONVERT
CREATE
CROSS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURSOR
DATABASE
DATABASES
DAY_HOUR
DAY_MICROSECOND
DAY_MINUTE
DAY_SECOND
DEC
DECIMAL
DECLARE
DEFAULT
DELAYED
DELETE
DESC
DESCRIBE
DETERMINISTIC
DISTINCT
DISTINCTROW
DIV
DOUBLE
DROP
DUAL
EACH
ELSE
ELSEIF
ENCLOSED
ESCAPED
EXISTS
EXIT
EXPLAIN
FALSE
FETCH
FLOAT
FLOAT4
FLOAT8
FOR
FORCE
FOREIGN
FROM
FULLTEXT
GRANT
GROUP
HAVING
HIGH_PRIORITY
HOUR_MICROSECOND
HOUR_MINUTE
HOUR_SECOND
IF
IGNORE
IN
INDEX
INFILE
INNER
INOUT
INSENSITIVE
INSERT
INT
INT1
INT2
INT3
INT4
INT8
INTEGER
INTERVAL
INTO
IS
ITERATE
JOIN
KEY
KEYS
KILL
LEADING
LEAVE
LEFT
LIKE
LIMIT
LINES
547
Tratamiento de palabras reservadas en MySQL
LOAD
LOCALTIME
LOCALTIMESTAMP
LOCK
LONG
LONGBLOB
LONGTEXT
LOOP
LOW_PRIORITY
MATCH
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
MIDDLEINT
MINUTE_MICROSECOND
MINUTE_SECOND
MOD
MODIFIES
NATURAL
NOT
NO_WRITE_TO_BINLOG
NULL
NUMERIC
ON
OPTIMIZE
OPTION
OPTIONALLY
OR
ORDER
OUT
OUTER
OUTFILE
PRECISION
PRIMARY
PROCEDURE
PURGE
READ
READS
REAL
REFERENCES
REGEXP
RELEASE
RENAME
REPEAT
REPLACE
REQUIRE
RESTRICT
RETURN
REVOKE
RIGHT
RLIKE
SCHEMA
SCHEMAS
SECOND_MICROSECOND
SELECT
SENSITIVE
SEPARATOR
SET
SHOW
SMALLINT
SONAME
SPATIAL
SPECIFIC
SQL
SQLEXCEPTION
SQLSTATE
SQLWARNING
SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT
SSL
STARTING
STRAIGHT_JOIN
TABLE
TERMINATED
THEN
TINYBLOB
TINYINT
TINYTEXT
TO
TRAILING
TRIGGER
TRUE
UNDO
UNION
UNIQUE
UNLOCK
UNSIGNED
UPDATE
USAGE
USE
USING
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
VALUES
VARBINARY
VARCHAR
VARCHARACTER
VARYING
WHEN
WHERE
WHILE
WITH
WRITE
XOR
YEAR_MONTH
ZEROFILL
Siguen las nuevas palabras reservadas de MySQL 5.0: ASENSITIVE
CALL
CONDITION
548
Tratamiento de palabras reservadas en MySQL
CONTINUE
CURSOR
DECLARE
DETERMINISTIC
EACH
ELSEIF
EXIT
FETCH
INOUT
INSENSITIVE
ITERATE
LEAVE
LOOP
MODIFIES
OUT
READS
RELEASE
REPEAT
RETURN
SCHEMA
SCHEMAS
SENSITIVE
SPECIFIC
SQL
SQLEXCEPTION
SQLSTATE
SQLWARNING
TRIGGER
UNDO
WHILE
MySQL permite que algunas palabras clave sean utilizadas como identificadores sin delimitar porque mucha gente las ha usado previamente. Por ejemplo: • ACTION • BIT • DATE • ENUM • NO • TEXT • TIME • TIMESTAMP
549