測試環境為 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
- 在 INSERT 字串時會用 “String” (Double Quote) 來表示裡面的資料為字串,這時候要注意不能包含 ” (Double Quote) 與 \ (Backslash Character) 這些都會造成錯誤.
- 在 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 函數有以下功能.
- 帶有單引號 ‘ , 反斜線 \ ,ASCII NUL 以及 Control-Z 帶有反斜線.
- 如果值是 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)
沒有解決問題,試試搜尋本站其他內容