转义符

功能描述

Vastbase在MySQL兼容模式下支持以下转义字符标志：

转义符	转义之后的字符
\"	"
\n	换行符
\r	回车符
\t	制表符
\b	退格符
\'	'
\\	\

注意事项

该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。

语法格式

无

示例

示例1： 在insert中使用转义符。

1、创建测试表。

CREATE TABLE table_1173698(id int, c1 mediumtext, c2 text, c3 mediumint );

2、向测试表插入数据。

INSERT INTO table_1173698 VALUES (1,'测试abc123','噼里\t啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (2,'测试abc123','噼里\n啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (3,'测试abc123','噼里\r啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (4,'测试abc123','噼里\b啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (5,'测试abc123','噼里\0啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (6,'测试abc123','噼里\\啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (7,'测试abc123','噼里\"啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (8,'测试abc123','噼里\'啪啦aaa',8388607);
INSERT INTO table_1173698 VALUES (9,'测试abc123','\b',8388607);
INSERT INTO table_1173698 VALUES (10,'测试abc123','\r',8388607);
INSERT INTO table_1173698 VALUES (11,'测试abc123','\t',8388607);
INSERT INTO table_1173698 VALUES (12,'测试abc123','\n',8388607);
INSERT INTO table_1173698 VALUES (13,'测试abc123','\0',8388607);
INSERT INTO table_1173698 VALUES (14,'测试abc123','\\',8388607);
INSERT INTO table_1173698 VALUES (15,'测试abc123','\"',8388607);
INSERT INTO table_1173698 VALUES (16,'测试abc123','\'',8388607);

3、查询结果。

SELECT * FROM table_1173698;

返回结果为：

 id |     c1     |       c2        |   c3
----+------------+-----------------+---------
  1 | 测试abc123 | 噼里    啪啦aaa | 8388607
  2 | 测试abc123 | 噼里           +| 8388607
    |            | 啪啦aaa         |
  3 | 测试abc123 | 噼里\r啪啦aaa   | 8388607
  4 | 测试abc123 | 噼里\x08啪啦aaa | 8388607
  5 | 测试abc123 | 噼里            | 8388607
  6 | 测试abc123 | 噼里\啪啦aaa    | 8388607
  7 | 测试abc123 | 噼里"啪啦aaa    | 8388607
  8 | 测试abc123 | 噼里'啪啦aaa    | 8388607
  9 | 测试abc123 | \x08            | 8388607
 10 | 测试abc123 | \r              | 8388607
 11 | 测试abc123 |                 | 8388607
 12 | 测试abc123 |                +| 8388607
    |            |                 |
 13 | 测试abc123 |                 | 8388607
 14 | 测试abc123 | \               | 8388607
 15 | 测试abc123 | "               | 8388607
 16 | 测试abc123 | '               | 8388607
(16 rows)

示例2： 在存储过程中使用转义符。

1、创建测试表并插入数据。

CREATE TABLE table_1174629(id int, c1 mediumtext, c2 text, c3 mediumint );
INSERT INTO table_1174629 VALUES (1,'测试abc123','1噼里\t啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (2,'测试abc123','2噼里\n啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (3,'测试abc123','3噼里\r啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (4,'测试abc123','4噼里\b啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (5,'测试abc123','5噼里\0啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (6,'测试abc123','6噼里\\啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (7,'测试abc123','7噼里\"啪啦aaa',8388607);
INSERT INTO table_1174629 VALUES (8,'测试abc123','8噼里\'啪啦aaa',8388607);

2、创建存储过程。

CREATE OR REPLACE PROCEDURE proc_insert_1174629 (id int, c1 mediumtext, c2 text, c3 mediumint)
AS
BEGIN
INSERT INTO table_1174629 VALUES(id, c1, c2, c3);
END;
/

CREATE OR REPLACE PROCEDURE proc_update_1174629 ()
AS
BEGIN
UPDATE table_1174629 SET c1='修改后\t abc123' WHERE c2 = '1噼里\t啪啦aaa';
UPDATE table_1174629 SET c1='修改后\r abc123' WHERE c2 ='2噼里\n啪啦aaa';
UPDATE table_1174629 SET c1='修改后\n abc123' WHERE c2 ='3噼里\r啪啦aaa';
UPDATE table_1174629 SET c1='修改后\b abc123' WHERE c2 ='4噼里\b啪啦aaa';
UPDATE table_1174629 SET c1='修改后\0 abc123' WHERE c2 ='5噼里\0啪啦aaa';
UPDATE table_1174629 SET c1='修改后\\ abc123' WHERE c2 ='6噼里\\啪啦aaa';
UPDATE table_1174629 SET c1='修改后\"abc123' WHERE c2 ='7噼里\"啪啦aaa';
UPDATE table_1174629 SET c1='修改后\' abc123' WHERE c2 ='8噼里\'啪啦aaa';
END;
/

3、打开serveroutput参数（使信息从存储过程传输回应用程序，输出在屏幕上）。

SET SERVEROUTPUT ON;

4、调用proc_insert_1174629存储过程并查询结果。

CALL proc_insert_1174629(9,'新\b插入\t测试\0abc123','8噼里\'啪啦aaa',8388607);
CALL proc_insert_1174629(10,'新\r插入\n测试\"abc123','8噼里\'啪啦aaa',8388607);
CALL proc_insert_1174629(11,'新\\插入\n测试\'abc123','8噼里\'啪啦aaa',8388607);
SELECT * FROM table_1174629 ORDER BY id;

返回结果为：

 id |          c1          |        c2        |   c3
----+----------------------+------------------+---------
  1 | 测试abc123           | 1噼里   啪啦aaa  | 8388607
  2 | 测试abc123           | 2噼里           +| 8388607
    |                      | 啪啦aaa          |
  3 | 测试abc123           | 3噼里\r啪啦aaa   | 8388607
  4 | 测试abc123           | 4噼里\x08啪啦aaa | 8388607
  5 | 测试abc123           | 5噼里            | 8388607
  6 | 测试abc123           | 6噼里\啪啦aaa    | 8388607
  7 | 测试abc123           | 7噼里"啪啦aaa    | 8388607
  8 | 测试abc123           | 8噼里'啪啦aaa    | 8388607
  9 | 新\x08插入      测试 | 8噼里'啪啦aaa    | 8388607
 10 | 新\r插入            +| 8噼里'啪啦aaa    | 8388607
    | 测试"abc123          |                  |
 11 | 新\插入             +| 8噼里'啪啦aaa    | 8388607
    | 测试'abc123          |                  |
(11 rows)

5、调用proc_update_1174629存储过程并查询结果。

CALL proc_update_1174629();
SELECT * FROM table_1174629 ORDER BY id;

返回结果为：

 id |        c1         |        c2        |   c3
----+-------------------+------------------+---------
  1 | 修改后   abc123   | 1噼里   啪啦aaa  | 8388607
  2 | 修改后\r abc123   | 2噼里           +| 8388607
    |                   | 啪啦aaa          |
  3 | 修改后           +| 3噼里\r啪啦aaa   | 8388607
    |  abc123           |                  |
  4 | 修改后\x08 abc123 | 4噼里\x08啪啦aaa | 8388607
  5 | 修改后            | 5噼里            | 8388607
  6 | 修改后\ abc123    | 6噼里\啪啦aaa    | 8388607
  7 | 修改后"abc123     | 7噼里"啪啦aaa    | 8388607
  8 | 修改后' abc123    | 8噼里'啪啦aaa    | 8388607
  9 | 修改后' abc123    | 8噼里'啪啦aaa    | 8388607
 10 | 修改后' abc123    | 8噼里'啪啦aaa    | 8388607
 11 | 修改后' abc123    | 8噼里'啪啦aaa    | 8388607
(11 rows)

清理环境

DROP TABLE table_1173698,table_1174629;
DROP PROCEDURE proc_insert_1174629;
DROP PROCEDURE proc_update_1174629;

VastbaseG100

转义符

功能描述

注意事项

语法格式

示例

弹框头部