diff options
| -rw-r--r-- | BitKeeper/etc/logging_ok | 2 | ||||
| -rw-r--r-- | Docs/manual.texi | 5424 | ||||
| -rw-r--r-- | innobase/btr/btr0cur.c | 11 | ||||
| -rw-r--r-- | sql/ha_innobase.cc | 41 | ||||
| -rw-r--r-- | sql/ha_innobase.h | 1 |
5 files changed, 2868 insertions, 2611 deletions
diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok index 0bc2e507968..49e1ef3cafd 100644 --- a/BitKeeper/etc/logging_ok +++ b/BitKeeper/etc/logging_ok @@ -1,2 +1,4 @@ +heikki@donna.mysql.fi jani@hynda.mysql.fi +jcole@tetra.spaceapes.com tim@work.mysql.com diff --git a/Docs/manual.texi b/Docs/manual.texi index dad548f53f6..685933b67af 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -115,7 +115,6 @@ distribution for that version. * MySQL Optimization:: * Reference:: @strong{MySQL} language reference * Table types:: @strong{MySQL} table types -* Fulltext Search:: Fulltext Search * Maintenance:: Maintaining a @strong{MySQL} installation * Adding functions:: Adding new functions to @strong{MySQL} * Adding procedures:: Adding new procedures to @strong{MySQL} @@ -26601,30 +26600,6 @@ with the update log. @end menu -@node SET TRANSACTION, , SET OPTION, SET OPTION -@subsubsection @code{SET TRANSACTION} Syntax - -@findex ISOLATION LEVEL - -@example -SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL -[READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE] -@end example - -Sets the transaction isolation level for the global, whole session or -the next transaction. - -The default behavior is to set the isolation level for the next (not started) -transaction. - -If you set the @code{GLOBAL} privilege it will affect all new created threads. -You will need the @code{PROCESS} privilege to do do this. - -Setting the @code{SESSION} privilege will affect the following and all -future transactions. - -You can set the default isolation level for @code{mysqld} with -@code{--transaction-isolation=...}. @xref{Command-line options}. @node Disk issues, , Optimizing the Server, MySQL Optimization @@ -26865,7 +26840,7 @@ Things that are not yet supported: @item @code{mysqldump} doesn't include the symbolic links information in the output. @item -@code{BACKUP TABLE} and @code{RESTORE TABLE} doesn't use symbolic links. +@code{BACKUP TABLE} and @code{RESTORE TABLE} don't respect symbolic links. @end itemize @@ -26876,33 +26851,14 @@ Things that are not yet supported: @chapter MySQL Language Reference @menu -* Literals:: Literals: How to write strings and numbers -* Variables:: User variables +* Language Structure:: * Column types:: Column types * Functions:: Functions -* CREATE DATABASE:: @code{CREATE DATABASE} syntax -* DROP DATABASE:: @code{DROP DATABASE} syntax -* CREATE TABLE:: @code{CREATE TABLE} syntax -* ALTER TABLE:: @code{ALTER TABLE} syntax -* RENAME TABLE:: @code{RENAME TABLE} syntax -* DROP TABLE:: @code{DROP TABLE} syntax -* DELETE:: @code{DELETE} syntax -* TRUNCATE:: @code{TRUNCATE} syntax -* SELECT:: @code{SELECT} syntax -* JOIN:: @code{JOIN} syntax -* INSERT:: @code{INSERT} syntax -* REPLACE:: @code{REPLACE} syntax -* LOAD DATA:: @code{LOAD DATA INFILE} syntax -* UPDATE:: @code{UPDATE} syntax -* USE:: @code{USE} syntax -* DESCRIBE:: @code{DESCRIBE} syntax (Get information about names of columns) -* COMMIT:: @code{BEGIN/COMMIT/ROLLBACK} syntax -* LOCK TABLES:: @code{LOCK TABLES/UNLOCK TABLES} syntax -* CREATE INDEX:: @code{CREATE INDEX} syntax -* DROP INDEX:: @code{DROP INDEX} syntax -* Comments:: Comment syntax -* CREATE FUNCTION:: @code{CREATE FUNCTION} syntax -* Reserved words:: Is @strong{MySQL} picky about reserved words? +* Data Manipulation:: +* Data Definition:: +* Basic User Commands:: +* Transactional Commands:: +* Fulltext Search:: @end menu @strong{MySQL} has a very complex, but intuitive and easy to learn SQL @@ -26913,8 +26869,21 @@ included in @strong{MySQL}. In order to use this chapter effectively, you may find it useful to refer to the various indexes. -@node Literals, Variables, Reference, Reference -@section Literals: How to Write Strings and Numbers +@node Language Structure, Column types, Reference, Reference +@section Language Structure + +@menu +* Literals:: +* Legal names:: +* Name case sensitivity:: +* Variables:: +* Comments:: +* Reserved words:: +@end menu + + +@node Literals, Legal names, Language Structure, Language Structure +@subsection Literals: How to Write Strings and Numbers @cindex strings, defined @cindex strings, escaping characters @@ -26927,15 +26896,16 @@ may find it useful to refer to the various indexes. * Number syntax:: Numbers * Hexadecimal values:: Hexadecimal values * NULL values:: @code{NULL} values -* Legal names:: Database, Table, Index, Column, and Alias Names @end menu + This section describes the various ways to write strings and numbers in @strong{MySQL}. It also covers the various nuances and ``gotchas'' that you may run into when dealing with these basic types in @strong{MySQL}. + @node String syntax, Number syntax, Literals, Literals -@subsection Strings +@subsubsection Strings A string is a sequence of characters, surrounded by either single quote (@samp{'}) or double quote (@samp{"}) characters (only the single quote @@ -27092,13 +27062,15 @@ characters to the proper escape sequences. @xref{Perl DBI Class, , Perl You should use an escape function on any string that might contain any of the special characters listed above! + +@node Number syntax, Hexadecimal values, String syntax, Literals +@subsubsection Numbers + @cindex numbers @cindex valid numbers, examples @cindex integers @cindex floats @cindex negative values -@node Number syntax, Hexadecimal values, String syntax, Literals -@subsection Numbers Integers are represented as a sequence of digits. Floats use @samp{.} as a decimal separator. Either type of number may be preceded by @samp{-} to @@ -27123,9 +27095,11 @@ Examples of valid floating-point numbers: An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number. -@tindex hexadecimal values + @node Hexadecimal values, NULL values, Number syntax, Literals -@subsection Hexadecimal Values +@subsubsection Hexadecimal Values + +@tindex hexadecimal values @strong{MySQL} supports hexadecimal values. In number context these act like an integer (64-bit precision). In string context these act like a binary @@ -27140,9 +27114,11 @@ mysql> select 0x5061756c; Hexadecimal strings are often used by ODBC to give values for BLOB columns. + +@node NULL values, , Hexadecimal values, Literals +@subsubsection @code{NULL} Values + @tindex NULL value -@node NULL values, Legal names, Hexadecimal values, Literals -@subsection @code{NULL} Values The @code{NULL} value means ``no data'' and is different from values such as @code{0} for numeric types or the empty string for string types. @@ -27152,6 +27128,10 @@ as @code{0} for numeric types or the empty string for string types. or export formats (@code{LOAD DATA INFILE}, @code{SELECT ... INTO OUTFILE}). @xref{LOAD DATA, , @code{LOAD DATA}}. + +@node Legal names, Name case sensitivity, Literals, Language Structure +@subsection Database, Table, Index, Column, and Alias Names + @cindex names @cindex legal names @cindex databases, names @@ -27159,13 +27139,12 @@ or export formats (@code{LOAD DATA INFILE}, @code{SELECT ... INTO OUTFILE}). @cindex indexes, names @cindex columns, names @cindex aliases, names -@node Legal names, , NULL values, Literals -@subsection Database, Table, Index, Column, and Alias Names @menu * Name case sensitivity:: Case sensitivity in names @end menu + Database, table, index, column, and alias names all follow the same rules in @strong{MySQL}. @@ -27252,10 +27231,12 @@ The syntax @code{.tbl_name} means the table @code{tbl_name} in the current database. This syntax is accepted for ODBC compatibility, because some ODBC programs prefix table names with a @samp{.} character. + +@node Name case sensitivity, Variables, Legal names, Language Structure +@subsection Case Sensitivity in Names + @cindex names, case-sensitivity @cindex case-sensitivity, in names -@node Name case sensitivity, , Legal names, Legal names -@subsubsection Case Sensitivity in Names @cindex database names, case sensitivity @cindex table names, case sensitivity @cindex column names, case sensitivity @@ -27301,11 +27282,13 @@ table names to lower case on storage and lookup. Note that if you change this option, you need to first convert your old table names to lower case before starting @code{mysqld}. + +@node Variables, Comments, Name case sensitivity, Language Structure +@subsection User Variables + @cindex variables, user @cindex user variables @cindex names, variables -@node Variables, Column types, Literals, Reference -@section User Variables @strong{MySQL} supports thread-specific variables with the @code{@@variablename} syntax. A variable name may consist of @@ -27359,10 +27342,183 @@ SELECT (@@aa:=id) AS a, (@@aa+3) AS b FROM table_name HAVING b=5; The reason is that @code{@@aa} will not contain the value of the current row, but the value of @code{id} for the previous accepted row. + +@menu +* Comments:: +* Reserved words:: +@end menu + +@node Comments, Reserved words, Variables, Language Structure +@subsection Comment Syntax + +@findex Comment syntax + +@cindex comments, adding + +The @strong{MySQL} server supports the @code{# to end of line}, @code{-- +to end of line} and @code{/* in-line or multiple-line */} comment +styles: + +@example +mysql> select 1+1; # This comment continues to the end of line +mysql> select 1+1; -- This comment continues to the end of line +mysql> select 1 /* this is an in-line comment */ + 1; +mysql> select 1+ +/* +this is a +multiple-line comment +*/ +1; +@end example + +Note that the @code{--} comment style requires you to have at least one space +after the @code{--}! + +Although the server understands the comment syntax just described, +there are some limitations on the way that the @code{mysql} client +parses @code{/* ... */} comments: + +@itemize @bullet +@item +Single-quote and double-quote characters are taken to indicate the beginning +of a quoted string, even within a comment. If the quote is not matched by a +second quote within the comment, the parser doesn't realize the comment has +ended. If you are running @code{mysql} interactively, you can tell that it +has gotten confused like this because the prompt changes from @code{mysql>} +to @code{'>} or @code{">}. + +@item +A semicolon is taken to indicate the end of the current SQL statement +and anything following it to indicate the beginning of the next statement. +@end itemize + +These limitations apply both when you run @code{mysql} interactively +and when you put commands in a file and tell @code{mysql} to read its +input from that file with @code{mysql < some-file}. + +@strong{MySQL} doesn't support the @samp{--} ANSI SQL comment style. +@xref{Missing comments}. + + +@node Reserved words, , Comments, Language Structure +@subsection Is MySQL Picky About Reserved Words? + +@cindex keywords +@cindex reserved words, exceptions + +A common problem stems from trying to create a table with column names that +use the names of datatypes or functions built into @strong{MySQL}, such as +@code{TIMESTAMP} or @code{GROUP}. You're allowed to do it (for example, +@code{ABS} is an allowed column name), but whitespace is not allowed between +a function name and the @samp{(} when using functions whose names are also +column names. + +The following words are explicitly reserved in @strong{MySQL}. Most of +them are forbidden by ANSI SQL92 as column and/or table names +(for example, @code{group}). +A few are reserved because @strong{MySQL} needs them and is +(currently) using a @code{yacc} parser: + +@c This is fixed by including the symbols table from lex.h here and then running +@c fix-mysql-reserved-words in emacs (or let David do it): +@c (defun fix-mysql-reserved-words () +@c (interactive) +@c (let ((cnt 0)) +@c (insert "\n@item ") +@c (while (looking-at "[ \t]*{ +\"\\([^\"]+\\)\"[ \t]*,.*\n") +@c (replace-match "@code{\\1}") +@c (incf cnt) +@c (if (> cnt 3) +@c (progn +@c (setf cnt 0) +@c (insert "\n@item ")) +@c (insert " @tab "))))) +@c But remove the non alphanumeric entries by hand first. +@c Updated after 3.23.4 990928 by David + +@multitable @columnfractions .25 .25 .25 .25 +@item @code{action} @tab @code{add} @tab @code{aggregate} @tab @code{all} +@item @code{alter} @tab @code{after} @tab @code{and} @tab @code{as} +@item @code{asc} @tab @code{avg} @tab @code{avg_row_length} @tab @code{auto_increment} +@item @code{between} @tab @code{bigint} @tab @code{bit} @tab @code{binary} +@item @code{blob} @tab @code{bool} @tab @code{both} @tab @code{by} +@item @code{cascade} @tab @code{case} @tab @code{char} @tab @code{character} +@item @code{change} @tab @code{check} @tab @code{checksum} @tab @code{column} +@item @code{columns} @tab @code{comment} @tab @code{constraint} @tab @code{create} +@item @code{cross} @tab @code{current_date} @tab @code{current_time} @tab @code{current_timestamp} +@item @code{data} @tab @code{database} @tab @code{databases} @tab @code{date} +@item @code{datetime} @tab @code{day} @tab @code{day_hour} @tab @code{day_minute} +@item @code{day_second} @tab @code{dayofmonth} @tab @code{dayofweek} @tab @code{dayofyear} +@item @code{dec} @tab @code{decimal} @tab @code{default} @tab @code{delayed} +@item @code{delay_key_write} @tab @code{delete} @tab @code{desc} @tab @code{describe} +@item @code{distinct} @tab @code{distinctrow} @tab @code{double} @tab @code{drop} +@item @code{end} @tab @code{else} @tab @code{escape} @tab @code{escaped} +@item @code{enclosed} @tab @code{enum} @tab @code{explain} @tab @code{exists} +@item @code{fields} @tab @code{file} @tab @code{first} @tab @code{float} +@item @code{float4} @tab @code{float8} @tab @code{flush} @tab @code{foreign} +@item @code{from} @tab @code{for} @tab @code{full} @tab @code{function} +@item @code{global} @tab @code{grant} @tab @code{grants} @tab @code{group} +@item @code{having} @tab @code{heap} @tab @code{high_priority} @tab @code{hour} +@item @code{hour_minute} @tab @code{hour_second} @tab @code{hosts} @tab @code{identified} +@item @code{ignore} @tab @code{in} @tab @code{index} @tab @code{infile} +@item @code{inner} @tab @code{insert} @tab @code{insert_id} @tab @code{int} +@item @code{integer} @tab @code{interval} @tab @code{int1} @tab @code{int2} +@item @code{int3} @tab @code{int4} @tab @code{int8} @tab @code{into} +@item @code{if} @tab @code{is} @tab @code{isam} @tab @code{join} +@item @code{key} @tab @code{keys} @tab @code{kill} @tab @code{last_insert_id} +@item @code{leading} @tab @code{left} @tab @code{length} @tab @code{like} +@item @code{lines} @tab @code{limit} @tab @code{load} @tab @code{local} +@item @code{lock} @tab @code{logs} @tab @code{long} @tab @code{longblob} +@item @code{longtext} @tab @code{low_priority} @tab @code{max} @tab @code{max_rows} +@item @code{match} @tab @code{mediumblob} @tab @code{mediumtext} @tab @code{mediumint} +@item @code{middleint} @tab @code{min_rows} @tab @code{minute} @tab @code{minute_second} +@item @code{modify} @tab @code{month} @tab @code{monthname} @tab @code{myisam} +@item @code{natural} @tab @code{numeric} @tab @code{no} @tab @code{not} +@item @code{null} @tab @code{on} @tab @code{optimize} @tab @code{option} +@item @code{optionally} @tab @code{or} @tab @code{order} @tab @code{outer} +@item @code{outfile} @tab @code{pack_keys} @tab @code{partial} @tab @code{password} +@item @code{precision} @tab @code{primary} @tab @code{procedure} @tab @code{process} +@item @code{processlist} @tab @code{privileges} @tab @code{read} @tab @code{real} +@item @code{references} @tab @code{reload} @tab @code{regexp} @tab @code{rename} +@item @code{replace} @tab @code{restrict} @tab @code{returns} @tab @code{revoke} +@item @code{rlike} @tab @code{row} @tab @code{rows} @tab @code{second} +@item @code{select} @tab @code{set} @tab @code{show} @tab @code{shutdown} +@item @code{smallint} @tab @code{soname} @tab @code{sql_big_tables} @tab @code{sql_big_selects} +@item @code{sql_low_priority_updates} @tab @code{sql_log_off} @tab @code{sql_log_update} @tab @code{sql_select_limit} +@item @code{sql_small_result} @tab @code{sql_big_result} @tab @code{sql_warnings} @tab @code{straight_join} +@item @code{starting} @tab @code{status} @tab @code{string} @tab @code{table} +@item @code{tables} @tab @code{temporary} @tab @code{terminated} @tab @code{text} +@item @code{then} @tab @code{time} @tab @code{timestamp} @tab @code{tinyblob} +@item @code{tinytext} @tab @code{tinyint} @tab @code{trailing} @tab @code{to} +@item @code{type} @tab @code{use} @tab @code{using} @tab @code{unique} +@item @code{unlock} @tab @code{unsigned} @tab @code{update} @tab @code{usage} +@item @code{values} @tab @code{varchar} @tab @code{variables} @tab @code{varying} +@item @code{varbinary} @tab @code{with} @tab @code{write} @tab @code{when} +@item @code{where} @tab @code{year} @tab @code{year_month} @tab @code{zerofill} +@end multitable + +The following symbols (from the table above) are disallowed by ANSI SQL +but allowed by @strong{MySQL} as column/table names. This is because some +of these names are very natural names and a lot of people have already +used them. + +@itemize @bullet +@item @code{ACTION} +@item @code{BIT} +@item @code{DATE} +@item @code{ENUM} +@item @code{NO} +@item @code{TEXT} +@item @code{TIME} +@item @code{TIMESTAMP} +@end itemize + + +@node Column types, Functions, Language Structure, Reference +@section Column Types + @cindex columns, types @cindex types, columns -@node Column types, Functions, Variables, Reference -@section Column Types @strong{MySQL} supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) @@ -27689,116 +27845,16 @@ be chosen from the list of values @code{'value1'}, @code{'value2'}, @end table @menu -* Storage requirements:: Column type storage requirements * Numeric types:: Numeric types * Date and time types:: Date and time types * String types:: String types * Choosing types:: Choosing the right type for a column * Other-vendor column types:: Using column types from other database engines +* Storage requirements:: Column type storage requirements @end menu -@cindex storage requirements, column type -@cindex columns, storage requirements - -@node Storage requirements, Numeric types, Column types, Column types -@subsection Column Type Storage Requirements - -The storage requirements for each of the column types supported by -@strong{MySQL} are listed below by category. - -@cindex numeric types -@cindex types, numeric - -@subsubheading Storage requirements for numeric types -@multitable @columnfractions .5 .5 -@item @strong{Column type} @tab @strong{Storage required} -@item @code{TINYINT} @tab 1 byte -@item @code{SMALLINT} @tab 2 bytes -@item @code{MEDIUMINT} @tab 3 bytes -@item @code{INT} @tab 4 bytes -@item @code{INTEGER} @tab 4 bytes -@item @code{BIGINT} @tab 8 bytes -@item @code{FLOAT(X)} @tab 4 if X <= 24 or 8 if 25 <= X <= 53 -@item @code{FLOAT} @tab 4 bytes -@item @code{DOUBLE} @tab 8 bytes -@item @code{DOUBLE PRECISION} @tab 8 bytes -@item @code{REAL} @tab 8 bytes -@item @code{DECIMAL(M,D)} @tab @code{M+2} bytes if D > 0, @code{M+1} bytes if D = 0 (@code{D}+2, if @code{M < D}) -@item @code{NUMERIC(M,D)} @tab @code{M+2} bytes if D > 0, @code{M+1} bytes if D = 0 (@code{D}+2, if @code{M < D}) -@end multitable - -@cindex date types -@cindex time types -@cindex types, date -@cindex types, time - -@subsubheading Storage requirements for date and time types - -@multitable @columnfractions .5 .5 -@item @strong{Column type} @tab @strong{Storage required} -@item @code{DATE} @tab 3 bytes -@item @code{DATETIME} @tab 8 bytes -@item @code{TIMESTAMP} @tab 4 bytes -@item @code{TIME} @tab 3 bytes -@item @code{YEAR} @tab 1 byte -@end multitable - -@subsubheading Storage requirements for string types - -@multitable @columnfractions .5 .5 -@item @strong{Column type} @tab @strong{Storage required} -@item @code{CHAR(M)} @tab @code{M} bytes, @code{1 <= M <= 255} -@item @code{VARCHAR(M)} @tab @code{L}+1 bytes, where @code{L <= M} and -@code{1 <= M <= 255} -@item @code{TINYBLOB}, @code{TINYTEXT} @tab @code{L}+1 bytes, -where @code{L} < 2^8 -@item @code{BLOB}, @code{TEXT} @tab @code{L}+2 bytes, -where @code{L} < 2^16 -@item @code{MEDIUMBLOB}, @code{MEDIUMTEXT} @tab @code{L}+3 bytes, -where @code{L} < 2^24 -@item @code{LONGBLOB}, @code{LONGTEXT} @tab @code{L}+4 bytes, -where @code{L} < 2^32 -@item @code{ENUM('value1','value2',...)} @tab 1 or 2 bytes, depending on -the number of enumeration values (65535 values maximum) -@item @code{SET('value1','value2',...)} @tab 1, 2, 3, 4 or 8 bytes, depending -on the number of set members (64 members maximum) -@end multitable - -@cindex BLOB, size -@cindex TEXT, size -@cindex VARCHAR, size -@code{VARCHAR} and the @code{BLOB} and @code{TEXT} types are variable-length -types, for which the storage requirements depend on the actual length of -column values (represented by @code{L} in the preceding table), rather than -on the type's maximum possible size. For example, a @code{VARCHAR(10)} -column can hold a string with a maximum length of 10 characters. The actual -storage required is the length of the string (@code{L}), plus 1 byte to -record the length of the string. For the string @code{'abcd'}, @code{L} is 4 -and the storage requirement is 5 bytes. - -The @code{BLOB} and @code{TEXT} types require 1, 2, 3, or 4 bytes to record -the length of the column value, depending on the maximum possible length of -the type. @xref{BLOB}. - -If a table includes any variable-length column types, the record format will -also be variable-length. Note that when a table is created, @strong{MySQL} -may, under certain conditions, change a column from a variable-length type to a -fixed-length type, or vice-versa. @xref{Silent column changes}. - -@cindex ENUM, size -The size of an @code{ENUM} object is determined by the number of -different enumeration values. One byte is used for enumerations with up -to 255 possible values. Two bytes are used for enumerations with up to -65535 values. @xref{ENUM}. - -@cindex SET, size -The size of a @code{SET} object is determined by the number of different -set members. If the set size is @code{N}, the object occupies @code{(N+7)/8} -bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A @code{SET} can have a maximum -of 64 members. @xref{SET}. - -@node Numeric types, Date and time types, Storage requirements, Column types +@node Numeric types, Date and time types, Column types, Column types @subsection Numeric Types @strong{MySQL} supports all of the ANSI/ISO SQL92 numeric types. These @@ -27924,11 +27980,13 @@ Conversions that occur due to clipping are reported as ``warnings'' for @code{ALTER TABLE}, @code{LOAD DATA INFILE}, @code{UPDATE}, and multi-row @code{INSERT} statements. -@cindex types, Date and Time -@cindex Date and Time types + @node Date and time types, String types, Numeric types, Column types @subsection Date and Time Types +@cindex types, Date and Time +@cindex Date and Time types + @menu * Y2K issues:: Y2K issues and date types * DATETIME:: The @code{DATETIME}, @code{DATE} and @code{TIMESTAMP} types @@ -27936,6 +27994,7 @@ multi-row @code{INSERT} statements. * YEAR:: The @code{YEAR} type @end menu + The date and time types are @code{DATETIME}, @code{DATE}, @code{TIMESTAMP}, @code{TIME}, and @code{YEAR}. Each of these has a range of legal values, as well as a ``zero'' value that is used when you @@ -28005,11 +28064,13 @@ automatically to @code{NULL} in @strong{MyODBC} Version 2.50.12 and above, because ODBC can't handle such values. @end itemize -@cindex Year 2000 issues -@cindex date types, Y2K issues + @node Y2K issues, DATETIME, Date and time types, Date and time types @subsubsection Y2K Issues and Date Types +@cindex Year 2000 issues +@cindex date types, Y2K issues + @strong{MySQL} itself is Y2K-safe (@pxref{Year 2000 compliance}), but input values presented to @strong{MySQL} may not be. Any input containing 2-digit year values is ambiguous, because the century is unknown. @@ -28041,11 +28102,13 @@ Note also that some functions like @code{MIN()} and @code{MAX()} will convert a case is to convert the @code{TIMESTAMP/DATE} to 4-digit year format or use something like @code{MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS))}. + +@node DATETIME, TIME, Y2K issues, Date and time types +@subsubsection The @code{DATETIME}, @code{DATE}, and @code{TIMESTAMP} Types + @tindex DATETIME @tindex DATE @tindex TIMESTAMP -@node DATETIME, TIME, Y2K issues, Date and time types -@subsubsection The @code{DATETIME}, @code{DATE}, and @code{TIMESTAMP} Types The @code{DATETIME}, @code{DATE}, and @code{TIMESTAMP} types are related. This section describes their characteristics, how they are similar, and how @@ -28302,10 +28365,12 @@ Year values in the range @code{70-99} are converted to @code{1970-1999}. @end itemize @end itemize -@tindex TIME + @node TIME, YEAR, DATETIME, Date and time types @subsubsection The @code{TIME} Type +@tindex TIME + @strong{MySQL} retrieves and displays @code{TIME} values in @code{'HH:MM:SS'} format (or @code{'HHH:MM:SS'} format for large hours values). @code{TIME} values may range from @code{'-838:59:59'} to @code{'838:59:59'}. The reason @@ -28371,10 +28436,12 @@ because @code{'00:00:00'} is itself a legal @code{TIME} value, there is no way to tell, from a value of @code{'00:00:00'} stored in a table, whether the original value was specified as @code{'00:00:00'} or whether it was illegal. -@tindex YEAR + @node YEAR, , TIME, Date and time types @subsubsection The @code{YEAR} Type +@tindex YEAR + The @code{YEAR} type is a 1-byte type used for representing years. @strong{MySQL} retrieves and displays @code{YEAR} values in @code{YYYY} @@ -28412,13 +28479,16 @@ in a @code{YEAR} context, such as @code{NOW()}. Illegal @code{YEAR} values are converted to @code{0000}. -@cindex types, strings -@cindex string types + @node String types, Choosing types, Date and time types, Column types @subsection String Types +@cindex types, strings +@cindex string types + @tindex CHAR @tindex VARCHAR + @menu * CHAR:: The @code{CHAR} and @code{VARCHAR} types * BLOB:: The @code{BLOB} and @code{TEXT} types @@ -28426,10 +28496,12 @@ Illegal @code{YEAR} values are converted to @code{0000}. * SET:: The @code{SET} type @end menu + The string types are @code{CHAR}, @code{VARCHAR}, @code{BLOB}, @code{TEXT}, @code{ENUM}, and @code{SET}. This section describes how these types work, their storage requirements, and how to use them in your queries. + @node CHAR, BLOB, String types, String types @subsubsection The @code{CHAR} and @code{VARCHAR} Types @@ -28489,11 +28561,13 @@ The @code{BINARY} attribute is sticky. This means that if a column marked column at table creation time. @xref{Silent column changes}. -@tindex BLOB -@tindex TEXT + @node BLOB, ENUM, CHAR, String types @subsubsection The @code{BLOB} and @code{TEXT} Types +@tindex BLOB +@tindex TEXT + A @code{BLOB} is a binary large object that can hold a variable amount of data. The four @code{BLOB} types @code{TINYBLOB}, @code{BLOB}, @code{MEDIUMBLOB}, and @code{LONGBLOB} differ only in the maximum length of @@ -28576,10 +28650,12 @@ internally by a separately allocated object. This is in contrast to all other column types, for which storage is allocated once per column when the table is opened. -@tindex ENUM + @node ENUM, SET, BLOB, String types @subsubsection The @code{ENUM} Type +@tindex ENUM + An @code{ENUM} is a string object whose value normally is chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time. @@ -28663,10 +28739,12 @@ If you want to get all possible values for an @code{ENUM} column, you should use: @code{SHOW COLUMNS FROM table_name LIKE enum_column_name} and parse the @code{ENUM} definition in the second column. -@tindex SET + @node SET, , ENUM, String types @subsubsection The @code{SET} Type +@tindex SET + A @code{SET} is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified when the table is created. @code{SET} column values that consist of multiple set members @@ -28748,11 +28826,13 @@ If you want to get all possible values for a @code{SET} column, you should use: @code{SHOW COLUMNS FROM table_name LIKE set_column_name} and parse the @code{SET} definition in the second column. -@cindex types, columns -@cindex choosing types + @node Choosing types, Other-vendor column types, String types, Column types @subsection Choosing the Right Type for a Column +@cindex types, columns +@cindex choosing types + For the most efficient use of storage, try to use the most precise type in all cases. For example, if an integer column will be used for values in the range between @code{1} and @code{99999}, @code{MEDIUMINT UNSIGNED} is the @@ -28768,7 +28848,7 @@ in a @code{BIGINT}. This allows you to do all calculations with integers and convert results back to floating-point values only when necessary. -@node Other-vendor column types, , Choosing types, Column types +@node Other-vendor column types, Storage requirements, Choosing types, Column types @subsection Using Column Types from Other Database Engines @cindex types, portability @@ -28802,10 +28882,114 @@ with types used by other vendors and then issue a @code{DESCRIBE tbl_name} statement, @strong{MySQL} reports the table structure using the equivalent @strong{MySQL} types. -@cindex functions for @code{SELECT} and @code{WHERE} clauses -@node Functions, CREATE DATABASE, Column types, Reference + +@node Storage requirements, , Other-vendor column types, Column types +@subsection Column Type Storage Requirements + +@cindex storage requirements, column type +@cindex columns, storage requirements + +The storage requirements for each of the column types supported by +@strong{MySQL} are listed below by category. + +@cindex numeric types +@cindex types, numeric + +@subsubheading Storage requirements for numeric types + +@multitable @columnfractions .5 .5 +@item @strong{Column type} @tab @strong{Storage required} +@item @code{TINYINT} @tab 1 byte +@item @code{SMALLINT} @tab 2 bytes +@item @code{MEDIUMINT} @tab 3 bytes +@item @code{INT} @tab 4 bytes +@item @code{INTEGER} @tab 4 bytes +@item @code{BIGINT} @tab 8 bytes +@item @code{FLOAT(X)} @tab 4 if X <= 24 or 8 if 25 <= X <= 53 +@item @code{FLOAT} @tab 4 bytes +@item @code{DOUBLE} @tab 8 bytes +@item @code{DOUBLE PRECISION} @tab 8 bytes +@item @code{REAL} @tab 8 bytes +@item @code{DECIMAL(M,D)} @tab @code{M+2} bytes if D > 0, @code{M+1} bytes if D = 0 (@code{D}+2, if @code{M < D}) +@item @code{NUMERIC(M,D)} @tab @code{M+2} bytes if D > 0, @code{M+1} bytes if D = 0 (@code{D}+2, if @code{M < D}) +@end multitable + +@cindex date types +@cindex time types +@cindex types, date +@cindex types, time + +@subsubheading Storage requirements for date and time types + +@multitable @columnfractions .5 .5 +@item @strong{Column type} @tab @strong{Storage required} +@item @code{DATE} @tab 3 bytes +@item @code{DATETIME} @tab 8 bytes +@item @code{TIMESTAMP} @tab 4 bytes +@item @code{TIME} @tab 3 bytes +@item @code{YEAR} @tab 1 byte +@end multitable + +@subsubheading Storage requirements for string types + +@multitable @columnfractions .5 .5 +@item @strong{Column type} @tab @strong{Storage required} +@item @code{CHAR(M)} @tab @code{M} bytes, @code{1 <= M <= 255} +@item @code{VARCHAR(M)} @tab @code{L}+1 bytes, where @code{L <= M} and +@code{1 <= M <= 255} +@item @code{TINYBLOB}, @code{TINYTEXT} @tab @code{L}+1 bytes, +where @code{L} < 2^8 +@item @code{BLOB}, @code{TEXT} @tab @code{L}+2 bytes, +where @code{L} < 2^16 +@item @code{MEDIUMBLOB}, @code{MEDIUMTEXT} @tab @code{L}+3 bytes, +where @code{L} < 2^24 +@item @code{LONGBLOB}, @code{LONGTEXT} @tab @code{L}+4 bytes, +where @code{L} < 2^32 +@item @code{ENUM('value1','value2',...)} @tab 1 or 2 bytes, depending on +the number of enumeration values (65535 values maximum) +@item @code{SET('value1','value2',...)} @tab 1, 2, 3, 4 or 8 bytes, depending +on the number of set members (64 members maximum) +@end multitable + +@cindex BLOB, size +@cindex TEXT, size +@cindex VARCHAR, size +@code{VARCHAR} and the @code{BLOB} and @code{TEXT} types are variable-length +types, for which the storage requirements depend on the actual length of +column values (represented by @code{L} in the preceding table), rather than +on the type's maximum possible size. For example, a @code{VARCHAR(10)} +column can hold a string with a maximum length of 10 characters. The actual +storage required is the length of the string (@code{L}), plus 1 byte to +record the length of the string. For the string @code{'abcd'}, @code{L} is 4 +and the storage requirement is 5 bytes. + +The @code{BLOB} and @code{TEXT} types require 1, 2, 3, or 4 bytes to record +the length of the column value, depending on the maximum possible length of +the type. @xref{BLOB}. + +If a table includes any variable-length column types, the record format will +also be variable-length. Note that when a table is created, @strong{MySQL} +may, under certain conditions, change a column from a variable-length type to a +fixed-length type, or vice-versa. @xref{Silent column changes}. + +@cindex ENUM, size +The size of an @code{ENUM} object is determined by the number of +different enumeration values. One byte is used for enumerations with up +to 255 possible values. Two bytes are used for enumerations with up to +65535 values. @xref{ENUM}. + +@cindex SET, size +The size of a @code{SET} object is determined by the number of different +set members. If the set size is @code{N}, the object occupies @code{(N+7)/8} +bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A @code{SET} can have a maximum +of 64 members. @xref{SET}. + + +@node Functions, Data Manipulation, Column types, Reference @section Functions for Use in @code{SELECT} and @code{WHERE} Clauses +@cindex functions for @code{SELECT} and @code{WHERE} clauses + A @code{select_expression} or @code{where_definition} in a SQL statement can consist of any expression using the functions described below. @@ -28847,229 +29031,57 @@ mysql> select MOD(29,9); @end example @menu -* Grouping functions:: Grouping functions -* Arithmetic functions:: Normal arithmetic operations -* Bit functions:: Bit functions -* Logical functions:: Logical operations -* Comparison functions:: Comparison operators -* String comparison functions:: String comparison functions -* Casts:: Cast operators -* Control flow functions:: Control flow functions -* Mathematical functions:: Mathematical functions +* Non-typed Operators:: * String functions:: String functions +* Numeric Functions:: * Date and time functions:: Date and time functions -* Miscellaneous functions:: Miscellaneous functions -* Group by functions:: Functions for @code{GROUP BY} clause +* Other Functions:: @end menu -@cindex functions, grouping -@cindex grouping, expressions -@node Grouping functions, Arithmetic functions, Functions, Functions -@subsection Grouping Functions -@table @code - -@findex () (parentheses) -@findex parentheses ( and ) -@item ( ... ) -Parentheses. Use these to force the order of evaluation in an expression: -@example -mysql> select 1+2*3; - -> 7 -mysql> select (1+2)*3; - -> 9 -@end example -@end table - -@node Arithmetic functions, Bit functions, Grouping functions, Functions -@subsection Normal Arithmetic Operations - -The usual arithmetic operators are available. Note that in the case of -@samp{-}, @samp{+}, and @samp{*}, the result is calculated with -@code{BIGINT} (64-bit) precision if both arguments are integers! - -@cindex operations, arithmetic -@cindex arithmetic expressions -@table @code -@findex + (addition) -@findex addition (+) -@item + -Addition: -@example -mysql> select 3+5; - -> 8 -@end example - -@findex - (subtraction) -@findex subtraction (-) -@item - -Subtraction: -@example -mysql> select 3-5; - -> -2 -@end example - -@findex * (multiplication) -@findex multiplication (*) -@item * -Multiplication: -@example -mysql> select 3*5; - -> 15 -mysql> select 18014398509481984*18014398509481984.0; - -> 324518553658426726783156020576256.0 -mysql> select 18014398509481984*18014398509481984; - -> 0 -@end example - -The result of the last expression is incorrect because the result of the integer -multiplication exceeds the 64-bit range of @code{BIGINT} calculations. - -@findex / (division) -@findex division (/) -@item / -Division: -@example -mysql> select 3/5; - -> 0.60 -@end example - -Division by zero produces a @code{NULL} result: - -@example -mysql> select 102/(1-1); - -> NULL -@end example -A division will be calculated with @code{BIGINT} arithmetic only if performed -in a context where its result is converted to an integer! -@end table +@node Non-typed Operators, String functions, Functions, Functions +@subsection Non-Type-Specific Operators and Functions -@findex arithmetic functions -@findex bit functions -@findex functions, arithmetic -@findex functions, bit -@node Bit functions, Logical functions, Arithmetic functions, Functions -@subsection Bit Functions +@menu +* Parenthesis:: +* Comparison Operators:: +* Logical Operators:: +* Control flow functions:: +@end menu -@strong{MySQL} uses @code{BIGINT} (64-bit) arithmetic for bit operations, so -these operators have a maximum range of 64 bits. -@table @code -@findex | (bitwise OR) -@findex OR, bitwise -@item | -Bitwise OR: -@example -mysql> select 29 | 15; - -> 31 -@end example +@node Parenthesis, Comparison Operators, Non-typed Operators, Non-typed Operators +@subsubsection Parenthesis -@findex & (bitwise AND) -@findex AND, bitwise -@item & -Bitwise AND: -@example -mysql> select 29 & 15; - -> 13 -@end example +@findex () (parentheses) +@findex parentheses ( and ) -@findex << (left shift) -@item << -Shifts a longlong (@code{BIGINT}) number to the left: -@example -mysql> select 1 << 2; - -> 4 -@end example +@cindex functions, grouping +@cindex grouping, expressions -@findex >> (right shift) -@item >> -Shifts a longlong (@code{BIGINT}) number to the right: @example -mysql> select 4 >> 2; - -> 1 +( ... ) @end example -@findex ~ -@item ~ -Invert all bits: -@example -mysql> select 5 & ~1; - -> 4 -@end example +Use parenthesis to force the order of evaluation in an expression. For +example: -@findex BIT_COUNT() -@item BIT_COUNT(N) -Returns the number of bits that are set in the argument @code{N}: @example -mysql> select BIT_COUNT(29); - -> 4 +mysql> select 1+2*3; + -> 7 +mysql> select (1+2)*3; + -> 9 @end example -@end table - -@findex Logical functions -@findex Functions, logical -@node Logical functions, Comparison functions, Bit functions, Functions -@subsection Logical Operations - -All logical functions return @code{1} (TRUE), @code{0} (FALSE) or -@code{NULL} (unknown, which is in most cases the same as FALSE): -@table @code -@findex NOT, logical -@findex ! (logical NOT) -@item NOT -@itemx ! -Logical NOT. Returns @code{1} if the argument is @code{0}, otherwise returns -@code{0}. -Exception: @code{NOT NULL} returns @code{NULL}: -@example -mysql> select NOT 1; - -> 0 -mysql> select NOT NULL; - -> NULL -mysql> select ! (1+1); - -> 0 -mysql> select ! 1+1; - -> 1 -@end example -The last example returns @code{1} because the expression evaluates -the same way as @code{(!1)+1}. -@findex OR, logical -@findex || (logical OR) -@item OR -@itemx || -Logical OR. Returns @code{1} if either argument is not @code{0} and not -@code{NULL}: -@example -mysql> select 1 || 0; - -> 1 -mysql> select 0 || 0; - -> 0 -mysql> select 1 || NULL; - -> 1 -@end example +@node Comparison Operators, Logical Operators, Parenthesis, Non-typed Operators +@subsubsection Comparison Operators -@findex AND, logical -@findex && (logical AND) -@item AND -@itemx && -Logical AND. Returns @code{0} if either argument is @code{0} or @code{NULL}, -otherwise returns @code{1}: -@example -mysql> select 1 && NULL; - -> 0 -mysql> select 1 && 0; - -> 0 -@end example -@end table +@findex comparison operators @cindex casts @cindex type conversions -@findex comparison operators -@node Comparison functions, String comparison functions, Logical functions, Functions -@subsection Comparison Operators Comparison operations result in a value of @code{1} (TRUE), @code{0} (FALSE), or @code{NULL}. These functions work for both numbers and strings. Strings @@ -29318,193 +29330,73 @@ mysql> select INTERVAL(22, 23, 30, 44, 200); @end example @end table -@findex string comparison functions -@findex functions, string comparison -@node String comparison functions, Casts, Comparison functions, Functions -@subsection String Comparison Functions - -@cindex case sensitivity, in string comparisons -@cindex string comparisons, case sensitivity -Normally, if any expression in a string comparison is case sensitive, the -comparison is performed in case-sensitive fashion. - -@table @code -@findex LIKE -@item expr LIKE pat [ESCAPE 'escape-char'] -Pattern matching using -SQL simple regular expression comparison. Returns @code{1} (TRUE) or @code{0} -(FALSE). With @code{LIKE} you can use the following two wild-card characters -in the pattern: - -@multitable @columnfractions .1 .9 -@item @code{%} @tab Matches any number of characters, even zero characters -@item @code{_} @tab Matches exactly one character -@end multitable -@example -mysql> select 'David!' LIKE 'David_'; - -> 1 -mysql> select 'David!' LIKE '%D%v%'; - -> 1 -@end example +@node Logical Operators, Control flow functions, Comparison Operators, Non-typed Operators +@subsubsection Logical Operators -To test for literal instances of a wild-card character, precede the character -with the escape character. If you don't specify the @code{ESCAPE} character, -@samp{\} is assumed: +@findex Logical functions +@findex Functions, logical -@multitable @columnfractions .1 .9 -@item @code{\%} @tab Matches one @code{%} character -@item @code{\_} @tab Matches one @code{_} character -@end multitable +All logical functions return @code{1} (TRUE), @code{0} (FALSE) or +@code{NULL} (unknown, which is in most cases the same as FALSE): +@table @code +@findex NOT, logical +@findex ! (logical NOT) +@item NOT +@itemx ! +Logical NOT. Returns @code{1} if the argument is @code{0}, otherwise returns +@code{0}. +Exception: @code{NOT NULL} returns @code{NULL}: @example -mysql> select 'David!' LIKE 'David\_'; +mysql> select NOT 1; -> 0 -mysql> select 'David_' LIKE 'David\_'; - -> 1 -@end example - -To specify a different escape character, use the @code{ESCAPE} clause: - -@example -mysql> select 'David_' LIKE 'David|_' ESCAPE '|'; +mysql> select NOT NULL; + -> NULL +mysql> select ! (1+1); + -> 0 +mysql> select ! 1+1; -> 1 @end example +The last example returns @code{1} because the expression evaluates +the same way as @code{(!1)+1}. -The following two statements illustrate that string comparisons are -case insensitive unless one of the operands is a binary string: - +@findex OR, logical +@findex || (logical OR) +@item OR +@itemx || +Logical OR. Returns @code{1} if either argument is not @code{0} and not +@code{NULL}: @example -mysql> select 'abc' LIKE 'ABC'; +mysql> select 1 || 0; -> 1 -mysql> SELECT 'abc' LIKE BINARY 'ABC'; +mysql> select 0 || 0; -> 0 -@end example - -@code{LIKE} is allowed on numeric expressions! (This is a @strong{MySQL} -extension to the ANSI SQL @code{LIKE}.) - -@example -mysql> select 10 LIKE '1%'; +mysql> select 1 || NULL; -> 1 -@end example - -Note: Because @strong{MySQL} uses the C escape syntax in strings (for example, -@samp{\n}), you must double any @samp{\} that you use in your @code{LIKE} -strings. For example, to search for @samp{\n}, specify it as @samp{\\n}. To -search for @samp{\}, specify it as @samp{\\\\} (the backslashes are stripped -once by the parser and another time when the pattern match is done, leaving -a single backslash to be matched). - -@findex NOT LIKE -@item expr NOT LIKE pat [ESCAPE 'escape-char'] -Same as @code{NOT (expr LIKE pat [ESCAPE 'escape-char'])}. - -@cindex mSQL compatibility -@cindex compatibility, with mSQL -@findex REGEXP -@findex RLIKE -@item expr REGEXP pat -@itemx expr RLIKE pat -Performs a pattern match of a string expression @code{expr} against a pattern -@code{pat}. The pattern can be an extended regular expression. -@xref{Regexp}. Returns @code{1} if @code{expr} matches @code{pat}, otherwise -returns @code{0}. @code{RLIKE} is a synonym for @code{REGEXP}, provided for -@code{mSQL} compatibility. Note: Because @strong{MySQL} uses the C escape -syntax in strings (for example, @samp{\n}), you must double any @samp{\} that -you use in your @code{REGEXP} strings. As of @strong{MySQL} Version 3.23.4, -@code{REGEXP} is case insensitive for normal (not binary) strings: -@example -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 @end example -@item -@code{REGEXP} and @code{RLIKE} use the current character set (ISO-8859-1 -Latin1 by default) when deciding the type of a character. - -@findex NOT REGEXP -@item expr NOT REGEXP pat -@itemx expr NOT RLIKE pat -Same as @code{NOT (expr REGEXP pat)}. - -@findex STRCMP() -@item STRCMP(expr1,expr2) -@code{STRCMP()} -returns @code{0} if the strings are the same, @code{-1} if the first -argument is smaller than the second according to the current sort order, -and @code{1} otherwise: - +@findex AND, logical +@findex && (logical AND) +@item AND +@itemx && +Logical AND. Returns @code{0} if either argument is @code{0} or @code{NULL}, +otherwise returns @code{1}: @example -mysql> select STRCMP('text', 'text2'); - -> -1 -mysql> select STRCMP('text2', 'text'); - -> 1 -mysql> select STRCMP('text', 'text'); +mysql> select 1 && NULL; -> 0 -@end example - -@findex MATCH ... AGAINST() -@item MATCH (col1,col2,...) AGAINST (expr) -@code{MATCH ... AGAINST()} is used for full-text search and returns -relevance - similarity measure between the text in columns -@code{(col1,col2,...)} and the query @code{expr}. Relevance is a -positive floating-point number. Zero relevance means no similarity. -For @code{MATCH ... AGAINST()} to work, a @strong{FULLTEXT} index -must be created first. @xref{CREATE TABLE, , @code{CREATE TABLE}}. -@code{MATCH ... AGAINST()} is available in @strong{MySQL} Version -3.23.23 or later. For details and usage examples -@pxref{Fulltext Search}. -@end table - -@findex casts -@cindex cast operators -@cindex operators, cast -@node Casts, Control flow functions, String comparison functions, Functions -@subsection Cast Operators - -@table @code -@findex BINARY -@item @code{BINARY} -The @code{BINARY} operator casts the string following it to a binary string. -This is an easy way to force a column comparison to be case sensitive even -if the column isn't defined as @code{BINARY} or @code{BLOB}: -@example -mysql> select "a" = "A"; - -> 1 -mysql> select BINARY "a" = "A"; +mysql> select 1 && 0; -> 0 @end example - -@code{BINARY} was introduced in @strong{MySQL} Version 3.23.0. - -Note that in some context @strong{MySQL} will not be able to use the -index efficiently when you cast an indexed column to @code{BINARY}. @end table -If you want to compare a blob case-insensitively you can always convert -the blob to upper case before doing the comparison: - -@example -SELECT 'A' LIKE UPPER(blob_col) FROM table_name; -@end example -We plan to soon introduce casting between different character sets to -make string comparison even more flexible. +@node Control flow functions, , Logical Operators, Non-typed Operators +@subsubsection Control Flow Functions @findex control flow functions @findex functions, control flow -@node Control flow functions, Mathematical functions, Casts, Functions -@subsection Control Flow Functions @table @code @cindex @code{NULL}, testing for null @@ -29606,400 +29498,12 @@ The type of the return value (@code{INTEGER}, @code{DOUBLE} or @code{STRING}) is the same as the type of the first returned value (the expression after the first @code{THEN}). -@findex mathematical functions -@findex functions, mathematical -@node Mathematical functions, String functions, Control flow functions, Functions -@subsection Mathematical Functions -All mathematical functions return @code{NULL} in case of an error. - -@table @code -@findex - (unary minus) -@findex minus, unary (-) -@findex unary minus (-) -@item - -Unary minus. Changes the sign of the argument: -@example -mysql> select - 2; - -> -2 -@end example - -Note that if this operator is used with a @code{BIGINT}, the return value is a -@code{BIGINT}! This means that you should avoid using @code{-} on integers that -may have the value of @code{-2^63}! - -@findex ABS() -@item ABS(X) -Returns the absolute value of @code{X}: -@example -mysql> select ABS(2); - -> 2 -mysql> select ABS(-32); - -> 32 -@end example - -This function is safe to use with @code{BIGINT} values. - -@findex SIGN() -@item SIGN(X) -Returns the sign of the argument as @code{-1}, @code{0}, or @code{1}, depending -on whether @code{X} is negative, zero, or positive: -@example -mysql> select SIGN(-32); - -> -1 -mysql> select SIGN(0); - -> 0 -mysql> select SIGN(234); - -> 1 -@end example - -@findex MOD() -@findex % (modulo) -@findex modulo (%) -@item MOD(N,M) -@itemx % -Modulo (like the @code{%} operator in C). -Returns the remainder of @code{N} divided by @code{M}: -@example -mysql> select MOD(234, 10); - -> 4 -mysql> select 253 % 7; - -> 1 -mysql> select MOD(29,9); - -> 2 -@end example - -This function is safe to use with @code{BIGINT} values. - -@findex FLOOR() -@item FLOOR(X) -Returns the largest integer value not greater than @code{X}: -@example -mysql> select FLOOR(1.23); - -> 1 -mysql> select FLOOR(-1.23); - -> -2 -@end example - -Note that the return value is converted to a @code{BIGINT}! - -@findex CEILING() -@item CEILING(X) -Returns the smallest integer value not less than @code{X}: -@example -mysql> select CEILING(1.23); - -> 2 -mysql> select CEILING(-1.23); - -> -1 -@end example - -Note that the return value is converted to a @code{BIGINT}! - -@findex ROUND() -@item ROUND(X) -Returns the argument @code{X}, rounded to the nearest integer: -@example -mysql> select ROUND(-1.23); - -> -1 -mysql> select ROUND(-1.58); - -> -2 -mysql> select ROUND(1.58); - -> 2 -@end example -Note that the behavior of @code{ROUND()} when the argument -is half way between two integers depends on the C library -implementation. Some round to the nearest even number, -always up, always down, or always towards zero. If you need -one kind of rounding, you should use a well-defined function -like @code{TRUNCATE()} or @code{FLOOR()} instead. - -@findex ROUND() -@item ROUND(X,D) -Returns the argument @code{X}, rounded to a number with @code{D} decimals. -If @code{D} is @code{0}, the result will have no decimal point or fractional -part: - -@example -mysql> select ROUND(1.298, 1); - -> 1.3 -mysql> select ROUND(1.298, 0); - -> 1 -@end example - -@findex EXP() -@item EXP(X) -Returns the value of @code{e} (the base of natural logarithms) raised to -the power of @code{X}: -@example -mysql> select EXP(2); - -> 7.389056 -mysql> select EXP(-2); - -> 0.135335 -@end example -@findex LOG() -@item LOG(X) -Returns the natural logarithm of @code{X}: -@example -mysql> select LOG(2); - -> 0.693147 -mysql> select LOG(-2); - -> NULL -@end example -If you want the log of a number @code{X} to some arbitary base @code{B}, use -the formula @code{LOG(X)/LOG(B)}. - -@findex LOG10() -@item LOG10(X) -Returns the base-10 logarithm of @code{X}: -@example -mysql> select LOG10(2); - -> 0.301030 -mysql> select LOG10(100); - -> 2.000000 -mysql> select LOG10(-100); - -> NULL -@end example - -@findex POW() -@findex POWER() -@item POW(X,Y) -@itemx POWER(X,Y) -Returns the value of @code{X} raised to the power of @code{Y}: -@example -mysql> select POW(2,2); - -> 4.000000 -mysql> select POW(2,-2); - -> 0.250000 -@end example - -@findex SQRT() -@item SQRT(X) -Returns the non-negative square root of @code{X}: -@example -mysql> select SQRT(4); - -> 2.000000 -mysql> select SQRT(20); - -> 4.472136 -@end example - -@findex PI() -@item PI() -Returns the value of PI. The default shown number of decimals is 5, but -@strong{MySQL} internally uses the full double precession for PI. -@example -mysql> select PI(); - -> 3.141593 -mysql> SELECT PI()+0.000000000000000000; - -> 3.141592653589793116 -@end example - -@findex COS() -@item COS(X) -Returns the cosine of @code{X}, where @code{X} is given in radians: -@example -mysql> select COS(PI()); - -> -1.000000 -@end example - -@findex SIN() -@item SIN(X) -Returns the sine of @code{X}, where @code{X} is given in radians: -@example -mysql> select SIN(PI()); - -> 0.000000 -@end example - -@findex TAN() -@item TAN(X) -Returns the tangent of @code{X}, where @code{X} is given in radians: -@example -mysql> select TAN(PI()+1); - -> 1.557408 -@end example - -@findex ACOS() -@item ACOS(X) -Returns the arc cosine of @code{X}, that is, the value whose cosine is -@code{X}. Returns @code{NULL} if @code{X} is not in the range @code{-1} to -@code{1}: -@example -mysql> select ACOS(1); - -> 0.000000 -mysql> select ACOS(1.0001); - -> NULL -mysql> select ACOS(0); - -> 1.570796 -@end example - -@findex ASIN() -@item ASIN(X) -Returns the arc sine of @code{X}, that is, the value whose sine is -@code{X}. Returns @code{NULL} if @code{X} is not in the range @code{-1} to -@code{1}: -@example -mysql> select ASIN(0.2); - -> 0.201358 -mysql> select ASIN('foo'); - -> 0.000000 -@end example - -@findex ATAN() -@item ATAN(X) -Returns the arc tangent of @code{X}, that is, the value whose tangent is -@code{X}: -@example -mysql> select ATAN(2); - -> 1.107149 -mysql> select ATAN(-2); - -> -1.107149 -@end example - -@findex ATAN2() -@item ATAN2(Y,X) -Returns the arc tangent of the two variables @code{X} and @code{Y}. It is -similar to calculating the arc tangent of @code{Y / X}, except that the -signs of both arguments are used to determine the quadrant of the -result: -@example -mysql> select ATAN(-2,2); - -> -0.785398 -mysql> select ATAN(PI(),0); - -> 1.570796 -@end example - -@findex COT() -@item COT(X) -Returns the cotangent of @code{X}: -@example -mysql> select COT(12); - -> -1.57267341 -mysql> select COT(0); - -> NULL -@end example - -@findex RAND() -@item RAND() -@itemx RAND(N) -Returns a random floating-point value in the range @code{0} to @code{1.0}. -If an integer argument @code{N} is specified, it is used as the seed value: -@example -mysql> select RAND(); - -> 0.5925 -mysql> select RAND(20); - -> 0.1811 -mysql> select RAND(20); - -> 0.1811 -mysql> select RAND(); - -> 0.2079 -mysql> select RAND(); - -> 0.7888 -@end example -You can't use a column with @code{RAND()} values in an @code{ORDER BY} -clause, because @code{ORDER BY} would evaluate the column multiple times. -In @strong{MySQL} Version 3.23, you can, however, do: -@code{SELECT * FROM table_name ORDER BY RAND()} - -This is useful to get a random sample of a set @code{SELECT * FROM -table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000}. - -Note that a @code{RAND()} in a @code{WHERE} clause will be re-evaluated -every time the @code{WHERE} is executed. - -@findex LEAST() -@item LEAST(X,Y,...) -With two or more arguments, returns the smallest (minimum-valued) argument. -The arguments are compared using the following rules: - -@itemize @bullet -@item -If the return value is used in an @code{INTEGER} context, or all arguments -are integer-valued, they are compared as integers. - -@item -If the return value is used in a @code{REAL} context, or all arguments are -real-valued, they are compared as reals. - -@item -If any argument is a case-sensitive string, the arguments are compared -as case-sensitive strings. - -@item -In other cases, the arguments are compared as case-insensitive strings: -@end itemize - -@example -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" -@end example -In @strong{MySQL} versions prior to Version 3.22.5, you can use @code{MIN()} -instead of @code{LEAST}. - -@findex GREATEST() -@item GREATEST(X,Y,...) -Returns the largest (maximum-valued) argument. -The arguments are compared using the same rules as for @code{LEAST}: -@example -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" -@end example -In @strong{MySQL} versions prior to Version 3.22.5, you can use @code{MAX()} -instead of @code{GREATEST}. - -@findex DEGREES() -@item DEGREES(X) -Returns the argument @code{X}, converted from radians to degrees: -@example -mysql> select DEGREES(PI()); - -> 180.000000 -@end example - -@findex RADIANS() -@item RADIANS(X) -Returns the argument @code{X}, converted from degrees to radians: -@example -mysql> select RADIANS(90); - -> 1.570796 -@end example - -@findex TRUNCATE() -@item TRUNCATE(X,D) -Returns the number @code{X}, truncated to @code{D} decimals. If @code{D} -is @code{0}, the result will have no decimal point or fractional part: -@example -mysql> select TRUNCATE(1.223,1); - -> 1.2 -mysql> select TRUNCATE(1.999,1); - -> 1.9 -mysql> select TRUNCATE(1.999,0); - -> 1 -@end example - -Note that as decimal numbers are normally not stored as exact numbers in -computers, but as double values, you may be fooled by the following -result: - -@cindex rounding errors -@example -mysql> select TRUNCATE(10.28*100,0); - -> 1027 -@end example - -The above happens because 10.28 is actually stored as something like -10.2799999999999999. -@end table +@node String functions, Numeric Functions, Non-typed Operators, Functions +@subsection String Functions @findex string functions @findex functions, string -@node String functions, Date and time functions, Mathematical functions, Functions -@subsection String Functions String-valued functions return @code{NULL} if the length of the result would be greater than the @code{max_allowed_packet} server parameter. @xref{Server @@ -30556,10 +30060,672 @@ If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This only affects comparisons. + +@menu +* String comparison functions:: +* Case Sensitivity:: +@end menu + +@node String comparison functions, Case Sensitivity, String functions, String functions +@subsubsection String Comparison Functions + +@findex string comparison functions +@findex functions, string comparison + +@cindex case sensitivity, in string comparisons +@cindex string comparisons, case sensitivity + +Normally, if any expression in a string comparison is case sensitive, the +comparison is performed in case-sensitive fashion. + +@table @code +@findex LIKE +@item expr LIKE pat [ESCAPE 'escape-char'] +Pattern matching using +SQL simple regular expression comparison. Returns @code{1} (TRUE) or @code{0} +(FALSE). With @code{LIKE} you can use the following two wild-card characters +in the pattern: + +@multitable @columnfractions .1 .9 +@item @code{%} @tab Matches any number of characters, even zero characters +@item @code{_} @tab Matches exactly one character +@end multitable + +@example +mysql> select 'David!' LIKE 'David_'; + -> 1 +mysql> select 'David!' LIKE '%D%v%'; + -> 1 +@end example + +To test for literal instances of a wild-card character, precede the character +with the escape character. If you don't specify the @code{ESCAPE} character, +@samp{\} is assumed: + +@multitable @columnfractions .1 .9 +@item @code{\%} @tab Matches one @code{%} character +@item @code{\_} @tab Matches one @code{_} character +@end multitable + +@example +mysql> select 'David!' LIKE 'David\_'; + -> 0 +mysql> select 'David_' LIKE 'David\_'; + -> 1 +@end example + +To specify a different escape character, use the @code{ESCAPE} clause: + +@example +mysql> select 'David_' LIKE 'David|_' ESCAPE '|'; + -> 1 +@end example + +The following two statements illustrate that string comparisons are +case insensitive unless one of the operands is a binary string: + +@example +mysql> select 'abc' LIKE 'ABC'; + -> 1 +mysql> SELECT 'abc' LIKE BINARY 'ABC'; + -> 0 +@end example + +@code{LIKE} is allowed on numeric expressions! (This is a @strong{MySQL} +extension to the ANSI SQL @code{LIKE}.) + +@example +mysql> select 10 LIKE '1%'; + -> 1 +@end example + +Note: Because @strong{MySQL} uses the C escape syntax in strings (for example, +@samp{\n}), you must double any @samp{\} that you use in your @code{LIKE} +strings. For example, to search for @samp{\n}, specify it as @samp{\\n}. To +search for @samp{\}, specify it as @samp{\\\\} (the backslashes are stripped +once by the parser and another time when the pattern match is done, leaving +a single backslash to be matched). + +@findex NOT LIKE +@item expr NOT LIKE pat [ESCAPE 'escape-char'] +Same as @code{NOT (expr LIKE pat [ESCAPE 'escape-char'])}. + +@cindex mSQL compatibility +@cindex compatibility, with mSQL +@findex REGEXP +@findex RLIKE +@item expr REGEXP pat +@itemx expr RLIKE pat +Performs a pattern match of a string expression @code{expr} against a pattern +@code{pat}. The pattern can be an extended regular expression. +@xref{Regexp}. Returns @code{1} if @code{expr} matches @code{pat}, otherwise +returns @code{0}. @code{RLIKE} is a synonym for @code{REGEXP}, provided for +@code{mSQL} compatibility. Note: Because @strong{MySQL} uses the C escape +syntax in strings (for example, @samp{\n}), you must double any @samp{\} that +you use in your @code{REGEXP} strings. As of @strong{MySQL} Version 3.23.4, +@code{REGEXP} is case insensitive for normal (not binary) strings: + +@example +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 +@end example + +@item +@code{REGEXP} and @code{RLIKE} use the current character set (ISO-8859-1 +Latin1 by default) when deciding the type of a character. + +@findex NOT REGEXP +@item expr NOT REGEXP pat +@itemx expr NOT RLIKE pat +Same as @code{NOT (expr REGEXP pat)}. + +@findex STRCMP() +@item STRCMP(expr1,expr2) +@code{STRCMP()} +returns @code{0} if the strings are the same, @code{-1} if the first +argument is smaller than the second according to the current sort order, +and @code{1} otherwise: + +@example +mysql> select STRCMP('text', 'text2'); + -> -1 +mysql> select STRCMP('text2', 'text'); + -> 1 +mysql> select STRCMP('text', 'text'); + -> 0 +@end example + +@findex MATCH ... AGAINST() +@item MATCH (col1,col2,...) AGAINST (expr) +@code{MATCH ... AGAINST()} is used for full-text search and returns +relevance - similarity measure between the text in columns +@code{(col1,col2,...)} and the query @code{expr}. Relevance is a +positive floating-point number. Zero relevance means no similarity. +For @code{MATCH ... AGAINST()} to work, a @strong{FULLTEXT} index +must be created first. @xref{CREATE TABLE, , @code{CREATE TABLE}}. +@code{MATCH ... AGAINST()} is available in @strong{MySQL} Version +3.23.23 or later. For details and usage examples +@pxref{Fulltext Search}. +@end table + + +@node Case Sensitivity, , String comparison functions, String functions +@subsubsection Case Sensitivity + +@findex casts + +@cindex cast operators +@cindex operators, cast + +@table @code +@findex BINARY +@item @code{BINARY} +The @code{BINARY} operator casts the string following it to a binary string. +This is an easy way to force a column comparison to be case sensitive even +if the column isn't defined as @code{BINARY} or @code{BLOB}: +@example +mysql> select "a" = "A"; + -> 1 +mysql> select BINARY "a" = "A"; + -> 0 +@end example + +@code{BINARY} was introduced in @strong{MySQL} Version 3.23.0. + +Note that in some context @strong{MySQL} will not be able to use the +index efficiently when you cast an indexed column to @code{BINARY}. +@end table + +If you want to compare a blob case-insensitively you can always convert +the blob to upper case before doing the comparison: + +@example +SELECT 'A' LIKE UPPER(blob_col) FROM table_name; +@end example + +We plan to soon introduce casting between different character sets to +make string comparison even more flexible. + + +@node Numeric Functions, Date and time functions, String functions, Functions +@subsection Numeric Functions + +@menu +* Arithmetic functions:: +* Mathematical functions:: +@end menu + + +@node Arithmetic functions, Mathematical functions, Numeric Functions, Numeric Functions +@subsubsection Arithmetic Operations + +The usual arithmetic operators are available. Note that in the case of +@samp{-}, @samp{+}, and @samp{*}, the result is calculated with +@code{BIGINT} (64-bit) precision if both arguments are integers! + +@cindex operations, arithmetic +@cindex arithmetic expressions +@table @code +@findex + (addition) +@findex addition (+) +@item + +Addition: +@example +mysql> select 3+5; + -> 8 +@end example + +@findex - (subtraction) +@findex subtraction (-) +@item - +Subtraction: +@example +mysql> select 3-5; + -> -2 +@end example + +@findex * (multiplication) +@findex multiplication (*) +@item * +Multiplication: +@example +mysql> select 3*5; + -> 15 +mysql> select 18014398509481984*18014398509481984.0; + -> 324518553658426726783156020576256.0 +mysql> select 18014398509481984*18014398509481984; + -> 0 +@end example + +The result of the last expression is incorrect because the result of the integer +multiplication exceeds the 64-bit range of @code{BIGINT} calculations. + +@findex / (division) +@findex division (/) +@item / +Division: +@example +mysql> select 3/5; + -> 0.60 +@end example + +Division by zero produces a @code{NULL} result: + +@example +mysql> select 102/(1-1); + -> NULL +@end example + +A division will be calculated with @code{BIGINT} arithmetic only if performed +in a context where its result is converted to an integer! +@end table + + +@node Mathematical functions, , Arithmetic functions, Numeric Functions +@subsubsection Mathematical Functions +All mathematical functions return @code{NULL} in case of an error. + +@findex mathematical functions +@findex functions, mathematical + +@table @code +@findex - (unary minus) +@findex minus, unary (-) +@findex unary minus (-) +@item - +Unary minus. Changes the sign of the argument: +@example +mysql> select - 2; + -> -2 +@end example + +Note that if this operator is used with a @code{BIGINT}, the return value is a +@code{BIGINT}! This means that you should avoid using @code{-} on integers that +may have the value of @code{-2^63}! + +@findex ABS() +@item ABS(X) +Returns the absolute value of @code{X}: +@example +mysql> select ABS(2); + -> 2 +mysql> select ABS(-32); + -> 32 +@end example + +This function is safe to use with @code{BIGINT} values. + +@findex SIGN() +@item SIGN(X) +Returns the sign of the argument as @code{-1}, @code{0}, or @code{1}, depending +on whether @code{X} is negative, zero, or positive: +@example +mysql> select SIGN(-32); + -> -1 +mysql> select SIGN(0); + -> 0 +mysql> select SIGN(234); + -> 1 +@end example + +@findex MOD() +@findex % (modulo) +@findex modulo (%) +@item MOD(N,M) +@itemx % +Modulo (like the @code{%} operator in C). +Returns the remainder of @code{N} divided by @code{M}: +@example +mysql> select MOD(234, 10); + -> 4 +mysql> select 253 % 7; + -> 1 +mysql> select MOD(29,9); + -> 2 +@end example + +This function is safe to use with @code{BIGINT} values. + +@findex FLOOR() +@item FLOOR(X) +Returns the largest integer value not greater than @code{X}: +@example +mysql> select FLOOR(1.23); + -> 1 +mysql> select FLOOR(-1.23); + -> -2 +@end example + +Note that the return value is converted to a @code{BIGINT}! + +@findex CEILING() +@item CEILING(X) +Returns the smallest integer value not less than @code{X}: +@example +mysql> select CEILING(1.23); + -> 2 +mysql> select CEILING(-1.23); + -> -1 +@end example + +Note that the return value is converted to a @code{BIGINT}! + +@findex ROUND() +@item ROUND(X) +Returns the argument @code{X}, rounded to the nearest integer: +@example +mysql> select ROUND(-1.23); + -> -1 +mysql> select ROUND(-1.58); + -> -2 +mysql> select ROUND(1.58); + -> 2 +@end example + +Note that the behavior of @code{ROUND()} when the argument +is half way between two integers depends on the C library +implementation. Some round to the nearest even number, +always up, always down, or always towards zero. If you need +one kind of rounding, you should use a well-defined function +like @code{TRUNCATE()} or @code{FLOOR()} instead. + +@findex ROUND() +@item ROUND(X,D) +Returns the argument @code{X}, rounded to a number with @code{D} decimals. +If @code{D} is @code{0}, the result will have no decimal point or fractional +part: + +@example +mysql> select ROUND(1.298, 1); + -> 1.3 +mysql> select ROUND(1.298, 0); + -> 1 +@end example + +@findex EXP() +@item EXP(X) +Returns the value of @code{e} (the base of natural logarithms) raised to +the power of @code{X}: +@example +mysql> select EXP(2); + -> 7.389056 +mysql> select EXP(-2); + -> 0.135335 +@end example +@findex LOG() +@item LOG(X) +Returns the natural logarithm of @code{X}: +@example +mysql> select LOG(2); + -> 0.693147 +mysql> select LOG(-2); + -> NULL +@end example +If you want the log of a number @code{X} to some arbitary base @code{B}, use +the formula @code{LOG(X)/LOG(B)}. + +@findex LOG10() +@item LOG10(X) +Returns the base-10 logarithm of @code{X}: +@example +mysql> select LOG10(2); + -> 0.301030 +mysql> select LOG10(100); + -> 2.000000 +mysql> select LOG10(-100); + -> NULL +@end example + +@findex POW() +@findex POWER() +@item POW(X,Y) +@itemx POWER(X,Y) +Returns the value of @code{X} raised to the power of @code{Y}: +@example +mysql> select POW(2,2); + -> 4.000000 +mysql> select POW(2,-2); + -> 0.250000 +@end example + +@findex SQRT() +@item SQRT(X) +Returns the non-negative square root of @code{X}: +@example +mysql> select SQRT(4); + -> 2.000000 +mysql> select SQRT(20); + -> 4.472136 +@end example + +@findex PI() +@item PI() +Returns the value of PI. The default shown number of decimals is 5, but +@strong{MySQL} internally uses the full double precession for PI. +@example +mysql> select PI(); + -> 3.141593 +mysql> SELECT PI()+0.000000000000000000; + -> 3.141592653589793116 +@end example + +@findex COS() +@item COS(X) +Returns the cosine of @code{X}, where @code{X} is given in radians: +@example +mysql> select COS(PI()); + -> -1.000000 +@end example + +@findex SIN() +@item SIN(X) +Returns the sine of @code{X}, where @code{X} is given in radians: +@example +mysql> select SIN(PI()); + -> 0.000000 +@end example + +@findex TAN() +@item TAN(X) +Returns the tangent of @code{X}, where @code{X} is given in radians: +@example +mysql> select TAN(PI()+1); + -> 1.557408 +@end example + +@findex ACOS() +@item ACOS(X) +Returns the arc cosine of @code{X}, that is, the value whose cosine is +@code{X}. Returns @code{NULL} if @code{X} is not in the range @code{-1} to +@code{1}: +@example +mysql> select ACOS(1); + -> 0.000000 +mysql> select ACOS(1.0001); + -> NULL +mysql> select ACOS(0); + -> 1.570796 +@end example + +@findex ASIN() +@item ASIN(X) +Returns the arc sine of @code{X}, that is, the value whose sine is +@code{X}. Returns @code{NULL} if @code{X} is not in the range @code{-1} to +@code{1}: +@example +mysql> select ASIN(0.2); + -> 0.201358 +mysql> select ASIN('foo'); + -> 0.000000 +@end example + +@findex ATAN() +@item ATAN(X) +Returns the arc tangent of @code{X}, that is, the value whose tangent is +@code{X}: +@example +mysql> select ATAN(2); + -> 1.107149 +mysql> select ATAN(-2); + -> -1.107149 +@end example + +@findex ATAN2() +@item ATAN2(Y,X) +Returns the arc tangent of the two variables @code{X} and @code{Y}. It is +similar to calculating the arc tangent of @code{Y / X}, except that the +signs of both arguments are used to determine the quadrant of the +result: +@example +mysql> select ATAN(-2,2); + -> -0.785398 +mysql> select ATAN(PI(),0); + -> 1.570796 +@end example + +@findex COT() +@item COT(X) +Returns the cotangent of @code{X}: +@example +mysql> select COT(12); + -> -1.57267341 +mysql> select COT(0); + -> NULL +@end example + +@findex RAND() +@item RAND() +@itemx RAND(N) +Returns a random floating-point value in the range @code{0} to @code{1.0}. +If an integer argument @code{N} is specified, it is used as the seed value: +@example +mysql> select RAND(); + -> 0.5925 +mysql> select RAND(20); + -> 0.1811 +mysql> select RAND(20); + -> 0.1811 +mysql> select RAND(); + -> 0.2079 +mysql> select RAND(); + -> 0.7888 +@end example +You can't use a column with @code{RAND()} values in an @code{ORDER BY} +clause, because @code{ORDER BY} would evaluate the column multiple times. +In @strong{MySQL} Version 3.23, you can, however, do: +@code{SELECT * FROM table_name ORDER BY RAND()} + +This is useful to get a random sample of a set @code{SELECT * FROM +table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000}. + +Note that a @code{RAND()} in a @code{WHERE} clause will be re-evaluated +every time the @code{WHERE} is executed. + +@findex LEAST() +@item LEAST(X,Y,...) +With two or more arguments, returns the smallest (minimum-valued) argument. +The arguments are compared using the following rules: + +@itemize @bullet +@item +If the return value is used in an @code{INTEGER} context, or all arguments +are integer-valued, they are compared as integers. + +@item +If the return value is used in a @code{REAL} context, or all arguments are +real-valued, they are compared as reals. + +@item +If any argument is a case-sensitive string, the arguments are compared +as case-sensitive strings. + +@item +In other cases, the arguments are compared as case-insensitive strings: +@end itemize + +@example +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" +@end example +In @strong{MySQL} versions prior to Version 3.22.5, you can use @code{MIN()} +instead of @code{LEAST}. + +@findex GREATEST() +@item GREATEST(X,Y,...) +Returns the largest (maximum-valued) argument. +The arguments are compared using the same rules as for @code{LEAST}: +@example +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" +@end example +In @strong{MySQL} versions prior to Version 3.22.5, you can use @code{MAX()} +instead of @code{GREATEST}. + +@findex DEGREES() +@item DEGREES(X) +Returns the argument @code{X}, converted from radians to degrees: +@example +mysql> select DEGREES(PI()); + -> 180.000000 +@end example + +@findex RADIANS() +@item RADIANS(X) +Returns the argument @code{X}, converted from degrees to radians: +@example +mysql> select RADIANS(90); + -> 1.570796 +@end example + +@findex TRUNCATE() +@item TRUNCATE(X,D) +Returns the number @code{X}, truncated to @code{D} decimals. If @code{D} +is @code{0}, the result will have no decimal point or fractional part: +@example +mysql> select TRUNCATE(1.223,1); + -> 1.2 +mysql> select TRUNCATE(1.999,1); + -> 1.9 +mysql> select TRUNCATE(1.999,0); + -> 1 +@end example + +Note that as decimal numbers are normally not stored as exact numbers in +computers, but as double values, you may be fooled by the following +result: + +@cindex rounding errors +@example +mysql> select TRUNCATE(10.28*100,0); + -> 1027 +@end example + +The above happens because 10.28 is actually stored as something like +10.2799999999999999. +@end table + + +@node Date and time functions, Other Functions, Numeric Functions, Functions +@subsection Date and Time Functions + @findex date and time functions @findex functions, date and time -@node Date and time functions, Miscellaneous functions, String functions, Functions -@subsection Date and Time Functions See @ref{Date and time types} for a description of the range of values each type has and the valid formats in which date and time values may be @@ -31097,10 +31263,85 @@ mysql> select TIME_TO_SEC('00:39:38'); @end example @end table + +@node Other Functions, , Date and time functions, Functions +@subsection Other Functions + +@menu +* Bit functions:: +* Miscellaneous functions:: +@end menu + + +@node Bit functions, Miscellaneous functions, Other Functions, Other Functions +@subsubsection Bit Functions + +@findex arithmetic functions +@findex bit functions +@findex functions, arithmetic +@findex functions, bit + +@strong{MySQL} uses @code{BIGINT} (64-bit) arithmetic for bit operations, so +these operators have a maximum range of 64 bits. + +@table @code +@findex | (bitwise OR) +@findex OR, bitwise +@item | +Bitwise OR: +@example +mysql> select 29 | 15; + -> 31 +@end example + +@findex & (bitwise AND) +@findex AND, bitwise +@item & +Bitwise AND: +@example +mysql> select 29 & 15; + -> 13 +@end example + +@findex << (left shift) +@item << +Shifts a longlong (@code{BIGINT}) number to the left: +@example +mysql> select 1 << 2; + -> 4 +@end example + +@findex >> (right shift) +@item >> +Shifts a longlong (@code{BIGINT}) number to the right: +@example +mysql> select 4 >> 2; + -> 1 +@end example + +@findex ~ +@item ~ +Invert all bits: +@example +mysql> select 5 & ~1; + -> 4 +@end example + +@findex BIT_COUNT() +@item BIT_COUNT(N) +Returns the number of bits that are set in the argument @code{N}: +@example +mysql> select BIT_COUNT(29); + -> 4 +@end example +@end table + + +@node Miscellaneous functions, , Bit functions, Other Functions +@subsubsection Miscellaneous Functions + @findex miscellaneous functions @findex functions, miscellaneous -@node Miscellaneous functions, Group by functions, Date and time functions, Functions -@subsection Miscellaneous Functions @table @code @findex DATABASE() @@ -31379,1145 +31620,26 @@ control of master-slave synchronization, but was originally written to facilitate replication testing. @end table -@findex GROUP BY functions -@findex functions, GROUP BY -@node Group by functions, , Miscellaneous functions, Functions -@subsection Functions for Use with @code{GROUP BY} Clauses - -If you use a group function in a statement containing no @code{GROUP BY} -clause, it is equivalent to grouping on all rows. - -@table @code -@findex COUNT() -@item COUNT(expr) -Returns a count of the number of non-@code{NULL} values in the rows -retrieved by a @code{SELECT} statement: - -@example -mysql> select student.student_name,COUNT(*) - from student,course - where student.student_id=course.student_id - GROUP BY student_name; - -@end example - -@code{COUNT(*)} is somewhat different in that it returns a count of -the number of rows retrieved, whether or not they contain @code{NULL} -values. - -@code{COUNT(*)} is optimized to -return very quickly if the @code{SELECT} retrieves from one table, no -other columns are retrieved, and there is no @code{WHERE} clause. -For example: - -@example -mysql> select COUNT(*) from student; -@end example - -@findex COUNT(DISTINCT) -@findex DISTINCT -@item COUNT(DISTINCT expr,[expr...]) -Returns a count of the number of different non-@code{NULL} values: - -@example -mysql> select COUNT(DISTINCT results) from student; -@end example - -In @strong{MySQL} you can get the number of distinct expression -combinations that don't contain NULL by giving a list of expressions. -In ANSI SQL you would have to do a concatenation of all expressions -inside @code{CODE(DISTINCT ..)}. - -@findex AVG() -@item AVG(expr) -Returns the average value of @code{expr}: - -@example -mysql> select student_name, AVG(test_score) - from student - GROUP BY student_name; -@end example - -@findex MIN() -@findex MAX() -@item MIN(expr) -@itemx MAX(expr) -Returns the minimum or maximum value of @code{expr}. @code{MIN()} and -@code{MAX()} may take a string argument; in such cases they return the -minimum or maximum string value. @xref{MySQL indexes}. - -@example -mysql> select student_name, MIN(test_score), MAX(test_score) - from student - GROUP BY student_name; -@end example - -@findex SUM() -@item SUM(expr) -Returns the sum of @code{expr}. Note that if the return set has no rows, -it returns NULL! - -@findex STD() -@findex STDDEV() -@cindex Oracle compatibility -@cindex compatibility, with Oracle -@item STD(expr) -@itemx STDDEV(expr) -Returns the standard deviation of @code{expr}. This is an extension to -ANSI SQL. The @code{STDDEV()} form of this function is provided for Oracle -compatibility. -@findex BIT_OR() -@item BIT_OR(expr) -Returns the bitwise @code{OR} of all bits in @code{expr}. The calculation is -performed with 64-bit (@code{BIGINT}) precision. - -@findex BIT_AND() -@item BIT_AND(expr) -Returns the bitwise @code{AND} of all bits in @code{expr}. The calculation is -performed with 64-bit (@code{BIGINT}) precision. -@end table +@node Data Manipulation, Data Definition, Functions, Reference +@section Data Manipulation: @code{SELECT}, @code{INSERT}, @code{UPDATE}, @code{DELETE} -@cindex @code{GROUP BY}, extensions to ANSI SQL -@strong{MySQL} has extended the use of @code{GROUP BY}. You can use columns or -calculations in the @code{SELECT} expressions that don't appear in -the @code{GROUP BY} part. This stands for @emph{any possible value for this -group}. You can use this to get better performance by avoiding sorting and -grouping on unnecessary items. For example, you don't need to group on -@code{customer.name} in the following query: - -@example -mysql> select order.custid,customer.name,max(payments) - from order,customer - where order.custid = customer.custid - GROUP BY order.custid; -@end example - -In ANSI SQL, you would have to add @code{customer.name} to the @code{GROUP -BY} clause. In @strong{MySQL}, the name is redundant if you don't run in -ANSI mode. - -@strong{Don't use this feature} if the columns you omit from the -@code{GROUP BY} part aren't unique in the group! You will get -unpredictable results. - -In some cases, you can use @code{MIN()} and @code{MAX()} to obtain a specific -column value even if it isn't unique. The following gives the value of -@code{column} from the row containing the smallest value in the @code{sort} -column: - -@example -substr(MIN(concat(rpad(sort,6,' '),column)),7) -@end example - -@xref{example-Maximum-column-group-row}. - -@cindex @code{ORDER BY}, aliases in -@cindex aliases, in @code{ORDER BY} clauses -@cindex @code{GROUP BY}, aliases in -@cindex aliases, in @code{GROUP BY} clauses -@cindex expression aliases -@cindex aliases, for expressions -Note that if you are using @strong{MySQL} Version 3.22 (or earlier) or if -you are trying to follow ANSI SQL, you can't use expressions in @code{GROUP -BY} or @code{ORDER BY} clauses. You can work around this limitation by -using an alias for the expression: - -@example -mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name - GROUP BY id,val ORDER BY val; -@end example - -In @strong{MySQL} Version 3.23 you can do: - -@example -mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND(); -@end example - -@findex CREATE DATABASE -@node CREATE DATABASE, DROP DATABASE, Functions, Reference -@section @code{CREATE DATABASE} Syntax - -@example -CREATE DATABASE [IF NOT EXISTS] db_name -@end example - -@code{CREATE DATABASE} creates a database with the given name. Rules for -allowable database names are given in @ref{Legal names}. An error occurs if -the database already exists and you didn't specify @code{IF NOT EXISTS}. - -Databases in @strong{MySQL} are implemented as directories containing files -that correspond to tables in the database. Because there are no tables in a -database when it is initially created, the @code{CREATE DATABASE} statement -only creates a directory under the @strong{MySQL} data directory. - -@cindex @code{mysqladmin} -You can also create databases with @code{mysqladmin}. -@xref{Client-Side Scripts}. - -@findex DROP DATABASE -@node DROP DATABASE, CREATE TABLE, CREATE DATABASE, Reference -@section @code{DROP DATABASE} Syntax - -@example -DROP DATABASE [IF EXISTS] db_name -@end example - -@code{DROP DATABASE} drops all tables in the database and deletes the -database. If you do a @code{DROP DATABASE} on a symbolic linked -database, both the link and the original database is deleted. @strong{Be -VERY careful with this command!} - -@code{DROP DATABASE} returns the number of files that were removed from -the database directory. Normally, this is three times the number of -tables, because normally each table corresponds to a @file{.MYD} file, a -@file{.MYI} file, and a @file{.frm} file. - -The @code{DROP DATABASE} command removes from the given database -directory all files with the following extensions: - -@multitable @columnfractions .25 .25 .25 .25 -@item .BAK @tab .DAT @tab .HSH @tab .ISD -@item .ISM @tab .ISM @tab .MRG @tab .MYD -@item .MYI @tab .db @tab .frm -@end multitable - -All subdirectories that consists of 2 digits (@code{RAID} directories) -are also removed. - -In @strong{MySQL} Version 3.22 or later, you can use the keywords -@code{IF EXISTS} to prevent an error from occurring if the database doesn't -exist. - -@cindex @code{mysqladmin} -You can also drop databases with @code{mysqladmin}. @xref{Client-Side Scripts}. - -@findex CREATE TABLE -@node CREATE TABLE, ALTER TABLE, DROP DATABASE, Reference -@section @code{CREATE TABLE} Syntax @menu -* Silent column changes:: Silent column changes +* SELECT:: +* INSERT:: +* INSERT DELAYED:: +* UPDATE:: +* DELETE:: +* TRUNCATE:: +* REPLACE:: +* LOAD DATA:: @end menu -@example -CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] -[table_options] [select_statement] - -create_definition: - col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] - [PRIMARY KEY] [reference_definition] - or PRIMARY KEY (index_col_name,...) - or KEY [index_name] (index_col_name,...) - or INDEX [index_name] (index_col_name,...) - or UNIQUE [INDEX] [index_name] (index_col_name,...) - or FULLTEXT [INDEX] [index_name] (index_col_name,...) - or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) - [reference_definition] - or CHECK (expr) - -type: - TINYINT[(length)] [UNSIGNED] [ZEROFILL] - or SMALLINT[(length)] [UNSIGNED] [ZEROFILL] - or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] - or INT[(length)] [UNSIGNED] [ZEROFILL] - or INTEGER[(length)] [UNSIGNED] [ZEROFILL] - or BIGINT[(length)] [UNSIGNED] [ZEROFILL] - or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] - or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] - or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] - or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] - or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] - or CHAR(length) [BINARY] - or VARCHAR(length) [BINARY] - or DATE - or TIME - or TIMESTAMP - or DATETIME - or TINYBLOB - or BLOB - or MEDIUMBLOB - or LONGBLOB - or TINYTEXT - or TEXT - or MEDIUMTEXT - or LONGTEXT - or ENUM(value1,value2,value3,...) - or SET(value1,value2,value3,...) - -index_col_name: - col_name [(length)] - -reference_definition: - REFERENCES tbl_name [(index_col_name,...)] - [MATCH FULL | MATCH PARTIAL] - [ON DELETE reference_option] - [ON UPDATE reference_option] - -reference_option: - RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT - -table_options: - TYPE = @{BDB | HEAP | ISAM | InnoDB | MERGE | MYISAM @} -or AUTO_INCREMENT = # -or AVG_ROW_LENGTH = # -or CHECKSUM = @{0 | 1@} -or COMMENT = "string" -or MAX_ROWS = # -or MIN_ROWS = # -or PACK_KEYS = @{0 | 1@} -or PASSWORD = "string" -or DELAY_KEY_WRITE = @{0 | 1@} -or ROW_FORMAT= @{ default | dynamic | fixed | compressed @} -or RAID_TYPE= @{1 | STRIPED | RAID0 @} RAID_CHUNKS=# RAID_CHUNKSIZE=# -or UNION = (table_name,[table_name...]) -or DATA DIRECTORY="directory" -or INDEX DIRECTORY="directory" - -select_statement: - [IGNORE | REPLACE] SELECT ... (Some legal select statement) -@end example - -@code{CREATE TABLE} -creates a table with the given name in the current database. Rules for -allowable table names are given in @ref{Legal names}. An error occurs if -there is no current database or if the table already exists. - -In @strong{MySQL} Version 3.22 or later, the table name can be specified as -@code{db_name.tbl_name}. This works whether or not there is a current -database. - -In @strong{MySQL} Version 3.23, you can use the @code{TEMPORARY} keyword when -you create a table. A temporary table will automatically be deleted if a -connection dies and the name is per connection. This means that two different -connections can both use the same temporary table name without conflicting -with each other or with an existing table of the same name. (The existing table -is hidden until the temporary table is deleted). - -In @strong{MySQL} Version 3.23 or later, you can use the keywords -@code{IF NOT EXISTS} so that an error does not occur if the table already -exists. Note that there is no verification that the table structures are -identical. - -Each table @code{tbl_name} is represented by some files in the database -directory. In the case of MyISAM-type tables you will get: - -@multitable @columnfractions .2 .8 -@item @strong{File} @tab @strong{Purpose} -@item @code{tbl_name.frm} @tab Table definition (form) file -@item @code{tbl_name.MYD} @tab Data file -@item @code{tbl_name.MYI} @tab Index file -@end multitable - -For more information on the properties of the various column types, see -@ref{Column types}: - -@itemize @bullet -@item -If neither @code{NULL} nor @code{NOT NULL} is specified, the column -is treated as though @code{NULL} had been specified. - -@item -An integer column may have the additional attribute @code{AUTO_INCREMENT}. -When you insert a value of @code{NULL} (recommended) or @code{0} into an -@code{AUTO_INCREMENT} column, the column is set to @code{value+1}, where -@code{value} is the largest value for the column currently in the table. -@code{AUTO_INCREMENT} sequences begin with @code{1}. -@xref{mysql_insert_id, , @code{mysql_insert_id()}}. - -If you delete the row containing the maximum value for an -@code{AUTO_INCREMENT} column, the value will be reused with an -@code{ISAM}, or @code{BDB} table but not with a -@code{MyISAM} or @code{InnoDB} table. If you delete all rows in the table -with @code{DELETE FROM table_name} (without a @code{WHERE}) in -@code{AUTOCOMMIT} mode, the sequence starts over for all table types. - -@strong{NOTE:} There can be only one @code{AUTO_INCREMENT} column per -table, and it must be indexed. @strong{MySQL} Version 3.23 will also only -work properly if the auto_increment column only has positive -values. Inserting a negative number is regarded as inserting a very large -positive number. This is done to avoid precision problems when -numbers 'wrap' over from positive to negative and also to ensure that one -doesn't accidentally get an auto_increment column that contains 0. - -@cindex ODBC compatibility -@cindex compatibility, with ODBC -To make @strong{MySQL} compatible with some ODBC applications, you can find -the last inserted row with the following query: - -@example -SELECT * FROM tbl_name WHERE auto_col IS NULL -@end example - -@item -@code{NULL} values are handled differently for @code{TIMESTAMP} columns than -for other column types. You cannot store a literal @code{NULL} in a -@code{TIMESTAMP} column; setting the column to @code{NULL} sets it to the -current date and time. Because @code{TIMESTAMP} columns behave this way, the -@code{NULL} and @code{NOT NULL} attributes do not apply in the normal way and -are ignored if you specify them. - -On the other hand, to make it easier for @strong{MySQL} clients to use -@code{TIMESTAMP} columns, the server reports that such columns may be -assigned @code{NULL} values (which is true), even though @code{TIMESTAMP} -never actually will contain a @code{NULL} value. You can see this when you -use @code{DESCRIBE tbl_name} to get a description of your table. - -Note that setting a @code{TIMESTAMP} column to @code{0} is not the same -as setting it to @code{NULL}, because @code{0} is a valid @code{TIMESTAMP} -value. - -@item -If no @code{DEFAULT} value is specified for a column, @strong{MySQL} -automatically assigns one. - -If the column may take @code{NULL} as a value, the default value is -@code{NULL}. - -If the column is declared as @code{NOT NULL}, the default value depends on -the column type: - -@itemize @minus -@item -For numeric types other than those declared with the @code{AUTO_INCREMENT} -attribute, the default is @code{0}. For an @code{AUTO_INCREMENT} column, the -default value is the next value in the sequence. - -@item -For date and time types other than @code{TIMESTAMP}, the default is the -appropriate zero value for the type. For the first @code{TIMESTAMP} -column in a table, the default value is the current date and time. -@xref{Date and time types}. - -@item -For string types other than @code{ENUM}, the default value is the empty string. -For @code{ENUM}, the default is the first enumeration value. -@end itemize - -Default values must be constants. This means, for example, that you cannot -set the default for a date column to be the value of a function such as -@code{NOW()} or @code{CURRENT_DATE}. - -@item -@code{KEY} is a synonym for @code{INDEX}. - -@item -In @strong{MySQL}, a @code{UNIQUE} key can have only distinct values. An -error occurs if you try to add a new row with a key that matches an existing -row. - -@item -@tindex PRIMARY KEY -A @code{PRIMARY KEY} is a unique @code{KEY} with the extra constraint -that all key columns must be defined as @code{NOT NULL}. In @strong{MySQL} -the key is named @code{PRIMARY}. A table can have only one @code{PRIMARY KEY}. -If you don't have a @code{PRIMARY KEY} and some applications ask for the -@code{PRIMARY KEY} in your tables, @strong{MySQL} will return the first -@code{UNIQUE} key, which doesn't have any @code{NULL} columns, as the -@code{PRIMARY KEY}. - -@item -A @code{PRIMARY KEY} can be a multiple-column index. However, you cannot -create a multiple-column index using the @code{PRIMARY KEY} key attibute in a -column specification. Doing so will mark only that single column as primary. -You must use the @code{PRIMARY KEY(index_col_name, ...)} syntax. - -@item -If the @code{PRIMARY} or @code{UNIQUE} key consists of only one column and this -is of type integer, you can also refer to it as @code{_rowid} -(new in Version 3.23.11). - -@item -If you don't assign a name to an index, the index will be assigned the same -name as the first @code{index_col_name}, with an optional suffix (@code{_2}, -@code{_3}, @code{...}) to make it unique. You can see index names for a -table using @code{SHOW INDEX FROM tbl_name}. -@xref{SHOW, , @code{SHOW}}. - -@item -@cindex @code{NULL} values, and indexes -@cindex indexes, and @code{NULL} values -Only the @code{MyISAM} table type supports indexes on columns that can have -@code{NULL} values. In other cases you must declare such columns -@code{NOT NULL} or an error results. - -@item -With @code{col_name(length)} syntax, you can specify an index that -uses only a part of a @code{CHAR} or @code{VARCHAR} column. This can -make the index file much smaller. -@xref{Indexes}. - -@item -@cindex @code{BLOB} columns, indexing -@cindex indexes, and @code{BLOB} columns -@cindex @code{TEXT} columns, indexing -@cindex indexes, and @code{TEXT} columns -Only the @code{MyISAM} table type supports indexing on @code{BLOB} and -@code{TEXT} columns. When putting an index on a @code{BLOB} or @code{TEXT} -column you MUST always specify the length of the index: -@example -CREATE TABLE test (blob_col BLOB, index(blob_col(10))); -@end example - -@item -When you use @code{ORDER BY} or @code{GROUP BY} with a @code{TEXT} or -@code{BLOB} column, only the first @code{max_sort_length} bytes are used. -@xref{BLOB, , @code{BLOB}}. - -@item -In @strong{MySQL} Version 3.23.23 or later, you can also create special -@strong{FULLTEXT} indexes. They are used for full-text search. Only the -@code{MyISAM} table type supports @code{FULLTEXT} indexes. They can be created -only from @code{VARCHAR} and @code{TEXT} columns. -Indexing always happens over the entire column, partial indexing is not -supported. See @ref{Fulltext Search} for details of operation. - -@item -The @code{FOREIGN KEY}, @code{CHECK}, and @code{REFERENCES} clauses don't -actually do anything. The syntax for them is provided only for compatibility, -to make it easier to port code from other SQL servers and to run applications -that create tables with references. -@xref{Missing functions}. - -@item -Each @code{NULL} column takes one bit extra, rounded up to the nearest byte. - -@item -The maximum record length in bytes can be calculated as follows: - -@example -row length = 1 - + (sum of column lengths) - + (number of NULL columns + 7)/8 - + (number of variable-length columns) -@end example - -@item -The @code{table_options} and @code{SELECT} options are only -implemented in @strong{MySQL} Version 3.23 and above. - -The different table types are: - -@multitable @columnfractions .20 .80 -@item BDB or Berkeley_db @tab Transaction-safe tables with page locking. @xref{BDB}. -@item HEAP @tab The data for this table is only stored in memory. @xref{HEAP}. -@item ISAM @tab The original table handler. @xref{ISAM}. -@item InnoDB @tab Transaction-safe tables with row locking. @xref{InnoDB}. -@item MERGE @tab A collection of MyISAM tables used as one table. @xref{MERGE}. -@item MyISAM @tab The new binary portable table handler that is replacing ISAM. @xref{MyISAM}. -@end multitable -@xref{Table types}. - -If a table type is specified, and that particular type is not available, -@strong{MySQL} will choose the closest table type to the one that you have -specified. For example, if @code{TYPE=BDB} is specified, and that distribution -of @strong{MySQL} does not support @code{BDB} tables, the table will be created -as @code{MyISAM} instead. - -The other table options are used to optimize the behavior of the -table. In most cases, you don't have to specify any of them. -The options work for all table types, if not otherwise indicated: - -@multitable @columnfractions .20 .80 -@item @code{AUTO_INCREMENT} @tab The next auto_increment value you want to set for your table (MyISAM). -@item @code{AVG_ROW_LENGTH} @tab An approximation of the average row length for your table. You only need to set this for large tables with variable size records. -@item @code{CHECKSUM} @tab Set this to 1 if you want @strong{MySQL} to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM). -@item @code{COMMENT} @tab A 60-character comment for your table. -@item @code{MAX_ROWS} @tab Max number of rows you plan to store in the table. -@item @code{MIN_ROWS} @tab Minimum number of rows you plan to store in the table. -@item @code{PACK_KEYS} @tab Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM). -@item @code{PASSWORD} @tab Encrypt the @code{.frm} file with a password. This option doesn't do anything in the standard @strong{MySQL} version. -@item @code{DELAY_KEY_WRITE} @tab Set this to 1 if want to delay key table updates until the table is closed (MyISAM). -@item @code{ROW_FORMAT} @tab Defines how the rows should be stored. Currently you can only use the DYNAMIC and STATIC options for MyISAM tables. -@end multitable - -When you use a @code{MyISAM} table, @strong{MySQL} uses the product of -@code{max_rows * avg_row_length} to decide how big the resulting table -will be. If you don't specify any of the above options, the maximum size -for a table will be 4G (or 2G if your operating systems only supports 2G -tables). The reason for this is just to keep down the pointer sizes -to make the index smaller and faster if you don't really need big files. - -If you don't use @code{PACK_KEYS}, the default is to only pack strings, -not numbers. If you use @code{PACK_KEYS=1}, numbers will be packed as well. - -When packing binary number keys, @strong{MySQL} will use prefix compression. -This means that you will only get a big benefit of this if you have -many numbers that are the same. Prefix compression means that every -key needs one extra byte to indicate how many bytes of the previous key are -the same for the next key (note that the pointer to the row is stored -in high-byte-first-order directly after the key, to improve -compression.) This means that if you have many equal keys on two rows -in a row, all following 'same' keys will usually only take 2 bytes -(including the pointer to the row). Compare this to the ordinary case -where the following keys will take storage_size_for_key + -pointer_size (usually 4). On the other hand, if all keys are -totally different, you will lose 1 byte per key, if the key isn't a -key that can have @code{NULL} values (In this case the packed key length will -be stored in the same byte that is used to mark if a key is @code{NULL}.) - -@item -If you specify a @code{SELECT} after the @code{CREATE} statement, -@strong{MySQL} will create new fields for all elements in the -@code{SELECT}. For example: - -@example -mysql> CREATE TABLE test (a int not null auto_increment, - primary key (a), key(b)) - TYPE=MyISAM SELECT b,c from test2; -@end example - -This will create a @code{MyISAM} table with three columns, a, b, and c. -Notice that the columns from the @code{SELECT} statement are appended to -the right side of the table, not overlapped onto it. Take the following -example: - -@example -mysql> select * from foo; -+---+ -| n | -+---+ -| 1 | -+---+ - -mysql> create table bar (m int) select n from foo; -Query OK, 1 row affected (0.02 sec) -Records: 1 Duplicates: 0 Warnings: 0 - -mysql> select * from bar; -+------+---+ -| m | n | -+------+---+ -| NULL | 1 | -+------+---+ -1 row in set (0.00 sec) -@end example - -For each row in table @code{foo}, a row is inserted in @code{bar} with -the values from @code{foo} and default values for the new columns. - -@code{CREATE TABLE ... SELECT} will not automaticly create any indexes -for you. This is done intentionally to make the command as flexible as -possible. If you want to have indexes in the created table, you should -specify these before the @code{SELECT} statement: - -@example -mysql> create table bar (unique (n)) select n from foo; -@end example - -If any errors occur while copying the data to the table, it will -automatically be deleted. - -To ensure that the update log/binary log can be used to re-create the -original tables, @strong{MySQL} will not allow concurrent inserts during -@code{CREATE TABLE .... SELECT}. -@item -The @code{RAID_TYPE} option will help you to break the 2G/4G limit for -the MyISAM data file (not the index file) on -operating systems that don't support big files. You can get also more speed -from the I/O bottleneck by putting @code{RAID} directories on different -physical disks. @code{RAID_TYPE} will work on any OS, as long as you have -configured @strong{MySQL} with @code{--with-raid}. For now the only allowed -@code{RAID_TYPE} is @code{STRIPED} (@code{1} and @code{RAID0} are aliases -for this). - -If you specify @code{RAID_TYPE=STRIPED} for a @code{MyISAM} table, -@code{MyISAM} will create @code{RAID_CHUNKS} subdirectories named 00, -01, 02 in the database directory. In each of these directories -@code{MyISAM} will create a @code{table_name.MYD}. When writing data -to the data file, the @code{RAID} handler will map the first -@code{RAID_CHUNKSIZE} *1024 bytes to the first file, the next -@code{RAID_CHUNKSIZE} *1024 bytes to the next file and so on. -@item -@code{UNION} is used when you want to use a collection of identical -tables as one. This only works with MERGE tables. @xref{MERGE}. - -For the moment you need to have @code{SELECT}, @code{UPDATE}, and -@code{DELETE} privileges on the tables you map to a @code{MERGE} table. -All mapped tables must be in the same database as the @code{MERGE} table. -@item -In the created table the @code{PRIMARY} key will be placed first, followed -by all @code{UNIQUE} keys and then the normal keys. This helps the -@strong{MySQL} optimizer to prioritize which key to use and also more quickly -detect duplicated @code{UNIQUE} keys. - -@item -By using @code{DATA DIRECTORY="directory"} or @code{INDEX -DIRECTORY="directory"} you can specify where the table handler should -put it's table and index files. This only works for @code{MyISAM} tables -in @code{MySQL} 4.0, when you are not using the @code{--skip-symlink} -option. @xref{Symbolic links to tables}. - -@end itemize - -@cindex silent column changes -@node Silent column changes, , CREATE TABLE, CREATE TABLE -@subsection Silent Column Specification Changes - -In some cases, @strong{MySQL} silently changes a column specification from -that given in a @code{CREATE TABLE} statement. (This may also occur with -@code{ALTER TABLE}.): - -@itemize @bullet -@item -@code{VARCHAR} columns with a length less than four are changed to -@code{CHAR}. - -@item -If any column in a table has a variable length, the entire row is -variable-length as a result. Therefore, if a table contains any -variable-length columns (@code{VARCHAR}, @code{TEXT}, or @code{BLOB}), -all @code{CHAR} columns longer than three characters are changed to -@code{VARCHAR} columns. This doesn't affect how you use the columns in -any way; in @strong{MySQL}, @code{VARCHAR} is just a different way to -store characters. @strong{MySQL} performs this conversion because it -saves space and makes table operations faster. @xref{Table types}. - -@item -@code{TIMESTAMP} display sizes must be even and in the range from 2 to 14. -If you specify a display size of 0 or greater than 14, the size is coerced -to 14. Odd-valued sizes in the range from 1 to 13 are coerced -to the next higher even number. - -@item -You cannot store a literal @code{NULL} in a @code{TIMESTAMP} column; setting -it to @code{NULL} sets it to the current date and time. Because -@code{TIMESTAMP} columns behave this way, the @code{NULL} and @code{NOT NULL} -attributes do not apply in the normal way and are ignored if you specify -them. @code{DESCRIBE tbl_name} always reports that a @code{TIMESTAMP} -column may be assigned @code{NULL} values. - -@item -@strong{MySQL} maps certain column types used by other SQL database vendors -to @strong{MySQL} types. @xref{Other-vendor column types}. -@end itemize - -If you want to see whether or not @strong{MySQL} used a column type other -than the one you specified, issue a @code{DESCRIBE tbl_name} statement after -creating or altering your table. - -@cindex @code{myisampack} -Certain other column type changes may occur if you compress a table -using @code{myisampack}. @xref{Compressed format}. - -@findex ALTER TABLE -@node ALTER TABLE, RENAME TABLE, CREATE TABLE, Reference -@section @code{ALTER TABLE} Syntax - -@example -ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] - -alter_specification: - ADD [COLUMN] create_definition [FIRST | AFTER column_name ] - or ADD [COLUMN] (create_definition, create_definition,...) - or ADD INDEX [index_name] (index_col_name,...) - or ADD PRIMARY KEY (index_col_name,...) - or ADD UNIQUE [index_name] (index_col_name,...) - or ADD FULLTEXT [index_name] (index_col_name,...) - or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) - [reference_definition] - or ALTER [COLUMN] col_name @{SET DEFAULT literal | DROP DEFAULT@} - or CHANGE [COLUMN] old_col_name create_definition - or MODIFY [COLUMN] create_definition - or DROP [COLUMN] col_name - or DROP PRIMARY KEY - or DROP INDEX index_name - or RENAME [TO] new_tbl_name - or ORDER BY col - or table_options -@end example - -@code{ALTER TABLE} allows you to change the structure of an existing table. -For example, you can add or delete columns, create or destroy indexes, change -the type of existing columns, or rename columns or the table itself. You can -also change the comment for the table and type of the table. -@xref{CREATE TABLE, , @code{CREATE TABLE}}. - -If you use @code{ALTER TABLE} to change a column specification but -@code{DESCRIBE tbl_name} indicates that your column was not changed, it is -possible that @strong{MySQL} ignored your modification for one of the reasons -described in @ref{Silent column changes}. For example, if you try to change -a @code{VARCHAR} column to @code{CHAR}, @strong{MySQL} will still use -@code{VARCHAR} if the table contains other variable-length columns. - -@code{ALTER TABLE} works by making a temporary copy of the original table. -The alteration is performed on the copy, then the original table is -deleted and the new one is renamed. This is done in such a way that -all updates are automatically redirected to the new table without -any failed updates. While @code{ALTER TABLE} is executing, the original -table is readable by other clients. Updates and writes to the table -are stalled until the new table is ready. - -Note that if you use any other option to @code{ALTER TABLE} than -@code{RENAME}, @strong{MySQL} will always create a temporary table, even -if the data wouldn't strictly need to be copied (like when you change the -name of a column). We plan to fix this in the future, but as one doesn't -normally do @code{ALTER TABLE} that often this isn't that high on our TODO. - -@itemize @bullet -@item -To use @code{ALTER TABLE}, you need @strong{ALTER}, @strong{INSERT}, -and @strong{CREATE} privileges on the table. - -@item -@code{IGNORE} is a @strong{MySQL} extension to ANSI SQL92. -It controls how @code{ALTER TABLE} works if there are duplicates on -unique keys in the new table. -If @code{IGNORE} isn't specified, the copy is aborted and rolled back. -If @code{IGNORE} is specified, then for rows with duplicates on a unique -key, only the first row is used; the others are deleted. - -@item -You can issue multiple @code{ADD}, @code{ALTER}, @code{DROP}, and -@code{CHANGE} clauses in a single @code{ALTER TABLE} statement. This is a -@strong{MySQL} extension to ANSI SQL92, which allows only one of each clause -per @code{ALTER TABLE} statement. - -@item -@code{CHANGE col_name}, @code{DROP col_name}, and @code{DROP -INDEX} are @strong{MySQL} extensions to ANSI SQL92. - -@item -@code{MODIFY} is an Oracle extension to @code{ALTER TABLE}. - -@item -The optional word @code{COLUMN} is a pure noise word and can be omitted. - -@item -If you use @code{ALTER TABLE tbl_name RENAME TO new_name} without any other -options, @strong{MySQL} simply renames the files that correspond to the table -@code{tbl_name}. There is no need to create the temporary table. -@xref{RENAME TABLE,, @code{RENAME TABLE}}. - -@item -@code{create_definition} clauses use the same syntax for @code{ADD} and -@code{CHANGE} as for @code{CREATE TABLE}. Note that this syntax includes -the column name, not just the column type. -@xref{CREATE TABLE, , @code{CREATE TABLE}}. - -@item -You can rename a column using a @code{CHANGE old_col_name create_definition} -clause. To do so, specify the old and new column names and the type that -the column currently has. For example, to rename an @code{INTEGER} column -from @code{a} to @code{b}, you can do this: - -@example -mysql> ALTER TABLE t1 CHANGE a b INTEGER; -@end example - -If you want to change a column's type but not the name, @code{CHANGE} -syntax still requires two column names even if they are the same. For -example: - -@example -mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; -@end example - -However, as of @strong{MySQL} Version 3.22.16a, you can also use @code{MODIFY} -to change a column's type without renaming it: - -@example -mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL; -@end example - -@item -If you use @code{CHANGE} or @code{MODIFY} to shorten a column for which -an index exists on part of the column (for instance, if you have an index -on the first 10 characters of a @code{VARCHAR} column), you cannot make -the column shorter than the number of characters that are indexed. - -@item -When you change a column type using @code{CHANGE} or @code{MODIFY}, -@strong{MySQL} tries to convert data to the new type as well as possible. - -@item -In @strong{MySQL} Version 3.22 or later, you can use @code{FIRST} or -@code{ADD ... AFTER col_name} to add a column at a specific position within -a table row. The default is to add the column last. - -@findex ALTER COLUMN -@item -@code{ALTER COLUMN} specifies a new default value for a column -or removes the old default value. -If the old default is removed and the column can be @code{NULL}, the new -default is @code{NULL}. If the column cannot be @code{NULL}, @strong{MySQL} -assigns a default value, as described in -@ref{CREATE TABLE, , @code{CREATE TABLE}}. - -@findex DROP INDEX -@item -@code{DROP INDEX} removes an index. This is a @strong{MySQL} extension to -ANSI SQL92. @xref{DROP INDEX}. - -@item -If columns are dropped from a table, the columns are also removed from any -index of which they are a part. If all columns that make up an index are -dropped, the index is dropped as well. - -@item -If a table contains only one column, the column cannot be dropped. -If what you intend is to remove the table, use @code{DROP TABLE} instead. - -@findex DROP PRIMARY KEY -@item -@code{DROP PRIMARY KEY} drops the primary index. If no such -index exists, it drops the first @code{UNIQUE} index in the table. -(@strong{MySQL} marks the first @code{UNIQUE} key as the @code{PRIMARY KEY} -if no @code{PRIMARY KEY} was specified explicitly.) - -@findex ORDER BY -@item -@code{ORDER BY} allows you to create the new table with the rows in a -specific order. Note that the table will not remain in this order after -inserts and deletes. In some cases, it may make sorting easier for -@strong{MySQL} if the table is in order by the column that you wish to -order it by later. This option is mainly useful when you know that you -are mostly going to query the rows in a certain order; By using this -option after big changes to the table, you may be able to get higher -performance. - -@findex ALTER TABLE -@item -If you use @code{ALTER TABLE} on a @code{MyISAM} table, all non-unique -indexes are created in a separate batch (like in @code{REPAIR}). -This should make @code{ALTER TABLE} much faster when you have many indexes. - -@item -@findex mysql_info() -With the C API function @code{mysql_info()}, you can find out how many -records were copied, and (when @code{IGNORE} is used) how many records were -deleted due to duplication of unique key values. - -@item -@cindex foreign keys -@cindex references -The @code{FOREIGN KEY}, @code{CHECK}, and @code{REFERENCES} clauses don't -actually do anything. The syntax for them is provided only for compatibility, -to make it easier to port code from other SQL servers and to run applications -that create tables with references. -@xref{Missing functions}. -@end itemize - -Here is an example that shows some of the uses of @code{ALTER TABLE}. We -begin with a table @code{t1} that is created as shown below: - -@example -mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10)); -@end example - -To rename the table from @code{t1} to @code{t2}: - -@example -mysql> ALTER TABLE t1 RENAME t2; -@end example - -To change column @code{a} from @code{INTEGER} to @code{TINYINT NOT NULL} -(leaving the name the same), and to change column @code{b} from -@code{CHAR(10)} to @code{CHAR(20)} as well as renaming it from @code{b} to -@code{c}: - -@example -mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); -@end example - -To add a new @code{TIMESTAMP} column named @code{d}: - -@example -mysql> ALTER TABLE t2 ADD d TIMESTAMP; -@end example - -To add an index on column @code{d}, and make column @code{a} the primary key: - -@example -mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); -@end example - -To remove column @code{c}: - -@example -mysql> ALTER TABLE t2 DROP COLUMN c; -@end example - -To add a new @code{AUTO_INCREMENT} integer column named @code{c}: - -@example -mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, - ADD INDEX (c); -@end example - -Note that we indexed @code{c}, because @code{AUTO_INCREMENT} columns must be -indexed, and also that we declare @code{c} as @code{NOT NULL}, because -indexed columns cannot be @code{NULL}. - -When you add an @code{AUTO_INCREMENT} column, column values are filled in -with sequence numbers for you automatically. You can set the first -sequence number by executing @code{SET INSERT_ID=#} before -@code{ALTER TABLE} or using the @code{AUTO_INCREMENT = #} table option. -@xref{SET OPTION}. - -With MyISAM tables, if you don't change the @code{AUTO_INCREMENT} -column, the sequence number will not be affected. If you drop an -@code{AUTO_INCREMENT} column and then add another @code{AUTO_INCREMENT} -column, the numbers will start from 1 again. - -@xref{ALTER TABLE problems}. - -@findex RENAME TABLE -@node RENAME TABLE, DROP TABLE, ALTER TABLE, Reference -@section @code{RENAME TABLE} Syntax - -@example -RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...] -@end example - -The rename is done atomically, which means that no other thread can -access any of the tables while the rename is running. This makes it -possible to replace a table with an empty one: - -@example -CREATE TABLE new_table (...); -RENAME TABLE old_table TO backup_table, new_table TO old_table; -@end example - -The rename is done from left to right, which means that if you want to -swap two tables names, you have to: - -@example -RENAME TABLE old_table TO backup_table, - new_table TO old_table, - backup_table TO new_table; -@end example - -As long as two databases are on the same disk you can also rename -from one database to another: - -@example -RENAME TABLE current_database.table_name TO other_database.table_name; -@end example - -When you execute @code{RENAME}, you can't have any locked tables or -active transactions. You must also have the @code{ALTER} and @code{DROP} -privilege on the original table and @code{CREATE} and @code{INSERT} -privilege on the new table. - -If @strong{MySQL} encounters any errors in a multiple table rename, it -will do a reverse rename for all renamed tables to get everything back -to the original state. - -@findex DROP TABLE -@node DROP TABLE, DELETE, RENAME TABLE, Reference -@section @code{DROP TABLE} Syntax - -@example -DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE] -@end example - -@code{DROP TABLE} removes one or more tables. All table data and the table -definition are @emph{removed}, so @strong{be careful} with this command! - -In @strong{MySQL} Version 3.22 or later, you can use the keywords -@code{IF EXISTS} to prevent an error from occurring for tables that don't -exist. - -@code{RESTRICT} and @code{CASCADE} are allowed to make porting easier. -For the moment they don't do anything. - -@strong{NOTE}: @code{DROP TABLE} is not transaction-safe and will -automatically commit any active transactions. - - -@node DELETE, TRUNCATE, DROP TABLE, Reference -@section @code{DELETE} Syntax - -@findex DELETE - -@example -DELETE [LOW_PRIORITY] FROM tbl_name - [WHERE where_definition] - [LIMIT rows] -@end example - -@code{DELETE} deletes rows from @code{tbl_name} that satisfy the condition -given by @code{where_definition}, and returns the number of records deleted. - -@c If an @code{ORDER BY} clause is used, the rows will be deleted in that order. -@c This is really only useful in conjunction with @code{LIMIT}. For example: - -@c @example -@c DELETE FROM somelog -@c WHERE user = 'jcole' -@c ORDER BY timestamp -@c LIMIT 1 -@c @end example - -@c This will delete the oldest entry (by @code{timestamp}) where the row matches -@c the @code{WHERE} clause. - -If you issue a @code{DELETE} with no @code{WHERE} clause, all rows are -deleted. If you do this in @code{AUTOCOMMIT} mode, this works as -@code{TRUNCATE}. @xref{TRUNCATE}. One problem with this is that -@code{DELETE} will return zero as the number of affected records, but -this will be fixed in 4.0. - -If you really want to know how many records are deleted when you are deleting -all rows, and are willing to suffer a speed penalty, you can use a -@code{DELETE} statement of this form: - -@example -mysql> DELETE FROM tbl_name WHERE 1>0; -@end example - -Note that this is MUCH slower than @code{DELETE FROM tbl_name} with no -@code{WHERE} clause, because it deletes rows one at a time. - -If you specify the keyword @code{LOW_PRIORITY}, execution of the -@code{DELETE} is delayed until no other clients are reading from the table. - -Deleted records are maintained in a linked list and subsequent @code{INSERT} -operations reuse old record positions. To reclaim unused space and reduce -file sizes, use the @code{OPTIMIZE TABLE} statement or the @code{myisamchk} -utility to reorganize tables. @code{OPTIMIZE TABLE} is easier, but -@code{myisamchk} is faster. -See @ref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}} and @ref{Optimization}. - -The @strong{MySQL}-specific @code{LIMIT rows} option to @code{DELETE} tells -the server the maximum number of rows to be deleted before control is -returned to the client. This can be used to ensure that a specific -@code{DELETE} command doesn't take too much time. You can simply repeat -the @code{DELETE} command until the number of affected rows is less than -the @code{LIMIT} value. - -@findex TRUNCATE -@node TRUNCATE, SELECT, DELETE, Reference -@section @code{TRUNCATE} Syntax - -@example -TRUNCATE TABLE table_name -@end example - -Is in 3.23 and the same thing as @code{DELETE FROM table_name}. @xref{DELETE}. -The differences are: - -@itemize @bullet -@item -Implemented as a drop and re-create of the table, which makes this -much faster when deleting many rows. -@item -Not transaction-safe; @code{TRUNCATE TABLE} will automatically end the current -transaction as if @code{COMMIT} would have been called. -@item -Doesn't return the number of deleted rows. -@item -As long as the table definition file @file{table_name.frm} is -valid, the table can be re-created this way, even if the data or index -files have become corrupted. -@end itemize - -@code{TRUNCATE} is an Oracle SQL extension. +@node SELECT, INSERT, Data Manipulation, Data Manipulation +@subsection @code{SELECT} Syntax @findex SELECT -@node SELECT, JOIN, TRUNCATE, Reference -@section @code{SELECT} Syntax @c help SELECT @example @@ -32789,6 +31911,14 @@ If you are using @code{FOR UPDATE} on a table handler with page/row locks, the examined rows will be write locked. @end itemize + +@menu +* JOIN:: +@end menu + +@node JOIN, , SELECT, SELECT +@subsubsection @code{JOIN} Syntax + @findex JOIN @findex INNER JOIN @findex CROSS JOIN @@ -32801,8 +31931,6 @@ the examined rows will be write locked. @findex NATURAL RIGHT JOIN @findex NATURAL RIGHT OUTER JOIN @findex STRAIGHT_JOIN -@node JOIN, INSERT, SELECT, Reference -@section @code{JOIN} Syntax @strong{MySQL} supports the following @code{JOIN} syntaxes for use in @code{SELECT} statements: @@ -32935,9 +32063,11 @@ mysql> select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND @xref{LEFT JOIN optimization, , @code{LEFT JOIN} optimization}. + +@node INSERT, INSERT DELAYED, SELECT, Data Manipulation +@subsection @code{INSERT} Syntax + @findex INSERT -@node INSERT, REPLACE, JOIN, Reference -@section @code{INSERT} Syntax @example INSERT [LOW_PRIORITY | DELAYED] [IGNORE] @@ -33063,13 +32193,14 @@ type. The column is set to the appropriate zero value for the type. @findex REPLACE ... SELECT @findex INSERT ... SELECT + @menu * INSERT SELECT:: -* INSERT DELAYED:: @end menu -@node INSERT SELECT, INSERT DELAYED, INSERT, INSERT -@subsection @code{INSERT ... SELECT} Syntax + +@node INSERT SELECT, , INSERT, INSERT +@subsubsection @code{INSERT ... SELECT} Syntax @example INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ... @@ -33113,11 +32244,14 @@ original tables, @strong{MySQL} will not allow concurrent inserts during You can of course also use @code{REPLACE} instead of @code{INSERT} to overwrite old rows. + +@node INSERT DELAYED, UPDATE, INSERT, Data Manipulation +@subsection @code{INSERT DELAYED} syntax + @findex INSERT DELAYED @findex DELAYED + @cindex INSERT DELAYED -@node INSERT DELAYED, , INSERT SELECT, INSERT -@subsection @code{INSERT DELAYED} syntax @example INSERT DELAYED ... @@ -33243,9 +32377,145 @@ server to handle a separate thread for each table on which you use @code{INSERT DELAYED}. This means that you should only use @code{INSERT DELAYED} when you are really sure you need it! + +@node UPDATE, DELETE, INSERT DELAYED, Data Manipulation +@subsection @code{UPDATE} Syntax + +@findex UPDATE + +@example +UPDATE [LOW_PRIORITY] [IGNORE] tbl_name + SET col_name1=expr1, [col_name2=expr2, ...] + [WHERE where_definition] + [LIMIT #] +@end example + +@code{UPDATE} updates columns in existing table rows with new values. +The @code{SET} clause indicates which columns to modify and the values +they should be given. The @code{WHERE} clause, if given, specifies +which rows should be updated. Otherwise all rows are updated. If the +@code{ORDER BY} clause is specified, the rows will be updated in the +order that is specified. + +If you specify the keyword @code{LOW_PRIORITY}, execution of the +@code{UPDATE} is delayed until no other clients are reading from the table. + +If you specify the keyword @code{IGNORE}, the update statement will not +abort even if we get duplicate key errors during the update. Rows that +would cause conflicts will not be updated. + +If you access a column from @code{tbl_name} in an expression, +@code{UPDATE} uses the current value of the column. For example, the +following statement sets the @code{age} column to one more than its +current value: + +@example +mysql> UPDATE persondata SET age=age+1; +@end example + +@code{UPDATE} assignments are evaluated from left to right. For example, the +following statement doubles the @code{age} column, then increments it: + +@example +mysql> UPDATE persondata SET age=age*2, age=age+1; +@end example + +If you set a column to the value it currently has, @strong{MySQL} notices +this and doesn't update it. + +@findex mysql_info() +@code{UPDATE} returns the number of rows that were actually changed. +In @strong{MySQL} Version 3.22 or later, the C API function @code{mysql_info()} +returns the number of rows that were matched and updated and the number of +warnings that occurred during the @code{UPDATE}. + +In @strong{MySQL} Version 3.23, you can use @code{LIMIT #} to ensure that +only a given number of rows are changed. + + +@node DELETE, TRUNCATE, UPDATE, Data Manipulation +@subsection @code{DELETE} Syntax + +@findex DELETE + +@example +DELETE [LOW_PRIORITY] FROM tbl_name + [WHERE where_definition] + [LIMIT rows] +@end example + +@code{DELETE} deletes rows from @code{tbl_name} that satisfy the condition +given by @code{where_definition}, and returns the number of records deleted. + +If you issue a @code{DELETE} with no @code{WHERE} clause, all rows are +deleted. If you do this in @code{AUTOCOMMIT} mode, this works as +@code{TRUNCATE}. @xref{TRUNCATE}. One problem with this is that +@code{DELETE} will return zero as the number of affected records, but +this will be fixed in 4.0. + +If you really want to know how many records are deleted when you are deleting +all rows, and are willing to suffer a speed penalty, you can use a +@code{DELETE} statement of this form: + +@example +mysql> DELETE FROM tbl_name WHERE 1>0; +@end example + +Note that this is MUCH slower than @code{DELETE FROM tbl_name} with no +@code{WHERE} clause, because it deletes rows one at a time. + +If you specify the keyword @code{LOW_PRIORITY}, execution of the +@code{DELETE} is delayed until no other clients are reading from the table. + +Deleted records are maintained in a linked list and subsequent @code{INSERT} +operations reuse old record positions. To reclaim unused space and reduce +file sizes, use the @code{OPTIMIZE TABLE} statement or the @code{myisamchk} +utility to reorganize tables. @code{OPTIMIZE TABLE} is easier, but +@code{myisamchk} is faster. +See @ref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}} and @ref{Optimization}. + +The @strong{MySQL}-specific @code{LIMIT rows} option to @code{DELETE} tells +the server the maximum number of rows to be deleted before control is +returned to the client. This can be used to ensure that a specific +@code{DELETE} command doesn't take too much time. You can simply repeat +the @code{DELETE} command until the number of affected rows is less than +the @code{LIMIT} value. + + +@node TRUNCATE, REPLACE, DELETE, Data Manipulation +@subsection @code{TRUNCATE} Syntax + +@findex TRUNCATE + +@example +TRUNCATE TABLE table_name +@end example + +Is in 3.23 and the same thing as @code{DELETE FROM table_name}. @xref{DELETE}. +The differences are: + +@itemize @bullet +@item +Implemented as a drop and re-create of the table, which makes this +much faster when deleting many rows. +@item +Not transaction-safe; @code{TRUNCATE TABLE} will automatically end the current +transaction as if @code{COMMIT} would have been called. +@item +Doesn't return the number of deleted rows. +@item +As long as the table definition file @file{table_name.frm} is +valid, the table can be re-created this way, even if the data or index +files have become corrupted. +@end itemize + +@code{TRUNCATE} is an Oracle SQL extension. + + +@node REPLACE, LOAD DATA, TRUNCATE, Data Manipulation +@subsection @code{REPLACE} Syntax + @findex REPLACE -@node REPLACE, LOAD DATA, INSERT, Reference -@section @code{REPLACE} Syntax @example REPLACE [LOW_PRIORITY | DELAYED] @@ -33275,9 +32545,11 @@ this case one row was inserted and then the duplicate was deleted. The above makes it easy to check if @code{REPLACE} added or replaced a row. + +@node LOAD DATA, , REPLACE, Data Manipulation +@subsection @code{LOAD DATA INFILE} Syntax + @findex LOAD DATA INFILE -@node LOAD DATA, UPDATE, REPLACE, Reference -@section @code{LOAD DATA INFILE} Syntax @example LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' @@ -33769,62 +33041,1022 @@ For more information about the efficiency of @code{INSERT} versus @code{LOAD DATA INFILE} and speeding up @code{LOAD DATA INFILE}, @xref{Insert speed}. -@findex UPDATE -@node UPDATE, USE, LOAD DATA, Reference -@section @code{UPDATE} Syntax + +@node Data Definition, Basic User Commands, Data Manipulation, Reference +@section Data Definition: @code{CREATE}, @code{DROP}, @code{ALTER} + +@menu +* CREATE DATABASE:: +* DROP DATABASE:: +* CREATE TABLE:: +* ALTER TABLE:: +* RENAME TABLE:: +* DROP TABLE:: +* CREATE INDEX:: +* DROP INDEX:: +@end menu + + +@node CREATE DATABASE, DROP DATABASE, Data Definition, Data Definition +@subsection @code{CREATE DATABASE} Syntax + +@findex CREATE DATABASE @example -UPDATE [LOW_PRIORITY] [IGNORE] tbl_name - SET col_name1=expr1, [col_name2=expr2, ...] - [WHERE where_definition] - [LIMIT #] +CREATE DATABASE [IF NOT EXISTS] db_name @end example -@code{UPDATE} updates columns in existing table rows with new values. -The @code{SET} clause indicates which columns to modify and the values -they should be given. The @code{WHERE} clause, if given, specifies -which rows should be updated. Otherwise all rows are updated. If the -@code{ORDER BY} clause is specified, the rows will be updated in the -order that is specified. +@code{CREATE DATABASE} creates a database with the given name. Rules for +allowable database names are given in @ref{Legal names}. An error occurs if +the database already exists and you didn't specify @code{IF NOT EXISTS}. -If you specify the keyword @code{LOW_PRIORITY}, execution of the -@code{UPDATE} is delayed until no other clients are reading from the table. +Databases in @strong{MySQL} are implemented as directories containing files +that correspond to tables in the database. Because there are no tables in a +database when it is initially created, the @code{CREATE DATABASE} statement +only creates a directory under the @strong{MySQL} data directory. -If you specify the keyword @code{IGNORE}, the update statement will not -abort even if we get duplicate key errors during the update. Rows that -would cause conflicts will not be updated. +@cindex @code{mysqladmin} +You can also create databases with @code{mysqladmin}. +@xref{Client-Side Scripts}. -If you access a column from @code{tbl_name} in an expression, -@code{UPDATE} uses the current value of the column. For example, the -following statement sets the @code{age} column to one more than its -current value: + +@node DROP DATABASE, CREATE TABLE, CREATE DATABASE, Data Definition +@subsection @code{DROP DATABASE} Syntax + +@findex DROP DATABASE @example -mysql> UPDATE persondata SET age=age+1; +DROP DATABASE [IF EXISTS] db_name @end example -@code{UPDATE} assignments are evaluated from left to right. For example, the -following statement doubles the @code{age} column, then increments it: +@code{DROP DATABASE} drops all tables in the database and deletes the +database. If you do a @code{DROP DATABASE} on a symbolic linked +database, both the link and the original database is deleted. @strong{Be +VERY careful with this command!} + +@code{DROP DATABASE} returns the number of files that were removed from +the database directory. Normally, this is three times the number of +tables, because normally each table corresponds to a @file{.MYD} file, a +@file{.MYI} file, and a @file{.frm} file. + +The @code{DROP DATABASE} command removes from the given database +directory all files with the following extensions: + +@multitable @columnfractions .25 .25 .25 .25 +@item .BAK @tab .DAT @tab .HSH @tab .ISD +@item .ISM @tab .ISM @tab .MRG @tab .MYD +@item .MYI @tab .db @tab .frm +@end multitable + +All subdirectories that consists of 2 digits (@code{RAID} directories) +are also removed. + +In @strong{MySQL} Version 3.22 or later, you can use the keywords +@code{IF EXISTS} to prevent an error from occurring if the database doesn't +exist. + +@cindex @code{mysqladmin} +You can also drop databases with @code{mysqladmin}. @xref{Client-Side Scripts}. + + +@menu +* CREATE TABLE:: +* ALTER TABLE:: +* RENAME TABLE:: +* DROP TABLE:: +* CREATE INDEX:: +* DROP INDEX:: +@end menu + +@node CREATE TABLE, ALTER TABLE, DROP DATABASE, Data Definition +@subsection @code{CREATE TABLE} Syntax + +@findex CREATE TABLE + +@menu +* Silent column changes:: Silent column changes +@end menu @example -mysql> UPDATE persondata SET age=age*2, age=age+1; +CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] +[table_options] [select_statement] + +create_definition: + col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] + [PRIMARY KEY] [reference_definition] + or PRIMARY KEY (index_col_name,...) + or KEY [index_name] (index_col_name,...) + or INDEX [index_name] (index_col_name,...) + or UNIQUE [INDEX] [index_name] (index_col_name,...) + or FULLTEXT [INDEX] [index_name] (index_col_name,...) + or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) + [reference_definition] + or CHECK (expr) + +type: + TINYINT[(length)] [UNSIGNED] [ZEROFILL] + or SMALLINT[(length)] [UNSIGNED] [ZEROFILL] + or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] + or INT[(length)] [UNSIGNED] [ZEROFILL] + or INTEGER[(length)] [UNSIGNED] [ZEROFILL] + or BIGINT[(length)] [UNSIGNED] [ZEROFILL] + or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] + or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] + or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] + or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] + or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] + or CHAR(length) [BINARY] + or VARCHAR(length) [BINARY] + or DATE + or TIME + or TIMESTAMP + or DATETIME + or TINYBLOB + or BLOB + or MEDIUMBLOB + or LONGBLOB + or TINYTEXT + or TEXT + or MEDIUMTEXT + or LONGTEXT + or ENUM(value1,value2,value3,...) + or SET(value1,value2,value3,...) + +index_col_name: + col_name [(length)] + +reference_definition: + REFERENCES tbl_name [(index_col_name,...)] + [MATCH FULL | MATCH PARTIAL] + [ON DELETE reference_option] + [ON UPDATE reference_option] + +reference_option: + RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT + +table_options: + TYPE = @{BDB | HEAP | ISAM | InnoDB | MERGE | MYISAM @} +or AUTO_INCREMENT = # +or AVG_ROW_LENGTH = # +or CHECKSUM = @{0 | 1@} +or COMMENT = "string" +or MAX_ROWS = # +or MIN_ROWS = # +or PACK_KEYS = @{0 | 1@} +or PASSWORD = "string" +or DELAY_KEY_WRITE = @{0 | 1@} +or ROW_FORMAT= @{ default | dynamic | fixed | compressed @} +or RAID_TYPE= @{1 | STRIPED | RAID0 @} RAID_CHUNKS=# RAID_CHUNKSIZE=# +or UNION = (table_name,[table_name...]) +or DATA DIRECTORY="directory" +or INDEX DIRECTORY="directory" + +select_statement: + [IGNORE | REPLACE] SELECT ... (Some legal select statement) @end example -If you set a column to the value it currently has, @strong{MySQL} notices -this and doesn't update it. +@code{CREATE TABLE} +creates a table with the given name in the current database. Rules for +allowable table names are given in @ref{Legal names}. An error occurs if +there is no current database or if the table already exists. + +In @strong{MySQL} Version 3.22 or later, the table name can be specified as +@code{db_name.tbl_name}. This works whether or not there is a current +database. + +In @strong{MySQL} Version 3.23, you can use the @code{TEMPORARY} keyword when +you create a table. A temporary table will automatically be deleted if a +connection dies and the name is per connection. This means that two different +connections can both use the same temporary table name without conflicting +with each other or with an existing table of the same name. (The existing table +is hidden until the temporary table is deleted). + +In @strong{MySQL} Version 3.23 or later, you can use the keywords +@code{IF NOT EXISTS} so that an error does not occur if the table already +exists. Note that there is no verification that the table structures are +identical. + +Each table @code{tbl_name} is represented by some files in the database +directory. In the case of MyISAM-type tables you will get: + +@multitable @columnfractions .2 .8 +@item @strong{File} @tab @strong{Purpose} +@item @code{tbl_name.frm} @tab Table definition (form) file +@item @code{tbl_name.MYD} @tab Data file +@item @code{tbl_name.MYI} @tab Index file +@end multitable + +For more information on the properties of the various column types, see +@ref{Column types}: + +@itemize @bullet +@item +If neither @code{NULL} nor @code{NOT NULL} is specified, the column +is treated as though @code{NULL} had been specified. + +@item +An integer column may have the additional attribute @code{AUTO_INCREMENT}. +When you insert a value of @code{NULL} (recommended) or @code{0} into an +@code{AUTO_INCREMENT} column, the column is set to @code{value+1}, where +@code{value} is the largest value for the column currently in the table. +@code{AUTO_INCREMENT} sequences begin with @code{1}. +@xref{mysql_insert_id, , @code{mysql_insert_id()}}. + +If you delete the row containing the maximum value for an +@code{AUTO_INCREMENT} column, the value will be reused with an +@code{ISAM}, or @code{BDB} table but not with a +@code{MyISAM} or @code{InnoDB} table. If you delete all rows in the table +with @code{DELETE FROM table_name} (without a @code{WHERE}) in +@code{AUTOCOMMIT} mode, the sequence starts over for all table types. + +@strong{NOTE:} There can be only one @code{AUTO_INCREMENT} column per +table, and it must be indexed. @strong{MySQL} Version 3.23 will also only +work properly if the auto_increment column only has positive +values. Inserting a negative number is regarded as inserting a very large +positive number. This is done to avoid precision problems when +numbers 'wrap' over from positive to negative and also to ensure that one +doesn't accidentally get an auto_increment column that contains 0. + +@cindex ODBC compatibility +@cindex compatibility, with ODBC +To make @strong{MySQL} compatible with some ODBC applications, you can find +the last inserted row with the following query: + +@example +SELECT * FROM tbl_name WHERE auto_col IS NULL +@end example + +@item +@code{NULL} values are handled differently for @code{TIMESTAMP} columns than +for other column types. You cannot store a literal @code{NULL} in a +@code{TIMESTAMP} column; setting the column to @code{NULL} sets it to the +current date and time. Because @code{TIMESTAMP} columns behave this way, the +@code{NULL} and @code{NOT NULL} attributes do not apply in the normal way and +are ignored if you specify them. + +On the other hand, to make it easier for @strong{MySQL} clients to use +@code{TIMESTAMP} columns, the server reports that such columns may be +assigned @code{NULL} values (which is true), even though @code{TIMESTAMP} +never actually will contain a @code{NULL} value. You can see this when you +use @code{DESCRIBE tbl_name} to get a description of your table. + +Note that setting a @code{TIMESTAMP} column to @code{0} is not the same +as setting it to @code{NULL}, because @code{0} is a valid @code{TIMESTAMP} +value. + +@item +If no @code{DEFAULT} value is specified for a column, @strong{MySQL} +automatically assigns one. + +If the column may take @code{NULL} as a value, the default value is +@code{NULL}. + +If the column is declared as @code{NOT NULL}, the default value depends on +the column type: + +@itemize @minus +@item +For numeric types other than those declared with the @code{AUTO_INCREMENT} +attribute, the default is @code{0}. For an @code{AUTO_INCREMENT} column, the +default value is the next value in the sequence. + +@item +For date and time types other than @code{TIMESTAMP}, the default is the +appropriate zero value for the type. For the first @code{TIMESTAMP} +column in a table, the default value is the current date and time. +@xref{Date and time types}. + +@item +For string types other than @code{ENUM}, the default value is the empty string. +For @code{ENUM}, the default is the first enumeration value. +@end itemize + +Default values must be constants. This means, for example, that you cannot +set the default for a date column to be the value of a function such as +@code{NOW()} or @code{CURRENT_DATE}. + +@item +@code{KEY} is a synonym for @code{INDEX}. + +@item +In @strong{MySQL}, a @code{UNIQUE} key can have only distinct values. An +error occurs if you try to add a new row with a key that matches an existing +row. +@item +@tindex PRIMARY KEY +A @code{PRIMARY KEY} is a unique @code{KEY} with the extra constraint +that all key columns must be defined as @code{NOT NULL}. In @strong{MySQL} +the key is named @code{PRIMARY}. A table can have only one @code{PRIMARY KEY}. +If you don't have a @code{PRIMARY KEY} and some applications ask for the +@code{PRIMARY KEY} in your tables, @strong{MySQL} will return the first +@code{UNIQUE} key, which doesn't have any @code{NULL} columns, as the +@code{PRIMARY KEY}. + +@item +A @code{PRIMARY KEY} can be a multiple-column index. However, you cannot +create a multiple-column index using the @code{PRIMARY KEY} key attibute in a +column specification. Doing so will mark only that single column as primary. +You must use the @code{PRIMARY KEY(index_col_name, ...)} syntax. + +@item +If the @code{PRIMARY} or @code{UNIQUE} key consists of only one column and this +is of type integer, you can also refer to it as @code{_rowid} +(new in Version 3.23.11). + +@item +If you don't assign a name to an index, the index will be assigned the same +name as the first @code{index_col_name}, with an optional suffix (@code{_2}, +@code{_3}, @code{...}) to make it unique. You can see index names for a +table using @code{SHOW INDEX FROM tbl_name}. +@xref{SHOW, , @code{SHOW}}. + +@item +@cindex @code{NULL} values, and indexes +@cindex indexes, and @code{NULL} values +Only the @code{MyISAM} table type supports indexes on columns that can have +@code{NULL} values. In other cases you must declare such columns +@code{NOT NULL} or an error results. + +@item +With @code{col_name(length)} syntax, you can specify an index that +uses only a part of a @code{CHAR} or @code{VARCHAR} column. This can +make the index file much smaller. +@xref{Indexes}. + +@item +@cindex @code{BLOB} columns, indexing +@cindex indexes, and @code{BLOB} columns +@cindex @code{TEXT} columns, indexing +@cindex indexes, and @code{TEXT} columns +Only the @code{MyISAM} table type supports indexing on @code{BLOB} and +@code{TEXT} columns. When putting an index on a @code{BLOB} or @code{TEXT} +column you MUST always specify the length of the index: +@example +CREATE TABLE test (blob_col BLOB, index(blob_col(10))); +@end example + +@item +When you use @code{ORDER BY} or @code{GROUP BY} with a @code{TEXT} or +@code{BLOB} column, only the first @code{max_sort_length} bytes are used. +@xref{BLOB, , @code{BLOB}}. + +@item +In @strong{MySQL} Version 3.23.23 or later, you can also create special +@strong{FULLTEXT} indexes. They are used for full-text search. Only the +@code{MyISAM} table type supports @code{FULLTEXT} indexes. They can be created +only from @code{VARCHAR} and @code{TEXT} columns. +Indexing always happens over the entire column, partial indexing is not +supported. See @ref{Fulltext Search} for details of operation. + +@item +The @code{FOREIGN KEY}, @code{CHECK}, and @code{REFERENCES} clauses don't +actually do anything. The syntax for them is provided only for compatibility, +to make it easier to port code from other SQL servers and to run applications +that create tables with references. +@xref{Missing functions}. + +@item +Each @code{NULL} column takes one bit extra, rounded up to the nearest byte. + +@item +The maximum record length in bytes can be calculated as follows: + +@example +row length = 1 + + (sum of column lengths) + + (number of NULL columns + 7)/8 + + (number of variable-length columns) +@end example + +@item +The @code{table_options} and @code{SELECT} options are only +implemented in @strong{MySQL} Version 3.23 and above. + +The different table types are: + +@multitable @columnfractions .20 .80 +@item BDB or Berkeley_db @tab Transaction-safe tables with page locking. @xref{BDB}. +@item HEAP @tab The data for this table is only stored in memory. @xref{HEAP}. +@item ISAM @tab The original table handler. @xref{ISAM}. +@item InnoDB @tab Transaction-safe tables with row locking. @xref{InnoDB}. +@item MERGE @tab A collection of MyISAM tables used as one table. @xref{MERGE}. +@item MyISAM @tab The new binary portable table handler that is replacing ISAM. @xref{MyISAM}. +@end multitable +@xref{Table types}. + +If a table type is specified, and that particular type is not available, +@strong{MySQL} will choose the closest table type to the one that you have +specified. For example, if @code{TYPE=BDB} is specified, and that distribution +of @strong{MySQL} does not support @code{BDB} tables, the table will be created +as @code{MyISAM} instead. + +The other table options are used to optimize the behavior of the +table. In most cases, you don't have to specify any of them. +The options work for all table types, if not otherwise indicated: + +@multitable @columnfractions .20 .80 +@item @code{AUTO_INCREMENT} @tab The next auto_increment value you want to set for your table (MyISAM). +@item @code{AVG_ROW_LENGTH} @tab An approximation of the average row length for your table. You only need to set this for large tables with variable size records. +@item @code{CHECKSUM} @tab Set this to 1 if you want @strong{MySQL} to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM). +@item @code{COMMENT} @tab A 60-character comment for your table. +@item @code{MAX_ROWS} @tab Max number of rows you plan to store in the table. +@item @code{MIN_ROWS} @tab Minimum number of rows you plan to store in the table. +@item @code{PACK_KEYS} @tab Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM). +@item @code{PASSWORD} @tab Encrypt the @code{.frm} file with a password. This option doesn't do anything in the standard @strong{MySQL} version. +@item @code{DELAY_KEY_WRITE} @tab Set this to 1 if want to delay key table updates until the table is closed (MyISAM). +@item @code{ROW_FORMAT} @tab Defines how the rows should be stored. Currently you can only use the DYNAMIC and STATIC options for MyISAM tables. +@end multitable + +When you use a @code{MyISAM} table, @strong{MySQL} uses the product of +@code{max_rows * avg_row_length} to decide how big the resulting table +will be. If you don't specify any of the above options, the maximum size +for a table will be 4G (or 2G if your operating systems only supports 2G +tables). The reason for this is just to keep down the pointer sizes +to make the index smaller and faster if you don't really need big files. + +If you don't use @code{PACK_KEYS}, the default is to only pack strings, +not numbers. If you use @code{PACK_KEYS=1}, numbers will be packed as well. + +When packing binary number keys, @strong{MySQL} will use prefix compression. +This means that you will only get a big benefit of this if you have +many numbers that are the same. Prefix compression means that every +key needs one extra byte to indicate how many bytes of the previous key are +the same for the next key (note that the pointer to the row is stored +in high-byte-first-order directly after the key, to improve +compression.) This means that if you have many equal keys on two rows +in a row, all following 'same' keys will usually only take 2 bytes +(including the pointer to the row). Compare this to the ordinary case +where the following keys will take storage_size_for_key + +pointer_size (usually 4). On the other hand, if all keys are +totally different, you will lose 1 byte per key, if the key isn't a +key that can have @code{NULL} values (In this case the packed key length will +be stored in the same byte that is used to mark if a key is @code{NULL}.) + +@item +If you specify a @code{SELECT} after the @code{CREATE} statement, +@strong{MySQL} will create new fields for all elements in the +@code{SELECT}. For example: + +@example +mysql> CREATE TABLE test (a int not null auto_increment, + primary key (a), key(b)) + TYPE=MyISAM SELECT b,c from test2; +@end example + +This will create a @code{MyISAM} table with three columns, a, b, and c. +Notice that the columns from the @code{SELECT} statement are appended to +the right side of the table, not overlapped onto it. Take the following +example: + +@example +mysql> select * from foo; ++---+ +| n | ++---+ +| 1 | ++---+ + +mysql> create table bar (m int) select n from foo; +Query OK, 1 row affected (0.02 sec) +Records: 1 Duplicates: 0 Warnings: 0 + +mysql> select * from bar; ++------+---+ +| m | n | ++------+---+ +| NULL | 1 | ++------+---+ +1 row in set (0.00 sec) +@end example + +For each row in table @code{foo}, a row is inserted in @code{bar} with +the values from @code{foo} and default values for the new columns. + +@code{CREATE TABLE ... SELECT} will not automaticly create any indexes +for you. This is done intentionally to make the command as flexible as +possible. If you want to have indexes in the created table, you should +specify these before the @code{SELECT} statement: + +@example +mysql> create table bar (unique (n)) select n from foo; +@end example + +If any errors occur while copying the data to the table, it will +automatically be deleted. + +To ensure that the update log/binary log can be used to re-create the +original tables, @strong{MySQL} will not allow concurrent inserts during +@code{CREATE TABLE .... SELECT}. +@item +The @code{RAID_TYPE} option will help you to break the 2G/4G limit for +the MyISAM data file (not the index file) on +operating systems that don't support big files. You can get also more speed +from the I/O bottleneck by putting @code{RAID} directories on different +physical disks. @code{RAID_TYPE} will work on any OS, as long as you have +configured @strong{MySQL} with @code{--with-raid}. For now the only allowed +@code{RAID_TYPE} is @code{STRIPED} (@code{1} and @code{RAID0} are aliases +for this). + +If you specify @code{RAID_TYPE=STRIPED} for a @code{MyISAM} table, +@code{MyISAM} will create @code{RAID_CHUNKS} subdirectories named 00, +01, 02 in the database directory. In each of these directories +@code{MyISAM} will create a @code{table_name.MYD}. When writing data +to the data file, the @code{RAID} handler will map the first +@code{RAID_CHUNKSIZE} *1024 bytes to the first file, the next +@code{RAID_CHUNKSIZE} *1024 bytes to the next file and so on. +@item +@code{UNION} is used when you want to use a collection of identical +tables as one. This only works with MERGE tables. @xref{MERGE}. + +For the moment you need to have @code{SELECT}, @code{UPDATE}, and +@code{DELETE} privileges on the tables you map to a @code{MERGE} table. +All mapped tables must be in the same database as the @code{MERGE} table. +@item +In the created table the @code{PRIMARY} key will be placed first, followed +by all @code{UNIQUE} keys and then the normal keys. This helps the +@strong{MySQL} optimizer to prioritize which key to use and also more quickly +detect duplicated @code{UNIQUE} keys. + +@item +By using @code{DATA DIRECTORY="directory"} or @code{INDEX +DIRECTORY="directory"} you can specify where the table handler should +put it's table and index files. This only works for @code{MyISAM} tables +in @code{MySQL} 4.0, when you are not using the @code{--skip-symlink} +option. @xref{Symbolic links to tables}. + +@end itemize + + +@node Silent column changes, , CREATE TABLE, CREATE TABLE +@subsubsection Silent Column Specification Changes + +@cindex silent column changes + +In some cases, @strong{MySQL} silently changes a column specification from +that given in a @code{CREATE TABLE} statement. (This may also occur with +@code{ALTER TABLE}.): + +@itemize @bullet +@item +@code{VARCHAR} columns with a length less than four are changed to +@code{CHAR}. + +@item +If any column in a table has a variable length, the entire row is +variable-length as a result. Therefore, if a table contains any +variable-length columns (@code{VARCHAR}, @code{TEXT}, or @code{BLOB}), +all @code{CHAR} columns longer than three characters are changed to +@code{VARCHAR} columns. This doesn't affect how you use the columns in +any way; in @strong{MySQL}, @code{VARCHAR} is just a different way to +store characters. @strong{MySQL} performs this conversion because it +saves space and makes table operations faster. @xref{Table types}. + +@item +@code{TIMESTAMP} display sizes must be even and in the range from 2 to 14. +If you specify a display size of 0 or greater than 14, the size is coerced +to 14. Odd-valued sizes in the range from 1 to 13 are coerced +to the next higher even number. + +@item +You cannot store a literal @code{NULL} in a @code{TIMESTAMP} column; setting +it to @code{NULL} sets it to the current date and time. Because +@code{TIMESTAMP} columns behave this way, the @code{NULL} and @code{NOT NULL} +attributes do not apply in the normal way and are ignored if you specify +them. @code{DESCRIBE tbl_name} always reports that a @code{TIMESTAMP} +column may be assigned @code{NULL} values. + +@item +@strong{MySQL} maps certain column types used by other SQL database vendors +to @strong{MySQL} types. @xref{Other-vendor column types}. +@end itemize + +If you want to see whether or not @strong{MySQL} used a column type other +than the one you specified, issue a @code{DESCRIBE tbl_name} statement after +creating or altering your table. + +@cindex @code{myisampack} +Certain other column type changes may occur if you compress a table +using @code{myisampack}. @xref{Compressed format}. + + +@node ALTER TABLE, RENAME TABLE, CREATE TABLE, Data Definition +@subsection @code{ALTER TABLE} Syntax + +@findex ALTER TABLE + +@example +ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] + +alter_specification: + ADD [COLUMN] create_definition [FIRST | AFTER column_name ] + or ADD [COLUMN] (create_definition, create_definition,...) + or ADD INDEX [index_name] (index_col_name,...) + or ADD PRIMARY KEY (index_col_name,...) + or ADD UNIQUE [index_name] (index_col_name,...) + or ADD FULLTEXT [index_name] (index_col_name,...) + or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) + [reference_definition] + or ALTER [COLUMN] col_name @{SET DEFAULT literal | DROP DEFAULT@} + or CHANGE [COLUMN] old_col_name create_definition + or MODIFY [COLUMN] create_definition + or DROP [COLUMN] col_name + or DROP PRIMARY KEY + or DROP INDEX index_name + or RENAME [TO] new_tbl_name + or ORDER BY col + or table_options +@end example + +@code{ALTER TABLE} allows you to change the structure of an existing table. +For example, you can add or delete columns, create or destroy indexes, change +the type of existing columns, or rename columns or the table itself. You can +also change the comment for the table and type of the table. +@xref{CREATE TABLE, , @code{CREATE TABLE}}. + +If you use @code{ALTER TABLE} to change a column specification but +@code{DESCRIBE tbl_name} indicates that your column was not changed, it is +possible that @strong{MySQL} ignored your modification for one of the reasons +described in @ref{Silent column changes}. For example, if you try to change +a @code{VARCHAR} column to @code{CHAR}, @strong{MySQL} will still use +@code{VARCHAR} if the table contains other variable-length columns. + +@code{ALTER TABLE} works by making a temporary copy of the original table. +The alteration is performed on the copy, then the original table is +deleted and the new one is renamed. This is done in such a way that +all updates are automatically redirected to the new table without +any failed updates. While @code{ALTER TABLE} is executing, the original +table is readable by other clients. Updates and writes to the table +are stalled until the new table is ready. + +Note that if you use any other option to @code{ALTER TABLE} than +@code{RENAME}, @strong{MySQL} will always create a temporary table, even +if the data wouldn't strictly need to be copied (like when you change the +name of a column). We plan to fix this in the future, but as one doesn't +normally do @code{ALTER TABLE} that often this isn't that high on our TODO. + +@itemize @bullet +@item +To use @code{ALTER TABLE}, you need @strong{ALTER}, @strong{INSERT}, +and @strong{CREATE} privileges on the table. + +@item +@code{IGNORE} is a @strong{MySQL} extension to ANSI SQL92. +It controls how @code{ALTER TABLE} works if there are duplicates on +unique keys in the new table. +If @code{IGNORE} isn't specified, the copy is aborted and rolled back. +If @code{IGNORE} is specified, then for rows with duplicates on a unique +key, only the first row is used; the others are deleted. + +@item +You can issue multiple @code{ADD}, @code{ALTER}, @code{DROP}, and +@code{CHANGE} clauses in a single @code{ALTER TABLE} statement. This is a +@strong{MySQL} extension to ANSI SQL92, which allows only one of each clause +per @code{ALTER TABLE} statement. + +@item +@code{CHANGE col_name}, @code{DROP col_name}, and @code{DROP +INDEX} are @strong{MySQL} extensions to ANSI SQL92. + +@item +@code{MODIFY} is an Oracle extension to @code{ALTER TABLE}. + +@item +The optional word @code{COLUMN} is a pure noise word and can be omitted. + +@item +If you use @code{ALTER TABLE tbl_name RENAME TO new_name} without any other +options, @strong{MySQL} simply renames the files that correspond to the table +@code{tbl_name}. There is no need to create the temporary table. +@xref{RENAME TABLE,, @code{RENAME TABLE}}. + +@item +@code{create_definition} clauses use the same syntax for @code{ADD} and +@code{CHANGE} as for @code{CREATE TABLE}. Note that this syntax includes +the column name, not just the column type. +@xref{CREATE TABLE, , @code{CREATE TABLE}}. + +@item +You can rename a column using a @code{CHANGE old_col_name create_definition} +clause. To do so, specify the old and new column names and the type that +the column currently has. For example, to rename an @code{INTEGER} column +from @code{a} to @code{b}, you can do this: + +@example +mysql> ALTER TABLE t1 CHANGE a b INTEGER; +@end example + +If you want to change a column's type but not the name, @code{CHANGE} +syntax still requires two column names even if they are the same. For +example: + +@example +mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; +@end example + +However, as of @strong{MySQL} Version 3.22.16a, you can also use @code{MODIFY} +to change a column's type without renaming it: + +@example +mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL; +@end example + +@item +If you use @code{CHANGE} or @code{MODIFY} to shorten a column for which +an index exists on part of the column (for instance, if you have an index +on the first 10 characters of a @code{VARCHAR} column), you cannot make +the column shorter than the number of characters that are indexed. + +@item +When you change a column type using @code{CHANGE} or @code{MODIFY}, +@strong{MySQL} tries to convert data to the new type as well as possible. + +@item +In @strong{MySQL} Version 3.22 or later, you can use @code{FIRST} or +@code{ADD ... AFTER col_name} to add a column at a specific position within +a table row. The default is to add the column last. + +@findex ALTER COLUMN +@item +@code{ALTER COLUMN} specifies a new default value for a column +or removes the old default value. +If the old default is removed and the column can be @code{NULL}, the new +default is @code{NULL}. If the column cannot be @code{NULL}, @strong{MySQL} +assigns a default value, as described in +@ref{CREATE TABLE, , @code{CREATE TABLE}}. + +@findex DROP INDEX +@item +@code{DROP INDEX} removes an index. This is a @strong{MySQL} extension to +ANSI SQL92. @xref{DROP INDEX}. + +@item +If columns are dropped from a table, the columns are also removed from any +index of which they are a part. If all columns that make up an index are +dropped, the index is dropped as well. + +@item +If a table contains only one column, the column cannot be dropped. +If what you intend is to remove the table, use @code{DROP TABLE} instead. + +@findex DROP PRIMARY KEY +@item +@code{DROP PRIMARY KEY} drops the primary index. If no such +index exists, it drops the first @code{UNIQUE} index in the table. +(@strong{MySQL} marks the first @code{UNIQUE} key as the @code{PRIMARY KEY} +if no @code{PRIMARY KEY} was specified explicitly.) + +@findex ORDER BY +@item +@code{ORDER BY} allows you to create the new table with the rows in a +specific order. Note that the table will not remain in this order after +inserts and deletes. In some cases, it may make sorting easier for +@strong{MySQL} if the table is in order by the column that you wish to +order it by later. This option is mainly useful when you know that you +are mostly going to query the rows in a certain order; By using this +option after big changes to the table, you may be able to get higher +performance. + +@findex ALTER TABLE +@item +If you use @code{ALTER TABLE} on a @code{MyISAM} table, all non-unique +indexes are created in a separate batch (like in @code{REPAIR}). +This should make @code{ALTER TABLE} much faster when you have many indexes. + +@item @findex mysql_info() -@code{UPDATE} returns the number of rows that were actually changed. -In @strong{MySQL} Version 3.22 or later, the C API function @code{mysql_info()} -returns the number of rows that were matched and updated and the number of -warnings that occurred during the @code{UPDATE}. +With the C API function @code{mysql_info()}, you can find out how many +records were copied, and (when @code{IGNORE} is used) how many records were +deleted due to duplication of unique key values. -In @strong{MySQL} Version 3.23, you can use @code{LIMIT #} to ensure that -only a given number of rows are changed. +@item +@cindex foreign keys +@cindex references +The @code{FOREIGN KEY}, @code{CHECK}, and @code{REFERENCES} clauses don't +actually do anything. The syntax for them is provided only for compatibility, +to make it easier to port code from other SQL servers and to run applications +that create tables with references. +@xref{Missing functions}. +@end itemize + +Here is an example that shows some of the uses of @code{ALTER TABLE}. We +begin with a table @code{t1} that is created as shown below: + +@example +mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10)); +@end example + +To rename the table from @code{t1} to @code{t2}: + +@example +mysql> ALTER TABLE t1 RENAME t2; +@end example + +To change column @code{a} from @code{INTEGER} to @code{TINYINT NOT NULL} +(leaving the name the same), and to change column @code{b} from +@code{CHAR(10)} to @code{CHAR(20)} as well as renaming it from @code{b} to +@code{c}: + +@example +mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); +@end example + +To add a new @code{TIMESTAMP} column named @code{d}: + +@example +mysql> ALTER TABLE t2 ADD d TIMESTAMP; +@end example + +To add an index on column @code{d}, and make column @code{a} the primary key: + +@example +mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); +@end example + +To remove column @code{c}: + +@example +mysql> ALTER TABLE t2 DROP COLUMN c; +@end example + +To add a new @code{AUTO_INCREMENT} integer column named @code{c}: + +@example +mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, + ADD INDEX (c); +@end example + +Note that we indexed @code{c}, because @code{AUTO_INCREMENT} columns must be +indexed, and also that we declare @code{c} as @code{NOT NULL}, because +indexed columns cannot be @code{NULL}. + +When you add an @code{AUTO_INCREMENT} column, column values are filled in +with sequence numbers for you automatically. You can set the first +sequence number by executing @code{SET INSERT_ID=#} before +@code{ALTER TABLE} or using the @code{AUTO_INCREMENT = #} table option. +@xref{SET OPTION}. + +With MyISAM tables, if you don't change the @code{AUTO_INCREMENT} +column, the sequence number will not be affected. If you drop an +@code{AUTO_INCREMENT} column and then add another @code{AUTO_INCREMENT} +column, the numbers will start from 1 again. + +@xref{ALTER TABLE problems}. + + +@node RENAME TABLE, DROP TABLE, ALTER TABLE, Data Definition +@subsection @code{RENAME TABLE} Syntax + +@findex RENAME TABLE + +@example +RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...] +@end example + +The rename is done atomically, which means that no other thread can +access any of the tables while the rename is running. This makes it +possible to replace a table with an empty one: + +@example +CREATE TABLE new_table (...); +RENAME TABLE old_table TO backup_table, new_table TO old_table; +@end example + +The rename is done from left to right, which means that if you want to +swap two tables names, you have to: + +@example +RENAME TABLE old_table TO backup_table, + new_table TO old_table, + backup_table TO new_table; +@end example + +As long as two databases are on the same disk you can also rename +from one database to another: + +@example +RENAME TABLE current_database.table_name TO other_database.table_name; +@end example + +When you execute @code{RENAME}, you can't have any locked tables or +active transactions. You must also have the @code{ALTER} and @code{DROP} +privilege on the original table and @code{CREATE} and @code{INSERT} +privilege on the new table. + +If @strong{MySQL} encounters any errors in a multiple table rename, it +will do a reverse rename for all renamed tables to get everything back +to the original state. + + +@node DROP TABLE, CREATE INDEX, RENAME TABLE, Data Definition +@subsection @code{DROP TABLE} Syntax + +@findex DROP TABLE + +@example +DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE] +@end example + +@code{DROP TABLE} removes one or more tables. All table data and the table +definition are @emph{removed}, so @strong{be careful} with this command! + +In @strong{MySQL} Version 3.22 or later, you can use the keywords +@code{IF EXISTS} to prevent an error from occurring for tables that don't +exist. + +@code{RESTRICT} and @code{CASCADE} are allowed to make porting easier. +For the moment they don't do anything. + +@strong{NOTE}: @code{DROP TABLE} is not transaction-safe and will +automatically commit any active transactions. + + +@node CREATE INDEX, DROP INDEX, DROP TABLE, Data Definition +@subsection @code{CREATE INDEX} Syntax + +@findex CREATE INDEX + +@cindex indexes +@cindex indexes, multi-part +@cindex multi-part index + +@example +CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... ) +@end example + +The @code{CREATE INDEX} statement doesn't do anything in @strong{MySQL} prior +to Version 3.22. In Version 3.22 or later, @code{CREATE INDEX} is mapped to an +@code{ALTER TABLE} statement to create indexes. +@xref{ALTER TABLE, , @code{ALTER TABLE}}. + +Normally, you create all indexes on a table at the time the table itself +is created with @code{CREATE TABLE}. +@xref{CREATE TABLE, , @code{CREATE TABLE}}. +@code{CREATE INDEX} allows you to add indexes to existing tables. + +A column list of the form @code{(col1,col2,...)} creates a multiple-column +index. Index values are formed by concatenating the values of the given +columns. + +For @code{CHAR} and @code{VARCHAR} columns, indexes can be created that +use only part of a column, using @code{col_name(length)} syntax. (On +@code{BLOB} and @code{TEXT} columns the length is required). The +statement shown below creates an index using the first 10 characters of +the @code{name} column: + +@example +mysql> CREATE INDEX part_of_name ON customer (name(10)); +@end example + +Because most names usually differ in the first 10 characters, this index should +not be much slower than an index created from the entire @code{name} column. +Also, using partial columns for indexes can make the index file much smaller, +which could save a lot of disk space and might also speed up @code{INSERT} +operations! + +Note that you can only add an index on a column that can have @code{NULL} +values or on a @code{BLOB}/@code{TEXT} column if you are using +@strong{MySQL} Version 3.23.2 or newer and are using the @code{MyISAM} +table type. + +For more information about how @strong{MySQL} uses indexes, see +@ref{MySQL indexes, , @strong{MySQL} indexes}. + +@code{FULLTEXT} indexes can index only @code{VARCHAR} and +@code{TEXT} columns, and only in @code{MyISAM} tables. @code{FULLTEXT} indexes +are available in @strong{MySQL} Version 3.23.23 and later. +@ref{Fulltext Search}. + + +@node DROP INDEX, , CREATE INDEX, Data Definition +@subsection @code{DROP INDEX} Syntax + +@findex DROP INDEX + +@example +DROP INDEX index_name ON tbl_name +@end example + +@code{DROP INDEX} drops the index named @code{index_name} from the table +@code{tbl_name}. @code{DROP INDEX} doesn't do anything in @strong{MySQL} +prior to Version 3.22. In Version 3.22 or later, @code{DROP INDEX} is mapped to an +@code{ALTER TABLE} statement to drop the index. +@xref{ALTER TABLE, , @code{ALTER TABLE}}. + + +@node Basic User Commands, Transactional Commands, Data Definition, Reference +@section Basic MySQL User Utility Commands + +@menu +* USE:: +* DESCRIBE:: +@end menu + + +@node USE, DESCRIBE, Basic User Commands, Basic User Commands +@subsection @code{USE} Syntax @findex USE -@node USE, DESCRIBE, UPDATE, Reference -@section @code{USE} Syntax @example USE db_name @@ -33858,11 +34090,11 @@ mysql> SELECT author_name,editor_name FROM author,db2.editor The @code{USE} statement is provided for Sybase compatibility. +@node DESCRIBE, , USE, Basic User Commands +@subsection @code{DESCRIBE} Syntax (Get Information About Columns) @findex DESC @findex DESCRIBE -@node DESCRIBE, COMMIT, USE, Reference -@section @code{DESCRIBE} Syntax (Get Information About Columns) @example @{DESCRIBE | DESC@} tbl_name @{col_name | wild@} @@ -33886,11 +34118,23 @@ This statement is provided for Oracle compatibility. The @code{SHOW} statement provides similar information. @xref{SHOW, , @code{SHOW}}. + +@node Transactional Commands, Fulltext Search, Basic User Commands, Reference +@section MySQL Transactional and Locking Commands + +@menu +* COMMIT:: +* LOCK TABLES:: +* SET TRANSACTION:: +@end menu + + +@node COMMIT, LOCK TABLES, Transactional Commands, Transactional Commands +@subsection @code{BEGIN/COMMIT/ROLLBACK} Syntax + @findex BEGIN @findex COMMIT @findex ROLLBACK -@node COMMIT, LOCK TABLES, DESCRIBE, Reference -@section @code{BEGIN/COMMIT/ROLLBACK} Syntax By default, @strong{MySQL} runs in @code{autocommit} mode. This means that as soon as you execute an update, @strong{MySQL} will store the update on @@ -33944,10 +34188,12 @@ a @code{COMMIT} before executing the command): You can change the isolation level for transactions with @code{SET TRANSACTION ISOLATION LEVEL ...}. @xref{SET TRANSACTION}. + +@node LOCK TABLES, SET TRANSACTION, COMMIT, Transactional Commands +@subsection @code{LOCK TABLES/UNLOCK TABLES} Syntax + @findex LOCK TABLES @findex UNLOCK TABLES -@node LOCK TABLES, CREATE INDEX, COMMIT, Reference -@section @code{LOCK TABLES/UNLOCK TABLES} Syntax @example LOCK TABLES tbl_name [AS alias] @{READ | [READ LOCAL] | [LOW_PRIORITY] WRITE@} @@ -34081,276 +34327,295 @@ automatically commit any active transactions before attempting to lock the tables. -@node CREATE INDEX, DROP INDEX, LOCK TABLES, Reference -@section @code{CREATE INDEX} Syntax +@node SET TRANSACTION, , LOCK TABLES, Transactional Commands +@subsection @code{SET TRANSACTION} Syntax -@findex CREATE INDEX - -@cindex indexes -@cindex indexes, multi-part -@cindex multi-part index +@findex ISOLATION LEVEL @example -CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... ) +SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL +[READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE] @end example -The @code{CREATE INDEX} statement doesn't do anything in @strong{MySQL} prior -to Version 3.22. In Version 3.22 or later, @code{CREATE INDEX} is mapped to an -@code{ALTER TABLE} statement to create indexes. -@xref{ALTER TABLE, , @code{ALTER TABLE}}. +Sets the transaction isolation level for the global, whole session or +the next transaction. -Normally, you create all indexes on a table at the time the table itself -is created with @code{CREATE TABLE}. -@xref{CREATE TABLE, , @code{CREATE TABLE}}. -@code{CREATE INDEX} allows you to add indexes to existing tables. +The default behavior is to set the isolation level for the next (not started) +transaction. -A column list of the form @code{(col1,col2,...)} creates a multiple-column -index. Index values are formed by concatenating the values of the given -columns. +If you set the @code{GLOBAL} privilege it will affect all new created threads. +You will need the @code{PROCESS} privilege to do do this. -For @code{CHAR} and @code{VARCHAR} columns, indexes can be created that -use only part of a column, using @code{col_name(length)} syntax. (On -@code{BLOB} and @code{TEXT} columns the length is required). The -statement shown below creates an index using the first 10 characters of -the @code{name} column: +Setting the @code{SESSION} privilege will affect the following and all +future transactions. + +You can set the default isolation level for @code{mysqld} with +@code{--transaction-isolation=...}. @xref{Command-line options}. + + +@node Fulltext Search, , Transactional Commands, Reference +@section MySQL Full-text Search + +@cindex searching, full-text +@cindex full-text search +@cindex FULLTEXT + +Since Version 3.23.23, @strong{MySQL} has support for full-text indexing +and searching. Full-text indexes in @strong{MySQL} are an index of type +@code{FULLTEXT}. @code{FULLTEXT} indexes can be created from @code{VARCHAR} +and @code{TEXT} columns at @code{CREATE TABLE} time or added later with +@code{ALTER TABLE} or @code{CREATE INDEX}. For large datasets, adding +@code{FULLTEXT} index with @code{ALTER TABLE} (or @code{CREATE INDEX}) would +be much faster than inserting rows into the empty table with a @code{FULLTEXT} +index. + +Full-text search is performed with the @code{MATCH} function. @example -mysql> CREATE INDEX part_of_name ON customer (name(10)); -@end example +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) -Because most names usually differ in the first 10 characters, this index should -not be much slower than an index created from the entire @code{name} column. -Also, using partial columns for indexes can make the index file much smaller, -which could save a lot of disk space and might also speed up @code{INSERT} -operations! +mysql> INSERT INTO articles VALUES + -> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'), + -> (0,'How To Use MySQL Efficiently', 'After you went through a ...'), + -> (0,'Optimizing MySQL','In this tutorial we will show how to ...'), + -> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalize ...'), + -> (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'), + -> (0,'MySQL Security', 'When configured properly, MySQL could be ...'); +Query OK, 5 rows affected (0.00 sec) +Records: 5 Duplicates: 0 Warnings: 0 -Note that you can only add an index on a column that can have @code{NULL} -values or on a @code{BLOB}/@code{TEXT} column if you are using -@strong{MySQL} Version 3.23.2 or newer and are using the @code{MyISAM} -table type. +mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); ++----+-------------------+---------------------------------------------+ +| id | title | body | ++----+-------------------+---------------------------------------------+ +| 5 | MySQL vs. YourSQL | In the following database comparison we ... | +| 1 | MySQL Tutorial | DBMS stands for DataBase Management ... | ++----+-------------------+---------------------------------------------+ +2 rows in set (0.00 sec) +@end example -For more information about how @strong{MySQL} uses indexes, see -@ref{MySQL indexes, , @strong{MySQL} indexes}. +The function @code{MATCH} matches a natural language query @code{AGAINST} +a text collection (which is simply the set of columns covered by a +@code{FULLTEXT} index). For every row in a table it returns relevance - +a similarity measure between the text in that row (in the columns that are +part of the collection) and the query. When it is used in a @code{WHERE} +clause (see example above) the rows returned are automatically sorted with +relevance decreasing. Relevance is a non-negative floating-point number. +Zero relevance means no similarity. Relevance is computed based on the +number of words in the row, the number of unique words in that row, the +total number of words in the collection, and the number of documents (rows) +that contain a particular word. -@code{FULLTEXT} indexes can index only @code{VARCHAR} and -@code{TEXT} columns, and only in @code{MyISAM} tables. @code{FULLTEXT} indexes -are available in @strong{MySQL} Version 3.23.23 and later. -@ref{Fulltext Search}. +The above is a basic example of using @code{MATCH} function. Rows are +returned with relevance decreasing. -@findex DROP INDEX -@node DROP INDEX, Comments, CREATE INDEX, Reference -@section @code{DROP INDEX} Syntax +@example +mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles; ++----+-----------------------------------------+ +| id | MATCH (title,body) AGAINST ('Tutorial') | ++----+-----------------------------------------+ +| 1 | 0.64840710366884 | +| 2 | 0 | +| 3 | 0.66266459031789 | +| 4 | 0 | +| 5 | 0 | +| 6 | 0 | ++----+-----------------------------------------+ +5 rows in set (0.00 sec) +@end example + +This example shows how to retrieve the relevances. As neither @code{WHERE} +nor @code{ORDER BY} clauses are present, returned rows are not ordered. @example -DROP INDEX index_name ON tbl_name +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. Normalize ... | 1.5055546709332 | +| 6 | When configured properly, MySQL could be ... | 1.31140957288 | ++----+-----------------------------------------------+-----------------+ +2 rows in set (0.00 sec) @end example -@code{DROP INDEX} drops the index named @code{index_name} from the table -@code{tbl_name}. @code{DROP INDEX} doesn't do anything in @strong{MySQL} -prior to Version 3.22. In Version 3.22 or later, @code{DROP INDEX} is mapped to an -@code{ALTER TABLE} statement to drop the index. -@xref{ALTER TABLE, , @code{ALTER TABLE}}. +This is more complex example - the query returns the relevance and still +sorts the rows with relevance decreasing. To achieve it one should specify +@code{MATCH} twice. Note, that this will cause no additional overhead, as +@strong{MySQL} optimizer will notice that these two @code{MATCH} calls are +identical and will call full-text search code only once. -@findex Comment syntax -@cindex comments, adding -@node Comments, CREATE FUNCTION, DROP INDEX, Reference -@section Comment Syntax +@strong{MySQL} uses a very simple parser to split text into words. A +``word'' is any sequence of letters, numbers, @samp{'}, and @samp{_}. Any +``word'' that is present in the stopword list or just too short (3 +characters or less) is ignored. -The @strong{MySQL} server supports the @code{# to end of line}, @code{-- -to end of line} and @code{/* in-line or multiple-line */} comment -styles: +Every correct word in the collection and in the query is weighted, +according to its significance in the query or collection. This way, a +word that is present in many documents will have lower weight (and may +even have a zero weight), because it has lower semantic value in this +particular collection. Otherwise, if the word is rare, it will receive a +higher weight. The weights of the words are then combined to compute the +relevance of the row. + +Such a technique works best with large collections (in fact, it was +carefully tuned this way). For very small tables, word distribution +does not reflect adequately their semantical value, and this model +may sometimes produce bizarre results. @example -mysql> select 1+1; # This comment continues to the end of line -mysql> select 1+1; -- This comment continues to the end of line -mysql> select 1 /* this is an in-line comment */ + 1; -mysql> select 1+ -/* -this is a -multiple-line comment -*/ -1; +mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL'); +Empty set (0.00 sec) @end example -Note that the @code{--} comment style requires you to have at least one space -after the @code{--}! +Search for the word @code{MySQL} produces no results in the above example. +Word @code{MySQL} is present in more than half of rows, and as such, is +effectively treated as a stopword (that is, with semantical value zero). +It is, really, the desired behavior - a natural language query should not +return every second row in 1GB table. -Although the server understands the comment syntax just described, -there are some limitations on the way that the @code{mysql} client -parses @code{/* ... */} comments: +A word that matches half of rows in a table is less likely to locate relevant +documents. In fact, it will most likely find plenty of irrelevant documents. +We all know this happens far too often when we are trying to find something on +the Internet with a search engine. It is with this reasoning that such rows +have been assigned a low semantical value in @strong{this particular dataset}. + +@menu +* Fulltext restrictions:: +* Fulltext Fine-tuning:: +* Fulltext Features to Appear in MySQL 4.0:: +* Fulltext TODO:: +@end menu + + +@node Fulltext restrictions, Fulltext Fine-tuning, Fulltext Search, Fulltext Search +@subsection Fulltext restrictions @itemize @bullet @item -Single-quote and double-quote characters are taken to indicate the beginning -of a quoted string, even within a comment. If the quote is not matched by a -second quote within the comment, the parser doesn't realize the comment has -ended. If you are running @code{mysql} interactively, you can tell that it -has gotten confused like this because the prompt changes from @code{mysql>} -to @code{'>} or @code{">}. - +All parameters to the @code{MATCH} function must be columns from the +same table that is part of the same fulltext index. @item -A semicolon is taken to indicate the end of the current SQL statement -and anything following it to indicate the beginning of the next statement. +The argument to @code{AGAINST} must be a constant string. @end itemize -These limitations apply both when you run @code{mysql} interactively -and when you put commands in a file and tell @code{mysql} to read its -input from that file with @code{mysql < some-file}. -@strong{MySQL} doesn't support the @samp{--} ANSI SQL comment style. -@xref{Missing comments}. +@node Fulltext Fine-tuning, Fulltext Features to Appear in MySQL 4.0, Fulltext restrictions, Fulltext Search +@subsection Fine-tuning MySQL Full-text Search -@findex CREATE FUNCTION -@findex DROP FUNCTION -@findex UDF functions -@findex User-defined functions -@findex Functions, user-defined -@node CREATE FUNCTION, Reserved words, Comments, Reference -@section @code{CREATE FUNCTION/DROP FUNCTION} Syntax +Unfortunately, full-text search has no user-tunable parameters yet, +although adding some is very high on the TODO. However, if you have a +@strong{MySQL} source distribution (@xref{Installing source}.), you can +somewhat alter the full-text search behavior. + +Note that full-text search was carefully tuned for the best searching +effectiveness. Modifying the default behavior will, in most cases, +only make the search results worse. Do not alter the @strong{MySQL} sources +unless you know what you are doing! +@itemize + +@item +Minimal length of word to be indexed is defined in +@code{myisam/ftdefs.h} file by the line @example -CREATE [AGGREGATE] FUNCTION function_name RETURNS @{STRING|REAL|INTEGER@} - SONAME shared_library_name +#define MIN_WORD_LEN 4 +@end example +Change it to the value you prefer, recompile @strong{MySQL}, and rebuild +your @code{FULLTEXT} indexes. -DROP FUNCTION function_name +@item +The stopword list is defined in @code{myisam/ft_static.c} +Modify it to your taste, recompile @strong{MySQL} and rebuild +your @code{FULLTEXT} indexes. + +@item +The 50% threshold is caused by the particular weighting scheme chosen. To +disable it, change the following line in @code{myisam/ftdefs.h}: +@example +#define GWS_IN_USE GWS_PROB +@end example +to +@example +#define GWS_IN_USE GWS_FREQ @end example +and recompile @strong{MySQL}. +There is no need to rebuild the indexes in this case. -A user-definable function (UDF) is a way to extend @strong{MySQL} with a new -function that works like native (built in) @strong{MySQL} functions such as -@code{ABS()} and @code{CONCAT()}. +@end itemize -@code{AGGREGATE} is a new option for @strong{MySQL} Version 3.23. An -@code{AGGREGATE} function works exactly like a native @strong{MySQL} -@code{GROUP} function like @code{SUM} or @code{COUNT()}. -@code{CREATE FUNCTION} saves the function's name, type, and shared library -name in the @code{mysql.func} system table. You must have the -@strong{insert} and @strong{delete} privileges for the @code{mysql} database -to create and drop functions. +@node Fulltext Features to Appear in MySQL 4.0, Fulltext TODO, Fulltext Fine-tuning, Fulltext Search +@subsection New Features of Full-text Search to Appear in MySQL 4.0 -All active functions are reloaded each time the server starts, unless -you start @code{mysqld} with the @code{--skip-grant-tables} option. In -this case, UDF initialization is skipped and UDFs are unavailable. -(An active function is one that has been loaded with @code{CREATE FUNCTION} -and not removed with @code{DROP FUNCTION}.) +This section includes a list of the fulltext features that are already +implemented in the 4.0 tree. It explains +@strong{More functions for full-text search} entry of @ref{TODO MySQL 4.0}. -For instructions on writing user-definable functions, see @ref{Adding -functions}. For the UDF mechanism to work, functions must be written in C or -C++, your operating system must support dynamic loading and you must have -compiled @code{mysqld} dynamically (not statically). +@itemize @bullet +@item @code{REPAIR TABLE} with @code{FULLTEXT} indexes, +@code{ALTER TABLE} with @code{FULLTEXT} indexes, and +@code{OPTIMIZE TABLE} with @code{FULLTEXT} indexes are now +up to 100 times faster. -@cindex keywords -@cindex reserved words, exceptions -@node Reserved words, , CREATE FUNCTION, Reference -@section Is MySQL Picky About Reserved Words? +@item @code{MATCH ... AGAINST} is going to supports the following +@strong{boolean operators}: -A common problem stems from trying to create a table with column names that -use the names of datatypes or functions built into @strong{MySQL}, such as -@code{TIMESTAMP} or @code{GROUP}. You're allowed to do it (for example, -@code{ABS} is an allowed column name), but whitespace is not allowed between -a function name and the @samp{(} when using functions whose names are also -column names. +@itemize @bullet +@item @code{+}word means the that word @strong{must} be present in every +row returned. +@item @code{-}word means the that word @strong{must not} be present in every +row returned. +@item @code{<} and @code{>} can be used to decrease and increase word +weight in the query. +@item @code{~} can be used to assign a @strong{negative} weight to a noise +word. +@item @code{*} is a truncation operator. +@end itemize -The following words are explicitly reserved in @strong{MySQL}. Most of -them are forbidden by ANSI SQL92 as column and/or table names -(for example, @code{group}). -A few are reserved because @strong{MySQL} needs them and is -(currently) using a @code{yacc} parser: +Boolean search utilizes a more simplistic way of calculating the relevance, +that does not have a 50% threshold. -@c This is fixed by including the symbols table from lex.h here and then running -@c fix-mysql-reserved-words in emacs (or let David do it): -@c (defun fix-mysql-reserved-words () -@c (interactive) -@c (let ((cnt 0)) -@c (insert "\n@item ") -@c (while (looking-at "[ \t]*{ +\"\\([^\"]+\\)\"[ \t]*,.*\n") -@c (replace-match "@code{\\1}") -@c (incf cnt) -@c (if (> cnt 3) -@c (progn -@c (setf cnt 0) -@c (insert "\n@item ")) -@c (insert " @tab "))))) -@c But remove the non alphanumeric entries by hand first. -@c Updated after 3.23.4 990928 by David +@item Searches are now up to 2 times faster due to optimized search algorithm. -@multitable @columnfractions .25 .25 .25 .25 -@item @code{action} @tab @code{add} @tab @code{aggregate} @tab @code{all} -@item @code{alter} @tab @code{after} @tab @code{and} @tab @code{as} -@item @code{asc} @tab @code{avg} @tab @code{avg_row_length} @tab @code{auto_increment} -@item @code{between} @tab @code{bigint} @tab @code{bit} @tab @code{binary} -@item @code{blob} @tab @code{bool} @tab @code{both} @tab @code{by} -@item @code{cascade} @tab @code{case} @tab @code{char} @tab @code{character} -@item @code{change} @tab @code{check} @tab @code{checksum} @tab @code{column} -@item @code{columns} @tab @code{comment} @tab @code{constraint} @tab @code{create} -@item @code{cross} @tab @code{current_date} @tab @code{current_time} @tab @code{current_timestamp} -@item @code{data} @tab @code{database} @tab @code{databases} @tab @code{date} -@item @code{datetime} @tab @code{day} @tab @code{day_hour} @tab @code{day_minute} -@item @code{day_second} @tab @code{dayofmonth} @tab @code{dayofweek} @tab @code{dayofyear} -@item @code{dec} @tab @code{decimal} @tab @code{default} @tab @code{delayed} -@item @code{delay_key_write} @tab @code{delete} @tab @code{desc} @tab @code{describe} -@item @code{distinct} @tab @code{distinctrow} @tab @code{double} @tab @code{drop} -@item @code{end} @tab @code{else} @tab @code{escape} @tab @code{escaped} -@item @code{enclosed} @tab @code{enum} @tab @code{explain} @tab @code{exists} -@item @code{fields} @tab @code{file} @tab @code{first} @tab @code{float} -@item @code{float4} @tab @code{float8} @tab @code{flush} @tab @code{foreign} -@item @code{from} @tab @code{for} @tab @code{full} @tab @code{function} -@item @code{global} @tab @code{grant} @tab @code{grants} @tab @code{group} -@item @code{having} @tab @code{heap} @tab @code{high_priority} @tab @code{hour} -@item @code{hour_minute} @tab @code{hour_second} @tab @code{hosts} @tab @code{identified} -@item @code{ignore} @tab @code{in} @tab @code{index} @tab @code{infile} -@item @code{inner} @tab @code{insert} @tab @code{insert_id} @tab @code{int} -@item @code{integer} @tab @code{interval} @tab @code{int1} @tab @code{int2} -@item @code{int3} @tab @code{int4} @tab @code{int8} @tab @code{into} -@item @code{if} @tab @code{is} @tab @code{isam} @tab @code{join} -@item @code{key} @tab @code{keys} @tab @code{kill} @tab @code{last_insert_id} -@item @code{leading} @tab @code{left} @tab @code{length} @tab @code{like} -@item @code{lines} @tab @code{limit} @tab @code{load} @tab @code{local} -@item @code{lock} @tab @code{logs} @tab @code{long} @tab @code{longblob} -@item @code{longtext} @tab @code{low_priority} @tab @code{max} @tab @code{max_rows} -@item @code{match} @tab @code{mediumblob} @tab @code{mediumtext} @tab @code{mediumint} -@item @code{middleint} @tab @code{min_rows} @tab @code{minute} @tab @code{minute_second} -@item @code{modify} @tab @code{month} @tab @code{monthname} @tab @code{myisam} -@item @code{natural} @tab @code{numeric} @tab @code{no} @tab @code{not} -@item @code{null} @tab @code{on} @tab @code{optimize} @tab @code{option} -@item @code{optionally} @tab @code{or} @tab @code{order} @tab @code{outer} -@item @code{outfile} @tab @code{pack_keys} @tab @code{partial} @tab @code{password} -@item @code{precision} @tab @code{primary} @tab @code{procedure} @tab @code{process} -@item @code{processlist} @tab @code{privileges} @tab @code{read} @tab @code{real} -@item @code{references} @tab @code{reload} @tab @code{regexp} @tab @code{rename} -@item @code{replace} @tab @code{restrict} @tab @code{returns} @tab @code{revoke} -@item @code{rlike} @tab @code{row} @tab @code{rows} @tab @code{second} -@item @code{select} @tab @code{set} @tab @code{show} @tab @code{shutdown} -@item @code{smallint} @tab @code{soname} @tab @code{sql_big_tables} @tab @code{sql_big_selects} -@item @code{sql_low_priority_updates} @tab @code{sql_log_off} @tab @code{sql_log_update} @tab @code{sql_select_limit} -@item @code{sql_small_result} @tab @code{sql_big_result} @tab @code{sql_warnings} @tab @code{straight_join} -@item @code{starting} @tab @code{status} @tab @code{string} @tab @code{table} -@item @code{tables} @tab @code{temporary} @tab @code{terminated} @tab @code{text} -@item @code{then} @tab @code{time} @tab @code{timestamp} @tab @code{tinyblob} -@item @code{tinytext} @tab @code{tinyint} @tab @code{trailing} @tab @code{to} -@item @code{type} @tab @code{use} @tab @code{using} @tab @code{unique} -@item @code{unlock} @tab @code{unsigned} @tab @code{update} @tab @code{usage} -@item @code{values} @tab @code{varchar} @tab @code{variables} @tab @code{varying} -@item @code{varbinary} @tab @code{with} @tab @code{write} @tab @code{when} -@item @code{where} @tab @code{year} @tab @code{year_month} @tab @code{zerofill} -@end multitable +@item Utility program @code{ft_dump} added for low-level @code{FULLTEXT} +index operations (querying/dumping/statistics). + +@end itemize -The following symbols (from the table above) are disallowed by ANSI SQL -but allowed by @strong{MySQL} as column/table names. This is because some -of these names are very natural names and a lot of people have already -used them. + +@node Fulltext TODO, , Fulltext Features to Appear in MySQL 4.0, Fulltext Search +@subsection Full-text Search TODO @itemize @bullet -@item @code{ACTION} -@item @code{BIT} -@item @code{DATE} -@item @code{ENUM} -@item @code{NO} -@item @code{TEXT} -@item @code{TIME} -@item @code{TIMESTAMP} +@item Make all operations with @code{FULLTEXT} index @strong{faster}. +@item Support for braces @code{()} in boolean full-text search. +@item Phrase search, proximity operators +@item Boolean search can work without @code{FULLTEXT} index +(yes, @strong{very} slow). +@item Support for "always-index words". They could be any strings +the user wants to treat as words, examples are "C++", "AS/400", "TCP/IP", etc. +@item Support for full-text search in @code{MERGE} tables. +@item Support for multi-byte charsets. +@item Make stopword list to depend of the language of the data. +@item Stemming (dependent of the language of the data, of course). +@item Generic user-supplyable UDF (?) preparser. +@item Make the model more flexible (by adding some adjustable +parameters to @code{FULLTEXT} in @code{CREATE/ALTER TABLE}). @end itemize + +@node Table types, Maintenance, Reference, Top +@chapter MySQL Table Types + @cindex table types, choosing @cindex @code{BDB} table type @cindex @code{Berkeley_db} table type @@ -34361,8 +34626,6 @@ used them. @cindex MySQL table types @cindex @code{MyISAM} table type @cindex types, of tables -@node Table types, Fulltext Search, Reference, Top -@chapter MySQL Table Types As of @strong{MySQL} Version 3.23.6, you can choose between three basic table formats (@code{ISAM}, @code{HEAP} and @code{MyISAM}. Newer @@ -36776,262 +37039,9 @@ not trivial). @end itemize -@node Fulltext Search, Maintenance, Table types, Top -@chapter MySQL Full-text Search - -@cindex searching, full-text -@cindex full-text search -@cindex FULLTEXT - -Since Version 3.23.23, @strong{MySQL} has support for full-text indexing -and searching. Full-text indexes in @strong{MySQL} are an index of type -@code{FULLTEXT}. @code{FULLTEXT} indexes can be created from @code{VARCHAR} -and @code{TEXT} columns at @code{CREATE TABLE} time or added later with -@code{ALTER TABLE} or @code{CREATE INDEX}. For large datasets, adding -@code{FULLTEXT} index with @code{ALTER TABLE} (or @code{CREATE INDEX}) would -be much faster than inserting rows into the empty table with a @code{FULLTEXT} -index. - -Full-text search is performed with the @code{MATCH} function. - -@example -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 VALUES - -> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'), - -> (0,'How To Use MySQL Efficiently', 'After you went through a ...'), - -> (0,'Optimizing MySQL','In this tutorial we will show how to ...'), - -> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalize ...'), - -> (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'), - -> (0,'MySQL Security', 'When configured properly, MySQL could be ...'); -Query OK, 5 rows affected (0.00 sec) -Records: 5 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 we ... | -| 1 | MySQL Tutorial | DBMS stands for DataBase Management ... | -+----+-------------------+---------------------------------------------+ -2 rows in set (0.00 sec) -@end example - -The function @code{MATCH} matches a natural language query @code{AGAINST} -a text collection (which is simply the set of columns covered by a -@code{FULLTEXT} index). For every row in a table it returns relevance - -a similarity measure between the text in that row (in the columns that are -part of the collection) and the query. When it is used in a @code{WHERE} -clause (see example above) the rows returned are automatically sorted with -relevance decreasing. Relevance is a non-negative floating-point number. -Zero relevance means no similarity. Relevance is computed based on the -number of words in the row, the number of unique words in that row, the -total number of words in the collection, and the number of documents (rows) -that contain a particular word. - -The above is a basic example of using @code{MATCH} function. Rows are -returned with relevance decreasing. - -@example -mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles; -+----+-----------------------------------------+ -| id | MATCH (title,body) AGAINST ('Tutorial') | -+----+-----------------------------------------+ -| 1 | 0.64840710366884 | -| 2 | 0 | -| 3 | 0.66266459031789 | -| 4 | 0 | -| 5 | 0 | -| 6 | 0 | -+----+-----------------------------------------+ -5 rows in set (0.00 sec) -@end example - -This example shows how to retrieve the relevances. As neither @code{WHERE} -nor @code{ORDER BY} clauses are present, returned rows are not ordered. - -@example -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. Normalize ... | 1.5055546709332 | -| 6 | When configured properly, MySQL could be ... | 1.31140957288 | -+----+-----------------------------------------------+-----------------+ -2 rows in set (0.00 sec) -@end example - -This is more complex example - the query returns the relevance and still -sorts the rows with relevance decreasing. To achieve it one should specify -@code{MATCH} twice. Note, that this will cause no additional overhead, as -@strong{MySQL} optimizer will notice that these two @code{MATCH} calls are -identical and will call full-text search code only once. - -@strong{MySQL} uses a very simple parser to split text into words. A -``word'' is any sequence of letters, numbers, @samp{'}, and @samp{_}. Any -``word'' that is present in the stopword list or just too short (3 -characters or less) is ignored. - -Every correct word in the collection and in the query is weighted, -according to its significance in the query or collection. This way, a -word that is present in many documents will have lower weight (and may -even have a zero weight), because it has lower semantic value in this -particular collection. Otherwise, if the word is rare, it will receive a -higher weight. The weights of the words are then combined to compute the -relevance of the row. - -Such a technique works best with large collections (in fact, it was -carefully tuned this way). For very small tables, word distribution -does not reflect adequately their semantical value, and this model -may sometimes produce bizarre results. - -@example -mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL'); -Empty set (0.00 sec) -@end example - -Search for the word @code{MySQL} produces no results in the above example. -Word @code{MySQL} is present in more than half of rows, and as such, is -effectively treated as a stopword (that is, with semantical value zero). -It is, really, the desired behavior - a natural language query should not -return every second row in 1GB table. - -A word that matches half of rows in a table is less likely to locate relevant -documents. In fact, it will most likely find plenty of irrelevant documents. -We all know this happens far too often when we are trying to find something on -the Internet with a search engine. It is with this reasoning that such rows -have been assigned a low semantical value in @strong{this particular dataset}. - -@menu -* Fulltext restrictions:: -* Fulltext Fine-tuning:: -* Fulltext Features to Appear in MySQL 4.0:: -* Fulltext TODO:: -@end menu - -@node Fulltext restrictions, Fulltext Fine-tuning, Fulltext Search, Fulltext Search -@section Fulltext restrictions -@itemize @bullet -@item -All parameters to the @code{MATCH} function must be columns from the -same table that is part of the same fulltext index. -@item -The argument to @code{AGAINST} must be a constant string. -@end itemize - -@node Fulltext Fine-tuning, Fulltext Features to Appear in MySQL 4.0, Fulltext restrictions, Fulltext Search -@section Fine-tuning MySQL Full-text Search - -Unfortunately, full-text search has no user-tunable parameters yet, -although adding some is very high on the TODO. However, if you have a -@strong{MySQL} source distribution (@xref{Installing source}.), you can -somewhat alter the full-text search behavior. - -Note that full-text search was carefully tuned for the best searching -effectiveness. Modifying the default behavior will, in most cases, -only make the search results worse. Do not alter the @strong{MySQL} sources -unless you know what you are doing! - -@itemize - -@item -Minimal length of word to be indexed is defined in -@code{myisam/ftdefs.h} file by the line -@example -#define MIN_WORD_LEN 4 -@end example -Change it to the value you prefer, recompile @strong{MySQL}, and rebuild -your @code{FULLTEXT} indexes. - -@item -The stopword list is defined in @code{myisam/ft_static.c} -Modify it to your taste, recompile @strong{MySQL} and rebuild -your @code{FULLTEXT} indexes. - -@item -The 50% threshold is caused by the particular weighting scheme chosen. To -disable it, change the following line in @code{myisam/ftdefs.h}: -@example -#define GWS_IN_USE GWS_PROB -@end example -to -@example -#define GWS_IN_USE GWS_FREQ -@end example -and recompile @strong{MySQL}. -There is no need to rebuild the indexes in this case. - -@end itemize - -@node Fulltext Features to Appear in MySQL 4.0, Fulltext TODO, Fulltext Fine-tuning, Fulltext Search -@section New Features of Full-text Search to Appear in MySQL 4.0 - -This section includes a list of the fulltext features that are already -implemented in the 4.0 tree. It explains -@strong{More functions for full-text search} entry of @ref{TODO MySQL 4.0}. - -@itemize @bullet -@item @code{REPAIR TABLE} with @code{FULLTEXT} indexes, -@code{ALTER TABLE} with @code{FULLTEXT} indexes, and -@code{OPTIMIZE TABLE} with @code{FULLTEXT} indexes are now -up to 100 times faster. -@item @code{MATCH ... AGAINST} is going to supports the following -@strong{boolean operators}: -@itemize @bullet -@item @code{+}word means the that word @strong{must} be present in every -row returned. -@item @code{-}word means the that word @strong{must not} be present in every -row returned. -@item @code{<} and @code{>} can be used to decrease and increase word -weight in the query. -@item @code{~} can be used to assign a @strong{negative} weight to a noise -word. -@item @code{*} is a truncation operator. -@end itemize - -Boolean search utilizes a more simplistic way of calculating the relevance, -that does not have a 50% threshold. - -@item Searches are now up to 2 times faster due to optimized search algorithm. - -@item Utility program @code{ft_dump} added for low-level @code{FULLTEXT} -index operations (querying/dumping/statistics). - -@end itemize - -@node Fulltext TODO, , Fulltext Features to Appear in MySQL 4.0, Fulltext Search -@section Full-text Search TODO - -@itemize @bullet -@item Make all operations with @code{FULLTEXT} index @strong{faster}. -@item Support for braces @code{()} in boolean full-text search. -@item Phrase search, proximity operators -@item Boolean search can work without @code{FULLTEXT} index -(yes, @strong{very} slow). -@item Support for "always-index words". They could be any strings -the user wants to treat as words, examples are "C++", "AS/400", "TCP/IP", etc. -@item Support for full-text search in @code{MERGE} tables. -@item Support for multi-byte charsets. -@item Make stopword list to depend of the language of the data. -@item Stemming (dependent of the language of the data, of course). -@item Generic user-supplyable UDF (?) preparser. -@item Make the model more flexible (by adding some adjustable -parameters to @code{FULLTEXT} in @code{CREATE/ALTER TABLE}). -@end itemize - - -@node Maintenance, Adding functions, Fulltext Search, Top +@node Maintenance, Adding functions, Table types, Top @chapter Maintaining a MySQL Installation @cindex installation maintenance @@ -39798,12 +39808,13 @@ your current time zone. This should be done for the environment in which the server runs, for example, in @code{safe_mysqld} or @code{mysql.server}. @xref{Environment variables}. +@node Case sensitivity, Problems with NULL, Timezone problems, Problems +@section Case Sensitivity in Searches + @cindex case sensitivity, in searches @cindex searching, and case-sensitivity @cindex Chinese @cindex Big5 Chinese character encoding -@node Case sensitivity, Problems with NULL, Timezone problems, Problems -@section Case Sensitivity in Searches By default, @strong{MySQL} searches are case-insensitive (although there are some character sets that are never case insensitive, such as @code{czech}). @@ -53032,6 +53043,8 @@ That's all there is to it! * Installing binary:: * Building clients:: * Perl support:: +* Group by functions:: +* CREATE FUNCTION:: @end menu @node Installing binary, Building clients, Placeholder, Placeholder @@ -53275,7 +53288,7 @@ files. -@node Perl support, , Building clients, Placeholder +@node Perl support, Group by functions, Building clients, Placeholder @appendixsec Perl Installation Comments @cindex Perl, installing @@ -53564,6 +53577,205 @@ Finally, you should install this new Perl. Again, the output of @code{make perl} indicates the command to use. +@node Group by functions, CREATE FUNCTION, Perl support, Placeholder +@appendixsec Functions for Use with @code{GROUP BY} Clauses + +@findex GROUP BY functions +@findex functions, GROUP BY + +If you use a group function in a statement containing no @code{GROUP BY} +clause, it is equivalent to grouping on all rows. + +@table @code +@findex COUNT() +@item COUNT(expr) +Returns a count of the number of non-@code{NULL} values in the rows +retrieved by a @code{SELECT} statement: + +@example +mysql> select student.student_name,COUNT(*) + from student,course + where student.student_id=course.student_id + GROUP BY student_name; + +@end example + +@code{COUNT(*)} is somewhat different in that it returns a count of +the number of rows retrieved, whether or not they contain @code{NULL} +values. + +@code{COUNT(*)} is optimized to +return very quickly if the @code{SELECT} retrieves from one table, no +other columns are retrieved, and there is no @code{WHERE} clause. +For example: + +@example +mysql> select COUNT(*) from student; +@end example + +@findex COUNT(DISTINCT) +@findex DISTINCT +@item COUNT(DISTINCT expr,[expr...]) +Returns a count of the number of different non-@code{NULL} values: + +@example +mysql> select COUNT(DISTINCT results) from student; +@end example + +In @strong{MySQL} you can get the number of distinct expression +combinations that don't contain NULL by giving a list of expressions. +In ANSI SQL you would have to do a concatenation of all expressions +inside @code{CODE(DISTINCT ..)}. + +@findex AVG() +@item AVG(expr) +Returns the average value of @code{expr}: + +@example +mysql> select student_name, AVG(test_score) + from student + GROUP BY student_name; +@end example + +@findex MIN() +@findex MAX() +@item MIN(expr) +@itemx MAX(expr) +Returns the minimum or maximum value of @code{expr}. @code{MIN()} and +@code{MAX()} may take a string argument; in such cases they return the +minimum or maximum string value. @xref{MySQL indexes}. + +@example +mysql> select student_name, MIN(test_score), MAX(test_score) + from student + GROUP BY student_name; +@end example + +@findex SUM() +@item SUM(expr) +Returns the sum of @code{expr}. Note that if the return set has no rows, +it returns NULL! + +@findex STD() +@findex STDDEV() +@cindex Oracle compatibility +@cindex compatibility, with Oracle +@item STD(expr) +@itemx STDDEV(expr) +Returns the standard deviation of @code{expr}. This is an extension to +ANSI SQL. The @code{STDDEV()} form of this function is provided for Oracle +compatibility. + +@findex BIT_OR() +@item BIT_OR(expr) +Returns the bitwise @code{OR} of all bits in @code{expr}. The calculation is +performed with 64-bit (@code{BIGINT}) precision. + +@findex BIT_AND() +@item BIT_AND(expr) +Returns the bitwise @code{AND} of all bits in @code{expr}. The calculation is +performed with 64-bit (@code{BIGINT}) precision. +@end table + +@cindex @code{GROUP BY}, extensions to ANSI SQL +@strong{MySQL} has extended the use of @code{GROUP BY}. You can use columns or +calculations in the @code{SELECT} expressions that don't appear in +the @code{GROUP BY} part. This stands for @emph{any possible value for this +group}. You can use this to get better performance by avoiding sorting and +grouping on unnecessary items. For example, you don't need to group on +@code{customer.name} in the following query: + +@example +mysql> select order.custid,customer.name,max(payments) + from order,customer + where order.custid = customer.custid + GROUP BY order.custid; +@end example + +In ANSI SQL, you would have to add @code{customer.name} to the @code{GROUP +BY} clause. In @strong{MySQL}, the name is redundant if you don't run in +ANSI mode. + +@strong{Don't use this feature} if the columns you omit from the +@code{GROUP BY} part aren't unique in the group! You will get +unpredictable results. + +In some cases, you can use @code{MIN()} and @code{MAX()} to obtain a specific +column value even if it isn't unique. The following gives the value of +@code{column} from the row containing the smallest value in the @code{sort} +column: + +@example +substr(MIN(concat(rpad(sort,6,' '),column)),7) +@end example + +@xref{example-Maximum-column-group-row}. + +@cindex @code{ORDER BY}, aliases in +@cindex aliases, in @code{ORDER BY} clauses +@cindex @code{GROUP BY}, aliases in +@cindex aliases, in @code{GROUP BY} clauses +@cindex expression aliases +@cindex aliases, for expressions +Note that if you are using @strong{MySQL} Version 3.22 (or earlier) or if +you are trying to follow ANSI SQL, you can't use expressions in @code{GROUP +BY} or @code{ORDER BY} clauses. You can work around this limitation by +using an alias for the expression: + +@example +mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name + GROUP BY id,val ORDER BY val; +@end example + +In @strong{MySQL} Version 3.23 you can do: + +@example +mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND(); +@end example + + +@node CREATE FUNCTION, , Group by functions, Placeholder +@appendixsec @code{CREATE FUNCTION/DROP FUNCTION} Syntax + +@findex CREATE FUNCTION +@findex DROP FUNCTION +@findex UDF functions +@findex User-defined functions +@findex Functions, user-defined + +@example +CREATE [AGGREGATE] FUNCTION function_name RETURNS @{STRING|REAL|INTEGER@} + SONAME shared_library_name + +DROP FUNCTION function_name +@end example + +A user-definable function (UDF) is a way to extend @strong{MySQL} with a new +function that works like native (built in) @strong{MySQL} functions such as +@code{ABS()} and @code{CONCAT()}. + +@code{AGGREGATE} is a new option for @strong{MySQL} Version 3.23. An +@code{AGGREGATE} function works exactly like a native @strong{MySQL} +@code{GROUP} function like @code{SUM} or @code{COUNT()}. + +@code{CREATE FUNCTION} saves the function's name, type, and shared library +name in the @code{mysql.func} system table. You must have the +@strong{insert} and @strong{delete} privileges for the @code{mysql} database +to create and drop functions. + +All active functions are reloaded each time the server starts, unless +you start @code{mysqld} with the @code{--skip-grant-tables} option. In +this case, UDF initialization is skipped and UDFs are unavailable. +(An active function is one that has been loaded with @code{CREATE FUNCTION} +and not removed with @code{DROP FUNCTION}.) + +For instructions on writing user-definable functions, see @ref{Adding +functions}. For the UDF mechanism to work, functions must be written in C or +C++, your operating system must support dynamic loading and you must have +compiled @code{mysqld} dynamically (not statically). + + + @node Function Index, Concept Index, Placeholder, Top @unnumbered SQL command, type and function index diff --git a/innobase/btr/btr0cur.c b/innobase/btr/btr0cur.c index e8ff88c6f4f..47a67d425cd 100644 --- a/innobase/btr/btr0cur.c +++ b/innobase/btr/btr0cur.c @@ -2351,6 +2351,7 @@ btr_estimate_n_rows_in_range( btr_path_t* slot1; btr_path_t* slot2; ibool diverged; + ulint divergence_level; ulint n_rows; ulint i; mtr_t mtr; @@ -2393,6 +2394,7 @@ btr_estimate_n_rows_in_range( n_rows = 1; diverged = FALSE; + divergence_level = 1000000; for (i = 0; ; i++) { ut_ad(i < BTR_PATH_ARRAY_N_SLOTS); @@ -2403,6 +2405,13 @@ btr_estimate_n_rows_in_range( if (slot1->nth_rec == ULINT_UNDEFINED || slot2->nth_rec == ULINT_UNDEFINED) { + if (i > divergence_level + 1) { + /* In trees whose height is > 1 our algorithm + tends to underestimate: multiply the estimate + by 2: */ + + n_rows = n_rows * 2; + } return(n_rows); } @@ -2417,6 +2426,8 @@ btr_estimate_n_rows_in_range( return(10); } + divergence_level = i; + diverged = TRUE; } else if (diverged) { n_rows = (n_rows * (slot1->n_recs + slot2->n_recs)) diff --git a/sql/ha_innobase.cc b/sql/ha_innobase.cc index 8ea700de789..7bd71363915 100644 --- a/sql/ha_innobase.cc +++ b/sql/ha_innobase.cc @@ -822,11 +822,11 @@ ha_innobase::open( if (NULL == (ib_table = dict_table_get(norm_name, NULL))) { - fprintf(stderr, "\ -Cannot find table %s from the internal data dictionary\n\ -of InnoDB though the .frm file for the table exists. Maybe you have deleted\n\ -and created again an InnoDB database but forgotten to delete the\n\ -corresponding .frm files of old InnoDB tables?\n", + fprintf(stderr, +"Cannot find table %s from the internal data dictionary\n" +"of InnoDB though the .frm file for the table exists. Maybe you have deleted\n" +"and created again an InnoDB database but forgotten to delete the\n" +"corresponding .frm files of old InnoDB tables?\n", norm_name); free_share(share); @@ -2660,6 +2660,37 @@ ha_innobase::records_in_range( } /************************************************************************* +Gives an UPPER BOUND to the number of rows in a table. This is used in +filesort.cc and the upper bound must hold. TODO: Since the number of +rows in a table may change after this function is called, we still may +get a 'Sort aborted' error in filesort.cc of MySQL. The ultimate fix is to +improve the algorithm of filesort.cc. */ + +ha_rows +ha_innobase::estimate_number_of_rows(void) +/*======================================*/ + /* out: upper bound of rows, currently 32-bit int + or uint */ +{ + row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt; + dict_table_t* ib_table; + + DBUG_ENTER("info"); + + ib_table = prebuilt->table; + + dict_update_statistics(ib_table); + + data_file_length = ((ulonglong) + ib_table->stat_clustered_index_size) + * UNIV_PAGE_SIZE; + + /* The minimum clustered index record size is 20 bytes */ + + return((ha_rows) (1000 + data_file_length / 20)); +} + +/************************************************************************* How many seeks it will take to read through the table. This is to be comparable to the number returned by records_in_range so that we can decide if we should scan the table or use keys. */ diff --git a/sql/ha_innobase.h b/sql/ha_innobase.h index 4dbff654337..d129e00ba6e 100644 --- a/sql/ha_innobase.h +++ b/sql/ha_innobase.h @@ -137,6 +137,7 @@ class ha_innobase: public handler enum ha_rkey_function start_search_flag, const byte *end_key,uint end_key_len, enum ha_rkey_function end_search_flag); + ha_rows estimate_number_of_rows(); int create(const char *name, register TABLE *form, HA_CREATE_INFO *create_info); |