Author: paul
Date: 2008-05-19 17:56:46 +0200 (Mon, 19 May 2008)
New Revision: 10770
Log:
r31490@frost: paul | 2008-05-19 10:51:42 -0500
Update 'problems with string comparisons' section with better
post-4.0 information.
Modified:
trunk/it/refman-5.1/errors-problems-core.xml
trunk/pt/refman-5.1/errors-problems-core.xml
trunk/refman-4.1/errors-problems.xml
trunk/refman-5.0/errors-problems.xml
trunk/refman-5.1/errors-problems-core.xml
trunk/refman-6.0/errors-problems.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:31489
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:31325
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:31490
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:31325
Modified: trunk/it/refman-5.1/errors-problems-core.xml
===================================================================
--- trunk/it/refman-5.1/errors-problems-core.xml 2008-05-19 15:56:40 UTC (rev 10769)
+++ trunk/it/refman-5.1/errors-problems-core.xml 2008-05-19 15:56:46 UTC (rev 10770)
Changed blocks: 3, Lines Added: 101, Lines Deleted: 14; 5689 bytes
@@ -3625,7 +3625,7 @@
<section id="case-sensitivity">
- <title>Case Sensitivity in Searches</title>
+ <title>Case Sensitivity in String Searches</title>
<indexterm>
<primary>case sensitivity</primary>
@@ -3637,17 +3637,23 @@
<secondary>and case sensitivity</secondary>
</indexterm>
- <indexterm>
- <primary>Chinese</primary>
- </indexterm>
+ <para>
+ For non-binary strings (<literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, <literal>TEXT</literal>), string
+ searches use the collation of the comparison operands. For
+ binary strings (<literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>),
+ comparisons use the numeric values of the bytes in the
+ operands; this means that for alphabetic characters,
+ comparisons will be case sensitive.
+ </para>
- <indexterm>
- <primary>Big5 Chinese character encoding</primary>
- </indexterm>
-
<para>
- By default, MySQL searches are not case sensitive. This means
- that if you search with
+ The default character set and collation are
+ <literal>latin1</literal> and
+ <literal>latin1_swedish_ci</literal>, so non-binary string
+ comparisons are case insensitive by default. This means that
+ if you search with
<literal><replaceable>col_name</replaceable> LIKE
'a%'</literal>, you get all column values that start with
<literal>A</literal> or <literal>a</literal>. If you want to
@@ -3674,13 +3680,94 @@
</para>
<para>
+ For comparison of non-binary strings that are case sensitive,
+ you can use <literal>COLLATE</literal> to indicate that a
+ case-insensitive collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = 'New York' COLLATE latin1_bin;</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT @s COLLATE latin1_swedish_ci = 'new york';</userinput>
++-------------------------------------------+
+| @s COLLATE latin1_swedish_ci = 'new york' |
++-------------------------------------------+
+| 1 |
++-------------------------------------------+
+</programlisting>
+
+ <para>
+ For comparison of binary strings that are case sensitive, you
+ can convert the string to a non-binary string and use
+ <literal>COLLATE</literal> to indicate that a case-insensitive
+ collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = BINARY 'New York';</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york';</userinput>
++-----------------------------------------------------------------+
+| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york' |
++-----------------------------------------------------------------+
+| 1 |
++-----------------------------------------------------------------+
+</programlisting>
+
+ <para>
+ If you need to determine whether a value will compare as a
+ non-binary or binary string, use the
+ <function role="sql">COLLATION()</function> function. This
+ example shows that <function role="sql">VERSION()</function>
+ returns a string that has a case-insensitive collation, so
+ comparisons are case insensitive:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(VERSION());</userinput>
++----------------------+
+| COLLATION(VERSION()) |
++----------------------+
+| utf8_general_ci |
++----------------------+
+</programlisting>
+
+ <para>
+ For binary strings, the collation value is
+ <literal>binary</literal>, so so comparisons will be case
+ sensitive. One context in which you will see this value is for
+ compression and encryption functions, which return binary
+ strings as a general rule. The following example shows that
+ <function role="sql">SHA1()</function> returns a binary
+ string:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(SHA1('x'));</userinput>
++----------------------+
+| COLLATION(SHA1('x')) |
++----------------------+
+| binary |
++----------------------+
+</programlisting>
+
+ <para>
Simple comparison operations (<literal>>=, >, =, <,
<=</literal>, sorting, and grouping) are based on each
character's <quote>sort value.</quote> Characters with the
- same sort value (such as <quote><literal>E</literal></quote>,
- <quote><literal>e</literal></quote>, and
- <quote><literal>é</literal></quote>) are treated as the
- same character.
+ same sort value (such as <quote><literal>e</literal></quote>
+ and <quote><literal>é</literal></quote>) are treated as
+ the same character.
</para>
</section>
Modified: trunk/pt/refman-5.1/errors-problems-core.xml
===================================================================
--- trunk/pt/refman-5.1/errors-problems-core.xml 2008-05-19 15:56:40 UTC (rev 10769)
+++ trunk/pt/refman-5.1/errors-problems-core.xml 2008-05-19 15:56:46 UTC (rev 10770)
Changed blocks: 3, Lines Added: 101, Lines Deleted: 14; 5689 bytes
@@ -3625,7 +3625,7 @@
<section id="case-sensitivity">
- <title>Case Sensitivity in Searches</title>
+ <title>Case Sensitivity in String Searches</title>
<indexterm>
<primary>case sensitivity</primary>
@@ -3637,17 +3637,23 @@
<secondary>and case sensitivity</secondary>
</indexterm>
- <indexterm>
- <primary>Chinese</primary>
- </indexterm>
+ <para>
+ For non-binary strings (<literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, <literal>TEXT</literal>), string
+ searches use the collation of the comparison operands. For
+ binary strings (<literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>),
+ comparisons use the numeric values of the bytes in the
+ operands; this means that for alphabetic characters,
+ comparisons will be case sensitive.
+ </para>
- <indexterm>
- <primary>Big5 Chinese character encoding</primary>
- </indexterm>
-
<para>
- By default, MySQL searches are not case sensitive. This means
- that if you search with
+ The default character set and collation are
+ <literal>latin1</literal> and
+ <literal>latin1_swedish_ci</literal>, so non-binary string
+ comparisons are case insensitive by default. This means that
+ if you search with
<literal><replaceable>col_name</replaceable> LIKE
'a%'</literal>, you get all column values that start with
<literal>A</literal> or <literal>a</literal>. If you want to
@@ -3674,13 +3680,94 @@
</para>
<para>
+ For comparison of non-binary strings that are case sensitive,
+ you can use <literal>COLLATE</literal> to indicate that a
+ case-insensitive collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = 'New York' COLLATE latin1_bin;</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT @s COLLATE latin1_swedish_ci = 'new york';</userinput>
++-------------------------------------------+
+| @s COLLATE latin1_swedish_ci = 'new york' |
++-------------------------------------------+
+| 1 |
++-------------------------------------------+
+</programlisting>
+
+ <para>
+ For comparison of binary strings that are case sensitive, you
+ can convert the string to a non-binary string and use
+ <literal>COLLATE</literal> to indicate that a case-insensitive
+ collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = BINARY 'New York';</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york';</userinput>
++-----------------------------------------------------------------+
+| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york' |
++-----------------------------------------------------------------+
+| 1 |
++-----------------------------------------------------------------+
+</programlisting>
+
+ <para>
+ If you need to determine whether a value will compare as a
+ non-binary or binary string, use the
+ <function role="sql">COLLATION()</function> function. This
+ example shows that <function role="sql">VERSION()</function>
+ returns a string that has a case-insensitive collation, so
+ comparisons are case insensitive:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(VERSION());</userinput>
++----------------------+
+| COLLATION(VERSION()) |
++----------------------+
+| utf8_general_ci |
++----------------------+
+</programlisting>
+
+ <para>
+ For binary strings, the collation value is
+ <literal>binary</literal>, so so comparisons will be case
+ sensitive. One context in which you will see this value is for
+ compression and encryption functions, which return binary
+ strings as a general rule. The following example shows that
+ <function role="sql">SHA1()</function> returns a binary
+ string:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(SHA1('x'));</userinput>
++----------------------+
+| COLLATION(SHA1('x')) |
++----------------------+
+| binary |
++----------------------+
+</programlisting>
+
+ <para>
Simple comparison operations (<literal>>=, >, =, <,
<=</literal>, sorting, and grouping) are based on each
character's <quote>sort value.</quote> Characters with the
- same sort value (such as <quote><literal>E</literal></quote>,
- <quote><literal>e</literal></quote>, and
- <quote><literal>é</literal></quote>) are treated as the
- same character.
+ same sort value (such as <quote><literal>e</literal></quote>
+ and <quote><literal>é</literal></quote>) are treated as
+ the same character.
</para>
</section>
Modified: trunk/refman-4.1/errors-problems.xml
===================================================================
--- trunk/refman-4.1/errors-problems.xml 2008-05-19 15:56:40 UTC (rev 10769)
+++ trunk/refman-4.1/errors-problems.xml 2008-05-19 15:56:46 UTC (rev 10770)
Changed blocks: 3, Lines Added: 101, Lines Deleted: 14; 5665 bytes
@@ -3732,7 +3732,7 @@
<section id="case-sensitivity">
- <title>Case Sensitivity in Searches</title>
+ <title>Case Sensitivity in String Searches</title>
<indexterm>
<primary>case sensitivity</primary>
@@ -3744,17 +3744,23 @@
<secondary>and case sensitivity</secondary>
</indexterm>
- <indexterm>
- <primary>Chinese</primary>
- </indexterm>
+ <para>
+ For non-binary strings (<literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, <literal>TEXT</literal>), string
+ searches use the collation of the comparison operands. For
+ binary strings (<literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>),
+ comparisons use the numeric values of the bytes in the
+ operands; this means that for alphabetic characters,
+ comparisons will be case sensitive.
+ </para>
- <indexterm>
- <primary>Big5 Chinese character encoding</primary>
- </indexterm>
-
<para>
- By default, MySQL searches are not case sensitive. This means
- that if you search with
+ The default character set and collation are
+ <literal>latin1</literal> and
+ <literal>latin1_swedish_ci</literal>, so non-binary string
+ comparisons are case insensitive by default. This means that
+ if you search with
<literal><replaceable>col_name</replaceable> LIKE
'a%'</literal>, you get all column values that start with
<literal>A</literal> or <literal>a</literal>. If you want to
@@ -3791,13 +3797,94 @@
</para>
<para>
+ For comparison of non-binary strings that are case sensitive,
+ you can use <literal>COLLATE</literal> to indicate that a
+ case-insensitive collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = 'New York' COLLATE latin1_bin;</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT @s COLLATE latin1_swedish_ci = 'new york';</userinput>
++-------------------------------------------+
+| @s COLLATE latin1_swedish_ci = 'new york' |
++-------------------------------------------+
+| 1 |
++-------------------------------------------+
+</programlisting>
+
+ <para>
+ For comparison of binary strings that are case sensitive, you
+ can convert the string to a non-binary string and use
+ <literal>COLLATE</literal> to indicate that a case-insensitive
+ collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = BINARY 'New York';</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york';</userinput>
++-----------------------------------------------------------------+
+| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york' |
++-----------------------------------------------------------------+
+| 1 |
++-----------------------------------------------------------------+
+</programlisting>
+
+ <para>
+ If you need to determine whether a value will compare as a
+ non-binary or binary string, use the
+ <function role="sql">COLLATION()</function> function. This
+ example shows that <function role="sql">VERSION()</function>
+ returns a string that has a case-insensitive collation, so
+ comparisons are case insensitive:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(VERSION());</userinput>
++----------------------+
+| COLLATION(VERSION()) |
++----------------------+
+| utf8_general_ci |
++----------------------+
+</programlisting>
+
+ <para>
+ For binary strings, the collation value is
+ <literal>binary</literal>, so so comparisons will be case
+ sensitive. One context in which you will see this value is for
+ compression and encryption functions, which return binary
+ strings as a general rule. The following example shows that
+ <function role="sql">SHA1()</function> returns a binary
+ string:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(SHA1('x'));</userinput>
++----------------------+
+| COLLATION(SHA1('x')) |
++----------------------+
+| binary |
++----------------------+
+</programlisting>
+
+ <para>
Simple comparison operations (<literal>>=, >, =, <,
<=</literal>, sorting, and grouping) are based on each
character's <quote>sort value.</quote> Characters with the
- same sort value (such as <quote><literal>E</literal></quote>,
- <quote><literal>e</literal></quote>, and
- <quote><literal>é</literal></quote>) are treated as the
- same character.
+ same sort value (such as <quote><literal>e</literal></quote>
+ and <quote><literal>é</literal></quote>) are treated as
+ the same character.
</para>
</section>
Modified: trunk/refman-5.0/errors-problems.xml
===================================================================
--- trunk/refman-5.0/errors-problems.xml 2008-05-19 15:56:40 UTC (rev 10769)
+++ trunk/refman-5.0/errors-problems.xml 2008-05-19 15:56:46 UTC (rev 10770)
Changed blocks: 3, Lines Added: 101, Lines Deleted: 14; 5665 bytes
@@ -3648,7 +3648,7 @@
<section id="case-sensitivity">
- <title>Case Sensitivity in Searches</title>
+ <title>Case Sensitivity in String Searches</title>
<indexterm>
<primary>case sensitivity</primary>
@@ -3660,17 +3660,23 @@
<secondary>and case sensitivity</secondary>
</indexterm>
- <indexterm>
- <primary>Chinese</primary>
- </indexterm>
+ <para>
+ For non-binary strings (<literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, <literal>TEXT</literal>), string
+ searches use the collation of the comparison operands. For
+ binary strings (<literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>),
+ comparisons use the numeric values of the bytes in the
+ operands; this means that for alphabetic characters,
+ comparisons will be case sensitive.
+ </para>
- <indexterm>
- <primary>Big5 Chinese character encoding</primary>
- </indexterm>
-
<para>
- By default, MySQL searches are not case sensitive. This means
- that if you search with
+ The default character set and collation are
+ <literal>latin1</literal> and
+ <literal>latin1_swedish_ci</literal>, so non-binary string
+ comparisons are case insensitive by default. This means that
+ if you search with
<literal><replaceable>col_name</replaceable> LIKE
'a%'</literal>, you get all column values that start with
<literal>A</literal> or <literal>a</literal>. If you want to
@@ -3697,13 +3703,94 @@
</para>
<para>
+ For comparison of non-binary strings that are case sensitive,
+ you can use <literal>COLLATE</literal> to indicate that a
+ case-insensitive collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = 'New York' COLLATE latin1_bin;</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT @s COLLATE latin1_swedish_ci = 'new york';</userinput>
++-------------------------------------------+
+| @s COLLATE latin1_swedish_ci = 'new york' |
++-------------------------------------------+
+| 1 |
++-------------------------------------------+
+</programlisting>
+
+ <para>
+ For comparison of binary strings that are case sensitive, you
+ can convert the string to a non-binary string and use
+ <literal>COLLATE</literal> to indicate that a case-insensitive
+ collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = BINARY 'New York';</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york';</userinput>
++-----------------------------------------------------------------+
+| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york' |
++-----------------------------------------------------------------+
+| 1 |
++-----------------------------------------------------------------+
+</programlisting>
+
+ <para>
+ If you need to determine whether a value will compare as a
+ non-binary or binary string, use the
+ <function role="sql">COLLATION()</function> function. This
+ example shows that <function role="sql">VERSION()</function>
+ returns a string that has a case-insensitive collation, so
+ comparisons are case insensitive:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(VERSION());</userinput>
++----------------------+
+| COLLATION(VERSION()) |
++----------------------+
+| utf8_general_ci |
++----------------------+
+</programlisting>
+
+ <para>
+ For binary strings, the collation value is
+ <literal>binary</literal>, so so comparisons will be case
+ sensitive. One context in which you will see this value is for
+ compression and encryption functions, which return binary
+ strings as a general rule. The following example shows that
+ <function role="sql">SHA1()</function> returns a binary
+ string:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(SHA1('x'));</userinput>
++----------------------+
+| COLLATION(SHA1('x')) |
++----------------------+
+| binary |
++----------------------+
+</programlisting>
+
+ <para>
Simple comparison operations (<literal>>=, >, =, <,
<=</literal>, sorting, and grouping) are based on each
character's <quote>sort value.</quote> Characters with the
- same sort value (such as <quote><literal>E</literal></quote>,
- <quote><literal>e</literal></quote>, and
- <quote><literal>é</literal></quote>) are treated as the
- same character.
+ same sort value (such as <quote><literal>e</literal></quote>
+ and <quote><literal>é</literal></quote>) are treated as
+ the same character.
</para>
</section>
Modified: trunk/refman-5.1/errors-problems-core.xml
===================================================================
--- trunk/refman-5.1/errors-problems-core.xml 2008-05-19 15:56:40 UTC (rev 10769)
+++ trunk/refman-5.1/errors-problems-core.xml 2008-05-19 15:56:46 UTC (rev 10770)
Changed blocks: 3, Lines Added: 101, Lines Deleted: 14; 5680 bytes
@@ -3651,7 +3651,7 @@
<section id="case-sensitivity">
- <title>Case Sensitivity in Searches</title>
+ <title>Case Sensitivity in String Searches</title>
<indexterm>
<primary>case sensitivity</primary>
@@ -3663,17 +3663,23 @@
<secondary>and case sensitivity</secondary>
</indexterm>
- <indexterm>
- <primary>Chinese</primary>
- </indexterm>
+ <para>
+ For non-binary strings (<literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, <literal>TEXT</literal>), string
+ searches use the collation of the comparison operands. For
+ binary strings (<literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>),
+ comparisons use the numeric values of the bytes in the
+ operands; this means that for alphabetic characters,
+ comparisons will be case sensitive.
+ </para>
- <indexterm>
- <primary>Big5 Chinese character encoding</primary>
- </indexterm>
-
<para>
- By default, MySQL searches are not case sensitive. This means
- that if you search with
+ The default character set and collation are
+ <literal>latin1</literal> and
+ <literal>latin1_swedish_ci</literal>, so non-binary string
+ comparisons are case insensitive by default. This means that
+ if you search with
<literal><replaceable>col_name</replaceable> LIKE
'a%'</literal>, you get all column values that start with
<literal>A</literal> or <literal>a</literal>. If you want to
@@ -3700,13 +3706,94 @@
</para>
<para>
+ For comparison of non-binary strings that are case sensitive,
+ you can use <literal>COLLATE</literal> to indicate that a
+ case-insensitive collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = 'New York' COLLATE latin1_bin;</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT @s COLLATE latin1_swedish_ci = 'new york';</userinput>
++-------------------------------------------+
+| @s COLLATE latin1_swedish_ci = 'new york' |
++-------------------------------------------+
+| 1 |
++-------------------------------------------+
+</programlisting>
+
+ <para>
+ For comparison of binary strings that are case sensitive, you
+ can convert the string to a non-binary string and use
+ <literal>COLLATE</literal> to indicate that a case-insensitive
+ collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = BINARY 'New York';</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york';</userinput>
++-----------------------------------------------------------------+
+| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york' |
++-----------------------------------------------------------------+
+| 1 |
++-----------------------------------------------------------------+
+</programlisting>
+
+ <para>
+ If you need to determine whether a value will compare as a
+ non-binary or binary string, use the
+ <function role="sql">COLLATION()</function> function. This
+ example shows that <function role="sql">VERSION()</function>
+ returns a string that has a case-insensitive collation, so
+ comparisons are case insensitive:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(VERSION());</userinput>
++----------------------+
+| COLLATION(VERSION()) |
++----------------------+
+| utf8_general_ci |
++----------------------+
+</programlisting>
+
+ <para>
+ For binary strings, the collation value is
+ <literal>binary</literal>, so so comparisons will be case
+ sensitive. One context in which you will see this value is for
+ compression and encryption functions, which return binary
+ strings as a general rule. The following example shows that
+ <function role="sql">SHA1()</function> returns a binary
+ string:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(SHA1('x'));</userinput>
++----------------------+
+| COLLATION(SHA1('x')) |
++----------------------+
+| binary |
++----------------------+
+</programlisting>
+
+ <para>
Simple comparison operations (<literal>>=, >, =, <,
<=</literal>, sorting, and grouping) are based on each
character's <quote>sort value.</quote> Characters with the
- same sort value (such as <quote><literal>E</literal></quote>,
- <quote><literal>e</literal></quote>, and
- <quote><literal>é</literal></quote>) are treated as the
- same character.
+ same sort value (such as <quote><literal>e</literal></quote>
+ and <quote><literal>é</literal></quote>) are treated as
+ the same character.
</para>
</section>
Modified: trunk/refman-6.0/errors-problems.xml
===================================================================
--- trunk/refman-6.0/errors-problems.xml 2008-05-19 15:56:40 UTC (rev 10769)
+++ trunk/refman-6.0/errors-problems.xml 2008-05-19 15:56:46 UTC (rev 10770)
Changed blocks: 3, Lines Added: 101, Lines Deleted: 14; 5663 bytes
@@ -3650,7 +3650,7 @@
<section id="case-sensitivity">
- <title>Case Sensitivity in Searches</title>
+ <title>Case Sensitivity in String Searches</title>
<indexterm>
<primary>case sensitivity</primary>
@@ -3662,17 +3662,23 @@
<secondary>and case sensitivity</secondary>
</indexterm>
- <indexterm>
- <primary>Chinese</primary>
- </indexterm>
+ <para>
+ For non-binary strings (<literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, <literal>TEXT</literal>), string
+ searches use the collation of the comparison operands. For
+ binary strings (<literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>),
+ comparisons use the numeric values of the bytes in the
+ operands; this means that for alphabetic characters,
+ comparisons will be case sensitive.
+ </para>
- <indexterm>
- <primary>Big5 Chinese character encoding</primary>
- </indexterm>
-
<para>
- By default, MySQL searches are not case sensitive. This means
- that if you search with
+ The default character set and collation are
+ <literal>latin1</literal> and
+ <literal>latin1_swedish_ci</literal>, so non-binary string
+ comparisons are case insensitive by default. This means that
+ if you search with
<literal><replaceable>col_name</replaceable> LIKE
'a%'</literal>, you get all column values that start with
<literal>A</literal> or <literal>a</literal>. If you want to
@@ -3699,13 +3705,94 @@
</para>
<para>
+ For comparison of non-binary strings that are case sensitive,
+ you can use <literal>COLLATE</literal> to indicate that a
+ case-insensitive collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = 'New York' COLLATE latin1_bin;</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT @s COLLATE latin1_swedish_ci = 'new york';</userinput>
++-------------------------------------------+
+| @s COLLATE latin1_swedish_ci = 'new york' |
++-------------------------------------------+
+| 1 |
++-------------------------------------------+
+</programlisting>
+
+ <para>
+ For comparison of binary strings that are case sensitive, you
+ can convert the string to a non-binary string and use
+ <literal>COLLATE</literal> to indicate that a case-insensitive
+ collation should be used:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @s = BINARY 'New York';</userinput>
+mysql> <userinput>SELECT @s = 'new york';</userinput>
++-----------------+
+| @s = 'new york' |
++-----------------+
+| 0 |
++-----------------+
+mysql> <userinput>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york';</userinput>
++-----------------------------------------------------------------+
+| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'new york' |
++-----------------------------------------------------------------+
+| 1 |
++-----------------------------------------------------------------+
+</programlisting>
+
+ <para>
+ If you need to determine whether a value will compare as a
+ non-binary or binary string, use the
+ <function role="sql">COLLATION()</function> function. This
+ example shows that <function role="sql">VERSION()</function>
+ returns a string that has a case-insensitive collation, so
+ comparisons are case insensitive:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(VERSION());</userinput>
++----------------------+
+| COLLATION(VERSION()) |
++----------------------+
+| utf8_general_ci |
++----------------------+
+</programlisting>
+
+ <para>
+ For binary strings, the collation value is
+ <literal>binary</literal>, so so comparisons will be case
+ sensitive. One context in which you will see this value is for
+ compression and encryption functions, which return binary
+ strings as a general rule. The following example shows that
+ <function role="sql">SHA1()</function> returns a binary
+ string:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT COLLATION(SHA1('x'));</userinput>
++----------------------+
+| COLLATION(SHA1('x')) |
++----------------------+
+| binary |
++----------------------+
+</programlisting>
+
+ <para>
Simple comparison operations (<literal>>=, >, =, <,
<=</literal>, sorting, and grouping) are based on each
character's <quote>sort value.</quote> Characters with the
- same sort value (such as <quote><literal>E</literal></quote>,
- <quote><literal>e</literal></quote>, and
- <quote><literal>é</literal></quote>) are treated as the
- same character.
+ same sort value (such as <quote><literal>e</literal></quote>
+ and <quote><literal>é</literal></quote>) are treated as
+ the same character.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r10770 - in trunk: . it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul | 19 May |
