MySQL 5.1 Reference Manual :: 9 Language Structure :: 9.1 Literal Values :: 9.1.1 Strings
  • MySQL 5.1 Reference Manual

  • 9.1 Literal Values
  • 9.1.1 Strings
  • 9.1.2 Numbers
  • 9.1.3 Hexadecimal Values
  • 9.1.4 Boolean Values
  • 9.1.5 Bit-Field Values
  • 9.1.6 NULL Values

9.1.1. Strings    字串

A string is a sequence of bytes or characters, enclosed within either single quote ( ' ) or double quote ( " ) characters. Examples:

'a string'
"another string"

字符串是多个字符组成的一个字符序列,由单引号( ' ) 或双引号 ( " ) 字符包围。

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotes because a string quoted within double quotes is interpreted as an identifier.

在 ANSI_QUOTES SQL模式中运行时只能用单引号, ............

A binary string is a string of bytes that has no character set or collation. A non-binary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte. For non-binary strings the unit is the character and some character sets allow multi-byte characters. Character value ordering is a function of the string collation.

String literals may have an optional character set introducer and COLLATE clause:

[_charset_name]'string' [COLLATE collation_name]

Examples:

SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

For more information about these forms of string syntax, see Section 10.3.5, “Character String Literal Character Set and Collation”.

User Comments

Posted by brusselsshrek on September 30 2005 1:12pm[Delete] [Edit]

I was having the following error and this page seems to be the key to solving it:

Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation

When I looked at my table, I had specified the collation sequence as:

utf8_bin   (binary是二进制)

yet in my SQL (in PHP code) I was simply specifying a literal (it was more fancy than "xyz" - arabic or chinese, but you will get the point):

SELECT * from my_table WHERE my_col = "xyz"

Now, the solution proved to be stating:

SELECT * from my_table WHERE my_col = _utf8"xyz"

Add your own comment.

Posted by baitie/(奥索网)[Delete] [Edit]

MySQL中文排序错误的解决方法

一. 方法1
  在MySQL中,进行中文排序和查找的时候,对汉字的排序和查找结果是错误的。这种情况在MySQL的很多版本中都存在。如果这个问题不解决,那么MySQL将无法实际处理中文。
  出现这个问题的原因是:MySQL在查询字符串时是大小写不敏感的,在编绎MySQL时一般以ISO-8859字符集作为默认的字符集,因此在比较过程中中文编码字符大小写转换造成了这种现象,一种解决方法是对于包含中文的字段加上"binary"属性,使之作为二进制比较,例如将"name char(10)"改成"name char(10)binary"。

二. 方法2
  如果你使用源码编译MySQL,可以编译MySQL时使用--with--charset=gbk 参数,这样MySQL就会直接支持中文查找和排序了。
.

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (‘\’), known as the escape character. MySQL recognizes the following escape sequences:

在字符串內,某個順序有特殊的意義。這些順序的每一個以一條反斜線backslash(“\”)開始,稱為 轉義字符escape character。MySQL識別下列轉義字符:

\0 An ASCII 0 (NUL) character.
\' A single quote (‘'’) character.
\" A double quote (‘"’) character.
\b A backspace character.
\n A newline (linefeed) character.
\r A carriage return character.一個回車符
\t A tab character.
\Z ASCII 26 (Control-Z). See note following the table.
\\ A backslash (‘\’) character.
\% A ‘%’ character. See note following the table.
\_ A ‘_’ character. See note following the table.

For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, ‘\x’ is just ‘x’.

These sequences are case sensitive. For example, ‘\b’ is interpreted as a backspace, but ‘\B’ is interpreted as ‘B’.

The ASCII 26 character can be encoded as ‘\Z’ to enable you to work around the problem that ASCII 26 stands for END-OF-FILE on Windows. ASCII 26 within a file causes problems if you try to use mysql db_name < file_name.

The ‘\%’ and ‘\_’ sequences are used to search for 文字實例literal instances of ‘%’ and ‘_’ in pattern-matching 正文contexts where they would 否則otherwise be interpreted as wildcard characters. See the description of the LIKE operator in Section 12.3.1, “String Comparison Functions”. If you use ‘\%’ or ‘\_’ in non-pattern-matching contexts, they evaluate to the strings ‘\%’ and ‘\_’, not to ‘%’ and ‘_’.

注意,如果你在某些正文環境中使用“\%”或“\%_”,這些將返回字符串“\%”和“\_”而不是“%”和“_”。

There are several ways to include quote characters within a string:

  • A ‘'’ inside a string quoted with ‘'’ may be written as ‘''’.

  • A ‘"’ inside a string quoted with ‘"’ may be written as ‘""’.

  • Precede the quote character by an escape character (‘\’).

  • A ‘'’ inside a string quoted with ‘"’ needs no special treatment and need not be doubled or escaped. In the same way, ‘"’ inside a string quoted with ‘'’ needs no special treatment.

有幾種方法在一個字符串內包括引號:

  • 一個字符串內用 ' 加引號的'可以被寫作為  ''  。
  • 一個字符串內用 "加引號的 "可以被寫作為" "  。
  • 你可以把一個轉義字符(\)放在引號前面。
  • 一個字符串內用"加引號的'不需要特殊對待而且不必被重複或轉義。同理,一個字符串內用 ' 加引號的與 " 也不需要特殊對待。

The following SELECT statements demonstrate how quoting and escaping work:

下面顯示的SELECT展示引號和轉義如何工作:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+

If you want to insert binary data into a string column (such as a BLOB column), the following characters must be represented by escape sequences:

如果你想要把二進制binary數據 插入到一個BLOB列,下列字符必須由轉義序列escape sequences表示:

NUL NUL byte (ASCII 0). Represent this character by ‘\0’ (a backslash followed by an ASCII ‘0’ character).
\ Backslash (ASCII 92). Represent this character by ‘\\’.
' Single quote (ASCII 39). Represent this character by ‘\'’.
" Double quote (ASCII 34). Represent this character by ‘\"’.

When writing application programs, any string that might contain any of these special characters must be properly escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server. You can do this in two ways:

  • Process the string with a function that escapes the special characters. In a C program, you can use the mysql_real_escape_string() C API function to escape characters. See Section 22.2.3.52, “mysql_real_escape_string()”. The Perl DBI interface provides a quote method to convert special characters to the proper escape sequences. See Section 22.4, “MySQL Perl API”. Other language interfaces may provide a similar capability.

  • As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.


12.3.1. String Comparison Functions

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.

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

  • expr LIKE pat [ESCAPE 'escape_char']

    Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

    The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

    Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

    mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
    +-----------------------------------------+
    | 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
    +-----------------------------------------+
    |                                       0 |
    +-----------------------------------------+
    mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
    +--------------------------------------+
    | 'ä' = 'ae' COLLATE latin1_german2_ci |
    +--------------------------------------+
    |                                    1 |
    +--------------------------------------+
    

    With LIKE you can use the following two wildcard characters in the pattern:

    Character Description
    % Matches any number of characters, even zero characters
    _ Matches exactly one character
    mysql> SELECT 'David!' LIKE 'David_';
            -> 1
    mysql> SELECT 'David!' LIKE '%D%v%';
            -> 1
    

    To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, ‘\’ is assumed.

    String Description
    \% Matches one ‘%’ character
    \_ Matches one ‘_’ character
    mysql> SELECT 'David!' LIKE 'David\_';
            -> 0
    mysql> SELECT 'David_' LIKE 'David\_';
            -> 1
    

    To specify a different escape character, use the ESCAPE clause:

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

    The escape sequence should be empty or one character long. As of MySQL 5.1.2, if the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.

    The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

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

    In MySQL, LIKE is allowed on numeric expressions. (This is an extension to the standard SQL LIKE.)

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

    Note: Because MySQL uses C escape syntax in strings (for example, ‘\n’ to represent a newline character), you must double any ‘\’ that you use in LIKE strings. For example, to search for ‘\n’, specify it as ‘\\n’. To search for ‘\’, specify it as ‘\\\\’; 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. (Exception: At the end of the pattern string, backslash can be specified as ‘\\’. At the end of the string, backslash stands for itself because there is nothing following to escape.)

  • expr NOT LIKE pat [ESCAPE 'escape_char']

    This is the same as NOT (expr LIKE pat [ESCAPE 'escape_char']).

  • expr NOT REGEXP pat, expr NOT RLIKE pat

    This is the same as NOT (expr REGEXP pat).

  • expr REGEXP pat expr RLIKE pat

    Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in Appendix G, Regular Expressions. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

    The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

    Note: Because MySQL uses the C escape syntax in strings (for example, ‘\n’ to represent the newline character), you must double any ‘\’ that you use in your REGEXP strings.

    REGEXP is not case sensitive, except when used with binary strings.

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

    REGEXP and RLIKE use the current character set when deciding the type of a character. The default is latin1 (cp1252 West European). Warning: These operators are not multi-byte safe.

  • STRCMP(expr1,expr2)

    STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

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

    STRCMP() 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.


User Comments

Posted by Sascha René Leib on October 10 2002 3:29am[Delete] [Edit]

Also, keep in mind that by default,
Swedish/Finnish string comparison rules are in
use! This means that e.g.: u <> ü = y !

Posted by James Sleeman on January 12 2004 7:43am[Delete] [Edit]

You should also be aware that because NOT ( NULL ) is NULL and NULL LIKE '%' is NULL, that NOT ( foo LIKE '%bar%' ) will return only those rows that do not contain 'bar' AND are also not NULL.

That is,
select count(*) from foo; => 2
select count(*) from foo where bar IS NULL; => 2
select count(*) from foo where bar LIKE '%narf%'; => 0
normal so far, but then we negate the last one, expecting to get 2 rows..
select count(*) from foo where NOT (bar LIKE '%narf%'); => 0


Posted by Jeremy Schultz on May 4 2004 10:10pm[Delete] [Edit]

In the manual it states "Before MySQL 4.0, STRCMP() is case sensitive.", I did not relize this, and I found out the hard way that STRCMP() is case-sensitive.

Also I found it strange that STRCMP() is case-sensitive, but ORDER BY is case-insensitive.

Here is the what I was doing, and I hope this helps others. I have a table that I display, just a basic dump of the table order by a varchar field. I have a section to display a single item from the list, then used the STRCMP to go to the next/prevoius item in the order it was displayed

Listing the table:
SELECT id, name, price, description WHERE category = 1 ORDER BY name

Getting the Next Item Button for display section:
SELECT id WHERE category = 1 AND STRCMP(name, 'current_item_name') = 1 ORDER BY name LIMIT 1

Getting the Prev Item Button for display section:
SELECT id WHERE category = 1 AND STRCMP(name, 'current_item_name') = -1 ORDER BY name DESC LIMIT 1

The Fix I use for now, is to lowercase the strings in STRCMP():
STRCMP(LOWER(name), LOWER('current_item_name'))

Posted by Moreno Gentili on July 2 2004 12:36pm[Delete] [Edit]

You got a wrong sytanx there, James Sleeman.

This query:
SELECT 'abcdefg' LIKE '%c%'

returns 1 because the string 'abcdefg' matches the LIKE pattern. As a consequence:
SELECT NOT ('abcdefg' LIKE '%c%')

returns 0

So, according to your example, the right syntax to select all those records that do not match the LIKE pattern is:
SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%narf%'

Posted by Anthony Law on July 9 2004 9:15pm[Delete] [Edit]

No Moreno you're wrong & completely missed the point.

James is trying to point out if BAR IS NULL then it won't show up in a NOT condition, because the opposite of NULL is still NULL (i.e. not 1).

In other words, both
a) WHERE bar LIKE '%';
b) WHERE bar NOT LIKE '%';
will miss the rows WHERE bar IS NULL;

Refer to the docs above. The syntax you gave is identical to his:

=> expr NOT LIKE pat [ESCAPE 'escape-char']
=>
=> This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).

Posted by Derek Hateley on July 12 2005 8:48pm[Delete] [Edit]

Hi All

I found a "Funny" today. Have been developing a VB6 app using ODBC connectivity, and mostly testing with Access for ease of setup and changing/adding data field. Anyway:

SELECT COUNT (*) AS Cnt FROM Customer WHERE Postcode LIKE 'CV%' worked fine pointing to Access, but fell over when I pointed to MySQL (version 3.23.58).

Turned out to be the space between COUNT & (*)

Now works a treat.

Posted by Ami Chayun on January 12 2006 10:16am[Delete] [Edit]

It is possible to search wildchar patterns when using bind parameters.
For example to search a substring:

select * from tblUser where Name LIKE CONCAT('%', ? ,'%');

And then (for example in perl DBI)
$dbh->selectrow_hashref($SQL_Stmnt, undef, @{[ 'Erik']});

This provides a 'safer' search environment in web interfaces, since it prevents SQL injections.

Add your own comment.




real_vine@hotmail.com