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

測試環境為 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
\0 ASCII NUL (0x00).
\’ Single quote (“’”).
\” Double quote (“””).
\b Backspace.
\n Newline, or linefeed,.
\r Carriage return.
\t Tab.
\Z ASCII 26 (Control+Z).
\\ Backslash (“\”).
\% “%” character.
\_ A “_” character.

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

MariaDB [(none)]> SELECT "\\0String";
+----------+
| \0String |
+----------+
| \0String |
+----------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT "String\n";
+---------+
| String
 |
+---------+
| String
 |
+---------+
1 row in set (0.000 sec)

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

那在 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 如何處理網站訪客的留言資料