測試環境為 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).
- 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)
- 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) - 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)
- 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)
- 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)
- 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)
- 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)
- 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)
- Backslash ( \ ) 需使用 \\
MariaDB [(none)]> SELECT '\'; '> '; +-----+ | '; | +-----+ | '; | +-----+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT '\\'; +---+ | \ | +---+ | \ | +---+ 1 row in set (0.000 sec) - % character 需使用 \%
- _ 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
- 在 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)
沒有解決問題,試試搜尋本站其他內容