SQL – 特殊字元( ” ‘ \ )的處理

Loading

測試環境為 CentOS 8 x86_64 (虛擬機)

在 SQL 的字串是用 “String” (Double Quote) 或是 ‘String’ (Single Quote) 來表示,但我的字串裡面真的要使用 ” 或是 ‘ 時該怎麼辦.

這時候需要使用 \ 反斜線 (Backslash Character) 這個跳脫字元(Escape Character) 來表示接下來的 ” 或是 ‘ 是字串的一部分.

MariaDB [(none)]> SELECT "String";
+--------+
| String |
+--------+
| String |
+--------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT "Str"i"ng";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"ng"' at line 1
MariaDB [(none)]> SELECT "Str\"i\"ng";
+----------+
| Str"i"ng |
+----------+
| Str"i"ng |
+----------+
1 row in set (0.000 sec)

或是用 “” 來表示 ” , ” 來表示 ‘ .

MariaDB [(none)]> SELECT "Str""i""ng";
+----------+
| Str"i"ng |
+----------+
| Str"i"ng |
+----------+
1 row in set (0.000 sec)

除了 ” 以及 ‘ 外,下面幾個特殊 跳脫序列字元 (Escape sequence Character) 要讓它們顯示為字元時,需使用 \ 反斜線 (Backslash Character).

  1. ASCII NULL (0x00) 需使用 \\0
    MariaDB [(none)]> SELECT '\0';
    +---+
    |   |
    +---+
    |   |
    +---+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT '\\0';
    +----+
    | \0 |
    +----+
    | \0 |
    +----+
    1 row in set (0.000 sec)
    
  2. Single quote ( ‘ ) 需使用 \’
    MariaDB [(none)]> SELECT 'This's';
        '> ';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '';
    '' at line 1
    MariaDB [(none)]> SELECT 'This\'s';
    +--------+
    | This's |
    +--------+
    | This's |
    +--------+
    1 row in set (0.001 sec)
    
  3. Double quote ( ” ) 需使用 \”
    MariaDB [(none)]> SELECT '"This"';
    +--------+
    | "This" |
    +--------+
    | "This" |
    +--------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT ""This"";
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '""' at line 1
    MariaDB [(none)]> SELECT "\"This\"";
    +--------+
    | "This" |
    +--------+
    | "This" |
    +--------+
    1 row in set (0.001 sec)
    
  4. Backspace ( \b ) 需使用 \\b
    MariaDB [(none)]> SELECT '\b';
    +---+
    |   |
    +---+
    | |
    +---+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT '\\b';
    +----+
    | \b |
    +----+
    | \b |
    +----+
    1 row in set (0.000 sec)
    
  5. New line or linefeed ( \n ) 需使用 \\n
    MariaDB [(none)]> SELECT '\n';
    +---+
    |   |
    +---+
    | 
     |
    +---+
    1 row in set (0.001 sec)
    
    MariaDB [(none)]> SELECT '\\n';
    +----+
    | \n |
    +----+
    | \n |
    +----+
    1 row in set (0.001 sec)
    
  6. Carriage return ( \r) 需使用 \\r
    MariaDB [(none)]> SELECT '\r';
    +---+
    |   |
    +---+
     |
    +---+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT '\\r';
    +----+
    | \r |
    +----+
    | \r |
    +----+
    1 row in set (0.000 sec)
    
  7. Tab 需使用 \\t
    MariaDB [(none)]> SELECT '\t';
    +---+
    |   |
    +---+
    | 	 |
    +---+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT '\\t';
    +----+
    | \t |
    +----+
    | \t |
    +----+
    1 row in set (0.000 sec)
    
  8. ASCII 26 (Control+Z) 需使用 \\Z
    MariaDB [testdb]> select '\Z';
    +---+
    |   |
    +---+
    |  |
    +---+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> select '\\Z';
    +----+
    | \Z |
    +----+
    | \Z |
    +----+
    1 row in set (0.000 sec)
    
  9. Backslash ( \ ) 需使用 \\
    MariaDB [(none)]> SELECT '\';
        '> ';
    +-----+
    | ';
     |
    +-----+
    | ';
     |
    +-----+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT '\\';
    +---+
    | \ |
    +---+
    | \ |
    +---+
    1 row in set (0.000 sec)
    
  10. % character 需使用 \%
  11. _ character 需使用 \_

那在 INSERT 與 SELECT LIKE 比較時還需要注意什麼嗎?

先建立要進行測試的資料庫.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server
  
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)
 
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
MariaDB [astl]> CREATE TABLE t1 (K1 INT(11) NOT NULL AUTO_INCREMENT , data1 VARCHAR(50) NOT NULL , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.010 sec)

INSERT

  1. 在 INSERT 字串時會用 “String” (Double Quote) 來表示裡面的資料為字串,這時候要注意不能包含 ” (Double Quote) 與 \ (Backslash Character) 這些都會造成錯誤.
  2. 在 INSERT 字串時會用 ‘String’ (Single Quote) 來表示裡面的資料為字串,這時候要注意不能包含 ‘ (Single Quote) 與 \ (Backslash Character) 這些都會造成錯誤.

必須使用下面的方式來取代.
\ 需用 \\ 取代.

MariaDB [testdb]> INSERT INTO t1 (data1) VALUES("C:\\Windows\\") ;
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> INSERT INTO t1 (data1) VALUES('C:\\Windows\\') ;
Query OK, 1 row affected (0.002 sec)

” 需用 \” 或是 “” 取代.

MariaDB [testdb]> INSERT INTO t1 (data1) VALUES("This is a \"Book1\"") ;
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO t1 (data1) VALUES("This is a ""Book2""") ;
Query OK, 1 row affected (0.001 sec)

‘ 需用 \’或是 ” 取代.

MariaDB [testdb]> INSERT INTO t1 (data1) VALUES('This is a \'Book3\'');
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO t1 (data1) VALUES('This is a ''Book4''');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> SELECT * FROM t1;
+----+-------------------+
| K1 | data1             |
+----+-------------------+
|  1 | C:\Windows\       |
|  2 | C:\Windows\       |
|  3 | This is a "Book1" |
|  4 | This is a "Book2" |
|  5 | This is a 'Book3' |
|  6 | This is a 'Book4' |
+----+-------------------+
6 rows in set (0.001 sec)

SELECT LIKE

在使用 SELECT + LIKE 比對資料時,遇到資料裡面有 ” (Double Quote) , ‘ (Single Quote) 或是 \ (Backslash Character) 時都需要在前加上 \ (Backslash Character).

遇到 \ (Backslash Character) 需用 \\ 或是 \\\\ 來取代.

MariaDB [testdb]> SELECT data1, data1 LIKE '%\\' FROM t1;
+-------------------+------------------+
| data1             | data1 LIKE '%\\' |
+-------------------+------------------+
| C:\Windows\       |                1 |
| C:\Windows\       |                1 |
| This is a "Book1" |                0 |
| This is a "Book2" |                0 |
| This is a 'Book3' |                0 |
| This is a 'Book4' |                0 |
+-------------------+------------------+
6 rows in set (0.001 sec)

MariaDB [testdb]> SELECT data1, data1 LIKE '%\\\\' FROM t1;
+-------------------+--------------------+
| data1             | data1 LIKE '%\\\\' |
+-------------------+--------------------+
| C:\Windows\       |                  1 |
| C:\Windows\       |                  1 |
| This is a "Book1" |                  0 |
| This is a "Book2" |                  0 |
| This is a 'Book3' |                  0 |
| This is a 'Book4' |                  0 |
+-------------------+--------------------+
6 rows in set (0.001 sec)

遇到 ‘ (Single Quote) 需用 \’ 來取代.

MariaDB [testdb]> SELECT data1, data1 LIKE '%\'' FROM t1;
+-------------------+------------------+
| data1             | data1 LIKE '%\'' |
+-------------------+------------------+
| C:\Windows\       |                0 |
| C:\Windows\       |                0 |
| This is a "Book1" |                0 |
| This is a "Book2" |                0 |
| This is a 'Book3' |                1 |
| This is a 'Book4' |                1 |
+-------------------+------------------+
6 rows in set (0.001 sec)

遇到 ” (Double Quote) 需用 \” 來取代.

MariaDB [testdb]> SELECT data1, data1 LIKE '%\"' FROM t1;
+-------------------+------------------+
| data1             | data1 LIKE '%\"' |
+-------------------+------------------+
| C:\Windows\       |                0 |
| C:\Windows\       |                0 |
| This is a "Book1" |                1 |
| This is a "Book2" |                1 |
| This is a 'Book3' |                0 |
| This is a 'Book4' |                0 |
+-------------------+------------------+
6 rows in set (0.001 sec)

QUOTE 函數

QUOTE 函數有以下功能.

  1. 帶有單引號 ‘ , 反斜線 \ ,ASCII NUL 以及 Control-Z 帶有反斜線.
  2. 如果值是 NULL,則傳回為字串 “NULL”
MariaDB [testdb]> SELECT QUOTE(data1) FROM t1;
+-----------------------+
| QUOTE(data1)          |
+-----------------------+
| 'C:\\Windows\\'       |
| 'C:\\Windows\\'       |
| 'This is a "Book1"'   |
| 'This is a "Book2"'   |
| 'This is a \'Book3\'' |
| 'This is a \'Book4\'' |
+-----------------------+
6 rows in set (0.001 sec)

傳回為字串 “NULL” .

MariaDB [testdb]> SELECT QUOTE(NULL); 
+-------------+
| QUOTE(NULL) |
+-------------+
| NULL        |
+-------------+
1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料