Below is the list of changes that have just been committed into a local
mysqldoc repository of jon. When jon does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.2952 05/07/09 01:47:09 jon@stripped +6 -0
More edits for 5.0 version of Functions chapter.
Fixed typo in Storage Engines chapter.
Added entity for pi.
Sync refman trees as needed.
refman/storage-engines.xml
1.3 05/07/09 01:47:07 jon@stripped +1 -1
Fixed typo that Andrey noticed.
refman/fixedchars.ent
1.2 05/07/09 01:47:07 jon@stripped +1 -0
Added entity for pi.
refman-5.0/storage-engines.xml
1.5 05/07/09 01:47:07 jon@stripped +1 -1
Sync to refman.
refman-5.0/functions.xml
1.8 05/07/09 01:47:07 jon@stripped +2995 -3363
More fixes for 5.0-specific version.
(Operation Bifurcation continues...)
refman-5.0/fixedchars.ent
1.2 05/07/09 01:47:07 jon@stripped +1 -0
Sync to refman.
refman-4.1/fixedchars.ent
1.2 05/07/09 01:47:07 jon@stripped +1 -0
Sync to refman.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: jon
# Host: gigan.
# Root: /home/jon/bk/mysqldoc
--- 1.1/refman-4.1/fixedchars.ent 2005-06-17 05:35:22 +10:00
+++ 1.2/refman-4.1/fixedchars.ent 2005-07-09 01:47:07 +10:00
@@ -238,3 +238,4 @@
<!ENTITY drcrop "⌌">
<!ENTITY ulcrop "⌏">
<!ENTITY urcrop "⌎">
+ <!ENTITY pi "ϖ">
--- 1.1/refman-5.0/fixedchars.ent 2005-06-17 05:46:21 +10:00
+++ 1.2/refman-5.0/fixedchars.ent 2005-07-09 01:47:07 +10:00
@@ -238,3 +238,4 @@
<!ENTITY drcrop "⌌">
<!ENTITY ulcrop "⌏">
<!ENTITY urcrop "⌎">
+ <!ENTITY pi "ϖ">
\ No newline at end of file
--- 1.7/refman-5.0/functions.xml 2005-07-01 04:02:40 +10:00
+++ 1.8/refman-5.0/functions.xml 2005-07-09 01:47:07 +10:00
@@ -8,64 +8,65 @@
%title.entities;
]>
<chapter id='functions'>
+
+ <title id='title-functions'>&title-functions;</title>
+
+ <indexterm type="concept">
+ <primary>functions for <literal>SELECT</literal> and
+ <literal>WHERE</literal> clauses</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>functions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>operators</primary>
+ </indexterm>
- <title id='title-functions'>&title-functions;</title>
-
- <indexterm type="concept">
- <primary>functions for <literal>SELECT</literal> and
<literal>WHERE</literal>
- clauses</primary>
- </indexterm>
+<!-- help_category Functions -->
- <indexterm type="concept">
- <primary>functions</primary>
- </indexterm>
+ <para>
+ Expressions can be used at several points in SQL statements, such as
+ in the <literal>ORDER BY</literal> or
<literal>HAVING</literal>
+ clauses of <literal>SELECT</literal> statements, in the
+ <literal>WHERE</literal> clause of a
<literal>SELECT</literal>,
+ <literal>DELETE</literal>, or <literal>UPDATE</literal>
statement,
+ or in <literal>SET</literal> statements. Expressions can be written
+ using literal values, column values, <literal>NULL</literal>,
+ functions, and operators. This chapter describes the functions and
+ operators that are allowed for writing expressions in MySQL.
+ </para>
+
+ <para>
+ An expression that contains <literal>NULL</literal> always produces
+ a <literal>NULL</literal> value unless otherwise indicated in the
+ documentation for a particular function or operator.
+ </para>
- <indexterm type="concept">
- <primary>operators</primary>
- </indexterm>
+ <para>
+ <emphasis role="bold">Note</emphasis>: By default, there must be no
+ whitespace between a function name and the parenthesis following it.
+ This helps the MySQL parser distinguish between function calls and
+ references to tables or columns that happen to have the same name as
+ a function. However, spaces around function arguments are permitted.
+ </para>
-<!-- help_category Functions -->
+ <para>
+ You can tell the MySQL server to accept spaces after function names
+ by starting it with the <literal>--sql-mode=IGNORE_SPACE</literal>
+ option. Individual client programs can request this behavior by
+ using the <literal>CLIENT_IGNORE_SPACE</literal> option for
+ <literal>mysql_real_connect()</literal>. In either case, all
+ function names become reserved words. See
+ <xref linkend="server-sql-mode"/>.
+ </para>
- <para>
- Expressions can be used at several points in SQL statements, such as
- in the <literal>ORDER BY</literal> or <literal>HAVING</literal>
- clauses of <literal>SELECT</literal> statements, in the
- <literal>WHERE</literal> clause of a <literal>SELECT</literal>,
- <literal>DELETE</literal>, or <literal>UPDATE</literal>
statement, or
- in <literal>SET</literal> statements. Expressions can be written using
- literal values, column values, <literal>NULL</literal>, functions, and
- operators. This chapter describes the functions and operators that are
- allowed for writing expressions in MySQL.
- </para>
-
- <para>
- An expression that contains <literal>NULL</literal> always produces a
- <literal>NULL</literal> value unless otherwise indicated in the
- documentation for a particular function or operator.
- </para>
-
- <para>
- <emphasis role="bold">Note</emphasis>: By default, there must be no
- whitespace between a function name and the parenthesis following it.
- This helps the MySQL parser distinguish between function calls and
- references to tables or columns that happen to have the same name as a
- function. Spaces around function arguments are permitted, though.
- </para>
-
- <para>
- You can tell the MySQL server to accept spaces after function names by
- starting it with the <literal>--sql-mode=IGNORE_SPACE</literal>
- option. Individual client programs can request this behavior by using
- the <literal>CLIENT_IGNORE_SPACE</literal> option for
- <literal>mysql_real_connect()</literal>. In either case, all function
- names become reserved words. See <xref linkend="server-sql-mode"/>.
- </para>
-
- <para>
- For the sake of brevity, most examples in this chapter display the
- output from the <command>mysql</command> program in abbreviated form.
- Instead of showing examples in this format:
- </para>
+ <para>
+ For the sake of brevity, most examples in this chapter display the
+ output from the <command>mysql</command> program in abbreviated
+ form. Instead of showing examples in this format:
+ </para>
<programlisting>
mysql> SELECT MOD(29,9);
@@ -77,9 +78,9 @@
1 rows in set (0.00 sec)
</programlisting>
- <para>
- This format is used instead:
- </para>
+ <para>
+ This format is used instead:
+ </para>
<programlisting>
mysql> SELECT MOD(29,9);
@@ -88,19 +89,19 @@
<!-- TODO: rename node to Operators -->
- <section id="non-typed-operators">
+ <section id="non-typed-operators">
<title
id='title-non-typed-operators'>&title-non-typed-operators;</title>
- <section id="operator-precedence">
+ <section id="operator-precedence">
- <title
id='title-operator-precedence'>&title-operator-precedence;</title>
+ <title
id='title-operator-precedence'>&title-operator-precedence;</title>
- <para>
- Operator precedences are shown in the following list, from lowest
- precedence to the highest. Operators that are shown together on a
- line have the same precedence.
- </para>
+ <para>
+ Operator precedences are shown in the following list, from lowest
+ precedence to the highest. Operators that are shown together on a
+ line have the same precedence.
+ </para>
<programlisting>
:=
@@ -120,168 +121,180 @@
BINARY, COLLATE
</programlisting>
- <para>
- The precedence shown for <literal>NOT</literal> is as of MySQL
- 5.0.2. For earlier versions, or from 5.0.2 on if the
- <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode is enabled, the
- precedence of <literal>NOT</literal> is the same as that of the
- <literal>!</literal> operator. See
- <xref linkend="server-sql-mode"/>.
- </para>
-
- </section>
-
- <section id="parentheses">
+ <para>
+ The precedence shown for <literal>NOT</literal> is as of
+ MySQL 5.0.2. For earlier versions, or from 5.0.2 on if the
+ <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode is enabled,
+ the precedence of <literal>NOT</literal> is the same as that
+ of the <literal>!</literal> operator. See
+ <xref linkend="server-sql-mode"/>.
+ </para>
- <title id='title-parentheses'>&title-parentheses;</title>
+ </section>
- <indexterm type="function">
- <primary>() (parentheses)</primary>
- </indexterm>
+ <section id="parentheses">
- <indexterm type="function">
- <primary>parentheses ( and )</primary>
- </indexterm>
+ <title id='title-parentheses'>&title-parentheses;</title>
- <indexterm type="concept">
- <primary>functions</primary>
- <secondary>grouping</secondary>
- </indexterm>
+ <indexterm type="function">
+ <primary>() (parentheses)</primary>
+ </indexterm>
- <indexterm type="concept">
- <primary>grouping</primary>
- <secondary>expressions</secondary>
- </indexterm>
+ <indexterm type="function">
+ <primary>parentheses ( and )</primary>
+ </indexterm>
- <itemizedlist>
+ <indexterm type="concept">
+ <primary>functions</primary>
+ <secondary>grouping</secondary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>grouping</primary>
+ <secondary>expressions</secondary>
+ </indexterm>
- <listitem><para>
- <literal>( ... )</literal>
- </para>
+ <itemizedlist>
- <para>
- Use parentheses to force the order of evaluation in an expression.
- For example:
- </para>
+ <listitem>
+ <para>
+ <literal>( ... )</literal>
+ </para>
+
+ <para>
+ Use parentheses to force the order of evaluation in an
+ expression. For example:
+ </para>
- <para>
<programlisting>
mysql> SELECT 1+2*3;
-> 7
mysql> SELECT (1+2)*3;
-> 9
</programlisting>
- </para></listitem>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
</section>
<section id="comparison-operators">
- <title
id='title-comparison-operators'>&title-comparison-operators;</title>
+ <title
id='title-comparison-operators'>&title-comparison-operators;</title>
- <indexterm type="function">
+ <indexterm type="function">
<primary>comparison operators</primary>
- </indexterm>
+ </indexterm>
<!-- help_category Comparison operators@Functions -->
- <indexterm type="concept">
+ <indexterm type="concept">
<primary>casts</primary>
- </indexterm>
+ </indexterm>
- <indexterm type="concept">
+ <indexterm type="concept">
<primary>type conversions</primary>
- </indexterm>
+ </indexterm>
- <para>
+ <para>
Comparison operations result in a value of <literal>1</literal>
(TRUE), <literal>0</literal> (FALSE), or
<literal>NULL</literal>.
These operations work for both numbers and strings. Strings are
automatically converted to numbers and numbers to strings as
necessary.
- </para>
+ </para>
- <para>
+ <para>
Some of the functions in this section (such as
<literal>LEAST()</literal> and <literal>GREATEST()</literal>)
return
values other than <literal>1</literal> (TRUE),
<literal>0</literal>
(FALSE), or <literal>NULL</literal>. However, the value they return
is based on comparison operations performed as described by the
following rules.
- </para>
+ </para>
- <para>
+ <para>
MySQL compares values using the following rules:
- </para>
-
- <itemizedlist>
+ </para>
- <listitem><para>
- If one or both arguments are <literal>NULL</literal>, the result of
- the comparison is <literal>NULL</literal>, except for the
- <literal>NULL</literal>-safe
<literal><=></literal> equality
- comparison operator.
- </para></listitem>
+ <itemizedlist>
- <listitem><para>
- If both arguments in a comparison operation are strings, they are
- compared as strings.
- </para></listitem>
+ <listitem>
+ <para>
+ If one or both arguments are <literal>NULL</literal>, the result
+ of the comparison is <literal>NULL</literal>, except for the
+ <literal>NULL</literal>-safe
<literal><=></literal>
+ equality comparison operator.
+ </para>
+ </listitem>
- <listitem><para>
- If both arguments are integers, they are compared as integers.
- </para></listitem>
+ <listitem>
+ <para>
+ If both arguments in a comparison operation are strings, they
+ are compared as strings.
+ </para>
+ </listitem>
- <listitem><para>
- Hexadecimal values are treated as binary strings if not compared to
- a number.
- </para></listitem>
+ <listitem>
+ <para>
+ If both arguments are integers, they are compared as integers.
+ </para>
+ </listitem>
- <listitem><indexterm type="concept">
- <primary>ODBC compatibility</primary>
- </indexterm>
+ <listitem>
+ <para>
+ Hexadecimal values are treated as binary strings if not compared
+ to a number.
+ </para>
+ </listitem>
- <indexterm type="concept">
- <primary>compatibility</primary>
- <secondary>with ODBC</secondary>
- </indexterm>
+ <listitem>
+ <indexterm type="concept">
+ <primary>ODBC compatibility</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>compatibility</primary>
+ <secondary>with ODBC</secondary>
+ </indexterm>
- <para>
- If one of the arguments is a <literal>TIMESTAMP</literal> or
- <literal>DATETIME</literal> column and the other argument is a
- constant, the constant is converted to a timestamp before the
- comparison is performed. This is done to be more ODBC-friendly.
- Note that this is not done for arguments in
- <literal>IN()</literal>! To be safe, always use complete
- datetime/date/time strings when doing comparisons.
- </para></listitem>
+ <para>
+ If one of the arguments is a <literal>TIMESTAMP</literal> or
+ <literal>DATETIME</literal> column and the other argument is a
+ constant, the constant is converted to a timestamp before the
+ comparison is performed. This is done to be more ODBC-friendly.
+ Note that this is not done for arguments in
+ <literal>IN()</literal>! To be safe, always use complete
+ datetime/date/time strings when doing comparisons.
+ </para>
+ </listitem>
- <listitem><para>
- In all other cases, the arguments are compared as floating-point
- (real) numbers.
- </para></listitem>
+ <listitem>
+ <para>
+ In all other cases, the arguments are compared as floating-point
+ (real) numbers.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
+ <para>
By default, string comparisons are not case sensitive and use the
current character set (ISO-8859-1 Latin1 by default, which also
works excellently for English).
- </para>
+ </para>
- <para>
+ <para>
To convert a value to a specific type for comparison purposes, you
can use the <literal>CAST()</literal> function. String values can be
converted to a different character set using
<literal>CONVERT()</literal>. See <xref linkend="cast-functions"/>.
- </para>
+ </para>
- <para>
+ <para>
The following examples illustrate conversion of strings to numbers
for comparison operations:
- </para>
+ </para>
<programlisting>
mysql> SELECT 1 > '6x';
@@ -294,46 +307,47 @@
-> 1
</programlisting>
- <para>
+ <para>
Note that when you are comparing a string column with a number,
MySQL can't use an index on the column to quickly look up the value.
If <replaceable>str_col</replaceable> is an indexed string column,
the index cannot be used when performing the lookup in the following
statement:
- </para>
+ </para>
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE
<replaceable>str_col</replaceable>=1;
</programlisting>
- <para>
+ <para>
The reason for this is that there are many different strings that
may convert to the value <literal>1</literal>:
<literal>'1'</literal>, <literal>' 1'</literal>,
<literal>'1a'</literal>, ...
- </para>
+ </para>
- <itemizedlist>
+ <itemizedlist>
<!-- description_for_help_topic = -->
<indexterm type="function">
- <primary>= (equal)</primary>
+ <primary>= (equal)</primary>
</indexterm>
<indexterm type="function">
- <primary>equal (=)</primary>
+ <primary>equal (=)</primary>
</indexterm>
- <listitem><para>
- <literal>=</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>=</literal>
+ </para>
- <para>
- Equal:
+ <para>
+ Equal:
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic = -->
@@ -359,27 +373,28 @@
</para>
<para>
- <indexterm type="concept">
- <primary><literal>NULL</literal></primary>
- <secondary>testing for null</secondary>
- </indexterm>
-
- <indexterm type="function">
- <primary><=> (equal to)</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="concept">
+ <primary><literal>NULL</literal></primary>
+ <secondary>testing for null</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary><=> (equal to)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><=></literal>
+ <listitem>
+ <para>
+ <literal><=></literal>
</para>
<para>
- <literal>NULL</literal>-safe equal. This operator performs an
- equality comparison like the <literal>=</literal> operator, but
- returns <literal>1</literal> rather than
<literal>NULL</literal> if
- both operands are <literal>NULL</literal>, and
<literal>0</literal>
- rather than <literal>NULL</literal> if one operand is
- <literal>NULL</literal>.
+ <literal>NULL</literal>-safe equal. This operator performs an
+ equality comparison like the <literal>=</literal> operator, but
+ returns 1 rather than <literal>NULL</literal> if both operands
+ are <literal>NULL</literal>, and 0 rather than
+ <literal>NULL</literal> if one operand
is<literal>NULL</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -398,37 +413,35 @@
</para>
<para>
- <literal><=></literal> was added in MySQL 3.23.0.
+<!-- description_for_help_topic != <> -->
</para>
<para>
-<!-- description_for_help_topic != <> -->
+ <indexterm type="function">
+ <primary><> (not equal)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>not equal (<>)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>!= (not equal)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>not equal (!=)</primary>
+ </indexterm>
</para>
+ </listitem>
+ <listitem>
<para>
- <indexterm type="function">
- <primary><> (not equal)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>not equal (<>)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>!= (not equal)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>not equal (!=)</primary>
- </indexterm>
- </para></listitem>
-
- <listitem><para>
- <literal><></literal> , <literal>!=</literal>
+ <literal><></literal> , <literal>!=</literal>
</para>
<para>
- Not equal:
+ Not equal:
<!-- end_description_for_help_topic -->
</para>
@@ -453,21 +466,23 @@
</para>
<para>
- <indexterm type="function">
- <primary><= (less than or equal)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>less than or equal (<=)</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="function">
+ <primary><= (less than or equal)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>less than or equal (<=)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><=</literal>
+ <listitem>
+ <para>
+ <literal><=</literal>
</para>
<para>
- Less than or equal:
+ Less than or equal:
<!-- end_description_for_help_topic -->
</para>
@@ -488,21 +503,23 @@
</para>
<para>
- <indexterm type="function">
- <primary>< (less than)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>less than (<)</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="function">
+ <primary>< (less than)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>less than (<)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><</literal>
+ <listitem>
+ <para>
+ <literal><</literal>
</para>
<para>
- Less than:
+ Less than:
<!-- end_description_for_help_topic -->
</para>
@@ -523,21 +540,23 @@
</para>
<para>
- <indexterm type="function">
- <primary>>= (greater than or equal)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>greater than or equal (>=)</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="function">
+ <primary>>= (greater than or equal)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>greater than or equal (>=)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>>=</literal>
+ <listitem>
+ <para>
+ <literal>>=</literal>
</para>
<para>
- Greater than or equal:
+ Greater than or equal:
<!-- end_description_for_help_topic -->
</para>
@@ -558,21 +577,23 @@
</para>
<para>
- <indexterm type="function">
- <primary>> (greater than)</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>greater than (>)</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="function">
+ <primary>> (greater than)</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>greater than (>)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>></literal>
+ <listitem>
+ <para>
+ <literal>></literal>
</para>
<para>
- Greater than:
+ Greater than:
<!-- end_description_for_help_topic -->
</para>
@@ -593,40 +614,42 @@
</para>
<para>
- <indexterm type="concept">
- <primary><literal>TRUE</literal></primary>
- <secondary>testing for</secondary>
- </indexterm>
-
- <indexterm type="concept">
- <primary><literal>FALSE</literal></primary>
- <secondary>testing for</secondary>
- </indexterm>
-
- <indexterm type="concept">
- <primary><literal>UNKNOWN</literal></primary>
- <secondary>testing for</secondary>
- </indexterm>
-
- <indexterm type="function">
- <primary>IS boolean_value</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>IS NOT boolean_value</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="concept">
+ <primary><literal>TRUE</literal></primary>
+ <secondary>testing for</secondary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary><literal>FALSE</literal></primary>
+ <secondary>testing for</secondary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary><literal>UNKNOWN</literal></primary>
+ <secondary>testing for</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>IS boolean_value</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>IS NOT boolean_value</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>IS
<replaceable>boolean_value</replaceable></literal> ,
- <literal>IS NOT
<replaceable>boolean_value</replaceable></literal>
+ <listitem>
+ <para>
+ <literal>IS
<replaceable>boolean_value</replaceable></literal> ,
+ <literal>IS NOT
<replaceable>boolean_value</replaceable></literal>
</para>
<para>
- Tests whether a value against a boolean value, where
- <replaceable>boolean_value</replaceable> can be
- <literal>TRUE</literal>, <literal>FALSE</literal>, or
- <literal>UNKNOWN</literal>.
+ Tests whether a value against a boolean value, where
+ <replaceable>boolean_value</replaceable> can be
+ <literal>TRUE</literal>, <literal>FALSE</literal>, or
+ <literal>UNKNOWN</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -645,9 +668,9 @@
</para>
<para>
- <literal>IS [NOT]
- <replaceable>boolean_value</replaceable></literal> syntax was
added
- in MySQL 5.0.2.
+ <literal>IS [NOT]
+ <replaceable>boolean_value</replaceable></literal> syntax was
+ added in MySQL 5.0.2.
</para>
<para>
@@ -655,26 +678,28 @@
</para>
<para>
- <indexterm type="concept">
- <primary><literal>NULL</literal></primary>
- <secondary>testing for null</secondary>
- </indexterm>
-
- <indexterm type="function">
- <primary>IS NULL</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>IS NOT NULL</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="concept">
+ <primary><literal>NULL</literal></primary>
+ <secondary>testing for null</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>IS NULL</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>IS NOT NULL</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>IS NULL</literal> , <literal>IS NOT NULL</literal>
+ <listitem>
+ <para>
+ <literal>IS NULL</literal> , <literal>IS NOT NULL</literal>
</para>
<para>
- Tests whether a value is or is not <literal>NULL</literal>.
+ Tests whether a value is or is not <literal>NULL</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -693,96 +718,105 @@
</para>
<para>
- <indexterm type="concept">
- <primary>ODBC compatibility</primary>
- </indexterm>
+ <indexterm type="concept">
+ <primary>ODBC compatibility</primary>
+ </indexterm>
- <indexterm type="concept">
- <primary>compatibility</primary>
- <secondary>with ODBC</secondary>
- </indexterm>
+ <indexterm type="concept">
+ <primary>compatibility</primary>
+ <secondary>with ODBC</secondary>
+ </indexterm>
- To be able to work well with ODBC programs, MySQL supports the
- following extra features when using <literal>IS NULL</literal>:
+ To be able to work well with ODBC programs, MySQL supports the
+ following extra features when using <literal>IS NULL</literal>:
</para>
<para>
- <itemizedlist>
+ <itemizedlist>
- <listitem><para>
- You can find the row that contains the most recent
- <literal>AUTO_INCREMENT</literal> value by issuing a statement of
- the following form immediately after generating the value:
- </para>
+ <listitem>
+ <para>
+ You can find the row that contains the most recent
+ <literal>AUTO_INCREMENT</literal> value by issuing a
+ statement of the following form immediately after generating
+ the value:
+ </para>
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE
<replaceable>auto_col</replaceable> IS NULL
</programlisting>
- <para>
- This behavior can be disabled by setting
- <literal>SQL_AUTO_IS_NULL=0</literal>. See
- <xref linkend="set-option"/>.
- </para></listitem>
-
- <listitem><para>
- For <literal>DATE</literal> and
<literal>DATETIME</literal>
- columns that are declared as <literal>NOT NULL</literal>, you can
- find the special date <literal>'0000-00-00'</literal> by using a
- statement like this:
- </para>
+ <para>
+ This behavior can be disabled by setting
+ <literal>SQL_AUTO_IS_NULL=0</literal>. See
+ <xref linkend="set-option"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal>DATE</literal> and
+ <literal>DATETIME</literal> columns that are declared as
+ <literal>NOT NULL</literal>, you can find the special date
+ <literal>'0000-00-00'</literal> by using a statement like
+ this:
+ </para>
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE
<replaceable>date_column</replaceable> IS NULL
</programlisting>
- <para>
- This is needed to get some ODBC applications to work because ODBC
- doesn't support a <literal>'0000-00-00'</literal> date value.
- </para></listitem>
-
- </itemizedlist>
- </para>
+ <para>
+ This is needed to get some ODBC applications to work
+ because ODBC doesn't support a
+ <literal>'0000-00-00'</literal> date value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
<para>
<!-- description_for_help_topic BETWEEN AND BETWEEN AND -->
</para>
<para>
- <indexterm type="function">
- <primary>BETWEEN ... AND</primary>
- </indexterm>
- </para></listitem>
+ <indexterm type="function">
+ <primary>BETWEEN ... AND</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><replaceable>expr</replaceable> BETWEEN
- <replaceable>min</replaceable> AND
- <replaceable>max</replaceable></literal>
- </para>
+ <listitem>
+ <para>
+ <literal><replaceable>expr</replaceable> BETWEEN
+ <replaceable>min</replaceable> AND
+ <replaceable>max</replaceable></literal>
+ </para>
- <para>
- If <replaceable>expr</replaceable> is greater than or equal to
- <replaceable>min</replaceable> and
<replaceable>expr</replaceable>
- is less than or equal to <replaceable>max</replaceable>,
- <literal>BETWEEN</literal> returns <literal>1</literal>,
otherwise
- it returns <literal>0</literal>. This is equivalent to the
- expression <literal>(<replaceable>min</replaceable> <=
- <replaceable>expr</replaceable> AND
<replaceable>expr</replaceable>
- <= <replaceable>max</replaceable>)</literal> if all the
- arguments are of the same type. Otherwise type conversion takes
- place according to the rules described at the beginning of this
- section, but applied to all the three arguments.
- </para>
+ <para>
+ If <replaceable>expr</replaceable> is greater than or equal to
+ <replaceable>min</replaceable> and
<replaceable>expr</replaceable>
+ is less than or equal to <replaceable>max</replaceable>,
+ <literal>BETWEEN</literal> returns <literal>1</literal>,
+ otherwise it returns <literal>0</literal>. This is equivalent to
+ the expression <literal>(<replaceable>min</replaceable>
<=
+ <replaceable>expr</replaceable> AND
<replaceable>expr</replaceable>
+ <= <replaceable>max</replaceable>)</literal> if all the
+ arguments are of the same type. Otherwise type conversion takes
+ place according to the rules described at the beginning of this
+ section, but applied to all the three arguments.
+ </para>
- <para>
+ <para>
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic BETWEEN AND -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
@@ -793,97 +827,104 @@
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic NOT BETWEEN -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>NOT BETWEEN</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>NOT BETWEEN</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><replaceable>expr</replaceable> NOT BETWEEN
- <replaceable>min</replaceable> AND
- <replaceable>max</replaceable></literal>
- </para>
+ <listitem>
+ <para>
+ <literal><replaceable>expr</replaceable> NOT BETWEEN
+ <replaceable>min</replaceable> AND
+ <replaceable>max</replaceable></literal>
+ </para>
- <para>
- This is the same as <literal>NOT (<replaceable>expr</replaceable>
- BETWEEN <replaceable>min</replaceable> AND
- <replaceable>max</replaceable>)</literal>.
+ <para>
+ This is the same as <literal>NOT
+ (<replaceable>expr</replaceable> BETWEEN
+ <replaceable>min</replaceable> AND
+ <replaceable>max</replaceable>)</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic COALESCE -->
- </para>
-
- <para>
- <indexterm type="concept">
- <primary><literal>NULL</literal></primary>
- <secondary>testing for null</secondary>
- </indexterm>
+ </para>
- <indexterm type="function">
- <primary>COALESCE()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="concept">
+ <primary><literal>NULL</literal></primary>
+ <secondary>testing for null</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>COALESCE()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>COALESCE(<replaceable>value</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>COALESCE(<replaceable>value</replaceable>,...)</literal>
+ </para>
- <para>
- Returns the first non-<literal>NULL</literal> value in the list.
+ <para>
+ Returns the first non-<literal>NULL</literal> value in the list.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic COALESCE -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic GREATEST -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>GREATEST()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>GREATEST()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>GREATEST(<replaceable>value1</replaceable>,<replaceable>value2</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>GREATEST(<replaceable>value1</replaceable>,<replaceable>value2</replaceable>,...)</literal>
+ </para>
- <para>
- With two or more arguments, returns the largest (maximum-valued)
- argument. The arguments are compared using the same rules as for
- <literal>LEAST()</literal>.
+ <para>
+ With two or more arguments, returns the largest (maximum-valued)
+ argument. The arguments are compared using the same rules as for
+ <literal>LEAST()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic GREATEST -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT GREATEST(2,0);
-> 2
@@ -892,172 +933,184 @@
mysql> SELECT GREATEST('B','A','C');
-> 'C'
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic IN -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>IN</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>IN</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><replaceable>expr</replaceable> IN
- (<replaceable>value</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal><replaceable>expr</replaceable> IN
+ (<replaceable>value</replaceable>,...)</literal>
+ </para>
- <para>
- Returns <literal>1</literal> if
<replaceable>expr</replaceable> is
- any of the values in the <literal>IN</literal> list, else returns
- <literal>0</literal>. If all values are constants, they are
- evaluated according to the type of <replaceable>expr</replaceable>
- and sorted. The search for the item then is done using a binary
- search. This means <literal>IN</literal> is very quick if the
- <literal>IN</literal> value list consists entirely of constants. If
- <replaceable>expr</replaceable> is a case-sensitive string
- expression, the string comparison is performed in case-sensitive
- fashion.
+ <para>
+ Returns <literal>1</literal> if
<replaceable>expr</replaceable>
+ is any of the values in the <literal>IN</literal> list, else
+ returns <literal>0</literal>. If all values are constants, they
+ are evaluated according to the type of
+ <replaceable>expr</replaceable> and sorted. The search for the
+ item then is done using a binary search. This means
+ <literal>IN</literal> is very quick if the
<literal>IN</literal>
+ value list consists entirely of constants. If
+ <replaceable>expr</replaceable> is a case-sensitive string
+ expression, the string comparison is performed in case-sensitive
+ fashion.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic IN -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1
</programlisting>
- </para>
+ </para>
- <para>
- The number of values in the <literal>IN</literal> list is only
- limited by the <literal>max_allowed_packet</literal> value.
- </para>
+ <para>
+ The number of values in the <literal>IN</literal> list is only
+ limited by the <literal>max_allowed_packet</literal> value.
+ </para>
- <para>
- In MySQL 5.0, in order to comply with the SQL standard,
- <literal>IN</literal> returns <literal>NULL</literal> not
only if
- the expression on the left hand side is <literal>NULL</literal>,
- but also if no match is found in the list and one of the
- expressions in the list is <literal>NULL</literal>.
- </para>
+ <para>
+ In MySQL 5.0, in order to comply with the SQL standard,
+ <literal>IN</literal> returns <literal>NULL</literal> not
only
+ if the expression on the left hand side is
+ <literal>NULL</literal>, but also if no match is found in the
+ list and one of the expressions in the list is
+ <literal>NULL</literal>.
+ </para>
- <para>
- <literal>IN()</literal> syntax can also be used to write certain
- types of subqueries. See <xref linkend="any-in-some-subqueries"/>.
- </para>
+ <para>
+ <literal>IN()</literal> syntax can also be used to write certain
+ types of subqueries. See
+ <xref linkend="any-in-some-subqueries"/>.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic NOT IN -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>NOT IN</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>NOT IN</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal><replaceable>expr</replaceable> NOT IN
- (<replaceable>value</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal><replaceable>expr</replaceable> NOT IN
+ (<replaceable>value</replaceable>,...)</literal>
+ </para>
- <para>
- This is the same as <literal>NOT (<replaceable>expr</replaceable>
- IN (<replaceable>value</replaceable>,...))</literal>.
+ <para>
+ This is the same as <literal>NOT
+ (<replaceable>expr</replaceable> IN
+ (<replaceable>value</replaceable>,...))</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic ISNULL -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>ISNULL()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>ISNULL()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>ISNULL(<replaceable>expr</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>ISNULL(<replaceable>expr</replaceable>)</literal>
+ </para>
- <para>
- If <replaceable>expr</replaceable> is
<literal>NULL</literal>,
- <literal>ISNULL()</literal> returns <literal>1</literal>,
otherwise
- it returns <literal>0</literal>.
+ <para>
+ If <replaceable>expr</replaceable> is
<literal>NULL</literal>,
+ <literal>ISNULL()</literal> returns <literal>1</literal>,
+ otherwise it returns <literal>0</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic ISNULL -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT ISNULL(1+1);
-> 0
mysql> SELECT ISNULL(1/0);
-> 1
</programlisting>
- </para>
+ </para>
- <para>
- A comparison of <literal>NULL</literal> values using
- <literal>=</literal> is always false.
- </para>
+ <para>
+ A comparison of <literal>NULL</literal> values using
+ <literal>=</literal> is always false.
+ </para>
- <para>
- The <literal>ISNULL()</literal> function shares some special
- behaviors with the <literal>IS NULL</literal> comparison operator,
- see the description of <literal>IS NULL</literal> at
- <xref linkend="comparison-operators" />.
- </para>
+ <para>
+ The <literal>ISNULL()</literal> function shares some special
+ behaviors with the <literal>IS NULL</literal> comparison
+ operator, see the description of <literal>IS NULL</literal> in
+ <xref linkend="comparison-operators" />.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic INTERVAL -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>INTERVAL()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>INTERVAL()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>INTERVAL(<replaceable>N</replaceable>,<replaceable>N1</replaceable>,<replaceable>N2</replaceable>,<replaceable>N3</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>INTERVAL(<replaceable>N</replaceable>,<replaceable>N1</replaceable>,<replaceable>N2</replaceable>,<replaceable>N3</replaceable>,...)</literal>
+ </para>
- <para>
- Returns <literal>0</literal> if <replaceable>N</replaceable>
<
- <replaceable>N1</replaceable>, <literal>1</literal> if
- <replaceable>N</replaceable> <
<replaceable>N2</replaceable> and
- so on or <literal>-1</literal> if
<replaceable>N</replaceable> is
- <literal>NULL</literal>. All arguments are treated as integers. It
- is required that <replaceable>N1</replaceable> <
- <replaceable>N2</replaceable> <
<replaceable>N3</replaceable>
- < <literal>...</literal> <
<replaceable>Nn</replaceable> for
- this function to work correctly. This is because a binary search is
- used (very fast).
+ <para>
+ Returns <literal>0</literal> if
<replaceable>N</replaceable>
+ < <replaceable>N1</replaceable>,
<literal>1</literal> if
+ <replaceable>N</replaceable> <
<replaceable>N2</replaceable>
+ and so on or <literal>-1</literal> if
+ <replaceable>N</replaceable> is <literal>NULL</literal>.
All
+ arguments are treated as integers. It is required that
+ <replaceable>N1</replaceable> <
<replaceable>N2</replaceable>
+ < <replaceable>N3</replaceable> <
<literal>...</literal>
+ < <replaceable>Nn</replaceable> for this function to work
+ correctly. This is because a binary search is used (very fast).
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic INTERVAL -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
@@ -1066,61 +1119,73 @@
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
-> 0
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic LEAST -->
- </para>
-
- <para>
- <indexterm type="function">
- <primary>LEAST()</primary>
- </indexterm>
- </para></listitem>
-
- <listitem><para>
-
<literal>LEAST(<replaceable>value1</replaceable>,<replaceable>value2</replaceable>,...)</literal>
- </para>
-
- <para>
- With two or more arguments, returns the smallest (minimum-valued)
- argument. The arguments are compared using the following rules.
- </para>
-
- <para>
- <itemizedlist>
+ </para>
- <listitem><para>
- If the return value is used in an <literal>INTEGER</literal>
- context or all arguments are integer-valued, they are compared as
- integers.
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>LEAST()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- If the return value is used in a <literal>REAL</literal> context
- or all arguments are real-valued, they are compared as reals.
- </para></listitem>
+ <listitem>
+ <para>
+
<literal>LEAST(<replaceable>value1</replaceable>,<replaceable>value2</replaceable>,...)</literal>
+ </para>
- <listitem><para>
- If any argument is a case-sensitive string, the arguments are
- compared as case-sensitive strings.
- </para></listitem>
+ <para>
+ With two or more arguments, returns the smallest
+ (minimum-valued) argument. The arguments are compared using the
+ following rules:
+ </para>
- <listitem><para>
- In other cases, the arguments are compared as case-insensitive
- strings.
- </para></listitem>
+ <para>
+ <itemizedlist>
- </itemizedlist>
+ <listitem>
+ <para>
+ If the return value is used in an
+ <literal>INTEGER</literal> context or all arguments are
+ integer-valued, they are compared as integers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the return value is used in a <literal>REAL</literal>
+ context or all arguments are real-valued, they are
+ compared as reals.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If any argument is a case-sensitive string, the arguments
+ are compared as case-sensitive strings.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ In all other cases, the arguments are compared as
+ case-insensitive strings.
+ </para>
+ </listitem>
+
+ </itemizedlist>
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic LEAST -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT LEAST(2,0);
-> 0
@@ -1129,86 +1194,89 @@
mysql> SELECT LEAST('B','A','C');
-> 'A'
</programlisting>
- </para>
+ </para>
- <para>
- Note that the preceding conversion rules can produce strange
- results in some borderline cases:
- </para>
+ <para>
+ Note that the preceding conversion rules can produce strange
+ results in some borderline cases:
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
- -> -9223372036854775808
+ -> -9223372036854775808
</programlisting>
- </para>
-
- <para>
- This happens because MySQL reads
- <literal>9223372036854775808.0</literal> in an integer context. The
- integer representation is not good enough to hold the value, so it
- wraps to a signed integer.
- </para></listitem>
+ </para>
- </itemizedlist>
+ <para>
+ This happens because MySQL reads
+ <literal>9223372036854775808.0</literal> in an integer context.
+ The integer representation is not good enough to hold the value,
+ so it wraps to a signed integer.
+ </para>
+ </listitem>
+
+ </itemizedlist>
</section>
<section id="logical-operators">
- <title id='title-logical-operators'>&title-logical-operators;</title>
+ <title id='title-logical-operators'>&title-logical-operators;</title>
- <indexterm type="function">
+ <indexterm type="function">
<primary>logical operators</primary>
- </indexterm>
+ </indexterm>
- <indexterm type="function">
+ <indexterm type="function">
<primary>operators</primary>
<secondary>logical</secondary>
- </indexterm>
+ </indexterm>
<!-- help_category Logical operators@Functions -->
- <para>
- In SQL, all logical operators evaluate to TRUE, FALSE, or
- <literal>NULL</literal> (UNKNOWN). In MySQL, these are implemented
- as <literal>1</literal> (TRUE), <literal>0</literal> (FALSE),
and
+ <para>
+ In SQL, all logical operators evaluate to <literal>TRUE</literal>,
+ <literal>FALSE</literal>FALSE, or <literal>NULL</literal>
+ (<literal>UNKNOWN</literal>). In MySQL, these are implemented as 1
+ (<literal>TRUE</literal>), 0 (<literal>FALSE</literal>FALSE),
and
<literal>NULL</literal>. Most of this is common to different SQL
database servers, although some servers may return any non-zero
- value for TRUE.
- </para>
+ value for <literal>TRUE</literal>.
+ </para>
- <itemizedlist>
+ <itemizedlist>
<!-- description_for_help_topic ! NOT -->
<indexterm type="function">
- <primary>NOT</primary>
- <secondary>logical</secondary>
+ <primary>NOT</primary>
+ <secondary>logical</secondary>
</indexterm>
<indexterm type="function">
- <primary>! (logical NOT)</primary>
+ <primary>! (logical NOT)</primary>
</indexterm>
- <listitem><para>
- <literal>NOT</literal> , <literal>!</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>NOT</literal> , <literal>!</literal>
+ </para>
- <para>
- Logical NOT. Evaluates to <literal>1</literal> if the operand is
- <literal>0</literal>, to <literal>0</literal> if the operand
is
- non-zero, and <literal>NOT NULL</literal> returns
- <literal>NULL</literal>.
+ <para>
+ Logical NOT. Evaluates to <literal>1</literal> if the operand is
+ <literal>0</literal>, to <literal>0</literal> if the
operand is
+ non-zero, and <literal>NOT NULL</literal> returns
+ <literal>NULL</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic ! -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT NOT 10;
-> 0
@@ -1222,43 +1290,45 @@
-> 1
</programlisting>
- The last example produces <literal>1</literal> because the
- expression evaluates the same way as <literal>(!1)+1</literal>.
- </para>
+ The last example produces <literal>1</literal> because the
+ expression evaluates the same way as <literal>(!1)+1</literal>.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic && AND -->
- </para>
-
- <para>
- <indexterm type="function">
- <primary>AND</primary>
- <secondary>logical</secondary>
- </indexterm>
+ </para>
- <indexterm type="function">
- <primary>&& (logical AND)</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>AND</primary>
+ <secondary>logical</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>&& (logical AND)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>AND</literal> ,
<literal>&&</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>AND</literal> ,
<literal>&&</literal>
+ </para>
- <para>
- Logical AND. Evaluates to <literal>1</literal> if all operands are
- non-zero and not <literal>NULL</literal>, to
<literal>0</literal>
- if one or more operands are <literal>0</literal>, otherwise
- <literal>NULL</literal> is returned.
+ <para>
+ Logical AND. Evaluates to <literal>1</literal> if all operands
+ are non-zero and not <literal>NULL</literal>, to 0 if one or
+ more operands are <literal>0</literal>, otherwise
+ <literal>NULL</literal> is returned.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic && -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT 1 && 1;
-> 1
@@ -1271,55 +1341,58 @@
mysql> SELECT NULL && 0;
-> 0
</programlisting>
- </para>
+ </para>
<!-- TODO: Determine if it's necessary to keep this at all. -->
<!--
- <para>
- Please note that MySQL versions prior to 4.0.5 stop evaluation when
- a <literal>NULL</literal> is encountered, rather than continuing
- the process to check for possible <literal>0</literal> values. This
- means that in these versions, <literal>SELECT (NULL AND
- 0)</literal> returns <literal>NULL</literal> instead of
- <literal>0</literal>. As of MySQL 4.0.5, the code has been
- re-engineered so that the result is always as prescribed by the SQL
- standards while still using the optimization wherever possible.
- </para>
+ <para>
+ Please note that MySQL versions prior to 4.0.5 stop evaluation
+ when a <literal>NULL</literal> is encountered, rather than
+ continuing the process to check for possible zero values. This
+ means that in these versions, <literal>SELECT (NULL AND
+ 0)</literal> returns <literal>NULL</literal> instead of 0. As
of
+ MySQL 4.0.5, the code has been re-engineered so that the result
+ is always as prescribed by the SQL standards while still using
+ the optimization wherever possible.
+ </para>
-->
- <para>
+ <para>
<!-- description_for_help_topic || OR -->
- </para>
-
- <para>
- <indexterm type="function">
- <primary>OR</primary>
- <secondary>logical</secondary>
- </indexterm>
+ </para>
- <indexterm type="function">
- <primary>|| (logical OR)</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>OR</primary>
+ <secondary>logical</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>|| (logical OR)</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>OR</literal> , <literal>||</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>OR</literal> , <literal>||</literal>
+ </para>
- <para>
- Logical OR. When both operands are non-<literal>NULL</literal>, the
- result is <literal>1</literal> if any operand is non-zero, and
- <literal>0</literal> otherwise. With a
<literal>NULL</literal>
- operand, the result is <literal>1</literal> if the other operand is
- non-zero, and <literal>NULL</literal> otherwise. If both operands
- are <literal>NULL</literal>, the result is
<literal>NULL</literal>.
+ <para>
+ Logical OR. When both operands are non-<literal>NULL</literal>,
+ the result is 1 if any operand is non-zero,
+ and 0 otherwise. With a <literal>NULL</literal> operand, the
+ result is 1 if the other operand is non-zero, and
+ <literal>NULL</literal> otherwise. If both operands are
+ <literal>NULL</literal>, the result is also
+ <literal>NULL</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic || -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT 1 || 1;
-> 1
@@ -1332,37 +1405,39 @@
mysql> SELECT 1 || NULL;
-> 1
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic XOR -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>XOR</primary>
- <secondary>logical</secondary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>XOR</primary>
+ <secondary>logical</secondary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>XOR</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>XOR</literal>
+ </para>
- <para>
- Logical XOR. Returns <literal>NULL</literal> if either operand is
- <literal>NULL</literal>. For non-<literal>NULL</literal>
operands,
- evaluates to <literal>1</literal> if an odd number of operands is
- non-zero, otherwise <literal>0</literal> is returned.
+ <para>
+ Logical XOR. Returns <literal>NULL</literal> if either operand
+ is <literal>NULL</literal>. For
non-<literal>NULL</literal>
+ operands, evaluates to 1 if an odd number of operands is
+ non-zero, otherwise 0 is returned.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic XOR -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT 1 XOR 1;
-> 0
@@ -1373,74 +1448,75 @@
mysql> SELECT 1 XOR 1 XOR 1;
-> 1
</programlisting>
- </para>
-
- <para>
- <literal>a XOR b</literal> is mathematically equal to <literal>(a
- AND (NOT b)) OR ((NOT a) and b)</literal>.
- </para>
- </listitem>
+ </para>
- </itemizedlist>
+ <para>
+ <literal>a XOR b</literal> is mathematically equal to
+ <literal>(a AND (NOT b)) OR ((NOT a) and b)</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
</section>
- </section>
+</section>
- <section id="control-flow-functions">
+<section id="control-flow-functions">
- <title
id='title-control-flow-functions'>&title-control-flow-functions;</title>
+<title
id='title-control-flow-functions'>&title-control-flow-functions;</title>
- <indexterm type="function">
- <primary>control flow functions</primary>
- </indexterm>
+<indexterm type="function">
+ <primary>control flow functions</primary>
+</indexterm>
- <indexterm type="function">
- <primary>functions</primary>
- <secondary>control flow</secondary>
- </indexterm>
+<indexterm type="function">
+ <primary>functions</primary>
+ <secondary>control flow</secondary>
+</indexterm>
<!-- help_category Control flow functions@Functions -->
- <itemizedlist>
+<itemizedlist>
<!-- description_for_help_topic CASE WHEN THEN ELSE END -->
- <indexterm type="function">
+ <indexterm type="function">
<primary>CASE</primary>
- </indexterm>
+ </indexterm>
- <listitem><para>
- <literal>CASE <replaceable>value</replaceable> WHEN
- [<replaceable>compare-value</replaceable>] THEN
- <replaceable>result</replaceable> [WHEN
- [<replaceable>compare-value</replaceable>] THEN
- <replaceable>result</replaceable> ...] [ELSE
- <replaceable>result</replaceable>] END</literal> ,
<literal>CASE
- WHEN [<replaceable>condition</replaceable>] THEN
- <replaceable>result</replaceable> [WHEN
- [<replaceable>condition</replaceable>] THEN
- <replaceable>result</replaceable> ...] [ELSE
- <replaceable>result</replaceable>] END</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>CASE <replaceable>value</replaceable> WHEN
+ [<replaceable>compare-value</replaceable>] THEN
+ <replaceable>result</replaceable> [WHEN
+ [<replaceable>compare-value</replaceable>] THEN
+ <replaceable>result</replaceable> ...] [ELSE
+ <replaceable>result</replaceable>] END</literal> ,
<literal>CASE
+ WHEN [<replaceable>condition</replaceable>] THEN
+ <replaceable>result</replaceable> [WHEN
+ [<replaceable>condition</replaceable>] THEN
+ <replaceable>result</replaceable> ...] [ELSE
+ <replaceable>result</replaceable>] END</literal>
+ </para>
- <para>
- The first version returns the <replaceable>result</replaceable>
- where
+ <para>
+ The first version returns the <replaceable>result</replaceable>
+ where
<literal><replaceable>value</replaceable>=<replaceable>compare-value</replaceable></literal>.
- The second version returns the result for the first condition that
- is true. If there was no matching result value, the result after
- <literal>ELSE</literal> is returned, or
<literal>NULL</literal> if
- there is no <literal>ELSE</literal> part.
+ The second version returns the result for the first condition that
+ is true. If there was no matching result value, the result after
+ <literal>ELSE</literal> is returned, or
<literal>NULL</literal> if
+ there is no <literal>ELSE</literal> part.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic CASE -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
@@ -1451,49 +1527,51 @@
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL
</programlisting>
- </para>
+ </para>
- <para>
- The default return type of a <literal>CASE</literal> expression is
- the compatible aggregated type of all return values, but also
- depends on the context in which it is used. If used in a string
- context, the result is returned as a string. If used in a numeric
- context, then the result is returned as a decimal, real, or integer
- value.
- </para>
+ <para>
+ The default return type of a <literal>CASE</literal> expression is
+ the compatible aggregated type of all return values, but also
+ depends on the context in which it is used. If used in a string
+ context, the result is returned as a string. If used in a numeric
+ context, then the result is returned as a decimal, real, or integer
+ value.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic IF -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>IF()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>IF()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>IF(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>,<replaceable>expr3</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>IF(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>,<replaceable>expr3</replaceable>)</literal>
+ </para>
- <para>
- If <replaceable>expr1</replaceable> is TRUE
- (<literal><replaceable>expr1</replaceable> <>
0</literal> and
- <literal><replaceable>expr1</replaceable> <>
NULL</literal>)
- then <literal>IF()</literal> returns
- <replaceable>expr2</replaceable>, else it returns
- <replaceable>expr3</replaceable>. <literal>IF()</literal>
returns a
- numeric or string value, depending on the context in which it is
- used.
+ <para>
+ If <replaceable>expr1</replaceable> is TRUE
+ (<literal><replaceable>expr1</replaceable> <>
0</literal>
+ and <literal><replaceable>expr1</replaceable> <>
+ NULL</literal>) then <literal>IF()</literal> returns
+ <replaceable>expr2</replaceable>; otherwise it returns
+ <replaceable>expr3</replaceable>. <literal>IF()</literal>
returns
+ a numeric or string value, depending on the context in which it is
+ used.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic IF -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT IF(1>2,2,3);
-> 3
@@ -1502,129 +1580,115 @@
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
</programlisting>
- </para>
+ </para>
- <para>
- If only one of <literal>expr2</literal> or
<literal>expr3</literal>
- is explicitly <literal>NULL</literal>, the result type of the
- <literal>IF()</literal> function is the type of
- non-<literal>NULL</literal> expression.
- </para>
+ <para>
+ If only one of <literal>expr2</literal> or
+ <literal>expr3</literal> is explicitly
<literal>NULL</literal>,
+ the result type of the <literal>IF()</literal> function is the
+ same type as the non-<literal>NULL</literal> expression.
+ </para>
- <para>
- <literal>expr1</literal> is evaluated as an integer value, which
- means that if you are testing floating-point or string values, you
- should do so using a comparison operation.
- </para>
+ <para>
+ <literal>expr1</literal> is evaluated as an integer value, which
+ means that if you are testing floating-point or string values, you
+ should do so using a comparison operation.
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT IF(0.1,1,0);
-> 0
mysql> SELECT IF(0.1<>0,1,0);
-> 1
</programlisting>
- </para>
+ </para>
- <para>
- In the first case shown, <literal>IF(0.1)</literal> returns
- <literal>0</literal> because <literal>0.1</literal> is
converted to
- an integer value, resulting in a test of <literal>IF(0)</literal>.
- This may not be what you expect. In the second case, the comparison
- tests the original floating-point value to see whether it is
- non-zero. The result of the comparison is used as an integer.
- </para>
+ <para>
+ In the first case shown, <literal>IF(0.1)</literal> returns 0
+ because 0.1 is converted to an integer value, resulting in a test
+ of <literal>IF(0)</literal>. This may not be what you expect. In
+ the second case, the comparison tests the original floating-point
+ value to see whether it is non-zero. The result of the comparison
+ is used as an integer.
+ </para>
- <para>
- The default return type of <literal>IF()</literal> (which may matter
- when it is stored into a temporary table) is calculated as follows:
- </para>
+ <para>
+ The default return type of <literal>IF()</literal> (which may
+ matter when it is stored into a temporary table) is calculated as follows:
+ </para>
- <informaltable>
- <tgroup cols="2">
- <colspec colwidth="50*"/>
- <colspec colwidth="20*"/>
- <tbody>
- <row>
- <entry>
- <emphasis role="bold">Expression</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Return Value</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>expr2</literal> or <literal>expr3</literal>
returns
- a string
- </entry>
- <entry>
- string
- </entry>
- </row>
- <row>
- <entry>
- <literal>expr2</literal> or <literal>expr3</literal>
returns
- a floating-point value
- </entry>
- <entry>
- floating-point
- </entry>
- </row>
- <row>
- <entry>
- <literal>expr2</literal> or <literal>expr3</literal>
returns
- an integer
- </entry>
- <entry>
- integer
- </entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="20*"/>
+ <tbody>
+ <row>
+ <entry><emphasis
role="bold">Expression</emphasis></entry>
+ <entry><emphasis role="bold">Return
Value</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>expr2</literal> or
<literal>expr3</literal>
+ returns a string</entry>
+ <entry>string</entry>
+ </row>
+ <row>
+ <entry><literal>expr2</literal> or
<literal>expr3</literal>
+ returns a floating-point value</entry>
+ <entry>floating-point</entry>
+ </row>
+ <row>
+ <entry><literal>expr2</literal> or
<literal>expr3</literal>
+ returns an integer</entry>
+ <entry>integer</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
- <para>
- If <literal>expr2</literal> and <literal>expr3</literal> are
- strings, the result is case sensitive if either string is case
- sensitive.
- </para>
+ <para>
+ If <literal>expr2</literal> and <literal>expr3</literal>
are
+ strings, the result is case sensitive if either string is case
+ sensitive.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic IFNULL -->
- </para>
-
- <para>
- <indexterm type="concept">
- <primary><literal>NULL</literal></primary>
- <secondary>testing for null</secondary>
- </indexterm>
+ </para>
- <indexterm type="function">
- <primary>IFNULL()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="concept">
+ <primary><literal>NULL</literal></primary>
+ <secondary>testing for null</secondary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>IFNULL()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>IFNULL(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>IFNULL(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
+ </para>
- <para>
- If <replaceable>expr1</replaceable> is not
<literal>NULL</literal>,
- <literal>IFNULL()</literal> returns
- <replaceable>expr1</replaceable>, else it returns
- <replaceable>expr2</replaceable>. <literal>IFNULL()</literal>
- returns a numeric or string value, depending on the context in which
- it is used.
+ <para>
+ If <replaceable>expr1</replaceable> is not
+ <literal>NULL</literal>, <literal>IFNULL()</literal>
returns
+ <replaceable>expr1</replaceable>, otherwise it returns
+ <replaceable>expr2</replaceable>.
<literal>IFNULL()</literal>
+ returns a numeric or string value, depending on the context in
+ which it is used.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic IFNULL -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT IFNULL(1,0);
-> 1
@@ -1635,136 +1699,138 @@
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
</programlisting>
- </para>
+ </para>
- <para>
- The default result value of
-
<literal>IFNULL(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
- is the more ``general'' of the two expressions, in the order
- <literal>STRING</literal>, <literal>REAL</literal>, or
- <literal>INTEGER</literal>. Consider the case of a table based on
- expressions or where MySQL must internally store a value returned by
- <literal>IFNULL()</literal> in a temporary table:
- </para>
+ <para>
+ The default result value of
+
<literal>IFNULL(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
+ is the more ``general'' of the two expressions, in the order
+ <literal>STRING</literal>, <literal>REAL</literal>, or
+ <literal>INTEGER</literal>. Consider the case of a table based on
+ expressions or where MySQL must internally store a value returned
+ by <literal>IFNULL()</literal> in a temporary table:
+ </para>
- <para>
+ <para>
<programlisting>
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
</programlisting>
- </para>
+ </para>
- <para>
- In this example, the type of the <literal>test</literal> column is
- <literal>CHAR(4)</literal>.
- </para>
+ <para>
+ In this example, the type of the <literal>test</literal> column is
+ <literal>CHAR(4)</literal>.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic NULLIF -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>NULLIF()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>NULLIF()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>NULLIF(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>NULLIF(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
+ </para>
- <para>
- Returns <literal>NULL</literal> if
- <literal><replaceable>expr1</replaceable> =
- <replaceable>expr2</replaceable></literal> is true, else returns
- <replaceable>expr1</replaceable>. This is the same as <literal>CASE
- WHEN <replaceable>expr1</replaceable> =
- <replaceable>expr2</replaceable> THEN NULL ELSE
- <replaceable>expr1</replaceable> END</literal>.
+ <para>
+ Returns <literal>NULL</literal> if
+ <literal><replaceable>expr1</replaceable> =
+ <replaceable>expr2</replaceable></literal> is true, otherwise
+ returns <replaceable>expr1</replaceable>. This is the same as
+ <literal>CASE WHEN <replaceable>expr1</replaceable> =
+ <replaceable>expr2</replaceable> THEN NULL ELSE
+ <replaceable>expr1</replaceable> END</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic NULLIF -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
</programlisting>
- </para>
+ </para>
- <para>
- Note that MySQL evaluates <literal>expr1</literal> twice if the
- arguments are not equal.
- </para>
- </listitem>
+ <para>
+ Note that MySQL evaluates <literal>expr1</literal> twice if the
+ arguments are not equal.
+ </para>
+ </listitem>
- </itemizedlist>
+</itemizedlist>
- </section>
+</section>
- <section id="string-functions">
+<section id="string-functions">
<title id='title-string-functions'>&title-string-functions;</title>
<!-- TODO: general 4.1 remarks about: lettercase and binary; collations -->
<indexterm type="function">
- <primary>string functions</primary>
+ <primary>string functions</primary>
</indexterm>
<indexterm type="function">
- <primary>functions</primary>
- <secondary>string</secondary>
+ <primary>functions</primary>
+ <secondary>string</secondary>
</indexterm>
<!-- help_category String Functions@Functions -->
<para>
- String-valued functions return <literal>NULL</literal> if the length
- of the result would be greater than the value of the
- <literal>max_allowed_packet</literal> system variable. See
- <xref linkend="server-parameters"/>.
+ String-valued functions return <literal>NULL</literal> if the length
+ of the result would be greater than the value of the
+ <literal>max_allowed_packet</literal> system variable. See
+ <xref linkend="server-parameters"/>.
</para>
<para>
- For functions that operate on string positions, the first position is
- numbered 1.
+ For functions that operate on string positions, the first position
+ is numbered 1.
</para>
<itemizedlist>
<!-- description_for_help_topic ASCII -->
- <indexterm type="function">
- <primary>ASCII()</primary>
- </indexterm>
+ <indexterm type="function">
+ <primary>ASCII()</primary>
+ </indexterm>
- <listitem><para>
- <literal>ASCII(<replaceable>str</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>ASCII(<replaceable>str</replaceable>)</literal>
+ </para>
- <para>
- Returns the numeric value of the leftmost character of the string
- <replaceable>str</replaceable>. Returns <literal>0</literal>
if
- <replaceable>str</replaceable> is the empty string. Returns
- <literal>NULL</literal> if <replaceable>str</replaceable> is
- <literal>NULL</literal>. <literal>ASCII()</literal> works for
- characters with numeric values from <literal>0</literal> to
- <literal>255</literal>.
+ <para>
+ Returns the numeric value of the leftmost character of the
+ string <replaceable>str</replaceable>. Returns 0 if
+ <replaceable>str</replaceable> is the empty string. Returns
+ <literal>NULL</literal> if <replaceable>str</replaceable>
is
+ <literal>NULL</literal>. <literal>ASCII()</literal> works
for
+ characters with numeric values from 0 to 255.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic ASCII -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT ASCII('2');
-> 50
@@ -1773,115 +1839,122 @@
mysql> SELECT ASCII('dx');
-> 100
</programlisting>
- </para>
+ </para>
- <para>
- See also the <literal>ORD()</literal> function.
- </para>
+ <para>
+ See also the <literal>ORD()</literal> function.
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic BIN -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>BIN()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>BIN()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>BIN(<replaceable>N</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>BIN(<replaceable>N</replaceable>)</literal>
+ </para>
- <para>
- Returns a string representation of the binary value of
- <replaceable>N</replaceable>, where
<replaceable>N</replaceable> is
- a longlong (<literal>BIGINT</literal>) number. This is equivalent to
- <literal>CONV(<replaceable>N</replaceable>,10,2)</literal>.
Returns
- <literal>NULL</literal> if <replaceable>N</replaceable> is
- <literal>NULL</literal>.
+ <para>
+ Returns a string representation of the binary value of
+ <replaceable>N</replaceable>, where
<replaceable>N</replaceable>
+ is a longlong (<literal>BIGINT</literal>) number. This is
+ equivalent to
+ <literal>CONV(<replaceable>N</replaceable>,10,2)</literal>.
+ Returns <literal>NULL</literal> if
<replaceable>N</replaceable> is
+ <literal>NULL</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic BIN -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT BIN(12);
-> '1100'
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic BIT_LENGTH -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>BIT_LENGTH()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>BIT_LENGTH()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>BIT_LENGTH(<replaceable>str</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>BIT_LENGTH(<replaceable>str</replaceable>)</literal>
+ </para>
- <para>
- Returns the length of the string <replaceable>str</replaceable> in
- bits.
+ <para>
+ Returns the length of the string <replaceable>str</replaceable> in
+ bits.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic BIT_LENGTH -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT BIT_LENGTH('text');
-> 32
</programlisting>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic CHAR function -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>CHAR()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>CHAR()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
- <literal>CHAR(<replaceable>N</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>CHAR(<replaceable>N</replaceable>,...)</literal>
+ </para>
- <para>
- <literal>CHAR()</literal> interprets the arguments as integers and
- returns a string consisting of the characters given by the code
- values of those integers. <literal>NULL</literal> values are
- skipped.
+ <para>
+ <literal>CHAR()</literal> interprets the arguments as integers and
+ returns a string consisting of the characters given by the code
+ values of those integers. <literal>NULL</literal> values are
+ skipped.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic CHAR function -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
</programlisting>
- </para>
+ </para>
<para>
<!-- description_for_help_topic CHAR_LENGTH -->
@@ -1893,20 +1966,21 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>CHAR_LENGTH(<replaceable>str</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>CHAR_LENGTH(<replaceable>str</replaceable>)</literal>
+ </para>
- <para>
- Returns the length of the string <replaceable>str</replaceable>,
- measured in characters. A multi-byte character counts as a single
- character. This means that for a string containing five two-byte
- characters, <literal>LENGTH()</literal> returns
- <literal>10</literal>, whereas
<literal>CHAR_LENGTH()</literal>
- returns <literal>5</literal>.
+ <para>
+ Returns the length of the string <replaceable>str</replaceable>,
+ measured in characters. A multi-byte character counts as a
+ single character. This means that for a string containing five
+ two-byte characters, <literal>LENGTH()</literal> returns
+ <literal>10</literal>, whereas
<literal>CHAR_LENGTH()</literal>
+ returns <literal>5</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- description_for_help_topic CHARACTER_LENGTH -->
@@ -1923,8 +1997,8 @@
</para>
<para>
- <literal>CHARACTER_LENGTH()</literal> is a synonym for
- <literal>CHAR_LENGTH()</literal>.
+ <literal>CHARACTER_LENGTH()</literal> is a synonym for
+ <literal>CHAR_LENGTH()</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -1939,25 +2013,26 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>COMPRESS(<replaceable>string_to_compress</replaceable>)</literal>
- </para>
-
- <para>
- Compresses a string. This function requires MySQL to have been
- compiled with a compression library such as <literal>zlib</literal>.
- Otherwise, the return value is always <literal>NULL</literal>. The
- compressed string can be uncompressed with
- <literal>UNCOMPRESS()</literal>.
+ <listitem>
+ <para>
+
<literal>COMPRESS(<replaceable>string_to_compress</replaceable>)</literal>
+ </para>
+
+ <para>
+ Compresses a string. This function requires MySQL to have been
+ compiled with a compression library such as
+ <literal>zlib</literal>. Otherwise, the return value is always
+ <literal>NULL</literal>. The compressed string can be
+ uncompressed with <literal>UNCOMPRESS()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic COMPRESS -->
- </para>
+ </para>
- <para>
+ <para>
<programlisting>
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
-> 21
@@ -1968,57 +2043,63 @@
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
-> 15
</programlisting>
- </para>
+ </para>
- <para>
- The compressed string contents are stored the following way:
+ <para>
+ The compressed string contents are stored the following way:
<itemizedlist>
-
- <listitem><para>
- Empty strings are stored as empty strings.
- </para></listitem>
-
- <listitem><para>
- Non-empty strings are stored as a four-byte length of the
- uncompressed string (low byte first), followed by the compressed
- string. If the string ends with space, an extra
- '<literal>.</literal>' character is added to avoid problems with
- endspace trimming should the result be stored in a
- <literal>CHAR</literal> or <literal>VARCHAR</literal>
column. (Use
- of <literal>CHAR</literal> or <literal>VARCHAR</literal> to
store
- compressed strings is not recommended. It is better to use a
- <literal>BLOB</literal> column instead.)
- </para></listitem>
-
+
+ <listitem>
+ <para>
+ Empty strings are stored as empty strings.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Non-empty strings are stored as a four-byte length of the
+ uncompressed string (low byte first), followed by the
+ compressed string. If the string ends with space, an extra
+ '<literal>.</literal>' character is added to avoid problems
+ with endspace trimming should the result be stored in a
+ <literal>CHAR</literal> or <literal>VARCHAR</literal>
column.
+ (Use of <literal>CHAR</literal> or
<literal>VARCHAR</literal>
+ to store compressed strings is not recommended. It is better
+ to use a <literal>BLOB</literal> column instead.)
+ </para>
+ </listitem>
+
</itemizedlist>
- </para>
+ </para>
- <para>
+ <para>
<!-- description_for_help_topic CONCAT -->
- </para>
+ </para>
- <para>
- <indexterm type="function">
- <primary>CONCAT()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>CONCAT()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>CONCAT(<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>CONCAT(<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,...)</literal>
+ </para>
- <para>
- Returns the string that results from concatenating the arguments.
- Returns <literal>NULL</literal> if any argument is
- <literal>NULL</literal>. May have one or more arguments. If all
- arguments are non-binary strings, the result is a non-binary string.
- If the arguments include any binary strings, the result is a binary
- string. A numeric argument is converted to its equivalent binary
- string form.
+ <para>
+ Returns the string that results from concatenating the arguments.
+ Returns <literal>NULL</literal> if any argument is
+ <literal>NULL</literal>. May have one or more arguments. If all
+ arguments are non-binary strings, the result is a non-binary
+ string. If the arguments include any binary strings, the result
+ is a binary string. A numeric argument is converted to its
+ equivalent binary string form.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic CONCAT -->
@@ -2039,32 +2120,34 @@
<!-- description_for_help_topic CONCAT_WS -->
</para>
- <para>
- <indexterm type="function">
- <primary>CONCAT_WS()</primary>
- </indexterm>
- </para></listitem>
+ <para>
+ <indexterm type="function">
+ <primary>CONCAT_WS()</primary>
+ </indexterm>
+ </para>
+ </listitem>
- <listitem><para>
-
<literal>CONCAT_WS(<replaceable>separator</replaceable>,<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>CONCAT_WS(<replaceable>separator</replaceable>,<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,...)</literal>
+ </para>
- <para>
- <literal>CONCAT_WS()</literal> stands for CONCAT With Separator and
- is a special form of <literal>CONCAT()</literal>. The first argument
- is the separator for the rest of the arguments. The separator is
- added between the strings to be concatenated. The separator can be a
- string as can the rest of the arguments. If the separator is
- <literal>NULL</literal>, the result is
<literal>NULL</literal>. The
- function skips any <literal>NULL</literal> values after the
- separator argument.
+ <para>
+ <literal>CONCAT_WS()</literal> stands for CONCAT With Separator
+ and is a special form of <literal>CONCAT()</literal>. The first
+ argument is the separator for the rest of the arguments. The
+ separator is added between the strings to be concatenated. The
+ separator can be a string as can the rest of the arguments. If the
+ separator is <literal>NULL</literal>, the result is
+ <literal>NULL</literal>. The function skips any
+ <literal>NULL</literal> values after the separator argument.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
- <para>
+ <para>
<!-- example_for_help_topic CONCAT_WS -->
- </para>
+ </para>
<para>
<programlisting>
@@ -2075,10 +2158,10 @@
</programlisting>
</para>
- <para>
- In MySQL 5.0, <literal>CONCAT_WS()</literal> does not skip empty
- strings.
- </para>
+ <para>
+ In MySQL 5.0, <literal>CONCAT_WS()</literal> does not skip empty
+ strings. (However, it does skip <literal>NULL</literal>s.)
+ </para>
<para>
<!-- description_for_help_topic CONV -->
@@ -2094,19 +2177,20 @@
<literal>CONV(<replaceable>N</replaceable>,<replaceable>from_base</replaceable>,<replaceable>to_base</replaceable>)</literal>
</para>
- <para>
- Converts numbers between different number bases. Returns a string
- representation of the number <replaceable>N</replaceable>, converted
- from base <replaceable>from_base</replaceable> to base
- <replaceable>to_base</replaceable>. Returns
<literal>NULL</literal>
- if any argument is <literal>NULL</literal>. The argument
- <replaceable>N</replaceable> is interpreted as an integer, but may
- be specified as an integer or a string. The minimum base is
- <literal>2</literal> and the maximum base is
<literal>36</literal>.
- If <replaceable>to_base</replaceable> is a negative number,
- <replaceable>N</replaceable> is regarded as a signed number.
- Otherwise, <replaceable>N</replaceable> is treated as unsigned.
- <literal>CONV()</literal> works with 64-bit precision.
+ <para>
+ Converts numbers between different number bases. Returns a string
+ representation of the number <replaceable>N</replaceable>,
+ converted from base <replaceable>from_base</replaceable> to base
+ <replaceable>to_base</replaceable>. Returns
+ <literal>NULL</literal> if any argument is
+ <literal>NULL</literal>. The argument
<replaceable>N</replaceable>
+ is interpreted as an integer, but may be specified as an integer
+ or a string. The minimum base is <literal>2</literal> and the
+ maximum base is <literal>36</literal>. If
+ <replaceable>to_base</replaceable> is a negative number,
+ <replaceable>N</replaceable> is regarded as a signed number.
+ Otherwise, <replaceable>N</replaceable> is treated as unsigned.
+ <literal>CONV()</literal> works with 64-bit precision.
<!-- end_description_for_help_topic -->
</para>
@@ -2142,14 +2226,14 @@
<literal>ELT(<replaceable>N</replaceable>,<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,<replaceable>str3</replaceable>,...)</literal>
</para>
- <para>
- Returns <replaceable>str1</replaceable> if
- <replaceable>N</replaceable> = <literal>1</literal>,
- <replaceable>str2</replaceable> if
<replaceable>N</replaceable> =
- <literal>2</literal>, and so on. Returns
<literal>NULL</literal> if
- <replaceable>N</replaceable> is less than
<literal>1</literal> or
- greater than the number of arguments. <literal>ELT()</literal> is
- the complement of <literal>FIELD()</literal>.
+ <para>
+ Returns <replaceable>str1</replaceable> if
+ <replaceable>N</replaceable> = <literal>1</literal>,
+ <replaceable>str2</replaceable> if
<replaceable>N</replaceable> =
+ <literal>2</literal>, and so on. Returns
<literal>NULL</literal>
+ if <replaceable>N</replaceable> is less than
<literal>1</literal>
+ or greater than the number of arguments. <literal>ELT()</literal>
+ is the complement of <literal>FIELD()</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -2177,21 +2261,23 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>EXPORT_SET(<replaceable>bits</replaceable>,<replaceable>on</replaceable>,<replaceable>off</replaceable>[,<replaceable>separator</replaceable>[,<replaceable>number_of_bits</replaceable>]])</literal>
- </para>
-
- <para>
- Returns a string in which for every bit set in the value
- <replaceable>bits</replaceable>, you get an
- <replaceable>on</replaceable> string and for every reset bit you get
- an <replaceable>off</replaceable> string. Bits in
- <replaceable>bits</replaceable> are examined from right to left
- (from low-order to high-order bits). Strings are added to the result
- from left to right, separated by the
- <replaceable>separator</replaceable> string (default
- '<literal>,</literal>'). The number of bits examined is given by
- <replaceable>number_of_bits</replaceable> (default 64).
+ <listitem>
+ <para>
+
<literal>EXPORT_SET(<replaceable>bits</replaceable>,<replaceable>on</replaceable>,<replaceable>off</replaceable>[,<replaceable>separator</replaceable>[,<replaceable>number_of_bits</replaceable>]])</literal>
+ </para>
+
+ <para>
+ Returns a string in which for every bit set in the value
+ <replaceable>bits</replaceable>, you get an
+ <replaceable>on</replaceable> string and for every reset bit you
+ get an <replaceable>off</replaceable> string. Bits in
+ <replaceable>bits</replaceable> are examined from right to left
+ (from low-order to high-order bits). Strings are added to the
+ result from left to right, separated by the
+ <replaceable>separator</replaceable> string (the default being the
+ comma character '<literal>,</literal>'). The number of bits
+ examined is given by <replaceable>number_of_bits</replaceable>
+ (defaults to 64).
<!-- end_description_for_help_topic -->
</para>
@@ -2219,33 +2305,34 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>FIELD(<replaceable>str</replaceable>,<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,<replaceable>str3</replaceable>,...)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>FIELD(<replaceable>str</replaceable>,<replaceable>str1</replaceable>,<replaceable>str2</replaceable>,<replaceable>str3</replaceable>,...)</literal>
+ </para>
- <para>
- Returns the index of <replaceable>str</replaceable> in the
- <replaceable>str1</replaceable>,
<replaceable>str2</replaceable>,
- <replaceable>str3</replaceable>, <literal>...</literal> list.
- Returns <literal>0</literal> if
<replaceable>str</replaceable> is
- not found.
- </para>
+ <para>
+ Returns the index of <replaceable>str</replaceable> in the
+ <replaceable>str1</replaceable>,
<replaceable>str2</replaceable>,
+ <replaceable>str3</replaceable>, <literal>...</literal>
list.
+ Returns <literal>0</literal> if
<replaceable>str</replaceable> is
+ not found.
+ </para>
- <para>
- If all arguments to <literal>FIELD()</literal> are strings, all
- arguments are compared as strings. If all arguments are numbers,
- they are compared as numbers. Otherwise, the arguments are compared
- as double.
- </para>
+ <para>
+ If all arguments to <literal>FIELD()</literal> are strings, all
+ arguments are compared as strings. If all arguments are numbers,
+ they are compared as numbers. Otherwise, the arguments are compared
+ as double.
+ </para>
- <para>
- If <replaceable>str</replaceable> is <literal>NULL</literal>,
the
- return value is <literal>0</literal> because
<literal>NULL</literal>
- fails equality comparison with any value. <literal>FIELD()</literal>
- is the complement of <literal>ELT()</literal>.
+ <para>
+ If <replaceable>str</replaceable> is
<literal>NULL</literal>, the
+ return value is <literal>0</literal> because
<literal>NULL</literal>
+ fails equality comparison with any value. <literal>FIELD()</literal>
+ is the complement of <literal>ELT()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic FIELD -->
@@ -2270,29 +2357,30 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>FIND_IN_SET(<replaceable>str</replaceable>,<replaceable>strlist</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>FIND_IN_SET(<replaceable>str</replaceable>,<replaceable>strlist</replaceable>)</literal>
+ </para>
- <para>
- Returns a value <literal>1</literal> to
<replaceable>N</replaceable>
- if the string <replaceable>str</replaceable> is in the string list
- <replaceable>strlist</replaceable> consisting of
- <replaceable>N</replaceable> substrings. A string list is a string
- composed of substrings separated by '<literal>,</literal>'
- characters. If the first argument is a constant string and the
- second is a column of type <literal>SET</literal>, the
- <literal>FIND_IN_SET()</literal> function is optimized to use bit
- arithmetic. Returns <literal>0</literal> if
- <replaceable>str</replaceable> is not in
- <replaceable>strlist</replaceable> or if
- <replaceable>strlist</replaceable> is the empty string. Returns
- <literal>NULL</literal> if either argument is
- <literal>NULL</literal>. This function does not work properly if the
- first argument contains a comma ('<literal>,</literal>') character.
+ <para>
+ Returns a value in the range of 1 to <replaceable>N</replaceable>
+ if the string <replaceable>str</replaceable> is in the string list
+ <replaceable>strlist</replaceable> consisting of
+ <replaceable>N</replaceable> substrings. A string list is a string
+ composed of substrings separated by '<literal>,</literal>'
+ characters. If the first argument is a constant string and the
+ second is a column of type <literal>SET</literal>, the
+ <literal>FIND_IN_SET()</literal> function is optimized to use bit
+ arithmetic. Returns 0 if <replaceable>str</replaceable> is not in
+ <replaceable>strlist</replaceable> or if
+ <replaceable>strlist</replaceable> is the empty string. Returns
+ <literal>NULL</literal> if either argument is
+ <literal>NULL</literal>. This function does not work properly if
+ the first argument contains a comma ('<literal>,</literal>')
+ character.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic FIND_IN_SET -->
@@ -2315,27 +2403,28 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>HEX(<replaceable>N_or_S</replaceable>)</literal>
- </para>
-
- <para>
- If <replaceable>N_OR_S</replaceable> is a number, returns a string
- representation of the hexadecimal value of
- <replaceable>N</replaceable>, where
<replaceable>N</replaceable> is
- a longlong (<literal>BIGINT</literal>) number. This is equivalent to
- <literal>CONV(N,10,16)</literal>.
- </para>
-
- <para>
- If <replaceable>N_OR_S</replaceable> is a string, this function
- returns a hexadecimal string of <replaceable>N_OR_S</replaceable>
- characters, where each character in
- <replaceable>N_OR_S</replaceable> is converted to two hexadecimal
- digits.
+ <listitem>
+ <para>
+ <literal>HEX(<replaceable>N_or_S</replaceable>)</literal>
+ </para>
+
+ <para>
+ If <replaceable>N_OR_S</replaceable> is a number, returns a
+ string representation of the hexadecimal value of
+ <replaceable>N</replaceable>, where
<replaceable>N</replaceable>
+ is a longlong (<literal>BIGINT</literal>) number. This is
+ equivalent to <literal>CONV(N,10,16)</literal>.
+ </para>
+
+ <para>
+ If <replaceable>N_OR_S</replaceable> is a string, this function
+ returns a hexadecimal string of <replaceable>N_OR_S</replaceable>
+ characters, where each character in
+ <replaceable>N_OR_S</replaceable> is converted to two hexadecimal
+ digits.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic HEX -->
@@ -2362,20 +2451,21 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>INSERT(<replaceable>str</replaceable>,<replaceable>pos</replaceable>,<replaceable>len</replaceable>,<replaceable>newstr</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>INSERT(<replaceable>str</replaceable>,<replaceable>pos</replaceable>,<replaceable>len</replaceable>,<replaceable>newstr</replaceable>)</literal>
+ </para>
- <para>
- Returns the string <replaceable>str</replaceable>, with the
- substring beginning at position <replaceable>pos</replaceable> and
- <replaceable>len</replaceable> characters long replaced by the
- string <replaceable>newstr</replaceable>. Returns the original
- string if <replaceable>pos</replaceable> is not within the length of
- the string. Replaces the rest of the string from position
- <replaceable>pos</replaceable> is
<replaceable>len</replaceable> is
- not within the length of the rest of the string. Returns
- <literal>NULL</literal> if any argument is null.
+ <para>
+ Returns the string <replaceable>str</replaceable>, with the
+ substring beginning at position <replaceable>pos</replaceable>
+ and <replaceable>len</replaceable> characters long replaced by the
+ string <replaceable>newstr</replaceable>. Returns the original
+ string if <replaceable>pos</replaceable> is not within the length
+ of the string. Replaces the rest of the string from position
+ <replaceable>pos</replaceable> is
<replaceable>len</replaceable>
+ is not within the length of the rest of the string. Returns
+ <literal>NULL</literal> if any argument is null.
<!-- end_description_for_help_topic -->
</para>
@@ -2395,9 +2485,9 @@
</programlisting>
</para>
- <para>
+ <para>
This function is multi-byte safe.
- </para>
+ </para>
<para>
<!-- description_for_help_topic INSTR -->
@@ -2409,19 +2499,20 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>INSTR(<replaceable>str</replaceable>,<replaceable>substr</replaceable>)</literal>
- </para>
-
- <para>
- Returns the position of the first occurrence of substring
- <replaceable>substr</replaceable> in string
- <replaceable>str</replaceable>. This is the same as the two-argument
- form of <literal>LOCATE()</literal>, except that the arguments are
- swapped.
+ <listitem>
+ <para>
+
<literal>INSTR(<replaceable>str</replaceable>,<replaceable>substr</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the position of the first occurrence of substring
+ <replaceable>substr</replaceable> in string
+ <replaceable>str</replaceable>. This is the same as the
+ two-argument form of <literal>LOCATE()</literal>, except that the
+ order of the arguments is reversed.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic INSTR -->
@@ -2436,10 +2527,10 @@
</programlisting>
</para>
- <para>
- This function is multi-byte safe. In MySQL 5.0, it is case sensitive
- only if either argument is a binary string.
- </para>
+ <para>
+ This function is multi-byte safe. In MySQL 5.0, it is case
+ sensitive only if at least one argument is a binary string.
+ </para>
<para>
<!-- description_for_help_topic LCASE -->
@@ -2451,16 +2542,17 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>LCASE(<replaceable>str</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>LCASE(<replaceable>str</replaceable>)</literal>
+ </para>
- <para>
- <literal>LCASE()</literal> is a synonym for
- <literal>LOWER()</literal>.
+ <para>
+ <literal>LCASE()</literal> is a synonym for
+ <literal>LOWER()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- description_for_help_topic LEFT -->
@@ -2472,16 +2564,17 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>LEFT(<replaceable>str</replaceable>,<replaceable>len</replaceable>)</literal>
- </para>
-
- <para>
- Returns the leftmost <replaceable>len</replaceable> characters from
- the string <replaceable>str</replaceable>.
+ <listitem>
+ <para>
+
<literal>LEFT(<replaceable>str</replaceable>,<replaceable>len</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the leftmost <replaceable>len</replaceable> characters
+ from the string <replaceable>str</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic LEFT -->
@@ -2504,19 +2597,20 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>LENGTH(<replaceable>str</replaceable>)</literal>
- </para>
-
- <para>
- Returns the length of the string <replaceable>str</replaceable>,
- measured in bytes. A multi-byte character counts as multiple bytes.
- This means that for a string containing five two-byte characters,
- <literal>LENGTH()</literal> returns <literal>10</literal>,
whereas
- <literal>CHAR_LENGTH()</literal> returns
<literal>5</literal>.
+ <listitem>
+ <para>
+ <literal>LENGTH(<replaceable>str</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the length of the string <replaceable>str</replaceable>,
+ measured in bytes. A multi-byte character counts as multiple
+ bytes. This means that for a string containing five two-byte
+ characters, <literal>LENGTH()</literal> returns 10, whereas
+ <literal>CHAR_LENGTH()</literal> returns 5.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic LENGTH -->
@@ -2543,25 +2637,26 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>LOAD_FILE(<replaceable>file_name</replaceable>)</literal>
- </para>
-
- <para>
- Reads the file and returns the file contents as a string. The file
- must be located on the server, you must specify the full pathname to
- the file, and you must have the <literal>FILE</literal> privilege.
- The file must be readable by all and be smaller than
- <literal>max_allowed_packet</literal> bytes.
- </para>
+ <listitem>
+ <para>
+
<literal>LOAD_FILE(<replaceable>file_name</replaceable>)</literal>
+ </para>
+
+ <para>
+ Reads the file and returns the file contents as a string. The file
+ must be located on the server, you must specify the full pathname
+ to the file, and you must have the <literal>FILE</literal>
+ privilege. The file must be readable by all and its size less than
+ <literal>max_allowed_packet</literal> bytes.
+ </para>
- <para>
- If the file doesn't exist or cannot be read because one of the
- preceding conditions is not satisfied, the function returns
- <literal>NULL</literal>.
+ <para>
+ If the file doesn't exist or cannot be read because one of the
+ preceding conditions is not satisfied, the function returns
+ <literal>NULL</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic LOAD_FILE -->
@@ -2609,7 +2704,7 @@
position of the first occurrence of substring
<replaceable>substr</replaceable> in string
<replaceable>str</replaceable>, starting at position
- <replaceable>pos</replaceable>. Returns <literal>0</literal>
if
+ <replaceable>pos</replaceable>. Returns 0 if
<replaceable>substr</replaceable> is not in
<replaceable>str</replaceable>.
@@ -2828,14 +2923,14 @@
</para>
<para>
- Returns a string representation of the octal value of
- <replaceable>N</replaceable>, where
<replaceable>N</replaceable> is
- a longlong (<literal>BIGINT</literal>)number. This is equivalent to
- <literal>CONV(N,10,8)</literal>. Returns
<literal>NULL</literal> if
- <replaceable>N</replaceable> is <literal>NULL</literal>.
+ Returns a string representation of the octal value of
+ <replaceable>N</replaceable>, where
<replaceable>N</replaceable> is
+ a longlong (<literal>BIGINT</literal>)number. This is equivalent to
+ <literal>CONV(N,10,8)</literal>. Returns
<literal>NULL</literal> if
+ <replaceable>N</replaceable> is <literal>NULL</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic OCT -->
@@ -3223,9 +3318,9 @@
strings. A standard soundex string is four characters long, but the
<literal>SOUNDEX()</literal> function returns an arbitrarily long
string. You can use <literal>SUBSTRING()</literal> on the result to
- get a standard soundex string. All non-alphabetic characters are
- ignored in the given string. All international alphabetic characters
- outside the A-Z range are treated as vowels.
+ get a standard soundex string. All non-alphabetic characters in
+ <replaceable>str</replaceable> are ignored. All international
+ alphabetic characters outside the A-Z range are treated as vowels.
<!-- end_description_for_help_topic -->
</para>
@@ -3574,7 +3669,7 @@
</para>
<para>
- Does the opposite of
+ Performs the opposite operation from
<literal>HEX(<replaceable>str</replaceable>)</literal>. That
is, it
interprets each pair of hexadecimal digits in the argument as a
number and converts it to the character represented by the number.
@@ -3610,14 +3705,15 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>UPPER(<replaceable>str</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>UPPER(<replaceable>str</replaceable>)</literal>
+ </para>
<para>
- Returns the string <replaceable>str</replaceable> with all
- characters changed to uppercase according to the current character
- set mapping (the default is ISO-8859-1 Latin1).
+ Returns the string <replaceable>str</replaceable> with all
+ characters changed to uppercase according to the current character
+ set mapping (the default is ISO-8859-1 Latin1).
<!-- end_description_for_help_topic -->
</para>
@@ -3634,8 +3730,9 @@
</para>
<para>
- This function is multi-byte safe.
- </para></listitem>
+ This function is multi-byte safe.
+ </para>
+ </listitem>
</itemizedlist>
@@ -3666,7 +3763,7 @@
<para>
If you want to convert a number to a string explicitly, use the
- <literal>CAST()</literal>:
+ <literal>CAST()</literal> function:
</para>
<programlisting>
@@ -3676,9 +3773,10 @@
<para>
- 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 affects only comparisons.
+ 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 (that is, it is case
+ sensitive in comparisons). This affects only comparisons.
</para>
<indexterm type="concept">
@@ -3692,8 +3790,8 @@
</indexterm>
<para>
- Normally, if any expression in a string comparison is case
- sensitive, the comparison is performed in case-sensitive fashion.
+ Normally, if any expression in a string comparison is case
+ sensitive, the comparison is performed in case-sensitive fashion.
</para>
<itemizedlist>
@@ -3704,62 +3802,53 @@
<primary>LIKE</primary>
</indexterm>
- <listitem><para>
- <literal><replaceable>expr</replaceable> LIKE
- <replaceable>pat</replaceable> [ESCAPE
- '<replaceable>escape-char</replaceable>']</literal>
- </para>
-
- <para>
- Pattern matching using SQL simple regular expression comparison.
- Returns <literal>1</literal> (TRUE) or <literal>0</literal>
- (FALSE). If either <replaceable>expr</replaceable> or
- <replaceable>pat</replaceable> is <literal>NULL</literal>,
the
- result is <literal>NULL</literal>.
- </para>
-
- <para>
- The pattern need not be a literal string. For example, it can be
- specified as a string expression or table column.
- </para>
-
- <para>
- With <literal>LIKE</literal> you can use the following two wildcard
- characters in the pattern:
- </para>
+ <listitem>
+ <para>
+ <literal><replaceable>expr</replaceable> LIKE
+ <replaceable>pat</replaceable> [ESCAPE
+ '<replaceable>escape-char</replaceable>']</literal>
+ </para>
+
+ <para>
+ Pattern matching using SQL simple regular expression comparison.
+ Returns 1 (<literal>TRUE</literal>) or 0
+ (<literal>FALSE</literal>). If either
+ <replaceable>expr</replaceable> or
+ <replaceable>pat</replaceable> is
<literal>NULL</literal>, the
+ result is also <literal>NULL</literal>.
+ </para>
+
+ <para>
+ The pattern need not be a literal string. For example, it can be
+ specified as a string expression or table column.
+ </para>
+
+ <para>
+ With <literal>LIKE</literal> you can use the following two
+ wildcard characters in the pattern:
+ </para>
- <informaltable>
- <tgroup cols="2">
- <colspec colwidth="20*"/>
- <colspec colwidth="80*"/>
- <tbody>
- <row>
- <entry>
- <emphasis role="bold">Character</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Description</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%</literal>
- </entry>
- <entry>
- Matches any number of characters, even zero characters
- </entry>
- </row>
- <row>
- <entry>
- <literal>_</literal>
- </entry>
- <entry>
- Matches exactly one character
- </entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="20*"/>
+ <colspec colwidth="80*"/>
+ <tbody>
+ <row>
+ <entry><emphasis
role="bold">Character</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>%</literal></entry>
+ <entry>Matches any number of characters, even zero
+ characters</entry>
+ </row>
+ <row>
+ <entry><literal>_</literal></entry>
+ <entry>Matches exactly one character</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
<para>
<!-- end_description_for_help_topic -->
@@ -3779,10 +3868,10 @@
</para>
<para>
- To test for literal instances of a wildcard character, precede the
- character with the escape character. If you don't specify the
- <literal>ESCAPE</literal> character, '<literal>\</literal>'
is
- assumed.
+ To test for literal instances of a wildcard character, precede the
+ character with the escape character. If you don't specify the
+ <literal>ESCAPE</literal> character, '<literal>\</literal>'
is
+ assumed.
</para>
<informaltable>
@@ -3791,28 +3880,16 @@
<colspec colwidth="80*"/>
<tbody>
<row>
- <entry>
- <emphasis role="bold">String</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Description</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>\%</literal>
- </entry>
- <entry>
- Matches one '<literal>%</literal>' character
- </entry>
- </row>
- <row>
- <entry>
- <literal>\_</literal>
- </entry>
- <entry>
- Matches one '<literal>_</literal>' character
- </entry>
+ <entry><emphasis
role="bold">String</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>\%</literal></entry>
+ <entry>Matches one '<literal>%</literal>'
character</entry>
+ </row>
+ <row>
+ <entry><literal>\_</literal></entry>
+ <entry>Matches one '<literal>_</literal>'
character</entry>
</row>
</tbody>
</tgroup>
@@ -3828,8 +3905,8 @@
</para>
<para>
- To specify a different escape character, use the
- <literal>ESCAPE</literal> clause:
+ To specify a different escape character, use the
+ <literal>ESCAPE</literal> clause:
</para>
<para>
@@ -3840,8 +3917,9 @@
</para>
<para>
- The following two statements illustrate that string comparisons are
- not case sensitive unless one of the operands is a binary string:
+ The following two statements illustrate that string comparisons
+ are not case sensitive unless one of the operands is a binary
+ string:
</para>
<para>
@@ -3854,9 +3932,9 @@
</para>
<para>
- In MySQL, <literal>LIKE</literal> is allowed on numeric
- expressions. (This is an extension to the standard SQL
- <literal>LIKE</literal>.)
+ In MySQL, <literal>LIKE</literal> is allowed on numeric
+ expressions. (This is an extension to the standard SQL
+ <literal>LIKE</literal>.)
</para>
<para>
@@ -3867,15 +3945,16 @@
</para>
<para>
- Note: Because MySQL uses the C escape syntax in strings (for
- example, '<literal>\n</literal>' to represent newline), you must
- double any '<literal>\</literal>' that you use in your
- <literal>LIKE</literal> strings. For example, to search for
- '<literal>\n</literal>', specify it as
'<literal>\\n</literal>'. To
- search for '<literal>\</literal>', specify it as
- '<literal>\\\\</literal>' (the backslashes are stripped once by the
- parser and another time when the pattern match is done, leaving a
- single backslash to be matched).
+ <emphasis role="bold">Note</emphasis>: Because MySQL uses C escape
+ syntax in strings (for example, '<literal>\n</literal>' to
+ represent a newline character), you must double any
+ '<literal>\</literal>' that you use in
<literal>LIKE</literal>
+ strings. For example, to search for '<literal>\n</literal>',
+ specify it as '<literal>\\n</literal>'. To search for
+ '<literal>\</literal>', specify it as
'<literal>\\\\</literal>';
+ this is because the backslashes are stripped once by the parser
+ and again when the pattern match is made, leaving a single
+ backslash to be matched against.
</para>
<para>
@@ -3895,9 +3974,9 @@
</para>
<para>
- This is the same as <literal>NOT (<replaceable>expr</replaceable>
- LIKE <replaceable>pat</replaceable> [ESCAPE
- '<replaceable>escape-char</replaceable>'])</literal>.
+ This is the same as <literal>NOT (<replaceable>expr</replaceable>
+ LIKE <replaceable>pat</replaceable> [ESCAPE
+ '<replaceable>escape-char</replaceable>'])</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -3920,8 +3999,8 @@
</para>
<para>
- This is the same as <literal>NOT (<replaceable>expr</replaceable>
- REGEXP <replaceable>pat</replaceable>)</literal>.
+ This is the same as <literal>NOT (<replaceable>expr</replaceable>
+ REGEXP <replaceable>pat</replaceable>)</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -3955,34 +4034,35 @@
</para>
<para>
- Performs a pattern match of a string expression
- <replaceable>expr</replaceable> against a pattern
- <replaceable>pat</replaceable>. The pattern can be an extended
- regular expression. The syntax for regular expressions is discussed
- in <xref linkend="regexp"/>. Returns <literal>1</literal> if
- <replaceable>expr</replaceable> matches
- <replaceable>pat</replaceable>, otherwise returns
- <literal>0</literal>. If either
<replaceable>expr</replaceable> or
- <replaceable>pat</replaceable> is <literal>NULL</literal>,
the
- result is <literal>NULL</literal>. <literal>RLIKE</literal>
is a
- synonym for <literal>REGEXP</literal>, provided for
- <literal>mSQL</literal> compatibility.
+ Performs a pattern match of a string expression
+ <replaceable>expr</replaceable> against a pattern
+ <replaceable>pat</replaceable>. The pattern can be an extended
+ regular expression. The syntax for regular expressions is
+ discussed in <xref linkend="regexp"/>. Returns 1 if
+ <replaceable>expr</replaceable> matches
+ <replaceable>pat</replaceable>, otherwise returns 0. If either
+ <replaceable>expr</replaceable> or
+ <replaceable>pat</replaceable> is <literal>NULL</literal>,
the
+ result is also <literal>NULL</literal>.
<literal>RLIKE</literal>
+ is a synonym for <literal>REGEXP</literal>, provided for
+ compatibility with <literal>mSQL</literal>.
</para>
<para>
- The pattern need not be a literal string. For example, it can be
- specified as a string expression or table column.
+ The pattern need not be a literal string. For example, it can be
+ specified as a string expression or table column.
</para>
<para>
- Note: Because MySQL uses the C escape syntax in strings (for
- example, '<literal>\n</literal>' to represent newline), you must
- double any '<literal>\</literal>' that you use in your
- <literal>REGEXP</literal> strings.
+ Note: Because MySQL uses the C escape syntax in strings (for
+ example, '<literal>\n</literal>' to represent the newline
+ character), you must double any '<literal>\</literal>' that you
+ use in your <literal>REGEXP</literal> strings.
</para>
<para>
- <literal>REGEXP</literal> is not case sensitive for normal (not binary)
strings.
+ <literal>REGEXP</literal> is not case sensitive, except when used
+ with binary strings.
<!-- end_description_for_help_topic -->
</para>
@@ -4007,10 +4087,10 @@
</para>
<para>
- <literal>REGEXP</literal> and <literal>RLIKE</literal> use
the
- current character set (ISO-8859-1 Latin1 by default) when deciding
- the type of a character. However, these operators are not
- multi-byte safe.
+ <literal>REGEXP</literal> and <literal>RLIKE</literal> use
the
+ current character set (ISO-8859-1 Latin1 by default) when deciding
+ the type of a character. <emphasis role="bold">Warning</emphasis>:
+ These operators are not multi-byte safe.
</para>
<para>
@@ -4023,15 +4103,15 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>STRCMP(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>STRCMP(<replaceable>expr1</replaceable>,<replaceable>expr2</replaceable>)</literal>
+ </para>
<para>
- <literal>STRCMP()</literal> returns <literal>0</literal> if
the
- strings are the same, <literal>-1</literal> if the first argument
- is smaller than the second according to the current sort order, and
- <literal>1</literal> otherwise.
+ <literal>STRCMP()</literal> returns 0 if the strings are
+ identical, -1 if the first argument is smaller than the second
+ according to the current sort order, and 1 otherwise.
<!-- end_description_for_help_topic -->
</para>
@@ -4052,11 +4132,12 @@
</para>
<para>
- In MySQL 5.0, <literal>STRCMP()</literal> uses the current
- character set when performing comparisons. This makes the default
- comparison behavior case insensitive unless one or both of the
- operands are binary strings.
- </para></listitem>
+ In MySQL 5.0, <literal>STRCMP()</literal> uses the current
+ character set when performing comparisons. This makes the default
+ comparison behavior case insensitive unless one or both of the
+ operands are binary strings.
+ </para>
+ </listitem>
</itemizedlist>
@@ -4080,13 +4161,13 @@
</indexterm>
<para>
- The usual arithmetic operators are available. Note that in the case
- of <literal>-</literal>, <literal>+</literal>, and
- <literal>*</literal>, the result is calculated with
- <literal>BIGINT</literal> (64-bit) precision if both arguments are
- integers. If one of the arguments is an unsigned integer, and the
- other argument is also an integer, the result is an unsigned
- integer. See <xref linkend="cast-functions"/>.
+ The usual arithmetic operators are available. Note that in the case
+ of <literal>-</literal>, <literal>+</literal>, and
+ <literal>*</literal>, the result is calculated with
+ <literal>BIGINT</literal> (64-bit) precision if both arguments are
+ integers. If one of the arguments is an unsigned integer, and the
+ other argument is also an integer, the result is an unsigned
+ integer. See <xref linkend="cast-functions"/>.
</para>
<indexterm type="concept">
@@ -4115,7 +4196,7 @@
</para>
<para>
- Addition:
+ Addition:
<!-- end_description_for_help_topic -->
</para>
@@ -4145,15 +4226,16 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>-</literal>
- </para>
-
- <para>
- Subtraction:
+ <listitem>
+ <para>
+ <literal>-</literal>
+ </para>
+
+ <para>
+ Subtraction:
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic - binary -->
@@ -4185,15 +4267,16 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>-</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>-</literal>
+ </para>
- <para>
- Unary minus. Changes the sign of the argument.
+ <para>
+ Unary minus. Changes the sign of the argument.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic - unary -->
@@ -4207,11 +4290,11 @@
</para>
<para>
- Note that if this operator is used with a
- <literal>BIGINT</literal>, the return value is a
- <literal>BIGINT</literal>! This means that you should avoid using
- <literal>-</literal> on integers that may have the value of
- <literal>-2^63</literal>!
+ <emphasis role="bold">Note</emphasis>: If this operator is used
+ with a <literal>BIGINT</literal>, the return value is also a
+ <literal>BIGINT</literal>. This means that you should avoid using
+ <literal>-</literal> on integers that may be equal to -2^63 or
+ less.
</para>
<para>
@@ -4228,12 +4311,13 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>*</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>*</literal>
+ </para>
- <para>
- Multiplication:
+ <para>
+ Multiplication:
<!-- end_description_for_help_topic -->
</para>
@@ -4254,9 +4338,10 @@
</para>
<para>
- The result of the last expression is incorrect because the result
- of the integer multiplication exceeds the 64-bit range of
- <literal>BIGINT</literal> calculations.
+ The result of the last expression is incorrect because the result
+ of the integer multiplication exceeds the 64-bit range of
+ <literal>BIGINT</literal> calculations. (See
+ <xref linkend="numeric-types"/>.)
</para>
<para>
@@ -4273,15 +4358,16 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>/</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>/</literal>
+ </para>
- <para>
- Division:
+ <para>
+ Division:
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic / -->
@@ -4295,7 +4381,7 @@
</para>
<para>
- Division by zero produces a <literal>NULL</literal> result:
+ Division by zero produces a <literal>NULL</literal> result:
</para>
<para>
@@ -4306,9 +4392,9 @@
</para>
<para>
- A division is calculated with <literal>BIGINT</literal> arithmetic
- only if performed in a context where its result is converted to an
- integer.
+ A division is calculated with <literal>BIGINT</literal> arithmetic
+ only if performed in a context where its result is converted to an
+ integer.
</para>
<para>
@@ -4317,18 +4403,19 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>DIV</literal>
+ <listitem>
+ <para>
+ <literal>DIV</literal>
<!-- description_for_help_topic DIV FLOOR BIGINT -->
- </para>
+ </para>
- <para>
- Integer division. Similar to <literal>FLOOR()</literal> but safe
- with <literal>BIGINT</literal> values.
+ <para>
+ Integer division. Similar to <literal>FLOOR()</literal> but safe
+ with <literal>BIGINT</literal> values.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic DIV -->
@@ -4351,8 +4438,8 @@
<title
id='title-mathematical-functions'>&title-mathematical-functions;</title>
<para>
- All mathematical functions return <literal>NULL</literal> in case of
- an error.
+ All mathematical functions return <literal>NULL</literal> in the
+ event of an error.
</para>
<indexterm type="function">
@@ -4372,15 +4459,16 @@
<primary>ABS()</primary>
</indexterm>
- <listitem><para>
- <literal>ABS(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>ABS(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the absolute value of <replaceable>X</replaceable>.
+ <para>
+ Returns the absolute value of <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic ABS -->
@@ -4396,7 +4484,7 @@
</para>
<para>
- This function is safe to use with <literal>BIGINT</literal> values.
+ This function is safe to use with <literal>BIGINT</literal> values.
</para>
<para>
@@ -4409,31 +4497,32 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>ACOS(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>ACOS(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the arc cosine of <replaceable>X</replaceable>, that is,
- the value whose cosine is <replaceable>X</replaceable>. Returns
- <literal>NULL</literal> if <replaceable>X</replaceable> is
not in
- the range <literal>-1</literal> to <literal>1</literal>.
+ <para>
+ Returns the arccosine of <replaceable>X</replaceable>, that is,
+ the value whose cosine is <replaceable>X</replaceable>. Returns
+ <literal>NULL</literal> if <replaceable>X</replaceable>
is not
+ in the range -1 to 1.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic ACOS -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT ACOS(1);
- -> 0.000000
+ -> 0
mysql> SELECT ACOS(1.0001);
-> NULL
mysql> SELECT ACOS(0);
- -> 1.570796
+ -> 1.5707963267949
</programlisting>
</para>
@@ -4447,29 +4536,30 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>ASIN(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>ASIN(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the arc sine of <replaceable>X</replaceable>, that is, the
- value whose sine is <replaceable>X</replaceable>. Returns
- <literal>NULL</literal> if <replaceable>X</replaceable> is
not in
- the range <literal>-1</literal> to <literal>1</literal>.
+ <para>
+ Returns the arcsine of <replaceable>X</replaceable>, that is,
+ the value whose sine is <replaceable>X</replaceable>. Returns
+ <literal>NULL</literal> if <replaceable>X</replaceable>
is not
+ in the range -1 to 1.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic ASIN -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT ASIN(0.2);
- -> 0.201358
+ -> 0.20135792079033
mysql> SELECT ASIN('foo');
- -> 0.000000
+ -> 0
</programlisting>
</para>
@@ -4483,27 +4573,28 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>ATAN(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>ATAN(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the arc tangent of <replaceable>X</replaceable>, that is,
- the value whose tangent is <replaceable>X</replaceable>.
+ <para>
+ Returns the arctangent of <replaceable>X</replaceable>, that is,
+ the value whose tangent is <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic ATAN -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT ATAN(2);
- -> 1.107149
+ -> 1.1071487177941
mysql> SELECT ATAN(-2);
- -> -1.107149
+ -> -1.1071487177941
</programlisting>
</para>
@@ -4517,33 +4608,33 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>ATAN(<replaceable>Y</replaceable>,<replaceable>X</replaceable>)</literal>
- ,
-
<literal>ATAN2(<replaceable>Y</replaceable>,<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>ATAN(<replaceable>Y</replaceable>,<replaceable>X</replaceable>)</literal>
,
+
<literal>ATAN2(<replaceable>Y</replaceable>,<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the arc tangent of the two variables
- <replaceable>X</replaceable> and
<replaceable>Y</replaceable>. It
- is similar to calculating the arc tangent of
- <literal><replaceable>Y</replaceable> /
- <replaceable>X</replaceable></literal>, except that the signs of
- both arguments are used to determine the quadrant of the result.
+ <para>
+ Returns the arctangent of the two variables
+ <replaceable>X</replaceable> and
<replaceable>Y</replaceable>.
+ It is similar to calculating the arctangent of
+ <literal><replaceable>Y</replaceable> /
+ <replaceable>X</replaceable></literal>, except that the signs
of
+ both arguments are used to determine the quadrant of the result.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic ATAN2 -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT ATAN(-2,2);
- -> -0.785398
+ -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
- -> 1.570796
+ -> 1.5707963267949
</programlisting>
</para>
@@ -4557,17 +4648,18 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>CEILING(<replaceable>X</replaceable>)</literal> ,
- <literal>CEIL(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>CEILING(<replaceable>X</replaceable>)</literal>
,
+ <literal>CEIL(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the smallest integer value not less than
- <replaceable>X</replaceable>.
+ <para>
+ Returns the smallest integer value not less than
+ <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic CEILING -->
@@ -4583,8 +4675,8 @@
</para>
<para>
- These two funcitons are synonymous. Note that the return value is
- converted to a <literal>BIGINT</literal>.
+ These two functions are synonymous. Note that the return value is
+ converted to a <literal>BIGINT</literal>.
</para>
<para>
@@ -4597,25 +4689,26 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>COS(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>COS(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the cosine of <replaceable>X</replaceable>, where
- <replaceable>X</replaceable> is given in radians.
+ <para>
+ Returns the cosine of <replaceable>X</replaceable>, where
+ <replaceable>X</replaceable> is given in radians.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic COS -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT COS(PI());
- -> -1.000000
+ -> -1
</programlisting>
</para>
@@ -4629,24 +4722,25 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>COT(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>COT(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the cotangent of <replaceable>X</replaceable>.
+ <para>
+ Returns the cotangent of <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic COT -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT COT(12);
- -> -1.57267341
+ -> -1.5726734063977
mysql> SELECT COT(0);
-> NULL
</programlisting>
@@ -4662,18 +4756,19 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>CRC32(<replaceable>expr</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>CRC32(<replaceable>expr</replaceable>)</literal>
+ </para>
- <para>
- Computes a cyclic redundancy check value and returns a 32-bit
- unsigned value. The result is <literal>NULL</literal> if the
- argument is <literal>NULL</literal>. The argument is expected be a
- string and is treated as one if it is not.
+ <para>
+ Computes a cyclic redundancy check value and returns a 32-bit
+ unsigned value. The result is <literal>NULL</literal> if the
+ argument is <literal>NULL</literal>. The argument is expected to
+ be a string and (if possible) is treated as one if it is not.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic CRC32 -->
@@ -4683,6 +4778,8 @@
<programlisting>
mysql> SELECT CRC32('MySQL');
-> 3259397556
+mysql> SELECT CRC32('mysql');
+ -> 2501908538
</programlisting>
</para>
@@ -4696,25 +4793,28 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>DEGREES(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>DEGREES(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the argument <replaceable>X</replaceable>, converted from
- radians to degrees.
+ <para>
+ Returns the argument <replaceable>X</replaceable>, converted
+ from radians to degrees.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic DEGREES -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT DEGREES(PI());
- -> 180.000000
+ -> 180
+mysql> SELECT DEGREES(PI() / 2);
+ -> 90
</programlisting>
</para>
@@ -4728,27 +4828,28 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>EXP(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>EXP(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the value of <literal>e</literal> (the base of natural
- logarithms) raised to the power of <replaceable>X</replaceable>.
+ <para>
+ Returns the value of <literal>e</literal> (the base of natural
+ logarithms) raised to the power of <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic EXP -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT EXP(2);
- -> 7.389056
+ -> 7.3890560989307
mysql> SELECT EXP(-2);
- -> 0.135335
+ -> 0.13533528323661
</programlisting>
</para>
@@ -4762,16 +4863,17 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>FLOOR(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>FLOOR(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the largest integer value not greater than
- <replaceable>X</replaceable>.
+ <para>
+ Returns the largest integer value not greater than
+ <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic FLOOR -->
@@ -4787,8 +4889,8 @@
</para>
<para>
- Note that the return value is converted to a
- <literal>BIGINT</literal>.
+ Note that the return value is converted to a
+ <literal>BIGINT</literal>.
</para>
<para>
@@ -4801,15 +4903,18 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>LN(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>LN(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the natural logarithm of <replaceable>X</replaceable>.
+ <para>
+ Returns the natural logarithm of <replaceable>X</replaceable>,
+ that is, the logarithm of <replaceable>X</replaceable> to the
+ base <replaceable>e</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic LN -->
@@ -4818,15 +4923,15 @@
<para>
<programlisting>
mysql> SELECT LN(2);
- -> 0.693147
+ -> 0.69314718055995
mysql> SELECT LN(-2);
-> NULL
</programlisting>
</para>
<para>
- This function is synonymous with
- <literal>LOG(<replaceable>X</replaceable>)</literal>.
+ This function is synonymous with
+ <literal>LOG(<replaceable>X</replaceable>)</literal>.
</para>
<para>
@@ -4845,8 +4950,8 @@
</para>
<para>
- If called with one parameter, this function returns the natural
- logarithm of <replaceable>X</replaceable>.
+ If called with one parameter, this function returns the natural
+ logarithm of <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -4858,29 +4963,29 @@
<para>
<programlisting>
mysql> SELECT LOG(2);
- -> 0.693147
+ -> 0.69314718055995
mysql> SELECT LOG(-2);
-> NULL
</programlisting>
</para>
<para>
- If called with two parameters, this function returns the logarithm
- of <replaceable>X</replaceable> for an arbitrary base
- <replaceable>B</replaceable>.
+ If called with two parameters, this function returns the logarithm
+ of <replaceable>X</replaceable> for an arbitrary base
+ <replaceable>B</replaceable>.
<programlisting>
mysql> SELECT LOG(2,65536);
- -> 16.000000
-mysql> SELECT LOG(1,100);
- -> NULL
+ -> 16
+mysql> SELECT LOG(10,100);
+ -> 2
</programlisting>
</para>
<para>
-
<literal>LOG(<replaceable>B</replaceable>,<replaceable>X</replaceable>)</literal>
- is equivalent to
-
<literal>LOG(<replaceable>X</replaceable>)/LOG(<replaceable>B</replaceable>)</literal>.
+
<literal>LOG(<replaceable>B</replaceable>,<replaceable>X</replaceable>)</literal>
+ is equivalent to
+ <literal>LOG(<replaceable>X</replaceable>) /
LOG(<replaceable>B</replaceable>)</literal>.
</para>
<para>
@@ -4893,13 +4998,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>LOG2(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>LOG2(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the base-2 logarithm of
- <literal><replaceable>X</replaceable></literal>.
+ <para>
+ Returns the base-2 logarithm of
+ <literal><replaceable>X</replaceable></literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -4911,17 +5017,17 @@
<para>
<programlisting>
mysql> SELECT LOG2(65536);
- -> 16.000000
+ -> 16
mysql> SELECT LOG2(-100);
-> NULL
</programlisting>
</para>
<para>
- <literal>LOG2()</literal> is useful for finding out how many bits a
- number would require for storage. This function is equivalent to
- the expression
- <literal>LOG(<replaceable>X</replaceable>)/LOG(2)</literal>.
+ <literal>LOG2()</literal> is useful for finding out how many bits
+ a number requires for storage. This function is equivalent to the
+ expression <literal>LOG(<replaceable>X</replaceable>) /
+ LOG(2)</literal>.
</para>
<para>
@@ -4934,12 +5040,13 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>LOG10(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>LOG10(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the base-10 logarithm of <replaceable>X</replaceable>.
+ <para>
+ Returns the base-10 logarithm of <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -4947,17 +5054,23 @@
<para>
<!-- example_for_help_topic LOG10 -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT LOG10(2);
- -> 0.301030
+ -> 0.30102999566398
mysql> SELECT LOG10(100);
- -> 2.000000
+ -> 2
mysql> SELECT LOG10(-100);
-> NULL
</programlisting>
</para>
+
+ <para>
+ <literal>LOG10(<replaceable>X</replaceable>)</literal>
+ is equivalent to
+ <literal>LOG(10,<replaceable>X</replaceable>)</literal>.
+ </para>
<para>
<!-- description_for_help_topic % MOD -->
@@ -4985,21 +5098,22 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>MOD(<replaceable>N</replaceable>,<replaceable>M</replaceable>)</literal>
- , <literal><replaceable>N</replaceable> %
- <replaceable>M</replaceable></literal> ,
- <literal><replaceable>N</replaceable> MOD
- <replaceable>M</replaceable></literal>
- </para>
+ <listitem>
+ <para>
+
<literal>MOD(<replaceable>N</replaceable>,<replaceable>M</replaceable>)</literal>
+ , <literal><replaceable>N</replaceable> %
+ <replaceable>M</replaceable></literal> ,
+ <literal><replaceable>N</replaceable> MOD
+ <replaceable>M</replaceable></literal>
+ </para>
- <para>
- Modulo operation. Returns the remainder of
- <replaceable>N</replaceable> divided by
- <replaceable>M</replaceable>.
+ <para>
+ Modulo operation. Returns the remainder of
+ <replaceable>N</replaceable> divided by
+ <replaceable>M</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic % -->
@@ -5019,12 +5133,12 @@
</para>
<para>
- This function is safe to use with <literal>BIGINT</literal> values.
+ This function is safe to use with <literal>BIGINT</literal> values.
</para>
<para>
- <literal>MOD()</literal> also works on values with a
- fractional part and returns the exact remainder after division:
+ <literal>MOD()</literal> also works on values with a
+ fractional part and returns the exact remainder after division:
</para>
<para>
@@ -5044,14 +5158,15 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>PI()</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>PI()</literal>
+ </para>
<para>
- Returns the value of PI. The default number of decimals displayed
- is five, but MySQL internally uses the full double-precision value
- for PI.
+ Returns the value of π (pi). The default number of decimals
+ displayed is seven, but MySQL internally uses the full
+ double-precision value.
<!-- end_description_for_help_topic -->
</para>
@@ -5083,15 +5198,16 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>POW(<replaceable>X</replaceable>,<replaceable>Y</replaceable>)</literal>
- ,
-
<literal>POWER(<replaceable>X</replaceable>,<replaceable>Y</replaceable>)</literal>
- </para>
-
- <para>
- Returns the value of <replaceable>X</replaceable> raised to the
- power of <replaceable>Y</replaceable>.
+ <listitem>
+ <para>
+
<literal>POW(<replaceable>X</replaceable>,<replaceable>Y</replaceable>)</literal>
+ ,
+
<literal>POWER(<replaceable>X</replaceable>,<replaceable>Y</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the value of <replaceable>X</replaceable> raised to the
+ power of <replaceable>Y</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -5100,12 +5216,14 @@
<!-- example_for_help_topic POWER -->
</para>
+<!-- Changed following to match what's produced in 5.0.7-beta -->
+
<para>
<programlisting>
mysql> SELECT POW(2,2);
- -> 4.000000
+ -> 4
mysql> SELECT POW(2,-2);
- -> 0.250000
+ -> 0.25
</programlisting>
</para>
@@ -5119,25 +5237,27 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>RADIANS(<replaceable>X</replaceable>)</literal>
- </para>
-
- <para>
- Returns the argument <replaceable>X</replaceable>, converted from
- degrees to radians.
+ <listitem>
+ <para>
+ <literal>RADIANS(<replaceable>X</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the argument <replaceable>X</replaceable>, converted
+ from degrees to radians. (Note that π radians equals 180
+ degrees.)
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic RADIANS -->
</para>
-
+<!-- Output as of 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT RADIANS(90);
- -> 1.570796
+ -> 1.5707963267949
</programlisting>
</para>
@@ -5151,19 +5271,20 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>RAND()</literal> ,
- <literal>RAND(<replaceable>N</replaceable>)</literal>
- </para>
-
- <para>
- Returns a random floating-point value in the range from
- <literal>0</literal> to <literal>1.0</literal>. If an
integer
- argument <replaceable>N</replaceable> is specified, it is used as
- the seed value (producing a repeatable sequence).
+ <listitem>
+ <para>
+ <literal>RAND()</literal> ,
+ <literal>RAND(<replaceable>N</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns a random floating-point value in the range from
+ 0 to 1.0. If an integer argument <replaceable>N</replaceable> is
+ specified, it is used as the seed value, which produces a
+ repeatable sequence.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic RAND -->
@@ -5175,20 +5296,26 @@
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
-mysql> SELECT RAND(20);
- -> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
+mysql> SELECT RAND(20);
+ -> 0.15888261251047
</programlisting>
</para>
-
+<!--
+ TODO: Rewrite the sentence I've commented out. It's *very*
+ confusing, as it seems to contradict directly what follows.
+-->
<para>
- You can't use a column with <literal>RAND()</literal> values in an
- <literal>ORDER BY</literal> clause, because <literal>ORDER
- BY</literal> would evaluate the column multiple times. You can use
- this function to retrieve rows in random order as shown here:
+ <!--
+ You can't use a column with <literal>RAND()</literal> values in an
+ <literal>ORDER BY</literal> clause, because <literal>ORDER
+ BY</literal> would evaluate the column multiple times.
+-->
+ You can use this function to retrieve rows in random order as
+ shown here:
</para>
<para>
@@ -5198,9 +5325,9 @@
</para>
<para>
- <literal>ORDER BY RAND()</literal> combined with
- <literal>LIMIT</literal> is useful for selecting a random sample of
- a set of rows:
+ <literal>ORDER BY RAND()</literal> combined with
+ <literal>LIMIT</literal> is useful for selecting a random sample
+ from a set of rows:
</para>
<para>
@@ -5211,16 +5338,16 @@
</para>
<para>
- Note that <literal>RAND()</literal> in a
<literal>WHERE</literal>
- clause is re-evaluated every time the <literal>WHERE</literal> is
- executed.
+ Note that <literal>RAND()</literal> in a
<literal>WHERE</literal>
+ clause is re-evaluated every time the <literal>WHERE</literal> is
+ executed.
</para>
<para>
- <literal>RAND()</literal> is not meant to be a perfect random
- generator, but instead a quick way of generating ad hoc random
- numbers that is portable between platforms for the same MySQL
- version.
+ <literal>RAND()</literal> is not meant to be a perfect random
+ generator, but instead a quick way of generating ad hoc random
+ numbers that is portable between platforms for the same MySQL
+ version.
</para>
<para>
@@ -5233,18 +5360,20 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>ROUND(<replaceable>X</replaceable>)</literal> ,
-
<literal>ROUND(<replaceable>X</replaceable>,<replaceable>D</replaceable>)</literal>
- </para>
-
- <para>
- Returns the argument <replaceable>X</replaceable>, rounded to the
- nearest integer. With two arguments, returns
- <replaceable>X</replaceable> rounded to
- <replaceable>D</replaceable> decimals.
<replaceable>D</replaceable>
- can be negative to round <replaceable>D</replaceable> digits left
- of the decimal point of the value <replaceable>X</replaceable>.
+ <listitem>
+ <para>
+ <literal>ROUND(<replaceable>X</replaceable>)</literal> ,
+
<literal>ROUND(<replaceable>X</replaceable>,<replaceable>D</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the argument <replaceable>X</replaceable>, rounded to
+ the nearest integer. With two arguments, returns
+ <replaceable>X</replaceable> rounded to
+ <replaceable>D</replaceable> decimals.
+ <replaceable>D</replaceable> can be negative in order to round
+ <replaceable>D</replaceable> digits left of the decimal point of
+ the value <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -5271,47 +5400,51 @@
</para>
<para>
- Before MySQL 5.0.3, the behavior of <literal>ROUND()</literal> when
- the argument is halfway between two integers depends on the C
- library implementation. Different implementations round to the
- nearest even number, always up, always down, or always toward zero.
- If you need one kind of rounding, you should use a well-defined
- function such as <literal>TRUNCATE()</literal> or
- <literal>FLOOR()</literal> instead.
+ Before MySQL 5.0.3, the behavior of <literal>ROUND()</literal>
+ when the argument is halfway between two integers depends on the C
+ library implementation. Different implementations round to the
+ nearest even number, always up, always down, or always toward
+ zero. If you need one kind of rounding, you should use a
+ well-defined function such as <literal>TRUNCATE()</literal> or
+ <literal>FLOOR()</literal> instead.
</para>
<para>
- As of MySQL 5.0.3, <literal>ROUND()</literal> uses the precision
- math library for exact arguments:
+ As of MySQL 5.0.3, <literal>ROUND()</literal> uses the precision
+ math library for exact arguments:
</para>
<para>
- <itemizedlist>
-
- <listitem><para>
- For exact-value numbers, <literal>ROUND()</literal> uses the
- "round half up" rule: A value with a fractional part of .5 or
- greater is rounded up to the next integer if positive or down to
- the next integer if negative. (In other words, it is rounded away
- from zero.) A value with a fractional part less than .5 is
- rounded down to the next integer if positive or up to the next
- integer if negative.
- </para></listitem>
-
- <listitem><para>
- For approximate-value numbers, the result depends on the C
- library. On many systems, this means that
- <literal>ROUND()</literal> uses the "round to nearest even" rule:
- A value with any fractional part is rounded to the nearest even
- integer.
- </para></listitem>
-
- </itemizedlist>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For exact-value numbers, <literal>ROUND()</literal> uses the
+ "round half up" rule: A value with a fractional part of .5
+ or greater is rounded up to the next integer if positive or
+ down to the next integer if negative. (In other words, it is
+ rounded away from zero.) A value with a fractional part less
+ than .5 is rounded down to the next integer if positive or
+ up to the next integer if negative.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For approximate-value numbers, the result depends on the C
+ library. On many systems, this means that
+ <literal>ROUND()</literal> uses the "round to nearest even"
+ rule: A value with any fractional part is rounded to the
+ nearest even integer.
+ </para>
+ </listitem>
+
+ </itemizedlist>
</para>
<para>
- The following example shows how rounding differs for exact and
- approximate values:
+ The following example shows how rounding differs for exact and
+ approximate values:
</para>
<para>
@@ -5326,7 +5459,7 @@
</para>
<para>
- For more information, see <xref linkend="precision-math"/>.
+ For more information, see <xref linkend="precision-math"/>.
</para>
<para>
@@ -5339,17 +5472,19 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>SIGN(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>SIGN(<replaceable>X</replaceable>)</literal>
+ </para>
- <para>
- Returns the sign of the argument as <literal>-1</literal>,
- <literal>0</literal>, or <literal>1</literal>, depending on
whether
- <replaceable>X</replaceable> is negative, zero, or positive.
+ <para>
+ Returns the sign of the argument as <literal>-1</literal>,
+ <literal>0</literal>, or <literal>1</literal>, depending
on
+ whether <replaceable>X</replaceable> is negative, zero, or
+ positive.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic SIGN -->
@@ -5376,13 +5511,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>SIN(<replaceable>X</replaceable>)</literal>
- </para>
-
- <para>
- Returns the sine of <replaceable>X</replaceable>, where
- <replaceable>X</replaceable> is given in radians.
+ <listitem>
+ <para>
+ <literal>SIN(<replaceable>X</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the sine of <replaceable>X</replaceable>, where
+ <replaceable>X</replaceable> is given in radians.
<!-- end_description_for_help_topic -->
</para>
@@ -5394,7 +5530,9 @@
<para>
<programlisting>
mysql> SELECT SIN(PI());
- -> 0.000000
+ -> 1.2246063538224e-16
+mysql> SELECT ROUND(SIN(PI()));
+ -> 0
</programlisting>
</para>
@@ -5408,13 +5546,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>SQRT(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>SQRT(<replaceable>X</replaceable>)</literal>
+ </para>
<para>
- Returns the non-negative square root of
- <replaceable>X</replaceable>.
+ Returns the square root of a non-negative number
+ <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -5422,13 +5561,19 @@
<para>
<!-- example_for_help_topic SQRT -->
</para>
-
+<!-- Output shown is from 5.0.7-beta. -->
+<!--
+ TODO: Mention how and why behaviour has changed from earlier
+ versions, e.g., in older versions SELECT SQRT(4); returned 2.00
+-->
<para>
<programlisting>
mysql> SELECT SQRT(4);
- -> 2.000000
+ -> 2
mysql> SELECT SQRT(20);
- -> 4.472136
+ -> 4.4721359549996
+mysql> SELECT SQRT(-16);
+ -> NULL
</programlisting>
</para>
@@ -5442,13 +5587,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>TAN(<replaceable>X</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>TAN(<replaceable>X</replaceable>)</literal>
+ </para>
<para>
- Returns the tangent of <replaceable>X</replaceable>, where
- <replaceable>X</replaceable> is given in radians.
+ Returns the tangent of <replaceable>X</replaceable>, where
+ <replaceable>X</replaceable> is given in radians.
<!-- end_description_for_help_topic -->
</para>
@@ -5456,11 +5602,13 @@
<para>
<!-- example_for_help_topic TAN -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
+mysql> SELECT TAN(PI());
+ -> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
- -> 1.557408
+ -> 1.5574077246549
</programlisting>
</para>
@@ -5474,21 +5622,22 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>TRUNCATE(<replaceable>X</replaceable>,<replaceable>D</replaceable>)</literal>
- </para>
-
- <para>
- Returns the number <replaceable>X</replaceable>, truncated to
- <replaceable>D</replaceable> decimals. If
- <replaceable>D</replaceable> is <literal>0</literal>, the
result
- has no decimal point or fractional part.
- <replaceable>D</replaceable> can be negative to truncate (make
- zero) <replaceable>D</replaceable> digits left of the decimal point
- of the value <replaceable>X</replaceable>.
+ <listitem>
+ <para>
+
<literal>TRUNCATE(<replaceable>X</replaceable>,<replaceable>D</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the number <replaceable>X</replaceable>, truncated to
+ <replaceable>D</replaceable> decimals. If
+ <replaceable>D</replaceable> is 0, the result has no decimal
+ point or fractional part. <replaceable>D</replaceable> can be
+ negative in order to truncate (make zero)
+ <replaceable>D</replaceable> digits left of the decimal point
+ of the value <replaceable>X</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic TRUNCATE -->
@@ -5512,12 +5661,16 @@
<para>
All numbers are rounded toward zero.
</para>
-<!-- NOTE: When I tried this example in 5.0.7, it returned 1028. -->
+<!--
+ NOTE: When I tried this example in 5.0.7, it returned 1028, and
+ SELECT 10.28 * 100 yielded 1028.00. Soi I've commented it out for
+ now. -JS
+-->
<!--
<para>
- Note that decimal numbers are normally not stored as exact numbers
- in computers, but as double-precision values, so you may be
- surprised by the following result:
+ Note that decimal numbers are normally not stored as exact numbers
+ in computers, but as double-precision values, so you may be
+ surprised by the following result:
</para>
<para>
@@ -5560,16 +5713,16 @@
</indexterm>
<para>
- This section describes the functions that can be used to manipulate
- temporal values. See <xref linkend="date-and-time-types"/> for a
- description of the range of values each date and time type has and
- the valid formats in which values may be specified.
+ This section describes the functions that can be used to manipulate
+ temporal values. See <xref linkend="date-and-time-types"/> for a
+ description of the range of values each date and time type has and
+ the valid formats in which values may be specified.
</para>
<para>
- Here is an example that uses date functions. The following query
- selects all records with a <literal>date_col</literal> value from
- within the last 30 days:
+ Here is an example that uses date functions. The following query
+ selects all records with a <literal>date_col</literal> value from
+ within the last 30 days:
</para>
<programlisting>
@@ -5578,45 +5731,45 @@
</programlisting>
<para>
- Note that the query also selects records with dates that lie in the
- future.
+ Note that the query also selects records with dates that lie in the
+ future.
</para>
<para>
- Functions that expect date values usually accept datetime values and
- ignore the time part. Functions that expect time values usually
- accept datetime values and ignore the date part.
+ Functions that expect date values usually accept datetime values and
+ ignore the time part. Functions that expect time values usually
+ accept datetime values and ignore the date part.
</para>
<para>
- Functions that return the current date or time each are evaluated
- only once per query at the start of query execution. This means that
- multiple references to a function such as <literal>NOW()</literal>
- within a single query always produce the same result. This principle
- also applies to <literal>CURDATE()</literal>,
- <literal>CURTIME()</literal>, <literal>UTC_DATE()</literal>,
- <literal>UTC_TIME()</literal>,
<literal>UTC_TIMESTAMP()</literal>,
- and to any of their synonyms.
+ Functions that return the current date or time each are evaluated
+ only once per query at the start of query execution. This means that
+ multiple references to a function such as <literal>NOW()</literal>
+ within a single query always produce the same result. This principle
+ also applies to <literal>CURDATE()</literal>,
+ <literal>CURTIME()</literal>, <literal>UTC_DATE()</literal>,
+ <literal>UTC_TIME()</literal>,
<literal>UTC_TIMESTAMP()</literal>,
+ and to any of their synonyms.
</para>
<para>
- In MySQL 5.0, the <literal>CURRENT_TIMESTAMP()</literal>,
- <literal>CURRENT_TIME()</literal>,
<literal>CURRENT_DATE()</literal>,
- and <literal>FROM_UNIXTIME()</literal> functions return values in the
- connection's current time zone, which is available as the value of
- the <literal>time_zone</literal> system variable. In addition,
- <literal>UNIX_TIMESTAMP()</literal> assumes that its argument is a
- datetime value in the current time zone. See
- <xref linkend="time-zone-support"/>.
+ In MySQL 5.0, the <literal>CURRENT_TIMESTAMP()</literal>,
+ <literal>CURRENT_TIME()</literal>,
+ <literal>CURRENT_DATE()</literal>, and
+ <literal>FROM_UNIXTIME()</literal> functions return values in the
+ connection's current time zone, which is available as the value of
+ the <literal>time_zone</literal> system variable. In addition,
+ <literal>UNIX_TIMESTAMP()</literal> assumes that its argument is a
+ datetime value in the current time zone. See
+ <xref linkend="time-zone-support"/>.
</para>
<para>
- The return value ranges in the following function descriptions apply
- for complete dates. If a date is a ``zero'' value or an incomplete
- date such as <literal>'2001-11-00'</literal>, functions that extract
- a part of a date may return <literal>0</literal>. For example,
- <literal>DAYOFMONTH('2001-11-00')</literal> returns
- <literal>0</literal>.
+ The return value ranges in the following function descriptions apply
+ for complete dates. If a date is a ``zero'' value or an incomplete
+ date such as <literal>'2001-11-00'</literal>, functions that extract
+ a part of a date may return 0. For example,
+ <literal>DAYOFMONTH('2001-11-00')</literal> returns 0.
</para>
<itemizedlist>
@@ -5627,46 +5780,47 @@
<primary>ADDDATE()</primary>
</indexterm>
- <listitem><para>
- <literal>ADDDATE(<replaceable>date</replaceable>,INTERVAL
- <replaceable>expr</replaceable>
- <replaceable>type</replaceable>)</literal> ,
-
<literal>ADDDATE(<replaceable>expr</replaceable>,<replaceable>days</replaceable>)</literal>
- </para>
-
- <para>
- When invoked with the <literal>INTERVAL</literal> form of the second
- argument, <literal>ADDDATE()</literal> is a synonym for
- <literal>DATE_ADD()</literal>. The related function
- <literal>SUBDATE()</literal> is a synonym for
- <literal>DATE_SUB()</literal>. For information on the
- <literal>INTERVAL</literal> argument, see the discussion for
- <literal>DATE_ADD()</literal>.
- </para>
-
- <para>
+ <listitem>
+ <para>
+ <literal>ADDDATE(<replaceable>date</replaceable>,INTERVAL
+ <replaceable>expr</replaceable>
+ <replaceable>type</replaceable>)</literal> ,
+
<literal>ADDDATE(<replaceable>expr</replaceable>,<replaceable>days</replaceable>)</literal>
+ </para>
+
+ <para>
+ When invoked with the <literal>INTERVAL</literal> form of the
+ second argument, <literal>ADDDATE()</literal> is a synonym for
+ <literal>DATE_ADD()</literal>. The related function
+ <literal>SUBDATE()</literal> is a synonym for
+ <literal>DATE_SUB()</literal>. For information on the
+ <literal>INTERVAL</literal> argument, see the discussion for
+ <literal>DATE_ADD()</literal>.
+ </para>
+
+ <para>
<programlisting>
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
</programlisting>
- </para>
-
- <para>
- If the <replaceable>days</replaceable> argument is simply an integer
- value, then MySQL 5.0 treats it as the number of days to be added to
- <replaceable>expr</replaceable>.
- </para>
+ </para>
- <para>
+ <para>
+ If the <replaceable>days</replaceable> argument is simply an
+ integer value, then MySQL 5.0 treats it as the number of days to
+ be added to <replaceable>expr</replaceable>.
+ </para>
+<!-- Output from 5.0.7-beta -->
+ <para>
<programlisting>
mysql> SELECT ADDDATE('1998-01-02', 31);
-> '1998-02-02'
</programlisting>
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- description_for_help_topic ADDTIME -->
@@ -5676,7 +5830,8 @@
<indexterm type="function">
<primary>ADDTIME()</primary>
</indexterm>
- </para></listitem>
+ </para>
+ </listitem>
<listitem><para>
<literal>ADDTIME(<replaceable>expr</replaceable>,<replaceable>expr2</replaceable>)</literal>
@@ -5687,10 +5842,10 @@
</para>
<para>
- <literal>ADDTIME()</literal> adds
<replaceable>expr2</replaceable>
- to <replaceable>expr</replaceable> and returns the result.
- <replaceable>expr</replaceable> is a time or datetime expression,
- and <replaceable>expr2</replaceable> is a time expression.
+ <literal>ADDTIME()</literal> adds
<replaceable>expr2</replaceable>
+ to <replaceable>expr</replaceable> and returns the result.
+ <replaceable>expr</replaceable> is a time or datetime expression,
+ and <replaceable>expr2</replaceable> is a time expression.
<!-- end_description_for_help_topic -->
</para>
@@ -5698,7 +5853,7 @@
<para>
<!-- example_for_help_topic ADDTIME -->
</para>
-
+<!-- Output from 5.0.7-beta -->
<para>
<programlisting>
mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',
@@ -5719,28 +5874,29 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>CONVERT_TZ(<replaceable>dt</replaceable>,<replaceable>from_tz</replaceable>,<replaceable>to_tz</replaceable>)</literal>
- </para>
-
- <para>
- <literal>CONVERT_TZ()</literal> converts a datetime value
- <replaceable>dt</replaceable> from time zone given by
- <replaceable>from_tz</replaceable> to the time zone given by
- <replaceable>to_tz</replaceable> and returns the resulting value.
- Time zones may be specified as described in
- <xref linkend="time-zone-support"/>. This function returns
- <literal>NULL</literal> if the arguments are invalid.
+ <listitem>
+ <para>
+
<literal>CONVERT_TZ(<replaceable>dt</replaceable>,<replaceable>from_tz</replaceable>,<replaceable>to_tz</replaceable>)</literal>
+ </para>
+
+ <para>
+ <literal>CONVERT_TZ()</literal> converts a datetime value
+ <replaceable>dt</replaceable> from time zone given by
+ <replaceable>from_tz</replaceable> to the time zone given by
+ <replaceable>to_tz</replaceable> and returns the resulting value.
+ Time zones may be specified as described in
+ <xref linkend="time-zone-support"/>. This function returns
+ <literal>NULL</literal> if the arguments are invalid.
<!-- end_description_for_help_topic -->
</para>
<para>
- If the value falls out of the supported range of the
- <literal>TIMESTAMP</literal> type when converted fom
- <replaceable>from_tz</replaceable> to UTC, no conversion occurs. The
- <literal>TIMESTAMP</literal> range is described at
- <xref linkend="date-and-time-type-overview"/>.
+ If the value falls out of the supported range of the
+ <literal>TIMESTAMP</literal> type when converted fom
+ <replaceable>from_tz</replaceable> to UTC, no conversion occurs.
+ The <literal>TIMESTAMP</literal> range is described in
+ <xref linkend="date-and-time-type-overview"/>.
</para>
<para>
@@ -5751,16 +5907,16 @@
<programlisting>
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
-> '2004-01-01 13:00:00'
-mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','-07:00');
- -> '2004-01-01 05:00:00'
+mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+ -> '2004-01-01 22:00:00'
</programlisting>
</para>
<para>
- To use named time zones such as <literal>'MET'</literal> or
- <literal>'Europe/Moscow'</literal>, the time zone tables must be
- properly set up. See <xref linkend="time-zone-support"/> for
- instructions.
+ <emphasis role="bold">Note</emphasis>: To use named time zones such
+ as <literal>'MET'</literal> or
<literal>'Europe/Moscow'</literal>,
+ the time zone tables must be properly set up. See
+ <xref linkend="time-zone-support"/> for instructions.
</para>
<para>
@@ -5773,18 +5929,19 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>CURDATE()</literal>
- </para>
-
- <para>
- Returns the current date as a value in
- <literal>'YYYY-MM-DD'</literal> or
<literal>YYYYMMDD</literal>
- format, depending on whether the function is used in a string or
- numeric context.
+ <listitem>
+ <para>
+ <literal>CURDATE()</literal>
+ </para>
+
+ <para>
+ Returns the current date as a value in
+ <literal>'YYYY-MM-DD'</literal> or
<literal>YYYYMMDD</literal>
+ format, depending on whether the function is used in a string or
+ numeric context.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic CURDATE -->
@@ -5807,19 +5964,21 @@
<indexterm type="function">
<primary>CURRENT_DATE</primary>
</indexterm>
- </para></listitem>
-
- <listitem><para>
- <literal>CURRENT_DATE</literal> ,
<literal>CURRENT_DATE()</literal>
</para>
+ </listitem>
- <para>
- <literal>CURRENT_DATE</literal> and
- <literal>CURRENT_DATE()</literal> are synonyms for
- <literal>CURDATE()</literal>.
+ <listitem>
+ <para>
+ <literal>CURRENT_DATE</literal> ,
<literal>CURRENT_DATE()</literal>
+ </para>
+
+ <para>
+ <literal>CURRENT_DATE</literal> and
+ <literal>CURRENT_DATE()</literal> are synonyms for
+ <literal>CURDATE()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- description_for_help_topic CURTIME -->
@@ -5831,14 +5990,16 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>CURTIME()</literal>
- </para>
-
- <para>
- Returns the current time as a value in <literal>'HH:MM:SS'</literal>
- or <literal>HHMMSS</literal> format, depending on whether the
- function is used in a string or numeric context.
+ <listitem>
+ <para>
+ <literal>CURTIME()</literal>
+ </para>
+
+ <para>
+ Returns the current time as a value in
+ <literal>'HH:MM:SS'</literal> or <literal>HHMMSS</literal>
+ format, depending on whether the function is used in a string or
+ numeric context.
<!-- end_description_for_help_topic -->
</para>
@@ -5866,14 +6027,15 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>CURRENT_TIME</literal> ,
<literal>CURRENT_TIME()</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>CURRENT_TIME</literal> ,
<literal>CURRENT_TIME()</literal>
+ </para>
<para>
- <literal>CURRENT_TIME</literal> and
- <literal>CURRENT_TIME()</literal> are synonyms for
- <literal>CURTIME()</literal>.
+ <literal>CURRENT_TIME</literal> and
+ <literal>CURRENT_TIME()</literal> are synonyms for
+ <literal>CURTIME()</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -5888,18 +6050,19 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>CURRENT_TIMESTAMP</literal> ,
- <literal>CURRENT_TIMESTAMP()</literal>
- </para>
-
- <para>
- <literal>CURRENT_TIMESTAMP</literal> and
- <literal>CURRENT_TIMESTAMP()</literal> are synonyms for
- <literal>NOW()</literal>.
+ <listitem>
+ <para>
+ <literal>CURRENT_TIMESTAMP</literal> ,
+ <literal>CURRENT_TIMESTAMP()</literal>
+ </para>
+
+ <para>
+
+ <literal>CURRENT_TIMESTAMP()</literal> are synonyms for
+ <literal>NOW()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- description_for_help_topic DATE function DATE -->
@@ -5911,16 +6074,17 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>DATE(<replaceable>expr</replaceable>)</literal>
- </para>
-
- <para>
- Extracts the date part of the date or datetime expression
- <replaceable>expr</replaceable>.
+ <listitem>
+ <para>
+ <literal>DATE(<replaceable>expr</replaceable>)</literal>
+ </para>
+
+ <para>
+ Extracts the date part of the date or datetime expression
+ <replaceable>expr</replaceable>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic DATE function -->
@@ -6028,175 +6192,90 @@
<colspec colwidth="55*"/>
<tbody>
<row>
- <entry>
- <replaceable>type</replaceable>
- <emphasis role="bold">Value</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Expected</emphasis>
- <replaceable>expr</replaceable>
- <emphasis role="bold">Format</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>MICROSECOND</literal>
- </entry>
- <entry>
- <literal>MICROSECONDS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>SECOND</literal>
- </entry>
- <entry>
- <literal>SECONDS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>MINUTE</literal>
- </entry>
- <entry>
- <literal>MINUTES</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>HOUR</literal>
- </entry>
- <entry>
- <literal>HOURS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>DAY</literal>
- </entry>
- <entry>
- <literal>DAYS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>WEEK</literal>
- </entry>
- <entry>
- <literal>WEEKS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>MONTH</literal>
- </entry>
- <entry>
- <literal>MONTHS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>QUARTER</literal>
- </entry>
- <entry>
- <literal>QUARTERS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>YEAR</literal>
- </entry>
- <entry>
- <literal>YEARS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>SECOND_MICROSECOND</literal>
- </entry>
- <entry>
- <literal>'SECONDS.MICROSECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>MINUTE_MICROSECOND</literal>
- </entry>
- <entry>
- <literal>'MINUTES.MICROSECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>MINUTE_SECOND</literal>
- </entry>
- <entry>
- <literal>'MINUTES:SECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>HOUR_MICROSECOND</literal>
- </entry>
- <entry>
- <literal>'HOURS.MICROSECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>HOUR_SECOND</literal>
- </entry>
- <entry>
- <literal>'HOURS:MINUTES:SECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>HOUR_MINUTE</literal>
- </entry>
- <entry>
- <literal>'HOURS:MINUTES'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>DAY_MICROSECOND</literal>
- </entry>
- <entry>
- <literal>'DAYS.MICROSECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>DAY_SECOND</literal>
- </entry>
- <entry>
- <literal>'DAYS HOURS:MINUTES:SECONDS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>DAY_MINUTE</literal>
- </entry>
- <entry>
- <literal>'DAYS HOURS:MINUTES'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>DAY_HOUR</literal>
- </entry>
- <entry>
- <literal>'DAYS HOURS'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>YEAR_MONTH</literal>
- </entry>
- <entry>
- <literal>'YEARS-MONTHS'</literal>
- </entry>
+ <entry><emphasis
role="bold"><replaceable>type</replaceable>
+ Value</emphasis></entry>
+ <entry><emphasis role="bold">Expected
+ <replaceable>expr</replaceable>
Format</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>MICROSECOND</literal></entry>
+ <entry><literal>MICROSECONDS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SECOND</literal></entry>
+ <entry><literal>SECONDS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>MINUTE</literal></entry>
+ <entry><literal>MINUTES</literal></entry>
+ </row>
+ <row>
+ <entry><literal>HOUR</literal></entry>
+ <entry><literal>HOURS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DAY</literal></entry>
+ <entry><literal>DAYS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>WEEK</literal></entry>
+ <entry><literal>WEEKS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>MONTH</literal></entry>
+ <entry><literal>MONTHS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>QUARTER</literal></entry>
+ <entry><literal>QUARTERS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>YEAR</literal></entry>
+ <entry><literal>YEARS</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SECOND_MICROSECOND</literal></entry>
+
<entry><literal>'SECONDS.MICROSECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>MINUTE_MICROSECOND</literal></entry>
+
<entry><literal>'MINUTES.MICROSECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>MINUTE_SECOND</literal></entry>
+ <entry><literal>'MINUTES:SECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>HOUR_MICROSECOND</literal></entry>
+ <entry><literal>'HOURS.MICROSECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>HOUR_SECOND</literal></entry>
+
<entry><literal>'HOURS:MINUTES:SECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>HOUR_MINUTE</literal></entry>
+ <entry><literal>'HOURS:MINUTES'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DAY_MICROSECOND</literal></entry>
+ <entry><literal>'DAYS.MICROSECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DAY_SECOND</literal></entry>
+ <entry><literal>'DAYS
HOURS:MINUTES:SECONDS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DAY_MINUTE</literal></entry>
+ <entry><literal>'DAYS HOURS:MINUTES'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DAY_HOUR</literal></entry>
+ <entry><literal>'DAYS HOURS'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>YEAR_MONTH</literal></entry>
+ <entry><literal>'YEARS-MONTHS'</literal></entry>
</row>
</tbody>
</tgroup>
@@ -6204,29 +6283,30 @@
<para>
The values <literal>QUARTER</literal> and
<literal>WEEK</literal>
- are new in MySQL 5.0.0.
+ are available beginning with MySQL 5.0.0.
</para>
<para>
- MySQL allows any punctuation delimiter in the
- <replaceable>expr</replaceable> format. Those shown in the table are
- the suggested delimiters. If the <replaceable>date</replaceable>
- argument is a <literal>DATE</literal> value and your calculations
- involve only <literal>YEAR</literal>,
<literal>MONTH</literal>, and
- <literal>DAY</literal> parts (that is, no time parts), the result is
- a <literal>DATE</literal> value. Otherwise, the result is a
- <literal>DATETIME</literal> value.
+ MySQL allows any punctuation delimiter in the
+ <replaceable>expr</replaceable> format. Those shown in the table
+ are the suggested delimiters. If the
+ <replaceable>date</replaceable>
+ argument is a <literal>DATE</literal> value and your calculations
+ involve only <literal>YEAR</literal>,
<literal>MONTH</literal>, and
+ <literal>DAY</literal> parts (that is, no time parts), the result
+ is a <literal>DATE</literal> value. Otherwise, the result is a
+ <literal>DATETIME</literal> value.
</para>
<para>
- <literal>INTERVAL <replaceable>expr</replaceable>
- <replaceable>type</replaceable></literal> is allowed on either side
- of the <literal>+</literal> operator if the expression on the other
- side is a date or datetime value. For the <literal>-</literal>
- operator, <literal>INTERVAL <replaceable>expr</replaceable>
- <replaceable>type</replaceable></literal> is allowed only on the
- right side, because it makes no sense to subtract a date or datetime
- value from an interval. (See examples below.)
+ <literal>INTERVAL <replaceable>expr</replaceable>
+ <replaceable>type</replaceable></literal> is allowed on either
side
+ of the <literal>+</literal> operator if the expression on the other
+ side is a date or datetime value. For the <literal>-</literal>
+ operator, <literal>INTERVAL <replaceable>expr</replaceable>
+ <replaceable>type</replaceable></literal> is allowed only on the
+ right side, because it makes no sense to subtract a date or
+ datetime value from an interval. (See examples below.)
</para>
<para>
@@ -6265,27 +6345,27 @@
</para>
<para>
- If you specify an interval value that is too short (does not include
- all the interval parts that would be expected from the
- <replaceable>type</replaceable> keyword), MySQL assumes that you
- have left out the leftmost parts of the interval value. For example,
- if you specify a <literal>type</literal> of
- <literal>DAY_SECOND</literal>, the value of
- <replaceable>expr</replaceable> is expected to have days, hours,
- minutes, and seconds parts. If you specify a value like
- <literal>'1:10'</literal>, MySQL assumes that the days and hours
- parts are missing and the value represents minutes and seconds. In
- other words, <literal>'1:10' DAY_SECOND</literal> is interpreted in
- such a way that it is equivalent to <literal>'1:10'
- MINUTE_SECOND</literal>. This is analogous to the way that MySQL
- interprets <literal>TIME</literal> values as representing elapsed
- time rather than as time of day.
+ If you specify an interval value that is too short (does not
+ include all the interval parts that would be expected from the
+ <replaceable>type</replaceable> keyword), MySQL assumes that you
+ have left out the leftmost parts of the interval value. For
+ example, if you specify a <literal>type</literal> of
+ <literal>DAY_SECOND</literal>, the value of
+ <replaceable>expr</replaceable> is expected to have days, hours,
+ minutes, and seconds parts. If you specify a value like
+ '1:10', MySQL assumes that the days and hours parts are missing
+ and the value represents minutes and seconds. In other words,
+ <literal>'1:10' DAY_SECOND</literal> is interpreted in such a way
+ that it is equivalent to <literal>'1:10' MINUTE_SECOND</literal>.
+ This is analogous to the way that MySQL interprets
+ <literal>TIME</literal> values as representing elapsed time rather
+ than as a time of day.
</para>
<para>
- If you add to or subtract from a date value something that contains
- a time part, the result is automatically converted to a datetime
- value:
+ If you add to or subtract from a date value something that contains
+ a time part, the result is automatically converted to a datetime
+ value:
</para>
<para>
@@ -6298,11 +6378,12 @@
</para>
<para>
- If you use very badly malformed dates, the result is
- <literal>NULL</literal>. If you add <literal>MONTH</literal>,
- <literal>YEAR_MONTH</literal>, or <literal>YEAR</literal> and
the
- resulting date has a day that is larger than the maximum day for the
- new month, the day is adjusted to the maximum days in the new month:
+ If you use very badly malformed dates, the result is
+ <literal>NULL</literal>. If you add
<literal>MONTH</literal>,
+ <literal>YEAR_MONTH</literal>, or <literal>YEAR</literal>
and the
+ resulting date has a day that is larger than the maximum day for
+ the new month, the day is adjusted to the maximum days in the new
+ month:
</para>
<para>
@@ -6322,323 +6403,206 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>DATE_FORMAT(<replaceable>date</replaceable>,<replaceable>format</replaceable>)</literal>
- </para>
-
- <para>
- Formats the <replaceable>date</replaceable> value according to the
- <replaceable>format</replaceable> string. The following specifiers
- may be used in the <replaceable>format</replaceable> string:
- </para>
+ <listitem>
+ <para>
+
<literal>DATE_FORMAT(<replaceable>date</replaceable>,<replaceable>format</replaceable>)</literal>
+ </para>
+
+ <para>
+ Formats the <replaceable>date</replaceable> value according to
+ the <replaceable>format</replaceable> string. The following
+ specifiers may be used in the <replaceable>format</replaceable>
+ string:
+ </para>
- <informaltable>
- <tgroup cols="2">
- <colspec colwidth="20*"/>
- <colspec colwidth="70*"/>
- <tbody>
- <row>
- <entry>
- <emphasis role="bold">Specifier</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Description</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%a</literal>
- </entry>
- <entry>
- Abbreviated weekday name
- (<literal>Sun</literal>..<literal>Sat</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%b</literal>
- </entry>
- <entry>
- Abbreviated month name
- (<literal>Jan</literal>..<literal>Dec</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%c</literal>
- </entry>
- <entry>
- Month, numeric
(<literal>0</literal>..<literal>12</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%D</literal>
- </entry>
- <entry>
- Day of the month with English suffix
- (<literal>0th</literal>, <literal>1st</literal>,
- <literal>2nd</literal>, <literal>3rd</literal>, ...)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%d</literal>
- </entry>
- <entry>
- Day of the month, numeric
- (<literal>00</literal>..<literal>31</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%e</literal>
- </entry>
- <entry>
- Day of the month, numeric
- (<literal>0</literal>..<literal>31</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%f</literal>
- </entry>
- <entry>
- Microseconds
-
(<literal>000000</literal>..<literal>999999</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%H</literal>
- </entry>
- <entry>
- Hour (<literal>00</literal>..<literal>23</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%h</literal>
- </entry>
- <entry>
- Hour (<literal>01</literal>..<literal>12</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%I</literal>
- </entry>
- <entry>
- Hour (<literal>01</literal>..<literal>12</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%i</literal>
- </entry>
- <entry>
- Minutes, numeric
- (<literal>00</literal>..<literal>59</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%j</literal>
- </entry>
- <entry>
- Day of year
(<literal>001</literal>..<literal>366</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%k</literal>
- </entry>
- <entry>
- Hour (<literal>0</literal>..<literal>23</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%l</literal>
- </entry>
- <entry>
- Hour (<literal>1</literal>..<literal>12</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%M</literal>
- </entry>
- <entry>
- Month name
-
(<literal>January</literal>..<literal>December</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%m</literal>
- </entry>
- <entry>
- Month, numeric
- (<literal>00</literal>..<literal>12</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%p</literal>
- </entry>
- <entry>
- <literal>AM</literal> or <literal>PM</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%r</literal>
- </entry>
- <entry>
- Time, 12-hour (<literal>hh:mm:ss</literal> followed by
- <literal>AM</literal> or <literal>PM</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%S</literal>
- </entry>
- <entry>
- Seconds
(<literal>00</literal>..<literal>59</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%s</literal>
- </entry>
- <entry>
- Seconds
(<literal>00</literal>..<literal>59</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%T</literal>
- </entry>
- <entry>
- Time, 24-hour (<literal>hh:mm:ss</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%U</literal>
- </entry>
- <entry>
- Week (<literal>00</literal>..<literal>53</literal>),
where
- Sunday is the first day of the week
- </entry>
- </row>
- <row>
- <entry>
- <literal>%u</literal>
- </entry>
- <entry>
- Week (<literal>00</literal>..<literal>53</literal>),
where
- Monday is the first day of the week
- </entry>
- </row>
- <row>
- <entry>
- <literal>%V</literal>
- </entry>
- <entry>
- Week (<literal>01</literal>..<literal>53</literal>),
where
- Sunday is the first day of the week; used with
- <literal>%X</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%v</literal>
- </entry>
- <entry>
- Week (<literal>01</literal>..<literal>53</literal>),
where
- Monday is the first day of the week; used with
- <literal>%x</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%W</literal>
- </entry>
- <entry>
- Weekday name
-
(<literal>Sunday</literal>..<literal>Saturday</literal>)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%w</literal>
- </entry>
- <entry>
- Day of the week
-
(<literal>0</literal>=Sunday..<literal>6</literal>=Saturday)
- </entry>
- </row>
- <row>
- <entry>
- <literal>%X</literal>
- </entry>
- <entry>
- Year for the week where Sunday is the first day of the week,
- numeric, four digits; used with <literal>%V</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%x</literal>
- </entry>
- <entry>
- Year for the week, where Monday is the first day of the
- week, numeric, four digits; used with <literal>%v</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>%Y</literal>
- </entry>
- <entry>
- Year, numeric, four digits
- </entry>
- </row>
- <row>
- <entry>
- <literal>%y</literal>
- </entry>
- <entry>
- Year, numeric, two digits
- </entry>
- </row>
- <row>
- <entry>
- <literal>%%</literal>
- </entry>
- <entry>
- A literal '<literal>%</literal>'.
- </entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="20*"/>
+ <colspec colwidth="70*"/>
+ <tbody>
+ <row>
+ <entry><emphasis
role="bold">Specifier</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>%a</literal></entry>
+ <entry>Abbreviated weekday name
+
(<literal>Sun</literal>..<literal>Sat</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%b</literal></entry>
+ <entry>Abbreviated month name
+
(<literal>Jan</literal>..<literal>Dec</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%c</literal></entry>
+ <entry>Month, numeric
+
(<literal>0</literal>..<literal>12</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%D</literal></entry>
+ <entry>Day of the month with English suffix
+ (<literal>0th</literal>, <literal>1st</literal>,
+ <literal>2nd</literal>,
+ <literal>3rd</literal>, ...)</entry>
+ </row>
+ <row>
+ <entry><literal>%d</literal></entry>
+ <entry>Day of the month, numeric
+
(<literal>00</literal>..<literal>31</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%e</literal></entry>
+ <entry>Day of the month, numeric
+
(<literal>0</literal>..<literal>31</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%f</literal></entry>
+ <entry>Microseconds
+
(<literal>000000</literal>..<literal>999999</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%H</literal></entry>
+ <entry>Hour
+
(<literal>00</literal>..<literal>23</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%h</literal></entry>
+ <entry>Hour
+
(<literal>01</literal>..<literal>12</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%I</literal></entry>
+ <entry>Hour
+
(<literal>01</literal>..<literal>12</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%i</literal></entry>
+ <entry>Minutes, numeric
+
(<literal>00</literal>..<literal>59</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%j</literal></entry>
+ <entry>Day of year
+
(<literal>001</literal>..<literal>366</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%k</literal></entry>
+ <entry>Hour
+
(<literal>0</literal>..<literal>23</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%l</literal></entry>
+ <entry>Hour
+
(<literal>1</literal>..<literal>12</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%M</literal></entry>
+ <entry>Month name
+
(<literal>January</literal>..<literal>December</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%m</literal></entry>
+ <entry>Month, numeric
+
(<literal>00</literal>..<literal>12</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%p</literal></entry>
+ <entry><literal>AM</literal> or
+ <literal>PM</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%r</literal></entry>
+ <entry>Time, 12-hour (<literal>hh:mm:ss</literal> followed
by
+ <literal>AM</literal> or
<literal>PM</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%S</literal></entry>
+ <entry>Seconds
+
(<literal>00</literal>..<literal>59</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%s</literal></entry>
+ <entry>Seconds
+
(<literal>00</literal>..<literal>59</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%T</literal></entry>
+ <entry>Time, 24-hour
(<literal>hh:mm:ss</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%U</literal></entry>
+ <entry>Week
(<literal>00</literal>..<literal>53</literal>),
+ where Sunday is the first day of the week</entry>
+ </row>
+ <row>
+ <entry><literal>%u</literal></entry>
+ <entry>Week
(<literal>00</literal>..<literal>53</literal>),
+ where Monday is the first day of the week</entry>
+ </row>
+ <row>
+ <entry><literal>%V</literal></entry>
+ <entry>Week
(<literal>01</literal>..<literal>53</literal>),
+ where Sunday is the first day of the week; used with
+ <literal>%X</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%v</literal></entry>
+ <entry>Week
(<literal>01</literal>..<literal>53</literal>),
+ where Monday is the first day of the week; used with
+ <literal>%x</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%W</literal></entry>
+ <entry>Weekday name
+
(<literal>Sunday</literal>..<literal>Saturday</literal>)</entry>
+ </row>
+ <row>
+ <entry><literal>%w</literal></entry>
+ <entry>Day of the week
+
(<literal>0</literal>=Sunday..<literal>6</literal>=Saturday)</entry>
+ </row>
+ <row>
+ <entry><literal>%X</literal></entry>
+ <entry>Year for the week where Sunday is the first day of
+ the week, numeric, four digits; used with
+ <literal>%V</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%x</literal></entry>
+ <entry>Year for the week, where Monday is the first day of
+ the week, numeric, four digits; used with
+ <literal>%v</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%Y</literal></entry>
+ <entry>Year, numeric, four digits</entry>
+ </row>
+ <row>
+ <entry><literal>%y</literal></entry>
+ <entry>Year, numeric (two digits)</entry>
+ </row>
+ <row>
+ <entry><literal>%%</literal></entry>
+ <entry>A literal '<literal>%</literal>'
character</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
<para>
- All other characters are copied to the result without
- interpretation.
+ All other characters are copied to the result without
+ interpretation.
</para>
<para>
- Note that the '<literal>%</literal>' character is required
- before format specifier characters.
+ Note that the '<literal>%</literal>' character is required
+ before format specifier characters.
</para>
<para>
- The reason why the ranges for the month and day specifiers begin
- with zero is that MySQL allows the storing of incomplete dates such as
+ Ranges for the month and day specifiers begin with zero due to the
+ fact that MySQL allows the storing of incomplete dates such as
<literal>'2004-00-00'</literal>.
<!-- end_description_for_help_topic -->
@@ -6675,16 +6639,17 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>DAY(<replaceable>date</replaceable>)</literal>
- </para>
-
- <para>
- <literal>DAY()</literal> is a synonym for
- <literal>DAYOFMONTH()</literal>.
+ <listitem>
+ <para>
+ <literal>DAY(<replaceable>date</replaceable>)</literal>
+ </para>
+
+ <para>
+ <literal>DAY()</literal> is a synonym for
+ <literal>DAYOFMONTH()</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- description_for_help_topic DAYNAME -->
@@ -6696,12 +6661,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>DAYNAME(<replaceable>date</replaceable>)</literal>
- </para>
-
- <para>
- Returns the name of the weekday for <replaceable>date</replaceable>.
+ <listitem>
+ <para>
+
<literal>DAYNAME(<replaceable>date</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the name of the weekday for
+ <replaceable>date</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -6727,16 +6694,17 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>DAYOFMONTH(<replaceable>date</replaceable>)</literal>
- </para>
-
- <para>
- Returns the day of the month for <replaceable>date</replaceable>, in
- the range <literal>1</literal> to <literal>31</literal>.
+ <listitem>
+ <para>
+
<literal>DAYOFMONTH(<replaceable>date</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the day of the month for <replaceable>date</replaceable>,
+ in the range <literal>1</literal> to
<literal>31</literal>.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic DAYOFMONTH -->
@@ -6759,18 +6727,18 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>DAYOFWEEK(<replaceable>date</replaceable>)</literal>
- </para>
-
- <para>
- Returns the weekday index for <replaceable>date</replaceable>
- (<literal>1</literal> = Sunday, <literal>2</literal> =
Monday, ...,
- <literal>7</literal> = Saturday). These index values correspond to
- the ODBC standard.
+ <listitem>
+ <para>
+
<literal>DAYOFWEEK(<replaceable>date</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the weekday index for <replaceable>date</replaceable>
+ (1 = Sunday, 2 = Monday, ..., 7 = Saturday). These index values
+ correspond to the ODBC standard.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic DAYOFWEEK -->
@@ -6799,7 +6767,7 @@
<para>
Returns the day of the year for <replaceable>date</replaceable>, in
- the range <literal>1</literal> to <literal>366</literal>.
+ the range 1 to 366.
<!-- end_description_for_help_topic -->
</para>
@@ -6825,10 +6793,11 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>EXTRACT(<replaceable>type</replaceable> FROM
- <replaceable>date</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+ <literal>EXTRACT(<replaceable>type</replaceable> FROM
+ <replaceable>date</replaceable>)</literal>
+ </para>
<para>
The <literal>EXTRACT()</literal> function uses the same kinds of
@@ -6890,10 +6859,10 @@
</para>
<para>
- <literal>FROM_DAYS()</literal> is not intended for use with values
- that precede the advent of the Gregorian calendar (1582), because it
- does not take into account the days that were lost when the calendar
- was changed. See <xref linkend="mysql-calendar"/>.
+ <literal>FROM_DAYS()</literal> is not intended for use with values
+ that precede the advent of the Gregorian calendar (1582), because
+ it does not take into account the days that were lost when the
+ calendar was changed. See <xref linkend="mysql-calendar"/>.
</para>
<para>
@@ -6934,11 +6903,11 @@
</para>
<para>
- If <replaceable>format</replaceable> is given, the result is
- formatted according to the <replaceable>format</replaceable> string.
- <replaceable>format</replaceable> may contain the same specifiers as
- those listed in the entry for the <literal>DATE_FORMAT()</literal>
- function.
+ If <replaceable>format</replaceable> is given, the result is
+ formatted according to the <replaceable>format</replaceable>
+ string. <replaceable>format</replaceable> may contain the same
+ specifiers as those listed in the entry for the
+ <literal>DATE_FORMAT()</literal> function.
<!-- end_description_for_help_topic -->
</para>
@@ -6971,30 +6940,24 @@
</para>
<para>
- Returns a format string. This function is useful in combination with
- the <literal>DATE_FORMAT()</literal> and the
- <literal>STR_TO_DATE()</literal> functions.
-
-<!-- Following is commented out because not yet implemented -->
- </para>
-
- <para>
-<!-- and when setting -->
- </para>
-
- <para>
-<!-- the server variables @code{DATE_FORMAT}, @code{TIME_FORMAT}, and -->
+ Returns a format string. This function is useful in combination
+ with the <literal>DATE_FORMAT()</literal> and the
+ <literal>STR_TO_DATE()</literal> functions.
</para>
- <para>
-<!-- @code{DATETIME_FORMAT}. -->
- </para>
+<!-- Following is commented out because not yet implemented: -->
+<!--
+ and when setting
+ the server variables <literal>DATE_FORMAT</literal>,
+ <literal>TIME_FORMAT</literal>, and
+ <literal>DATETIME_FORMAT</literal>.
+-->
<para>
- The three possible values for the first argument and the five
- possible values for the second argument result in 15 possible format
- strings (for the specifiers used, see the table in the
- <literal>DATE_FORMAT()</literal> function description).
+ The three possible values for the first argument and the five
+ possible values for the second argument result in 15 possible
+ format strings (for the specifiers used, see the table in the
+ <literal>DATE_FORMAT()</literal> function description).
</para>
<informaltable>
@@ -7003,162 +6966,89 @@
<colspec colwidth="40*"/>
<tbody>
<row>
- <entry>
- <emphasis role="bold">Function Call</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Result</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATE,'USA')</literal>
- </entry>
- <entry>
- <literal>'%m.%d.%Y'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATE,'JIS')</literal>
- </entry>
- <entry>
- <literal>'%Y-%m-%d'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATE,'ISO')</literal>
- </entry>
- <entry>
- <literal>'%Y-%m-%d'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATE,'EUR')</literal>
- </entry>
- <entry>
- <literal>'%d.%m.%Y'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATE,'INTERNAL')</literal>
- </entry>
- <entry>
- <literal>'%Y%m%d'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATETIME,'USA')</literal>
- </entry>
- <entry>
- <literal>'%Y-%m-%d-%H.%i.%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATETIME,'JIS')</literal>
- </entry>
- <entry>
- <literal>'%Y-%m-%d %H:%i:%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATETIME,'ISO')</literal>
- </entry>
- <entry>
- <literal>'%Y-%m-%d %H:%i:%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATETIME,'EUR')</literal>
- </entry>
- <entry>
- <literal>'%Y-%m-%d-%H.%i.%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(DATETIME,'INTERNAL')</literal>
- </entry>
- <entry>
- <literal>'%Y%m%d%H%i%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(TIME,'USA')</literal>
- </entry>
- <entry>
- <literal>'%h:%i:%s %p'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(TIME,'JIS')</literal>
- </entry>
- <entry>
- <literal>'%H:%i:%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(TIME,'ISO')</literal>
- </entry>
- <entry>
- <literal>'%H:%i:%s'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(TIME,'EUR')</literal>
- </entry>
- <entry>
- <literal>'%H.%i.%S'</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>GET_FORMAT(TIME,'INTERNAL')</literal>
- </entry>
- <entry>
- <literal>'%H%i%s'</literal>
- </entry>
+ <entry><emphasis role="bold">Function
Call</emphasis></entry>
+ <entry><emphasis role="bold">Result</emphasis></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATE,'USA')</literal></entry>
+ <entry><literal>'%m.%d.%Y'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATE,'JIS')</literal></entry>
+ <entry><literal>'%Y-%m-%d'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATE,'ISO')</literal></entry>
+ <entry><literal>'%Y-%m-%d'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATE,'EUR')</literal></entry>
+ <entry><literal>'%d.%m.%Y'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATE,'INTERNAL')</literal></entry>
+ <entry><literal>'%Y%m%d'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATETIME,'USA')</literal></entry>
+ <entry><literal>'%Y-%m-%d-%H.%i.%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATETIME,'JIS')</literal></entry>
+ <entry><literal>'%Y-%m-%d %H:%i:%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATETIME,'ISO')</literal></entry>
+ <entry><literal>'%Y-%m-%d %H:%i:%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATETIME,'EUR')</literal></entry>
+ <entry><literal>'%Y-%m-%d-%H.%i.%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(DATETIME,'INTERNAL')</literal></entry>
+ <entry><literal>'%Y%m%d%H%i%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(TIME,'USA')</literal></entry>
+ <entry><literal>'%h:%i:%s %p'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(TIME,'JIS')</literal></entry>
+ <entry><literal>'%H:%i:%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(TIME,'ISO')</literal></entry>
+ <entry><literal>'%H:%i:%s'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(TIME,'EUR')</literal></entry>
+ <entry><literal>'%H.%i.%S'</literal></entry>
+ </row>
+ <row>
+
<entry><literal>GET_FORMAT(TIME,'INTERNAL')</literal></entry>
+ <entry><literal>'%H%i%s'</literal></entry>
</row>
</tbody>
</tgroup>
</informaltable>
<para>
- ISO format is ISO 9075, not ISO 8601.
- </para>
-
- <para>
- In MySQL 5.0, <literal>TIMESTAMP</literal> can also be used;
- <literal>GET_FORMAT()</literal> returns the same values as for
- <literal>DATETIME</literal>.
- </para>
-
- <para>
-<!-- If the first argument of -->
- </para>
-
- <para>
-<!-- @code{GET_FORMAT()} is illegal or missing, or if the second argument -->
+ ISO format is ISO 9075, not ISO 8601.
</para>
<para>
-<!-- is missing, the function returns an error. If the second argument is illegal,
-->
+ In MySQL 5.0, <literal>TIMESTAMP</literal> can also be used;
+ <literal>GET_FORMAT()</literal> returns the same values as for
+ <literal>DATETIME</literal>.
</para>
- <para>
-<!-- the function returns @code{NULL}. -->
- </para>
+<!--
+ If the first argument of
+ @code{GET_FORMAT()} is illegal or missing, or if the second
+ argument is missing, the function returns an error. If the second
+ argument is illegal,the function returns @code{NULL}.
+-->
<para>
<!-- end_description_for_help_topic -->
@@ -7173,12 +7063,14 @@
mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
-> '03.10.2003'
mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
- -> 2003-10-31
-<!-- Following is commented out because not yet implemented -->
-
-<!-- mysql> SET DATE_FORMAT=GET_FORMAT(DATE, 'USA'); SELECT '2003-10-31';
-->
+ -> '2003-10-31'
+
+<!-- Following is commented out because not yet implemented: -->
-<!-- -> 10-31-2003 -->
+<!--
+mysql> SET DATE_FORMAT=GET_FORMAT(DATE, 'USA'); SELECT '2003-10-31';
+ -gt; '10-31-2003'
+-->
</programlisting>
@@ -7197,14 +7089,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>HOUR(<replaceable>time</replaceable>)</literal>
- </para>
-
- <para>
- Returns the hour for <replaceable>time</replaceable>. The range of
- the return value is <literal>0</literal> to
<literal>23</literal>
- for time-of-day values.
+ <listitem>
+ <para>
+ <literal>HOUR(<replaceable>time</replaceable>)</literal>
+ </para>
+
+ <para>
+ Returns the hour for <replaceable>time</replaceable>. The range
+ of the return value is 0 to 23 for time-of-day values.
<!-- end_description_for_help_topic -->
</para>
@@ -7221,9 +7113,9 @@
</para>
<para>
- However, the range of <literal>TIME</literal> values actually is
- much larger, so <literal>HOUR</literal> can return values greater
- than <literal>23</literal>.
+ However, the range of <literal>TIME</literal> values actually is
+ much larger, so <literal>HOUR</literal> can return values greater
+ than <literal>23</literal>.
<programlisting>
@@ -7640,8 +7532,8 @@
</para>
<para>
- Returns the quarter of the year for <replaceable>date</replaceable>,
- in the range <literal>1</literal> to <literal>4</literal>.
+ Returns the quarter of the year for
+ <replaceable>date</replaceable>, in the range
<literal>1</literal> to <literal>4</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -7740,12 +7632,15 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>STR_TO_DATE(<replaceable>str</replaceable>,<replaceable>format</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>STR_TO_DATE(<replaceable>str</replaceable>,<replaceable>format</replaceable>)</literal>
+ </para>
+ </listitem>
+ <listitem>
<para>
- This is the reverse function of the <literal>DATE_FORMAT()</literal>
+ This is the reverse of the <literal>DATE_FORMAT()</literal>
function. It takes a string <replaceable>str</replaceable> and a
format string <replaceable>format</replaceable>.
<literal>STR_TO_DATE()</literal> returns a
@@ -7755,49 +7650,22 @@
time parts.
</para>
- <para>
<!-- NOT YET IMPLEMENTED START -->
- </para>
-
- <para>
-<!-- If @var{format_str} is not specified, values are expected to be in the -->
- </para>
-
- <para>
-<!-- following format (for the specifiers used. (See the table in the -->
- </para>
-
- <para>
-<!-- @code{DATE_FORMAT()} function description.) -->
- </para>
-
- <para>
-<!-- @multitable @columnfractions .20 .65 -->
- </para>
-
- <para>
-<!-- @item @strong{Type} @tab @strong{Format} -->
- </para>
-
- <para>
-<!-- @item @code{DATE} @tab @code{'%Y-%m-%d'} -->
- </para>
-
- <para>
-<!-- @item @code{TIME} @tab @code{'%h-%m-%s'} -->
- </para>
- <para>
-<!-- @item @code{TIMESTAMP} @tab @code{'%Y-%m-%d %H:%i:%s'} -->
- </para>
-
- <para>
-<!-- @end multitable -->
- </para>
+<!--
+ If @var{format_str} is not specified, values are expected to be in
+ the following format (for the specifiers used. (See the table in
+ the @code{DATE_FORMAT()} function description.)
+
+ @multitable @columnfractions .20 .65
+ @item @strong{Type} @tab @strong{Format}
+ @item @code{DATE} @tab @code{'%Y-%m-%d'}
+ @item @code{TIME} @tab @code{'%h-%m-%s'}
+ @item @code{TIMESTAMP} @tab @code{'%Y-%m-%d %H:%i:%s'}
+ @end multitable
+-->
- <para>
<!-- NOT YET IMPLEMENTED STOP -->
- </para>
<para>
The date, time, or datetime values contained in
@@ -7826,13 +7694,11 @@
<!-- -> 2003-10-03 00:00:00 -->
-mysql> SELECT STR_TO_DATE('03.10.2003 09.20',
- -> '%d.%m.%Y %H.%i');
+mysql> SELECT STR_TO_DATE('03.10.2003 09.20','%d.%m.%Y %H.%i');
-> '2003-10-03 09:20:00'
mysql> SELECT STR_TO_DATE('10arp', '%carp');
-> '0000-10-00 00:00:00'
-mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00',
- -> '%Y-%m-%d %H:%i:%s');
+mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00','%Y-%m-%d %H:%i:%s');
-> NULL
</programlisting>
</para>
@@ -7955,8 +7821,7 @@
<para>
<programlisting>
-mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
- -> '1 1:1:1.000002');
+mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
-> '1997-12-30 22:58:58.999997'
mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
-> '-00:59:59.999999'
@@ -7994,13 +7859,14 @@
</indexterm>
</para></listitem>
- <listitem><para>
- <literal>TIME(<replaceable>expr</replaceable>)</literal>
- </para>
-
- <para>
- Extracts the time part of the time or datetime expression
- <replaceable>expr</replaceable>.
+ <listitem>
+ <para>
+ <literal>TIME(<replaceable>expr</replaceable>)</literal>
+ </para>
+
+ <para>
+ Extracts the time part of the time or datetime expression
+ <replaceable>expr</replaceable>.
<!-- end_description_for_help_topic -->
</para>
@@ -8077,12 +7943,12 @@
</para>
<para>
- With a single argument, this function returns the date or datetime
- expression <replaceable>expr</replaceable> as a datetime value. With
- two arguments, it adds the time expression
- <replaceable>expr2</replaceable> to the date or datetime expression
- <replaceable>expr</replaceable> and returns theresult as a datetime
- value.
+ With a single argument, this function returns the date or datetime
+ expression <replaceable>expr</replaceable> as a datetime value.
+ With two arguments, it adds the time expression
+ <replaceable>expr2</replaceable> to the date or datetime expression
+ <replaceable>expr</replaceable> and returns theresult as a datetime
+ value.
<!-- end_description_for_help_topic -->
</para>
@@ -8327,8 +8193,9 @@
</para>
<para>
- For dates before 1582, results from this function are not reliable.
- See <xref linkend="mysql-calendar"/>.
+ For dates before 1582 (and possibly a later year in other locales),
+ results from this function are not reliable. See
+ <xref linkend="mysql-calendar"/> for details.
</para>
<para>
@@ -8511,7 +8378,7 @@
</para>
<para>
- The function returns the week number for
+ This function returns the week number for
<replaceable>date</replaceable>. The two-argument form of
<literal>WEEK()</literal> allows you to specify whether the week
starts on Sunday or Monday and whether the return value should be in
@@ -8536,137 +8403,64 @@
<tbody>
<row>
<entry/>
- <entry>
- <emphasis role="bold">First day</emphasis>
- </entry>
+ <entry><emphasis role="bold">First
day</emphasis></entry>
<entry/>
- <entry></entry>
+ <entry/>
+ </row>
+ <row>
+ <entry><emphasis role="bold">Mode</emphasis></entry>
+ <entry><emphasis role="bold">of
week</emphasis></entry>
+ <entry><emphasis role="bold">Range</emphasis></entry>
+ <entry><emphasis role="bold">Week 1 is the first
+ week...</emphasis></entry>
+ </row>
+ <row>
+ <entry>0</entry>
+ <entry>Sunday</entry>
+ <entry>0-53</entry>
+ <entry>with a Sunday in this year</entry>
+ </row>
+ <row>
+ <entry>1</entry>
+ <entry>Monday</entry>
+ <entry>0-53</entry>
+ <entry>with more than 3 days this year</entry>
+ </row>
+ <row>
+ <entry>2</entry>
+ <entry>Sunday</entry>
+ <entry>1-53</entry>
+ <entry>with a Sunday in this year</entry>
+ </row>
+ <row>
+ <entry>3</entry>
+ <entry>Monday</entry>
+ <entry>1-53</entry>
+ <entry>with more than 3 days this year</entry>
</row>
<row>
- <entry>
- <emphasis role="bold">Mode</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">of week</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Range</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Week 1 is the first week...</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- 0
- </entry>
- <entry>
- Sunday
- </entry>
- <entry>
- 0-53
- </entry>
- <entry>
- with a Sunday in this year
- </entry>
- </row>
- <row>
- <entry>
- 1
- </entry>
- <entry>
- Monday
- </entry>
- <entry>
- 0-53
- </entry>
- <entry>
- with more than 3 days this year
- </entry>
- </row>
- <row>
- <entry>
- 2
- </entry>
- <entry>
- Sunday
- </entry>
- <entry>
- 1-53
- </entry>
- <entry>
- with a Sunday in this year
- </entry>
- </row>
- <row>
- <entry>
- 3
- </entry>
- <entry>
- Monday
- </entry>
- <entry>
- 1-53
- </entry>
- <entry>
- with more than 3 days this year
- </entry>
- </row>
- <row>
- <entry>
- 4
- </entry>
- <entry>
- Sunday
- </entry>
- <entry>
- 0-53
- </entry>
- <entry>
- with more than 3 days this year
- </entry>
- </row>
- <row>
- <entry>
- 5
- </entry>
- <entry>
- Monday
- </entry>
- <entry>
- 0-53
- </entry>
- <entry>
- with a Monday in this year
- </entry>
- </row>
- <row>
- <entry>
- 6
- </entry>
- <entry>
- Sunday
- </entry>
- <entry>
- 1-53
- </entry>
- <entry>
- with more than 3 days this year
- </entry>
- </row>
- <row>
- <entry>
- 7
- </entry>
- <entry>
- Monday
- </entry>
- <entry>
- 1-53
- </entry>
- <entry>
- with a Monday in this year
- </entry>
+ <entry>4</entry>
+ <entry>Sunday</entry>
+ <entry>0-53</entry>
+ <entry>with more than 3 days this year</entry>
+ </row>
+ <row>
+ <entry>5</entry>
+ <entry>Monday</entry>
+ <entry>0-53</entry>
+ <entry>with a Monday in this year</entry>
+ </row>
+ <row>
+ <entry>6</entry>
+ <entry>Sunday</entry>
+ <entry>1-53</entry>
+ <entry>with more than 3 days this year</entry>
+ </row>
+ <row>
+ <entry>7</entry>
+ <entry>Monday</entry>
+ <entry>1-53</entry>
+ <entry>with a Monday in this year</entry>
</row>
</tbody>
</tgroup>
@@ -8928,96 +8722,40 @@
<colspec colwidth="14*"/>
<tbody>
<row>
- <entry>
- Monday
- </entry>
- <entry>
- Tuesday
- </entry>
- <entry>
- Wednesday
- </entry>
- <entry>
- Thursday
- </entry>
- <entry>
- Friday
- </entry>
- <entry>
- Saturday
- </entry>
- <entry>
- Sunday
- </entry>
+ <entry>Monday</entry>
+ <entry>Tuesday</entry>
+ <entry>Wednesday</entry>
+ <entry>Thursday</entry>
+ <entry>Friday</entry>
+ <entry>Saturday</entry>
+ <entry>Sunday</entry>
</row>
<row>
- <entry>
- 1
- </entry>
- <entry>
- 2
- </entry>
- <entry>
- 3
- </entry>
- <entry>
- 4
- </entry>
- <entry>
- 15
- </entry>
- <entry>
- 16
- </entry>
- <entry>
- 17
- </entry>
+ <entry>1</entry>
+ <entry>2</entry>
+ <entry>3</entry>
+ <entry>4</entry>
+ <entry>15</entry>
+ <entry>16</entry>
+ <entry>17</entry>
</row>
<row>
- <entry>
- 18
- </entry>
- <entry>
- 19
- </entry>
- <entry>
- 20
- </entry>
- <entry>
- 21
- </entry>
- <entry>
- 22
- </entry>
- <entry>
- 23
- </entry>
- <entry>
- 24
- </entry>
+ <entry>18</entry>
+ <entry>19</entry>
+ <entry>20</entry>
+ <entry>21</entry>
+ <entry>22</entry>
+ <entry>23</entry>
+ <entry>24</entry>
</row>
<row>
- <entry>
- 25
- </entry>
- <entry>
- 26
- </entry>
- <entry>
- 27
- </entry>
- <entry>
- 28
- </entry>
- <entry>
- 29
- </entry>
- <entry>
- 30
- </entry>
- <entry>
- 31
- </entry>
+ <entry>25</entry>
+ <entry>26</entry>
+ <entry>27</entry>
+ <entry>28</entry>
+ <entry>29</entry>
+ <entry>30</entry>
+ <entry>31</entry>
</row>
</tbody>
</tgroup>
@@ -9049,7 +8787,7 @@
</para>
<!--
- TODO: change node name to use "Full-text"; by convention, we use a
+ TODO: Change node name to use "Full-text"; by convention, we use a
dash
-->
@@ -9426,23 +9164,17 @@
operators:
</para>
-<!-- Note: the operators are listed here in the order in -->
-
-<!-- which they appear in the ft_boolean_syntax variable -->
-
-<!-- (default value: + -><()~*:""&|) -->
-
-<!-- Exceptions: -->
-
-<!-- - The third item (no operator) that corresponds to the -->
-
-<!-- space is in the second position of the ft_boolean_syntax -->
-
-<!-- value. -->
-
-<!-- - The :, &, and | characters are omitted from the -->
-
-<!-- - following list. -->
+<!--
+ Note: the operators are listed here in the order in
+ which they appear in the ft_boolean_syntax variable
+ (default value: + -><()~*:""&|)
+ Exceptions:
+ - The third item (no operator) that corresponds to the
+ space is in the second position of the ft_boolean_syntax
+ value.
+ - The :, &, and | characters are omitted from the
+ following list.
+-->
<itemizedlist>
@@ -9527,18 +9259,18 @@
<para>
A phrase that is enclosed within double quote
- ('<literal>"</literal>') characters matches only rows that contain
- the phrase <emphasis>literally, as it was typed</emphasis>. The
- full-text engine splits the phrase into words, performs a search in
- the <literal>FULLTEXT</literal> index for the words. Before MySQL
- 5.0.3, the engine then performs a substring search for the phrase
- in the records that are found, so the match must include non-word
- characters in the phrase. As of MySQL 5.0.3, non-word characters
- need not be matched exactly: Phrase searching requires only that
- matches contain exactly the same words as the phrase and in the
- same order. For example, <literal>"test phrase"</literal> matches
- <literal>"test, phrase"</literal> as of MySQL 5.0.3, but not
- before.
+ ('<literal>"</literal>') characters matches only rows that
+ contain the phrase <emphasis>literally, as it was typed</emphasis>.
+ The full-text engine splits the phrase into words, performs a
+ search in the <literal>FULLTEXT</literal> index for the words.
+ Prior to MySQL 5.0.3, the engine then performed a substring search
+ for the phrase in the records that were found, so the match must
+ include non-word characters in the phrase. As of MySQL 5.0.3,
+ non-word characters need not be matched exactly: Phrase searching
+ requires only that matches contain exactly the same words as the
+ phrase and in the same order. For example, <literal>"test
+ phrase"</literal> matches <literal>"test, phrase"</literal> in
MySQL
+ 5.0.3, but not before.
</para>
<para>
@@ -9714,10 +9446,11 @@
</para></listitem>
<listitem><para>
- Ideographic languages such as Chinese and Japanese don't have word
- delimiters. You should be aware that the
- <literal>FULLTEXT</literal> parser cannot determine where words
- begin and end in these languages. Implications and workarounds are
+ Ideographic languages such as Chinese and Japanese do not have word
+ delimiters. Therefore, the <literal>FULLTEXT</literal> parser
+ <emphasis>cannot determine where words
+ begin and end in these and other such languages</emphasis>.
+ The implications of this and some workarounds for the problem are
described in <xref linkend="fulltext-search"/>.
</para></listitem>
@@ -9758,8 +9491,8 @@
<para>
Note that full-text search is carefully tuned for the most
effectiveness. Modifying the default behavior in most cases can
- actually decrease it. Do not alter the MySQL sources unless you know
- what you are doing!
+ actually decrease it. <emphasis>Do not alter the MySQL sources unless you know
+ what you are doing</emphasis>.
</para>
<para>
@@ -9822,7 +9555,7 @@
</programlisting>
<para>
- Change the line to this:
+ Change that line to this:
</para>
<programlisting>
@@ -9845,6 +9578,7 @@
variable also can be changed while the server is running, but you
must have the <literal>SUPER</literal> privilege to do so. No
rebuilding of indexes is necessary in this case.
+<!-- Don't change this to an xref due to capitalisation. -->
<link
linkend="server-system-variables">&title-server-system-variables;</link>
describes the rules governing how to set this variable.
</para></listitem>
@@ -9866,28 +9600,6 @@
mysql> REPAIR TABLE <replaceable>tbl_name</replaceable> QUICK;
</programlisting>
-<!-- TODO: See if this section can be dropped. -->
-<!--
- <para>
- With regard specifically to using the <literal>IN BOOLEAN
- MODE</literal> capability, if you upgrade from MySQL 3.23 to 4.0 or
- later, it's necessary to replace the index header as well. To do
- this, do a <literal>USE_FRM</literal> repair operation:
- </para>
-
-<programlisting>
-mysql> REPAIR TABLE <replaceable>tbl_name</replaceable> USE_FRM;
-</programlisting>
-
- <para>
- This is necessary because boolean full-text searches require a flag
- in the index header that was not present in MySQL 3.23, and that is
- not added if you do only a <literal>QUICK</literal> repair. If you
- attempt a boolean full-text search without rebuilding the indexes
- this way, the search returns incorrect results.
- </para>
--->
-
<para>
Note that if you use <command>myisamchk</command> to perform an
operation that modifies table indexes (such as repair or analyze),
@@ -10661,7 +10373,8 @@
Because AES is a block-level algorithm, padding is used to encode
uneven length strings and so the result string length may be
calculated as
-
<literal>16*(trunc(<replaceable>string_length</replaceable>/16)+1)</literal>.
+ <literal>16 * (trunc(<replaceable>string_length</replaceable> /
16)
+ + 1)</literal>.
</para>
<para>
@@ -10690,9 +10403,9 @@
</para>
<para>
- You can get even more security by not transferring the key over the
- connection for each query, which can be accomplished by storing it
- in a server-side variable at connection time. For example:
+ You can get even better security by not transferring the key over
+ the connection for each query, which can be accomplished by storing
+ it in a server-side variable at connection time. For example:
<programlisting>
SELECT @password:='my password';
@@ -10841,37 +10554,22 @@
<colspec colwidth="65*"/>
<tbody>
<row>
- <entry>
- <emphasis role="bold">Argument</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Description</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- No argument
- </entry>
- <entry>
- The first key from the DES key file is used.
- </entry>
- </row>
- <row>
- <entry>
- <replaceable>key_num</replaceable>
- </entry>
- <entry>
- The given key number (0-9) from the DES key file is used.
- </entry>
- </row>
- <row>
- <entry>
- <replaceable>key_str</replaceable>
- </entry>
- <entry>
- The given key string is used to encrypt
- <replaceable>str</replaceable>.
- </entry>
+ <entry><emphasis
role="bold">Argument</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry>No argument</entry>
+ <entry>The first key from the DES key file is used.</entry>
+ </row>
+ <row>
+ <entry><replaceable>key_num</replaceable></entry>
+ <entry>The given key number (0-9) from the DES key file is
+ used.</entry>
+ </row>
+ <row>
+ <entry><replaceable>key_str</replaceable></entry>
+ <entry>The given key string is used to encrypt
+ <replaceable>str</replaceable>.</entry>
</row>
</tbody>
</tgroup>
@@ -10941,8 +10639,8 @@
<para>
<programlisting>
-mysql> SELECT customer_address FROM customer_table WHERE
- crypted_credit_card = DES_ENCRYPT('credit_card_number');
+mysql> SELECT customer_address FROM customer_table
+ > WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
</programlisting>
</para>
@@ -10989,11 +10687,11 @@
</para>
<para>
- If <literal>crypt()</literal> is not available on your system,
- <literal>ENCRYPT()</literal> always returns
- <literal>NULL</literal>. Because of this, we recommend that you use
- <literal>MD5()</literal> or <literal>SHA1()</literal>
instead,
- because those two functions exist on all platforms.
+ If <literal>crypt()</literal> is not available on your system (such
+ as is the case with Windows), <literal>ENCRYPT()</literal> always
+ returns <literal>NULL</literal>. Because of this, we recommend that
+ you use <literal>MD5()</literal> or
<literal>SHA1()</literal>
+ instead, because those two functions exist on all platforms.
</para>
<para>
@@ -11057,12 +10755,14 @@
</para>
<para>
- <literal>OLD_PASSWORD()</literal> is was added in MySQL 4.1,when
- the implementation of <literal>PASSWORD()</literal> was changed to
- improve security. <literal>OLD_PASSWORD()</literal> returns the
- value of the pre-4.1 implementation of
- <literal>PASSWORD()</literal>. See
- <xref linkend="password-hashing"/>.
+ <literal>OLD_PASSWORD()</literal> is was added in MySQL 4.1,when
+ the implementation of <literal>PASSWORD()</literal> was changed to
+ improve security. <literal>OLD_PASSWORD()</literal> returns the
+ value of the pre-4.1 implementation of
+ <literal>PASSWORD()</literal>, and is intended to permit you to
+ reset passwords for any pre-4.1 clients that need to connect to
+ your version 4.1 or later MySQL server without locking them out.
+ See <xref linkend="password-hashing"/>.
</para>
<para>
@@ -11237,10 +10937,10 @@
</para>
<para>
- The time reported is elapsed time on the client end, not CPU time
- on the server end. It is advisable to execute
- <literal>BENCHMARK()</literal> several times, and to interpret the
- result with regard to how heavily loaded the server machine is.
+ The time reported is elapsed time on the client end, not CPU time
+ on the server end. It is advisable to execute
+ <literal>BENCHMARK()</literal> several times, and to interpret the
+ result with regard to how heavily loaded the server machine is.
</para>
<para>
@@ -11258,7 +10958,7 @@
</para>
<para>
- Returns the character set of the string argument.
+ Returns the character set of the string argument.
<!-- end_description_for_help_topic -->
</para>
@@ -11288,15 +10988,16 @@
</indexterm>
</para></listitem>
- <listitem><para>
-
<literal>COERCIBILITY(<replaceable>str</replaceable>)</literal>
- </para>
+ <listitem>
+ <para>
+
<literal>COERCIBILITY(<replaceable>str</replaceable>)</literal>
+ </para>
- <para>
- Returns the collation coercibility value of the string argument.
+ <para>
+ Returns the collation coercibility value of the string argument.
<!-- end_description_for_help_topic -->
- </para>
+ </para>
<para>
<!-- example_for_help_topic COERCIBILITY -->
@@ -11324,82 +11025,41 @@
<colspec colwidth="70*"/>
<tbody>
<row>
- <entry>
- <emphasis role="bold">Coercibility</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Meaning</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Example</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>0</literal>
- </entry>
- <entry>
- Explicit collation
- </entry>
- <entry>
- Value with <literal>COLLATE</literal> clause
- </entry>
- </row>
- <row>
- <entry>
- <literal>1</literal>
- </entry>
- <entry>
- No collation
- </entry>
- <entry>
- Concatenation of strings with different collations
- </entry>
- </row>
- <row>
- <entry>
- <literal>2</literal>
- </entry>
- <entry>
- Implicit collation
- </entry>
- <entry>
- Column value
- </entry>
- </row>
- <row>
- <entry>
- <literal>3</literal>
- </entry>
- <entry>
- System constant
- </entry>
- <entry>
- <literal>USER()</literal> return value
- </entry>
- </row>
- <row>
- <entry>
- <literal>4</literal>
- </entry>
- <entry>
- Coercible
- </entry>
- <entry>
- Literal string
- </entry>
- </row>
- <row>
- <entry>
- <literal>5</literal>
- </entry>
- <entry>
- Ignorable
- </entry>
- <entry>
- <literal>NULL</literal> or an expression derived from
- <literal>NULL</literal>
- </entry>
+ <entry><emphasis
role="bold">Coercibility</emphasis></entry>
+ <entry><emphasis
role="bold">Meaning</emphasis></entry>
+ <entry><emphasis
role="bold">Example</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>0</literal></entry>
+ <entry>Explicit collation</entry>
+ <entry>Value with <literal>COLLATE</literal>
clause</entry>
+ </row>
+ <row>
+ <entry><literal>1</literal></entry>
+ <entry>No collation</entry>
+ <entry>Concatenation of strings with different
+ collations</entry>
+ </row>
+ <row>
+ <entry><literal>2</literal></entry>
+ <entry>Implicit collation</entry>
+ <entry>Column value</entry>
+ </row>
+ <row>
+ <entry><literal>3</literal></entry>
+ <entry>System constant</entry>
+ <entry><literal>USER()</literal> return value</entry>
+ </row>
+ <row>
+ <entry><literal>4</literal></entry>
+ <entry>Coercible</entry>
+ <entry>Literal string</entry>
+ </row>
+ <row>
+ <entry><literal>5</literal></entry>
+ <entry>Ignorable</entry>
+ <entry><literal>NULL</literal> or an expression derived
from
+ <literal>NULL</literal></entry>
</row>
</tbody>
</tgroup>
@@ -11418,66 +11078,36 @@
<colspec colwidth="70*"/>
<tbody>
<row>
- <entry>
- <emphasis role="bold">Coercibility</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Meaning</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Example</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>0</literal>
- </entry>
- <entry>
- Explicit collation
- </entry>
- <entry>
- Value with <literal>COLLATE</literal> clause
- </entry>
- </row>
- <row>
- <entry>
- <literal>1</literal>
- </entry>
- <entry>
- No collation
- </entry>
- <entry>
- Concatenation of strings with different collations
- </entry>
- </row>
- <row>
- <entry>
- <literal>2</literal>
- </entry>
- <entry>
- Implicit collation
- </entry>
- <entry>
- Column value
- </entry>
- </row>
- <row>
- <entry>
- <literal>3</literal>
- </entry>
- <entry>
- Coercible
- </entry>
- <entry>
- Literal string
- </entry>
+ <entry><emphasis
role="bold">Coercibility</emphasis></entry>
+ <entry><emphasis
role="bold">Meaning</emphasis></entry>
+ <entry><emphasis
role="bold">Example</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>0</literal></entry>
+ <entry>Explicit collation</entry>
+ <entry>Value with <literal>COLLATE</literal>
clause</entry>
+ </row>
+ <row>
+ <entry><literal>1</literal></entry>
+ <entry>No collation</entry>
+ <entry>Concatenation of strings with different collations</entry>
+ </row>
+ <row>
+ <entry><literal>2</literal></entry>
+ <entry>Implicit collation</entry>
+ <entry>Column value</entry>
+ </row>
+ <row>
+ <entry><literal>3</literal></entry>
+ <entry>Coercible</entry>
+ <entry>Literal string</entry>
</row>
</tbody>
</tgroup>
</informaltable>
<para>
- Lower values have higher precedence.
+ Lower values have higher precedence.
</para>
<para>
@@ -11560,10 +11190,10 @@
</para>
<para>
- Returns the username and hostname combination that the current
- session was authenticated as. This value corresponds to the MySQL
- account that determines your access privileges. It can be different
- from the value of <literal>USER()</literal>.
+ Returns the username and hostname combination that the current
+ session was authenticated as. This value corresponds to the MySQL
+ account that determines your access privileges. It can be
+ different from the value of <literal>USER()</literal>.
<!-- end_description_for_help_topic -->
</para>
@@ -11585,19 +11215,19 @@
</para>
<para>
- The example illustrates that although the client specified a
- username of <literal>davida</literal> (as indicated by the value of
- the <literal>USER()</literal> function), the server authenticated
- the client using an anonymous user account (as seen by the empty
- username part of the <literal>CURRENT_USER()</literal> value). One
- way this might occur is that there is no account listed in the
- grant tables for <literal>davida</literal>.
+ The example illustrates that although the client specified a
+ username of <literal>davida</literal> (as indicated by the value of
+ the <literal>USER()</literal> function), the server authenticated
+ the client using an anonymous user account (as seen by the empty
+ username part of the <literal>CURRENT_USER()</literal> value). One
+ way this might occur is that there is no account listed in the
+ grant tables for <literal>davida</literal>.
</para>
<para>
- In MySQL 5.0, the string returned by
- <literal>CURRENT_USER()</literal> uses the
<literal>utf8</literal>
- character set.
+ In MySQL 5.0, the string returned by
+ <literal>CURRENT_USER()</literal> uses the
+ <literal>utf8</literal> character set.
</para>
<para>
@@ -11615,8 +11245,8 @@
</para>
<para>
- Returns the default (current) database name. In MySQL 5.0, the
- string has the <literal>utf8</literal> character set.
+ Returns the default (current) database name. In MySQL 5.0, the
+ string has the <literal>utf8</literal> character set.
<!-- end_description_for_help_topic -->
</para>
@@ -11654,15 +11284,15 @@
</para>
<para>
- A <literal>SELECT</literal> statement may include a
- <literal>LIMIT</literal> clause to restrict the number of rows the
- server returns to the client. In some cases, it is desirable to
- know how many rows the statement would have returned without the
- <literal>LIMIT</literal>, but without running the statement again.
- To get this row count, include a
- <literal>SQL_CALC_FOUND_ROWS</literal> option in the
- <literal>SELECT</literal> statement, then invoke
- <literal>FOUND_ROWS()</literal> afterward:
+ A <literal>SELECT</literal> statement may include a
+ <literal>LIMIT</literal> clause to restrict the number of rows the
+ server returns to the client. In some cases, it is desirable to
+ know how many rows the statement would have returned without the
+ <literal>LIMIT</literal>, but without running the statement again.
+ To get this row count, include a
+ <literal>SQL_CALC_FOUND_ROWS</literal> option in the
+ <literal>SELECT</literal> statement, then invoke
+ <literal>FOUND_ROWS()</literal> afterward:
<!-- end_description_for_help_topic -->
</para>
@@ -11680,53 +11310,53 @@
</para>
<para>
- The second <literal>SELECT</literal> returns a number indicating
- how many rows the first <literal>SELECT</literal> would have
- returned had it been written without the <literal>LIMIT</literal>
- clause. (If the preceding <literal>SELECT</literal> statement does
- not include the <literal>SQL_CALC_FOUND_ROWS</literal> option, then
- <literal>FOUND_ROWS()</literal> may return a different result when
- <literal>LIMIT</literal> is used than when it is not.)
+ The second <literal>SELECT</literal> returns a number indicating
+ how many rows the first <literal>SELECT</literal> would have
+ returned had it been written without the <literal>LIMIT</literal>
+ clause. (If the preceding <literal>SELECT</literal> statement does
+ not include the <literal>SQL_CALC_FOUND_ROWS</literal> option,
+ then <literal>FOUND_ROWS()</literal> may return a different result
+ when <literal>LIMIT</literal> is used than when it is not.)
</para>
<para>
- Note that if you are using <literal>SELECT
- SQL_CALC_FOUND_ROWS</literal>, MySQL must calculate how many rows
- are in the full result set. However, this is faster than running
- the query again without <literal>LIMIT</literal>, because the
- result set need not be sent to the client.
+ Note that if you are using <literal>SELECT
+ SQL_CALC_FOUND_ROWS</literal>, MySQL must calculate how many rows
+ are in the full result set. However, this is faster than running
+ the query again without <literal>LIMIT</literal>, because the
+ result set need not be sent to the client.
</para>
<para>
- <literal>SQL_CALC_FOUND_ROWS</literal> and
- <literal>FOUND_ROWS()</literal> can be useful in situations when
- you want to restrict the number of rows that a query returns, but
- also determine the number of rows in the full result set without
- running the query again. An example is a Web script that presents a
- paged display containing links to the pages that show other
- sections of a search result. Using <literal>FOUND_ROWS()</literal>
- allows you to determine how many other pages are needed for the
- rest of the result.
+ <literal>SQL_CALC_FOUND_ROWS</literal> and
+ <literal>FOUND_ROWS()</literal> can be useful in situations when
+ you want to restrict the number of rows that a query returns, but
+ also determine the number of rows in the full result set without
+ running the query again. An example is a Web script that presents
+ a paged display containing links to the pages that show other
+ sections of a search result. Using <literal>FOUND_ROWS()</literal>
+ allows you to determine how many other pages are needed for the
+ rest of the result.
</para>
<para>
- The use of <literal>SQL_CALC_FOUND_ROWS</literal> and
- <literal>FOUND_ROWS()</literal> is more complex for
- <literal>UNION</literal> queries than for simple
- <literal>SELECT</literal> statements, because
- <literal>LIMIT</literal> may occur at multiple places in a
- <literal>UNION</literal>. It may be applied to individual
- <literal>SELECT</literal> statements in the
- <literal>UNION</literal>, or global to the
<literal>UNION</literal>
- result as a whole.
+ The use of <literal>SQL_CALC_FOUND_ROWS</literal> and
+ <literal>FOUND_ROWS()</literal> is more complex for
+ <literal>UNION</literal> queries than for simple
+ <literal>SELECT</literal> statements, because
+ <literal>LIMIT</literal> may occur at multiple places in a
+ <literal>UNION</literal>. It may be applied to individual
+ <literal>SELECT</literal> statements in the
+ <literal>UNION</literal>, or global to the
+ <literal>UNION</literal> result as a whole.
</para>
<para>
- The intent of <literal>SQL_CALC_FOUND_ROWS</literal> for
- <literal>UNION</literal> is that it should return the row count
- that would be returned without a global <literal>LIMIT</literal>.
- The conditions for use of <literal>SQL_CALC_FOUND_ROWS</literal>
- with <literal>UNION</literal> are:
+ The intent of <literal>SQL_CALC_FOUND_ROWS</literal> for
+ <literal>UNION</literal> is that it should return the row count
+ that would be returned without a global <literal>LIMIT</literal>.
+ The conditions for use of <literal>SQL_CALC_FOUND_ROWS</literal>
+ with <literal>UNION</literal> are:
</para>
<para>
@@ -11790,15 +11420,15 @@
</para>
<para>
- The last ID that was generated is maintained in the server on a
- per-connection basis. This means the value the function returns to
- a given client is the most recent <literal>AUTO_INCREMENT</literal>
- value generated by that client. The value cannot be affected by
- other clients, even if they generate
- <literal>AUTO_INCREMENT</literal> values of their own. This
- behavior ensures that you can retrieve your own ID without concern
- for the activity of other clients, and without the need for locks
- or transactions.
+ The last ID that was generated is maintained in the server on a
+ per-connection basis. This means the value the function returns
+ to a given client is the most recent
+ <literal>AUTO_INCREMENT</literal> value generated by that client.
+ The value cannot be affected by other clients, even if they
+ generate <literal>AUTO_INCREMENT</literal> values of their own.
+ This behavior ensures that you can retrieve your own ID without
+ concern for the activity of other clients, and without the need
+ for locks or transactions.
</para>
<para>
@@ -12125,7 +11755,7 @@
<para>
Returns the default value for a table column. Starting with MySQL
- 5.0.2, you get an error if the column has no default value.
+ 5.0.2, an error results if the column has no default value.
<!-- end_description_for_help_topic -->
</para>
@@ -12198,28 +11828,29 @@
</para>
<para>
- Tries to obtain a lock with a name given by the string
- <replaceable>str</replaceable>, with a timeout of
- <replaceable>timeout</replaceable> seconds. Returns
- <literal>1</literal> if the lock was obtained successfully,
- <literal>0</literal> if the attempt timed out (for example, because
- another client has previously locked the name), or
- <literal>NULL</literal> if an error occurred (such as running out
- of memory or the thread was killed with <command>mysqladmin
- kill</command>). If you have a lock obtained with
- <literal>GET_LOCK()</literal>, it is released when you execute
- <literal>RELEASE_LOCK()</literal>, execute a new
- <literal>GET_LOCK()</literal>, or your connection terminates
- (either normally or abnormally).
+ Tries to obtain a lock with a name given by the string
+ <replaceable>str</replaceable>, with a timeout of
+ <replaceable>timeout</replaceable> seconds. Returns
+ <literal>1</literal> if the lock was obtained successfully,
+ <literal>0</literal> if the attempt timed out (for example,
+ because another client has previously locked the name), or
+ <literal>NULL</literal> if an error occurred (such as running out
+ of memory or the thread was killed with <command>mysqladmin
+ kill</command>). If you have a lock obtained with
+ <literal>GET_LOCK()</literal>, it is released when you execute
+ <literal>RELEASE_LOCK()</literal>, execute a new
+ <literal>GET_LOCK()</literal>, or your connection terminates
+ (either normally or abnormally).
</para>
<para>
- This function can be used to implement application locks or to
- simulate record locks. Names are locked on a server-wide basis. If
- a name has been locked by one client, <literal>GET_LOCK()</literal>
- blocks any request by another client for a lock with the same name.
- This allows clients that agree on a given lock name to use the name
- to perform cooperative advisory locking.
+ This function can be used to implement application locks or to
+ simulate record locks. Names are locked on a server-wide basis. If
+ a name has been locked by one client,
+ <literal>GET_LOCK()</literal> blocks any request by another client
+ for a lock with the same name. This allows clients that agree on a
+ given lock name to use the name to perform cooperative advisory
+ locking.
<!-- end_description_for_help_topic -->
</para>
@@ -12244,10 +11875,10 @@
</para>
<para>
- Note that the second <literal>RELEASE_LOCK()</literal> call returns
- <literal>NULL</literal> because the lock
<literal>'lock1'</literal>
- was automatically released by the second
- <literal>GET_LOCK()</literal> call.
+ Note that the second <literal>RELEASE_LOCK()</literal> call
+ returns <literal>NULL</literal> because the lock
+ <literal>'lock1'</literal> was automatically released by the
+ second <literal>GET_LOCK()</literal> call.
</para>
<para>
@@ -12445,20 +12076,20 @@
</para>
<para>
- Releases the lock named by the string
- <replaceable>str</replaceable> that was obtained with
- <literal>GET_LOCK()</literal>. Returns <literal>1</literal>
if the
- lock was released, <literal>0</literal> if the lock wasn't locked
- by this thread (in which case the lock is not released), and
- <literal>NULL</literal> if the named lock didn't exist. The lock
- does not exist if it was never obtained by a call to
- <literal>GET_LOCK()</literal> or if it has previously been
- released.
+ Releases the lock named by the string
+ <replaceable>str</replaceable> that was obtained with
+ <literal>GET_LOCK()</literal>. Returns <literal>1</literal>
if the
+ lock was released, <literal>0</literal> if the lock wasn't locked
+ by this thread (in which case the lock is not released), and
+ <literal>NULL</literal> if the named lock didn't exist. The lock
+ does not exist if it was never obtained by a call to
+ <literal>GET_LOCK()</literal> or if it has previously been
+ released.
</para>
<para>
- The <literal>DO</literal> statement is convenient to use with
- <literal>RELEASE_LOCK()</literal>. See <xref linkend="do"/>.
+ The <literal>DO</literal> statement is convenient to use with
+ <literal>RELEASE_LOCK()</literal>. See <xref linkend="do"/>.
<!-- end_description_for_help_topic -->
</para>
@@ -12759,13 +12390,12 @@
</para>
<para>
- This optimization applies only to <literal>MyISAM</literal> and
- <literal>ISAM</literal> tables only, because an exact record count
- is stored for these table types and can be accessed very quickly.
- For transactional storage engines (<literal>InnoDB</literal>,
- <literal>BDB</literal>), storing an exact row count is more
- problematic because multiple transactions may be occurring, each of
- which may affect the count.
+ This optimization applies only to <literal>MyISAM</literal> tables
+ only, because an exact record count is stored for these table
+ types and can be accessed very quickly. For transactional storage
+ engines (<literal>InnoDB</literal>,
<literal>BDB</literal>),
+ storing an exact row count is more problematic because multiple
+ transactions may be occurring, each of which may affect the count.
</para>
<para>
@@ -12905,8 +12535,8 @@
</para>
<!--
- Commented out until someone can say what GROUP_CONCAT() can do that
- LIST() cannot...
+ Commented out until someone can say what GROUP_CONCAT() can do
+ that LIST() cannot...
-->
<!--
@@ -12914,7 +12544,7 @@
The <literal>GROUP_CONCAT()</literal> function is an enhanced
implementation of the basic <literal>LIST()</literal> function
supported by Sybase SQL Anywhere. It is backward compatible with
- the extremely limited functionality of <literal>LIST()</literal>
+ the extremely limited functionality of <literal>LIST()</literal>
if only one column and no other options are specified.
<literal>LIST()</literal> does have a default sorting order.
</para>
@@ -12953,15 +12583,15 @@
</para>
<para>
- Returns the minimum or maximum value of
- <replaceable>expr</replaceable>. <literal>MIN()</literal>
and
- <literal>MAX()</literal> may take a string argument; in such cases
- they return the minimum or maximum string value. See
- <xref linkend="mysql-indexes"/>. The <literal>DISTINCT</literal>
- keyword can be used as of MySQL 5.0.0 to find the minimum or
- maximum of the distinct values of <replaceable>expr</replaceable>;
- this is supported, but produces the same result as omitting
- <literal>DISTINCT</literal>.
+ Returns the minimum or maximum value of
