插入"&"特殊字符的几种思考

川长思鸟来 2023-07-20 13:42 25阅读 0赞

一位铁杆朋友，今天问了个问题，写了一个Python程序，从文件读取数据，其中可能包含“&”这种特殊字符，为了让其能插入Oracle，需要做什么处理？

首先，我想问的问题，"&"字符在插入数据库的时候有什么特别之处？

在sqlplus下执行如下SQL，向表A插入记录“a&a”，他会提示我们“Enter value for a:”，输入变量a的值，此处输入空，则实际存储的，就是"a"，

SQL> create table a (id number, a varchar2(25));
    Table created.
    
    
    SQL> insert into a values(1, 'a&a');
    Enter value for a:
    old   1: insert into a values(1, 'a&a')
    new   1: insert into a values(1, 'a')
    
    
    1 row created.
    
    
    SQL> select * from a;
           ID   A
    ---------- -------------------------         
            1   a

执 行show all，可以看到这行define "&" (hex 26)，其实这个就是Oracle中用来识别自定义变量的设置，当SQL中包含"&"时，Oracle就会认为你要输入变量了，就像上面显示的 “Enter value for a:”，

SQL> show all
    ...
    define "&" (hex 26)

在sqlplus下，可以设置set define off，此时就关闭了自定义变量，他会将"&"看作是普通字符，直接使用"a&a"，不会提示“Enter value for a:”，

SQL> set define off
    
    
    SQL> insert into a values(1, 'a&a');
    1 row created.

但是在python程序中，使用cx\_Oracle连接Oracle数据库，并不支持执行set define off，原因就是cx\_Oracle只支持标准SQL语句，这个set define off只是在sqlplus下才能执行的语句，

> The command "set define off" is not a SQL statement, but a SQL*Plus statement. As such, it can only be executed in SQL*Plus. Since cx\_Oracle only processes SQL statements, this command is unnecessary in any case! If you are reading from a file that contains SQL statements that are normally run by SQL\*Plus you'll need to filter these statements out.

当然，你可以使用shell脚本，执行sqlplus就可以用set define off了，但是受限于实际，不能将Python程序转为shell。

除此之外，还可以怎么做？

我们能得到"&"的ASCII码是38，

SQL> select ascii('&') from dual;
    ASCII('&')
    ----------
        38

我们使用chr(38)，代替"&"，通过拼接字符串，就可以插入了，

SQL> insert into a values(1, 'a'||chr(38)||'a');
    1 row created.
    
    
    SQL> select * from a;
            ID A
    ---------- -------------------------
             1 a&a

但是问题来了，Python程序可以读取行数据的时候，将"&"替换为'||chr(38)||'，但是这只是一般的情况，当出现"&&"，或者多个"&"的时候，可能要特殊的判断逻辑，才可以替换正确，有些复杂。

有其他的方法么？

![format_png][]

我们换种思维，"&"字符直接入库，Oracle会将其认为是有特殊含义的，如果插入的不是"&"，就可以解决了？

步骤如下，

1.Python读取行数据时，将字符串中"&"替换为其他的字符，例如"\#"，或者不可见字符(避免待替换字符就存在于字符串中)。

2.插入Oracle时，将"\#"，replace替换为"&"，如下所示，

SQL> insert into a values(1, replace('a#c', '#', chr(38)));
    1 row created.
    
    
    SQL> select * from a;
            ID A
    ---------- -------------------------
             1 a&c

这个方案就比较耐看了，一方面，是无需关注原始字符串中，到底存在几个连续的"&"，要特殊的判断，另一方面，插入的时候，replace函数能通用，无需针对不同的输入选择特殊的逻辑，一个词概括，就是“通用”。

参考：

https://stackoverflow.com/questions/57470276/unable-to-set-define-off-in-cx-oracle

http://blog.itpub.net/20750200/viewspace-706760/

https://blog.csdn.net/dreary001/article/details/84355504

**近期热文：**

《[公众号600篇文章分类和索引][600]》

《[Oracle ACE，一段不可思议的旅程][Oracle ACE]》

《[Oracle 19c之RPM安装][Oracle 19c_RPM]》

《[应用执行慢的问题排查路径][Link 1]》

《[ACOUG年会感想][ACOUG]》

《[千万级表数据更新的需求][Link 2]》

《[探寻大表删除字段慢的原因][Link 3]》

《[一次Oracle bug的故障排查过程思考][Oracle bug]》

《[新增字段的一点一滴技巧][Link 4]》

《[对recursive calls的深刻理解][recursive calls]》

《[《Oracle Concept》第三章 - 12][Oracle Concept_ - 12]》

《[一次惊心动魄的问题排查][Link 5]》

《[英超梦幻之行][Link 6]》

《[藤子不二雄博物馆之行][Link 7]》

《[传控Tiki-Taka战术解惑][Tiki-Taka]》

[format_png]: /images/20230528/0a6d52a18e85431c8c29c6632757bf3a.png
[600]: https://blog.csdn.net/bisal/article/details/104548761
[Oracle ACE]: https://blog.csdn.net/bisal/article/details/94386471
[Oracle 19c_RPM]: https://blog.csdn.net/bisal/article/details/100909708
[Link 1]: https://blog.csdn.net/bisal/article/details/102480420
[ACOUG]: https://blog.csdn.net/bisal/article/details/103675746
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814beaddf6c2fcd242591e4892a3ac21a272f3307b9771a8ca9b76ef96b1a7bd8249c564fa&idx=1&mid=2247487001&scene=21&sn=15394f15aedee7f8f261fa7294df08a5#wechat_redirect
[Link 3]: https://blog.csdn.net/bisal/article/details/86635285
[Oracle bug]: https://blog.csdn.net/bisal/article/details/101729688
[Link 4]: https://blog.csdn.net/bisal/article/details/103626212
[recursive calls]: https://blog.csdn.net/bisal/article/details/100074863
[Oracle Concept_ - 12]: https://blog.csdn.net/bisal/article/details/100788405
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8149e8ddf6c0fea92181efe658b69c6acd52b691cd562e830f0e63a565215a136105272d27&idx=1&mid=2247486491&scene=21&sn=584c4ac132b69a1e319b2a2bedfd49e2#wechat_redirect
[Link 6]: https://blog.csdn.net/bisal/article/details/103044518
[Link 7]: https://blog.csdn.net/bisal/article/details/103379370
[Tiki-Taka]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814e5cddf6c74a763ac9fac98f94e538bca009fdcff3795533026269686983ceae190fe883&idx=1&mid=2247486383&scene=21&sn=d4c54d7033fc9a21d622e5937cddf64e#wechat_redirect