MySQL Slashes and Backslashes


There was a on Twitter to @MySQL How can I insert “/” into a varchar field please. Since they were polite and used please, I wanted to be helpful.

At first I thought this was the ol’ MySQL string literal problem where you need to have two backslashes to get one backslash in a character field. See String Literals in the MySQL Manual. Back Slash and Fore Slash A lot of people have trouble with the backslash when they first get started with MySQL. From the quote below, you will see that some characters need to be escaped with a backslash. But what about the forward slash?

I remember a previous job were I saved many UNIX file names but I did not remember anything unusual about forward slashed. My mental cache had been flushed of the answer. So I created a test table and experimented.

As can be shown in the image, the ‘\\’ trick works for inputting a single backslash. And ‘//’ will input TWO slashes. So there is no trick for fore slashes.

No too bad for a Monday morning with no caffeine.

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”. 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”. Escape processing is done according to the character set indicated by the character_set_connection system variable. This is true even for strings that are preceded by an introduce that indicates a different character set, as discussed in Section 10.1.3.5, “Character String Literal Character Set and Collation”.

Table 9.1 Special Character Escape Sequences

Escape Sequence Character Represented by Sequence
An ASCII NUL (0x00) 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.

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.5.1, “String Comparison Functions”. If you use “\%” or “\_” outside of 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.

About these ads

3 Comments

Filed under Basics, MySQL

3 responses to “MySQL Slashes and Backslashes

  1. In a similar fashion:
    CREATE DATABASE `test\test`
    CREATE DATABASE `test/test`
    CREATE DATABASE `test/test/`
    CREATE DATABASE `test/test//`
    CREATE DATABASE `test\test\\`
    CREATE DATABASE `test\test\`
    CREATE DATABASE `test
    test`

    Seems like you can use \ and / in database names (and even newlines!), but you can’t have a single \ in the end of the database name.

    Ever tried to connect to a database (USE or mysql ) which had a newline in the database name?

  2. Pingback: MySQL Slashes and Backslashes | Open Source DBA's Blog « SSHlab.com

  3. Joe G

    “So their is no trick for fore slashes.”

    Good article. Minor nit-pick: I think this is clearer as “So, there is no trick for forward slashes.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s